5.1. Database Access (Common)

Caution

This version is already obsolete. Please check the latest guideline.

Todo

TBD

The following topics in this chapter are currently under study.

5.1.1. Overview

This chapter explains the method of accessing the data stored in RDBMS.

For the O/R Mapper dependent part, refer to

5.1.1.1. About JDBC DataSource

RDBMS can be accessed from the application by referring to JDBC datasource.
JDBC datasource can be used to exclude settings such as JDBC driver load, connection information (URL, user, password etc.) from application.
Therefore, RDBMS to be used and environment in which the application is to be deployed need not be taken into account.
about data source

Picture - About JDBC DataSource

JDBC datasource is implemented from Application Server, OSS library, Third-Party library, Spring Framework etc.; hence it is necessary to select the datasource based on project requirements and deployment environment.
The typical datasources are introduced below.

5.1.1.1.1. JDBC datasource provided by Application Server

When datasource is to be used in Web application, normally JDBC datasource provided by Application Server is used.
JDBC datasource of Application Server provides functionalities required in web application such as Connection Pooling as standard functionalities.
Datasources provided by Application Server
Sr. No. Application Server Reference page
Apache Tomcat 7
Refer to Apache Tomcat 7 User Guide (The Tomcat JDBC Connection Pool).
Refer to Apache Tomcat 7 User Guide (JNDI Datasource HOW-TO) (Apache Commons DBCP).
Oracle WebLogic Server 12c Refer to Oracle WebLogic Server Product Documentation.
IBM WebSphere Application Server Version 8.5 Refer to WebSphere Application Server Online information center.
Resin 4.0 Refer to Resin Documentation.

5.1.1.1.2. JDBC datasource provided by OSS/Third-Party library

When JDBC datasource of Application Server is not used, JDBC datasource of OSS/Third-Party library should be used.
This guideline introduces only Apache Commons DBCP; however other libraries can also be used.
JDBC datasource provided by OSS/Third-Party library
Sr. No. Library name Description
Apache Commons DBCP Refer to Apache Commons DBCP.

5.1.1.1.3. JDBC datasource provided by Spring Framework

Implementation class of JDBC datasource of Spring Framework cannot be used as datasource of Web application since it does not provide connection pooling.
In Spring Framework, implementation class and adapter class of JDBC datasource are provided; however they are introduced as JDBC datasource classes provided by Spring Framework of Appendix, since usage is restricted.

5.1.1.2. About transaction management

When transactions are to be stored using Spring Framework functionality, PlatformTransactionManager needs to be selected based on project requirements and deployment environment.
For details, refer to Settings for using transaction management of Domain Layer Implementation.

5.1.1.3. About declaration of transaction boundary/attribute

Transaction boundary and transaction attributes should be declared by specifying @Transactional annotation in Service.
For details, refer to Regarding transaction management of Domain Layer Implementation .

5.1.1.4. About exclusion control of data

When updating data, it is necessary to execute exclusion control to ensure data consistency and integrity.
For details on exclusion control of data, refer to Exclusive Control.

5.1.1.5. About exception handling

