Universal DAO

Universal DAO provides a simple O/R mapper that uses Jakarta Persistence (external site) annotations.

Since Universal DAO uses a Database Access (JDBC Wrapper), the Database Access (JDBC Wrapper) must be configured to use Universal DAO.

Tip

Universal DAO is positioned as a simple O/R mapper and is not intended to realize all database access. Perform Database Access (JDBC Wrapper) operations directly if it cannot be realized with Universal DAO.

For example, in Universal DAO, update/delete with conditions other than the primary key cannot be performed, so Database Access (JDBC Wrapper) operations must be performed directly.

Tip

Universal DAO does not provide an automatic configuration function for common items (insert users, update users, etc. defined in all tables). To automatically configure values for common items, apply Doma Adapter and use Doma entity listener function.

To use Universal DAO in any case, configure the common items explicitly in the application before using the functions of Universal DAO.

Function overview

Simple CRUD without writing SQL

Just by adding Jakarta Persistence annotation to Entity, a simple CRUD can be created without writing SQL. SQL statements are constructed at runtime based on Jakarta Persistence annotations.

  • Registration/Batch registration
  • Update/Batch update by specifying the primary key
  • Delete/Batch delete by specifying the primary key
  • Search by specifying the primary key

For Jakarta Persistence annotations that can be used in Entity, see Jakarta Persistence annotation that can be used for Entity.

Tip

In the above CRUD function of Universal DAO, schema can be specified using @Tableannotation (see Jakarta Persistence annotation that can be used for Entity). However, Switch the schema in SQL statement for each environment function of Database Access (JDBC Wrapper) cannot be used with the above CRUD function of Universal DAO. Use the Database Access (JDBC Wrapper) directly instead of Universal DAO for switching the schema for each environment.

Search results can be mapped to Bean

In search, as with Database Access (JDBC Wrapper), you can create an SQL file and perform a search by specifying the SQL ID. In Universal DAO, search results can be obtained by mapping them to Bean (Entity, Form, DTO). Map the items whose Bean property name matches the SELECT clause name.

For data types that can be used for Bean, see Data types that can be used for Bean.

Module list

<dependency>
  <groupId>com.nablarch.framework</groupId>
  <artifactId>nablarch-common-dao</artifactId>
</dependency>

How to use

Important

See nablarch.common.dao.UniversalDao for the basic usage of Universal DAO.

Configure settings to use Universal DAO

To use universal DAO, add BasicDaoContextFactory configuration to component definition in addition to Database Access (JDBC Wrapper) configuration.

<!-- Configure the component name in "daoContextFactory". -->
<component name="daoContextFactory" class="nablarch.common.dao.BasicDaoContextFactory" />

Search with any SQL (SQL file)

To search using SQL, create an SQL file and search by specifying the SQL ID, similar to Manage SQL in files of database access.

UniversalDao.findAllBySqlFile(User.class, "FIND_BY_NAME");

SQL file is derived from Bean that maps the search results. When User.class in the above example is sample.entity.User, the path of the SQL file is sample/entity/User.sql under the class path.

If “#” is included in the SQL ID, it is interpreted as “SQL file path#SQL ID”. In the example below, the SQL file path is sample/entity/Member.sql under the class path, and the SQL ID is FIND_BY_NAME.

UniversalDao.findAllBySqlFile(GoldUser.class, "sample.entity.Member#FIND_BY_NAME");

Tip

The specification including “#” can be used when SQL is to be aggregated in function units (action handler units). However, since there is a disadvantage that the specification becomes complicated, basically use the specification without “#”.

Obtain search results using JOIN of tables

Sometimes the result of JOIN of multiple tables in list search is required to be acquired. Since it is inefficient in such cases, create a Bean that maps the SQL that can be searched once and JOIN results, without individually searching the JOIN target data.

Deferred loading of search results

The memory becomes insufficient in processes that handle large amounts of search results, and all search results cannot be expanded in the memory. Some of the cases are as follows.

  • Download large amounts of data on the Web
  • Process large amounts of data in batch

In such cases, use deferred loading of Universal DAO. When deferred loading is used, Universal DAO loads the records one by one, but the amount of memory used changes depending on the JDBC fetch size. For details of the fetch size, refer to the manual provided by the database vendor.

Deferred loading can be used by just calling the UniversalDao#defer method first during search. since server cursor is used internally, DeferredEntityList#close method must be called.

