You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Harunobu Oyama <ho...@telensa.com> on 2016/07/15 09:56:29 UTC

Aggregate function

Hi,

What is the Cayenne way to execute aggregate functions?

For example, SELECT MAX("reading_id") FROM "reading" ?

I tried passing SQLTemplate containing the SQL written above
to performGenericQuery method, with no luck. Are we supposed to use JDBC?
If so, what is the best way to get the JDBC Connection?

Any hint would be appreciated.

thank you,
nobu

Re: Aggregate function

Posted by Harunobu Oyama <ho...@telensa.com>.
FYI,

the reading table has about 100 millions records now.

nobu



On 15 July 2016 at 17:39, Harunobu Oyama <ho...@telensa.com> wrote:

> Michael,
>
> Unfortunately the utility did not work for me.
>
> After entering the max method, it ended up failing by throwing an
> exception like following. after spending 5 to 10 seconds.
> The rest of the program works as before, if I comment out the use of
> AggregateUtils.
>
> Any idea?
>
> nobu
>
>
>
>
>
> //////////////////////////////////////////////////////////////////////////////////////////////////
> // console output snippet
>
> INFO: Opening connection: jdbc:postgresql://127.0.0.1:5432/CayenneTest
> Login: postgres
> Password: *******
> Jul 15, 2016 5:16:15 PM org.apache.cayenne.log.CommonsJdbcEventLogger
> logConnectSuccess
> INFO: +++ Connecting: SUCCESS.
>
>   // Spend some time here and then the exception got thrown
>
> Exception in thread "main" java.lang.RuntimeException: Cannot run MAX query
> at
> cbe.fetching.utilities.AggregateUtils.getResultUsingTranslator(AggregateUtils.java:230)
> at
> cbe.fetching.utilities.AggregateUtils.getResultForFunction(AggregateUtils.java:188)
> at cbe.fetching.utilities.AggregateUtils.max(AggregateUtils.java:85)
> at com.telensa.CayenneTest.main(CayenneTest.java:85)
> Caused by: org.apache.cayenne.CayenneRuntimeException: [v.3.1.1 Apr 29
> 2016 12:50:34] Error detecting database type: Can't obtain connection.
> Request timed out. Total used connections: 1
> at
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:113)
> at
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory$1.get(DefaultDbAdapterFactory.java:84)
> at
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory$1.get(DefaultDbAdapterFactory.java:81)
> at org.apache.cayenne.dba.AutoAdapter.loadAdapter(AutoAdapter.java:93)
> at org.apache.cayenne.dba.AutoAdapter.getAdapter(AutoAdapter.java:81)
> at
> org.apache.cayenne.dba.AutoAdapter.getQuotingStrategy(AutoAdapter.java:206)
> at org.apache.cayenne.access.trans.JoinStack.<init>(JoinStack.java:69)
> at
> org.apache.cayenne.access.trans.SelectTranslator.createJoinStack(SelectTranslator.java:102)
> at
> org.apache.cayenne.access.trans.SelectTranslator.getJoinStack(SelectTranslator.java:83)
> at
> org.apache.cayenne.access.trans.SelectTranslator.createSqlString(SelectTranslator.java:112)
> at
> cbe.fetching.utilities.AggregateTranslator.createSqlString(AggregateTranslator.java:72)
> at
> org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:204)
> at
> cbe.fetching.utilities.AggregateUtils.getResultUsingTranslator(AggregateUtils.java:219)
> ... 3 more
> Caused by: java.sql.SQLException: Can't obtain connection. Request timed
> out. Total used connections: 1
> at
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> at org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> at org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> at
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> ... 15 more
>
>
>
>
> On 15 July 2016 at 16:17, Harunobu Oyama <ho...@telensa.com> wrote:
>
>> Hi Michael,
>>
>> Thank you for your message. I will give it a try.
>>
>> As for "reading_id", yes it is the primary key and actually generated by
>> using bigserial.
>> While I was populating 3 billions of those, I wrote a throw-away code to
>> assign the
>> value from the Java code without spending time on sequence query. It did
>> made the
>> data population about 20% faster. The query was meant to be used to find
>> out the
>> initial value, but I even cheated to hard code the value this time.
>>
>> nobu
>>
>>
>> On 15 July 2016 at 15:49, Michael Gentry <bl...@gmail.com> wrote:
>>
>>> Hi Nobu,
>>>
>>> I have an AggregateUtils that I use:
>>>
>>>
>>> https://github.com/mrg/cbe/tree/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/utilities
>>>
>>> Here is an example of code using AggregateUtils:
>>>
>>>
>>> https://github.com/mrg/cbe/blob/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/Aggregates.java
>>>
>>> To use it for your example would be something like this:
>>>
>>> SelectQuery query = new SelectQuery(Reading.class);
>>> BigDecimal max = AggregateUtils.max(dataContext, query,
>>> READING.READING_ID_PROPERTY);
>>>
>>> On a side note, I'm guessing "reading_id" is your primary key?  If so,
>>> why
>>> are you trying to get the maximum value for it?  Is the primary key/id
>>> not
>>> being generated by the database?
>>>
>>> mrg
>>>
>>>
>>> On Fri, Jul 15, 2016 at 5:56 AM, Harunobu Oyama <ho...@telensa.com> wrote:
>>>
>>> > Hi,
>>> >
>>> > What is the Cayenne way to execute aggregate functions?
>>> >
>>> > For example, SELECT MAX("reading_id") FROM "reading" ?
>>> >
>>> > I tried passing SQLTemplate containing the SQL written above
>>> > to performGenericQuery method, with no luck. Are we supposed to use
>>> JDBC?
>>> > If so, what is the best way to get the JDBC Connection?
>>> >
>>> > Any hint would be appreciated.
>>> >
>>> > thank you,
>>> > nobu
>>> >
>>>
>>
>>
>

