Ejb3 QL Query

The query language of ejb3 is similar to the intermediate and Object-Oriented Query Language of SQL. It can be compiled into SQL statements that are acceptable to different underlying databases, thus shielding the differences between different databases and ensuring that code written in ejb3 QL query languages can be run in different databases. Compared with the query language of EJB 2.1, ejb3 can be constructed at runtime and supports polymorphism, which is far more flexible and powerful than the query of EJB 2.1. In a program, ejb3 QL can be used in both upper case (select) and lower case (select), but not in combination.

Query interface

Javax. Persistence. query is the query operation interface of ejb3. To query, you must first obtain the query object through entitymanager.

public Query createQuery(String ejbqlString);

Next we will make a simple query to 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 called the abstract schema type of the COM. redsoft. samples. Order class in ejb3 queries. In ejb3 Ql, the query entity queries the abstract schema type of entity. In the same entitymanagerfactory, two entity classes with the same abstract schema type are 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 the result of a list set. We can use the iterator or list. Get (INT) method to obtain each qualified entity. The query in the Liberator ejb3 persistence runtime environment only compiles ejb3 QL into the corresponding SQL when constructing the query, but does not execute it. The compiled SQL statement is executed only when the application code calls the iterator. Next (), iterator. hasnext () or list. Get (INT) method for the first time.

In the result set returned by the Liberator ejb3 persistence runtime environment, all results are not saved, but a row pointer pointing to JDBC resultset or cached resultset is kept. Only when the user needs to obtain the entity instance will the data be obtained from the resultset and filled into the entity instance and returned to the application.

If the query results contain all qualified entity, the Liberator ejb3 persistence runtime environment automatically caches the results of each query by default. In this way, you do not need to access the database for the same query operation next time, but directly return the result set from the cache. However, if an update, insert, or delete operation is performed on the cached entity class before the next query operation, the cached result set is automatically cleared, in this way, the data will be obtained from the database in the next query, ensuring that the query always gets the correct results and avoids dirty data caching.

Sometimes a query returns massive data. The Liberator ejb3 runtime environment uses an adaptive weak reference pojo management mechanism to process massive data. In our tests and the customer's environment, we can handle tens of millions of data records. When processing a large amount of data, you must disable the cache of the set results.

// Assume that the number of returned results is large.

final Query query = entityManager.createQuery( "select o from Order o");

// Disable cache for query results

query.setHint( Constants.QUERY_RESULT_CACHE, "false");

final List result = query.getResultList();

final Iterator iterator = result.iterator();

// Here we can process massive data

while( iterator.hasNext() ){

// Process Order

}

Simple Query

The following is an example of a simple query. It can be seen that the use of SQL is very similar.

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 attribute in the order class. Address has a streetnumber attribute.

final Query query = entityManager.createQuery( "select o from Order o where o.address.streetNumber >= 123" );

Note that the attribute of entity is queried in the condition statement. The attribute name must be consistent with the attribute variable name in entity.

Query using parameters

Parameter query is similar to parameter query in SQL. Ejb3 QL supports two parameter definitions: named parameters and positional parameters. Only one parameter definition method can be used in the same query.

Naming parameters:

final Query query = entityManager.createQuery( "select o from Order o where o.id = :myId");

// Set parameters in the query

query.setParameter( "myId", 2 );

// Multiple parameters can be used

final Query query = entityManager.createQuery( "select o from Order o where o.id = :myId and o.customer = :customerName" );

// Set parameters in the query

query.setParameter( "myId", 2 );

query.setParameter( "customerName", "foo" );

Note that two named parameters with the same name cannot be used in the same query.

Location parameters:

final Query query = entityManager.createQuery( "select o from Order o where o.id = ?1");

// Set parameters in the query

Query. setparameter (1, 2); // 1 indicates the first parameter, and 2 indicates the parameter value.

// Or

final Query query = entityManager.createQuery( "select o from Order o where o.id = ?1").setParameter( 1, 2 );

// Multiple parameters can be used

final Query query = entityManager.createQuery( "select o from Order o where o.id = ?1 and o.customer = ?2" );

// Set parameters in the query

query.setParameter( 1, 2 );

query.setParameter( 2, "foo" );

If you need to run in different ejb3 runtime environments in the future, use the location parameter to ensure that the application is portable.

Order)

The following is an example of a simple query. It can be seen that the use of SQL is very similar. "ASC" and "DESC" are ascending and descending, respectively. If not explicitly stated, the default is ASC ascending in ejb3 QL.

// If this parameter is not specified, ASC is in ascending order by default,

final Query query = entityManager.createQuery( "select o from Order o order by o.id");

Final query = entitymanager. createquery ("select O from order o order by O. Address. streetnumber DESC"); // DESC is in descending order.

final Query query = entityManager.createQuery( "select o from Order o order by o.id, o.address.streetNumber");

Query partial attributes

In the previous example, all objects are queried for the entity class, and the returned object is also the entity of the entity class to be queried. Ejb3 QL also allows us to directly query and return the required attributes, rather than returning the entire entity. In some cases where there are many attributes in entity, such queries can improve performance.

