OJB query
This document describes the use of different query mechanisms. The code in the document is tested through JUnit.
content:
1. Query by criteria
2. Odmg Object Query Language (OQL)
3. JDO Queries
Inquiry by criteria:
In this section, you will learn how to use criteria for queries. The corresponding class is placed in org.apache.ojb.broker package
in. Using the criteria query can be obtained throughout the object (such as Person), you can also use Report Queries
To a line of data
A query mainly includes the following two parts:
1. Get the object of the corresponding class
2. A list of conditions with Order By and Group By
OJB provides a QueryFactory class to create a new query. Although all Query classes are the construction method
A total, but we still recommend using QueryFactory to create a new query:
Query Q = queryfactory.newquery (person.class, crit);
Each condition represents a SQL-WHERE statement.
Criteria crit = new criteria ();
Crit.Addequalto ("firstname", "Tom");
Crit.Addequalto ("Lastname", "Hanks");
Query Q = queryfactory.newquery (person.class, crit);
The corresponding SQL statement is as follows:
Select ... from person where firstname = "tom" and lastname = "hanks";
Query conditions:
OJB provides conditional selection of all SQL-Comparers. In most cases, you don't need to go directly to it.
EqualToCriteria class. The Criteria class provides the corresponding way, as follows:
1. Create criteria: such as Addequalto ("firstname", "Tom" by comparing the value of the corresponding field:
2. Create criteria: such as Addequaltofield ("Firstname", "other_fi" by comparing two fields: AddEqualtofield
ELD ")
3. Create criteria: such as Addisnull ("firstname" by checking if it is null value: AddInUll ("firstname")
4. Create a blurred SQL Criteria: such as AddSQL ("Reverse (Name) Like 'RE%')
Below is a method of comparing a field value:
Addequalto
AddLike
Addgreaterorequalthan
Addgreatertrthan
AddLike
AddBetWeen, this method requires two parameters
Addin, this method uses the Collection class as a value parameter
Below is a method of comparing two fields, both ... Field end:
Addequaltofield
AddgreatertHanfield
And Of Course There Negative Forms
IN / NOT IN
Some databases limit the number of parameters in the syntax
If there is any restriction, OJB will open the in statement into several statements, the following example sets the limit number 3:
Select ... from artikel a0 where a0.kategorie_nr in (?,?) OR A0.KATEGORIE_NR IN (?) Order by 7 DESC
The restriction of IN can be defined in OJB.Properties:
...
# THE SQLINLIMIT Entry Limits the Number of Values in-SQL
# Statement, -1 for no limits. This Hint is buy in criteria.
SQLINLIMIT = 200
...
And / OR
The above-mentioned query conditions are "and" the relationship, sometimes you need to get the relationship between "and", as follows:
Criteria crit1 = new criteria ();
crit1.addlike ("firstname", "% o%");
crit1.addlike ("Lastname", "% M%");
Criteria crit2 = new criteria ();
crit2.addequalto ("firstname", "Hank");
crit1.addorcriteria (crit2);
Query Q = queryfactory.newquery (person.class, crit1);
Collection results = Broker.getCollectionByQuery (q);
The corresponding SQL statement is as follows:
SELECT ... WHERE (FirstName Like "%") and lastname
Like "% m%" or firstname = "hank"
Sort and group:
The following method can be used to sort and group:
AddorderByascending (String AnattributeName);
AddorderByDescending (String AnattributeName);
AddGroupby; this method is used for Report Queries
You can also make multiple sorting and packets, you can call AddorderBY:
CRIT = New criteria ();
crit.AddorderbyDescending ("ID");
crit.addorderbyascending ("Lastname");
Query = new querybycriteria (person.class, crit);
Broker.getCollectionByQuery (Query);
The above code will query all of the Persons, and arranged by the descending order of the ID, the ascending order of LastName is arranged. This query will be produced
Life as the following SQL statement:
Select A0.ID, A0.Firstname, A0.lastname from
Person A0 ORDER BY 1 DESC, 3
If you don't use LastName and use LastName, then new lastname will be automatically created:
Select a0.id, a0.firstname, a0.lastname, Lastname from Person A0 ORDER BY 1 DESC,
4
If there are multiple tables containing the lastname, the SQL statement will report an error, so it is best to use and attribute names.