Re: Aggregate function

Posted by Harunobu Oyama <ho...@telensa.com>.
Michael,

I am really sorry, but I can no longer reproduce the issue that I was
seeing even if I removed the unnecessary Cayenne query that I inserted
before the AggregateUtils use. I will report you the cause, if I
successfully pointed out the exact cause of the issue.

nobu


On 15 July 2016 at 19:15, Michael Gentry <bl...@gmail.com> wrote:

> Hi Nobu,
>
> That's an odd limitation I wouldn't have expected to exist.  Can you share
> how you were creating the runtime and calling AggregateUtils when it was
> failing?
>
> Thanks,
>
> mrg
>
>
> On Fri, Jul 15, 2016 at 1:46 PM, Harunobu Oyama <ho...@telensa.com> wrote:
>
> > Michael,
> >
> > Increasing the maximum connection did not help, but using Cayenne for
> > something else beforehand solved the problem.
> > I do not understand the logic behind it, but it seems AggregateUtils
> fails
> > if it is CayenneRuntime's first customer.
> >
> > Problem sorted and the utility provides me the functionality I wanted.
> >
> > thank you,
> > nobu
> >
> >
> >
> > On 15 July 2016 at 18:36, Michael Gentry <bl...@gmail.com> wrote:
> >
> > > Hi Nobu,
> > >
> > > Try increasing your maximum connections.
> > >
> > > mrg
> > >
> > >
> > > On Fri, Jul 15, 2016 at 1:26 PM, Harunobu Oyama <ho...@telensa.com>
> wrote:
> > >
> > > > Michael,
> > > >
> > > > The min connection and max connection are both 1 as default.
> > > >
> > > > nobu
> > > >
> > > >
> > > > On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com>
> wrote:
> > > >
> > > > > On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com>
> > > wrote:
> > > > >
> > > > > > Caused by: java.sql.SQLException: Can't obtain connection.
> Request
> > > > timed
> > > > > > out. Total used connections: 1
> > > > > > at
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> > > > > > at
> > > > >
> > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> > > > > > at
> > > > >
> > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> > > > > > at
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> > > > > > ... 15 more
> > > > >
> > > > >
> > > > > Hi Nobu,
> > > > >
> > > > > Given this part of the exception, it seems it was unable to get a
> > > > > connection to the database.  How is your connection pool
> configured?
> > > Do
> > > > > you only have one connection?
> > > > >
> > > > > mrg
> > > > >
> > > >
> > >
> >
>

Re: Aggregate function

Posted by Michael Gentry <bl...@gmail.com>.
Hi Nobu,

That's an odd limitation I wouldn't have expected to exist.  Can you share
how you were creating the runtime and calling AggregateUtils when it was
failing?

Thanks,

mrg


On Fri, Jul 15, 2016 at 1:46 PM, Harunobu Oyama <ho...@telensa.com> wrote:

