Re: How do you update a record with apache cayenne without using select clause

[Andrus Adamchik]

>> I do not want to write SQL query in SQL TEMPLETE or EJBQLQuery,
>> because I have two databases MS SQL server and PostgreSQL and both of them
>> have syntactical difference. 


BTW EJBQL is expressed in object terms, so there is no "native" SQL in it and 
it is fully DB-agnostic. So it gives you the best of both worlds - direct DB 
updates, but DB-independent syntax.

>> Because I already have id which I want to update, so why would I use select 
>> query ?. I am
>> dealing with more then 25 lakhs record.

Direct updates vs select-then-update is a tradeoff of performance vs. clarity 
of design and reuse of the object code. E.g. in a batch job that needs to 
process lots of objects, but doesn't have a user waiting for an immediate 
response, select-then-update approach may be preferable. And Cayenne supports 
iterated queries that allow you to process an infinite number of objects 
without crashing your app:

https://cayenne.apache.org/docs/3.1/cayenne-guide/performance-tuning.html#iterated-queries

This API is much more user-friendly in 4.0, but 3.1 version is still fully 
functional. So you may consider this tradeoff in your situation.

Andrus

> On Sep 12, 2015, at 10:45 AM, John Huss  wrote:
> 
> SqlTemplate allows you to define different queries for different databases
> and chooses the correct one at runtime.
> 
> 
> On Sat, Sep 12, 2015 at 7:51 AM Dipesh Jain  wrote:
> 
>> @Ari I do not want to write SQL query in SQL TEMPLETE or EJBQLQuery,
>> because I have two databases MS SQL server and PostgreSQL and both of them
>> have syntactical difference. I want to use predefined manipulation and
>> updation methods in cayenne, so that if I change database I do not have to
>> write SQL queries again.
>> 
>> @Andrus my concern is first select a record and then updating it does
>> not take more time then direct updation. Because I already have id
>> which I want to update, so why would I use select query ?. I am
>> dealing with more then 25 lakhs record.
>> 
>> 
>> On Fri, Sep 11, 2015 at 9:02 PM, Dipesh Jain  wrote:
>> 
>>> I am using apache cayenne 3.1. I know of one way to update records in the
>>> database is to first use select query to find record and then update it,
>>> but I don't want to use a select query for updating. How can I directly
>>> update the record? I do not want to use EJBQLQuery or SQL TEMPLETE.
>>> 
>>> --
>>> Thanks and Regards
>>> Deepesh Jain
>>> 
>> 
>> 
>> 
>> --
>> Thanks and Regards
>> Deepesh Jain
>> 

Re: How do you update a record with apache cayenne without using select clause

[John Huss]

SqlTemplate allows you to define different queries for different databases
and chooses the correct one at runtime.


On Sat, Sep 12, 2015 at 7:51 AM Dipesh Jain  wrote:

> @Ari I do not want to write SQL query in SQL TEMPLETE or EJBQLQuery,
> because I have two databases MS SQL server and PostgreSQL and both of them
> have syntactical difference. I want to use predefined manipulation and
> updation methods in cayenne, so that if I change database I do not have to
> write SQL queries again.
>
> @Andrus my concern is first select a record and then updating it does
> not take more time then direct updation. Because I already have id
> which I want to update, so why would I use select query ?. I am
> dealing with more then 25 lakhs record.
>
>
> On Fri, Sep 11, 2015 at 9:02 PM, Dipesh Jain  wrote:
>
> > I am using apache cayenne 3.1. I know of one way to update records in the
> > database is to first use select query to find record and then update it,
> > but I don't want to use a select query for updating. How can I directly
> > update the record? I do not want to use EJBQLQuery or SQL TEMPLETE.
> >
> > --
> > Thanks and Regards
> > Deepesh Jain
> >
>
>
>
> --
> Thanks and Regards
> Deepesh Jain
>

Re: How do you update a record with apache cayenne without using select clause

[Dipesh Jain]

@Ari I do not want to write SQL query in SQL TEMPLETE or EJBQLQuery,
because I have two databases MS SQL server and PostgreSQL and both of them
have syntactical difference. I want to use predefined manipulation and
updation methods in cayenne, so that if I change database I do not have to
write SQL queries again.

@Andrus my concern is first select a record and then updating it does
not take more time then direct updation. Because I already have id
which I want to update, so why would I use select query ?. I am
dealing with more then 25 lakhs record.


On Fri, Sep 11, 2015 at 9:02 PM, Dipesh Jain  wrote:

> I am using apache cayenne 3.1. I know of one way to update records in the
> database is to first use select query to find record and then update it,
> but I don't want to use a select query for updating. How can I directly
> update the record? I do not want to use EJBQLQuery or SQL TEMPLETE.
>
> --
> Thanks and Regards
> Deepesh Jain
>



-- 
Thanks and Regards
Deepesh Jain

Re: How do you update a record with apache cayenne without using select clause

[Andrus Adamchik]

> On Sep 11, 2015, at 6:32 PM, Dipesh Jain  wrote:
> 
> I am using apache cayenne 3.1. I know of one way to update records in the
> database is to first use select query to find record and then update it,
> but I don't want to use a select query for updating. How can I directly
> update the record? I do not want to use EJBQLQuery or SQL TEMPLETE.
> 
> -- 
> Thanks and Regards
> Deepesh Jain

Hi Deepesh, 

My #1 solution won't be EJBQLQuery or SQLTemplate either. I would actually 
select the objects that I need, change them and commit. This is a true ORM 
solution, as you are working with objects, can apply business logic, listeners 
and what not. 

In more rare cases of "bulk update", when a single query updates a bunch of 
records without bothering to fault objects, EJBQLQuery or SQLTemplate is the 
way to do it. 

So what case are you dealing with?

Andrus

Re: How do you update a record with apache cayenne without using select clause

[Aristedes Maniatis]

On 12/09/2015 1:32am, Dipesh Jain wrote:
> I am using apache cayenne 3.1. I know of one way to update records in the
> database is to first use select query to find record and then update it,
> but I don't want to use a select query for updating. How can I directly
> update the record? I do not want to use EJBQLQuery or SQL TEMPLETE.

That's a bit like asking "how do I code in Java without using a keyboard?" What 
are you trying to do and why are you trying to do it without the tools the ORM 
gives you?

Ari


-- 
-->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A