Chapter 5 - Querying Database

Chapter 5 - Querying Database

DB Visual ARCHITECT(DB-VA) provides two features to query 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
Using Criteria

Introduction

If you want to provide the search function and make an easy way to retrieve records from the database, then you can use ORM Qualifier or Generate Criteria in DB-VA, in this chapter, we use an example to tell you that how to use the criteria class to search the persistent objects with your defined condition. First of all, you need to create the Class Diagram and synchronizes to Entity Relationship Diagram.
Class Diagram:

Entity Relationship Diagram:

Creating Test Data

You can create a staff record in the database and test how the criteria class can help you to retrieve the record.

Open CreateUntitledData.cs and modify the code with the following content:

private void CreateData() {

PersistentTransaction t = com.UntitledPersistentManager.Instance().GetSession().BeginTransaction();

try {

com.Staff lcomStaff = com.StaffFactory.CreateStaff();

// Initialize the properties of the persistent object

lcomStaff.Name = "Paul";

lcomStaff.Age = 12;

lcomStaff.Dob = new DateTime(1993, 11, 7);

lcomStaff.Gender = 'm';

lcomStaff.Save();

lcomStaff = com.StaffFactory.CreateStaff();

lcomStaff.Name = "Erica";

lcomStaff.Age = 33;

lcomStaff.Dob = new DateTime(1972, 11, 7);

lcomStaff.Gender = 'f';

lcomStaff.Save();

lcomStaff = com.StaffFactory.CreateStaff();

lcomStaff.Name = "Peggy";

lcomStaff.Age = 45;

lcomStaff.Dob = new DateTime(1960, 11, 7);

lcomStaff.Gender = 'f';

lcomStaff.Save();

lcomStaff = com.StaffFactory.CreateStaff();

lcomStaff.Name = "Sam";

lcomStaff.Age = 22;

lcomStaff.Dob = new DateTime(1983, 11, 7);

lcomStaff.Gender = 'm';

lcomStaff.Save();

t.Commit();

}

catch(Exception e) {

t.RollBack();

Console.WriteLine(e);

}

…

}

Execute the CreateUntitledData.class. The new record is added.

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. ORM Qualifier can be defined when you generate persistence code.

Defining ORM Qualifier

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

Click the ORM Qualifiers tab, then click Add….

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

Generate persistent code. The ORM Qualifier methods will be generated in the persistent class according to your selected Persistent API. For example, if you have selected Factory class as Persistent API, the following methods will be generated to 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 the 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 the specified session.

Retrieving From ORM Qualifier

After you have created the “Gender” qualifier, you can use it to load or list the Staff data from database. The following are examples to load or list records by ORM qualifier with the generated sample code.

By Load method:

System.Console.WriteLine("Retrieving Staff by gender...");

System.Console.WriteLine(com.StaffFactory.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...

Staff[ Name=Paul Age=12 Gender=m Dob=12/7/1993 12:00:00 AM ID=1 ]

By List method:

System.Console.WriteLine("Retrieving Staffs by gender...");

foreach(com.Staff lStaff in com.StaffFactory.ListByGender('f'))

{

System.Console.WriteLine(lStaff);

}

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=12/7/1972 12:00:00 AM ID=2 ]