> Michael,
>
> Increasing the maximum connection did not help, but using Cayenne for
> something else beforehand solved the problem.
> I do not understand the logic behind it, but it seems AggregateUtils fails
> if it is CayenneRuntime's first customer.
>
> Problem sorted and the utility provides me the functionality I wanted.
>
> thank you,
> nobu
>
>
>
> On 15 July 2016 at 18:36, Michael Gentry <bl...@gmail.com> wrote:
>
> > Hi Nobu,
> >
> > Try increasing your maximum connections.
> >
> > mrg
> >
> >
> > On Fri, Jul 15, 2016 at 1:26 PM, Harunobu Oyama <ho...@telensa.com> wrote:
> >
> > > Michael,
> > >
> > > The min connection and max connection are both 1 as default.
> > >
> > > nobu
> > >
> > >
> > > On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:
> > >
> > > > On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com>
> > wrote:
> > > >
> > > > > Caused by: java.sql.SQLException: Can't obtain connection. Request
> > > timed
> > > > > out. Total used connections: 1
> > > > > at
> > > > >
> > > > >
> > > >
> > >
> >
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> > > > > at
> > > >
> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> > > > > at
> > > >
> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> > > > > at
> > > > >
> > > > >
> > > >
> > >
> >
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> > > > > ... 15 more
> > > >
> > > >
> > > > Hi Nobu,
> > > >
> > > > Given this part of the exception, it seems it was unable to get a
> > > > connection to the database.  How is your connection pool configured?
> > Do
> > > > you only have one connection?
> > > >
> > > > mrg
> > > >
> > >
> >
>

Re: Aggregate function

Posted by Harunobu Oyama <ho...@telensa.com>.
Michael,

Increasing the maximum connection did not help, but using Cayenne for
something else beforehand solved the problem.
I do not understand the logic behind it, but it seems AggregateUtils fails
if it is CayenneRuntime's first customer.

Problem sorted and the utility provides me the functionality I wanted.

thank you,
nobu



On 15 July 2016 at 18:36, Michael Gentry <bl...@gmail.com> wrote:

> Hi Nobu,
>
> Try increasing your maximum connections.
>
> mrg
>
>
> On Fri, Jul 15, 2016 at 1:26 PM, Harunobu Oyama <ho...@telensa.com> wrote:
>
> > Michael,
> >
> > The min connection and max connection are both 1 as default.
> >
> > nobu
> >
> >
> > On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:
> >
> > > On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com>
> wrote:
> > >
> > > > Caused by: java.sql.SQLException: Can't obtain connection. Request
> > timed
> > > > out. Total used connections: 1
> > > > at
> > > >
> > > >
> > >
> >
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> > > > at
> > > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> > > > at
> > > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> > > > at
> > > >
> > > >
> > >
> >
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> > > > ... 15 more
> > >
> > >
> > > Hi Nobu,
> > >
> > > Given this part of the exception, it seems it was unable to get a
> > > connection to the database.  How is your connection pool configured?
> Do
> > > you only have one connection?
> > >
> > > mrg
> > >
> >
>

Re: Aggregate function

Posted by Michael Gentry <bl...@gmail.com>.
Hi Nobu,

Try increasing your maximum connections.

mrg


On Fri, Jul 15, 2016 at 1:26 PM, Harunobu Oyama <ho...@telensa.com> wrote:

> Michael,
>
> The min connection and max connection are both 1 as default.
>
> nobu
>
>
> On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:
>
> > On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com> wrote:
> >
> > > Caused by: java.sql.SQLException: Can't obtain connection. Request
> timed
> > > out. Total used connections: 1
> > > at
> > >
> > >
> >
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> > > at
> > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> > > at
> > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> > > at
> > >
> > >
> >
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> > > ... 15 more
> >
> >
> > Hi Nobu,
> >
> > Given this part of the exception, it seems it was unable to get a
> > connection to the database.  How is your connection pool configured?  Do
> > you only have one connection?
> >
> > mrg
> >
>

Re: Aggregate function

Posted by John Huss <jo...@gmail.com>.
You have to call query.setFetchesDataRows(true)
On Fri, Jul 15, 2016 at 1:08 PM John Huss <jo...@gmail.com> wrote:

