7.3.1. Database Access (JDBC Wrapper)¶

7.3.1.1.1. Can be used without sensing the database dialect¶

By configuring Dialect corresponding to the database product used, the application can be implemented without sensing the dialect.

Dialect provides the following functions:

• A method that returns whether the identity column can be used (supportsIdentity )
• A method that returns whether batch insert can be performed on a table that has an identity (automatic numbered) column (supportsIdentityWithBatchInsert)
• A method that returns whether the sequence object can be used (supportsSequence )
• A method that returns whether offset (or a function equivalent to offset) can be used in the specified range of search query (supportsOffset )
• A method to determine whether SQLException that indicates a unique constraint violation has occurred (isDuplicateException )
• A method to determine whether SQLException of transaction timeout target has occurred (isTransactionTimeoutError )
• A method for generating SQL statement to acquire the next value from the sequence object (buildSequenceGeneratorSql )
• A method that returns ResultSetConvertor , which acquires value from ResultSet (getResultSetConvertor )
• A method to convert search query to range specification (for paging) SQL (convertPaginationSql )
• A method for converting search query to the number acquisition SQL (convertCountSql(String) )
• A method for converting SQL ID to the number acquisition SQL (convertCountSql(String, Object, StatementFactory) )
• A method that returns SQL to check if Connection is connected to the database (getPingSql )

See Use dialects for database products for how to configure Dialect .

7.3.1.1.2. Write SQL in SQL file, not logic¶

SQL is defined in the SQL file and not in the principle logic.

SQL is not required to be assembled by logic by describing in the SQL file, and since PreparedStatement is always used, the vulnerability of SQL injection can be eliminated.

For details, see Manage SQL in files .

7.3.1.1.3. Bean property values can be embedded in SQL bind variables¶

Provides a function to automatically bind the value configured in Bean property to IN parameter of java.sql.PreparedStatement .

By using this function, it is not necessary to call the method for configuring the value of java.sql.PreparedStatement multiple times, and it is not necessary to modify the index when the number of IN parameters increases or decreases.

For details, see Execute SQL with Bean object as input .

7.3.1.1.4. like search can be easily implemented¶

Provides a function to automatically insert an escape clause and escape processing of wildcard characters in like search.

For details, see Perform a like search .

7.3.1.1.5. SQL statements can be dynamically constructed based on the state of the Bean object during execution¶

Provides a function to dynamically assemble the SQL statement to be executed based on the state of the Bean object.

For example, dynamic construction of conditions and IN clauses can be performed.

See below for details.

• Execute SQL with variable conditions
• Executing SQL with variable number of conditions for in clause
• Execute SQL by dynamically switching sort items of order by during runtime

7.3.1.1.6. SQL query results can be cached¶

Provides a function to return search results from the cache without accessing the database when the executed SQL and conditions acquired from an external source (value configured in the bind variable) are equivalent.

For details, see Cache search results (handle cached data for the same SQL under the same condition) .

7.3.1.3.1. Connection configuration for the database¶

The connection configuration for the database can be selected from the following two types.

• Creating a database connection using javax.sql.DataSource
• Creating a database connection using a data source registered on an application server.

To use a connection method other than the above (for example, to use the OSS connection pooling library), refer to Add a connection method to the database and add an implementation that connects to the database.

Connection configuration example

Generate database connection using javax.sql.DataSource

<component class="nablarch.core.db.connection.BasicDbConnectionFactoryForDataSource">
  <!-- See Javadoc for details of the configuration values -->
</component>

Generating a database connection from an application server data source

<component class="nablarch.core.db.connection.BasicDbConnectionFactoryForJndi">
  <!-- See Javadoc for details of the configuration values -->
</component>

Refer to Javadoc of each class for the configuration values of BasicDbConnectionFactoryForDataSource and BasicDbConnectionFactoryForJndi .

7.3.1.3.2. Use dialects for database products¶

The dialect function is enabled by configuring the dialect corresponding to the database product in the component configuration file.

Component configuration example

This is a configuration example for the component that acquires the database connection from javax.sql.DataSource . Even in the case of BasicDbConnectionFactoryForJndi , configure dialect to the dialect property as shown in the following example.

<component class="nablarch.core.db.connection.BasicDbConnectionFactoryForDataSource">
  <!-- Properties that are not related to dialect are omitted -->

  <!--
  Dialect is configured in the dialect property.
  In this example, the dialect for the Oracle database has been configured.
  -->
  <property name="dialect">
    <component class="nablarch.core.db.dialect.OracleDialect" />
  </property>
</component>

