The First O/R-Mapping

In this chapter you will learn the very basic object mapping. The object relations are discussed in the next chapter.

Tentackle's Persistence Layer

For several reasons, Tentackle's object relational mapping does not follow the POJO approach. Instead, it is based on an extended derivate of the Active Record Pattern. Entities are implemented as subclasses of the abstract class AppDbObject, the Application Database Object (in fact, AppDbObject in turn extends DbObject, a simpler Database Object). The concrete implementation of an entity is accomplished by overriding methods, most of them being abstract. While this approach has several advantages (low complexity, no runtime configuration, high performance, no attach/detach, lazy loading possible at any time -- to name only a few), there can be quite a lot of such methods and, depending on the model, a lot of code. However, thanks to the Wurbelizer, this tedious and errorprone job is completely automated. All the code is generated from the model. So, how does all that fit together, what does the model look like and where is it located?
Let's start with Invoice.java!

Go to the Netbeans project tab, right-click on the Source Packages-folder of the Invoicer project, execute New File... and from the Tentackle templates select AppDbObject.java:

Next, create the file Invoice.java in src/org/tentackle/invoicer/db:

Note: For this tutorial, we've chosen org.tentackle.invoicer as the package name. Of course, this is not a subpackage of Tentackle, but just a package name like any other.

The editor will open. Move the cursor to the second comment block and replace <dbms-table-name> by the name of the database table for the invoice header: invoice.

/**
 * @{
 * # name of database table
 * tablename = invoice
 * mapping   = $dbmodel/$tablename.map    # java <-> db mapping
 * relations = $dbmodel/$tablename.rel    # object relations
 * @}
 */

In the third comment block, describe the attributes and indexes of the invoice header:

/**
 * @> $mapping
 * # mapping for Invoice
 * #
 * # create unique index ${tablename}_no on $tablename (invoice_no)
 * # create index ${tablename}_customer on $tablename (customer_id)
 * # create index ${tablename}_date on $tablename (invoice_date)
 * 
 * String     10    invoiceNo       invoice_no        the unique invoice number
 * Date       0     invoiceDate     invoice_date      the invoice's date
 * long       0     customerId      customer_id       object ID of the customer
 * String     255   address         address           the address (multiline)
 * DMoney     0     total           total             sum (without tax)
 * DMoney     0     tax             tax               tax (vat)
 * Timestamp  0     printed         printed           first printed
 * @<
 */

You probably ask yourself, what all these funny @-characters are good for?
It's all about Agile Generative Programming. We will not explain the details here. If you are curious about the Wurbelizer, how it works and what can be done with it, please visit http://www.wurbelizer.org.

To improve readability, the attributes are not described in XML but as simple text in table form. The first column describes the Java type, the second the maximum number of characters (with regard to the database column and the GUI), the third is the Java name of the attribute, the fourth the name of the database column and the rest is comment or options (covered later).

Tentackle supports the following data types:

• all Java primitives (char, boolean, byte, short, int, long, float, double).
• all Java objects derived from primitives (Character, Boolean, Byte, Short, Integer, Long, Float, Double).
• BigDecimal
• String
• the java.sql types Date, Time and Timestamp
• Binary: a tentackle type for a Binary Large Object
• BMoney and DMoney: tentackle types for money (more on that later)

By convention, primitives are mapped to NOT NULL columns, whereas objects are assumed WITH NULL. For some data types, this convention can be changed optionally.
Take a look at the create index lines and discover that you can use variables like $tablename. And where the heck is $dbmodel declared? Look at cg/codegen.properties ...

When you have finished editing, let's generate the code!

To do so you can either Build -> wurbelize sources, press the wurbelize-button in the toolbar or press Ctrl-Shift-F11 (or whatever shortcut you have chosen in the previous chapter).
Scroll down in Invoice.java and see what happened. You will find a lot of gray code, which is initially shown collapsed in the editor. Expand some of collapsed blocks to inspect the generated code, for example:

The gray code is a so-called guarded block in Netbeans. The text within such a guarded block cannot be edited by the developer. It is locked by special comment tags. The type of comment tags used by the Wurbelizer (i.e. the IDE) is selected by the property guardtype in cg/codegen.properties.

Please fix the imports (Ctrl-Shift-I) and make sure that Date and Timestamp are imported from java.sql. Next, implement the toString() method:

  public String toString() {
    return getInvoiceNo();
  }

Did you notice, that the method getInvoiceNo() has been generated? Press F11 to build the project. Everything should compile and build without errors.
Fine!

But what about the database backend? Do we need to create the tables manually? Fortunately, no. All SQL-scripts are automatically generated. Just make sure that the property dbtype in cg/codegen.properties corresponds to your database backend. The property is only used for SQL code generation. There are three Ant-targets related to that:

• sqltable: generates SQL statements to create the database tables. Use it to create a database from scratch.
• sqlmigrate: generates SQL statements to migrate an existing database to the current data model. If you made changes to your model (for example add, remove or change a column, or add a new entity), simply run sqlmigrate, which will compare the current database to the model and generate all necessary statements so that the database's tables, columns and indexes will reflect the model. Notice, however, although the migration tool does its best to generate reasonable code, you should always verify the SQL script before running it. For example, the tool may generate a statement to rename a column, but in fact it should be dropped and another one created. In such cases you will find a rename statement and a drop- and add statement commented out, so you can easily change that. Another example for manual intervention: in one of the next chapters the Invoicer application will be extended to provide multi-tenancy. Some tables must be changed to hold the additional tenant_id. Consequently, the tool will generate code that initializes that id to zero, because it is NOT NULL. However, you probably want to assign the id of an existing tenant.
• sqlhistory: generates SQL statement to create history tables. Tentackle provides a feature called "field-level history". We'll cover that in one of the next chapters.

For now, just run sqltable. Either create a shortcut as described in the previous chapter or in the Files-tab right-click on build.xml and execute Run Target -> sqltable.
Open db/mysql/createtables.sql and see what it looks like:

Don't run the script now as we will add more tables before we connect the first time to the database -- and if you accidently did, don't worry: sqlmigrate is your friend.

Next, similar to Invoice.java please create Customer.java and edit the model:

/**
 * @{
 * # name of database table
 * tablename = customer
 * mapping   = $dbmodel/$tablename.map    # java <-> db mapping
 * relations = $dbmodel/$tablename.rel    # object relations
 * @}
 */

/**
 * @> $mapping
 * # mapping for Customer
 * #
 * # create unique index ${tablename}_no on $tablename (customer_no)
 *
 * String     0     normText        normtext          normalized search string [nomethod, nodeclare]
 * String     10    customerNumber  customer_no       unique customer number
 * String     255   address         address           customer name and address
 * @<
 */

The attribute normText is predefined like id and serial. It is optional and contains a normalized search string. By normalizing a string we are able to find objects even if the query is not spelled 100% correctly (for example Maier vs. Meyer). Furthermore, if the normtext includes more than one attribute of the object, we can search for a string in all of those attributes simultaneously without the need to specify which. Tentackle provides a search dialog, that defaults to a normtext query. So, at least for master data, it is always a good idea to use the normtext feature. Simply add the normText-line in the mapping (note the two options!) and overwrite the following method:

/**
  * Prepares the fields before setFields().
  *
  * @return true if preparation done, false if some error.
  */
@Override
public boolean prepareSetFields() {
  setNormText(StringHelper.normalize(getCustomerNumber() + "," + getAddress()));
  return true;
}

The toString()-method should simply return getCustomerNumber(). For your exercise, please create InvoiceLine and Product similarly.

Notes

• For larger applications it is strongly recommended to introduce application-specific datatypes. For example, the customerNumber is a String with a length of 10 characters. In the file cg/codegen.properties you can easily define such types as properties (e.g. CodeNumber=String 10) and then reference these types in the model (e.g. $CodeNumber customerNumber customer_no unique customer number). Whenever you change a type definition in codegen.properties, the corresponding mappings will be automatically changed as well. For this tutorial, however, we will not use application-specific types.
• The project's state at the end of chapter is archived in snapshot2.zip. As an alternative download update2.zip which will only contain the changed files and thus will not overwrite any changes you already made to some configuation files (for example using postgres instead of mysql).
  For each chapter you will find such a snapshot- and update-archive to get your project in sync with the tutorial.

... next step