> That's the query you don't want. You want a DataRow object not a Reading.
> On Fri, Jul 15, 2016 at 12:58 PM Harunobu Oyama <ho...@telensa.com> wrote:
>
>> John,
>>
>> So, there is no Cayenne native way to perform a query such as SELECT
>> MAX("reading_id") FROM "reading"  ?
>> It sounds like you are recommending to write another query to fetch a
>> record with max reading_id instead.
>>
>> Indeed, the code below worked.
>>
>>   SQLTemplate query = new SQLTemplate( Reading.class, "SELECT * FROM
>> \"reading\" WHERE \"reading_id\"=( SELECT MAX(\"reading_id\") FROM
>> \"reading\" )" );
>>   List result = context.performQuery( query );
>>
>> thank you,
>> nobu
>>
>>
>>
>>
>> On 15 July 2016 at 18:35, John Huss <jo...@gmail.com> wrote:
>>
>> > Make sure your sqltemplate us set to fetch datarows (hashmap) not
>> objects
>> > On Fri, Jul 15, 2016 at 12:27 PM Harunobu Oyama <ho...@telensa.com> wrote:
>> >
>> > > Michael,
>> > >
>> > > The min connection and max connection are both 1 as default.
>> > >
>> > > nobu
>> > >
>> > >
>> > > On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:
>> > >
>> > > > On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com>
>> > wrote:
>> > > >
>> > > > > Caused by: java.sql.SQLException: Can't obtain connection. Request
>> > > timed
>> > > > > out. Total used connections: 1
>> > > > > at
>> > > > >
>> > > > >
>> > > >
>> > >
>> >
>> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
>> > > > > at
>> > > >
>> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
>> > > > > at
>> > > >
>> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
>> > > > > at
>> > > > >
>> > > > >
>> > > >
>> > >
>> >
>> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
>> > > > > ... 15 more
>> > > >
>> > > >
>> > > > Hi Nobu,
>> > > >
>> > > > Given this part of the exception, it seems it was unable to get a
>> > > > connection to the database.  How is your connection pool configured?
>> > Do
>> > > > you only have one connection?
>> > > >
>> > > > mrg
>> > > >
>> > >
>> >
>>
>

Re: Aggregate function

Posted by John Huss <jo...@gmail.com>.
That's the query you don't want. You want a DataRow object not a Reading.
On Fri, Jul 15, 2016 at 12:58 PM Harunobu Oyama <ho...@telensa.com> wrote:

> John,
>
> So, there is no Cayenne native way to perform a query such as SELECT
> MAX("reading_id") FROM "reading"  ?
> It sounds like you are recommending to write another query to fetch a
> record with max reading_id instead.
>
> Indeed, the code below worked.
>
>   SQLTemplate query = new SQLTemplate( Reading.class, "SELECT * FROM
> \"reading\" WHERE \"reading_id\"=( SELECT MAX(\"reading_id\") FROM
> \"reading\" )" );
>   List result = context.performQuery( query );
>
> thank you,
> nobu
>
>
>
>
> On 15 July 2016 at 18:35, John Huss <jo...@gmail.com> wrote:
>
> > Make sure your sqltemplate us set to fetch datarows (hashmap) not objects
> > On Fri, Jul 15, 2016 at 12:27 PM Harunobu Oyama <ho...@telensa.com> wrote:
> >
> > > Michael,
> > >
> > > The min connection and max connection are both 1 as default.
> > >
> > > nobu
> > >
> > >
> > > On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:
> > >
> > > > On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com>
> > wrote:
> > > >
> > > > > Caused by: java.sql.SQLException: Can't obtain connection. Request
> > > timed
> > > > > out. Total used connections: 1
> > > > > at
> > > > >
> > > > >
> > > >
> > >
> >
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> > > > > at
> > > >
> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> > > > > at
> > > >
> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> > > > > at
> > > > >
> > > > >
> > > >
> > >
> >
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> > > > > ... 15 more
> > > >
> > > >
> > > > Hi Nobu,
> > > >
> > > > Given this part of the exception, it seems it was unable to get a
> > > > connection to the database.  How is your connection pool configured?
> > Do
> > > > you only have one connection?
> > > >
> > > > mrg
> > > >
> > >
> >
>

Re: Aggregate function

Posted by Harunobu Oyama <ho...@telensa.com>.
Robert,

Thank you for your message and sorry for my slow response.
EJBQLQuery looks like a good way to perform 'general' query to get non
entity result.

nobu


On 15 July 2016 at 22:28, Robert Zeigler <ro...@roxanemy.com>
wrote:

