Main Tutorials

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 Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
33 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Mayur Limbachiya
2 years ago

Sir, i am going to create Entity class, my questions is “how can we decide the number of NamedQuery , i mean to say How many NamedQuery require?”

SergioRomero
7 years ago

You can include spring in this example?

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

mahesh
4 years ago
Reply to  SergioRomero

fuck you sergio go to hell…***

Kate
9 years ago

Hi, where do I place XML mapping file?

Artur Yolchyan
8 years ago
Reply to  Kate

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

ch
10 years ago

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
10 years ago

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
10 years ago

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

E
10 years ago

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
10 years ago

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

shiva
10 years ago

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

Mik
11 years ago
 
//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
11 years ago
Reply to  Mik

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

Shyam
5 years ago
Reply to  Jai

I have the same Exception

@NamedQueries({
@NamedQuery(name = "getHolderCompanyDetails", query = "from _HoldingCompany h where h.financeSourceId =:financeSourceId and h.transactionType = :transactionType and h.state = :state")
})
public class _HoldingCompany implements java.io.Serializable {

@Id
@Column(name = "MAP_OID")
private Long oid;

DAO

public class HoldingCompanyDAO extends AbstractDAO {

public HoldingCompanyDAO(SessionFactory sessionFactory) {
super(sessionFactory);
}

public _HoldingCompany getHoldingCompany(String financeSourceId, String state, String transactionType) {
try {
Query query = currentSession().getNamedQuery(“getHolderCompanyDetails”);
query.setParameter(“financeSourceId”, financeSourceId);
query.setParameter(“transactionType”, transactionType);
query.setParameter(“state”, state);
query.setMaxResults(1);
Object holdingCompany = query.uniqueResult();
if (holdingCompany == null) {
return null;
}

Exception ; org.hibernate.MappingException: Named query not known: getHolderCompanyDetails

vishal
11 years ago

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 = "acid", query = "call call_log_by_user_enum_v1(:i_limit,:i_offset)",  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<TopStudent> topStudents1 = new ArrayList<TopStudent>();
		try
		{
	    
			
			Query q = entityManager.createNativeQuery("{call call_log_by_user_enum_v1(?,?)};",TopStudent.class);
						
			List<TopStudent> top = q.setParameter(1,0).setParameter(2, 2).getResultList();


			List<TopStudent> top3= (List<TopStudent>)entityManager.createNamedQuery("acid").setParameter("i_limit",0).setParameter("i_offset", 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
11 years ago

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

Cristian Daniel Ortiz Cuellar
11 years ago

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
11 years ago

Thanks a ton.

Rick Robinson
11 years ago

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

Pablo Gilvan
11 years ago

“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
11 years ago

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”

Dolo
3 years ago
Reply to  Daisies

Hibernate returns an entity object. Whatever fields you have mapped to DB columns will be filled.

winzter143
11 years ago

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

Thanks.

Harshad
12 years ago

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
12 years ago

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
12 years ago

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

tripurari
12 years ago
Reply to  tripurari

my problem is related to @Action and @NameQueries annotations

Sharath
11 years ago
Reply to  tripurari

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
13 years ago

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
13 years ago

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();
}
}

ayz
13 years ago

think there’s something missing

like ACTUALLY EXECUTING THE QUERY!!!!

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