// Call close using try-with-resources.
// Get DeferredEntityList by downcast.
try (DeferredEntityList<User> users
        = (DeferredEntityList<User>) UniversalDao.defer()
                                        .findAllBySqlFile(User.class, "FIND_BY_NAME")) {
    for (User user : users) {
        // Process using the user
    }
}

Important

Depending on the RDBMS used, if transaction control is performed while a cursor is open, the cursor will be closed. Note that this may result in an error when transaction control is performed during processing of large amounts of data using lazy loading, as it may refer to a cursor that has already been closed. Avoid this by adjusting the cursor behavior according to the manual provided by the database vendor, or by paging to avoid handling large amounts of data.

Searching by specifying the conditions

Like the search screen, Universal DAO also provides a search with specified conditions.

// Get the search conditions
ProjectSearchForm condition = context.getRequestScopedVar("form");

// Search by specifying the conditions
List<Project> projects = UniversalDao.findAllBySqlFile(
    Project.class, "SEARCH_PROJECT", condition);

Important

For the search condition, specify a dedicated Bean that has the search condition instead of Entity. However, Entity may be specified when accessing only one table.

Convert type

In Universal DAO, @Temporal can be used to specify how to map java.util.Date and java.util.Calendar type values to the database. Since arbitrary mapping is not possible for other types, Entity properties must be defined according to the database type and specifications of the JDBC driver to be used.

Though Universal DAO uses Jakarta Persistence annotation information when sending automatically generated SQL to the DB, Jakarta Persistence annotation information is not used when sending arbitrary SQL to the DB. Therefore, the type conversion is as follows.

When executing SQL automatically generated from the Entity
During output to a database
  • For properties configured with @Temporal, converts to the type specified in @Temporal.
  • For other than above, conversion is performed by delegating the process to the Database Access (JDBC Wrapper).
When fetching from database
  • For properties configured with @Temporal, converts from the type specified in @Temporal.
  • For other than the above, values are converted based on Entity information.
When searching with an arbitrary SQL
During output to a database
When fetching from database
  • Perform the same process as when executing SQL automatically generated from the Entity.

Important

If the database type and property type do not match, a type conversion error may occur during runtime. In addition, implicit type conversion is performed during SQL execution, which may cause performance degradation (caused as index is not used).

To map between database and Java data type, refer to the JDBC driver manual as it depends on the product used.

For example, if the DB is a date type, the property type is java.sql.Date in many databases. If the DB is a numeric type (integer, bigint, number), the property type will be int (java.lang.Integer) or long (java.lang.Long).

Paging

Universal DAO Search supports paging. First call the UniversalDao#per method and UniversalDao#page method for paging at the time of search.

EntityList<User> users = UniversalDao.per(3).page(1)
                            .findAllBySqlFile(User.class, "FIND_ALL_USERS");

Information such as the number of search results required for displaying the paging screen is stored in Pagination. Pagination can be obtained from EntityList.

Pagination pagination = users.getPagination();

Tip

Search process for paging is performed using range specified search function of database access (JDBC wrapper).

Tip

In paging, the number acquisition SQL is issued before the actual acquisition process of the range specified records. If performance degradation occurs due to the number acquisition SQL, change the number acquisition SQL as necessary by referring to Change the number acquisition SQL for the paging process.

Numbering the surrogate keys

When numbering the surrogate keys, use the following annotations.

Universal DAO supports all strategies of jakarta.persistence.GenerationType.

GenerationType.AUTO

@Id
@Column(name = "USER_ID", length = 15)
@GeneratedValue(strategy = GenerationType.AUTO)
public Long getId() {
    return id;
}
  • Select the numbering method based on Dialect configured in the database function. The priority is in the order IDENTITY → SEQUENCE → TABLE.
  • If SEQUENCE is selected, the sequence object name will be “<table name>_<column name to be numbered>”.
  • To specify the sequence object name, use @SequenceGenerator.
GenerationType.IDENTITY

@Id
@Column(name = "USER_ID", length = 15)
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long getId() {
    return id;
}
GenerationType.SEQUENCE

@Id
@Column(name = "USER_ID", length = 15)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
@SequenceGenerator(name = "seq", sequenceName = "USER_ID_SEQ")
public Long getId() {
    return id;
}
  • Specify the sequence object name with @SequenceGenerator.
  • If the sequenceName attribute is omitted, it will be “<table name>_<column name to be numbered>”.
GenerationType.TABLE

