How to use prepared statement in JPA

Question

I am a play framework application Developer.I am using createNativeQuery method in JPA. In this example i want to use prepared statement. Please anyone help me? Here is the code without JPA. I need help to convert it to Prepared statement.

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

Answer 1

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

Answer 2

Brief Summary

You need to use query parameters here, but since you are using a native query, you may be limited in your options compared to with JPQL.

State of World

You may be limited to positional parameters:

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

Hibernate's implementation of JPA supports named parameters:

Native SQL queries support positional as well as named parameters

---

Solution

Hibernate

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

I'll repeat it 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. These can be expressed in two ways — explicitly and implicitly.

Explicit index

Mark the parameter using ?1 (or some other number). This index can be used to uniquely identify that 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 the parameter using just ?. Its index will be based on the sequence of all parameters participating 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

Observe that:

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

---

Additional sources

• How to create a native query with named parameters?