Chapter 5 - Querying Database

Chapter 5 - Querying Database

DB Visual ARCHITECT (DB-VA) provides two features for querying database. You can use the ORM Qualifier and Criteria to define you requirements to retrieve the data from database. They provide a much simpler way to retrieve records from the database than SQL query.

In this chapter:

Introduction
Creating Test Data
Using ORM Qualifier
Defining ORM Qualifier
Retrieving from ORM Qualifier
Using Criteria
Configuring Criteria Class Generation
Description of Criteria Class
Comparing the Criteria Class and SQL Query

Introductions

When you want to provide the search function and make an easy way for retrieving record from the database, then you can use ORMQualifier or Generate Criteria in DB-VA. In this chapter, we use an example to teach you how to use the criteria class to search the persistent objects from a user-defined condition. First of all, you need to create the following Class Diagram and synchronize to Entity Relationship Diagram.

Class Diagram:


Figure 5.1 - Class Diagram

Entity Relationship Diagram:


Figure 5.2 - Entity Relationship Diagram

Creating Test Data

Select generate Sample when generate Persistent Java code. Then you can create the staff to the database and test the criteria class to help you to get the record from database.

Open the CreateUntitledData.java and modify the code of the following:

public class CreateUntitledData {

public void createTestData() throws PersistentException {
PersistentTransaction t = com.UntitledPersistentManager.instance().getSession().beginTransaction();
try {
com.Staff lcomStaff = com.StaffFactory.createStaff();
// Initialize the properties of the persistent object
lcomStaff.setName("Paul");
lcomStaff.setAge(12);
java.util.Calendar c = java.util.Calendar.getInstance();
c.set(1993, 11, 7);
lcomStaff.setDob(c.getTime());
lcomStaff.setGender('m');
lcomStaff.save();

lcomStaff = com.StaffFactory.createStaff();
lcomStaff.setName("Erica");
lcomStaff.setAge(33);
c.set(1972, 11, 7);
lcomStaff.setDob(c.getTime());
lcomStaff.setGender('f');
lcomStaff.save();

lcomStaff = com.StaffFactory.createStaff();
lcomStaff.setName("Peggy");
lcomStaff.setAge(45);
c.set(1960, 11, 7);
lcomStaff.setDob(c.getTime());
lcomStaff.setGender('f');
lcomStaff.save();

lcomStaff = com.StaffFactory.createStaff();
lcomStaff.setName("Sam");
lcomStaff.setAge(22);
c.set(1983, 11, 7);
lcomStaff.setDob(c.getTime());
lcomStaff.setGender('m');
lcomStaff.save();
t.commit();
}
catch (Exception e) {
t.rollback();
}
}
...

}

Execute the CreateUntitledData class. It creates the specified records in database.


Figure 5.3 - The executed result

Using ORM Qualifier

ORM Qualifier is an additional feature of DB-VA allowing you to specify the extra data retrieval rules apart from the system pre-defined rules. The ORM Qualifier can be defined to generate persistence code.

Defining ORM Qualifier

Right-click on the Staff class, select Open Specification.


Figure 5.4 - To open class specification

Click the ORM Qualifiers Tab, then click Add.


Figure 5.5 - Class Specification dialog

ORM Qualifier Specification dialog box is displayed with a list of attributes of the Staff class. Enter the name as Gender and select attribute gender.


Figure 5.6 - ORM Qualifier Specification (ORM Qualifiers) dialog

Generate Persistent Java code, the ORM Qualifier methods will be generated in Persistent class according to the selected Persistent API. For example, if you selected Factory class as Persistent API, then the following methods will be generated in the StaffFactory class.

Return Type	Method Name	Sample	Description
Class	loadByORMQualifier(DataType attribute)	loadByGender(char gender)	Retrieve the first record that matches the specified value with the attribute defined in the ORM Qualifier.
Class	loadByORMQualifier (PersistentSession session, DataType attribute)	loadByGender(PersistentSession session, char gender)	Retrieve the first record that matches the specified value with the attribute defined in the ORM Qualifier and specified session.
Class[]	listByORMQualifier (DataType attribute)	listByGender(char gender)	Retrieve the records that match the specified value with the attribute defined in the ORM Qualifier.
Class[]	listByORMQualifier (PersistentSession session, DataType attribute)	listByGender(PersistentSession session, char gender)	Retrieve the records that match the specified value with the attribute defined in the ORM Qualifier and specified session.

Table 5.1

Retrieving from ORM Qualifier

Having created the "Gender" qualifier, you can use the "Gender" qualifier to load or list the Staff data from database. The following examples show how to load or list ORM qualifier with the generated sample code.

By Load method:

System.out.println(com.PersonFactory.loadByGender('m'));

Result: After executing the code the first occurrence of 'm' gender column in the Staff table will be loaded to the object identified as Staff.

Retrieving staff by gender...

Person[ Name=Paul Age=12 Gender=m Dob=1993-11-07 ID=1 ]

By List method:

System.out.println("Retrieving Staffs by gender...");

com.Staff[] staffs = com.StaffFactory.listByGender('f');
for (int i = 0; i < staffs.length; i++){

System.out.println(staffs[i]);

}

