Menu
I have a list of IDs in a String, and want to use Hibernate to get the rows with these IDs. TrackedItem is a Hibernate/JPA entity (sorry if I'm getting the naming mixed up here).
My code is:
String idsText = "380, 382, 386"; ArrayList<Long> ids = new ArrayList<Long>(); for (String i : idsText.split(",")) { ids.add(Long.getLong(i)); } List<TrackedItem> items = TrackedItem.find("id IN (?)", ids).fetch(); But that fails: JPAQueryException occured : Error while executing query from models.TrackedItem where id IN (?): java.util.ArrayList cannot be cast to java.lang.Long
How can I make the IN part work? Thanks.
989
You can use native SQL to express database queries if you want to utilize database-specific features such as query hints or the CONNECT keyword in Oracle. Hibernate 3. x allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.
Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties. HQL queries are translated by Hibernate into conventional SQL queries, which in turns perform action on database.
For Hibernate Native SQL Query, we use Session. createSQLQuery(String query) to create the SQLQuery object and execute it. For example, if you want to read all the records from Employee table, we can do it through below code. When we execute above code for the data setup we have, it produces following output.
beginTransaction(); String select = "FROM Employee e INNER JOIN Team t ON e. Id_team=t. Id_team"; Query query = session. createQuery(select); List elist = query.
The syntax of your JPQL query is incorrect. Either use (with a positional parameter):
List<Long> ids = Arrays.asList(380L, 382L, 386L); Query query = em.createQuery("FROM TrackedItem item WHERE item.id IN (?1)"); query.setParameterList(1, ids) List<TrackedItem> items = query.getResultList(); Or (with a named parameter):
List<Long> ids = Arrays.asList(380L, 382L, 386L); Query query = em.createQuery("FROM TrackedItem item WHERE item.id IN :ids"); query.setParameterList("ids", ids) List<TrackedItem> items = query.getResultList(); Below, the relevant sections of the JPA 1.0 specification about parameters:
4.6.4.1 Positional Parameters
The following rules apply to positional parameters.
- Input parameters are designated by the question mark (?) prefix followed by an integer. For example:
?1.- Input parameters are numbered starting from 1.
Note that the same parameter can be used more than once in the query string and that the ordering of the use of parameters within the query string need not conform to the order of the positional parameters.4.6.4.2 Named Parameters
A named parameter is an identifier that is prefixed by the ":" symbol. It follows the rules for identifiers defined in Section 4.4.1. Named parameters are case sensitive.
Example:
SELECT c FROM Customer c WHERE c.status = :statSection 3.6.1 describes the API for the binding of named query parameters
185
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
