JPA Learning (6) JPQL and jpa learning jpql

JPA Learning (6) JPQL and jpa learning jpql

JPQL is short for Java Persistence Query Language. JPQL is a median and Object-Oriented Query Language similar to SQL. It is eventually compiled into SQL queries for different underlying databases, thus shielding the differences between different databases. JPQL statements can be select statements, update statements, or delete statements, which are encapsulated and executed through the Query interface.

1. The Query interface encapsulates methods for executing database queries. You can call the createQuery, create NamedQuery, and createNativeQuery methods of EntityManager to obtain the Query object. You can call the methods of the Query interface to perform the Query operation.

2.Query interface methods:
　　　　Int executeUpdate ():Used to execute update or delete statements.List getResultList () : Used to execute the select statement and return the Object List of the result set.Object getSingleResult ()It is used to execute the select statement that returns only a single result entity.Query setFirstResult (int startPosition) Used to set the object record from which the query results are returned.Query setMaxResults (int maxResult) Sets the maximum number of returned objects. It can be used with setFirstResult to implement paging query.Query setFlushMode (FlushModeType flushMode) Set the Flush mode of the query object. The parameter can have two enumerated values: FlushModeType. AUTO is used to automatically update database records, and FlushMode Type. COMMIT is used to update database records until the transaction is committed.SetHint (String hintName, Object value) Set specific supplier parameters or prompt information related to the query object. For parameter names and their values, see the documentation of the specific JPA implementation library provider. If the second parameter is invalid, an IllegalArgumentException exception is thrown.SetParameter (int position, Object value) Assign values to the specified position parameter of the query statement. Position specifies the parameter serial number. value is the value assigned to the parameter.SetParameter (int position, Date d, TemporalType) Assign a Date value to the specified location parameter of the query statement. Position: Specifies the parameter serial number. value is the value assigned to the parameter. temporalType is an enumeration constant of TemporalType, including DATE, TIME, and TIMESTAMP ,, it is used to temporarily convert the Date value of Java to the Date and time type supported by the database (java. SQL. date, java. SQL. time and java. SQL. timestamp ).SetParameter (int position, Calendar c, TemporalType type)Assign the Calenda R value to the specified location parameter of the query statement. Position specifies the parameter sequence number. value is the value assigned to the parameter. The meaning and trade-off of temporalType are the same as those before.SetParameter (String name, Object value) Assign values to the specified name parameter of the query statement.SetParameter (String name, Date d, TemporalType) Assign a Date value to the specified name parameter of the query statement. The usage is the same as before.SetParameter (String name, Calendar c, TemporalType type) Set the Calendar value for the specified name parameter of the query statement. Name is the parameter name, and others are the same as before. When this method is called, if the parameter location or name is incorrect, or the assigned parameter value type does not match, an IllegalArgumentException exception is thrown.

3. In fact, in addition to the above methods, there is no difference between JPQL and general SQL.
　　SELECT statement: Select o from VO o; pay attention to the following points in the query statement. First, VO is worthy of this entity class. In addition, when conditions are involved, fields are all vo attributes, instead of fields in the data table.
　　　　　　

@Test    public void testJPQL(){        String sql="select o from User o where id=?1";        Query query=em.createQuery(sql);        query.setParameter(1, 1);        List<User> users=query.getResultList();        for(User u:users){            System.out.println(u.getName());        }    }

WHERE:

The where clause is used to specify query conditions, where and condition expressions. Example: select o from Orders o where o. id = 1 select o from Orders o where o. id> 3 and o. confirm = 'true' select o from Orders o where o. address. streetNumber> = 123 JPQL also supports queries containing parameters, for example, select o from Orders o where o. id =: myId select o from Orders o where o. id =: myId and o. customer =: customerName Note: The parameter name must be prefixed with a colon (:). Query must be used before Query. the setParameter (name, value) method assigns a value to the parameter.

You can also use the sequence number without using the parameter name, for example, select o from Order o where o. id =? 1 and o. customer =? 2. Where? 1 represents the first parameter ,? 2 represents the first parameter. Before executing a Query, you must use the overload method Query. setParameter (pos, value) to provide the parameter value. Query query = entityManager. createQuery ("select o from Orders o where o. id =? 1 and o. customer =? 2 "); query. setParameter (1, 2); query. setParameter (2," John "); List orders = query. getResultList ();... ...
The available operators in the where condition expression are basically the same as those in SQL, including: Arithmetic Operators: +-*/+ (positive)-(negative) Relational operators: ==<>>>=<<=... And like in is null and other logical operators: and or not