Result: After executing the code, all rows which contain 'f' in the gender column in the Staff table will be retrieved and stored in an array of Staff object.

Retrieving Staffs by gender...

Staff[ Name=Erica Age=33 Gender=f Dob=1972-11-07 ID=2 ]
Staff[ Name=Peggy Age=45 Gender=f Dob=1960-11-07 ID=3 ]

Using Criteria

When generating the persistence class for each ORM-Persistable class defined in the object model, the corresponding criteria class can also be generated.

Configuring Criteria Class Generation

After you have created the Class Diagram and ER Diagram, setup the database and generate code configuration. If you want to use the Criteria Class, you must select the Generate Criteria option in the Database Code Generation Dialog.

From the menu, select Tools > Object-Relational Mapping (ORM) > Generate Code... to open Database Code Generation dialog box.


Figure 5.7 - To generate code

Select Generate Criteria option and the other settings can be set to follow the picture below.

Check Sample so you can use the sample to test the criteria class.


Figure 5.8 - Database Code Generation dialog

Open the Advance Settings dialog box and set the Override toString Method to All Properties. It can help you to easily print out the persistent object data. Click OK to generate the Java Code with Criteria Class.


Figure 5.9 - Advance Settings dialog

For more details of configuring the database and generating code, you can refer to Chapter 1 - Generate Java, Database and Persistent Library.

Description of Criteria Class

The following is the criteria class of the previous generated Staff class called "StaffCriteria".

public class StaffCriteria extends AbstractORMCriteria {

public final StringExpression name;
public final IntegerExpression age;
public final CharacterExpression gender;
public final DateExpression dob;
public final IntegerExpression ID;

public StaffCriteria(PersistentSession session) {
super(session.createCriteria(Staff.class));
name = new StringExpression("name", this);
age = new IntegerExpression("age", this);
gender = new CharacterExpression("gender", this);
dob = new DateExpression("dob", this);
ID = new IntegerExpression("ID", this);
}

public StaffCriteria() throws PersistentException {
this(com.UntitledPersistentManager.instance().getSession());
}

public Staff uniqueStaff() {
return (Staff) super.uniqueResult();
}

public Staff[] listStaff() {
return (Staff[]) super.list().toArray(new Staff[super.list().size()]);
}

}

The StaffCriteria class is generated with attribute, which are defined in the object model, with type of one of Expression with respect to the type of attribute defined in the object model and two operations for specifying the type of record retrieval.

To apply the restriction to the property, implement the following code template:

criteria.property.expression(parameter);

where criteria is the instance of the criteria class; property is the property of the criteria; expression is the expression to be applied on the property; parameter is the parameter(s) of the expression.

The table below shows the expression that can be used for specifying the condition for query.

Expression	Description
eq(value)	The value of the property is equal to the specified value.
ne(value)	The value of the property is not equal to the specified value.
gt(value)	The value of the property is greater than to the specified value.
ge(value)	The value of the property is greater than or equal to the specified value.
lt(value)	The value of the property is less than the specified value.
le(value)	The value of the property is less than or equal to the specified value.
isEmpty()	The value of the property is empty.
isNotEmpty()	The value of the property is not empty.
isNull()	The value of the property is NULL.
isNotNull()	The value of the property is not NULL.
in(values)	The value of the property contains the specified values in the array.
between(value1, value2)	The value of the property is between the two specified values, value1 and value2.
like(value)	The value of the property matches the string pattern of value; use % in value for wildcard.
ilike(value)	The value of the property matches the string pattern of value, ignoring case differences.

Table 5.2

For example:

staffCriteria.age.ge(13);

There are two types of ordering to sort the retrieved records, that is, ascending and descending order.

To sort the retrieved records with respect to the property, implement the following code template:

criteria.property.order(ascending_order);

where the value of ascending_order is either true or false. True refers to sort the property in ascending order while false refers to sort the property in descending order.

For example:

staffCriteria.age.order(true);

To set the range of the number of records to be retrieved by using one of the two methods:

setFirstResult(int i) - Retrieve the i-th record from the results as the first result.
setMaxResult(int i) - Set the maximum number of retrieved records by specified value, i.

For example:

staffCriteria.setMaxResults(100);

The StaffCriteria class contains two methods to load the retrieved record(s) to an object or array.

uniqueClass() - Retrieve a single record matching the specified condition(s) for the criteria; Exception will be thrown if the number of retrieved record is not equal to 1.
listClass() - Retrieve the records matched with the specified condition(s) for the criteria.

For example:

com.Staff[] lcomStaffs = staffCriteria.listStaff();

Comparing the Criteria Class and SQL Query

The SQL Query can help you to find the record from the database and Criteria Class also can provide the same function to get the persistent object from the database.

The SQL Query is very long and easy to have syntax mistake but with the Criteria Class it is easy to set the condition for each property of the persistent class. The Criteria Class can directly get the Persistent Objects but SQL Query only get the individual data in database.

By Specifying one criteria

Retrieve a staff record whose name is "Paul":