// Directly query the attributes we are interested in (columns)

final Query query = entityManager.createQuery( "select o.id, o.customerName, o.address.streetNumber from Order o order by o.id");

// The set is no longer an order, but an array of objects []

final List result = query.getResultList();

// The first line

Object[] row = result.get( 0 );

// The first value in the array is ID.

int id = Integer.parseInt( row[0].toString() );

String customerName = row[1].toString();

String streetNumber = Integer.parseInt( row[2].toString() );

Constructor)

Ejb3 QL allows you to directly use the query property result as a Java class constructor parameter and generate an entity to return the result.

// We take the three required attributes as the constructor parameters of a class (orderholder) 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 set is orderholder.

List result = query.getResultList();

This Java class does not need to be an entity class.NewThe full name must be used for Java class.

Aggregate Query)

Like most SQL statements, ejb3 QL also supports aggregate functions in queries. Currently, ejb ql supports the following Aggregate functions:

• 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 single, we can use getsingleresult () to obtain the result.

final Object result = query.getSingleResult();

// Because the ID type in order is long,

final Long max = (Long)result;

// In some databases, the result types returned by the Max function are different from those returned by the column corresponding to the ID. For a safer method, you can use the string method for transformation.

fina long max = Long.parseLong( result.toString() );

An aggregate function can also be returned as an attribute to be queried.

// Return the final query of the manufacturer of all orders and the total value of their orders

= entityManager.createQuery( "select o.vender, sum(o.amount) FROM Order o　group by o.vender");");

Like SQL, if the aggregate function is not the only return column of select... from, you must use the "Group by" statement. "Group by" should contain all attributes except Aggregate functions in the SELECT statement.

// Return the name of the manufacturer of all orders, the goods number and the total value of each order. // 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");

To add query conditions, you must use the "having" Condition Statement instead of the "where" statement.

// Return the goods number of the manufacturer of all orders and the total value of each order of the goods. // here "having O. Vender = 'foo' is the condition

final Query query

= entityManager.createQuery( "select o.vender, o.partNumber, sum(o.amount) FROM Order o　

group by o.vender，o.partNumber having o.vender='foo'");

Parameters can be used in a "having" statement like a "where" statement.

// Return the goods number of the manufacturer of all orders and the total value of each order of the goods. // here "having O. Vender = 'foo' is the condition final query Query

= entityManager.createQuery( "select o.vender, o.partNumber, sum(o.amount) FROM Order o　

group by o.vender，o.partNumber having o.vender=?1");

query.setParameter( 1, "foo" );

final List result = query.getResultList();

Join)

In ejb3 Ql, in most cases, the use of Object Attributes implies 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 ejb3 Ql is compiled into the following SQL statements, the Association is automatically included. When ejb3 Ql is compiled into SQL statements, the left join clause is used by default ).

select o.id, o.vender, o.partNumber, o.amount, addressTable.id, addressTable.streetNumber

from orderTable as o left join addressTable where addressTable.streetNumber = 2000

However, in some cases, we still need to precisely control the association. Therefore, ejb3 QL still supports similar association syntaxes in SQL:

• Left out join/left join
• Inner join
• Left join/inner join fetch

Left join, left out joinAre allowed to meet the conditions in the right expression of the entiies is null.

// Return all order records whose addresses are 2000, regardless of whether orderitemfinal query = entitymanager. createquery ("select O from order o

left join o.orderItems where o.address.streetNumber=2000 order by o.id");

Because ejb3 QL uses left join by default. This query is equivalent to the ejb3 QL below.

// Return all order records whose addresses are 2000, regardless of whether orderitemfinal query = entitymanager. createquery ("select O from order o

where o.address.streetNumber=2000 order by o.id");

The explicit use of left join/left Outer Join is rare.

Inner joinThe expression on the right must return entities.

// Return all order records whose addresses are 2000. orderitemfinal 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 fetchIt provides a flexible query Loading Method to Improve query performance. In the default query, the set attribute in entity is not associated by default, and the set attribute is lazy-load by default ).

