7.3.2. Universal DAO¶

7.3.2.1.1. Simple CRUD without writing SQL¶

Just by adding JPA annotation to Entity, a simple CRUD can be created without writing SQL. SQL statements are constructed at runtime based on JPA 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 JPA annotations that can be used in Entity, see JPA annotation that can be used for Entity.

7.3.2.1.2. 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.

7.3.2.3.1. 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" />

7.3.2.3.2. 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");

7.3.2.3.3. 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.

7.3.2.3.4. 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
    }
}

7.3.2.3.5. 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);

7.3.2.3.6. 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 JPA annotation information when sending automatically generated SQL to the DB, JPA 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

• Conversion is performed by delegating the process to the Database Access (JDBC Wrapper).

When fetching from database

• Perform the same process as when executing SQL automatically generated from the Entity.

7.3.2.3.7. 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();

7.3.2.3.8. Numbering the surrogate keys¶

When numbering the surrogate keys, use the following annotations.

• @GeneratedValue
• @SequenceGenerator
• @TableGenerator

Universal DAO supports all strategies of javax.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>”.

7.3.2.3.9. 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.

• batchInsert
• batchUpdate
• batchDelete

7.3.2.3.10. Optimistic locking¶

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

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.

}

7.3.2.3.11. 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.

7.3.2.3.12. 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.

7.3.2.3.13. 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.

7.3.2.3.14. 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).

7.3.2.3.15. 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.

• Configure implementation class of ConnectionFactory to connectionFactory property. For details of implementation class of ConnectionFactory, see Connection configuration for the database.
• Configure implementation class of TransactionFactory to transactionFactory property. For details of implementation of TransactionFactory, see Perform transaction control for the database.

<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();

7.3.2.4.1. 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" />

7.3.2.4.2. 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.

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>