> Cayenne supports EJBQLQuery, which would be another way to go about this.
>
> See also:
>
> http://cayenne.apache.org/docs/4.0/cayenne-guide/queries.html#ejbqlquery <
> http://cayenne.apache.org/docs/4.0/cayenne-guide/queries.html#ejbqlquery>
>
> Robert
>
> > On Jul 15, 2016, at 12:58 PM, Harunobu Oyama <ho...@telensa.com> wrote:
> >
> > John,
> >
> > So, there is no Cayenne native way to perform a query such as SELECT
> > MAX("reading_id") FROM "reading"  ?
> > It sounds like you are recommending to write another query to fetch a
> > record with max reading_id instead.
> >
> > Indeed, the code below worked.
> >
> >  SQLTemplate query = new SQLTemplate( Reading.class, "SELECT * FROM
> > \"reading\" WHERE \"reading_id\"=( SELECT MAX(\"reading_id\") FROM
> > \"reading\" )" );
> >  List result = context.performQuery( query );
> >
> > thank you,
> > nobu
> >
> >
> >
> >
> > On 15 July 2016 at 18:35, John Huss <jo...@gmail.com> wrote:
> >
> >> Make sure your sqltemplate us set to fetch datarows (hashmap) not
> objects
> >> On Fri, Jul 15, 2016 at 12:27 PM Harunobu Oyama <ho...@telensa.com> wrote:
> >>
> >>> Michael,
> >>>
> >>> The min connection and max connection are both 1 as default.
> >>>
> >>> nobu
> >>>
> >>>
> >>> On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:
> >>>
> >>>> On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com>
> >> wrote:
> >>>>
> >>>>> Caused by: java.sql.SQLException: Can't obtain connection. Request
> >>> timed
> >>>>> out. Total used connections: 1
> >>>>> at
> >>>>>
> >>>>>
> >>>>
> >>>
> >>
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> >>>>> at
> >>>>
> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> >>>>> at
> >>>>
> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> >>>>> at
> >>>>>
> >>>>>
> >>>>
> >>>
> >>
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> >>>>> ... 15 more
> >>>>
> >>>>
> >>>> Hi Nobu,
> >>>>
> >>>> Given this part of the exception, it seems it was unable to get a
> >>>> connection to the database.  How is your connection pool configured?
> >> Do
> >>>> you only have one connection?
> >>>>
> >>>> mrg
> >>>>
> >>>
> >>
>
>

Re: Aggregate function

Posted by Robert Zeigler <ro...@roxanemy.com>.
Cayenne supports EJBQLQuery, which would be another way to go about this.

See also:

http://cayenne.apache.org/docs/4.0/cayenne-guide/queries.html#ejbqlquery <http://cayenne.apache.org/docs/4.0/cayenne-guide/queries.html#ejbqlquery>

Robert

> On Jul 15, 2016, at 12:58 PM, Harunobu Oyama <ho...@telensa.com> wrote:
> 
> John,
> 
> So, there is no Cayenne native way to perform a query such as SELECT
> MAX("reading_id") FROM "reading"  ?
> It sounds like you are recommending to write another query to fetch a
> record with max reading_id instead.
> 
> Indeed, the code below worked.
> 
>  SQLTemplate query = new SQLTemplate( Reading.class, "SELECT * FROM
> \"reading\" WHERE \"reading_id\"=( SELECT MAX(\"reading_id\") FROM
> \"reading\" )" );
>  List result = context.performQuery( query );
> 
> thank you,
> nobu
> 
> 
> 
> 
> On 15 July 2016 at 18:35, John Huss <jo...@gmail.com> wrote:
> 
>> Make sure your sqltemplate us set to fetch datarows (hashmap) not objects
>> On Fri, Jul 15, 2016 at 12:27 PM Harunobu Oyama <ho...@telensa.com> wrote:
>> 
>>> Michael,
>>> 
>>> The min connection and max connection are both 1 as default.
>>> 
>>> nobu
>>> 
>>> 
>>> On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:
>>> 
>>>> On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com>
>> wrote:
>>>> 
>>>>> Caused by: java.sql.SQLException: Can't obtain connection. Request
>>> timed
>>>>> out. Total used connections: 1
>>>>> at
>>>>> 
>>>>> 
>>>> 
>>> 
>> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
>>>>> at
>>>> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
>>>>> at
>>>> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
>>>>> at
>>>>> 
>>>>> 
>>>> 
>>> 
>> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
>>>>> ... 15 more
>>>> 
>>>> 
>>>> Hi Nobu,
>>>> 
>>>> Given this part of the exception, it seems it was unable to get a
>>>> connection to the database.  How is your connection pool configured?
>> Do
>>>> you only have one connection?
>>>> 
>>>> mrg
>>>> 
>>> 
>> 


Re: Aggregate function

Posted by Harunobu Oyama <ho...@telensa.com>.
John,

So, there is no Cayenne native way to perform a query such as SELECT
MAX("reading_id") FROM "reading"  ?
It sounds like you are recommending to write another query to fetch a
record with max reading_id instead.

Indeed, the code below worked.

  SQLTemplate query = new SQLTemplate( Reading.class, "SELECT * FROM
\"reading\" WHERE \"reading_id\"=( SELECT MAX(\"reading_id\") FROM
\"reading\" )" );
  List result = context.performQuery( query );