In Spring Framework, a function is provided to convert JDBC exception (java.sql.SQLException) and O/R Mapper specific exception to data access exception (subclass of (org.springframework.dao.DataAccessException) provided by Spring Framework.
For the class which is converted to data access exception of Spring Framework, refer to Classes provided by Spring Framework for converting to data access exception of Appendix.
The converted data access exception need not be handled in application code; however, some errors (such as unique constraint violation, exclusion error etc.) need to be handled as per the requirements.
When handling data access exception, exception of subclass notifying error details should be caught instead of DataAccessException.
Typical subclasses which are likely to be handled in application code are as follows:
Subclasses of DB access exception, which are likely to be handled
Sr. No. Class name Description
org.springframework.dao.
DuplicateKeyException
Exception that occurs in case of unique constraint violation.
org.springframework.dao.
OptimisticLockingFailureException
Exception that occurs in case of optimistic locking failure. It occurs when same data is updated with different logic.
This exception occurs when JPA is used as O/R Mapper. MyBatis does not have optimistic locking function; hence this exception does not occur from O/R Mapper.
org.springframework.dao.
PessimisticLockingFailureException
Exception that occurs in case of pessimistic locking failure. It occurs when same data is locked with different logic and the lock is not released even after “waiting for unlocking” timeout period has elapsed.

Note

When optimistic locking is to be implemented using MyBatis in O/R Mapper, it should be implemented as Service or Repository process.

As a method of notifying the optimistic locking failure to Controller, this guideline recommends generation of OptimisticLockingFailureException and exception of its child class.

This is to make implementation of application layer (implementation of Controller) independent of O/R Mapper to be used.

Todo

It has been recently found that using JPA (Hibernate) results in occurrence of unexpected errors.

  • In case of unique constraint violation, org.springframework.dao.DataIntegrityViolationException occurs and not DuplicateKeyException.

See the example below for handling unique constraint violation as business exception.

try {
    accountRepository.saveAndFlash(account);
} catch(DuplicateKeyException e) { // (1)
    throw new BusinessException(ResultMessages.error().add("e.xx.xx.0002"), e); // (2)
}
Sr. No. Description
(1)
Exception (DuplicateKeyException) that occurs in case of unique constraint violation is caught.
(2)
Business exception indicating that there is duplicate data is thrown.
When exception is caught, make sure to specify the cause of exception (e) in business exception.

5.1.1.6. About multiple datasources

Multiple datasources may be required depending on the application.
Typical cases wherein multiple datasources are required, are shown below.
Typical case where multiple datasources are required
Sr. No. Case Example Feature
When database and schema are divided according to data (tables). When group of tables maintaining customer information and group of tables maintaining invoice information are stored in separate database and schema. The data to be handled in the process is fixed; hence the datasource to be used can be defined statically.
When database and schema to be used are divided according to users (login users). When database and schema are divided according to users (Multitenant etc.). The datasource to be used differs depending on users; hence the datasource to be used dynamically can be defined.

Todo

TBD

The following details will be added in future.

  • Conceptual diagram
  • Details of above two cases Especially, in case (1), transaction management method is likely to change depending on processing pattern (such as DB update using multiple datasources; DB update using only a single datasource, DB read only, no concurrent access etc.); hence plan to break down considering all these aspects.

5.1.1.7. About common library classes

Common library provides classes that carry out following processes.
For more details about common library, refer to links given below.

5.1.2. How to use

5.1.2.1. Datasource settings

5.1.2.1.1. Settings when using DataSource defined in Application Server

When using datasource defined in Application Server, it is necessary to perform settings in Bean definition file to register the object fetched through JNDI as a bean.
Settings when PostgreSQL is used as database and Tomcat7 is used as Application Server are given below.

  • xxx-context.xml (Tomcat config file)

    <!-- (1) -->
<Resource
   type="javax.sql.DataSource"
   name="jdbc/SampleDataSource"
   driverClassName="org.postgresql.Driver"
   url="jdbc:postgresql://localhost:5432/terasoluna"
   username="postgres"
   password="postgres"
   defaultAutoCommit="false"
   /> <!-- (2) -->

  • xxx-env.xml

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/SampleDataSource" /> <!-- (3) -->
Sr. No. Attribute name Description
(1)
 - Define datasource.

type Specify resource type. Specify javax.sql.DataSource.

name Specify resource name. The name specified here is JNDI name.

driverClassName Specify JDBC driver class. In the example, JDBC driver class provided by PostgreSQL is specified.

url Specify URL. [Needs to be changed as per environment]

username Specify user name. [Needs to be changed as per environment]

password Specify password of user. [Needs to be changed as per environment]

defaultAutoCommit Specify default value of auto commit flag. Specify ‘false’. It is forcibly set to ‘false’ when it is under Transaction Management.
(2)
 -
In case of Tomcat7, tomcat-jdbc-pool is used if factory attribute is omitted.
For more details about settings, refer to Attributes of The Tomcat JDBC Connection Pool.
(3)
 - Specify JNDI name of datasource. In case of Tomcat, specify the value specified in resource name “(1)-name” at the time of defining datasource.

5.1.2.1.2. Settings when using DataSource for which Bean is defined

When using datasource of OSS/Third-Party library or JDBC datasource of Spring Framework without using the datasource provided by Application Server,
bean for DataSource class needs to be defined in Bean definition file.
Settings when PostgreSQL is used as database and Apache Commons DBCP is used as datasource are given below.
  • xxx-env.xml
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"
    destroy-method="close">                                           <!-- (1) (8) -->
    <property name="driverClassName" value="org.postgresql.Driver" /> <!-- (2) -->
    <property name="url" value="jdbc:postgresql://localhost:5432/terasoluna" /> <!-- (3) -->
    <property name="username" value="postgres" />                     <!-- (4) -->
    <property name="password" value="postgres" />                     <!-- (5) -->
    <property name="defaultAutoCommit" value="false"/>               <!-- (6) -->
    <!-- (7) -->
</bean>
Sr. No. Description
(1)
 Specify implementation class of datasource. In the example, datasource class (org.apache.commons.dbcp2.BasicDataSource) provided by Apache Commons DBCP is specified.
(2)
 Specify JDBC driver class. In the example, JDBC driver class provided by PostgreSQL is specified.
(3)
 Specify URL. [Needs to be changed as per environment]
(4)
 Specify user name. [Needs to be changed as per environment]
(5)
 Specify password of user. [Needs to be changed as per environment]
(6)
 Specify default value of auto commit flag. Specify ‘false’. It is forcibly set to ‘false’ when it is under Transaction Management.
(7)
In BasicDataSource, configuration values common in JDBC, JDBC driver specific properties values, connection pooling configuration values can be specified other than the values mentioned above.
For more details about settings, refer to DBCP Configuration.
(8)
In the example, values are specified directly; however, for fields where configuration values change with the environment, actual configuration values should be specified in properties file using Placeholder(${…}).
For Placeholder, refer to PropertyPlaceholderConfigurer of Spring Reference Document.

5.1.2.2. Settings to enable transaction management

For basic settings to enable transaction management, refer to Settings for using transaction management of Domain Layer Implementation.

For PlatformTransactionManager, the class to be used changes depending on the O/R Mapper used; hence for detailed settings, refer to:

5.1.2.3. JDBC debug log settings

When more detailed information than the log output using O/R Mapper(MyBatis, Hibernate) is required, the information output using log4jdbc(log4jdbc-remix) can be used.
For details on log4jdbc, refer to log4jdbc project page.
For details on log4jdbc-remix, refer to log4jdbc-remix project page.

Warning

This is a debug setting; hence it should not be performed in case of applications that are to be released in performance test environment or production environment.

5.1.2.3.2. log4jdbc logger settings

  • logback.xml
<!-- (1) -->
<logger name="jdbc.sqltiming">
    <level value="debug" />
</logger>

<!-- (2) -->
<logger name="jdbc.sqlonly">
    <level value="warn" />
</logger>

<!-- (3) -->
<logger name="jdbc.audit">
    <level value="warn" />
</logger>

<!-- (4) -->
<logger name="jdbc.connection">
    <level value="warn" />
</logger>

<!-- (5) -->
<logger name="jdbc.resultset">
    <level value="warn" />
</logger>

<!-- (6) -->
<logger name="jdbc.resultsettable">
    <level value="debug" />
</logger>
Sr. No. Description
(1)
Logger to output SQL execution time and SQL statement wherein the value is set in bind variable. Since this SQL contains values for bind variables, it can be executed using DB access tool.
(2)
Logger to output SQL statement wherein the value is set in bind variable. The difference with (1) is that SQL execution time is not output.
(3)
Logger to exclude ResultSet interface, call methods of JDBC interface and to output arguments and return values. This log is useful for analyzing the JDBC related issues; however volume of the output log is large.
(4)
Logger to output connected/disconnected events and number of connections in use. This log is useful for analyzing connection leak, but it need not be output unless there is connection leak issue.
(5)
Logger to call methods of ResultSet interface and output arguments and return values. This log is useful during analysis when actual result differs from expected result; however volume of the output log is large.
(6)
Logger to output the contents of ResultSet by converting them into a format so that they can be easily verified. This log is useful during analysis when actual result differs from expected result; however volume of the output log is large.

Warning

Large amount of log is output depending on the type of logger; hence only the required logger should be defined or output.

In the above sample, log level for loggers which output very useful logs during development, is set to "debug". As for other loggers, the log level needs to be set to "debug" whenever required.

When the application is to be released in performance test environment or production environment, log using log4jdbc logger should not be output at the time of normal end of process.

Typically log level should be set to "warn".

5.1.2.3.3. Settings of log4jdbc option

Default operations of log4jdbc can be customized by placing properties file log4jdbc.propertiesunder class path.

  • log4jdbc.properties
# (1)
log4jdbc.dump.sql.maxlinelength=0
# (2)
Sr. No. Description
(1)
 Specify word-wrap setting for SQL statement. If ‘0’ is specified, SQL statement is not wrapped.
(2)
 For option details, refer to log4jdbc project page.

5.1.3. How to extend

5.1.3.1. Settings for using multiple datasources

Todo

TBD

Following details will be added in future.

  • Example of settings based on notes to be considered while using multiple datasources
  • Example of implementation if it is getting impacted

5.1.4. How to resolve the problem


5.1.4.1. How to resolve N+1

N+1 occurs when more number of SQL statements need to be executed in accordance with the number of records to be fetched from the database. This problem causes high load on the database and deteriorates response time.

Details are given below.

about N+1 Problem
Sr. No. Description
(1)
Search the records matching the search conditions from MainTable.
In the above example, col1 of MainTable fetches 'Foo' records and the total records fetched are 20.
(2)
For each record searched in (1), related records are fetched from SubTable.
In the above example, the id column of SubTable fetches the same records as the id column of records fetched in (1).
This SQL is executed for number of records fetched in (1).
In the above example, SQL is executed totally 21 times.
Supposing there are 3 SubTables, SQL is executed totally 61 times; hence countermeasures are required.

Typical example to resolve N+1 is given below.

5.1.4.1.1. Resolving N+1 using JOINs (Join Fetch)

By performing JOIN on SubTable and MainTable, records of MainTable and SubTable are fetched by executing SQL once.
When relation of MainTable and SubTable is 1:1, check whether N+1 can be resolved using this method.
about solve N+1 Problem using JOIN
Sr. No. Description
(1)
When searching records matching the search conditions, the records are fetched in batch from MainTable and SubTable, by performing JOIN on SubTable.
In the above example, col1 of MainTable collectively fetches 'Foo' records and records of SubTable that match the id of the records matching with search conditions.
When there are duplicate column names, it is necessary to assign alias name in order to identify the table to which that column belongs.
If JOIN (Join Fetch) is used, all the required data can be fetched by executing SQL once.

Note

When performing JOIN by JPQL

For example of performing JOIN using JPQL, refer to JOIN FETCH.

Warning

When relation with SubTable is 1:N, the problem can be resolved using JOIN (Join Fetch); however the following points should be noted.

  • When JOIN is performed on records having 1:N relation, unnecessary data is fetched depending on the number of records in SubTable. For details, refer to Notes during collective fetch.
  • When using JPA (Hibernate), if N portions in 1:N are multiple, then it is necessary to use java.util.Set instead of java.util.List as a collection type storage N portion.

5.1.5. Appendix

5.1.5.2. About Sequencer

Sequencer is a common library for fetching sequence value.
Use the sequence value fetched from Sequencer as a configuration value of primary key column of the database.

Note

Reason for creating Sequencer as a common library

The reason for creating Sequencer is that there is no mechanism to format the sequence value as string in ID generator functionality of JPA. In actual application development, sometimes the formatted string is also set as primary key; hence Sequencer is provided as common library.

When value set as primary key is number, it is recommended to use ID generator functionality of JPA. For ID generator functionality of JPA, refer to How to add entities of Database Access (JPA).

The primary objective of creating Sequencer is to supplement functions which are not supported by JPA; but it can also be used when sequence value is required in the processes not relating to JPA.

5.1.5.2.1. About classes provided by common library

List of classes of Sequencer functionality of common library is as follows:
For usage example, refer to How to use common library of How to use.
Sr. No. Class name Description
org.terasoluna.gfw.common.sequencer.
Sequencer
Interface that defines the method to fetch subsequent sequence value (getNext) and method to return current sequence value (getCurrent).
org.terasoluna.gfw.common.sequencer.
JdbcSequencer
Implementation class of Sequencer interface for JDBC.
This class is used to fetch sequence value by executing SQL in the database.
For this class, it is assumed that values are fetched from sequence object of the database; however it is also possible to fetch the values from other than sequence object by calling function stored in the database.

5.1.5.2.2. How to use common library

Define a bean for Sequencer.

  • xxx-infra.xml
<!-- (1) -->
<bean id="articleIdSequencer" class="org.terasoluna.gfw.common.sequencer.JdbcSequencer">
     <!-- (2) -->
    <property name="dataSource" ref="dataSource" />
     <!-- (3) -->
    <property name="sequenceClass" value="java.lang.String" />
    <!-- (4) -->
    <property name="nextValueQuery"
        value="SELECT TO_CHAR(NEXTVAL('seq_article'),'AFM0000000000')" />
    <!-- (5) -->
    <property name="currentValueQuery"
        value="SELECT TO_CHAR(CURRVAL('seq_article'),'AFM0000000000')" />
</bean>
Sr. No. Description
(1)
Define a bean for class that implements org.terasoluna.gfw.common.sequencer.Sequencer.
In the above example, (JdbcSequencer) class for fetching sequence value by executing SQL is specified.
(2)
Specify the datasource for executing the SQL to fetch sequence value.
(3)
Specify the type of sequence value to be fetched.
In the above example, since conversion to string is done using SQL; java.lang.String type is specified.
(4)
Specify SQL for fetching subsequent sequence value.
In the above example, sequence value fetched from sequence object of (PostgreSQL) database is formatted as string.
When sequence value fetched from the database is 1, "A0000000001" is returned as return value of Sequencer#getNext() method.
(5)
Specify SQL for fetching current sequence value.
When sequence value fetched from the database is 2, "A0000000002" is returned as return value of Sequencer#getCurrent() method.

Fetch sequence value from Sequencer for which bean is defined.

  • Service
// omitted

// (1)
@Inject
@Named("articleIdSequencer") // (2)
Sequencer<String> articleIdSequencer;

// omitted

@Transactional
public Article createArticle(Article inputArticle) {

    String articleId = articleIdSequencer.getNext(); // (3)
    inputArticle.setArticleId(articleId);

    Article savedArticle = articleRepository.save(inputArticle);

    return savedArticle;
}
Sr. No. Description
(1)
Inject Sequencer object for which bean is defined.
In the above example, since sequence value is fetched as formatted string, java.lang.String type is specified as generics type of Sequencer.
(2)
Specify bean name of the bean to be injected in value attribute of @javax.inject.Named annotation.
In the above example, bean name ("articleIdSequencer") defined in xxx-infra.xml is specified.
(3)
Call Sequencer#getNext() method and fetch the subsequent sequence value.
In the above example, fetched sequence value is used as Entity ID.
When fetching current sequence value, call Sequencer#getCurrent() method.

Tip

When Sequencer for which bean is defined is 1, @Named annotation can be omitted. When specifying multiple sequencers, bean name needs to be specified using @Named annotation.

5.1.5.3. Classes provided by Spring Framework for converting to data access exception

Classes of Spring Framework which play a role in converting an exception to data access exception, are as follows:

Classes of Spring Framework for converting to data access exception
Sr. No. Class name Description
org.springframework.jdbc.support.
SQLErrorCodeSQLExceptionTranslator
 When MyBatis or JdbcTemplate is used, JDBC exception is converted to data access exception of Spring Framework using this class. Conversion rules are mentioned in XML file. XML file used by default is org/springframework/jdbc/support/sql-error-codes.xml in spring-jdbc.jar. It is also possible to change the default behavior by placing XML file (sql-error-codes.xml) just below class path.
org.springframework.orm.jpa.vendor.
HibernateJpaDialect
 When JPA (Hibernate implementation) is used, O/R Mapper exception (Hibernate exception) is converted to data access exception of Spring Framework using this class.
org.springframework.orm.jpa.
EntityManagerFactoryUtils
 If an exception that cannot be converted by HibernateJpaDialect has occurred, JPA exception is converted to data access exception of Spring Framework using this class.
Sub classes of
org.hibernate.dialect.Dialect
 When JPA (Hibernate implementation) is used, exceptions are converted to JDBC exception and O/R Mapper exception using this class.

5.1.5.4. JDBC datasource classes provided by Spring Framework

Spring Framework provides implementation of JDBC datasource. However since they are very simple classes, they are rarely used in production environment.
These classes are mainly used during Unit Testing.
JDBC datasource classes provided by Spring Framework
Sr. No. Class name Description
org.springframework.jdbc.datasource.
DriverManagerDataSource
 Datasource class for creating new connection by calling java.sql.DriverManager#getConnection when connection fetch request is received from the application. When connection pooling is required, Application Server datasource or datasource of OSS/Third-Party library should be used.
org.springframework.jdbc.datasource.
SingleConnectionDataSource
 Child class of DriverManagerDataSource.This class provides implementation of single shared connection. This is a datasource class for unit test which works with single thread. Even in case of Unit Testing, if this class is used when datasource is to be accessed with multithread, care needs to be taken as it may not show the expected behavior.
org.springframework.jdbc.datasource.
SimpleDriverDataSource
 Datasource class for creating new connection by calling java.sql.Driver#getConnection when connection fetch request is received from the application. When connection pooling is required, Application Server datasource or datasource of OSS/Third-Party library should be used.
Spring Framework provides adapter classes with extended JDBC datasource operations.
Specific adapter classes are introduced below.
JDBC datasource adapter classes provided by Spring Framework
Sr. No. Class name Description
org.springframework.jdbc.datasource.
TransactionAwareDataSourceProxy
 Adapter class for converting a datasource which does not store transactions, into a datasource storing Spring Framework transactions.
org.springframework.jdbc.datasource.lookup.
IsolationLevelDataSourceRoute
 Adapter class for switching the datasource to be used based on independence level of an active transaction.