7.3.1.3.3. Manage SQL in files¶

In this function, SQL is managed in the SQL file as described in Write SQL in SQL file, not logic . To handle SQL files, it is necessary to configure the component configuration file. For details, see Configuration for loading SQL from SQL files .

Create an SQL file according to the following rules.

• Create the SQL under the class path.
• Multiple SQL statements can be described in one SQL file, but SQLID must be unique within the file.
• Insert a blank line between SQLIDs. (Lines with spaces are not considered blank lines)
• Insert = between SQLID and SQL.
• Describe comments with -- . (Block comments are not supported)
• SQL may be formatted using line breaks and spaces (tabs).

An example of a SQL file is shown below.

-- ＸＸＸＸＸ acquisition SQL
-- SQL_ID:GET_XXXX_INFO
GET_XXXX_INFO =
select
   col1,
   col2
from
   test_table
where
   col1 = :col1


-- ＸＸＸＸＸ update SQL
-- SQL_ID:UPDATE_XXXX
update_xxxx =
update
    test_table
set
    col2 = :col2
where
    col1 = :col1

Configuration for loading SQL from SQL files

This section describes the configuration required to load SQL from an SQL file.

To load SQL, configure BasicSqlLoader in BasicStatementFactory#sqlLoader .

In this example, file encoding and extension are configured. When the configuration is omitted, the following values are used.

File encoding:	utf-8
Extension:	sql

The component BasicStatementFactory defined here must be configured in the component that acquires the database connection defined in Connection configuration for the database .

Configuration example

<component name="statementFactory" class="nablarch.core.db.statement.BasicStatementFactory">
  <property name="sqlLoader">
    <component class="nablarch.core.db.statement.BasicSqlLoader">
      <property name="fileEncoding" value="utf-8"/>
      <property name="extension" value="sql"/>
    </component>
  </property>
</component>

7.3.1.3.4. Execute SQL by specifying SQLID¶

To execute SQL based on SQLID, use the database connection obtained from DbConnectionContext . A database connection has to be registered in DbConnectionContext using Database Connection Management Handler .

The mapping rules between the SQLID and the executed SQL are as follows.

• Up to # of SQLID is the SQL file name.
• After # of SQLID is the SQLID in the SQL file

Implementation examples

In this example, since jp.co.tis.sample.action.SampleAction#findUser is specified in SQLID, the SQL file is jp.co.tis.sample.action.SampleAction.sql below the class path. SQLID in the SQL file is findUser .

• See Javadoc for how to use AppDbConnection and SqlPStatement .

// Get database connection from DbConnectionContext.
AppDbConnection connection = DbConnectionContext.getConnection();

// Generate the statement based on SQLID.
SqlPStatement statement = connection.prepareStatementBySqlId(
    "jp.co.tis.sample.action.SampleAction#findUser");

// Configure the condition.
statement.setLong(1, userId);

// Execute the search process.
SqlResultSet result = statement.retrieve();

7.3.1.3.5. Execute a stored procedure¶

Even when executing a stored procedure, basically implement it in the same way as executing a SQL.

An example is shown below.

• For details on how to use SqlCStatement , refer to the Javadoc.

// Generate an execution statement for the stored procedure based on SQLID.
SqlCStatement statement = connection.prepareCallBySqlId(
    "jp.co.tis.sample.action.SampleAction#execute_sp");

// Configure the IN and OUT parameters.
statement.registerOutParameter(1, Types.CHAR);

// Execute.
statement.execute();

// Acquire OUT parameter.
String result = statement.getString(1);

7.3.1.3.6. Execute the SQL by specifying the search scope¶

A specific range of results may be displayed by using a paging function on the search result list screen of a web system. This function provides a function that can specify the range of search results for such applications.

Implementation examples

When generating a statement from a database connection( connection ), specify the search target range. In this example, since the following values are specified, up to 10 records are fetched from the 11th record.

Start position:	11
Number of records fetched:
	10

// Acquire database connection from DbConnectionContext
AppDbConnection connection = DbConnectionContext.getConnection();

// Generate the statement object by specifying the SQLID and search range.
SqlPStatement statement = connection.prepareStatementBySqlId(
    "jp.co.tis.sample.action.SampleAction#findUser", new SelectOption(11, 10));

// Execute the search process
SqlResultSet result = statement.retrieve();

7.3.1.3.7. Execute SQL with Bean object as input¶

As described in Bean property values can be embedded in SQL bind variables , SQL can be executed using the Bean object as an input.

When executing SQL using the Bean object as an input, use a named bind variable for the IN parameter of SQL. In the named parameter, describe the property name of Bean that is received as input after : .

