Data Access Layer

Languages:

English| | Translate this article...

Back to Main Page

Data Access Layer

The goal of the InfiniteERP Data Access Layer (DAL) development is to strengthen the middle-tier in the application, i.e. to implement business logic in Java. The DAL provides the application developer with the following functionality:

• Type-safe querying and retrieval of business objects from the database
• A convenient API to update or create new data in the database
• A type-safe interface to update information of a business object, increased productivity by making the properties of a business object directly visible through getters and setters (in the IDE)
• Transaction and context handling
• Security and validation checking
• Automatically maps new entries in the Application Dictionary to database tables and columns
• Generates Java class business objects (and their associations) on the basis of the Application Dictionary model

The DAL consists of a development-time and runtime part. The development-time part takes care of generating Java business object classes. The runtime part takes care of mapping Java classes to the database and supporting functionality such as security and validation.

A "Hello World" Example

As a first simple example, let's create a new business partner group and store it in the database:

// create the object through the factory final Category bpg = OBProvider.getInstance().get(Category.class);

// set some values bpg.setDefault(true); bpg.setDescription("hello world"); bpg.setName("hello world"); bpg.setValue("hello world"); bpg.setActive(true);

// store it in the database OBDal.getInstance().save(bpg);

There are a number of things which are important to note:

• The above code does not set an explicit user context. The user context is set automatically when running the above code in InfiniteERP. However, in other environments, it has to be set explicitly — see User Context for more information.
• There is a BPGroup class which models the data of the c_bp_group table. This class has type-safe getters and setters for all the data in this table.
• A factory (the OBProvider) is used to create an instance of the BPGroup class.
• The OBDal service is the main entry point into the Data Access Layer; it offers save, remove and query functionality.

The code snippet above also shows that you do not need to work with SQL or JDBC to work with the data from the database. As a developer, you work directly with objects and the available data is directly visible through the getters and setters.

As a next step, let's query for the business partner group and change its description:

// create an OBCriteria object and add a filter final OBCriteria<Category> obCriteria = OBDal.getInstance().createCriteria(Category.class); obCriteria.add(Restrictions.eq("name", "hello world"));

// perform the actual query returning a typed list final List<Category> categories = obCriteria.list(); final Category cat = categories.get(0);

// and set a new name cat.setName("another hello world"); OBDal.getInstance().save(cat);

This code snippet introduced a number of new concepts:

• The OBDal service is used to create an OBCriteria object.
• The OBCriteria object represents the query, implements the Hibernate Criteria interface and can be used as a standard Hibernate Criteria object. The OBCriteria object also supports sorting and paging parameters.
• The OBCriteria list method performs the actual query; it returns a type-safe list of the requested objects.
• After changing the name of the business partner group, you don't need to do an explicit save. At commit time, Hibernate will automatically detect dirty objects and save those.

DAL Architecture

The architecture for the Data Access Layer in InfiniteERP is implemented as follows:

• Runtime model: the runtime model is the main driver for generating the business objects and the Hibernate mapping. It is also used extensively in security, export/import and in webservices implementations.
• Hibernate Mapping: from the runtime model, the DAL (during initialization) generates a Hibernate mapping. This Hibernate mapping is used to initialize Hibernate.
• Database Schema: the runtime model (actually the Application Dictionary) can be used to update the database schema. This is not available as part of the DAL but as part of the DBSourceManager product.
• Data Access Layer: the DAL provides an API to store, query and remove business objects from the database.
• Business Model/Logic Layer: the business model/logic layer contains the implementation of the business processes.
• Business Services: the service layer exposes the business logic to the outside world.

The complete architecture runs inside of a context which provides security and transaction handling.

Business Object

This document uses the term business object to denote an entity and its dependent information. A business object can be a simple entity such as a currency which just has basic primitive fields. On the other hand, it can also be a structure of entities, for example an order header with its order line.

A business object structure always has one business object which is the owner of all the business objects in that structure. For example, for a sales order the sales order header business object is the owner of the complete structure (i.e., of the sales order lines). A sales order line cannot exist without its sales order header, and when a sales order header is removed, its sales order lines should also be removed.

