Databases
StackOverflow2013
Postsdbo
POHibernate 4, JPA 2 and HSQL, not able to query by ID
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHibernate 4, JPA 2 and HSQL, not able to query by ID
    primarykey
    data
    text
    <p>I have a domain object called Service that contains an id field amongst others:</p> <pre><code>@Id @GeneratedValue(strategy = GenerationType.SEQUENCE) @Column(name = "id", nullable = false) private Integer id; @Column(name = "type") private Service.ServiceTypes serviceType; </code></pre> <p>In my DAO implementation, I can easily query by serviceType:</p> <pre><code>@Override public Set&lt;Service&gt; queryDatabaseByServiceType(ServiceTypes serviceType) { logger.debug("Querying the database by service type " + serviceType.toString()); return new HashSet&lt;Service&gt;(super.entityManager.createQuery("from Service s where s.type = :serviceType").setParameter("serviceType", serviceType).getResultList()); } </code></pre> <p>When I copy and past that method and try to run the query against "id", I get zero results.</p> <pre><code>@Override public Service queryDatabaseByServiceId(int id) { List&lt;Service&gt; results = super.entityManager.createQuery("from Service s where s.id = :id").setParameter("id", id).getResultList(); return results.get(0); </code></pre> <p>always returns nothing.... I've tried it a million different ways...</p> <pre><code>CriteriaBuilder builder = super.entityManager.getCriteriaBuilder(); CriteriaQuery&lt;Service&gt; criteria = builder.createQuery(Service.class); Root&lt;Service&gt; s1 = criteria.from(Service.class); TypedQuery&lt;Service&gt; query = super.entityManager.createQuery( criteria.select(s1).where(builder.equal(s1.get("id"), id))); Service service = query.getSingleResult(); return service; </code></pre> <p>Still nothing. I'm not sure why I can query against other columns but not the ID. I've verified the IDs I'm passing in are valid, nothing ever works.</p> <p>Any ideas?</p> <p>EDIT: Here are other ways I've tried the query:</p> <pre><code>Service ser = super.entityManager.find(Service.class, new Integer(1)); List z1 = super.entityManager.createQuery("select s.id from Service s").getResultList(); List results1 = super.entityManager.createQuery("SELECT s FROM Service s WHERE id = 1").getResultList(); List&lt;Service&gt; results = super.entityManager.createQuery("from Service as s where s.id = :id").setParameter("id", new Integer(1)).getResultList(); </code></pre> <p>I've tried changing the field in my domain object to "idx" instead of "id", I've tried changing the column name to "service_id" thinking maybe there's some internal conflict between what it uses for the id field and my object, but still nothing. As noted before and in the comments, querying by any other field works fine and I can easily get results showing the IDs:</p> <pre><code>return new HashSet&lt;Service&gt;(super.entityManager.createQuery("from Service s where s.type = :serviceType").setParameter("serviceType", serviceType).getResultList()); </code></pre> <p>produces a populated list and when converted to json i see this in my browser:</p> <pre><code>{"services":[{"id":2,"serviceType":"x",..... </code></pre> <p>With all those various types of queries, here is the log output:</p> <pre><code>DEBUG: service.impl.ServiceServiceImpl - Querying the database by service id 2 Hibernate: select service0_.service_id as service1_0_, service0_.description as descript2_0_, service0_.name as name0_, service0_.type as type0_, service0_.tmodel as tmodel0_, service0_.uddi_id as uddi6_0_, service0_.url as url0_, service0_.version as version0_ from services service0_ where service0_.service_id=? TRACE: org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [INTEGER] - 1 INFO : service.impl.ServiceServiceImpl - results list from id query: 0 Hibernate: select service0_.service_id as service1_0_0_, service0_.description as descript2_0_0_, service0_.name as name0_0_, service0_.type as type0_0_, service0_.tmodel as tmodel0_0_, service0_.uddi_id as uddi6_0_0_, service0_.url as url0_0_, service0_.version as version0_0_ from services service0_ where service0_.service_id=? TRACE: org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [INTEGER] - 1 Hibernate: select service0_.service_id as col_0_0_ from services service0_ INFO : service.impl.ServiceServiceImpl - z1 count: 0 Hibernate: select service0_.service_id as service1_0_, service0_.description as descript2_0_, service0_.name as name0_, service0_.type as type0_, service0_.tmodel as tmodel0_, service0_.uddi_id as uddi6_0_, service0_.url as url0_, service0_.version as version0_ from services service0_ where service0_.service_id=1 INFO : service.impl.ServiceServiceImpl - results1 from id query: 0 Hibernate: select service0_.service_id as service1_0_, service0_.description as descript2_0_, service0_.name as name0_, service0_.type as type0_, service0_.tmodel as tmodel0_, service0_.uddi_id as uddi6_0_, service0_.url as url0_, service0_.version as version0_ from services service0_ where service0_.service_id=2 INFO : service.impl.ServiceServiceImpl - results2 from id query: 0 Hibernate: select service0_.service_id as service1_0_0_, service0_.description as descript2_0_0_, service0_.name as name0_0_, service0_.type as type0_0_, service0_.tmodel as tmodel0_0_, service0_.uddi_id as uddi6_0_0_, service0_.url as url0_0_, service0_.version as version0_0_ from services service0_ where service0_.service_id=? TRACE: org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [INTEGER] - 2 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USfredt
    1. USBal
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHibernate 4, JPA 2 and HSQL, not able to query by ID
      1. USBoris Treukhov
      1. VTFavorite
    1. CONo idea, but why don't you simply use `Service s = em.find(Service.class, id);`?
      singulars
      1. POHibernate 4, JPA 2 and HSQL, not able to query by ID
      1. USJB Nizet
    2. COI did try that -- also no results... it's like it's storing the ID differently than expected
      singulars
      1. POHibernate 4, JPA 2 and HSQL, not able to query by ID
      1. USBal
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload