Get Hibernate Entity instance from id column in SQLQuery result

I have (non-Hibernated) database tables that contain ids for Hibernate entities. I can query them (using createSQLQuery), which gives me the ids, from which I can then load the entities.

I'd like to do that in one step, and I think I can do that with addEntity, but I am not sure how exactly. (Hibernate's documentation web site is down. Again.) I can use addEntity when all the columns for the entity table are present, but I have only the id now.

This complains about the missing columns:

return (List<MyEntity>) session.createSQLQuery(
            "select entity_id from the_table where foreign_key_value = ?")
            .addEntity("entity_id", MyEntity.class)
            .setLong(0, foreignKey).list();

From stackoverflow

• i would use a join

  select * 
  from entity_table 
  where entity_id = (select entity_id
                     from non_hibernate_table
                     where id = ?)
  

  Thilo : Could do that, but then I would be doing Hibernate's work :-) Also, this would skip lazy-loading and the entity cache.

• For oracle dialect. If u have problem with mapping database column type to java data type u can set it manually like that: .addScalar("integerFieldName", Hibernate.INTEGER)

  public class LookupCodeName
  {
      private String code;
      private String name;
  
   /*... getter-setters ... */
  }
  
  public class someBL {
  
  public List<LookupCodeName> returnSomeEntity() {
        SQLQuery sqlQuery =  (SQLQuery)((HibernateSession)em).getHibernateSession()
         .createSQLQuery( "SELECT st.name as name, st.code as code FROM someTable st")
         .addScalar("code")
         .addScalar("name")
  .setResultTransformer(Transformers.aliasToBean(LookupCodeName.class));
      }
  return (List<LookupCodeName>)sqlQuery.list();
  }
  

• I think you want something like:

  session.createSQLQuery("select {entity.*} from entity_table {entity} where ....")
    .addEntity("entity", Entity.class).(bind-parameters).list();
  

  Hibernate will expand "{entity.*}" to be the relevant columns from entity_table.

  Although if you already have the IDs, you can simply use session.load() to convert those to actual instances (well, lazy-load proxies).