Geek Answers Handbook

How to use prepared expression in JPA

I am a developer of a platform for games. I am using the createNativeQuery method in JPA. In this example, I want to use a prepared statement. Please help me? Here is the code without JPA. I need help to convert it to a trained operator.

Query query = JPA.em().createNativeQuery("select count(*) from truck t inner join" + "box b where t.truck_id=b.truck_id and t.shipment_upc='" + code + "'"); BigInteger val = (BigInteger)query.getSingleResult(); System.out.println(val);
+6
source share
2 answers
 Query query = JPA.em().createNativeQuery("select count(*) from truck t inner join box b where t.truck_id=b.truck_id and t.shipment_upc=:code"); query.setParameter("code", code);
+12
source

Brief Summary

Here you need to use query parameters, but since you are using your own query, you can limit your parameters compared to JPQL.

State of the world

Perhaps you are limited by positional parameters :

JPA does not require native query support with named parameters, but some JPA providers may

JPA sleep mode support named parameters :

Native SQL queries support positional as well as named parameters

Decision

Hibernate

Subir Kumar Sao's answer shows how to solve this using named parameters. This is possible, at least in Hibernate.

I will repeat this here for the sake of comparison:

 Query query = JPA.em().createNativeQuery( "SELECT COUNT(*) "+ "FROM truck AS t "+ "INNER JOIN box b "+ "WHERE t.truck_id = b.truck_id "+ "AND t.shipment_upc = :code" ); query.setParameter("code", code);

Generic JPA (including EclipseLink)

I found that with EclipseLink (2.5.1) named parameters were not supported.

Instead, it becomes necessary to use positional parameters. They can be expressed in two ways - explicitly and implicitly.

Explicit Index

Mark the parameter with ?1 (or another number). This index can be used to uniquely identify this particular parameter in your query.

 Query query = JPA.em().createNativeQuery( "SELECT COUNT(*) "+ "FROM truck AS t "+ "INNER JOIN box b "+ "WHERE t.truck_id = b.truck_id "+ "AND t.shipment_upc = ?1" ); query.setParameter(1, code);

Implicit Index

Mark option using only ? . Its index will be based on the sequence of all parameters involved in your query string.

 Query query = JPA.em().createNativeQuery( "SELECT COUNT(*) "+ "FROM truck AS t "+ "INNER JOIN box b "+ "WHERE t.truck_id = b.truck_id "+ "AND t.shipment_upc = ?" ); query.setParameter(1, code);

Notes

Notice, that:

  • Positional parameters are 1-indexed.
  • The key in the Query parameter map is simply the index of the positional parameter.

Additional sources

+3
source

More articles:


All Articles