An implementation example is shown below.

SQL example

Use named parameters for IN parameters.

insert into user
  (
  id,
  name
  ) values (
  :id,
  :userName
  )

Implementation examples

Set the required value in the Bean object and call the function to execute SQL using the Bean object as an input.

• See Javadoc for how to use AppDbConnection and ParameterizedSqlPStatement .
• For the relationship between SQLID and the executed SQL, see Execute SQL by specifying SQLID .

// Create a bean and configure a value for the property
UserEntity entity = new UserEntity();
entity.setId(1);              // Configure a value to id property
entity.setUserName("Name"); // Configure a value to userName property

// Acquire database connection from DbConnectionContext
AppDbConnection connection = DbConnectionContext.getConnection();

// Generate a statement based on SQLID
ParameterizedSqlPStatement statement = connection.prepareParameterizedSqlStatementBySqlId(
    "jp.co.tis.sample.action.SampleAction#insertUser");

// Configure the value of bean property to bind variable and execute the SQL
// Value of id property of bean is configured in :id of the SQL.
// Value of userName property of bean is configured in :userName of SQL.
int result = statement.executeUpdateByObject(entity);

nablarch.dbAccess.isFieldAccess=true

7.3.1.3.8. Convert type¶

Database access (JDBC wrapper) delegates the type conversion of variables used for input to/output from the database to the JDBC driver. Therefore, it is necessary to define the types of variables used for input and output according to the database type and specifications of the JDBC driver that is used.

If an arbitrary type conversion is necessary, the application performs type conversion on the variables used for input to/output from the database.

• When using Bean for input, perform type conversion when setting the value to Bean property. when using a Bean for output, perform type conversion after extracting the value from the property.
• When using Map for input, type conversion is performed when setting the value in Map. When using Map for output, perform type conversion after extracting the value.
• When configuring a bind variable by specifying an index, convert the object to be configured to the bind variable to an appropriate type. When acquiring a value from SqlRow , perform type conversion after the value has been acquired.

7.3.1.3.9. Automatically configure a common value when executing SQL¶

Provides a function to automatically configure the value to be configured each time when registering or updating data, immediately before executing the SQL. For example, this function can be used for items such as registration date and time and update date and time.

This function is enabled only when Execute SQL with Bean object as input is used to automatically configure the value based on the annotation configured in the property.

A usage example is shown below.

Component configuration file

To use this function, configure a class that performs automatic value configuration in the component configuration file.

As shown in the following example. configure AutoPropertyHandler implementation class in a list with respect to BasicStatementFactory#updatePreHookObjectHandlerList . The implementation class provided as standard is placed under the nablarch.core.db.statement.autoproperty package.

The component BasicStatementFactory defined here should be configured in the component that acquires the database connection defined in Connection configuration for the database .

<component name="statementFactory"
    class="nablarch.core.db.statement.BasicStatementFactory">

  <property name="updatePreHookObjectHandlerList">
    <list>
      <!-- Configure the implementation class nablarch.core.db.statement.AutoPropertyHandler in a list-->
    </list>
  </property>
</component>

Bean object (Entity)

Configure the annotation to the property for which the value has to be configured automatically. The annotation provided as standard is placed under the nablarch.core.db.statement.autoproperty package.

public class UserEntity {
  // User ID
  private String id;

  // Registration date and time
  // Automatically configured when registering
  @CurrentDateTime
  private Timestamp createdAt;

  // Update date and time
  // Automatically configured during registration/update
  @CurrentDateTime
  private String updatedAt;

  // Access method, etc. are omitted
}

SQL

SQL is created in the same way as Execute SQL with Bean object as input .

insert into user (
  id,
  createdAt,
  updatedAt
) values (
  :id,
  :createdAt,
  :updatedAt
)

Implementation examples

Implementation is the same as Execute SQL with Bean object as input . For items whose values are automatically configured, there is no need to configure values for Beans with logic. Even if the value is explicitly configured, it is overwritten by the automatic value configuration function immediately before the SQL is executed.

// Create a bean and configure a value for the property
// Values are not required to be configured for createdAt and updatedAt which are automatically configured
UserEntity entity = new UserEntity();
entity.setId(1);

// Acquire database connection from DbConnectionContext
AppDbConnection connection = DbConnectionContext.getConnection();

// Generate a statement based on SQLID
ParameterizedSqlPStatement statement = connection.prepareParameterizedSqlStatementBySqlId(
    "jp.co.tis.sample.action.SampleAction#insertUser");