thank you,
nobu




On 15 July 2016 at 18:35, John Huss <jo...@gmail.com> wrote:

> Make sure your sqltemplate us set to fetch datarows (hashmap) not objects
> On Fri, Jul 15, 2016 at 12:27 PM Harunobu Oyama <ho...@telensa.com> wrote:
>
> > Michael,
> >
> > The min connection and max connection are both 1 as default.
> >
> > nobu
> >
> >
> > On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:
> >
> > > On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com>
> wrote:
> > >
> > > > Caused by: java.sql.SQLException: Can't obtain connection. Request
> > timed
> > > > out. Total used connections: 1
> > > > at
> > > >
> > > >
> > >
> >
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> > > > at
> > > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> > > > at
> > > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> > > > at
> > > >
> > > >
> > >
> >
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> > > > ... 15 more
> > >
> > >
> > > Hi Nobu,
> > >
> > > Given this part of the exception, it seems it was unable to get a
> > > connection to the database.  How is your connection pool configured?
> Do
> > > you only have one connection?
> > >
> > > mrg
> > >
> >
>

Re: Aggregate function

Posted by John Huss <jo...@gmail.com>.
Make sure your sqltemplate us set to fetch datarows (hashmap) not objects
On Fri, Jul 15, 2016 at 12:27 PM Harunobu Oyama <ho...@telensa.com> wrote:

> Michael,
>
> The min connection and max connection are both 1 as default.
>
> nobu
>
>
> On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:
>
> > On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com> wrote:
> >
> > > Caused by: java.sql.SQLException: Can't obtain connection. Request
> timed
> > > out. Total used connections: 1
> > > at
> > >
> > >
> >
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> > > at
> > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> > > at
> > org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> > > at
> > >
> > >
> >
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> > > ... 15 more
> >
> >
> > Hi Nobu,
> >
> > Given this part of the exception, it seems it was unable to get a
> > connection to the database.  How is your connection pool configured?  Do
> > you only have one connection?
> >
> > mrg
> >
>

Re: Aggregate function

Posted by Harunobu Oyama <ho...@telensa.com>.
Michael,

The min connection and max connection are both 1 as default.

nobu


On 15 July 2016 at 18:16, Michael Gentry <bl...@gmail.com> wrote:

> On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com> wrote:
>
> > Caused by: java.sql.SQLException: Can't obtain connection. Request timed
> > out. Total used connections: 1
> > at
> >
> >
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> > at
> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> > at
> org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> > at
> >
> >
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> > ... 15 more
>
>
> Hi Nobu,
>
> Given this part of the exception, it seems it was unable to get a
> connection to the database.  How is your connection pool configured?  Do
> you only have one connection?
>
> mrg
>

Re: Aggregate function

Posted by Michael Gentry <bl...@gmail.com>.
On Fri, Jul 15, 2016 at 12:39 PM, Harunobu Oyama <ho...@telensa.com> wrote:

> Caused by: java.sql.SQLException: Can't obtain connection. Request timed
> out. Total used connections: 1
> at
>
> org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
> at org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
> at org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
> at
>
> org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
> ... 15 more


Hi Nobu,

Given this part of the exception, it seems it was unable to get a
connection to the database.  How is your connection pool configured?  Do
you only have one connection?

mrg

Re: Aggregate function

Posted by Harunobu Oyama <ho...@telensa.com>.
Michael,

Unfortunately the utility did not work for me.

After entering the max method, it ended up failing by throwing an exception
like following. after spending 5 to 10 seconds.
The rest of the program works as before, if I comment out the use of
AggregateUtils.

Any idea?

nobu




//////////////////////////////////////////////////////////////////////////////////////////////////
// console output snippet

INFO: Opening connection: jdbc:postgresql://127.0.0.1:5432/CayenneTest
Login: postgres
Password: *******
Jul 15, 2016 5:16:15 PM org.apache.cayenne.log.CommonsJdbcEventLogger
logConnectSuccess
INFO: +++ Connecting: SUCCESS.

  // Spend some time here and then the exception got thrown

