🌐 English · Translate this article… ← Kembali ke Main Page
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:
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.
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:
c_bp_group table. This class has type-safe getters and setters for all the data in this table.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:
list method performs the actual query; it returns a type-safe list of the requested objects.The architecture for the Data Access Layer in InfiniteERP is implemented as follows:
The complete architecture runs inside of a context which provides security and transaction handling.
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.
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.
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:
The OBDal API makes extensive use of the OBCriteria and [OBQuery](/#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.
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:
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();
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();
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);
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:
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.getId method.getIdentifier method of the BaseOBObject uses the identifier properties of the object to create a displayable title for that object.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.
The Data Access Layer uses names defined in the Application Dictionary for different purposes:
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.
An entity (corresponds to a table in InfiniteERP) has different names, relevant for different situations:
AD_Table.tablename) which is the database table name in the physical database.AD_Table.name) which is a globally unique name. It corresponds to the XML tag name used for that entity.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).
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:
AD_Column.name is used as the start of the property name calculation.OrderLineList in the Order entity).Next, the property name generation performs the following steps:
EnableInCash).CPocEmailID).AccountingFact → accountingFact).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:
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.getOrganization/setOrganization methods. InfiniteERP automatically detects this if it has a column with the name organization.isActive/setActive methods. InfiniteERP automatically detects this if it has a column with the name active.creationDate, created, updated, updatedBy.The above interfaces are used by the DAL when an object is saved for the first time or updated in the database:
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.
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:
newOBObject is set to true explicitlySo, 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.
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.
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).
The user context can also be set by calling OBContext.setOBContext(userId) with a userId which exists in the ad_user table.
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.
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.
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.
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 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:
AD_Window_Access table.If any of the above checks fails, then a SecurityException is thrown.
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:
A user may delete a business object if:
AD_Table.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 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](/#Administrator_Mode).
The DAL supports views in practically the same way as normal tables defined in the Application Dictionary. This means that:
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.
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.
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.
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:
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.
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
}
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:
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.
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();
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:
DalInitializingTask.doExecute method instead of the execute method.build.xml): * ``propertiesFile="${base.config}/Openbravo.properties"
* ``userId="100"
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.
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.
Hibernate will detect that a business object is new when:
newOBObject is set to true explicitlySo 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.
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.
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.
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](/Java_Coding_Conventions) for more information.
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.