// Call without configuring the value for the automatically configured items.
// The database function automatically configures the values.
int result = statement.executeUpdateByObject(entity);

7.3.1.3.10. Perform a like search¶

Like search uses Execute SQL with Bean object as input , and conditions for like search are described in the SQL according to the following rules.

In the case of prefix match

Enter % at the end of the named parameter.

Example: name like :userName%

In the case of suffix match

Enter % at the beginning of the named parameter.

Example: name like :%userName

In the case of middle match

Enter % before and after the named parameter.

Example: name like :%userName%

See Define escape character and escape target characters during like search for the definition of escape character and escape target character.

An implementation example is shown below.

SQL

Define SQL according to the above rules.

select *
  from user
 where name like :userName%

Implementation examples

Just by executing the SQL in the same way as Execute SQL with Bean object as input , value rewriting and escape processing are performed for like conditions. In this case, the actual condition is name like 'Na%' escape'\' .

• See Javadoc for how to use AppDbConnection and ParameterizedSqlPStatement .
• For the relationship between SQLID and the executed SQL, see Execute SQL by specifying SQLID

// Create a bean and configure a value for the property
UserEntity entity = new UserEntity();
entity.setUserName("Na"); // Configure a value to userName property

// Acquire database connection from DbConnectionContext
AppDbConnection connection = DbConnectionContext.getConnection();

// Generate a statement based on SQLID
ParameterizedSqlPStatement statement = connection.prepareParameterizedSqlStatementBySqlId(
    "jp.co.tis.sample.action.SampleAction#findUserByName");

// Configure bean property value to bind variable and execute the SQL
// In this example, name like 'Na%' is executed
int result = statement.retrieve(bean);

7.3.1.3.11. Define escape character and escape target characters during like search¶

Escape character and escape target characters are defined in the component configuration file. Since the escape character is automatically configured, it is not necessary to explicitly configure the escape characters.

When the configuration is omitted, the following values are used.

Component configuration example

In this example, \ is configured as the escape character, and 4 characters % , ％ , _ , ＿ are configured as the escape target characters.

The component BasicStatementFactory defined here should be configured in the component that acquires the database connection defined in Connection configuration for the database .

<component name="statementFactory" class="nablarch.core.db.statement.BasicStatementFactory">
  <!-- Escape character definition -->
  <property name="likeEscapeChar" value="\" />

  <!-- Escape target character definition (configured separated with commas) -->
  <property name="likeEscapeTargetCharList" value="%,％,_,＿" />
</component>

7.3.1.3.12. Execute SQL with variable conditions¶

To execute SQL with variable conditions, use Execute SQL with Bean object as input and describe the conditions using the following notation.

Description rules for variable conditions

Variable condition is described with $if(property name){SQL statement condition} The condition is excluded by the value of Bean object corresponding to the property name after $if . Excluded conditions are as follows.

• For an array or java.util.Collection , if the property value is null or size is 0
• For types other than the above, the property value is null or empty string (in case of string object)

The $if special syntax has the following restrictions.

• Only the where clause can be used
• $if cannot be used within $if

Important

This function is used when the search conditions change depending on the input contents of the user, such as the search screen of a web application. It is not used to standardize multiple SQLs that differ only in conditions. If standardized, since the function may cause unexpected bugs when SQL is changed, be sure to define multiple SQL.

An example is shown below.

SQL

For this SQL, user_name and user_kbn conditions are variables.

select
  user_id,
  user_name,
  user_kbn
from
  user
where
  $if (userName) {user_name like :userName%}
  and $if (userKbn) {user_kbn in ('1', '2')}
  and birthday = :birthday

Implementation examples

Since the value is set only for userName property, user_kbn defined in the variable condition is excluded from the condition during execution.

// Create a bean and configure a value for the property
UserEntity entity = new UserEntity();
entity.setUserName("Name");

// Acquire database connection from DbConnectionContext
AppDbConnection connection = DbConnectionContext.getConnection();

// Generate a statement based on SQLID
// Specify a Bean object with a condition in the second argument.
// SQL variable conditions are assembled based on the state of this Bean object.
ParameterizedSqlPStatement statement = connection.prepareParameterizedSqlStatementBySqlId(
    "jp.co.tis.sample.action.SampleAction#insertUser", entity);

// SQL is executed by configuring the value of the entity property to the bind variable
SqlResultSet result = statement.retrieve(entity);

7.3.1.3.13. Executing SQL with variable number of conditions for in clause¶

To execute SQL with condition count of the in clause as variable, use Execute SQL with Bean object as input and describe the conditions using the following notation.

