Documentation » Empire-db class overview Download

Empire class overview

To help you working with Empire-db this document gives you an overview of the most important Empire-db classes and how to apply them in your application.

The following chart shows very simplified in the top section (yellow) the most important Empire-db classes and their relationships. In the bottom section (orange) you see which classes you should extend in order to define your data model. All other Empire-db classes are usually used directly rather than extended unless you need to override their behavior.

DBDatabase

The database class is your root interface for accessing the data model description (i.e. the metadata) as well as for creating command objects and directly executing statements. In order to use this class you should extend it with your own database class in which you define all tables, views and relations. To make static references to your tables and views from your code you should provide a public final property for each table and view.

The tables, views and relations properties are collections for the corresponding object types. These collections may be used to write generic code like e.g. for data synchronization purposes.

Before using any of the methods on DBDatabase the database must be opened using the open method. For this you will require an instance of a DBDatabaseDriver for the target DBMS (not shown here).

Use the createCommand() method to obtain a DBCommand object for the target DBMS. Use this to dynamically create portable select, insert, update and delete statements.

Use executeSQL() to execute an SQL insert, update or delete statement.

Use querySingleValue() or any other of the query functions to perform simple queries without the overhead of using a DBReader.

DBTable / DBView

The DBTable and DBView classes are used to describe the corresponding database object. You should first create one class for every single table and view in your data model and name the class accordingly. In the constructor you should add all columns using the addColumn() method and in order to make static references to your columns from your code you should provide a public final property for each column. For tables you should additionally provide a primary key by calling setPrimaryKey().

Create an instance of each class in your DBDatabase derived class and assign it to a public final property as described above.

DBRecord

The DBRecord class holds the data of one database entity i.e. of a single row in one of the database's tables or views. An instance of DBRecord can be used with any table or view and hence extending it is not required. However we still recommend to extend DBRecord and create an individual class for each of your entities for two reasons: first there is type-safety, since you want your internal code to rely on certain entities and second it is likely that, as your project grows, you will need to override existing and implement new methods there. Initially you can leave them empty i.e. without any declared properties or methods.

Use the getValue() and setValue() methods to obtain and modify record data.
Use the isNew() and isModified() properties to evaluate a record's state or use the getColumn() method to obtain field meta data.

The methods create(), read(), update() and delete() are provided here for convenience. However they are only proxies. The implementation code resides in the corresponding DBRowSet derived object i.e. DBTable or DBView. So in order to changing or extending their behavior you should consider overriding the corresponding DBRowSet method.

DBCommand

The DBCommand class provides methods for the creation of SQL commands for select, insert, update and delete operations. To obtain a command object, first create a database object and call the open method with a DBDatabaseDriver instance for you target DBMS.

Use the select(), join(), where(), having(), groupBy(), orderBy() and set() methods to define your command. Use one of the various factory functions on the DBColumn and DBColumnExpr objects to perform data transformation or to create constraints. Here are a few examples:

// obtain command object from database
DBCommand cmd = db.createCommand();
// select the first 3 characters from lastname, set FOO if null, make them upper case etc  
cmd.select(EMPLOYEES.LASTNAME.coalesce("foo").upper().substring(0, 3).as("INDEX"));
// left join with departments
cmd.join(DEPARTMENTS.DEPARTMENT_ID, EMPLOYEES.DEPARTMENT_ID, DBJoinType.LEFT);
// Set contraint that length of lastname must be greater than 3
cmd.where(EMPLOYEES.LASTNAME.length().isGreaterThan(3));
// order descending by Lastname
cmd.orderBy(EMPLOYEES.LASTNAME, true);
// Set Lastname to Foo (for updates and inserts)
cmd.set(EMPLOYEES.LASTNAME.to("Foo"));

After completing your command you can either perform a query by using a DBReader object or obtain an SQL command string for your target DBMS calling either getSelect(), getUpdate(), getInsert() or getDelete(). Pass this string either to the database object's executeSQL() method or any of the various query methods.

DBReader

The DBReader class is used to perform a database query and access the result rows. To work with a DBReader first create a DBCommand object and define your query. Then use the DBCommand object with the DBReader's open method to perform the query. Afterwards you can do one of the following to obtain the query results:

Iterate through the results using the moveNext() method or using an iterator obtainted by the iterator() function. With every call the reader's cursor will be moved one row forward. Use the getValue() or any of the other value getters such as getString(), getBoolean(), getDate() etc. to access field data for a given column expression or field index.
Use the getBeanList() method to obtain a list of POJO objects. The POJO class must have either property setters for each of the query result fields or it must have a suitable constructor which arguments match the fields of the query. To find a setter method, the reader calls getBeanPropertyName() for every column expression used in the query. If no special property name has been set, then the property name is built from the column name with an underscore acting as the word separator. For example for a column named LAST_ANNUAL_SALARY a setter named setLastAnnualSalary() is required.
Use the getXMLDocument() method to create a DOM document from the entire query result. This document will also include the field descriptions. Alternatively use the methods addColumnDesc() and addRows() to add the query results to an existing DOM element.

Remember to always close a reader after it has been opened. We recommend using a try / finally block as follows:

DBReader reader = new DBReader();
try
{   // Open Reader
    reader.open(cmd, conn);
    // Use Reader
    ...
} finally
{   // close Reader
    reader.close();
}

Other hints

For the case of an error Empire-db offers an option of whether to work with exceptions or method return values. Most Empire-db object methods return a Boolean value indicating success or failure. However this applies only if exceptions are switched off, which is the case by default. In order to enable exceptions please use the following code on application startup:

// Enable Exceptions
de.esteam.empire.commons.ErrorObject.setExceptionsEnabled(true);