@Id
@Column(name = "USER_ID", length = 15)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "table")
@TableGenerator(name = "table", pkColumnValue = "USER_ID")
public Long getId() {
    return id;
}
  • Specify the value that identifies a record with @TableGenerator.
  • If the pkColumnValue attribute is omitted, it will be “<table name>_<column name to be numbered>”.

Tip

Numbering process of surrogate key using sequence and table is performed using Surrogate Key Numbering. Refer to the link destination for configuring the value (configuring the table and column names when using a table).

Perform batch execution (batch registration, update and deletion)

Universal DAO allows batch execution when registering, updating, or deleting large amounts of data. By performing batch execution, the number of round trips between the application server and database server can be reduced, and improvement in performance can be expected.

Batch execution uses the following methods.

Important

Exclusive control processing is not performed in the batch update processing that uses batchUpdate. If the version of the Entity to be updated and the version in the database do not match, the process ends normally without updating the record.

If the update processing requires exclusive control, call the update process for each record instead of batch update.

Optimistic locking

Universal DAO automatically performs optimistic locking when an Entity with @Version is updated. Throws jakarta.persistence.OptimisticLockException if an exclusive error occurs in optimistic locking.

Important

@Version can be specified only for numeric type properties. It does not work properly with string type properties.

Screen transition during an exclusive error is performed using OnError.

// Specify the target exception in the type attribute and transition destination path
// in the path attribute.
@OnError(type = OptimisticLockException.class,
         path = "/WEB-INF/view/common/errorPages/userError.jsp")
public HttpResponse update(HttpRequest request, ExecutionContext context) {

    UniversalDao.update(user); // Before and after processing is omitted.

}

Important

Note that optimistic locking cannot be used in batch update process (batchUpdate) as described in Perform batch execution (batch registration, update and deletion).

Pessimistic locking

Universal DAO does not particularly provide the pessimistic locking function.

Pessimistic locking is done by using the database row locking (select for update). SQL with the row lock (select for update) executes using the UniversalDao#findBySqlFile method.

Concept of exclusive control

It is necessary to decide from a business perspective the table in which the version column used for exclusive control has to be defined.

Tables with version numbers are defined for each unit of exclusive control and the largest unit in which conflicts are allowed. For example, if business allows locking in a large unit called “user”, a version number is defined in the user table. However, note that the possibility of conflict increases if the unit is increased, and update failure (in the case of optimistic locking) and processing delay (in the case of pessimistic locking) will occur.

Register (update) binary data with a large data size

You may want to register (update) binary data with a large data size, such as BLOB of Oracle. In the case of Universal DAO, since registration (update) cannot be performed unless all data is loaded into memory, use the functions provided by the database to register (update) directly from a file.

For details, see Accessing columns of binary type.

Registration (update) of text data with a large data size

You may want to register (update) text data with a large data size, such as CLOB of Oracle. In the case of Universal DAO, since registration (update) cannot be performed unless all data is loaded into memory, use the functions provided by the database to register (update) directly from a file.

For details, see Access columns of string type with a large number of digits (e.g. CLOB).

Execute in a transaction different from the current transaction

This section explains how to perform the same process as Execute SQL in a transaction different from the current transaction of Database Access (JDBC Wrapper) with universal DAO.

The following procedures are required to use individual transactions.

  1. Define SimpleDbTransactionManager in the component configuration file.
  2. Use SimpleDbTransactionManager to execute Universal DAO in a new transaction.

An usage example is shown below.

Component configuration file

Define SimpleDbTransactionManager in the component configuration file.

<component name="find-persons-transaction"
    class="nablarch.core.db.transaction.SimpleDbTransactionManager">

  <!-- Configure ConnectionFactory implementation class in the connectionFactory property -->
  <property name="connectionFactory" ref="connectionFactory" />

  <!-- Configure TransactionFactory implementation class in the transactionFactory property -->
  <property name="transactionFactory" ref="transactionFactory" />

  <!-- Configure a name to identify the transaction -->
  <property name="dbTransactionName" value="update-login-failed-count-transaction" />

</component>
Implementation examples

Use SimpleDbTransactionManager configured in the component configuration file and execute universal DAO. In addition, instead of using SimpleDbTransactionManager directly, use UniversalDao.Transaction to perform transaction control.

First, create a class that inherits UniversalDao.Transaction.

private static final class FindPersonsTransaction extends UniversalDao.Transaction {

    // Prepare a container to receive the result.
    private EntityList<Person> persons;

    FindPersonsTransaction() {
        // Specify SimpleDbTransactionManager as super ().
        // The name specified in the component definition or the SimpleDbTransactionManager object can be specified.
        // In this example, the name mentioned in the component definition is specified.
        super("find-persons-transaction");
    }

