JPA Learning (6) JPQL

JPQL language, the abbreviation for Java persistence Query Language. JPQL is an intermediate and object-oriented query language that is very similar to SQL and will eventually be compiled into SQL queries against different underlying databases, thus masking differences in different databases. The JPQL language statement can be a SELECT statement, an UPDATE statement, or a DELETE statement, which are executed through the Query interface encapsulation.

1. The query interface encapsulates the relevant methods for executing database queries. Call Entitymanager's createquery, create Namedquery, and Createnativequery methods to get the query object, which can then invoke the query interface's related methods to perform the querying operation.

2.The main method of the query interface：
　　　 int executeupdate ():　Used to execute an UPDATE or DELETE statement. List getresultlist () : Used to execute a SELECT statement and return a list of result set entities.Object Getsingleresult ()Used to perform a SELECT statement that returns only a single result entity. Query setfirstresult (int startposition) Used to set which entity record to start returning query results from. Query setmaxresults (int maxresult) Used to set the maximum number of returned result entities. Use it in conjunction with Setfirstresult to implement paged queries. Query setflushmode (flushmodetype flushmode) Sets the flush mode of the query object. The parameter can take 2 enumeration values: Flushmodetype.auto updates the database records automatically, Flushmode type.commit updates the database records until the transaction is committed. sethint (String hintname, Object value) Sets specific vendor parameters or prompt information related to the query object. Parameter names and their values require reference to a specific JPA implementation library provider's documentation. A IllegalArgumentException exception is thrown if the second argument is invalid. setparameter (int position, Object value) Assigns a value to the specified positional parameter of the query statement. Position Specifies the parameter ordinal, which is the value assigned to the parameter. setparameter (int position, Date D, temporaltype type) Assigns a Date value to the specified positional parameter of the query statement. Position the parameter ordinal, value is the value assigned to the parameter, Temporaltype the enumeration constant of Temporaltype, including date, time, and TIMESTAMP three, which is used to temporarily convert the Java date value to the database-supported Date-time types (Java.sql.Date, Java.sql.Time, and Java.sql.Timestamp).setparameter (int position, Calendar C, temporaltype type)Assigns the Calenda R value to the specified positional parameter of the query statement. POSITION specifies the parameter ordinal, value is the values assigned to the parameter, the meaning and trade-offs of Temporaltype. Setparameter (String name, Object value) Assigns a value to the specified name parameter of the query statement. Setparameter (String name, Date D, temporaltype type) Assigns a Date value to the specified name parameter of the query statement. Usage Ibid. Setparameter (String name, Calendar C, temporaltype type) Set the calendar value for the specified name parameter of the query statement. Name is the name of the parameter, and the other is the same. The illegalargumentexception exception is thrown if the parameter position or parameter name is incorrect, or if the assigned parameter value type does not match when the method is called.

3, in fact, in addition to the above methods, JPQL and general SQL is no different.
　　SELECT statement : Select o from VO o; the query statement needs to pay attention to a few points, first, VO is worthy of this entity class, and there is the condition, the field is the property of VO, rather than the field 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 the query criteria, where and conditional 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 Ord ERs o where o.address.streetnumber >= 123 JPQL also supports queries that contain parameters, such as: select O from Orders o where o.id =: myId select o from or DERs o where o.id =: myId and O.customer =: CustomerName Note: You must precede the parameter name with a colon (:), you should use the Query.setparameter (name, value) method to assign a value to the argument before executing the query

You can also use the ordinal of a parameter 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, and the? 2 represents the first parameter. You need to use the overloaded method Query.setparameter (POS, value) to supply the parameter values before executing the query. 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 operators available in the Where condition expression are basically consistent with SQL, including: arithmetic operators: +-*/+ (positive)-(negative) relational operators: = = <> > >= < <= between...and like in is null Equal logical operators: and OR not

