-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 
Author Message
 Post subject: generated SQL runs fine but .get() returns null!
PostPosted: Sat May 07, 2005 6:27 am 
Beginner
Beginner

Joined: Thu May 05, 2005 4:49 pm
Posts: 30
I'm having some query trouble. When I try to get(Doc.class,7), I get a null result back. Setting the log to debug verifies that there wasn't a result set. However when I run the generated SQL manually, I get the right row back. Ideas?

Hibernate version: 3.0.2

Mapping documents:
Code:
<class name="Item" table="items">
     
      <id name="id" type="long" unsaved-value="null">
         <generator class="native" />
      </id>
      <version name="version" />
      <property name="created" type="timestamp" />
     
      <property name="price" type="big_decimal" not-null="true" />
      <property name="salePrice" type="big_decimal" not-null="true" column="sale_price" />
      <property name="onSale" type="boolean" column="on_sale" />
     
      <joined-subclass name="od.domain.Doc" table="document_items">
         <key column="item_id" />
         <property name="name" />
         <property name="active" type="boolean" />
         <property name="abstractText" column="abstract_text" type="text" />
         <set name="categories" cascade="none" table="category_docs">
            <key column="doc_id" />
            <many-to-many column="category_id" class="od.domain.Category" />
         </set>
      </joined-subclass>


Full stack trace of any exception that occurs: get(Doc.class,id) returns null

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

The generated SQL (show_sql=true): select doc0_.item_id as id0_, doc0_1_.version as version1_0_, doc0_1_.created as created1_0_, doc0_1_.price as price1_0_, doc0_1_.sale_price as sale5_1_0_, doc0_1_.on_sale as on6_1_0_, doc0_.name as name3_0_, doc0_.active as active3_0_, doc0_.abstract_text as abstract4_3_0_ from document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id where doc0_.item_id=?

