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.

About the Author

author image
mkyong
Founder of Mkyong.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

avatar
26 Comment threads
6 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
27 Comment authors
SergioRomeroArtur YolchyanKatechNikolay Nikolov Recent comment authors
newest oldest most voted
SergioRomero
Guest
SergioRomero

You can include spring in this example?

It can be done without DAO¿? I just want to use the custom-sql.

Kate
Guest
Kate

Hi, where do I place XML mapping file?

Artur Yolchyan
Guest
Artur Yolchyan

Hi, you can just add @Entity and @Table(name = “stock”) and every property map with table columns using annotation @Column(name = “columnNameInStockTable”)

ch
Guest
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.

Nikolay Nikolov
Guest
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.

Diva
Guest
Diva

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

E
Guest
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… Read more »

nelson
Guest
nelson

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

shiva
Guest
shiva

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

trackback
Hibernate : Named Query | PremAseem.com
Mik
Guest
Mik
 
//AlumnoImportacionInf...
@NamedNativeQueries(value = { @NamedNativeQuery(name = "sp_GetAlumnoForLoad", query = "EXEC dbo.sp_getAlumnoForLoad @kclave = :kclave", resultClass = AlumnoImportacionInformacionPersonal.class)})
@Entity
@Table(name = "AlumnoImportacionInformacionPersonal")
public class AlumnoImportacionInformacionPersonal implements Serializable {}

//AlumnoImp..Dao
List<AlumnoImportacionInformacionPersonal> result;
Query query = getSession().getNamedQuery("sp_GetAlumnoForLoad");
query.setParameter("kclave", clave);
result = (List<AlumnoImportacionInformacionPersonal>)query.list();

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

any idea??

Jai
Guest
Jai

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

vishal
Guest
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… Read more »

Srishti
Guest
Srishti

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

Cristian Daniel Ortiz Cuellar
Guest
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
Guest
Steven

Thanks a ton.

Rick Robinson
Guest
Rick Robinson

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

Pablo Gilvan
Guest
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
Guest
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
Guest
winzter143

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

Thanks.

Harshad
Guest
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
Guest
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

tripurari
Guest
tripurari

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

tripurari
Guest
tripurari

my problem is related to @Action and @NameQueries annotations

Sharath
Guest
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
Guest
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
Guest
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… Read more »

trackback
Hibernate Tutorials

[…] Hibernate named query examples Working with named query in XML file and Annotation. […]

ayz
Guest
ayz

think there’s something missing

like ACTUALLY EXECUTING THE QUERY!!!!

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

trackback
Hibernate native SQL queries examples | Hibernate

[…] Alternative, you also can use the named query to call your native SQL statement. See Hibernate named query examples here. […]