Description rules of in clause

Add [] at the end of the named parameter of the condition. The property type of Bean object corresponding to the named parameter must be an array or java.util.Collection (including subtype) [1] .

Tip

If the property value, that is the condition of the IN clause, is null or size 0, be sure to define the corresponding condition as a variable condition. If the property value is null when the condition is not configured as a variable condition, since the condition will be xxxx in (null) , the search result may not be acquired correctly.

* In the in clause, since the conditional expression (in parentheses) cannot be empty, the conditional expression is specified as in (null) if an array of size 0 or null is specified.

An example is shown below.

SQL

In this SQL, the in condition of user_kbn is dynamically constructed. Since it is used together with $if, if the userKbn property is null or the size is 0, it is excluded from the condition.

select
  user_id,
  user_name,
  user_kbn
from
  user
where
  $if (userKbn) {user_kbn in (:userKbn[])}

Execution example

In this example, since two elements are configured in userKbn property, the condition of the executed SQL is userKbn in (?, ?) .

Only records with userKbn 1 and 3 are acquired from the database.

// Create a bean and configure a value for the property
UserSearchCondition condition = new UserSearchCondition();
condition.setUserKbn(Arrays.asList("1", "3"));

// Acquire database connection from DbConnectionContext
AppDbConnection connection = DbConnectionContext.getConnection();

// Generate a statement based on SQLID
// Specify a Bean object with a condition in the second argument.
// in clause of SQL is assembled based on the state of this Bean object.
ParameterizedSqlPStatement statement = connection.prepareParameterizedSqlStatementBySqlId(
    "jp.co.tis.sample.action.SampleAction#searchUser", condition);

// SQL is executed by configuring the value of the condition property to the bind variable
SqlResultSet result = statement.retrieve(condition);

7.3.1.3.14. Execute SQL by dynamically switching sort items of order by during runtime¶

To execute SQL with sort item of order by as variable, use Execute SQL with Bean object as input and describe the conditions using the following notation.

Description rules of order by clause

To make the sort item a variable, use $sort instead of order by clause and describe as follows.

$sort (property name) {(case 1) (case 2) ・ ・ ・ (case n)}

Property name: Property name that holds the sort ID of Bean object
Case: Represents the switching candidate for the order by clause.
        Describe the sort ID that uniquely identifies the candidate and string specified in the order by clause (hereinafter referred to as the case body).
        Specify "default" as the sort ID for the default case when no match is found to any candidate.

• Each case is represented by enclosing the sort ID and case body in single-byte parentheses.
• Sort ID and case body are separated by a half-width space.
• Half-width spaces cannot be used for the sort ID.
• A half-width space can be used for the case body.
• The first string that appears after the opening parentheses is the sort ID.
• After the sort ID and before the closing parentheses is the case body.
• The sort ID and case body are trimmed.

A usage example is shown below.

SQL

select
  user_id,
  user_name
from
  user
where
  user_name = :userName
