FYI, the reading table has about 100 millions records now.
nobu On 15 July 2016 at 17:39, Harunobu Oyama <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 >>> > >>> >> >> >
