CODEWITHSUNDEEP
hibernatehql
palayan
palayan

Reputation: 59

I want to understand the difference between running a native query and a named query in a loop in Hibernate

can anyone help me undersatnd the difference between running a native query and a named query in a loop in Hibernate. I searched many forums but didn't get a clear view.

The problem I faced in my code is, there were two queries one is named native and one is named query. So when I iterated the queries inside a loop in hibernate, incase of the named native query I got the entity with the same values as a result time and again but in case of the named query I got different entities with the different values. Why this is happening?

the database I used is SQL server 2008, wrote the query in orm.xml.

Lets say this is my service class

@Autowired
@Qualifier("empDAO")
private EmpDAO empdao;

for(String empName : empNameList){

empEntity = empDao.getNativeResult(empName)

sysout(empEntity.getName())
sysout(empEntity.getAge())

empEntity = empDao.getNamedResult(empName)

sysout(empEntity.getName())
sysout(empEntity.getAge())

}

This My orm.xml

http://java.sun.com/xml/ns/persistence/orm_1_0.xsd">

<named-query name="getEmpNamedQuery">
    <query><![CDATA[
            select EMP      
                from 
    EmployeeEntity EMP           
              WHERE EMP.name LIKE CONCAT('%',CONCAT(:userId,'%'))

    ]]></query>
</named-query>


<named-native-query name="retrieveNativeretrieveEmployeeData"
    result-set-mapping="retrieveEmployeeDataMapping">
    <query><![CDATA[
            SELECT 
                ROW_NUMBER() OVER (ORDER BY EMP.EMP_ID) RowNumber,
                CURR.CURR_NAME as CurrencyCode,
                prd.PRD_TYPE_SUBTYPE_NAME as ProductName,
                    FROM
                    Employee_Table EMP
                    WHERE EMP.EMP_NAME LIKE ('%'+(:userId+'%'))
                    group by EMP.EMP_ID
     ]]></query>
</named-native-query>

<sql-result-set-mapping name="retrieveEmployeeDataMapping">
    <entity-result
        entity-class="myProject.persistance.entity.EmployeeNativeEntity">
        <field-result name="empName" column="EmployeeName" />
        <field-result name="rowNumber" column="RowNumber" />
        <field-result name="empAge" column="EmployeeAge" />
    </entity-result>
</sql-result-set-mapping>

My DAO Class

import javax.persistence.Query; import org.springframework.stereotype.Repository;

import com.metlife.epooling.persistent.entity.ClientMstEntity;

@Repository("empDAO") public class EmpDAO{

public EmployeeEntity getNamedResult(String name){
    Query query = entityManager.createNamedQuery("getEmpNamedQuery");
    query.setParameter("userId", name);
    return (EmployeeEntity)query.getSingleResult();

}

public EmployeeNativeEntity getNativeResult(String name){
        Query query = entityManager.createNamedQuery("getEmpNamedQuery");
        query.setParameter("userId", name);
        return (EmployeeNativeEntity)query.getSingleResult();

}

}

The entity EmployeeEntity consist three columns empId, empName, empAge

the second entity EmployeeNativeEntity consist three columns rownumber,empAge,empName

Upvotes: 0

Views: 1078

Answers (1)

Alex
Alex

Reputation: 11579

In the namedquery you get list of records but in the namednativequery you get ROW_NUMBER() (how many records) grouped by id. No wonder that it will be different result.

Upvotes: 0

Related Questions