$sort(sortId) {
  (user_id_asc  user_id asc)
  (user_id_desc user_id desc)
  (name_asc     user_name asc)
  (name_desc    user_name desc)
  (default      user_id)

Implementation examples

In this example, since name_asc is configured in the sort ID, the order by clause becomes order by user_name asc .

// Create a bean and configure a value for the property
UserSearchCondition condition = new UserSearchCondition();
condition.setUserName("Name");
condition.setSortId("name_asc");      // Configure the sort ID

// Acquire database connection from DbConnectionContext
AppDbConnection connection = DbConnectionContext.getConnection();

// Generate a statement based on SQLID
// Specify a Bean object with a condition in the second argument.
// order by clause of SQL is assembled based on the state of this Bean object.
ParameterizedSqlPStatement statement = connection.prepareParameterizedSqlStatementBySqlId(
    "jp.co.tis.sample.action.SampleAction#searchUser", condition);

// SQL is executed by configuring the value of the condition property to the bind variable
SqlResultSet result = statement.retrieve(condition);

7.3.1.3.15. Accessing columns of binary type¶

This section describes how to access binary type columns such as blob (binary type differs depending on the database product).

Obtain the value of binary type

When acquiring a binary value, acquire the value as byte[] from SqlRow of the search result object.

An example is shown below.

SqlResultSet rows = statement.retrieve();

SqlRow row = rows.get(0);

// Acquire the value of the encrypted column in binary using getBytes
byte[] encryptedPassword = row.getBytes("password");

Important

In the case of the above implementation example, all the contents of the column are deployed on the Java heap. Therefore, when data of a very large size is read, it squeezes the heap area, causing failures such as system down.

Therefore, when reading a large amount of data, use the Blob object as shown below to avoid consuming a large amount of heap.

SqlResultSet rows = select.retrieve();

// Acquire the data as Blob
Blob pdf = (Blob) rows.get(0).get("PDF");

try (InputStream input = pdf.getBinaryStream()) {
  // Read the data sequentially from InputStream.
  // Note that if read all at once, everything will be loaded into the heap
}

Register/update binary value

To register/update a small binary value, use SqlPStatement#setByte .

SqlPStatement statement = getSqlPStatement("UPDATE_PASSWORD");

statement.setBytes(1, new byte[] {0x30, 0x31, 0x32});
int updateCount = statement.executeUpdate();

When registering and updating a large binary value, use SqlPStatement#setBinaryStream , and send values directly to the database from InputStream which represents a file, etc.

final Path pdf = Paths.get("input.pdf");
try (InputStream input = Files.newInputStream(pdf)) {
    statement.setBinaryStream(1, input, (int) Files.size(pdf));
}

7.3.1.3.16. Access columns of string type with a large number of digits (e.g. CLOB)¶

This section describes how to access a large string type column such as CLOB.

Acquire the value of CLOB type

When acquiring CLOB type values, acquire the value as string type from search result object .

An example is shown below.

SqlResultSet rows = statement.retrieve();
SqlRow row = rows.get(0);

// Acquire CLOB value as a string.
String mailBody = row.getString("mailBody");

Important

In the case of the above implementation example, all the contents of the column are deployed on the Java heap. Therefore, when data of a very large size is read, it squeezes the heap area, causing failures such as system down.

Therefore, when reading a large amount of data, use the Clob object as shown below to avoid consuming a large amount of heap.

SqlResultSet rows = select.retrieve();

// Acquire the data as Clob
Clob mailBody = (Clob) rows.get(0).get("mailBody");

try (Reader reader = mailBody.getCharacterStream()) {
  // Read data sequentially from the Reader.
  // If all the read data is held in the heap, note that the heap will be squeezed.
}

Register (update) the value in CLOB type

When registering and updating a value with a small size, configure a string type value using SqlPStatement#setString .

An example is shown below.

statement.setString(1, "Value");
statement.executeUpdate();

When registering or updating a large value, use SqlPStatement#setCharacterStream , and send values to the database through Reader that represents a text file, etc.

An example is shown below.

Path path = Paths.get(filePath);
try (Reader reader = Files.newBufferedReader(path, StandardCharsets.UTF_8)) {
  // Register the Reader value using setCharacterStream.
  statement.setCharacterStream(1, reader, (int) Files.size(path));
}

7.3.1.3.17. Exception types that occur when accessing the database¶

Exceptions when accessing the database access are broadly divided into the following four types.

Since these exceptions are all unchecked exceptions, there is no need to catch them with try-catch such as SQLException .

Exception when there is a database access error

The exception that occurs when accessing the database, and DbAccessException is thrown.

Exception when there is a database connection error

If the exception during database access indicates a database connection error, DbConnectionException is thrown. This exception is handled by the Retry Handler. (If Retry Handler is not applied, it is handled as a runtime exception.)

Dialect is used when determining a database connection error.

SQL execution exception

Exception which occurs when SQL execution fails, and SqlStatementException is thrown.

Exception when the SQL execution is a violation of unique constraint

If the exception during SQL execution is an exception indicating a unique constraint violation, DuplicateStatementException is thrown.

To handle a unique constraint violation, refer Process by handling unique constraint violation .

Dialect is used to determine a unique constraint violation.

7.3.1.3.18. Process by handling unique constraint violation¶

If a process has to be performed when a unique constraint is violated, DuplicateStatementException is caught with try-catch and processed.

Dialect is used to determine a unique constraint violation.

7.3.1.3.19. Processing of long-running transactions is aborted as errors¶

Implemented by transaction management. For details, see Apply transaction timeout for the database .

7.3.1.3.20. Execute SQL in a transaction different from the current transaction¶

There may be cases database has to be accessed using an individual transaction instead of a transaction started by the database connection management handler and transaction control handler.

For example, to confirm changes to the database even when business processing fails, define a transaction different from the current transaction and access the database.

The following procedures are required to use individual transactions.

1. Configure SimpleDbTransactionManager in the component configuration file.
2. Acquire SimpleDbTransactionManager from the system repository and execute SQL in a new transaction. (Instead of acquiring from the system repository, SimpleDbTransactionManager can be used)

A usage example is shown below.

Component configuration file

Configure SimpleDbTransactionManager in the component configuration file.

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

<component name="update-login-failed-count-transaction" class="nablarch.core.db.transaction.SimpleDbTransactionManager">
  <!-- Configure ConnectionFactory implementation class in connectionFactory property -->
  <property name="connectionFactory" ref="connectionFactory" />

  <!-- Configure TransactionFactory implementation class in 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 to execute SQL. In addition, instead of using SimpleDbTransactionManager directly, use SimpleDbTransactionExecutor to perform transaction control.

// Acquire SimpleDbTransactionManager from the system repository
SimpleDbTransactionManager dbTransactionManager =
    SystemRepository.get("update-login-failed-count-transaction");

// Execute by specifying SimpleDbTransactionManager in the constructor
SqlResultSet resultSet = new SimpleDbTransactionExecutor<SqlResultSet>(dbTransactionManager) {
  @Override
  public SqlResultSet execute(AppDbConnection connection) {
    SqlPStatement statement = connection.prepareStatementBySqlId(
        "jp.co.tis.sample.action.SampleAction#findUser");
    statement.setLong(1, userId);
    return statement.retrieve();
  }
}.doTransaction();

7.3.1.3.21. Cache search results (handle cached data for the same SQL under the same condition)¶

If the update time is fixed or the data is accessed frequently but the latest data is not required to be returned, the search results can be cached to reduce the load on the database.

This function can be used effectively with the following functions.

• Data that is referenced in large quantities without the need for strict up-to-date results such as sales rankings
• Data that is updated only at night and not updated during the day

Constraints

LOB type

When LOB (BLOB type or CLOB type) column is acquired, LOB locator is acquired instead of acquiring the data stored in DB. To acquire the actual value, acquire the value through this LOB locator.

The expiry interval of the LOB locator depends on the implementation of each RDBMS. Normally, the locator cannot be accessed when java.sql.ResultSet or java.sql.Connection is closed. For this reason, BLOB and CLOB types cannot be included in a cache that has a longer lifetime than ResultSet or Connection .

Application redundancy

The component maintaining the cache provided by default retains the cache in the JVM heap. For this reason, when applications have a redundant configuration, the search results are cached for each application.

For this reason, since the cache timing is different, each application may hold a different cache.

When application servers are redundant and a round-robin load balancer is used, a different server may be accessed each time. Note that if different caches are maintained for each server, different results may be displayed on the screen for each request.

A usage example is shown below.

Component configuration file

Follow the procedure below to configure for enabling the cache of search results.

1. Defining the components to cache query results
2. Search result cache configuration for each SQLID
3. Definition of SQL execution component that caches the search results

Defining the components of the query result cache class

Configure InMemoryResultSetCache of the class that caches the query results provided by default.

<component name="resultSetCache" class="nablarch.core.db.cache.InMemoryResultSetCache">
  <property name="cacheSize" value="100"/>
  <property name="systemTimeProvider" ref="systemTimeProvider"/>
</component>

Cache configuration for each SQL ID

Configure the cache for each SQL ID. The cache expiration date can be configured for each SQLID using BasicExpirationSetting provided by default.

The following units can be used for the expiration date.

ms:	millisecond
sec:	Sec
min:	Minutes
h:	Hours

<!-- Cache expiration configuration-->
  <component name="expirationSetting"
      class="nablarch.core.cache.expirable.BasicExpirationSetting">

    <property name="expiration">
      <map>
        <!-- Configure SQLID in key and expiration date in value-->
        <entry key="please.change.me.tutorial.ss11AA.W11AA01Action#SELECT" value="100ms"/>
        <entry key="please.change.me.tutorial.ss11AA.W11AA02Action#SELECT" value="30sec"/>
      </map>
    </property>

  </component>

Definition of SQL execution component that caches the search results

To cache search results, configure CacheableStatementFactory in the generation class of the SQL execution component. Since CacheableStatementFactory inherits BasicStatementFactory provided by default, the basic settings are the same as BasicStatementFactory .

For the expirationSetting and resultSetCache property, configure the cache component of the query result configured above and the cache configuration component for each SQLID.

The component CacheableStatementFactory defined here should be configured in the component that acquires the database connection defined in Connection configuration for the database .

<!-- Configure cacheableStatementFactory to generate cacheable statements-->
<component name="cacheableStatementFactory"
           class="nablarch.core.db.cache.CacheableStatementFactory">

  <!-- Expiration date setting -->
  <property name="expirationSetting" ref="expirationSetting"/>
  <!-- Cache implementation -->
  <property name="resultSetCache" ref="resultSetCache"/>

</component>

Implementation examples

Database access using SQL does not change depending on the presence of cache. It is implemented as follows.

• Execute SQL by specifying SQLID
• Execute SQL with Bean object as input

7.3.1.3.22. Process using the java.sql.Connection¶

Handling of JDBC native database connection ( java.sql.Connection ) may be required. For example, when java.sql.DatabaseMetaData is required to be used.

This can be supported by acquiring java.sql.Connection obtained from TransactionManagerConnection which has been obtained from DbConnectionContext.

An example is shown below.

TransactionManagerConnection managerConnection = DbConnectionContext.getTransactionManagerConnection();
Connection connection = managerConnection.getConnection();
return connection.getMetaData();

7.3.1.3.23. Switch the schema in SQL statement for each environment¶

If a different schema has to be referenced only for a specific SQL (table), the schema is explicitly specified in the SQL statement (for example: SELECT * FROM A_SCHEMA.TABLE1 ), but there are cases where the schema name to be referred differs depending on the environment (see the example below).

Reference schema of TABLE1

In this case, the method of explicitly describing the schema name in the SQL statement cannot be used.

-- SELECT by specifying the schema name
SELECT * FROM A_SCHEMA.TABLE1  -- Works in production environment but not in the test environment

For such a case, provide a function to switch the schema in the SQL statement for each environment.

First, describe a placeholder #SCHEMA# [2]for replacing the schema in the SQL statement.

-- SELECT by specifying the schema name
SELECT * FROM #SCHEMA#.TABLE1

Configure BasicSqlLoader to replace the placeholders, as shown in the following example:

<component name="statementFactory" class="nablarch.core.db.statement.BasicStatementFactory">
  <property name="sqlLoader">
    <component name="sqlLoader" class="nablarch.core.db.statement.BasicSqlLoader">
      <property name="sqlLoaderCallback">
        <list>
          <!-- Replace #SCHEMA# in SQL statement with specified value -->
          <component class="nablarch.core.db.statement.sqlloader.SchemaReplacer">
            <property name="schemaName" value="${nablarch.schemaReplacer.schemaName}"/>
          </component>
        </list>
      </property>
    </component>
  </property>
</component>

Configure the value to replace the placeholder in schemaName of SchemaReplacer . In the above example, the value after replacement is configured in the environment-dependent value nablarch.schemaReplacer.schemaName . By switching this value for each environment, the schema in the SQL statement can be replaced with the one corresponding to the environment (see How to switch the environment configuration value for each environment for details of the switching method).

7.3.1.4.1. Add a connection method to the database¶

The procedure for adding a database connection method will be described. For example, this procedure should be followed when using the OSS connection pool library.

1. Inherit ConnectionFactorySupport and create a class that generates a database connection.
2. Configure the class that is created in the component configuration file. ( see Connection configuration for the database )

7.3.1.4.2. Add dialects¶

The procedure to add a dialect will be described.

For example, dialects have to be added when there is no dialect corresponding to the database product to be used, or to switch the availability of a specific function, it is necessary to add a dialect.

1. Inherit DefaultDialect and create a dialect corresponding to the database product.
2. Configure the created dialect in the component configuration file (see Use dialects for database products )

7.3.1.4.3. Switching the exception class when accessing the database¶

This section describes the procedure to switch the exception class during database access.

For example, to change the exception class of deadlock error, work according to this procedure.

1. Create the implementation class of DbAccessExceptionFactory that generates database access error.
2. Create the implementation class of SqlStatementExceptionFactory that generates SQL run-time error.
3. Define the class that is created in the component configuration file.

The detailed procedure is shown below.

Create the implementation class of DbAccessExceptionFactory

Create an implementation class of this interface to change DbAccessException that is generated during database connection acquisition and transaction control (commit and rollback).

Create the implementation class of SqlStatementExceptionFactory

Create an implementation class of this interface to change SqlStatementException that occurs when SQL is executed.

Define in the component configuration file

Implementation class of DbAccessExceptionFactory must be configured in the component that acquires the database connection defined in Connection configuration for the database .

<component class="sample.SampleDbAccessExceptionFactory" />

The implementation class of SqlStatementExceptionFactory is configured for BasicStatementFactory . BasicStatementFactory must be configured in the component that acquires the database connection defined in Connection configuration for the database .

<component name="statementFactory" class="nablarch.core.db.statement.BasicStatementFactory">
  <property name="sqlStatementExceptionFactory">
    <component class="sample.SampleStatementExceptionFactory" />
  </property>
</component>