Here are some examples of common query expressions://The following statement queries an order with an Id between 100 and 200. Select o from Orders o where o.id between and 200//The following statement queries the customer whose nationality is ' US ', ' CN ' or ' JP '. Select C from Customers C where c.county in (' US ', ' CN ', ' JP ')//The following statement queries the customer whose phone number starts with 139. % represents any number of character sequences, including 0. Select C from Customers C where c.phone like ' 139% '//The following statement queries a customer whose name contains 4 characters and 234 is an OSE. _ represents any single character. Select C from Customers C where c.lname like ' _ose '//The following statements query for customers with unknown phone numbers. Nul L is used to test whether a single value is empty. Select C from Customers C where c.phone is null//The following statement queries the order for which the order item has not been entered. Empty is used to test whether the collection is empty. Select o from Orders o where O.orderitems is empty

　　Query Partial properties:
If you only need to query part of an entity's properties, you don't have to return the entire entity. For example: Select O.id, O.customername, o.address.streetnumber from Order o The ORDER by o.id the query returned is no longer a collection of orders entities, but a collection of object arrays (Obje Ct[]), each member of the collection is an array of objects that can be accessed through array elements.

        /**         * Using 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 the use of SQL, you can specify the ascending order with "ASC" and "desc". If not explicitly stated, the default is ascending. Select o Order by o.id select O from Orders o order by o.address.streetnumber desc Select o from Orders O or Der by o.customer ASC, O.id DESC

　The GROUP BY clause and aggregate query:The GROUP BY clause is used to group statistics on the results of the query, typically using aggregate functions. Commonly used aggregate functions are AVG, SUM, COUNT, MAX, MIN, and so on, they have the same meaning as SQL. For example: select Max (o.id) from Orders o queries that do not have a GROUP BY clause are based on the entire entity class, use an aggregate function to return a single result value, and use Query.getsingleresult () to get the results of the query. For 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 on a group by group, and the usage is basically the same as the WHERE clause, in which case the WHERE clause acts on the base table or view in order to select a record that satisfies the condition; the HAVING clause acts on the grouping, Used to select a group that satisfies a condition, which typically uses an aggregate function in a conditional expression. For example, the following statement is used to query the goods and quantities sold by a merchant with a total order greater than 100: Select O.seller, O.goodid, sum (o.amount) from V_orders o Group by O.seller, O.goodid havin The G sum (O.amount) > The-having clause can use parameters just like the WHERE clause.

　　associative queries : In JPQL, there are many times when an implicit association (join) query is implemented by configuring the class property of an entity association in an entity class. For example: Select O from Orders o where o.address.streetnumber=2000 the above JPQL statements are automatically included when compiled into the following SQL, and the default is left associative. In some cases, it may still be necessary to have precise control over the association. For this reason, JPQL also supports similar associative syntax in SQL. Such as: Left-out Join/left join INNER join-left Join/inner join fetch where the LEFT join and left-out join are justified, all of which are allowed to be empty in the right expression that matches the condition.

　Sub-query: JPQL also supports subqueries, which can contain another query in a WHERE or HAVING clause. When a subquery returns more than 1 result sets, it is often used in any, all, exist s expressions for collection-matching queries. Their usage is basically the same as SQL statements.

　　jpql function : JPQL provides some of the following built-in functions, including string processing functions, arithmetic functions, and date functions. String handler functions are: Concat (string s1, String s2): string merge/Join function. SUBSTRING (string s, int start, int length): Takes a string function. Trim ([Leading|trailing|both,] [Char C,] string s): Removes the specified first/trailing character or space from the string. Lower (string s): Converts a string to lowercase. Upper (string s): Converts a string to uppercase. Length (string s): to find the lengths of the strings. Locate (string s1, string s2[, int start]): Finds the position where the second string (substring) appears from the first string. Returns 0 if it is not found. The arithmetic function mainly has ABS, mod, sqrt, size and so on. The Size is used to find the number of elements in the collection. The date functions are mainly three, namely Current_date, Current_time, Current_timestamp, which do not require parameters, return the current date, time, and time stamp on the server

Update statement:The UPDATE statement is used to perform data update operations. Primarily used for bulk update of a single entity class The following statement sets the account balance to not be reimbursed for accounts with less than million dollars: Update Customers c Set c.status = ' unpaid ' where c.balance < 10000

DELETE statement:The DELETE statement is used to perform data update operations. The following statement deletes an inactive customer without an order: delete from Customers c where c.status = ' inactive ' and c.orders is empty

　　

JPA Learning (6) JPQL