Hibernate named query examples

Often times, developer like to put HQL string literals scatter all over the Java code, this method is hard to maintaine and look ugly. Fortunately, Hibernate come out a technique called “names queries” , it lets developer to put all HQL into the XML mapping file or via annotation.

How to declare named query

The named query is supported in both HQL or native SQL. see examples…

1. XML mapping file

HQL in mapping file

<!-- stock.hbm.xml -->
<hibernate-mapping>
    <class name="com.mkyong.common.Stock" table="stock" ...>
        <id name="stockId" type="java.lang.Integer">
            <column name="STOCK_ID" />
            <generator class="identity" />
        </id>
        <property name="stockCode" type="string">
            <column name="STOCK_CODE" length="10" not-null="true" unique="true" />
        </property>
        ...
    </class>
 
    <query name="findStockByStockCode">
        <![CDATA[from Stock s where s.stockCode = :stockCode]]>
    </query>
 
</hibernate-mapping>

Native SQL in mapping file

<!-- stock.hbm.xml -->
<hibernate-mapping>
    <class name="com.mkyong.common.Stock" table="stock" ...>
        <id name="stockId" type="java.lang.Integer">
            <column name="STOCK_ID" />
            <generator class="identity" />
        </id>
        <property name="stockCode" type="string">
            <column name="STOCK_CODE" length="10" not-null="true" unique="true" />
        </property>
        ...
    </class>
 
    <sql-query name="findStockByStockCodeNativeSQL">
	<return alias="stock" class="com.mkyong.common.Stock"/>
	<![CDATA[select * from stock s where s.stock_code = :stockCode]]>
    </sql-query>
</hibernate-mapping>

You can place a named query inside ‘hibernate-mapping‘ element, but do not put before the ‘class‘ element, Hibernate will prompt invalid mapping file, all your named queries have to put after the ‘class‘ element.

Note
Regarding the CDATA , it’s always good practice to wrap your query text with CDATA, so that the XML parser will not prompt error for some special XML characters like ‘>’ , <‘ and etc.

2. Annotation

HQL in annotation

@NamedQueries({
	@NamedQuery(
	name = "findStockByStockCode",
	query = "from Stock s where s.stockCode = :stockCode"
	)
})
@Entity
@Table(name = "stock", catalog = "mkyong")
public class Stock implements java.io.Serializable {
...

Native SQL in annotation

@NamedNativeQueries({
	@NamedNativeQuery(
	name = "findStockByStockCodeNativeSQL",
	query = "select * from stock s where s.stock_code = :stockCode",
        resultClass = Stock.class
	)
})
@Entity
@Table(name = "stock", catalog = "mkyong")
public class Stock implements java.io.Serializable {
...

In native SQL, you have to declare the ‘resultClass‘ to let Hibernate know what is the return type, failed to do it will caused the exception “org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported“.

Call a named query

In Hibernate, you can call the named query via getNamedQuery method.

Query query = session.getNamedQuery("findStockByStockCode")
.setString("stockCode", "7277");
Query query = session.getNamedQuery("findStockByStockCodeNativeSQL")
.setString("stockCode", "7277");

Conclusion

Named queries are global access, which means the name of a query have to be unique in XML mapping files or annotations. In real environment, it’s always good practice to isolate all the named queries into their own file. In addition, named queries stored in the Hibernate mapping files or annotation are more easier to maintain than queries scattered through the Java code.

Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

  • Pingback: Blue Coaster33()

  • ch