Exception in thread "main" java.lang.RuntimeException: Cannot run MAX query
at
cbe.fetching.utilities.AggregateUtils.getResultUsingTranslator(AggregateUtils.java:230)
at
cbe.fetching.utilities.AggregateUtils.getResultForFunction(AggregateUtils.java:188)
at cbe.fetching.utilities.AggregateUtils.max(AggregateUtils.java:85)
at com.telensa.CayenneTest.main(CayenneTest.java:85)
Caused by: org.apache.cayenne.CayenneRuntimeException: [v.3.1.1 Apr 29 2016
12:50:34] Error detecting database type: Can't obtain connection. Request
timed out. Total used connections: 1
at
org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:113)
at
org.apache.cayenne.configuration.server.DefaultDbAdapterFactory$1.get(DefaultDbAdapterFactory.java:84)
at
org.apache.cayenne.configuration.server.DefaultDbAdapterFactory$1.get(DefaultDbAdapterFactory.java:81)
at org.apache.cayenne.dba.AutoAdapter.loadAdapter(AutoAdapter.java:93)
at org.apache.cayenne.dba.AutoAdapter.getAdapter(AutoAdapter.java:81)
at
org.apache.cayenne.dba.AutoAdapter.getQuotingStrategy(AutoAdapter.java:206)
at org.apache.cayenne.access.trans.JoinStack.<init>(JoinStack.java:69)
at
org.apache.cayenne.access.trans.SelectTranslator.createJoinStack(SelectTranslator.java:102)
at
org.apache.cayenne.access.trans.SelectTranslator.getJoinStack(SelectTranslator.java:83)
at
org.apache.cayenne.access.trans.SelectTranslator.createSqlString(SelectTranslator.java:112)
at
cbe.fetching.utilities.AggregateTranslator.createSqlString(AggregateTranslator.java:72)
at
org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:204)
at
cbe.fetching.utilities.AggregateUtils.getResultUsingTranslator(AggregateUtils.java:219)
... 3 more
Caused by: java.sql.SQLException: Can't obtain connection. Request timed
out. Total used connections: 1
at
org.apache.cayenne.conn.PoolManager.uncheckPooledConnection(PoolManager.java:454)
at org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:382)
at org.apache.cayenne.conn.PoolManager.getConnection(PoolManager.java:371)
at
org.apache.cayenne.configuration.server.DefaultDbAdapterFactory.detectAdapter(DefaultDbAdapterFactory.java:97)
... 15 more




On 15 July 2016 at 16:17, Harunobu Oyama <ho...@telensa.com> wrote:

> Hi Michael,
>
> Thank you for your message. I will give it a try.
>
> As for "reading_id", yes it is the primary key and actually generated by
> using bigserial.
> While I was populating 3 billions of those, I wrote a throw-away code to
> assign the
> value from the Java code without spending time on sequence query. It did
> made the
> data population about 20% faster. The query was meant to be used to find
> out the
> initial value, but I even cheated to hard code the value this time.
>
> nobu
>
>
> On 15 July 2016 at 15:49, Michael Gentry <bl...@gmail.com> wrote:
>
>> Hi Nobu,
>>
>> I have an AggregateUtils that I use:
>>
>>
>> https://github.com/mrg/cbe/tree/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/utilities
>>
>> Here is an example of code using AggregateUtils:
>>
>>
>> https://github.com/mrg/cbe/blob/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/Aggregates.java
>>
>> To use it for your example would be something like this:
>>
>> SelectQuery query = new SelectQuery(Reading.class);
>> BigDecimal max = AggregateUtils.max(dataContext, query,
>> READING.READING_ID_PROPERTY);
>>
>> On a side note, I'm guessing "reading_id" is your primary key?  If so, why
>> are you trying to get the maximum value for it?  Is the primary key/id not
>> being generated by the database?
>>
>> mrg
>>
>>
>> On Fri, Jul 15, 2016 at 5:56 AM, Harunobu Oyama <ho...@telensa.com> wrote:
>>
>> > Hi,
>> >
>> > What is the Cayenne way to execute aggregate functions?
>> >
>> > For example, SELECT MAX("reading_id") FROM "reading" ?
>> >
>> > I tried passing SQLTemplate containing the SQL written above
>> > to performGenericQuery method, with no luck. Are we supposed to use
>> JDBC?
>> > If so, what is the best way to get the JDBC Connection?
>> >
>> > Any hint would be appreciated.
>> >
>> > thank you,
>> > nobu
>> >
>>
>
>

Re: Aggregate function

Posted by Harunobu Oyama <ho...@telensa.com>.
Hi Michael,

Thank you for your message. I will give it a try.

As for "reading_id", yes it is the primary key and actually generated by
using bigserial.
While I was populating 3 billions of those, I wrote a throw-away code to
assign the
value from the Java code without spending time on sequence query. It did
made the
data population about 20% faster. The query was meant to be used to find
out the
initial value, but I even cheated to hard code the value this time.