The DAL uses the foreign keys from the child to parent to create the parent-child association in Java and Hibernate. More specifically: the foreign key columns which have the field isParent set to yes (checked/true) define the business object parent-child relations; other foreign key columns define standard many-to-one associations. For example, the foreign key field c_order_id in c_order_line is used to create the one-to-many association (in the in-memory runtime model) from c_order to c_order_line. This one-to-many association is then used to generate a List member in the Java Order class and a one-to-many mapping in Hibernate.

Note: Generated one-to-many properties in the parent entity load all children in memory when invoked, so they should only be generated when the expected amount of child records is low, otherwise it might cause an OutOfMemoryError.

DAL Main Interfaces

The DAL offers three main services to instantiate, create and query InfiniteERP business objects: OBDal, OBCriteria and OBProvider. The service classes can all be found in the org.openbravo.dal.service package.

OBDal

The OBDal instance (available through OBDal.getInstance()) is the main entrance point for retrieving and storing business objects in the database in a validated and secure way. It provides the following functions:

• save: stores a new business object in the database or updates an existing business object. For existing business objects, it is not required to call this method as Hibernate does automatic dirty checking.
• get: retrieves a single business object using its ID. There are two versions: one using the class name (of the generated business object) and one using the entity name.
• remove: delete a business object from the database; the actual database delete is done at commit time.
• create OBCriteria: OBCriteria objects are used for querying.
• commitAndClose and rollbackAndClose: these methods can be used to implement custom transaction handling. Normally, this is done by the environment (InfiniteERP web container or InfiniteERP test).

The OBDal API makes extensive use of the OBCriteria and OBQuery classes to support querying.

The OBDal class allows accessing the read-only database (pool). In this case, the instance must be retrieved with the OBDal.getReadOnlyInstance() method.

OBCriteria

The OBCriteria class implements the Hibernate Criteria interface. It extends the standard Hibernate Criteria functionality for filtering on active, client and organization. In addition, it offers convenience methods to set order by and to perform count actions.

Summarizing, the OBCriteria object supports all Hibernate Criteria features:

• Setting the where-clause of a filter using the Hibernate Criterion concept
• Setting paging parameters such as first row and maximum number of results
• Setting order by on the query
• Specifying joins for performance reasons
• Performing counts, average, etc.

An OBCriteria instance is created as follows:

final OBCriteria<Currency> obc = OBDal.getInstance().createCriteria(Currency.class);

Query on the name property:

obc.add(Restrictions.eq("name", "testname"));

Restrictions.eq is an instance of the Hibernate Criterion class. The Hibernate Criterion concept is an expression language coded in Java supporting most commonly used expressions (and, or, equal, not-equal, in, between, etc.).

Setting a descending order by on the name property:

obc.addOrderBy("name", false);

Or another one: order by the name property of a referenced business object:

obc.addOrderBy("product.name", false);

Set paging parameters, return 10 objects, beginning with the 100th:

obc.setFirstResult(100); obc.setMaxResults(10);

Also return inactive objects (as a default, only active objects are returned):

obc.setFilterOnActive(false);

Count the number of Currency objects in the database:

final int count = obc.count();

OBQuery

The OBQuery class is an extension of the Hibernate Query object. It extends the standard Hibernate Query functionality for filtering on active, client and organization.

The OBQuery object is created through the OBDal.createQuery method. The first argument of createQuery is a class or an entity name, the second argument is the where-clause.

final OBQuery<Category> obQuery = OBDal.getInstance().createQuery(Category.class, "name='testname' or searchKey='testvalue'"); final List<Category> bpgs = obQuery.list();

OBProvider

InfiniteERP business objects should not be instantiated directly using the new operator. Instead, the OBProvider class should be used to create an instance of the required business object. The OBProvider is located in the org.openbravo.base.provider package and can be retrieved using the method OBProvider.getInstance().

final Category bpg = OBProvider.getInstance().get(Category.class);

// The ENTITYNAME constant is created by the business object generation logic final BPGroup bpg = (BPGroup) OBProvider.getInstance().get(BPGroup.ENTITYNAME);

BaseOBObject

All InfiniteERP business objects inherit from the BaseOBObject class. This class is located in the org.openbravo.base.structure package.