    Do you know if there are known issues with Hibernate 4.2.3 to support named queries?
    Here is a brief summary of issue i am facing
    – I want to upgrade Hibernate 4.1.3 to 4.2.3.
    – I am using ojdbc6-11.2.0.3.0.jar
    – org.hibernate.Query list() [Named query execution to return ref cursor]
    – This method works fine with old version of hibernate and gives following error with 4.2.3
    [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 17166, SQLState: 99999
    [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Cannot perform fetch on a PLSQL statement: next

    Appreciate if you or anyone in this forum can help in this regard.

  • http://feraldeveloper.blogspot.com/2013/09/storing-jpql-queries-separately-from.html Nikolay Nikolov

    Thank you – very informative post. Using some of the advice in this post I am providing an alternative way to define the named queries (namely, separate from the code of the JPA2 entities) – just click on the link contained with my name to see the explanation.

  • http://javadomain.in Diva

    you can find some more details in the below link,”http://javadomain.in/hibernate-select-annotation-example/”

  • E

    Hi mkyong,

    I have read many of your articles and have to say they are some of the better ones out there re Spring, Hibernate and Java – well done

    Was hoping I could ask another question here while I’m at it which no one seems to be able to answer for me..

    I have a server side process that uses HQL to do an insert in one transaction. After this transaction (outside @Transaction) it then does a native sql read (for various reasons). The SQL native read is still done through the hibernate architecture.

    Im noticing that sometimes the native read is not finding what it is supposed to and wondering if this is because the transaction has not flushed yet – does it even need to flush ? I thought that the read should always work no matter what as long as it was in or after the transaction .

    Or maybe the read must be in HQL to be sure ?

    hope you can help

  • nelson

    what file i have to modify if i use JPA mapping?

  • shiva

    This is a really helpful article. Well done sir! Thank you!

  • Pingback: Hibernate : Named Query | PremAseem.com()

  • Mik
    //AlumnoImportacionInf...
    @NamedNativeQueries(value = { @NamedNativeQuery(name = &quot;sp_GetAlumnoForLoad&quot;, query = &quot;EXEC dbo.sp_getAlumnoForLoad @kclave = :kclave&quot;, resultClass = AlumnoImportacionInformacionPersonal.class)})
    @Entity
    @Table(name = &quot;AlumnoImportacionInformacionPersonal&quot;)
    public class AlumnoImportacionInformacionPersonal implements Serializable {}
     
    //AlumnoImp..Dao
    List&lt;AlumnoImportacionInformacionPersonal&gt; result;
    Query query = getSession().getNamedQuery(&quot;sp_GetAlumnoForLoad&quot;);
    query.setParameter(&quot;kclave&quot;, clave);
    result = (List&lt;AlumnoImportacionInformacionPersonal&gt;)query.list();

    //Struts error message
    Named query not known: sp_GetAlumnoForLoad
    org.hibernate.MappingException: Named query not known: sp_GetAlumnoForLoad

    any idea??

    • Jai

      You are putting your “sp_GetAlumnoForLoad” inside the class tag in XML.

  • vishal

    Hello,

    Do you have examples of how to call mysql stored procedures?

    I was just trying to learn something i didn’t know. So i coded a small example to test.

    @javax.persistence.Entity
    public class Top implements Serializable{	
    	private static final long serialVersionUID = 1L;
    	private Integer id;
    	private String name;
    	private float percent;
     
    	public void setId(Integer id) {
    		this.id = id;
    	}
     
    	@Id
    	public Integer getId() {
    		return id;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setPercent(float percent) {
    		this.percent = percent;
    	}
    	public float getPercent() {
    		return percent;
    	}
     
    }

    @Entity
    @NamedNativeQuery(name = &quot;acid&quot;, query = &quot;call call_log_by_user_enum_v1(:i_limit,:i_offset)&quot;,  resultClass = TopStudent.class )
    public class TopStudent extends Top {
    	private static final long serialVersionUID = 1L;
    	private Integer numberOfTests;
     
    	public void setNumberOfTests(Integer numberOfTests) {
    		this.numberOfTests = numberOfTests;
    	}
     
     
    	public Integer getNumberOfTests() {
    		return numberOfTests;
    	}
    }

    — manager
    I call the stored procedure in my manager class (does not work)

    @Transactional
    	public void populateTop()
    	{
    		List&lt;TopStudent&gt; topStudents1 = new ArrayList&lt;TopStudent&gt;();
    		try
    		{
     
     
    			Query q = entityManager.createNativeQuery(&quot;{call call_log_by_user_enum_v1(?,?)};&quot;,TopStudent.class);
     
    			List&lt;TopStudent&gt; top = q.setParameter(1,0).setParameter(2, 2).getResultList();
     
     
    			List&lt;TopStudent&gt; top3= (List&lt;TopStudent&gt;)entityManager.createNamedQuery(&quot;acid&quot;).setParameter(&quot;i_limit&quot;,0).setParameter(&quot;i_offset&quot;, 3)     	
    			.getResultList();
    			TopStudent topStudent;
    			for (Iterator iterator = top3.iterator(); iterator.hasNext();) {
    				topStudent = (TopStudent) iterator.next();
    				topStudents1.add(topStudent);
    				}	        		        
     
    		}
    		catch(Exception e)
    		{
    			log.error(e);
    		}		
    	}

    The strange thing that i saw was that:
    1. if i only have 1 call to the namednativequery, it works.
    2. if i have more than 1 query in my method i get a transaction isolation error:
    3. if i have multiple native queries instead of stored procedure, i got no error.
    [JDBCExceptionReporter] SQL Error: 1568, SQLState: 25001
    [JDBCExceptionReporter] Transaction isolation level can’t be changed while a transaction is in progress
    javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query

    Have you ever had this situation?

    i use jboss, seam, jpa, mysql

    regard,
    vishal

  • Srishti

    Hi,
    hey can you give an example for writing insert and update namedQuery using annotation?
    Thanks,
    Srishti

  • Cristian Daniel Ortiz Cuellar

    Hi MKYONG. “it’s always good practice to isolate all the named queries into their own file”. how can i set all the NamedQueries of my APP into a Single FILE. thanks a lot.

  • Steven

    Thanks a ton.

  • Rick Robinson

    This is a really helpful article. Well done sir! Thank you!

  • Pablo Gilvan

    “In native SQL, you have to declare the ‘resultClass‘ to let Hibernate know what is the return type, failed to do it will caused the exception “org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported”.”

    Ty, you saved me!

  • Daisies

    Hi,

    In your query, you use “select *”. How can we use “select column1, column2 from …”
    I don’t want to load all columns. Hibernate returns me error “column not found”

  • winzter143

    How can resolve @NamedQuery and @NamedNativeQuery? Sounds like the library not exist. Can someone shared the jar library?

    Thanks.

  • Harshad

    org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [SELECT nextval(‘mbmcproperty.property_code_seq’) as nextval]; SQL state [25006]; error code [0]; ERROR: cannot execute nextval() in a read-only transaction; nested exception is org.postgresql.util.PSQLException: ERROR: cannot execute nextval() in a read-only transaction

  • roro

    Hi,

    could you give us a way to face the NoResultException in case the result of the query is empty ?

    Is there a good practice to have ?

    Thanks

    • http://www.mkyong.com mkyong

      Just catch the exception or doing a simple empty checking, then return whatever you want :) This is common to handle empty result.

  • tripurari

    my application related to struts 2.o spring and jpa how can build ant file

    • tripurari

      my problem is related to @Action and @NameQueries annotations

      • http://www.gmail.com Sharath

        Hi, i am trying to integrate the Postgres SQL function with Hibernate but i am getting some exception can u just provide me one good example to integrate with Functions and Hibernate…….

  • Markus

    What would be the best practice for this case:

    I have an Entity names Entity1 and i also want to select a sum() in the query.

    select Entity1, sum(anything needed) where ….

    I do have to declare a result class so my question is:
    How would i handle such a case? Create a class specially for this case?

  • murthy

    Thanks for the great tutorials.

    Is there anyway I can turn off the update query that goes with select query? To explain, let us say I want to select rows from a table. I use the spring+hibernate so I do getHibernateTemplate().find(“from User where id=?”, new Long(1)). Now, before after I run this query I want to save a User object. It is here in between that Hibernate tries to do an update in the table for the object being saved whose id is not yet created or written to the DB.

    Here are the code samples:

    In User.hbm.xml

    public void save(UploadItem ui){
    findByFileSeqNum();
    getHibernateTemplate().save(ui);
    }

    @Transactional(readOnly=true)
    public void findByFileSeqNum(){

    List cats = getHibernateTemplate().findByNamedQuery(“findUserIdNativeSQL”, new java.lang.Long(1));
    Iterator it = cats.iterator();
    while(it.hasNext()) {
    UploadItem ui2 = (UploadItem)it.next();
    }
    }

  • Pingback: Hibernate Tutorials()

  • ayz

    think there’s something missing

    like ACTUALLY EXECUTING THE QUERY!!!!

    next time you put up a sampe FINISH IT!!!

  • Pingback: Hibernate native SQL queries examples | Hibernate()