nobu


On 15 July 2016 at 15:49, Michael Gentry <bl...@gmail.com> wrote:

> Hi Nobu,
>
> I have an AggregateUtils that I use:
>
>
> https://github.com/mrg/cbe/tree/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/utilities
>
> Here is an example of code using AggregateUtils:
>
>
> https://github.com/mrg/cbe/blob/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/Aggregates.java
>
> To use it for your example would be something like this:
>
> SelectQuery query = new SelectQuery(Reading.class);
> BigDecimal max = AggregateUtils.max(dataContext, query,
> READING.READING_ID_PROPERTY);
>
> On a side note, I'm guessing "reading_id" is your primary key?  If so, why
> are you trying to get the maximum value for it?  Is the primary key/id not
> being generated by the database?
>
> mrg
>
>
> On Fri, Jul 15, 2016 at 5:56 AM, Harunobu Oyama <ho...@telensa.com> wrote:
>
> > Hi,
> >
> > What is the Cayenne way to execute aggregate functions?
> >
> > For example, SELECT MAX("reading_id") FROM "reading" ?
> >
> > I tried passing SQLTemplate containing the SQL written above
> > to performGenericQuery method, with no luck. Are we supposed to use JDBC?
> > If so, what is the best way to get the JDBC Connection?
> >
> > Any hint would be appreciated.
> >
> > thank you,
> > nobu
> >
>

Re: Aggregate function

Posted by Michael Gentry <bl...@gmail.com>.
Hi Nobu,

I have an AggregateUtils that I use:

https://github.com/mrg/cbe/tree/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/utilities

Here is an example of code using AggregateUtils:

https://github.com/mrg/cbe/blob/master/FetchingObjects/Aggregates/src/main/java/cbe/fetching/Aggregates.java

To use it for your example would be something like this:

SelectQuery query = new SelectQuery(Reading.class);
BigDecimal max = AggregateUtils.max(dataContext, query,
READING.READING_ID_PROPERTY);

On a side note, I'm guessing "reading_id" is your primary key?  If so, why
are you trying to get the maximum value for it?  Is the primary key/id not
being generated by the database?

mrg


On Fri, Jul 15, 2016 at 5:56 AM, Harunobu Oyama <ho...@telensa.com> wrote:

> Hi,
>
> What is the Cayenne way to execute aggregate functions?
>
> For example, SELECT MAX("reading_id") FROM "reading" ?
>
> I tried passing SQLTemplate containing the SQL written above
> to performGenericQuery method, with no luck. Are we supposed to use JDBC?
> If so, what is the best way to get the JDBC Connection?
>
> Any hint would be appreciated.
>
> thank you,
> nobu
>

Re: Aggregate function

Posted by Harunobu Oyama <ho...@telensa.com>.
John,

The following code showed "The result was a null" message.

  SQLTemplate query = new SQLTemplate( Reading.class, "SELECT
MAX(\"reading_id\") FROM \"reading\"" );
  List result = context.performQuery( query );
  result.forEach( r -> {
    if ( r == null ) {
      System.out.println( "The result was a null" );
    } else {
      System.out.println( r.toString() );
    }
  } );

nobu



On 15 July 2016 at 18:05, John Huss <jo...@gmail.com> wrote:

> You need to call context.performQuery NOT performGenericQuery.
>
> On Fri, Jul 15, 2016 at 4:56 AM Harunobu Oyama <ho...@telensa.com> wrote:
>
> > Hi,
> >
> > What is the Cayenne way to execute aggregate functions?
> >
> > For example, SELECT MAX("reading_id") FROM "reading" ?
> >
> > I tried passing SQLTemplate containing the SQL written above
> > to performGenericQuery method, with no luck. Are we supposed to use JDBC?
> > If so, what is the best way to get the JDBC Connection?
> >
> > Any hint would be appreciated.
> >
> > thank you,
> > nobu
> >
>

Re: Aggregate function

Posted by John Huss <jo...@gmail.com>.
You need to call context.performQuery NOT performGenericQuery.

On Fri, Jul 15, 2016 at 4:56 AM Harunobu Oyama <ho...@telensa.com> wrote:

> Hi,
>
> What is the Cayenne way to execute aggregate functions?
>
> For example, SELECT MAX("reading_id") FROM "reading" ?
>
> I tried passing SQLTemplate containing the SQL written above
> to performGenericQuery method, with no luck. Are we supposed to use JDBC?
> If so, what is the best way to get the JDBC Connection?
>
> Any hint would be appreciated.
>
> thank you,
> nobu
>