Hibernate CreateSQL Query Problem

Question

Hello All

I'm trying to use hibernates built in createsql function but it seems that it doesn't like the following query.

List =hibernateSession.createSQLQuery("SELECT number, location FROM table 
WHERE other_number IN 
(SELECT f.number FROM table2 AS f JOIN table3 AS g on f.number = g.number 
WHERE g.other_number = " + var + ") 
ORDER BY number").addEntity(Table.class).list();

I have a feeling it's from the nested select statement, but I'm not sure. The inner select is used elsewhere in the code and it returns results fine.

This is my mapping for the first table:

<hibernate-mapping>

    <class name="org.efs.openreports.objects.Table" table="table">

        <id name="id" column="other_number" type="java.lang.Integer">
            <generator class="native"/>
        </id>

        <property name="number" column="number" not-null="true" unique="true"/>
        <property name="location" column="location" not-null="true" unique="true"/>

    </class>

</hibernate-mapping>

And the .java

public class Table implements Serializable
{
  private Integer id;//panel_facility
  private Integer number;
  private String location;

  public Table()
  {
  }

  public void setId(Integer id)
  {
    this.id = id;
  }

  public Integer getId()
  {
    return id;
  }

  public void setNumber(Integer number)
  {
    this.number = number;
  }

  public Integer number()
  {
    return number;
  }

  public String location()
  {
    return location;  
  }

  public void setLocation(String location)
  {
      this.location = location;
  }

}

Any suggestions?

Edit (Added mapping)

Answer 1

you should select the id as well in the outer select. (and not use string concatenation in your query)

Use something like that, hibernate will insert the right column names:

List l = hibernateSession.createSQLQuery("SELECT {t.*} FROM table t
WHERE other_number IN 
(SELECT f.number FROM table2 AS f JOIN table3 AS g on f.number = g.number 
WHERE g.other_number = :var) 
ORDER BY number")
.addEntity("t", Table.class).setParameter("var", actualObject).list();