The following are examples of common Query expressions: // The following statement queries orders with IDs ranging from 100 to 200. Select o from Orders o where o. id between 100 and 200 // The following statement queries customers of the 'us', 'cn', or 'jp 'nationality. Select c from MERs c where c. county in ('us', 'cn', 'jp ') // The following statement queries Customers whose mobile phone number starts with 139. % Indicates any number of character sequences, including 0. Select c from MERs c where c. phone like '000000' // The following statement queries Customers whose names contain 4 characters and 139% characters are ose. _ Represents any single character. Select c from MERs c where c. lname like '_ Oss' // The following statement queries Customers with unknown phone numbers. Nul l is used to test whether a single value is null. Select c from MERs c where c. phone is null // The following statement queries orders that have not yet entered the order item. Empty is used to test whether the set is empty. Select o from Orders o where o. orderItems is empty

　　Query some attributes:
If you only need to query partial attributes of an object, you do not need to return the entire object. Example: select o. id, o. customerName, o. address. streetNumber from Order o order by o. the id returned by executing this query is not a set of Orders entities, but a set of Object arrays (Object []). Each member of the set is an Object array, and each attribute can be accessed through array elements.

/*** Use Query cache */String SQL = "select 0 from User o"; query Query = em. createQuery (SQL); query. setHint (QueryHints. CACHEABLE, false );

　　Order by clause:The order by clause is used to sort the query result set. Similar to SQL, you can use "asc" and "desc" to specify the ascending or descending order. If not explicitly stated, the default value is ascending. Select o from Orders o order by o. id select o from Orders o order by o. address. streetNumber desc select o from Orders o order by o. customer asc, o. id desc

　Group by clause and Aggregate Query:The group by clause is used to group statistics on query results. Aggregate functions are usually used. Common Aggregate functions include AVG, SUM, COUNT, MAX, and MIN, which have the same meaning as SQL. Example: select max (o. id) from Orders o queries without a group by clause are based on the entire object class. Using an aggregate function, a single result value is returned. You can use Query. getSingleResult () to get the query result. Example: Query query = entityManager. createQuery ("select max (o. id) from Orders o "); Object result = query. getSingleResult (); Long max = (Long) result ;... ...

　　Having clause: The Having clause is used to set constraints for group by clauses. Its usage is basically the same as that of the where clause. The difference is that the where clause acts on the base table or view to select records that meet the conditions; the having clause acts on a group and is used to select a group that meets the conditions. The conditional expressions usually use aggregate functions. For example, the following statement is used to query the commodities sold by sellers whose total number is greater than 100: select o. seller, o. goodId, sum (o. amount) from V_Orders o group by o. seller, o. goodId having sum (o. amount)> 100 The having clause can use parameters like the where clause.

　　Join Query: In JPQL, implicit join queries are often performed by configuring object Association class attributes in the object class. For example, select o from Orders o where o. address. streetNumber = 2000 when the preceding JPQL statement is compiled into the following SQL statements, the Association is automatically included. The default value is left Association. In some cases, you may still need to precisely control the association. Therefore, JPQL also supports similar association syntaxes as in SQL. For example, left out join/left join inner join left join/inner join fetch where left join and left out join allow null entities in the right expression that meets the conditions.

　Subquery:JPQL also supports subqueries. A where or having clause can contain another query. When a subquery returns more than one result set, it usually appears in the any, all, exist s expressions for set matching queries. Their usage is basically the same as that of SQL statements.

　　JPQL Functions: JPQL provides the following built-in functions, including string processing functions, arithmetic functions, and date functions. String processing functions include concat (String s1, String s2): String merging/connection functions. Substring (String s, int start, int length): String function. Trim ([leading | trailing | both,] [char c,] String s): removes the specified characters or spaces at the beginning or end of a String. Lower (String s): converts a String to lowercase. Upper (String s): converts a String to a large write format. Length (String s): Evaluate the length of a String. Locate (String s1, String s2 [, int start]): locate the position where the second String (substring) appears from the first String. If not found, 0 is returned. Arithmetic functions include abs, mod, sqrt, and size. Size is used to calculate the number of elements in a set. There are three date functions, namely current_date, current_time, and current_timestamp. They do not require parameters and return the current date, time, and timestamp on the server.

Update statement:The update statement is used to update data. It is mainly used for batch update of a single entity class. The following statement sets the state of the customer whose account balance is less than 10 million RMB to unpaid: update MERs c set c. status = 'unpaid 'where c. balance <10000

Delete statement:The delete statement is used to update data. Delete from Customers c where c. status = 'inactive' and c. orders is empty