    //This method is automatically executed in another transaction.
    // If the process is completed successfully, the transaction is committed.
    // If an exception or error is thrown, the transaction is rolled back.
    @Override
    protected void execute() {
        // Implement the process using UniversalDao in the execute method.
        persons = UniversalDao.findAllBySqlFile(Person.class, "FIND_PERSONS");
    }

    // Prepare getter that returns the result.
    public EntityList<Person> getPersons() {
        return persons;
    }
}

Then, call the class that inherits UniversalDao.Transaction.

// Executed in a different transaction after generation.
FindPersonsTransaction findPersonsTransaction = new FindPersonsTransaction();

// Acquire the result.
EntityList<Person> persons = findPersonsTransaction.getPersons();

Expansion example

Support when information cannot be obtained from DatabaseMetaData

Depending on the database, the primary key information cannot be acquired from java.sql.DatabaseMetaData due to the use of synonyms or permission problems. If the primary key information cannot be acquired, the search that specifies the primary key does not work properly. For such cases, support by creating an inherited class of DatabaseMetaDataExtractor. Refer to the product manual as the method to acquire the primary key information depends on the database.

Configuration is required to use the created class.

<!--
Configuration example when sample.dao.CustomDatabaseMetaDataExtractor is created
Configure the component name as "databaseMetaDataExtractor".
-->
<component name="databaseMetaDataExtractor" class="sample.dao.CustomDatabaseMetaDataExtractor" />

Change the number acquisition SQL for the paging process

In paging, the number acquisition SQL is issued before the actual acquisition process of the range specified records. By default, the number acquisition SQL is the original SQL wrapped in a SELECT COUNT(*) FROM . If the original SQL has a heavy processing load, such as containing a ORDER BY clause, and you want to remove the ORDER BY clause to reduce the load, you can customise the dialect you are using and change the number acquisition SQL.

Important

The number acquisition SQL must have the same search conditions as the original SQL. When preparing the number acquisition SQL, make sure that there is no difference in the search conditions between the two.

If you want to change the number acquisition SQL, inherit the dialect used in your project and then change Dialect#convertCountSql(String, Object, StatementFactory) implementation.

Implementation examples

Below is an example of customising nablarch.core.db.dialect.H2Dialect. In this example, the mapping between the original SQL and the number acquisition SQL is set in the component and the number acquisition SQL is changed.

Tip

Appropriate mapping rules should be considered for each project.

public class CustomH2Dialect extends H2Dialect {

    /**
     * The mapping of the number acquisition SQL
     */
    private Map<String, String> sqlMap;

    /**
     * {@inheritDoc}
     *
     * If the SQLID corresponding to {@code sqlId} exists in the mapping of the number acquisition SQL,
     * it is returned as the number acquisition SQL.
     */
    @Override
    public String convertCountSql(String sqlId, Object params, StatementFactory statementFactory) {

        if (sqlMap.containsKey(sqlId)) {
            return statementFactory.getVariableConditionSqlBySqlId(sqlMap.get(sqlId), params);
        }

        return convertCountSql(statementFactory.getVariableConditionSqlBySqlId(sqlId, params));
    }

    /**
     * Set the mapping of the number acquisition SQL.
     *
     * @param sqlMap The mapping of the number acquisition SQL
     */
    public void setSqlMap(Map<String, String> sqlMap){
        this.sqlMap = sqlMap;
    }
}

The customised dialect must be set in the component configuration file. Below is an example of how a customised dialect can be configured in a component configuration file. In this example, the mapping of the number acquisition SQL is set with the <property> element.

<component name="dialect" class="com.nablarch.example.app.db.dialect.CustomH2Dialect">
  <property name="sqlMap">
    <map>
      <entry key="com.nablarch.example.app.entity.Project#SEARCH_PROJECT"
             value="com.nablarch.example.app.entity.Project#SEARCH_PROJECT_FORCOUNT"/>
    </map>
  </property>
</component>

Jakarta Persistence annotation that can be used for Entity

Jakarta Persistence annotations that can be used for entity are as follows.

Important

Using annotations and attributes that are not described here will not work.

When configuring in a field, specify explicitly with @Access. Refers to the field annotation only when explicitly specified with @Access.

Even when configuring an annotation in a field, since acquiring and configuring values are performed through properties in UniversalDao, getters and setters must be created.