The BaseOBObject class offers the following functionality:

• Direct access (called the dynamic API) to all the properties and values of the data within the business object through the get(String propertyName) and set(String propertyName, Object value) methods. These methods are particularly useful when working with generic functions such as security and logging.
• Access to the Entity describing the type and properties of the business object.
• Security and validation checks when getting and setting values.
• Access to the ID of the object through the getId method.
• Access to the identifier of the object: the getIdentifier method of the BaseOBObject uses the identifier properties of the object to create a displayable title for that object.

Generated Business Object Classes

At development time, the DAL will generate business object classes for each table defined in the Application Dictionary. This is done as part of the compile.complete Ant task or can be done separately through the generate.entities Ant task. These generated classes are the classes which are normally used by a developer because they offer compile-time-checked typed access to properties.

The generated classes extend BaseOBObject and offer typed wrapper getters and setters around the generic set and get method of the parent BaseOBObject class:

public String getName() {

   return (String) get("name");

}

public void setName(String name) {

   set("name", name);

}

In addition, the generated Java classes set default values (in the constructor) and have a static ENTITYNAME variable which must be used when it is required to refer directly to an entity name of an entity.

Entity, Property and Column Naming

The Data Access Layer uses names defined in the Application Dictionary for different purposes:

• XML tag names in REST web services and import/export
• Class names of generated Entity classes
• Member names of members of generated Entity classes
• To detect that a certain Entity implements/supports a certain interface

The Application Dictionary historically allows many different types of names (also ones that are illegal for XML/Java). Therefore, the data access layer applies specific conversion logic to always ensure that names are allowed and unique for XML/Java.

Entity Naming

An entity (corresponds to a table in InfiniteERP) has different names, relevant for different situations:

• A table name (stored in AD_Table.tablename) which is the database table name in the physical database.
• An entity name (present in AD_Table.name) which is a globally unique name. It corresponds to the XML tag name used for that entity.
• A Java class name (stored in AD_Table.classname), the class name is used when generating the Java business object. It is unique within the data package of the table.

Each table (i.e., entity) in InfiniteERP belongs to a data package. A data package has a Java package field which defines the Java package in which the entity Java class is generated.

Warning: AD_Table.name should not contain blank spaces. If the name of the table contains spaces, the process of building the entity name will remove those spaces (e.g., My Table will be converted to MyTable).

Property Naming

For property naming, the logic has two distinct steps:

1. First determine an initial property name, and 2. Correct/convert this property name.

The initial property name is determined as follows:

• For standard primitive type (varchar, numeric, etc.) and foreign key columns: the value in AD_Column.name is used as the start of the property name calculation.
• For properties which model a list of child entities (one-to-many): if the column name on the other side (from the child to the parent) is the same as the primary key column name of the parent, then use the entity name of the child plus the suffix "List" (e.g., OrderLineList in the Order entity).

Next, the property name generation performs the following steps:

• Spaces are removed and used to camelcase (e.g., "Enable in Cash" → EnableInCash).
• Underscores are removed and used to camelcase (e.g., "C_Poc_email_ID" → CPocEmailID).
• "Illegal" characters are removed: only characters from a to z and A to Z and numbers (not as prefix) are maintained.
• The first character is lowercased (e.g., AccountingFact → accountingFact).

Important Interfaces

The generated classes implement a set of interfaces which can be used to check if a certain instance of a class has specific functionality available:

• [ClientEnabled]: flags an object as having a getClient/setClient method and as an object which is stored by Client. InfiniteERP automatically detects that a table implements this interface if it has a column with the name client.
• [OrganizationEnabled]: an object implementing this interface has getOrganization/setOrganization methods. InfiniteERP automatically detects this if it has a column with the name organization.
• [ActiveEnabled]: an object implementing this interface has an active flag (boolean) which can be reached through the isActive/setActive methods. InfiniteERP automatically detects this if it has a column with the name active.
• [Traceable]: a Traceable object has audit information: created and updated (date fields), and createdBy and updatedBy (contain a User). InfiniteERP automatically detects this if it has the following columns (all are required): creationDate, created, updated, updatedBy.

Client, Organization and Audit Information

The above interfaces are used by the DAL when an object is saved for the first time or updated in the database:

