JPA supports two ways of expressing queries to retrieve entities and other persistent data from the database: query statements (Java Persistence Query Language, JPQL) and criteria API (criteria API). JPQL is a database-independent query statement that is used to operate logical entity models rather than physical data models. The conditional API is to build query conditions based on the entity model
1. Getting started with Java persistent query statements
The code copy is as follows: List<Person> persons= entityManager.createQuery("select p from Person p").getResultList();
1. This query statement is similar to SQL. But the difference between it and real SQL is that instead of making a selection query from a table, it specifies the entity from the application domain model.
2. The query select clause only lists the alias of the query entity. If you only query a certain column, you can use the dot (.) operator to navigate the entity properties. As shown below:
The code copy is as follows: List<String> persons= entityManager.createQuery("select p.firstName from Person p").getResultList();
1.1. Filter criteria
Like SQL, JPQL also supports where clauses, which are used to filter the search criteria. Includes most operators, such as: in, between, like, function expression substring, length, etc.
The code copy is as follows: List<Person> persons = entityManager.createQuery("select p from Person p where p.age>23").getResultList();
1.2. Projection results
If the amount of data queried is relatively large, you can use projection to query only useful columns.
The code copy is as follows:/Project List<Object> persons = entityManager.createQuery("select p.firstName,p.age from Person p").getResultList();
1.3. Aggregation query
JPQL's aggregate query syntax is similar to SQL. For example count
The code copy is as follows: List<Integer> count=entityManager.createQuery("select count(p) from Person p").getResultList();
1.4. Query parameters
JPQL supports two types of parameter binding syntax.
1. Position parameter representation
Where the parameter is indicated in the query string, which is the number that follows the parameter immediately after a question mark (?). When executing a query, the developer specifies the parameters that should be replaced
Query query=entityManager.createQuery("select p from Person p where p.age=?1 and p.firstName=?2");query.setParameter(1,21);query.setParameter(2,"Jack"); 2. Named parameter notation
By following a colon (:) and indicating it in the query string, the developer specifies the parameter name that should be replaced when executing the query
Query query=entityManager.createQuery("select p from Person p where p.age=:age and p.firstName=:name");query.setParameter("age",21);query.setParameter("name","Jack");2. Define the query
JPA provides Query and TypedQuery (introduced by JPA 2.0) interfaces to configure and execute queries. Query returns the Object type, while TypedQuery returns the specified Class type.
//No type specified, return Object type Query q = entityManager.createQuery("select p from Person p");//Specify the return type as Person type TypedQuery<Person> q1 = entityManager.createQuery("select p from Person p", Person.class);2.1. Dynamic query definition
JPA query engine can parse JPQL strings into syntax trees, obtain the metadata of entity objects-relational maps in expressions, and then generate equivalent SQL. Therefore, there are two ways to conduct dynamic query.
1.Split string method
Tip: Will cause SQL injection problems
/** * Dynamic splicing string construction query conditions* * @param name * @param age * @return */public static String queryPersonJPQL(String name, int age) { String queryQL = "select p from Person p where p.firstName= '" + name + "' and p.age=" + age; return queryQL;}//Call Query query = entityManager.createQuery(queryPersonJPQL("jack", 21)); 2. Dynamic parameterized construction of query conditions (recommended)
/** * Dynamic parameterized construction query conditions* * @return */public static String queryPersonJPQLByParams() { String queryQL = "select p from Person p where p.firstName=:name and p.age=:age"; return queryQL;}Query query = entityManager.createQuery(queryPersonJPQLByParams());query.setParameter("name", "Jack");query.setParameter("age", 21);2.2. Named query definition
Named query is a powerful tool. Use the @NamedQuery annotation to define a named query that can be placed on top of any entity's class definition. This annotation defines the name of the query and its text.
Tip: Named query is placed on the entity class corresponding to the query result
@Entity@NamedQuery(name = "findByAge", query = "select p from Person p where p.age=:age")public class Person { //Omitted} The name defined in Tip:NamedQuery needs to be unique in the entire persistence unit, otherwise there will be an error in the operation.
eg:
The code copy is as follows:
Exception in thread "main" org.hibernate.DuplicateMappingException: Duplicate query mapping findByAge at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.checkQueryName
Call
The code copy is as follows:
List<Person> people = entityManager.createNamedQuery("findByAge", Person.class).setParameter("age", 21).getResultList();
If a class defines two or more named queries, it must be placed in @NamedQueries()
2.3.Binding parameters
Through the previous example, we can see that there are two ways to bind parameters: 1. Position parameterization binding. 2. Name parameterized binding. All are bound through the setParameter method of the Query interface.
1. Position parameterization
TypedQuery<X> setParameter(int position, Object value);
2. Name parameterization
TypedQuery<X> setParameter(String name, Object value);
The first is position parameterized binding. If the position changes, the bound code needs to be changed. The second type is recommended.
2.4. Execute query
The Query interface and the TypedQuery interface provide three different ways to perform queries.
1.executeUpdate
Used to perform batch updates or deletion
2.getSingleResult
Get a single result set. If no data is obtained, a NoResultException will be thrown. If multiple pieces of data are obtained, a NonUniqueResultException will be thrown
3.getResultList
Get the corresponding result set and specify the order of the set. List needs to be used as the return value type. If no data is obtained, an empty set is returned and no exception is thrown
2.5. Pagination
The pagination query can be completed through the setFirstResult() and setMaxResults() methods
The query page number is 0, and 2 pieces of data are displayed on each page
The code copy is as follows:
List<Person> people = entityManager.createQuery("select p from Person p", Person.class).setFirstResult(0).setMaxResults(2).getResultList();
Tip: Cannot be used for queries connected by collection relationships, because these queries may return duplicate values.
2.6. Query timeout
If an application needs to set a limit on query response time, you can set the javax.persistence.query.timeout property in the query (introduced by jpa 2.0) or use it as part of the persistence property. This property defines the number of == milliseconds allowed to run before the query is terminated. If the query timeout, a QueryTimeoutException will be thrown.
TypedQuery<Person> query = entityManager.createQuery("select p from Person p", Person.class);//The unit is milliseconds javax.persistence.query.timeoutquery.setHint("javax.persistence.query.timeout", 5000);List<Person> people = query.getResultList();2.7. Batch updates and deletions
The batch update entity is completed through the update statement. Batch deletion of entities is done through delete statements. Both specify the properties of an entity and its class.
entityManager.getTransaction().begin();Query query = entityManager.createQuery("update Person p set p.firstName=:name where p.id=:id");query.setParameter("name", "xiaobai");query.setParameter("id", 2);query.executeUpdate();Query query1 = entityManager.createQuery("delete Person p where p.id=:id");query1.setParameter("id", 9);query1.executeUpdate();entityManager.getTransaction().commit();3. Suggestions for using JPQL query
In application systems, the number of queries is usually used more than adding, modifying, and deleting. Therefore, it is particularly important to use the query and display reasonably.
1. It is recommended to use named query (NamedQuery)
Persistence-provided programs usually use precompiled methods to use named queries as part of the program initialization phase. This avoids the system overhead of continuous parsing of JPQL and generating SQL.
2. Use projection to retrieve a small number of columns first.
JPA query usually returns all columns of the entire entity, but for a huge amount of data, not all entity columns need to be used. Then we can use projection to handle it.
List<List<Object[]>> persons = entityManager.createQuery("select new List(firstName,age) from Person p").getResultList();for (Object o : persons) { System.out.println(o);}//Output result[Jack, 21][Jack, 21][Jack, 21][lily, 19][tom, 23][tom, 23]The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.