''' // The table corresponding to the set attribute variable (orderitems) is not associated after ejb3 Ql is compiled by default.

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 time, the orderitems (set property variable) in the order object is null.

final Order order = (Order)result.get( 0 )

// When the application needs it, ejb3 runtime will execute an SQL statement to load orderitems belonging to the current order

Collection orderItems = order.getOrderItems();

The query performance is insufficient. To query N orders, we need an SQL statement to obtain the original/Object Attributes of all orders. However, we need another N statements to obtain the attributes of the orderitems set for each order. To avoid the performance problem of N + 1, we can use join fetch to query all order information with an SQL statement at a time.

// Return all order records whose addresses are 2000. orderitem must exist in order.

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 fetch is used, this query generates only one SQL statement, which greatly improves the performance of N + 1 SQL statement.

Compare entity

When a parameter is used in a query, the parameter types include string, original data type (INT, double, etc.), and their object types (integer, double, etc ), it can also be an entity instance.

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" );

// Directly use the address object as the parameter.

query.setParameter( 1, address );

Batch update)

Ejb3 QL supports batch update.

Query query = managerNew.createQuery("update Order as o set o.vender=:newvender,  o.partNumber='fooPart' where o.vender = 'foo'");

query.setParameter("newvender", "barVender");

// Number of update records

int result = query.executeUpdate();

Batch Delete)

Ejb3 QL supports batch 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();

Use operator not

// Query all orders whose vender is not equal to "foo"

Query query = managerNew.createQuery("SELECT FROM Order AS o WHERE not(o.vender='foo')");

List result = query.getResultList();

// Delete all orders whose vender is not equal to "foo"

Query query = managerNew.createQuery("DELETE FROM Order AS o WHERE not(o.vender='foo')");

int result = query.executeUpdate();

Use the between operator

// Query all the order values of amount between 5 and 10 (including 5, 10)

Query query = managerNew.createQuery("select o FROM Order AS o left join o.orderItems ot where o.amount BETWEEN 5 AND 10 order by o.vender desc");

List result = query.getResultList();

Use the operator in

// Query the order of all vender "foo1", "foo2", or "foo3"

Query query = managerNew.createQuery("select o FROM Order AS o left join o.orderItems ot where o.vender in ( 'foo1', 'foo2', 'foo3' ) order by o.vender desc");

List result = query.getResultList();

Use the like Operator

// Query all orders whose names start with the string "foo"

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 the order of all vender strings ending with "foo"

Query query = managerNew.createQuery("select o FROM Order as o where o.vender like '%foo' order by o.vender desc");

List result = query.getResultList();

// It can be used together with not, for example, to query all the order whose vender does not end with the string "foo"

Query query = managerNew.createQuery("select o FROM Order as o where o.vender not like '%foo' order by o.vender desc");

List result = query.getResultList();

// It can be used in combination with escape. For example, you can query all the order orders starting with "foo" and ignore the '3' character.

// If the vender is "foo1", "foo2", "foo3" meets this condition, and "3foo1", "f3oo4" also meets the condition.

Query query = managerNew.createQuery("select o FROM Order as o where o.vender like '%foo' escape '3' order by o.vender desc");

List result = query.getResultList();

Use the operator is null

// Query all orderquery queries without addresses = managernew. createquery ("select O from order as O where O. Address is null ");

List result = query.getResultList();

// Query the non-empty order of all addresses

Query query = managerNew.createQuery("select o FROM Order as o where o.address is not null");

List result = query.getResultList();

Use the operator is empty

Is emptyIs an operator for collection. AndNot.

// Query orderquery query = managernew. createquery ("select O from order o where O. orderitems is empty by O. Vender DESC ");

List result = query.getResultList();

// Query the non-empty order of the orderitems set

Query query = managerNew.createQuery("select o FROM Order o where o.orderItems is not empty by o.vender desc");

List result = query.getResultList();

Use the operator exists

[Not] existsIt must be used 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");

Use the all/Some/Any operator

Query query = managerNew.createQuery("select emp from EmployeeA emp where emp.salary > all ( select m.salary from Manager m where m.department = emp.department)");

List result = query.getResultList();

Query query = managerNew.createQuery("select emp from EmployeeA emp where emp.salary > any ( select m.salary from Manager 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 Manager m where m.department = emp.department)");

List result = query.getResultList();

String Functions

Ejb3 QL defines built-in functions for ease of use. The usage of these functions is similar to the corresponding function methods in SQL. The string functions defined in ejb3 QL include:

• Concat String concatenation
• Substring string Truncation
• Trim spaces in trim
• Lower to lowercase
• Replace upper with uppercase
• Length String Length
• Locate string location

// Concat combines the two strings in the parameter into a string. Here firstname is "foo" and 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.firstName='foo'");

List result = query.getResultList();

assertEquals("foobar", result.get(0).toString());

// The 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='charles'");

List result = query.getResultList();

Object[] row1 = (Object[]) result.get(0);

assertEquals("oo", row1[1].toString());

// Obtain the starting position of "Ar" in firstname

Query query = managerNew.createQuery("SELECT emp.firstName , emp.salary , locate( emp.firstName, 'ar') FROM EmployeeA as emp where emp.firstName='charles1111'");

List result = query.getResultList();

Computing functions

The computing functions defined in ejb3 QL include:

• ABS absolute value
• Square root of SQRT
• MoD returns the remainder.
• Size: the number of sets.

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();

// Functions can also be used in conditions.

Query query = managerNew.createQuery("select o.vender, sum(o.amount) FROM Order AS o left join o.orderItems ot group by o.vender having size(o.orderItems) = 0 or lower( o.vender ) = 'foo' order by o.vender desc");

List result = query.getResultList();

// Obtain the remainder

Query query = managerNew.createQuery("select mod( o.owner.info.age, 10 ) FROM Order o where exists ( select o from 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");

Subquery

Subqueries can be used in where and having condition statements.

Query query = managerNew.createQuery("select emp from EmployeeA as emp where ( select count(m) from Manager as m where m.department = emp.department) > 0 ");

List result = query.getResultList();