-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
BusyByte
 Post subject: select max not working with createSQLQuery
PostPosted: Sun Feb 12, 2006 12:30 am 
Beginner
Beginner

Joined: Sun Feb 12, 2006 12:19 am
Posts: 20
Hibernate version: 3.1

Name and version of the database you are using:
HSQLDB and MySQL

I tried this and it doesn't work and I'm not sure why and think it should work:
Code:
StringBuffer sb = new StringBuffer(20);
   sb.append("select MAX(IFNULL(");
   sb.append(primaryKeyColumn.getName());
   sb.append(", 0)) as maxPrimayKey from ");
   sb.append(tableName);

Integer max = (Integer) session.createSQLQuery(sb.toString()).addScalar("maxPrimaryKey", Hibernate.INTEGER).uniqueResult();



However if I do this it does work:
Code:
StringBuffer sb = new StringBuffer(20);
   sb.append("select MAX(IFNULL(");
   sb.append(primaryKeyColumn.getName());
   sb.append(", 0)) as maxPrimayKey from ");
   sb.append(tableName);
Connection connection = session.connection();
   
   PreparedStatement statement = null;
   ResultSet rs = null;
   int maxPrimaryKeyValue = -1;
       statement = connection.prepareStatement(sb.toString());
   
       rs = statement.executeQuery();
      
       int numRows = 0;
       if(rs != null)
       {
      while(rs.next())
      {
          maxPrimaryKeyValue = rs.getInt(1);
          numRows++;
      }
       }
       if(numRows > 0)
       {
      logger.error("More than one max primary key returned");
       }
       //Integer max = (Integer) session.createSQLQuery(sb.toString()).addScalar("maxPrimaryKey", Hibernate.INTEGER).uniqueResult();
       connection.close();


Anyone have an id how to get the first way with the session.createSQLQuery to work?
Top
 Profile  
 
twashing
 Post subject:
PostPosted: Mon Feb 13, 2006 2:43 pm 
Newbie

Joined: Sun May 09, 2004 2:45 pm
Posts: 14
I'm having the same problem. This is the code and corresponding results I get:


Code:
// 2.  CODE
// get the next id
SQLQuery nextQuery = session.createSQLQuery( "SELECT MAX(EventId) as maxPrimayKey FROM SP_Events" );
nextQuery.addEntity( "SpEvents", SpEvents.class );
List result = nextQuery.list();
System.out.println( "Next ID > " + result );


RESULT
Quote:
compile-src:
[echo] COMPILING SRC...
[javac] Compiling 248 source files to C:\TIMS\build
[javac] 8 warnings

runClass:
[java] >>> USING Hibernate Default Configuration [hibernate.cfg.xml]
[java] >>> USING Hibernate Config [hibernate.cfg.xml]
[java] org.hibernate.exception.SQLGrammarException: could not execute query
[java] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
[java] at org.hibernate.loader.Loader.doList(Loader.java:1596)
[java] at org.hibernate.loader.Loader.list(Loader.java:1577)
[java] at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
[java] at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
[java] at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
[java] at com.lehman.tims.event.CallEvent.main(CallEvent.java:251)
[java] Caused by: java.sql.SQLException: S0022: Invalid column name 'CloseDate0_'.
[java] at com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:531)
[java] at com.sybase.jdbc2.tds.TdsResultSet.findColumnByLabel(TdsResultSet.java:535)
[java] at com.sybase.jdbc2.jdbc.SybResultSet.findColumn(SybResultSet.java:538)
[java] at com.sybase.jdbc2.jdbc.SybResultSet.getString(SybResultSet.java:329)
[java] at org.hibernate.type.StringType.get(StringType.java:16)
[java] at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
[java] at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
[java] at org.hibernate.type.AbstractType.hydrate(AbstractType.java:80)
[java] at org.hibernate.type.ComponentType.hydrate(ComponentType.java:423)
[java] at org.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:182)
[java] at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
[java] at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
[java] at org.hibernate.loader.Loader.doQuery(Loader.java:412)
[java] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:21
8)
[java] at org.hibernate.loader.Loader.doList(Loader.java:1593)
[java] ... 5 more
[java] Exception in thread "main"
[java] Java Result: 1



Code:
// 2.
// get the next id
SQLQuery nextQuery = session.createSQLQuery( "SELECT MAX(EventId) as maxPrimayKey FROM SP_Events" );
nextQuery.addScalar( "maxPrimaryKey", Hibernate.INTEGER );
Object result = nextQuery.uniqueResult();
System.out.println( "Next ID > " + result );


RESULT
Quote:
runClass:
[java] >>> USING Hibernate Default Configuration [hibernate.cfg.xml]
[java] >>> USING Hibernate Config [hibernate.cfg.xml]
[java] org.hibernate.exception.SQLGrammarException: could not execute query
[java] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
[java] at org.hibernate.loader.Loader.doList(Loader.java:1596)
[java] at org.hibernate.loader.Loader.list(Loader.java:1577)
[java] at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
[java] at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
[java] at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
[java] at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
[java] at com.lehman.tims.event.CallEvent.main(CallEvent.java:257)
[java] Caused by: java.sql.SQLException: S0022: Invalid column name 'maxPrimaryKey'.
[java] at com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:531)
[java] at com.sybase.jdbc2.tds.TdsResultSet.findColumnByLabel(TdsResultSet.java:535)
[java] at com.sybase.jdbc2.jdbc.SybResultSet.findColumn(SybResultSet.java:538)
[java] at com.sybase.jdbc2.jdbc.SybResultSet.getInt(SybResultSet.java:349)
[java] at org.hibernate.type.IntegerType.get(IntegerType.java:26)
[java] at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
[java] at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:94)
[java] at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:1
33)
[java] at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:329)
[java] at org.hibernate.loader.Loader.doQuery(Loader.java:412)
[java] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:21
8)
[java] at org.hibernate.loader.Loader.doList(Loader.java:1593)
[java] ... 6 more
[java] Exception in thread "main"
[java] Java Result: 1
Top
 Profile  
 
BusyByte
 Post subject: typo in your last post
PostPosted: Wed Feb 15, 2006 8:59 am 
Beginner
Beginner

Joined: Sun Feb 12, 2006 12:19 am
Posts: 20
Shouldn't your

Code:
nextQuery.addEntity( "SpEvents", SpEvents.class );


be

Code:
nextQuery.addEntity( "Sp_Events", SpEvents.class );


Don't know if you just had a typo but I still haven't gotten mine to work either.


Anyone have any ideas?
Top
 Profile  
 
twashing
 Post subject:
PostPosted: Wed Feb 15, 2006 7:35 pm 
Newbie

Joined: Sun May 09, 2004 2:45 pm
Posts: 14
I didn't solve this problem directly. But we got around it by using "IDENTITY" database columns. This is specific to Sybase and a few others, but it gives you the ability to add a record to the database without specifying an id. The database will generate a sequence ID for you and return it as a result. It's supported in hibernate here:
http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-declaration-id-generator


/tim
Top
 Profile  
 
BusyByte
 Post subject: not concerned with inserting a record
PostPosted: Wed Feb 15, 2006 11:16 pm 
Beginner
Beginner

Joined: Sun Feb 12, 2006 12:19 am
Posts: 20
I"m not concerned with inserting a record as much as selecting the last one inserted.

Shawn
Top
 Profile  
 
twashing
 Post subject:
PostPosted: Thu Feb 16, 2006 9:17 pm 
Newbie

Joined: Sun May 09, 2004 2:45 pm
Posts: 14
Hmmm, I don't have an answer then. I too used a custom JDBC call.

/tim
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.