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

Use a parameter `t.shipment_upc= :upc` -> `setParameter("upc", code)` — Paul Samsotha, Apr 11 '14 at 09:48
thank you for your reply. I am a beginner . can you tell me in detail.. Thank in advance. — user3454863, Apr 11 '14 at 09:56

score 21 · Accepted Answer · answered Apr 11 '14 at 09:50

21

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

answered Apr 11 '14 at 09:50

Subir Kumar Sao

8,171
3
26
47

3

Hey @SubirKumarSao, parameters using tags like ":xxx" doesn't work on "createNativeQuery" only on "createQuery", at least on my setup using EclipseLink, are you sure the code above run? If yes, can you please tell me what version of things are you using? thx for collaboration. – Adriano Spadoni Jul 18 '14 at 15:23
1

Its working perfectly for select and update. I would like to know how to do the same for insert – user3454863 Dec 12 '14 at 14:30
@Drix you are right to observe that this is impossible in EclipseLink (at least as of 2.5.1). See [my answer](https://stackoverflow.com/questions/23008926/how-to-use-prepared-statement-in-jpa/38502014#38502014) for a solution compatible with EclipseLink — incorporating positional parameters. – Birchlabs Jul 21 '16 at 10:42
I thought the OP asked for `PreparedStatement.class`, which has the benefit of batched executions? This doesn't really answer the question. – TheRealChx101 Aug 19 '22 at 13:06

score 6 · Answer 2 · edited May 23 '17 at 12:10

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?