• A ClientEnabled object for which the Client is not set will get the current Client of the user (present in the user context).
• An OrganizationEnabled object for which the Organization is not set will get the current Organization of the user (present in the user context).
• A Traceable object: when saved for the first time, the created, createdBy, updated and updatedBy are set. When an object is updated, then the updated and updatedBy are set.

So, a developer does not need to explicitly set this information in a new or existing object.

Creating a New Instance of a Business Object

A business object may never be created using the Java new operator. All business objects should be created using the OBProvider factory class:

final Category bpg = OBProvider.getInstance().get(Category.class);

Hibernate will detect that a business object is new when:

• The ID of the business object is not set
• When the flag newOBObject is set to true explicitly

So, if you want to create a new business object with a specific ID (by calling setId(...)), then you explicitly need to call businessObject.setNewOBObject(true). Otherwise, Hibernate will throw an exception.

User Context

The DAL operates within a user context. The user context is implemented in the OBContext class in the org.openbravo.dal.core package. The OBContext is initialized using a userId. On the basis of this userId, the OBContext computes the role, clients, organizations and accessible entities. This information is used by the DAL for security checking and automatic filtering on client and organization.

The OBContext is stored as a ThreadLocal variable and the DAL always assumes that there is one available.

User Context in a Running InfiniteERP Instance

When the code runs inside an InfiniteERP application, the user context is always set. This is done through a request filter (the DalRequestFilter in the org.openbravo.dal.core package).

User Context in a Standalone Situation

The user context can also be set by calling OBContext.setOBContext(userId) with a userId which exists in the ad_user table.

Administrator Mode

The OBContext provides an Administrator Mode which can be used to perform administrative actions even if the user does not have enough privileges. This mode bypasses the Entity Access checking and it does not filter by Client or Organization.

An additional Administrator Mode is provided, which bypasses the Entity Access checking but does filter by Client Organization.

The syntax to activate the restricted Admin Mode:

try {

   OBContext.setAdminMode(true);

   // do administrative things here

} finally {

   OBContext.restorePreviousMode();

}

In some cases, it is necessary to prevent also client/organization check; this can be done using OBContext.setAdminMode(false).

Note: The calls to setAdminMode/restorePreviousMode are balanced, meaning that for each call to setAdminMode there is also exactly one call to restorePreviousMode.

Transaction and Session

The DAL implements the so-called open-session-view pattern. With a slight variation: the DAL will automatically create a Hibernate Session and start a transaction when the first data access takes place (if none existed). So not when the HTTP request begins. The Session and Transaction are placed in a ThreadLocal and re-used for subsequent data access actions in the same thread.

An important thing to be aware of: normally all database actions are flushed to the database when the session is committed. In a web environment, this is at the end of the HTTP request. To perform flush on demand, call the OBDal.getInstance().flush() method.

• Within InfiniteERP: the open-session-view pattern is used. When running the code in the InfiniteERP application, the transaction commit and session close take place at the end of the HTTP request. If an exception occurs, then a rollback is performed.
• In test environment: the DAL base test class takes care of committing or rolling back transactions.
• Standalone: if the code is running standalone, then an explicit commit or rollback needs to be performed. This can be done through the OBDal methods: OBDal.getInstance().commitAndClose() or OBDal.getInstance().rollbackAndClose().

Warning: SQLC and DAL: The standard InfiniteERP database access (through Windows) works outside of the DAL. This means that database access uses a different connection than the DAL. If both connections update the database, then it is possible that a deadlock situation happens. So, when working/updating through both the DAL and SQLC, one should always first commit/close the connection of the DAL explicitly before continuing with SQLC, or the other way around.

Security and Validation

The DAL performs many different security and validation checks, in both read and write mode. A security violation results in a SecurityException, a validation error in a ValidationException.

Write Access

Write access checks are done when the OBDal save or remove methods are called or when a business object is saved by Hibernate (at flush/commit). For write access, the following checks are performed:

• The user must have access to a window/tab which displays the entity. See the AD_Window_Access table.
• The organization of the business object must be in the list of writable organizations of that user.
• The client of the business object must be in the list of writable clients of that user.

If any of the above checks fails, then a SecurityException is thrown.

