EJB3 QL Query

Http://www.blogjava.net/liaojiyong/archive/2008/07/11/56216.htmlEJB3 QL Query

The query language of EJB3 is an intermediate and object-like query language that is very similar to SQL. It can be compiled into SQL that can be accepted by different underlying databases, thus masking the differences between different databases, ensuring that code written in the EJB3 QL query language can run on different databases. Compared to the query language of EJB 2.1, EJB3 can be run-time constructed to support polymorphism, far more flexible and powerful than EJB 2.1 queries. Using EJB3 QL in a program can use uppercase (select) or lowercase (select), but not case-sensitive (for example: Select) Mixed use.

Query interface

Javax.persistence.Query is an interface for EJB3 query operations. To make a query, you first get the query object through Entitymanager.

                    Public Query createquery (String ejbqlstring);                                             

Let's do one of the simplest queries and query all the Com.redsoft.samples.Order classes.

                    Final query query = Entitymanager.createquery ("Select O from Order o");     Final List result = Query.getresultlist ();    Final Iterator Iterator = Result.iterator ();    while (Iterator.hasnext ()) {        //process Order    }                            

Note "From Order". "Order" is known as the abstract schema type of the Com.redsoft.samples.Order class in the EJB3 query. The query entity is queried for the abstract Schema type of entity in EJB3 QL. In the same entitymanagerfactory, the same entity class with two abstract Schema types is not allowed at the same time. For example, Com.redsoft.samples.Order and Com.redsoft.foo.Order are not allowed at the same time.

Query returns a list of the results of the collection, we can use iterator or list.get (int) method to obtain each qualified entity. Liberator EJB3 Persistence the query queries in the run environment simply compile the EJB3 QL into the corresponding SQL, but not execute. Compiled SQL is only really executed when the application code first calls Iterator.next (), Iterator.hasnext (), or the List.get (int) method.

In the result collection returned by the Liberator EJB3 persistence run environment, all results are not saved, but only one row pointer that points to the JDBC resultset or cache resultset is persisted. Only when the user does need to obtain an entity instance will the data be fetched from the resultset and populated into the entity instance to be returned to the app.

If the query results are in combination with all eligible entity, the Liberator EJB3 persistence run environment automatically caches the results of each query by default. This way, the next time the same query operation does not have to access the database, the result collection is returned directly from the cache. However, if a update/insert/delete operation is performed on the cached entity class before the next query operation, the cached result set is automatically emptied so that the next query will get the data from the database, ensuring that the query always obtains the correct results and avoids caching dirty data.

Sometimes the query returns a huge amount of data. Liberator EJB3 operating Environment adopts adaptive weak reference Pojo management mechanism, which can deal with massive data. In our tests and the customer's environment can be tens other data volume. However, when dealing with large amounts of data, be careful to turn off caching of collection results.

                        Suppose the number of results returned is huge    . Final query query = Entitymanager.createquery ("Select O from Order o");        Turn off cache query.sethint for query results    (Constants.query_result_cache, "false");    Final List result = Query.getresultlist ();    Final Iterator Iterator = Result.iterator ();        Here we can handle a huge amount of data while    (Iterator.hasnext ()) {        //processing order    }                            

Simple query The following is an example of a simple query that you can see similar to how SQL is used.

                    Final query query = Entitymanager.createquery ("Select O from Order o where o.id = 1");        Final query query = Entitymanager.createquery ("Select O from Order o where o.id = 1 and o.confirm = ' true ');        Final query query = Entitymanager.createquery ("Select O from Order o where o.id = 1 or o.customer = ' foo ');            Address is an object variable property on the Order class with an address that has a Streetnumber property of    final query query = Entitymanager.createquery ("Select O From Order o where O.address.streetnumber >= 123 ");                  

Note that the attribute of the entity is queried in the conditional statement, and the name of the property needs to match the name of the attribute variable in entity. Querying with parameters

parameter queries are similar to parameter queries in SQL. EJB3 QL supports two ways of defining parameters: named parameters and Positional parameters. Only one parameter definition mode is allowed in the same query.

Named parameters:

                    Final query query = Entitymanager.createquery ("Select O from Order o where o.id =: MyId");        Set the parameter Query.setparameter in the query    ("MyId", 2);            You can use multiple parameters for    final query query = Entitymanager.createquery ("Select O from Order o where o.id =: myId and O.customer = : CustomerName ");            Set the parameter Query.setparameter in the query    ("MyId", 2);    Query.setparameter ("CustomerName", "foo");                  

Note It is not allowed to use two named parameters of the same name in the same query.

Position parameters:

                    Final query query = Entitymanager.createquery ("Select O from Order o where o.id =? 1");        Set the parameters in the query    query.setparameter (1, 2);//1 for the first argument, 2 for the value of the parameter        //or    final query query = Entitymanager.createquery ("Select O from Order o where o.id =? 1"). Setparameter (1, 2);                You can use multiple parameters for    final query query = Entitymanager.createquery ("Select O from Order o where o.id =? 1 and O.customer =? 2" );            Set the parameters in the query    query.setparameter (1, 2);    Query.setparameter (2, "foo");                  

If you need to run in a different EJB3 running environment in the future, use the location parameter to ensure that the application is portable. Sort (order by) Here is an example of a simple query that you can see similar to how SQL is used. "ASC" and "DESC" are ascending and descending, respectively, and if not explicitly stated, ASC Ascending is the default in EJB3 QL.

                    If not noted, the default ASC is ascending,                   final query query = Entitymanager.createquery ("Select O from Order o order by o.id");        Final query query = Entitymanager.createquery ("Select O from Order o ' ORDER by o.address.streetnumber desc");//desc is descending 
   final query query = Entitymanager.createquery ("Select O from Order o order by o.id, O.address.streetnumber");                

The query section properties in the previous example are for queries against the entity class, and the entities that are queried for the entity class are returned. EJB3 QL also allows us to directly query back the properties we need, rather than returning the entire entity. Such queries can improve performance in cases where attributes are particularly high in some entity.

                    Directly query the attributes (columns) We are interested in    final query query = Entitymanager.createquery ("Select O.id, O.customername, O.address.streetnumber from Order o o.id ");        The collection is no longer an order, but a object[] object array    final List result = Query.getresultlist ();        First row    object[] row = result.get (0);        The first value in the array is the ID    int id = integer.parseint (row[0].tostring ());    String customerName = row[1].tostring ();    String Streetnumber = Integer.parseint (row[2].tostring ());                   

Using the constructor in the query (Constructor) EJB3 QL supports the query's property results directly as a constructor parameter for a Java class and produces the entity as the result of the return.

                    We take the required three attributes as a class (Orderholder) constructor parameter, and use the new function.                    query query = Entitymanager.createquery ("Select New Com.redsoft.ejb3.dummy.OrderHolder (   o.id, O.vender, O.partnumber  ) from  Order as O ");        The result in the collection is Orderholder    list result = Query.getresultlist ();                         

The Java class does not need to be an entity class. NEWRequires the Java class to use the full name. Aggregate Query (Aggregation)

Like most SQL, EJB3 QL supports aggregate functions in queries. The aggregate functions currently supported by EJB QL include:

• Avg
• SUM
• COUNT
• MAX
• MIN

                                            Final query query = Entitymanager.createquery ("Select MAX (o.id) from Order where o.customername= ' foo '");            If we know that the result is a single, we can get the result with Getsingleresult ()    final Object results = Query.getsingleresult ();            Because the type of ID in order is long,    final long max = (Long) result;                    In some databases, the type of results returned by the MAX function is not necessarily the same as the type of the column that the ID corresponds to, and a more secure way to transform the    fina long max = Long.parselong (result.tostring ()) with string.                        

Aggregate functions can also be returned as a property of the query.

                            Return all orders to the manufacturer and their total order value    final query Query         = Entitymanager.createquery ("Select O.vender, sum (o.amount) from Order o GROUP by O.vender ");                        

As with SQL, a "GROUP by" statement is required if the aggregate function is not the only return column of Select...from. "GROUP by" should contain all properties except the aggregate function in the SELECT statement.

                            Returns the name of the manufacturer of all orders, the goods number and the total order value of each goods    //Note that group by must be followed by O.vender and o.partnumber    final query Query         = Entitymanager.createquery ("Select O.vender, O.partnumber, sum (o.amount) from Order o Group by O.vender,o.partnumber");                        

If you also need to add a query condition, you need to use a "having" conditional statement instead of a "WHERE" statement.

                            Return all orders the manufacturer is the "foo" of the goods number and the total order value of each type of goods    //Here "having o.vender = ' foo ' as the conditional    final query Query         = Entitymanager.createquery ("Select O.vender, O.partnumber, sum (o.amount) from the Order o            Group BY O.vender,o.partnumber having o.vender= ' foo ');                

You can use parameters like "where" statements in the "having" statement.

                            Return all orders the manufacturer is the "foo" of the goods number and the total order value of each type of goods    //Here "having o.vender = ' foo ' as the conditional    final query Query         = Entitymanager.createquery ("Select O.vender, O.partnumber, sum (o.amount) from the Order o            Group BY O.vender,o.partnumber having o.vender=?1 ");    Query.setparameter (1, "foo");    Final List result = Query.getresultlist ();                

Association (join)

In EJB3 QL, in most cases, the use of Object Properties implies association (join). For example, in the following query:

                    Final query query = Entitymanager.createquery ("Select O from Order o         where o.address.streetnumber=2000 order by o.id" );                   

When the EJB3 QL is compiled into the following SQL, the association is automatically included, and the EJB3 QL is compiled into SQL with the associated Default left association (right join).

        Select O.id, O.vender, O.partnumber, O.amount, Addresstable.id, addresstable.streetnumber from         ordertable as O left J Oin addresstable where Addresstable.streetnumber = 2000          

In some cases, however, we still need precise control of the association. Therefore, EJB3 QL still supports similar associative syntax in sql:

• Left out Join/left Join
• INNER JOIN
• Left Join/inner join Fetch

Left joins, left-out joins, and so on, are all allowed to entiies in the right-hand expression that matches the condition.

                    Returns all order records with address 2000, regardless of whether there is a OrderItem                    final query query = entitymanager.createquery in order ("Select O from Order o
   left Join O.orderitems where o.address.streetnumber=2000 order by o.id ");                       

Since EJB3 QL uses the left join by default. Such a query is actually equivalent to the following EJB3 QL.

                    Returns all order records with address 2000, regardless of whether there is a OrderItem                    final query query = entitymanager.createquery in order ("Select O from Order o
   where o.address.streetnumber=2000 ORDER by o.id ");                       

You will need to explicitly use the left join/left outer join in less cases.

INNER JOIN requires that the right expression must return entities.

                    Returns all order records with address 2000, which must have OrderItem                    final query query = Entitymanager.createquery ("Select O from Order o         INNER JOIN O.orderitems where o.address.streetnumber=2000 order by o.id ");                       

left/left Out/inner Join Fetch provides a flexible way to query loading to improve the performance of queries. In the default query, the collection property in entity is not associated by default, and the collection property defaults to slow Load (lazy-load).

                                "//default EJB3 QL compiled without associated set property variable (orderitems) corresponding to table                    final query query = Entitymanager.createquery (" Select O from Order o
   
    inner Join O.orderitems where o.address.streetnumber=2000 order by o.id ");    Final List result = Query.getresultlist ();        At this point, the OrderItems (collection attribute variable) in the order entity is empty    final Order order = (order) result.get (0)        //EJB3 Runtime executes a sq when required by the application L statement to load the OrderItems    Collection orderitems = Order.getorderitems () belonging to the current order;                       
   

There is a lack of performance on this query. In order to query n order, we need an SQL statement to get all the original/object properties of the order, but we need another n statement to get the OrderItems collection properties for each order. To avoid n+1 performance problems, we can use the join fetch to query all of the order information at once using an SQL statement.

                    Returns all order records with address 2000, which must have OrderItem                    final query query = Entitymanager.createquery ("Select O from Order o         INNER JOIN fetch o.orderitems where o.address.streetnumber=2000 ORDER by o.id ");                    

Because of the use of fetch, this query produces only one SQL statement, which has a significant performance improvement over the original N+1 SQL statement.

Compare entity

When 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.

                    Final query query = Entitymanager.createquery ("Select O from order o where o.address =. 1 ORDER by O.id");        Final Address address = new Address (2001, "Foo Street", "Foo City", "Foo province");        The Address object is directly used as an argument.    Query.setparameter (1, address);                   

Bulk update (batch update)

EJB3 QL supports batch updates.

                   Query query = managernew.createquery ("Update Order as O set O.vender=:newvender,  o.partnumber= ' Foopart ' where O.vender = ' foo ');   Query.setparameter ("Newvender", "Barvender");      Record number of update   int result = Query.executeupdate ();                

Bulk Delete (Batch remove)

EJB3 QL supports bulk deletion.

                    Query query = Managernew.createquery ("DELETE from Order");    int result = Query.executeupdate ();                    Query query = Managernew.createquery ("DELETE from Order as O WHERE o.vender= ' Redsoft '");    int result = Query.executeupdate ();                            

Using the operator not

                                    Query for all vender not equal to "foo" in the order                    query query = Managernew.createquery ("Select from Order as O WHERE is not (o.vender= ' foo ')") ;    List result = Query.getresultlist ();                    Delete all vender not equal to "foo" in the order    query query = managernew.createquery ("Delete from Order as O WHERE is not (o.vender= ' foo ')") ;    int result = Query.executeupdate ();                

Using the operator between

                                    Query for all values amount between 5 and 10 (contains 5, 10) of order                   Query query = Managernew.createquery ("Select O from Order as O left join O.order Items ot where o.amount between 5 and ten order by o.vender Desc ");    List result = Query.getresultlist ();             

Using the operator in

                    The query for all vender is "foo1", "Foo2" or "Foo3" of order                    query query = Managernew.createquery ("Select O from Order as O" and left join O. OrderItems ot where O.vender in (' Foo1 ', ' foo2 ', ' Foo3 ') Order by o.vender Desc ");    List result = Query.getresultlist ();                                     

Use the operator like

                    Query all vender with the string "foo" beginning with the order query query = Managernew.createquery ("Select O from    Order as O where O.vender like ' foo% ' ORDER by o.vender Desc ");            List result = Query.getresultlist (); Query all vender with the string "foo" at the end of the order query query = Managernew.createquery ("Select O from Order as O where o.v    Ender like '%foo ' ORDER by o.vender Desc ");            List result = Query.getresultlist (); Can be used in conjunction with not, such as querying all vender not with the string "foo" end of the order query query = Managernew.createquery ("Select O from Orde    R as O where O.vender '%foo ' ORDER by o.vender Desc ");        List result = Query.getresultlist ();    Can be used in conjunction with escape, such as querying all vender to start with "foo" and ignoring the ' 3 ' character.    If the vender is "foo1", "Foo2", "Foo3" meet this condition, and "3foo1", "F3oo4" also meet the conditions. Query query = Managernew.createquery ("Select O from order as O where O.vender like '%foo ' escape ' 3 ' ORDER by O.vender des    C ");                      List result = Query.getresultlist ();                        

Using the operator is NULL

                    Query all non-address order query Query                    = Managernew.createquery ("Select O from Order as O where o.address is null");    List result = Query.getresultlist ();        Query all addresses non-empty order     Query query = Managernew.createquery ("Select O from Order as O where o.address are not null");    List result = Query.getresultlist ();                                         

Use operator is EMPTY

IS- EMPTY is an operator for collection properties (Collection). Can be used with not.

                    The query OrderItems collection is empty for order                    query query = Managernew.createquery ("Select O from Order o where O.orderitems are empty by O . vender desc ");    List result = Query.getresultlist ();       Query OrderItems collection non-empty order                    Query query = Managernew.createquery ("Select O from Order o where o.orderitems are not empty by O.vender Desc ");    List result = Query.getresultlist ();                                  

Using the operator exists

[not]exists needs to be used in conjunction with subqueries.]

                    Query query = Manager.createquery ("Select O from Order o where exists (select O from Order o where o.partnumber=?1) Order by O.vender Desc ");    Query.setparameter (1, "partnumber");            Query query = Manager.createquery ("Select O from Order o where o.vender= ' partnumber ' and NOT EXISTS (select O from Order o where o.partnumber=?1) Order by o.vender Desc ");    Query.setparameter (1, "partnumber");             

Using the operator All/some/any

                    Query query = managernew.createquery ("Select emp from Employeea emp where emp.salary > All (select M.salary from Manag Er m where m.department = Emp.department) ");    List result = Query.getresultlist ();        Query query = managernew.createquery ("Select emp from Employeea emp where emp.salary > No (select M.salary from Manag Er m where m.department = Emp.department) ");    List result = Query.getresultlist ();        Query query = managernew.createquery ("Select emp from Employeea emp where emp.salary > Some (select M.salary from Mana Ger m where m.department = Emp.department) ");    List result = Query.getresultlist ();             

String functions

EJB3 QL defines built-in functions for ease of use. These functions are used in a similar way to the corresponding function methods in SQL. The string functions defined in EJB3 QL include:

• CONCAT string concatenation
• SUBSTRING string interception
• TRIM Remove Space
• LOWER Convert to lowercase
• UPPER, replace with uppercase.
• Length string Lengths
• LOCATE string Positioning

//Concat the two strings in the argument and forms a string, here FirstName is "foo", LastName is "bar" query query = Entitymanager.createquery (" Select Concat (O.owner.firstname, O.owner.lastname) from Order as O left outer join O.orderitems as Oi where o.owner.firs    Tname= ' foo ');    List result = Query.getresultlist ();             Assertequals ("Foobar", Result.get (0). toString ()); FirstName is "FooBar" and the result should return "oo" query query = Entitymanager.createquery ("Select O.vender,substring ( O.owner.firstname, 1, 3), o.owner.info.age from Order as O left outer join O.orderitems as Oi where o.owner.firstname= ' ch    Arles ' ");    List result = Query.getresultlist ();    Object[] Row1 = (object[]) result.get (0);        Assertequals ("oo", row1[1].tostring ()); Get "Ar" in FirstName start position in query query = Managernew.createquery ("Select Emp.firstname, Emp.salary, locate (Emp.firstna    Me, ' ar ') from Employeea as EMP where emp.firstname= ' charles1111 ' ");                     List result = Query.getresultlist (); 

Calculation function

The computational functions defined in EJB3 QL include:

• ABS Absolute
• SQRT Square Root
• MOD take remainder
• Size to take the number of collections

           Query query = Entitymanager.createquery ("Select O.vender, Size (o.orderitems) from Order o  where o.owner.firstname = ' Charles ' GROUP by O.vender ORDER by o.vender Desc ");    List result = Query.getresultlist ();        The function can also be used in the condition of    query query = Managernew.createquery ("Select O.vender, sum (o.amount) from Order as O left join O.orderit EMS OT GROUP BY O.vender have size (o.orderitems) = 0 or lower (o.vender) = ' foo ' ORDER by o.vender Desc ");    List result = Query.getresultlist ();        Fetch remainder    query query = managernew.createquery ("Select mod (o.owner.info.age) from Order o where exists (select o f Rom order o where o.partnumber=: name) and o.vender= ' Order1 ' and exists (select O from Order o where o.amount=: name1) ORDER BY o.vender Desc ");                     

Sub-query

Subqueries can be used in where and having conditional statements.

           Query query = managernew.createquery ("Select emp from Employeea as EMP where (select COUNT (m) from Manager as M where M.D epartment = emp.department) > 0 ");    List result = Query.getresultlist ();            

EJB3 QL Query