composite primary keys with stored procedures

BLudkiewicz · **Posted:** Wed Oct 04, 2006 5:17 pm

Hibernate version: 3.2CR4 (with Hibernate Annotations 3.2CR2)

Hey all,
I've got a stored procedure I am calling using Annotations. The important parts of the class are below:

Code:

@NamedNativeQuery( name="CountTypes",
                   query="call QueryCount( :types, :state, :zip, :sort, :fromDay, :toDay )",
                   resultSetMapping="QueryCount",
                   hints={ @QueryHint( name="org.hibernate.callable", value="true" ) } )
@SqlResultSetMapping( name="QueryCount",
                      entities=@EntityResult( entityClass=FilteredCounts.class,
                                              fields={ @FieldResult( name="state", column="STATE"    ),
                                                       @FieldResult( name="city",  column="LOCATION" ),
                                                       @FieldResult( name="dm",    column="DM"       ), 
                                                       @FieldResult( name="tv",    column="TV"       ),
                                                       @FieldResult( name="ins",   column="INS"      ),
                                                       @FieldResult( name="sw",    column="SW"       ),
                                                       @FieldResult( name="other", column="OTH"      ),
                                                       @FieldResult( name="total", column="TOTAL"    ) } ) )
@Entity
public class FilteredCounts implements Serializable
{
    @Id
    private String city = null;
    @Id
    private String state = null;
    
    // ...
}

If I run this as is, then the state field will be null for every record retrieved. If I change the order of the two Ids (so state is on top of city), then the city field will be null for every record retrieved. So it seems to me that Hibernate is ignoring the second Id (which is bad for me). If I choose either city or state, then I will get duplicate records returned for rows that have the same city/state, respectively.

If I make an IdClass:

Code:

@Embeddable
public final class FilteredCountsPK implements Serializable
{
         @Column( name="LOCATION" )
         private String city = null;
         @Column( name="STATE" )
         private String state = null;

         // ...
}

and I add '@IdClass( FilteredCountsPK.class )' to FilteredCounts I get:

Code:

13:15:18.186 [DEBUG] AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
13:15:18.201 [INFO ] StringType - could not read column value from result set: LOCATION8_0_; An undefined column name was detected.
13:15:18.201 [DEBUG] AbstractBatcher - about to close ResultSet (open ResultSets: 1, globally: 1)
13:15:18.201 [DEBUG] AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
13:15:18.201 [DEBUG] JDBCExceptionReporter - could not execute query [call QueryCount( ?, ?, ?, ?, ?, ?, ? )]
java.sql.SQLException: An undefined column name was detected.
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:389)
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:366)
   at com.ibm.as400.access.JDServerRow.findField(JDServerRow.java:390)
   at com.ibm.as400.access.AS400JDBCResultSet.findColumn(AS400JDBCResultSet.java:514)
   at com.ibm.as400.access.AS400JDBCResultSet.getString(AS400JDBCResultSet.java:3257)
   at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342)
   at org.hibernate.type.StringType.get(StringType.java:18)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
   at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
   at org.hibernate.type.ComponentType.hydrate(ComponentType.java:560)
   at org.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:275)
   at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:553)
   at org.hibernate.loader.Loader.doQuery(Loader.java:689)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2144)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
   at org.hibernate.loader.Loader.list(Loader.java:2023)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
   at tlc.dw.rp.FilteredCountsRequestProcessor.retrieve(FilteredCountsRequestProcessor.java:58)
   at tlc.dw.rp.RequestProcessor.processStatefulObject(RequestProcessor.java:129)
   at tlc.dw.rp.RequestProcessor.processRequest(RequestProcessor.java:83)
   at tlc.dw.jms.QueueListener.onMessage(QueueListener.java:103)
   at org.apache.activemq.ActiveMQMessageConsumer.dispatch(ActiveMQMessageConsumer.java:795)
   at org.apache.activemq.ActiveMQSessionExecutor.dispatch(ActiveMQSessionExecutor.java:96)
   at org.apache.activemq.ActiveMQSessionExecutor.iterate(ActiveMQSessionExecutor.java:149)
   at org.apache.activemq.thread.PooledTaskRunner.runTask(PooledTaskRunner.java:110)
   at org.apache.activemq.thread.PooledTaskRunner.access$100(PooledTaskRunner.java:25)
   at org.apache.activemq.thread.PooledTaskRunner$1.run(PooledTaskRunner.java:43)
   at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
   at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
   at java.lang.Thread.run(Unknown Source)
