HQL returns no results but generated SQL works perfectly on SQL Developer

Question

So I have this query

private static final String GET_LOCK_HISTORY1 = "select lh from UserLockHistoryEntity lh "; 

which is executed using this code

List<UserLockHistoryEntity> result = getQuery(GET_LOCK_HISTORY1).list();

This returns an empty array. On the console I get the generated SQL which is

   select
        userlockhi0_.USER_LOCK_HISTORY_ID as USER_LOC1_15_,
        userlockhi0_.LOCK_TYPE as LOCK_TYP2_15_,
        userlockhi0_.TIMESTAMP as TIMESTAM3_15_,
        userlockhi0_.LOCKED_USER_ID as LOCKED_U4_15_,
        userlockhi0_.COMPANY_ID as COMPANY_5_15_,
        userlockhi0_.PARTNER_ID as PARTNER_6_15_,
        userlockhi0_.IP_ORIGIN as IP_ORIGI7_15_,
        userlockhi0_.LOCKED_BY_USER as LOCKED_B8_15_,
        userlockhi0_.LOCKED_BY_TYPE as LOCKED_B9_15_,
        userlockhi0_.LOCK_REASON as LOCK_RE10_15_ 
    from
        ONCPRTDEV.USER_LOCK_HISTORY userlockhi0_

and I paste it on SQL developer which returns me 8 rows (all table records)

Bellow it's my mapping file

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jan 22, 2014 3:07:58 PM by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="pt.vdf.onc.core.business.entity.user.UserLockHistoryEntity" table="USER_LOCK_HISTORY">
        <id name="id" type="java.lang.Long">
            <column name="USER_LOCK_HISTORY_ID" />
            <generator class="assigned" />
        </id>
        <property name="lockType">
            <column name="LOCK_TYPE" />
            <type name="org.hibernate.type.EnumType">
                <param name="enumClass">pt.vdf.onc.core.common.type.user.UserLockType</param>
            </type>
        </property>
        <property name="timestamp" type="java.lang.String">
            <column name="TIMESTAMP" />
        </property>
        <property name="userId" type="java.lang.Long">
            <column name="LOCKED_USER_ID" />
        </property>
        <property name="companyId" type="java.lang.Long">
            <column name="COMPANY_ID" />
        </property>
        <property name="partnerId" type="java.lang.Long">
            <column name="PARTNER_ID" />
        </property>
        <property name="ipOrigin" type="java.lang.String">
            <column name="IP_ORIGIN" />
        </property>
        <property name="lockedByUser" type="java.lang.Long">
            <column name="LOCKED_BY_USER" />
        </property>

        <property name="lockedByType">
            <column name="LOCKED_BY_TYPE" />
            <type name="org.hibernate.type.EnumType">
                <param name="enumClass">pt.vdf.onc.core.common.type.user.UserLockByType</param>
            </type>
        </property>
        <property name="lockedReason">
            <column name="LOCK_REASON" />
            <type name="org.hibernate.type.EnumType">
                <param name="enumClass">pt.vdf.onc.core.common.type.user.UserLockReason</param>
            </type>
        </property>
    </class>
</hibernate-mapping>

and entity

public class UserLockHistoryEntity implements java.io.Serializable {


    private Long id;
    private UserLockType lockType;
    private String timestamp;
    private Long userId;
    private Long companyId;
    private Long partnerId;
    private String ipOrigin;
    private Long lockedByUser;
    private UserLockByType lockedByType;
    private UserLockReason lockedReason;

    //getters and setters here (removed them for simplicity)

}

Table definition:

USER_LOCK_HISTORY_ID    NUMBER(38,0)
LOCK_TYPE   NUMBER(38,0)
TIMESTAMP   DATE
LOCKED_USER_ID  NUMBER(38,0)
COMPANY_ID  NUMBER(38,0)
PARTNER_ID  NUMBER(38,0)
IP_ORIGIN   VARCHAR2(30 BYTE)
LOCKED_BY_USER  VARCHAR2(50 BYTE)
LOCK_REASON NUMBER(38,0)
LOCKED_BY_TYPE  NUMBER

And some rows as an example:

809 0   14.01.22    5003953 1003739 0   127.0.0.1   5003953 2   0
810 0   14.01.22    5003953 1003739 0   127.0.0.1   5003953 2   0
811 0   14.01.22    2054497 621936  0   127.0.0.1   2054497 2   0

Why am I getting an empty result List when the query works perfectly on SQL Developer? Thanks for your help

Answer 1

How is your pojo annotated? Is the id field annotated as an id?

public class UserLockHistoryEntity implements java.io.Serializable {

    private Long id;
    ...

    @Id
    @Column(name = "USER_LOCK_HISTORY_ID", unique = true, nullable = false)
    public Long getId() {
       return this.id;
    }
    ...
}

Answer 2

For

List<UserLockHistoryEntity> result = getQuery(GET_LOCK_HISTORY1).list();

The valid query is

select lh from UserLockHistoryEntity lh

For query

select lh.id from UserLockHistoryEntity lh

The valid result is:

List<Long> result = getQuery(GET_LOCK_HISTORY1).list();