Criteria Class	SQL Query
com.StaffCriteria staffCriteria = new com.StaffCriteria(); staffCriteria.name.eq("Paul"); com.Staff[] lcomStaffs = staffCriteria.listStaff(); int length = (lcomStaffs == null) ? 0 : Math.min(lcomStaffs.length, 100); for (int i = 0; i < length; i++) { System.out.println(lcomStaffs[i]); } System.out.println(length + " Staff record(s) retrieved.");	SELECT * FROM staff WHERE name = 'Paul';

Table 5.3

The Result:

Criteria Class	SQL Query
Staff[ Name=Paul Age=12 Gender=m Dob=1993-12-07 ID=1 ] 1 Staff record(s) retrieved.

Table 5.4

Retrieving all staff records whose date of birth is between 1/1/1970 and 31/12/1985:

Criteria Class	SQL Query
com.StaffCriteria staffCriteria = new com.StaffCriteria(); staffCriteria.dob.between(new GregorianCalendar(1970, 1, 1).getTime(), new GregorianCalendar(1985, 12, 31).getTime()); com.Staff[] lcomStaffs = staffCriteria.listStaff(); int length = (lcomStaffs == null) ? 0 : Math.min(lcomStaffs.length, 100); for (int i = 0; i < length; i++) { System.out.println(lcomStaffs[i]); } System.out.println(length + " Staff record(s) retrieved.");	SELECT * FROM staff WHERE dob > '1970-01-01' AND dob < '1985-01-01';

Criteria Class

SQL Query

com.StaffCriteria staffCriteria = new com.StaffCriteria();
staffCriteria.dob.between(new GregorianCalendar(1970, 1, 1).getTime(), new GregorianCalendar(1985, 12, 31).getTime());
com.Staff[] lcomStaffs = staffCriteria.listStaff();
int length = (lcomStaffs == null) ? 0 : Math.min(lcomStaffs.length, 100);
for (int i = 0; i < length; i++) {

System.out.println(lcomStaffs[i]);

}
System.out.println(length + " Staff record(s) retrieved.");

SELECT * FROM staff WHERE dob > '1970-01-01' AND dob < '1985-01-01';

Table 5.5

The Result:

Criteria Class	SQL Query
Listing Staff by Criteria... Staff[ Name=Erica Age=33 Gender=g Dob=1972-12-07 ID=2 ] Staff[ Name=Sam Age=22 Gender=m Dob=1983-12-07 ID=4 ] 2 Staff record(s) retrieved.

Table 5.6

By specifying more than one criteria

Retrieve all male staff records whose age is between 18 and 22:

Criteria Class	SQL Query
com.StaffCriteria staffCriteria = new com.StaffCriteria(); staffCriteria.age.in(new int[]{18, 22}); staffCriteria.gender.eq('m'); com.Staff[] lcomStaffs = staffCriteria.listStaff(); int length = (lcomStaffs == null)? 0 : Math.min(lcomStaffs.length, 100); for (int i = 0; i < length; i++) { System.out.println(lcomStaffs[i]); } System.out.println(length + " Staff record(s) retrieved.");	SELECT * FROM staff WHERE age = 18 OR age = 22 AND gender = 'm';

Table 5.7

The Result:

Criteria Class	SQL Query
Staff[ Name=Sam Age=22 Gender=m Dob=1983-12-07 ID=4 ] 1 Staff record(s) retrieved.

Table 5.8

Retrieve all staff records whose name starts with "P" and age is lesser than 50, ordering by the name:

Criteria Class	SQL Query
com.StaffCriteria staffCriteria = new com.StaffCriteria(); staffCriteria.name.like("P%"); staffCriteria.age.lt(50); staffCriteria.name.order(true); com.Staff[] lcomStaffs = staffCriteria.listStaff(); int length = (lcomStaffs == null)? 0 : Math.min(lcomStaffs.length, 100); for (int i = 0; i < length; i++) { System.out.println(lcomStaffs[i]); } System.out.println(length + " Staff record(s) retrieved.");	SELECT * From staff WHERE age < 50 AND name LIKE 'p%';

Criteria Class

SQL Query

com.StaffCriteria staffCriteria = new com.StaffCriteria();
staffCriteria.name.like("P%");
staffCriteria.age.lt(50);
staffCriteria.name.order(true);
com.Staff[] lcomStaffs = staffCriteria.listStaff();
int length = (lcomStaffs == null)? 0 : Math.min(lcomStaffs.length, 100);
for (int i = 0; i < length; i++) {

System.out.println(lcomStaffs[i]);

}
System.out.println(length + " Staff record(s) retrieved.");

SELECT * From staff WHERE age < 50 AND name LIKE 'p%';

Table 5.9

The Result:

Criteria Class	SQL Query
Staff[ Name=Paul Age=12 Gender=m Dob=1993-12-07 ID=1 ] Staff[ Name=Peggy Age=45 Gender=g Dob=1960-12-07 ID=3 ] 2 Staff record(s) retrieved.

Table 5.10

Visual Paradigm International Limited
Website:	www.visual-paradigm.com
E-mail:	support@visual-paradigm.com