13:15:18.201 [WARN ] JDBCExceptionReporter - SQL Error: -99999, SQLState: 42703
13:15:18.201 [ERROR] JDBCExceptionReporter - An undefined column name was detected.
13:15:18.201 [DEBUG] ConnectionManager - aggressively releasing JDBC connection
13:15:18.201 [DEBUG] ConnectionManager - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
13:15:18.201 [ERROR] FilteredCountsRequestProcessor.retrieve() { dpben:2E2371C42EE27075757047B0783043CC:14486929646393 } - While retrieving FilteredCounts
org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:2147)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
   at org.hibernate.loader.Loader.list(Loader.java:2023)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
   at tlc.dw.rp.FilteredCountsRequestProcessor.retrieve(FilteredCountsRequestProcessor.java:58)
   at tlc.dw.rp.RequestProcessor.processStatefulObject(RequestProcessor.java:129)
   at tlc.dw.rp.RequestProcessor.processRequest(RequestProcessor.java:83)
   at tlc.dw.jms.QueueListener.onMessage(QueueListener.java:103)
   at org.apache.activemq.ActiveMQMessageConsumer.dispatch(ActiveMQMessageConsumer.java:795)
   at org.apache.activemq.ActiveMQSessionExecutor.dispatch(ActiveMQSessionExecutor.java:96)
   at org.apache.activemq.ActiveMQSessionExecutor.iterate(ActiveMQSessionExecutor.java:149)
   at org.apache.activemq.thread.PooledTaskRunner.runTask(PooledTaskRunner.java:110)
   at org.apache.activemq.thread.PooledTaskRunner.access$100(PooledTaskRunner.java:25)
   at org.apache.activemq.thread.PooledTaskRunner$1.run(PooledTaskRunner.java:43)
   at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
   at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
   at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: An undefined column name was detected.
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:389)
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:366)
   at com.ibm.as400.access.JDServerRow.findField(JDServerRow.java:390)
   at com.ibm.as400.access.AS400JDBCResultSet.findColumn(AS400JDBCResultSet.java:514)
   at com.ibm.as400.access.AS400JDBCResultSet.getString(AS400JDBCResultSet.java:3257)
   at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342)
   at org.hibernate.type.StringType.get(StringType.java:18)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
   at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
   at org.hibernate.type.ComponentType.hydrate(ComponentType.java:560)
   at org.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:275)
   at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:553)
   at org.hibernate.loader.Loader.doQuery(Loader.java:689)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2144)
   ... 19 more

So Locations isn't being found in the ResultSet anymore...

Here's what the EntityLoader is telling me:

Code:

13:01:27.483 [DEBUG] EntityLoader - Static select for entity tlc.domain.object.mediacount.FilteredCounts: select filteredco0_.LOCATION as LOCATION8_0_, filteredco0_.STATE as STATE8_0_, filteredco0_.dm as dm8_0_, filteredco0_.ins as ins8_0_, filteredco0_.sw as sw8_0_, filteredco0_.tv as tv8_0_, filteredco0_.other as other8_0_, filteredco0_.total as total8_0_ from FilteredCounts filteredco0_ where filteredco0_.LOCATION=? and filteredco0_.STATE=? for read only with rs

Here's how I call the NamedQuery:

Code:

