JPA JPQL query, sort ..... Go

http://macrabbit.iteye.com/blog/855384 JPQL is a query language with features similar to SQL, JPQL is completely object-oriented, with inheritance, polymorphism, and correlation characteristics, and hibernate hql very similar. parameters of the query statementThe JPQL statement supports two ways of defining a parameter: named parameter and positional parameter. Only one parameter definition mode is allowed in the same query statement. The format of the command parameter is: ": + parameter Name" Example: Query query = em.createquery ("Select p from the person p where p.personid= : Id"); Query.setparameter (" Id ", New Integer (1)); The format of the positional parameter is "? + position Number" Example: Query query = em.createquery ("Select p from the person p where p.personid= ? 1"); Query.setparameter (1,new Integer (1));   If you need to pass  java.util.Date  or  java.util.calendar   parameters into a parameter query  , you need to use a special Setparameter ()   method   The relevant  setParameter  method is defined as follows:  public Interface query{//  named parameter query with the parameter type  java.util.datequery setparameter (String name, java.util.Date value, Temporaltype temporaltype);//  named parameter query with the parameter type  java.util.calendarquery setparameter (String name, Calendar Value, Temporaltype temporaltype);//  positional parameter query, with parameter type  java.util.datequery setparameter (int position, Date Value, Temporaltype temporaltype);//  positional parameter query is used when the parameter type is  java.util.calendarquery setparameter (int position, Calendar value, Temporaltype temporaltype);}   because a  Date  or  Calendar  object can describe a real date, time, or timestamp  .  so we need to tell  Query  How the object uses these parameters, we pass the  javax.persistence.TemporalType  as parameters into the  setParameter  method, Tells the query interface what number to use when converting java.util.date  or  java.util.Calendar  parameters to local  SQL According to the library type  .   The following examples to learn JPQL statements, examples of entity bean  have  person, Order, orderitem ,  the relationship between them is: a person   has multiple  Order,  one  Order  there are multiple  OrderItem . Case sensitivity of the &NBSP;JPQL statement: Except for the  Java  class and property names, the query is case insensitive  . So, select  and  select and  SELECT  are the same, but  com.foshanshop.ejb3.bean.Person  and   com.foshanshop.ejb3.bean.person  are different, person.name  and  person.NAME  are also different.    named QueriesCan be passed on the entity bean @NamedQueryOr @NamedQueriesPre-Define one or more query statements to reduce the number of bugs that result from writing errors each time. Frequently used query statements are often defined as named queries. Define a single named query: @NamedQuery (name= "Getperson", query= "from person WHERE personid=?1") @Entitypublic class person implements Se rializable{If you are defining multiple named queries, you should define @NamedQuery in the @javax. Persistence.namedqueries: @NamedQueries ({@NamedQuery (name=) Getperson ", query=" from the person where personid=?1 "), @NamedQuery (name=" Getpersonlist ", query=" from the person where AGE&G t;? 1 ")}) @Entitypublic class person implements serializable{when a named query is defined, we can execute its query by name. The code is as follows: Query query = em. createnamedquery ("Getperson"); Query.setparameter (1, 1); Sort (order BY)"ASC" and "DESC" are ascending and descending, respectively, ASC ascending by default in JPQL://sorted first by age, then by date of birth, query query = Em.createquery ("Select p from person P ord Er by p.age desc, p.birthday asc "); Querying partial propertiesIn general, it is a query for the entity class, and the entity that is returned is also the entities that are queried. J P QL also allows us to directly query the properties we need, rather than returning the entire Entity. In some Entity attributes are particularly high, such queries can improve performance examples://query only the attributes (columns) We are interested in (column) query Query=em.createquery ("Select P.personid, p.name from the person p ORDER BY P.personid Desc ");//The element in the collection is no longer a person, but a object[] object array list result = Query.getresultlist (); if (result!=null) { Iterator Iterator = Result.iterator (), while (Iterator.hasnext ()) {object[] row = (object[]) iterator.next (); int PersonID = Integer.parseint (row[0].tostring ()); String personname = row[1].tostring (); ..... }} using constructors in queries (Constructor)JPQL supports the query's property results directly as a constructor parameter for a Java class, and produces the entity returned as a result. For example, the above example only gets the name and PersonID property of the person entity bean, and we do not want the element of the returned collection to be object[], and we want to wrap it with a class. You need to use the constructor .。   Example: public class Simpleperson {private Integer personid;private String name; 。。。。 Public Simpleperson () {}public Simpleperson (Integer PersonID, String name) {this. name = name;this. PersonID = PersonID; }} The query code is://We take the required two attributes as Simpleperson constructor parameters, and use the new function. Query query = Em.createquery (" Select New Com.foshanshop.ejb3.bean.SimplePerson (P. PersonID, p.name) from person p order by p.personid Desc");//The element in the collection is the Simpleperson object list result = Query.getresultlist (); if (result!=null) {Iterator Iterator = Result.iterator () while (Iterator.hasnext ()) {Simpleperson Simpleperson = (Simpleperson) iterator.next (); }} Attention!#2013/4/16 problems encountered in the actual combatTest n long solution, divided into the following points:1.p.name Here the name is best to match the Getter Method name GetName (), which is to fill out the name of the get, such as P. Name, avoid reporting could not resolve property:name error2. The JPA implementation of hibernate found that the constructor does not support the Java.sql.Timestamp and Java.sql.Date attribute types, and if it is a time field, it is currently found to be a java.util.Date type for the property type to avoid reporting unable to Locate appropriate constructor on class error Aggregate Queries (Aggregation)The aggregation functions supported by JPQL include: 1. AVG () 2. SUM () 3. Count (), the return type is Long, note that the count (*) syntax is available in Hibernate, but is not available in TopLink other products 4. MAX () 5. MIN () Example://Get maximum age query query = Em.createquery ("Select Max (p.age) from Person P"); Object result = Query.getsingleresult ();String MaxAge = result.tostring ();//Gets the average age of query = Em.createquery ("Select AVG (p.age) from Person P");//get minimum age query = EM.C Reatequery ("Select min (p.age) from Person P");//Get total number of query = Em.createquery ("SELECT count (p) from Person P");//Get the sum of the ages que ry = Em.createquery ("Select sum (p.age) from Person P"); If the aggregate function is not the only return column of Select...from, a "GROUP by" statement is required. "GROUP by" should contain Select all properties in the statement except the aggregate function. Example://Returns the total number of male and female students. Query query = em.createquery ("Select P.sex, Count (p) from the person p group by p.sex");//The elements in the collection are no longer person, Instead of a object[] object array list result = Query.getresultlist (); If you need to add a query condition, you need to use the "having" conditional statement instead of the "WHERE" statement Example://Returns the gender of more than 1 people query query = em.createquery ("Select P.sex, Count (p) from Pe Rson p GROUP by P.sex have Count (*) >?1 ");//Set parameter in query Query.setparameter (1, New Long (1));//The element in the collection is no longer a person, but an OBJ Ect[] Object Array list result = Query.getresultlist (); Associate (join)JPQL still supports similar associative syntax in SQL: Left out join/left joininner joinleft join Fetch/inner join FETCH Left out Join/left join Are allowed in the right-hand expression that matches the condition. entiies is empty (You will need to explicitly use the left join/left outer join in less cases. )Example: Get an order for a 26-year-old, regardless of whether there is a orderitemselect o from Order O-left join O.orderitems the where o.ower.age=26 order by O.orde Rid INNER JOIN requires that the right expression must return entities . Example://Get an order for a 26-year-old, must have orderitemselect o from order o inner join O.orderitems where o.ower.age=26 order by O.orderi D !! Important points of knowledge : in the default query, Entity the collection properties in are not associated by default, and the collection property defaults to lazy loading (lazy-load) . So, Left fetch/left out Fetch/inner join Fetch provides a flexible way to query load to improve the performance of queries. Example: Private String queryinnerjoinlazyload () {//default non-Associative collection property variable (orderitems) corresponding table query query = Em.createquery ("Select O from Ord Er o INNER JOINO.orderitems where o.ower.age=26 ORDER by O.orderid "); List result = Query.getresultlist (); if (Result!=null && result.size () >0) {// then get Order in the entity OrderItems ( Collection Property Variables )  is emptyOrder order = (order) result.get (0);// When needed, EJB3 Runtime will execute a SQL statement to load the current Order of the //orderitemsset<orderitem> list = Order.getorderitems ();iterator<orderitem> Iterator = List.iterator (); if ( Iterator.hasnext ()) {OrderItem orderitem =iterator.next (); SYSTEM.OUT.PRINTLN ("Order Product Name:" + orderitem.getproductname ());}} The code above executes "select O from the Order o inner join O.orderitems where o.ower.age=26 order by O.orderid" when compiled into SQL as follows (he does not contain the Set property variable (o Rderitems) for the table field): Select Order0_.orderid as orderid6_, Order0_.amount as amount6_, order0_.person_id asperson4_6_, Orde R0_.createdate as createdate6_ from Orders order0_ inner joins orderitemsorderitems1_ on order0_.orderid=orderitems1_.  order_id, Person person2_ Whereorder0_.person_id=person2_.personid and person2_.age=26 order by Order0_.orderid The above code when executed to set<orderitem> list = Order.getorderitems (); When will execute a SQL Statement to loadThe orderitems that belongs to the current order, compiled into SQL as follows: Select orderitems0_.order_id as order4_1_, orderitems0_.id as id1_, orderitems0_.id a S id7_0_,orderitems0_.order_id as order4_7_0_, orderitems0_.productname as productn2_7_0_,orderitems0_.price as Price7 _0_ from OrderItems orderitems0_ where the Orderitems0_.order_id=?order by Orderitems0_.id ASC has insufficient performance on this query. In order to query n order, we need an SQL statement to get all of the original object properties of order, but we need another n statement to get the OrderItems collection properties for each order. to avoid n+1 performance issues, we can leverage Join Fetch use one at a time. SQL statement to Order all the information in the query .Example//Get an order for a 26-year-old who must have orderitemquery query = Em.createquery ("Select O from Order o INNER JOIN FetchO.orderitems whereo.ower.age=26 ORDER by O.orderid "); The above sentence HPQL compiled into the following sql:select Order0_.orderid as orderid18_0_, orderitems1_.id as id19_1_, Order0_.amount asamount18_0_, order0_.person_id as person4_18_0_, order0_.createdate as createdate18_0_,orderitems1_.order_id as order4_19_1_, Orderitems1_.productname as productn2_19_1_,orderitems1_.price as price19_1_, orderitems1_.order_id as order4_0__, Orderitems1_.id as Id0__from Orders order0_ inner join OrderItems orderitems1_ onorder0_.orderid=orderitems1_.order_id, Person person2_ Whereorder0_.person_id=person2_.personid and person2_.age=26 order by order0_.orderid,orderitems1_.id ASC above because of the use of fetch, this query will only produce a SQL statement, than the original need to n+1 SQL statement has a significant performance improvement exclude the same record DISTINCTUsing associative queries, we often get duplicate objects, such as the following: "Select O from Order o INNER join fetch o.orderitems ORDER by O.orderid" when there are N OrderItem N order, and some order objects are often the same, we need to use the DISTINCT keyword to exclude the same object. Example: Select DISTINCTo from order o inner join fetch o.orderitems Order by O.orderid Compare EntityWhen you use a parameter query in a query, the parameter types, except String, the original data type (int, double, and so on), and their object type (Integer, double, and so on), can also be instances of Entity. Example://Querying all orders for a person query query = em.createquery ("Select O from Order o where O.ower =?1ORDER by O.orderid "); person person = new person ();p Erson.setpersonid (New Integer (1));//Set parameter Query.setparameter (1,person) in query; Batch Update (Batch Update)HPQL Support Batch Update example://The amount of all orders plus 10Query query = em.createquery ("Update order as O set o.amount=o.amount+10");//update number of records int r Esult = query. Executeupdate (); Bulk Delete (Batch Remove)Example://To delete an order with an amount less than 100, delete the Order subkey first, and then delete the order query query = em. CreateQuery ("Delete from OrderItem item where Item.order in (the From order As O where o.amount<100) "), query. Executeupdate (); query = em. CreateQuery ("Delete from Order as O where o.amount<100"); Query.executeupdate (); Number of records for delete using Operators notQuery all orders except the specified query query = em.createquery ("Select O from Order o where Not (o.ower =?1)ORDER by O.orderid "); person person = new person ();p Erson.setpersonid (New Integer (2));//Set parameter Query.setparameter (1,person) in query; using Operators betweenSelect O from Order as O where O.amount between and 1000 using Operators inchFind Personselect p from person as p where p.age in (26,21) age 26,21 using Operators likeFind Personselect p from person as p where p.name like ' li% ' with the string "Li" using Operators is NULLThe query contains all Orderselect o from Order as O where o.ower are [not] null using Operators is EMPTYIS-EMPTY is an operator for collection properties (Collection). Can be used with not. Note: Low-copyright Mysql does not support is empty//queries all orderselect that contain order items o from order as O where o.orderitems are [not] EMPTY using Operators EXISTS[NOT] EXISTS needs to be used in conjunction with sub-queries。 Note: Low-copyright Mysql does not support exists//if there is an order with order number 1, get all Orderitemselect oi from OrderItem as Oi where exists(Select o from Order o where o.orderid=1)//If no order with order Number 10 is present, get Orderitemselect Oi from OrderItem as Oi where Oi in ID 1 . Id=1 and Not exists(Select O from Order o where o.orderid=10) String FunctionsJPQL defines built-in functions for ease of use. These functions are used in a similar way to the corresponding function methods in SQL. Includes: 1. CONCAT string concatenation 2. SUBSTRING string Intercept 3. TRIM removes the space 4. LOWER Convert to lowercase 5. UPPER is replaced with capital 6. Length string 7. LOCATE String Positioning Example://Query all personnel, and after the name with the string "_foshan" select P.personid, concat (p.name, ' _foshan ') from the person as p//query all personnel, only take the surname First three characters of a name Select P.personid, substring (p.name,1,3) from the person as P calculation functionThe calculation functions defined by HPQL include: ABS absolute value sqrt square root mod take the remainder size to take the quantity example of the collection://query All order number of orders and number of order items select O.orderid, size (o.orderitems) from Or Der as O Group by o.orderid//queries all order number of orders and their total amount/10 remainder select O.orderid, mod (O.amount) from order as O Sub-query subqueries can be used to WHERE and the having in a conditional statementExample://Query All orderselect of a purchaser aged 26 years old o from Order as O where O.ower in (select p from person as p where p.age =26) result set paginationThere are times when executing a query will return thousands of records, in fact we just need to show a subset of the data. At this point we need to page the result set, QUERYAPI has two interface methods to solve this problem: setmaxresults () and the Setfirstresult ()。 Setmaxresults method Settings gets how many records the Setfirstresult method setting starts from that index in the result set (if there are 3 records returned, the container automatically indexes the records, the index starts at 0, and then 0, 1, 2) Example: public Li St Getpersonlist (int max, int whichpage) {try {int index = (whichpage-1) * MAX; Query query = em. CreateQuery ("from person p order by personid ASC"); List List = query. setmaxresults (max). Setfirstresult (Index). getresultlist (); em. Clear (); Detach the Entitymanager-managed entity bean in memory and let the VM recycle the return list;

JPA JPQL query, sort ..... Go