Since the field and property are linked by name, if the names are different, the annotation of the field cannot be referenced by the property. Therefore, be sure to use the same field and property names (getXX, setXX).

Tip

For example, when using a library that generates boilerplate code such as Lombok, take full advantage of the library as configuring the annotation on the field eliminates the need to create a getter.

jakarta.persistence.Entity

This annotation is configured in the Entity class corresponding to the database table.

When this annotation is configured, the table name is derived from the class name. The table name is the value obtained by converting the class name (Pascal case) into the snake case (all uppercase).

Book class        -> BOOK
BookAuthor class  -> BOOK_AUTHOR

Tip

If the table name cannot be derived from the class name, specify the table name explicitly using @Table that is described later.

jakarta.persistence.Table

This annotation is used to specify the table name.

If a value is specified in the name attribute, that value will be used as the table name. If a value is specified in schema attribute, access the table by specifying the specified schema name as a qualifier. For example, when work is specified in the schema attribute and the table name is users_work, work.users_work is accessed.

jakarta.persistence.Access

This annotation is used to specify the location to configure the annotation.

Refers to the field annotation only when explicitly specified in the field.

jakarta.persistence.Column

This annotation is used to specify the column name.

If a value is specified in the name attribute, that value will be used as the column name.

Tip

If this annotation is not set, the column name is derived from the property name. The derivation method is the same as the derivation method used for the table name. For details, see @Entity.

jakarta.persistence.Id

This annotation is configured in the primary key.

In the case of a compound primary key, configure this annotation to multiple getters or fields.

jakarta.persistence.Version

This annotation is configured in the version column used for exclusive control.

This annotation can be specified only for numeric type properties. It does not work properly with string type properties.

When this annotation is set, the version column is automatically added to the condition during update processing, and optimistic locking is performed.

Tip

Only one annotation can be specified in the Entity.

jakarta.persistence.Temporal

This annotation specifies how to map the values of java.util.Date and java.util.Calendar types to the database.

Converts the value of the Java object to the database type specified in value attribute and registers it in the database.

jakarta.persistence.GeneratedValue

This annotation indicates that the automatically numbered value is registered.

Configures the numbering method to the strategy attribute. When AUTO is configured, the numbering method is selected according to the following rules.

  • If there is a Generator configuration corresponding to the generator attribute, performs the numbering process using that Generator.
  • If generator is not configured or there is no corresponding Generator configuration, select the numbering method based on Dialect configured in the database function. The priority is in the order IDENTITY → SEQUENCE → TABLE.

Configure an arbitrary name in the generator attribute.

Tip

If @GeneratedValue cannot be used to acquire the sequence object name for sequence numbering or the value that identifies the record for table numbering, derive each value from the table name and column name to be automatically numbered.

Table name "USER", Column name to be numbered "ID" -> USER_ID
jakarta.persistence.SequenceGenerator

This annotation has to be configured when using sequence numbering.

In the name attribute, configure the same value as the generator attribute of @GeneratedValue.

Configure the sequence object name created in the database to the sequenceName attribute.

Tip

The numbering function is used to perform sequence numbering. For this reason, numbering configuration must be performed separately.

jakarta.persistence.TableGenerator

This annotation is configured when using table numbering.

In the name attribute, configure the same value as the generator attribute of @GeneratedValue.

In the pkColumnValue attribute, configure a value to identify the record in the numbering table.

Tip

The numbering function is used to perform table numbering. For this reason, numbering configuration must be performed separately.

Data types that can be used for Bean

Data types that can be used in Bean, which maps search results, are as follows.

Important

Search results cannot be mapped to data types not listed here (runtime exception is thrown).

java.lang.String
java.lang.Short
Primitive types can also be specified. For primitive types, null is handled as 0.
java.lang.Integer
Primitive types can also be specified. For primitive types, null is handled as 0.
java.lang.Long
Primitive types can also be specified. For primitive types, null is handled as 0.
java.math.BigDecimal
java.lang.Boolean
Primitive types can also be specified. For primitive types, null is handled as false. In the case of wrapper type (Boolean), the read method name must start with get. In the case of a primitive type, the read method name may start with ‘is’.
java.util.Date
The data type has to be specified in the database with @Temporal of Jakarta Persistence.
java.sql.Date
java.sql.Timestamp
java.time.LocalDate
java.time.LocalDateTime
byte[]

Be careful not to expand the data in the heap by using this function for the value of data type of very large size such as BLOB. When handling very large binary data, use database access directly and refer to the data through Stream.

For details, see Accessing columns of binary type.