results = session.getNamedQuery( "CountTypes" )
                    .setParameter( "type",      fcl.getType()  )
                    .setParameter( "state",      fcl.getState()      )
                    .setParameter( "zip",        fcl.getZip()        )
                    .setParameter( "sort",       fcl.getSortBy()     )
                    .setParameter( "fromDay",    fcl.getFromDay()    )
                    .setParameter( "toDay",      fcl.getToDay()      )
                    .list();

Can anyone tell me how I am supposed to use a composite primary key from a NamedNativeQuery (stored procedure)?

Thanks,
~B

emmanuel · **Posted:** Mon Oct 09, 2006 4:59 pm

1 is clearly wrong

2. might be a error, can you open a JIRA issue with a running test case (native query, no stored proc)

BLudkiewicz · **Posted:** Tue Oct 10, 2006 12:59 pm

Can you be more specific as far as what you need for a 'running' test case (as to run this you'll need a database table)? Do you want me to give you the source with a 'select *' as the query (or just leave it blank so you can create your own query) along with a class to run everything? Please advise.

Thanx,
~B

emmanuel · **Posted:** Tue Oct 10, 2006 5:58 pm

I can generate the Database from the Hibernate mappings.
A select * form Table is perfect.

I think the easiest way is to check the hibernate test suite, all the infrastructure is ready, sessionFactory or emf are ready to use inside the testcase. That makes the writing faster.

Thanks for the help

BLudkiewicz · **Posted:** Thu Jan 18, 2007 5:40 pm

Sorry I haven't put up a test case yet... I've been really busy... but I did find a 'workaround' of sorts for this problem and I wanted to share it...

It appears the problem isn't with stored procedures and composite keys but with using @SqlResultSetMapping and @IdClass annotations together... let me explain...

I ended up having to write a SQL query and tried to run it a similar way by using:
session.createSQLQuery(query).setResultSetMapping(mapping).list();

This gave me the same error, an unidentified column name was detected (label6_0_)... so I tried to use:

session.createSQLQuery(query).addEntity(Entity.class).list();

and it worked... so I'm curious, is using @IdClass (due to a composite primary key) with @SqlResultSetMapping something that shouldn't be done? If it still looks like it's a bug I'll post it on the JIRA sometime in the next couple of weeks (I'll have some downtime by then)...

~B

BTW - I'm currently using Hibernate 3.2.1 with Annotations 3.2.1.

Here's some code to better illustrate what I'm talking about:

My test class:

Code:

import java.util.List;

import org.hibernate.HibernateException;

import tlc.domain.object.lead.LeadGroup;
import tlc.dw.hibernate.HibernateUtil;

public class TestGrouping
{
   public static void main( String[] args )
   {
      HibernateUtil.init();
      
      Integer fileNumber = 7587;
      String mediaType = "*ALL";
      String[] states   = new String[] { "TN", "VA" };
      String[] counties = new String[ 0 ];
      String[] cities   = new String[ 0 ];
      String[] postals  = new String[ 0 ];
      
      String locationQuery;
      if( states.length != 0 )
         locationQuery = "  AND m.state IN (" +createLocations( states   );
      else if( counties.length != 0 )
         locationQuery = "  AND m.county IN (" +createLocations( counties );
      else if( cities.length != 0 )
         locationQuery = "  AND m.city IN (" +createLocations( cities   );
      else if( postals.length != 0 )
         locationQuery = "  AND SUBSTR(m.postal,1,5) IN (" +createLocations( postals  );
      else
      {
         shutdown();
         
         throw new RuntimeException( "No locations selected to group" );
      }
      
      StringBuilder mediaTypeQuery = new StringBuilder();
      if( !"*ALL".equals( mediaType ) )
      {
         mediaTypeQuery.append( "  AND h.mediatype = " );
         if( "TV".equals( mediaType ) )
            mediaTypeQuery.append( "(CASE WHEN h.market = '' THEN 'CB' ELSE 'BC' END)" );
         else
            mediaTypeQuery.append( "'" ).append( mediaType ).append( "'" );
      }
      
       StringBuilder sql = new StringBuilder();
       sql.append( "SELECT q.label, q.format, q.cost, count(*) as total FROM\n"         );
       sql.append( "(SELECT h.campaign AS label,\n"                                     );
       sql.append( "CASE WHEN h.mediatype IN ('BC', 'CB') THEN 'TV'\n"                  );
       sql.append( "     WHEN h.form <> '' THEN h.form\n"                               );
       sql.append( "     WHEN h.form =  '' THEN h.mediatype\n"                          );
       sql.append( "     ELSE '' END AS format,\n"                                      );
          sql.append( "CASE WHEN h.assignprc > 0 THEN h.assignprc ELSE\n"                  );
          sql.append( "     (SELECT CASE WHEN l.ldldam > 0 THEN l.ldldam ELSE\n"           );
          sql.append( "      DEC(COALESCE(l.ldldcs,0)/COALESCE(l.ldldpr,1),7,2) END\n"     );
          sql.append( "      FROM ldpflds AS l WHERE l.ldpono = h.campaign) END AS cost\n" );
          sql.append( "FROM mktrsphst AS h\n"                                              );
          sql.append( "JOIN mktrspmst AS m ON h.respondrid = m.respondrid\n"               );
          sql.append( "WHERE h.owningfil = " ).append( fileNumber ).append( "\n"           );
          sql.append( "  AND h.lockflag <> 'Y'\n"                                          );
          sql.append( "  AND h.docid <> ' '\n"                                         );
          sql.append( locationQuery ).append( ")\n"                                        );
          sql.append( mediaTypeQuery ).append( ") AS q\n"                                  );
          sql.append( "GROUP BY q.label, q.format, q.cost\n"                               );
          sql.append( "ORDER BY q.label, q.format, q.cost"                                 );          
          
          List< ? > results = null;
        try
       {
          results = HibernateUtil.getSession().createSQLQuery( sql.toString()   )
//                                             .setResultSetMapping( "LeadGroup" )
                                     .addEntity( LeadGroup.class )
                                     .list();
       }
        catch( HibernateException he )
        {
            he.printStackTrace();
            shutdown();
        }
        
        if( results != null)
           for( Object obj: results )
              System.out.println( obj );
        
        shutdown();
   }
   
   private static String createLocations( String[] locations )
   {
      StringBuilder locationString = new StringBuilder();
      for( String location: locations )
         locationString.append( "'" ).append( location ).append( "'," );
      
      return locationString.substring( 0, locationString.length() - 1 );
   }
   
   private static void shutdown()
   {
        HibernateUtil.close();
   }
}

The Entity:

Code:

@Entity
@SqlResultSetMapping( name="LeadGroup",
                      entities=@EntityResult( entityClass=LeadGroup.class,
                                              fields={ @FieldResult( name="label",  column="label"  ),
                                                     @FieldResult( name="format", column="format" ),
                                                     @FieldResult( name="total",  column="total"  ),
                                                     @FieldResult( name="cost",   column="cost"   ) } ) )
@IdClass( LeadGroupPK.class )
public final class LeadGroup extends Lead implements Comparable< LeadGroup >
{
    @Transient
    private final static long serialVersionUID = 200701111059L;
    
    @Id
    private String   label         = "";
    @Id
    private String   format      = "";
    private Integer total         = 0;
    @Id
    private Money   cost         = new Money( 0L );
    @Transient
    private Integer quantity    = 0;
    @Transient
    private Money   price         = new Money( 0L );
    @Transient
    private Integer totalAssigned;
...
}

The Primary Key class:

Code:

@Embeddable
public class LeadGroupPK implements Serializable
{
   private final static long serialVersionUID = 200701181017L;

    private String label;
    private String format;
    private Money  cost;

...
}