Read Access

A user can only view information from their own clients and accessible organizations. This is ensured by the DAL by automatically adding filter criteria in the OBCriteria object.

Read access is checked for both direct read access and derived read access:

• Direct read access: allows a user to see all the information of a certain entity. Based on the window access tables.
• Derived read access: only the active property, audit info and the ID and identifier may be read by a user. For example, if a user may directly read an invoice entity, and an invoice refers to a currency, then the user has derived read access to a currency.

Delete Check

A user may delete a business object if:

• They have write access to the entity of the business object.
• The entity is not set as not-deletable, this is defined in the AD_Table.

Validation

Property values are validated when the setter is called or the generic set method on the BaseOBObject is called. The following checks are performed:

• A check is done if the instance of the value is valid for that property
• For String values, a check is done on length or on the allowed String values (list/enumerate values)
• For numeric values, the min and max are checked
• For mandatory values, a check is done if the value is unequal to null

Entity Organization Validation

A business object may only refer to other business objects which belong to the natural tree of the organization of the business object. This validation is done when a business object is saved (i.e., when the session commits/flushes).

Cross Organization References: Entity Organization validation can be bypassed if the foreign key column that references to the other object is marked as Allow Cross Organization Reference and the context is in Cross Organization Reference Administrator Mode.

DAL Support for Database Views

The DAL supports views in practically the same way as normal tables defined in the Application Dictionary. This means that:

• Database views are considered as normal business objects
• Entities are generated for database views
• Database views can be queried using HQL and the DAL query APIs
• Database views can be accessed through the XML and JSON REST web service APIs

There is one difference between a database view and a database table: the DAL does not support updates on views; view business objects can be read and queried but not inserted or updated.

Note: For the DAL to consider a view as a business object, it needs to have a primary key column defined in the Application Dictionary. This column does not need to be a real primary key in the database but it must hold unique values for each record of the view.

SQL Functions in HQL

To use SQL Functions in HQL, you first must make sure that Hibernate knows about your function. So in your Java code, you have to register the function. This is done by creating a class that implements the SQLFunctionRegister interface and annotated as @ApplicationScoped.

@ApplicationScoped public class ExampleSQLFunctionRegister implements SQLFunctionRegister {

   @Override
   public Map<String, SQLFunction> getSQLFunctions() {
       Map<String, SQLFunction> sqlFunctions = new HashMap<>();
       sqlFunctions.put("ad_column_identifier_std", new StandardSQLFunction("ad_column_identifier_std", StandardBasicTypes.STRING));
       sqlFunctions.put("now", new StandardSQLFunction("now", StandardBasicTypes.DATE));
       return sqlFunctions;
   }

}

After registering the function, you can use it directly in an HQL query.

Executing Native SQL Queries

The DAL also allows the execution of native SQL queries:

String documentNo = (String) OBDal.getInstance().getSession()

   .createNativeQuery("SELECT documentNo FROM c_order WHERE c_order_id = :id")
   .setParameter("id", orderId)
   .uniqueResult();

Note: The createNativeQuery method accepts a second argument where the returning result type may be specified. But this is not supported due to a bug in Hibernate.

Runtime Model and the Dynamic API

The DAL makes the model (defined in the Application Dictionary) available at runtime. The runtime model is especially useful in generic functionality such as import/export, security and validation.

The runtime model consists of two main concepts:

• Entity: an entity models a database table and its associations to and from other tables. An entity has an EntityName which is globally unique.
• Property: a property corresponds to a column in the database. A property can be a primitive property (String, Date, numeric) or a reference to another entity.

The runtime model is available through the ModelProvider class (in org.openbravo.base.model) which can be retrieved by calling ModelProvider.getInstance().

The runtime model makes it possible to use model-driven development techniques also at runtime. For example, the runtime model together with the dynamic API offered by the BaseOBObject makes it possible to iterate through all properties (and their values) of a business object without knowing the exact type of the business objects.

Testing

It is of vital importance to follow a test-driven development approach for every development project. The Data Access Layer is tested using many JUnit test cases.