Debug level Hibernate log excerpt:
Code:
2005-05-07 04:09:16,031 DEBUG (DefaultLoadEventListener.java:185) - loading entity: [od.domain.Doc#7]
2005-05-07 04:09:16,031 DEBUG (DefaultLoadEventListener.java:331) - attempting to resolve: [od.domain.Doc#7]
2005-05-07 04:09:16,031 DEBUG (DefaultLoadEventListener.java:367) - object not resolved in any cache: [od.domain.Doc#7]
2005-05-07 04:09:16,031 DEBUG (BasicEntityPersister.java:2457) - Materializing entity: [od.domain.Doc#7]
2005-05-07 04:09:16,031 DEBUG (Loader.java:1302) - loading entity: [od.domain.Doc#7]
2005-05-07 04:09:16,046 DEBUG (AbstractBatcher.java:277) - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2005-05-07 04:09:16,046 DEBUG (AbstractBatcher.java:422) - opening JDBC connection
2005-05-07 04:09:16,046 DEBUG (AbstractBatcher.java:311) - select doc0_.item_id as id0_, doc0_1_.version as version1_0_, doc0_1_.created as created1_0_, doc0_1_.price as price1_0_, doc0_1_.sale_price as sale5_1_0_, doc0_1_.on_sale as on6_1_0_, doc0_.name as name3_0_, doc0_.active as active3_0_, doc0_.abstract_text as abstract4_3_0_ from document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id where doc0_.item_id=?
2005-05-07 04:09:16,046 DEBUG (AbstractBatcher.java:365) - preparing statement
2005-05-07 04:09:16,046 DEBUG (NullableType.java:59) - binding '7' to parameter: 1
2005-05-07 04:09:16,046 DEBUG (AbstractBatcher.java:293) - about to open ResultSet (open ResultSets: 0, globally: 0)
2005-05-07 04:09:16,046 DEBUG (Loader.java:384) - processing result set
2005-05-07 04:09:16,046 DEBUG (Loader.java:408) - done processing result set (0 rows)
2005-05-07 04:09:16,046 DEBUG (AbstractBatcher.java:300) - about to close ResultSet (open ResultSets: 1, globally: 1)
2005-05-07 04:09:16,046 DEBUG (AbstractBatcher.java:285) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2005-05-07 04:09:16,046 DEBUG (AbstractBatcher.java:403) - closing statement
2005-05-07 04:09:16,046 DEBUG (Loader.java:504) - total objects hydrated: 0
2005-05-07 04:09:16,046 DEBUG (PersistenceContext.java:789) - initializing non-lazy collections
2005-05-07 04:09:16,046 DEBUG (Loader.java:1330) - done entity load
2005-05-07 04:09:16,062 DEBUG (SessionImpl.java:254) - closing session
2005-05-07 04:09:16,062 DEBUG (AbstractBatcher.java:437) - closing JDBC connection (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)


Top
 Profile  
 
 Post subject: More details - please help!
PostPosted: Sat May 07, 2005 5:10 pm 
Beginner
Beginner

Joined: Thu May 05, 2005 4:49 pm
Posts: 30
I found out that when I run a query like "from Doc where id = 7", it works. When I run a query like "from Doc where id = ?" and pass 7 to the finder function, it breaks. Here's two log excerpts:

Code:
2005-05-07 15:01:54,968 DEBUG (SessionImpl.java:777) - find: from Doc where id = 7
2005-05-07 15:01:54,968 DEBUG (QueryParameters.java:220) - named parameters: {}
2005-05-07 15:01:54,968 DEBUG (QueryTranslatorImpl.java:207) - parse() - HQL: from od.domain.Doc where id = 7
2005-05-07 15:01:54,968 DEBUG (QueryTranslatorImpl.java:223) - --- HQL AST ---
\-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  \-[FROM] 'from'
    |     \-[RANGE] 'RANGE'
    |        \-[DOT] '.'
    |           +-[DOT] '.'
    |           |  +-[IDENT] 'od'
    |           |  \-[IDENT] 'domain'
    |           \-[IDENT] 'Doc'
    \-[WHERE] 'where'
       \-[EQ] '='
          +-[IDENT] 'id'
          \-[NUM_INT] '7'

2005-05-07 15:01:54,968 DEBUG (ErrorCounter.java:72) - throwQueryException() : no errors
2005-05-07 15:01:54,968 DEBUG (HqlSqlBaseWalker.java:120) - query() << begin, level = 1
2005-05-07 15:01:54,968 DEBUG (FromElement.java:81) - FromClause{level=1} :  od.domain.Doc (no alias) -> doc0_
2005-05-07 15:01:54,968 DEBUG (FromReferenceNode.java:48) - Resolved :  id -> doc0_.item_id
2005-05-07 15:01:54,984 DEBUG (HqlSqlBaseWalker.java:125) - query() : finishing up , level = 1
2005-05-07 15:01:54,984 DEBUG (HqlSqlWalker.java:331) - processQuery() :  ( SELECT ( FromClause{level=1} document_items doc0_ ) ( where ( = doc0_.item_id 7 ) ) )
2005-05-07 15:01:54,984 DEBUG (HqlSqlWalker.java:451) - Derived SELECT clause created.
2005-05-07 15:01:54,984 DEBUG (JoinProcessor.java:112) - Using FROM fragment [document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id]
2005-05-07 15:01:54,984 DEBUG (HqlSqlBaseWalker.java:128) - query() >> end, level = 1
2005-05-07 15:01:54,984 DEBUG (QueryTranslatorImpl.java:193) - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT'  querySpaces (document_items,items)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'doc0_.item_id as id' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=document_items,tableAlias=doc0_,colums={,className=od.domain.Doc}}}
    |  \-[SQL_TOKEN] SqlFragment: 'doc0_1_.version as version1_, doc0_1_.created as created1_, doc0_1_.price as price1_, doc0_1_.sale_price as sale5_1_, doc0_1_.on_sale as on6_1_, doc0_.name as name3_, doc0_.active as active3_, doc0_.abstract_text as abstract4_3_'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[], fromElementByTableAlias=[doc0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=document_items,tableAlias=doc0_,colums={,className=od.domain.Doc}}
    \-[WHERE] SqlNode: 'where'
       \-[EQ] SqlNode: '='
          +-[SQL_TOKEN] IdentNode: 'doc0_.item_id' {originalText=id}
          \-[NUM_INT] LiteralNode: '7'

2005-05-07 15:01:54,984 DEBUG (ErrorCounter.java:72) - throwQueryException() : no errors
2005-05-07 15:01:54,984 DEBUG (QueryTranslatorImpl.java:177) - HQL: from od.domain.Doc where id = 7
2005-05-07 15:01:54,984 DEBUG (QueryTranslatorImpl.java:178) - SQL: select doc0_.item_id as id, doc0_1_.version as version1_, doc0_1_.created as created1_, doc0_1_.price as price1_, doc0_1_.sale_price as sale5_1_, doc0_1_.on_sale as on6_1_, doc0_.name as name3_, doc0_.active as active3_, doc0_.abstract_text as abstract4_3_ from document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id where doc0_.item_id=7
2005-05-07 15:01:55,077 DEBUG (AbstractBatcher.java:277) - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2005-05-07 15:01:55,077 DEBUG (AbstractBatcher.java:311) - select doc0_.item_id as id, doc0_1_.version as version1_, doc0_1_.created as created1_, doc0_1_.price as price1_, doc0_1_.sale_price as sale5_1_, doc0_1_.on_sale as on6_1_, doc0_.name as name3_, doc0_.active as active3_, doc0_.abstract_text as abstract4_3_ from document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id where doc0_.item_id=7
2005-05-07 15:01:55,077 DEBUG (AbstractBatcher.java:365) - preparing statement
2005-05-07 15:01:55,077 DEBUG (AbstractBatcher.java:293) - about to open ResultSet (open ResultSets: 0, globally: 0)
2005-05-07 15:01:55,077 DEBUG (Loader.java:384) - processing result set
2005-05-07 15:01:55,093 DEBUG (Loader.java:389) - result set row: 0
2005-05-07 15:01:55,093 DEBUG (NullableType.java:86) - returning '7' as column: id
2005-05-07 15:01:55,093 DEBUG (Loader.java:791) - result row: EntityKey[od.domain.Doc#7]


versus

Code:
2005-05-07 15:01:55,109 DEBUG (SessionImpl.java:777) - find: from Doc where id = ?
2005-05-07 15:01:55,109 DEBUG (QueryParameters.java:217) - parameters: [7]
2005-05-07 15:01:55,109 DEBUG (QueryParameters.java:220) - named parameters: {}
2005-05-07 15:01:55,109 DEBUG (QueryTranslatorImpl.java:207) - parse() - HQL: from od.domain.Doc where id = ?
2005-05-07 15:01:55,124 DEBUG (QueryTranslatorImpl.java:223) - --- HQL AST ---
\-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  \-[FROM] 'from'
    |     \-[RANGE] 'RANGE'
    |        \-[DOT] '.'
    |           +-[DOT] '.'
    |           |  +-[IDENT] 'od'
    |           |  \-[IDENT] 'domain'
    |           \-[IDENT] 'Doc'
    \-[WHERE] 'where'
       \-[EQ] '='
          +-[IDENT] 'id'
          \-[PARAM] '?'

2005-05-07 15:01:55,124 DEBUG (ErrorCounter.java:72) - throwQueryException() : no errors
2005-05-07 15:01:55,124 DEBUG (HqlSqlBaseWalker.java:120) - query() << begin, level = 1
2005-05-07 15:01:55,124 DEBUG (FromElement.java:81) - FromClause{level=1} :  od.domain.Doc (no alias) -> doc0_
2005-05-07 15:01:55,140 DEBUG (FromReferenceNode.java:48) - Resolved :  id -> doc0_.item_id
2005-05-07 15:01:55,140 DEBUG (HqlSqlBaseWalker.java:125) - query() : finishing up , level = 1
2005-05-07 15:01:55,140 DEBUG (HqlSqlWalker.java:331) - processQuery() :  ( SELECT ( FromClause{level=1} document_items doc0_ ) ( where ( = doc0_.item_id ? ) ) )
2005-05-07 15:01:55,140 DEBUG (HqlSqlWalker.java:451) - Derived SELECT clause created.
2005-05-07 15:01:55,140 DEBUG (JoinProcessor.java:112) - Using FROM fragment [document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id]
2005-05-07 15:01:55,140 DEBUG (HqlSqlBaseWalker.java:128) - query() >> end, level = 1
2005-05-07 15:01:55,140 DEBUG (QueryTranslatorImpl.java:193) - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT'  querySpaces (document_items,items)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'doc0_.item_id as id' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=document_items,tableAlias=doc0_,colums={,className=od.domain.Doc}}}
    |  \-[SQL_TOKEN] SqlFragment: 'doc0_1_.version as version1_, doc0_1_.created as created1_, doc0_1_.price as price1_, doc0_1_.sale_price as sale5_1_, doc0_1_.on_sale as on6_1_, doc0_.name as name3_, doc0_.active as active3_, doc0_.abstract_text as abstract4_3_'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[], fromElementByTableAlias=[doc0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=document_items,tableAlias=doc0_,colums={,className=od.domain.Doc}}
    \-[WHERE] SqlNode: 'where'
       \-[EQ] SqlNode: '='
          +-[SQL_TOKEN] IdentNode: 'doc0_.item_id' {originalText=id}
          \-[PARAM] SqlNode: '?'

2005-05-07 15:01:55,140 DEBUG (ErrorCounter.java:72) - throwQueryException() : no errors
2005-05-07 15:01:55,140 DEBUG (QueryTranslatorImpl.java:177) - HQL: from od.domain.Doc where id = ?
2005-05-07 15:01:55,140 DEBUG (QueryTranslatorImpl.java:178) - SQL: select doc0_.item_id as id, doc0_1_.version as version1_, doc0_1_.created as created1_, doc0_1_.price as price1_, doc0_1_.sale_price as sale5_1_, doc0_1_.on_sale as on6_1_, doc0_.name as name3_, doc0_.active as active3_, doc0_.abstract_text as abstract4_3_ from document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id where doc0_.item_id=?
2005-05-07 15:01:55,140 DEBUG (ErrorCounter.java:72) - throwQueryException() : no errors
2005-05-07 15:01:55,202 DEBUG (AbstractBatcher.java:277) - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2005-05-07 15:01:55,202 DEBUG (AbstractBatcher.java:311) - select doc0_.item_id as id, doc0_1_.version as version1_, doc0_1_.created as created1_, doc0_1_.price as price1_, doc0_1_.sale_price as sale5_1_, doc0_1_.on_sale as on6_1_, doc0_.name as name3_, doc0_.active as active3_, doc0_.abstract_text as abstract4_3_ from document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id where doc0_.item_id=?
2005-05-07 15:01:55,202 DEBUG (AbstractBatcher.java:365) - preparing statement
2005-05-07 15:01:55,202 DEBUG (NullableType.java:59) - binding '7' to parameter: 1
2005-05-07 15:01:55,202 DEBUG (AbstractBatcher.java:293) - about to open ResultSet (open ResultSets: 0, globally: 0)
2005-05-07 15:01:55,202 DEBUG (Loader.java:384) - processing result set
2005-05-07 15:01:55,202 DEBUG (Loader.java:408) - done processing result set (0 rows)
2005-05-07 15:01:55,202 DEBUG (AbstractBatcher.java:300) - about to close ResultSet (open ResultSets: 1, globally: 1)
2005-05-07 15:01:55,202 DEBUG (AbstractBatcher.java:285) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2005-05-07 15:01:55,202 DEBUG (AbstractBatcher.java:403) - closing statement
2005-05-07 15:01:55,202 DEBUG (Loader.java:504) - total objects hydrated: 0


Could this be a problem with my database driver using PreparedStatements or something? I'm using MySQL version 4.1.11-nt and mysql-connector-java-3.1.8-bin.jar


Top
 Profile  
 
 Post subject:
PostPosted: Sat May 07, 2005 6:34 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
I think that your Doc (id=7) haven't items and joined query doesn't work

try map like simple one-to-many (maybe bidirectional) or add any row with doc=7 in items

regards


Top
 Profile  
 
 Post subject: err
PostPosted: Sat May 07, 2005 6:54 pm 
Beginner
Beginner

Joined: Thu May 05, 2005 4:49 pm
Posts: 30
Hey snpesnpe,
Did you see my latest post? When I run "from Doc where id = 7", it works. Based on that, I think my joins are correct.


Top
 Profile  
 
 Post subject:
PostPosted: Sat May 07, 2005 7:05 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
yes, I'm sorry.What is type in database for id ; it is type mismatch in mysql or jdbc driver fault

regrads


Top
 Profile  
 
 Post subject: answer
PostPosted: Sat May 07, 2005 7:17 pm 
Beginner
Beginner

Joined: Thu May 05, 2005 4:49 pm
Posts: 30
Thanks for your ideas, here's what I found out. I took Hibernate and Spring out of the equation and made a small JSP that tests out the driver / DB combo. I think there's a bug in the driver.

My JSP code:
Code:
<%
   Class.forName("com.mysql.jdbc.Driver");
   Connection con = DriverManager.getConnection(
      "jdbc:mysql://localhost/outsourcingdocs?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8",
      "username","password");
   
   PreparedStatement stmt = con.prepareStatement("select doc0_.item_id as id, doc0_1_.version as version1_, " +
      "doc0_1_.created as created1_, doc0_1_.price as price1_, doc0_1_.sale_price as sale5_1_, " +
      "doc0_1_.on_sale as on6_1_, doc0_.name as name3_, doc0_.active as active3_, " +
      "doc0_.abstract_text as abstract4_3_" +
      " from document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id" +
      " where doc0_.item_id = ?");
   stmt.setLong(1,7);
   ResultSet rs = stmt.executeQuery();
   while (rs.next()) {%>
      Doc: <%=rs.getString("id")%> - <%=rs.getString("name3_")%>
<% }

   stmt = con.prepareStatement("select childcateg0_.parent_id as parent5___, childcateg0_.id as id__, " +
   "childcateg0_.id as id0_, childcateg0_.version as version13_0_, childcateg0_.created as created13_0_," +
   " childcateg0_.name as name13_0_, childcateg0_.parent_id as parent5_13_0_ from categories childcateg0_" +
   " where childcateg0_.parent_id = ?");
   stmt.setLong(1,3);
   rs = stmt.executeQuery();
   while (rs.next()) {%>
      Cat: <%=rs.getString("id__")%> - <%=rs.getString("name13_0_")%>
<% }
   stmt.close();
   con.close();
%>


Basically, I'm preparing two statement, each with one parameter. In both statements I set the parameter to a valid ID. The first one returns no rows, the second one returns two results.

If I change the queries to normal queries (by typing in the value of each parameter in the place of the "?"), the first query then returns one row.

I'm using Connector/J 3.1.8a and MySQL 4.1.11-nt-max
I tried the same code using Connector/J 3.2alpha, same results.

My workaround will be to append the following to the connection URL of my datasource:
"&useServerPrepStmts=false"


Top
 Profile  
 
 Post subject:
PostPosted: Sat May 07, 2005 8:07 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
is item_id in table document_items type long ?


Top
 Profile  
 
 Post subject: desc
PostPosted: Sat May 07, 2005 8:09 pm 
Beginner
Beginner

Joined: Thu May 05, 2005 4:49 pm
Posts: 30
Code:
mysql> desc document_items;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| item_id       | bigint(20)   |      | PRI | 0       |       |
| name          | varchar(255) | YES  |     | NULL    |       |
| active        | tinyint(1)   | YES  |     | NULL    |       |
| abstract_text | text         | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc items;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | bigint(20)    |      | PRI | NULL    | auto_increment |
| version    | int(11)       |      |     | 0       |                |
| created    | datetime      | YES  |     | NULL    |                |
| price      | decimal(19,2) | YES  |     | NULL    |                |
| sale_price | decimal(19,2) | YES  |     | NULL    |                |
| on_sale    | tinyint(1)    | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)


Top
 Profile  
 
 Post subject:
PostPosted: Sat May 07, 2005 8:18 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
you can try map to BigInteger, but I think that is driver bug.Is there newer version ?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 

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.