Staff[ Name=Peggy Age=45 Gender=f Dob=12/7/1960 12:00:00 AM 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, you 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 box.

From the menu, select Tools > Object-Relational Mapping (ORM) > Generate Code… to open the Database Code Generation dialog box.
Select the Generate Criteria option. The other settings can be set to follow the picture below.

Sample

Override toString Method

All Properties

“Generate C# .NET code, Database Schema (DDL) and Persistent Library”

Description of Criteria Class

The following is the generated Staff class’s Criteria Class call “StaffCriteria”.

namespace com {

public class StaffCriteria : AbstractORMCriteria {

private StringExpression _name;

public StringExpression Name {

get {

return _name;

}

private Int32Expression _age;

public Int32Expression Age {

get {

return _age;

}

private CharExpression _gender;

public CharExpression Gender {

get {

return _gender;

}

private DateTimeExpression _dob;

public DateTimeExpression Dob {

get {

return _dob;

}

private Int32Expression _ID;

public Int32Expression ID {

get {

return _ID;

}

public StaffCriteria(PersistentSession session) : base(session.CreateCriteria(typeof(Staff))) {

_name = new StringExpression("Name", this);

_age = new Int32Expression("Age", this);

_gender = new CharExpression("Gender", this);

_dob = new DateTimeExpression("Dob", this);

_ID = new Int32Expression("ID", this);

}

public StaffCriteria() : this(com.UntitledPersistentManager.Instance().GetSession()) {

}

public Staff UniqueStaff() {

return (Staff)base.UniqueResult();

}

public Staff[] ListStaff() {

IList lList = base.List();

Staff[] lValues = new Staff[lList.Count];

lList.CopyTo(lValues, 0);

return lValues;

}

The StaffCriteria class is generated with attribute, which are defined in the object model, with type 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, call the method:

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

For example:

staffCriteria.Age.Ge(13);

There are two types of ordering to sort the retrieved records - ascending and descending.

To sort the retrieved records with respect to the property, call the method:

criteria.property.Order(ascending_order);

where the value of ascending_order is either true or false. Pass true to sort the property in ascending order, or pass false 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, use one of these 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 the 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 1.

ListClass() – Retrieve the records matched with the specified condition(s) for the criteria.

For example:

com.Staff[] staffs = staffCriteria.ListStaff();

Comparing Criteria Class and SQL Query

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

SQL Query is very long and easy to have syntax mistake but with the Criteria Class you can set the condition easily for each property of the persistent class. With Criteria Class you can get the Persistent Objects directly, but with SQL Query you can 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");

staffCriteria.SetMaxResults(ROW_COUNT);

com.Staff[] staffs = staffCriteria.ListStaff();

int length =staffs== null ? 0 : Math.Min(staffs.Length, 100);

for (int i = 0; i < length; i++) {

System.Console.WriteLine(staffs[i]);

}

System.Console.WriteLine(length + " Staff record(s) retrieved.");

SELECT * FROM staff WHERE name = 'Paul';

The Result:

Criteria Class

SQL Query

Staff[ Name=Paul Age=12 Gender=m Dob=11/7/1993 12:00:00 AM ID=1 ]

1 Staff record(s) retrieved.

Retrieve 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 DateTime(1970,1,1), new DateTime(1985,12,31));

staffCriteria.SetMaxResults(ROW_COUNT);

com.Staff[] staffs = staffCriteria.ListStaff();

int length =staffs== null ? 0 : Math.Min(staffs.Length, ROW_COUNT);

for (int i = 0; i < length; i++) {

System.Console.WriteLine(staffs[i]);

}

System.Console.WriteLine(length + " Staff record(s) retrieved.");

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

The Result:

Criteria Class

SQL Query

Staff[ Name=Erica Age=33 Gender=f Dob=11/7/1972 12:00:00 AM ID=2 ]

Staff[ Name=Sam Age=22 Gender=m Dob=11/7/1983 12:00:00 AM ID=4 ]

2 Staff record(s) retrieved.

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

staffCriteria.SetMaxResults(ROW_COUNT);

com.Staff[] staffs = staffCriteria.ListStaff();

int length =staffs== null ? 0 : Math.Min(staffs.Length, ROW_COUNT);

for (int i = 0; i < length; i++) {

System.Console.WriteLine(staffs[i]);

}

System.Console.WriteLine(length + " Staff record(s) retrieved.");

SELECT * FROM staff WHERE age = 18 OR age = 22 AND gender = 'm';

The Result:

Criteria Class

SQL Query

Staff[ Name=Sam Age=22 Gender=m Dob=11/7/1983 12:00:00 AM ID=4 ]

1 Staff record(s) retrieved.

Retrieve all staff records whose name starts with "P" and age is less 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);

staffCriteria.SetMaxResults(ROW_COUNT);

com.Staff[] staffs = staffCriteria.ListStaff();

int length =staffs== null ? 0 : Math.Min(staffs.Length, ROW_COUNT);

for (int i = 0; i < length; i++) {

System.Console.WriteLine(staffs[i]);

}

System.Console.WriteLine(length + " Staff record(s) retrieved.");

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

The Result:

Criteria Class

SQL Query

Staff[ Name=Paul Age=12 Gender=m Dob=11/7/1993 12:00:00 AM ID=1 ]

Staff[ Name=Peggy Age=45 Gender=f Dob=11/7/1960 12:00:00 AM ID=3 ]

2 Staff record(s) retrieved.

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