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.
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.
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?”
You can include spring in this example?
It can be done without DAO¿? I just want to use the custom-sql.
fuck you sergio go to hell…***
Hi, where do I place XML mapping file?
Hi, you can just add @Entity and @Table(name = “stock”) and every property map with table columns using annotation @Column(name = “columnNameInStockTable”)
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.
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.
you can find some more details in the below link,”http://javadomain.in/hibernate-select-annotation-example/”
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
what file i have to modify if i use JPA mapping?
This is a really helpful article. Well done sir! Thank you!
//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??
You are putting your “sp_GetAlumnoForLoad” inside the class tag in XML.
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
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
Hi,
hey can you give an example for writing insert and update namedQuery using annotation?
Thanks,
Srishti
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.
Thanks a ton.
This is a really helpful article. Well done sir! Thank you!
“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!
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”
Hibernate returns an entity object. Whatever fields you have mapped to DB columns will be filled.
How can resolve @NamedQuery and @NamedNativeQuery? Sounds like the library not exist. Can someone shared the jar library?
Thanks.
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
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
Just catch the exception or doing a simple empty checking, then return whatever you want 🙂 This is common to handle empty result.
my application related to struts 2.o spring and jpa how can build ant file
my problem is related to @Action and @NameQueries annotations
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…….
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?
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();
}
}
think there’s something missing
like ACTUALLY EXECUTING THE QUERY!!!!
next time you put up a sampe FINISH IT!!!
you can get it via session.getNamedQuery(“findStockByStockCode”);
To practice more examples, visit here https://mkyong.com/tutorials/hibernate-tutorials/