As a developer, you can make use of the same test infrastructure as the Data Access Layer test cases. The only thing you need to do is to let your test class inherit from the OBBaseTest class. The OBBaseTest class takes care of managing transactions, the context and initializing the DAL.

public void testMyStuff() {

   setTestUserContext();

   // do your test here

}

Calling Processes/Stored Procedures from the DAL

Sometimes, it makes sense to call a stored procedure from the DAL using the same database connection as it is being used by the DAL. For this purpose, the DAL includes two utility classes:

• [CallProcess]
• [Call Stored Procedure]

These classes make use of the same database connection as the DAL; in addition, instead of working with String parameters, you can work with the Java (primitive) objects directly.

The DalConnectionProvider

To access and make use of classic InfiniteERP code, it is often needed to have a ConnectionProvider object available. When combining DAL actions with classic InfiniteERP operations, it makes sense to use one overall database connection and commit all actions in one step.

To support this, the DAL provides a special ConnectionProvider implementation which makes use of the DAL database connection: the DalConnectionProvider.

ConnectionProvider cp = new DalConnectionProvider();

For those queries that want to use the read-only pool, the connection provider must be defined as follows:

ConnectionProvider cp = DalConnectionProvider.getReadOnlyConnectionProvider();

Using the Data Access Layer in an Ant Task

To ease the use of the DAL in Ant, a base Ant task class is offered by the DAL: the DalInitializingTask in the org.openbravo.dal.core package.

To make use of this class, the following changes need to be made to the Ant task and the custom Java Ant task implementation:

• The custom Ant task Java class should inherit from the DalInitializingTask.
• The custom Ant task Java class should implement a doExecute method instead of the execute method.
• Two additional properties are required in the Ant task definition (in the build.xml):

 * propertiesFile="${base.config}/Openbravo.properties"
 * userId="100"

Important Information

Hibernate Proxies

To improve performance of single-ended associations, the DAL makes use of the Hibernate proxy functionality. The Hibernate proxy functionality wraps an object inside a Hibernate proxy object. This is done at runtime using CGLIB. The Hibernate proxy object takes care of loading the business object when it is actually accessed. The advantage of this approach is that if an object is never accessed, then it is neither loaded, saving performance.

However, the Hibernate proxy is very visible when a developer debugs through an application because the instance of an object at runtime will not be the exact class (for example BPGroup) but an instance of a Hibernate proxy class.

Performance: Getting the ID of a BaseOBObject

In many cases, a developer just wants access to the ID or entity name of an object. To prevent loading of the business object when retrieving just this information, the DalUtil class in org.openbravo.dal.core offers an getId method and a getEntityName method. These methods work directly with the HibernateProxy object and do not load the underlying business object.

Creating a New Business Object with a Specific ID

Hibernate will detect that a business object is new when:

• The ID of the business object is not set
• When the flag newOBObject is set to true explicitly

So if you want to create a new business object with a specific ID (by calling setId(...)), then you explicitly need to call businessObject.setNewOBObject(true). Otherwise, Hibernate will not detect the business object as being new and throw an exception.

Coding Practices when Using/Extending the DAL

Exception Structure

All exceptions thrown by the DAL extend the base OBException class. The OBException is a RuntimeException so no explicit catch and throw statements are required.

When creating your own exception class, it is best to extend OBException so that you can make use of the standard logging capabilities.

Runtime Invariants: The Check Class

The Data Access Layer in various locations performs assertions or runtime invariant checks. To make implementing these type of checks more convenient, the DAL uses the Check class which is located in the org.openbravo.base.util package. The Check class offers methods to check for instanceof, isNull, isNotNull, etc.

Code Formatting

The source code, which is part of the Data Access Layer, is formatted using one formatting template. It is essential that when developing code in or using the Data Access Layer, that this same code format template is used. See Java Coding Conventions for more information.

See Also

• How to work with the Data Access Layer
• How to develop a DAL background process
• How to do a complex query using the DAL-1
• How to do a complex query using the DAL-2
• How to call a stored procedure from the DAL
• Java Coding Conventions
• Application Dictionary

References

• Data Access Layer — Etendo Documentation

---

This page is a derivative of Data Access Layer by Openbravo Wiki, used under CC BY-SA 2.5 ES. This work is licensed under CC BY-SA 2.5.