6.2. Database Access (MyBatis3)¶
Table of Contents
- Overview
- How to use
- pom.xml settings
- Settings for integration of MyBatis3 and Spring
- MyBatis3 settings
- Implementation of database access process
- How to map a JavaBean in Search results
- Search process for Entity
- Entity registration process
- Update process of Entity
- Delete process for Entity
- Implementing dynamic SQL
- Escape during LIKE search
- SQL Injection countermeasures
- How to extend
- Appendix
6.2.1. Overview¶
This chapter describes how to access database using MyBatis3.
This guideline presumes the use of Mapper interface of MyBatis3 as a Repository interface. Refer to “Implementation of Repository” for Repository interface.
6.2.1.1. About MyBatis3¶
In this guideline, CRUD operation of Entity is performed by using Mapper interface added from MyBatis3. Refer to “Mapper interface mechanism” for details of Mapper interface.
This guideline does not cover explanation of all the functionalities of MyBatis3, Hence, it is recommended to refer “MyBatis 3 REFERENCE DOCUMENTATION ” .
6.2.1.1.1. Component structure of MyBatis3¶
Sr. No. Component/Configuration file Description
MyBatis configuration file XML file that explains operation settings of MyBatis3.
It is a file that explains details such as connecting destination for database, path of mapping file, operation settings of MyBatis and so on. It is not necessary to specify connecting destination of database and mapping file path settings in the configuration file, when using it by integrating with Spring. However, settings are performed when changing or extending default operations of MyBatis3.
org.apache.ibatis.session.
SqlSessionFactoryBuilder
A component to read MyBatis configuration file and generate
SqlSessionFactory
.This component is not directly handled by the application class when used by integrating with Spring.
org.apache.ibatis.session.
SqlSessionFactory
A component to generate
SqlSession
.This component is not directly handled by the application class when used by integrating with Spring.
org.apache.ibatis.session.
SqlSession
A component to provide API for SQL execution and transaction control.
It is the component that plays the most important role when accessing database using MyBatis3.
When this component is used by integrating with Spring, it is not directly handled by the application class.
Mapper interface An interface to call the SQL defined in mapping file in typesafe.
Developer needs to create only the interface, as MyBatis3 automatically generates an implementation class for the Mapper interface.
Mapping file XML file that explains SQL and O/R mapping settings.
- Processes that are performed at the start of the application. Processes (1) to (3) mentioned below correspond to this type.
- Processes that are performed for each request from the client. Processes (4) to (10) mentioned below correspond to this type.
Sr. No. Description
Application requests building SqlSessionFactory
forSqlSessionFactoryBuilder
.
SqlSessionFactoryBuilder
reads MyBatis configuration file for generatingSqlSessionFactory
.
SqlSessionFactoryBuilder
generatesSqlSessionFactory
based on the definition of MyBatis configuration file.
Sr. No. Description
Client requests a process for the application.
Application fetches SqlSession
fromSqlSessionFactory
that is built by usingSqlSessionFactoryBuilder
.
SqlSessionFactory
generatesSqlSession
and returns it to the application.
Application fetches the implementation object of Mapper interface from SqlSession
.
Application calls Mapper interface method.
Refer to “Mapper interface mechanism” for mechanism of Mapper interface.
Implementation object of Mapper interface calls SqlSession
method and requests SQL execution.
SqlSession
fetches the SQL to be executed from mapping file and executes SQL.Tip
Transaction control
Commit and rollback for the transaction are performed by calling
SqlSession
API from application code. However, it is not described in the above flow.When integrated with Spring, Spring transaction control functionality performs commit and rollback. As a result, the API controlling
SqlSession
is not called directly from application class.
6.2.1.2. Regarding MyBatis3 and Spring integration¶
There are following advantages of using MyBatis-Spring.
- MyBatis3 SQL can be executed in the transactions managed by Spring. Hence, it is not necessary to control the transactions that are dependent on MyBatis3 API.
- MyBatis3 exception is converted to a generic exception (``org.springframework.dao.DataAccessException``) provided by Spring. Hence, exception process that is not dependent on MyBatis3 API can be implemented.
- The overall initialization process for using MyBatis3 is performed by MyBatis-Spring API. Hence, MyBatis3 API need not be used directly.
- Mapper object can be injected in Singleton Service class to generate a thread safe Mapper object.
This guideline presumes the use of MyBatis-Spring.
This guideline does not cover explanation of all the functionalities of MyBatis-Spring, Hence, it is recommended to refer “Mybatis-Spring REFERENCE DOCUMENTATION ” .
6.2.1.2.1. Component structure of MyBatis-Spring¶
Sr. No. Component/Configuration file Description
org.mybatis.spring.
SqlSessionFactoryBean
Component that builds
SqlSessionFactory
and stores objects on Spring DI container.In standard MyBatis3,
SqlSessionFactory
is built based on the information defined in MyBatis configuration file. However, By usingSqlSessionFactoryBean
,SqlSessionFactory
can be built even in the absence of MyBatis configuration file. It can also be used in combination.
org.mybatis.spring.mapper.
MapperFactoryBean
Component that builds Singleton Mapper object and stores objects on Spring DI container.
Mapper object generated by MyBatis3 standard mechanism is not thread safe. Hence, it was necessary to assign an instance for each thread. Mapper object created by MyBatis-Spring component can generate a thread safe Mapper object. As a result, DI can be applied to Singleton components like Service etc.
org.mybatis.spring.
SqlSessionTemplate
SqlSession
component of Singleton version that implementsSqlSession
interface.
SqlSession
object generated by MyBatis3 standard mechanism is not thread safe. Hence, it was necessary to assign an instance for each thread.SqlSession
object generated by MyBatis-Spring component can generate a thread safeSqlSession
object. As a result, DI can be applied to Singleton components like Service etc.However, this guideline does not assume handling
SqlSession
directly.
The flow by which the main components of MyBatis-Spring access the database is explained below. Processes to access database can be broadly divided into two types.
- Processes that are performed at the start of the application. Processes (1) to (4) mentioned below correspond to this type.
- Processes that are performed for each request from the client. Processes (5) to (11) mentioned below correspond to this type.
Processes that are performed at the start of the application are executed by the following flow.
Sr. No. Description
SqlSessionFactoryBean
requests buildingSqlSessionFactory
forSqlSessionFactoryBuilder
.
SqlSessionFactoryBuilder
reads MyBatis configuration file for generatingSqlSessionFactory
.
SqlSessionFactoryBuilder
generatesSqlSessionFactory
based on the definition of MyBatis configuration file.
SqlSessionFactory
thus generated is stored by the Spring DI container.
MapperFactoryBean
generates a thread safeSqlSession
(SqlSessionTemplate
) and a thread safe Mapper object (Proxy object of Mapper interface).Mapper object thus generated is stored by Spring DI container and DI is applied for Service class etc. Mapper object provides a thread safe implementation by using thread safe
SqlSession
(SqlSessionTemplate
).
Processes that are performed for each request from client are executed by the following flow.
Sr. No. Description
Client requests a process for the application.
Application (Service) calls the method of Mapper object (Proxy object that implements Mapper interface) injected by DI container.
Refer to “Mapper interface mechanism” for Mapper interface mechanism.
Mapper object calls SqlSession
(SqlSessionTemplate
) method corresponding to the called method.
SqlSession
(SqlSessionTemplate
) calls the proxy enabled and thread safeSqlSession
method.
Proxy enabled and thread safe
SqlSession
uses MyBatis3 standardSqlSession
assigned to the transaction.When
SqlSession
assigned to the transaction does not exist,SqlSessionFactory
method is called to fetchSqlSession
of standard MyBatis3.
SqlSessionFactory
returns MyBatis3 standardSqlSession
.Since the returned MyBatis3 standard
SqlSession
is assigned to the transaction, if it is within the same transaction, sameSqlSession
is used without creating a new one.
MyBatis3 standard SqlSession
fetches SQL to be executed from mapping file and executes the SQL.Tip
Transaction control
Although it is not explained in the flow, the commit and rollback of transaction is performed by Spring transaction control function.
Refer to “Regarding transaction management” for how to control transaction using Spring transaction control function.
6.2.2. How to use¶
Actual configuration and implementation methods for accessing database using MyBatis3 are explained below.
The explanation hereafter can be broadly classified as below.
Sr. No. Classification Description
Overall application settings Settings for using MyBatis3 in an application and for changing MyBatis3 operations, are explained below.
The contents explained here are required when application architecture performs settings at the time of project start-up.Therefore, application developers need not be aware of these contents separately.
Following sections correspond to this classification.
When a project is generated from a Blank project for MyBatis3 , a major part of the configuration explained above is already configured. Hence, the application architect determines the project characteristics and adds or changes the configuration as required.
How to implement data access process How to implement basic data access process using MyBatis3 is explained.
The contents explained here are necessary for the application developers at the time of implementation.
Following sections correspond to this classification.
6.2.2.1. pom.xml settings¶
pom.xml
.pom.xml
(projectName-domain/pom.xml
) of domain project.<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <artifactId>projectName-domain</artifactId> <packaging>jar</packaging> <parent> <groupId>com.example</groupId> <artifactId>mybatis3-example-app</artifactId> <version>1.0.0-SNAPSHOT</version> <relativePath>../pom.xml</relativePath> </parent> <dependencies> <!-- omitted --> <!-- (1) --> <dependency> <groupId>org.terasoluna.gfw</groupId> <artifactId>terasoluna-gfw-mybatis3-dependencies</artifactId> <type>pom</type> </dependency> <!-- omitted --> </dependencies> <!-- omitted --> </project>
Sr. No. Description
Add terasoluna-gfw-mybatis3 to dependencies. Dependency relation with MyBatis3 and MyBatis-Spring is defined in terasoluna-gfw-mybatis3. Note
In the above setting example, since it is assumed that the dependent library version is managed by the parent project terasoluna-gfw-parent, specifying the version in pom.xml is not necessary.
Warning
Configuration while using in Java SE 7 environment
terasoluna-gfw-mybatis3-dependencies configures dependency relation considering Java SE 8 as a prerequisite. Java SE 8 dependency library should be excluded as below while using in Java SE 7 environment. For java SE 8 dependency library, refer “OSS Versions ” of architectural overview.<dependency> <groupId>org.terasoluna.gfw</groupId> <artifactId>terasoluna-gfw-mybatis3-dependencies</artifactId> <exclusions> <exclusion> <groupId>org.mybatis</groupId> <artifactId>mybatis-typehandlers-jsr310</artifactId> </exclusion> </exclusions> </dependency>
6.2.2.2. Settings for integration of MyBatis3 and Spring¶
6.2.2.2.1. Datasource settings¶
When MyBatis3 and Spring are integrated, the datasource managed by Spring DI container should be used.
When a project is generated from a Blank project for MyBatis3 , datasource of Apache Commons DBCP is already configured. Hence the settings should be changed in accordance with project requirements.
Refer to “Datasource settings ” for the details on how to configure datasource.
6.2.2.2.2. Transaction control settings¶
PlatformTransactionManager
managed by Spring DI container should be used for transaction control.DataSourceTransactionManager
that performs transaction control by calling JDBC API, is used.DataSourceTransactionManager
is already configured.Configuration example is as given below.
projectName-env/src/main/resources/META-INF/spring/projectName-env.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jee="http://www.springframework.org/schema/jee" xmlns:jdbc="http://www.springframework.org/schema/jdbc" xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> <!-- omitted --> <!-- (1) --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <!-- (2) --> <property name="dataSource" ref="dataSource" /> <!-- (3) --> <property name="rollbackOnCommitFailure" value="true" /> </bean> <!-- omitted --> </beans>
Sr. No. Description
Specify org.springframework.jdbc.datasource.DataSourceTransactionManager
asPlatformTransactionManager
.
Specify configured datasource bean in
dataSource
property.When SQL is executed in the transaction, connection is fetched from datasource specified here.
Rollback process is called when an error occurs during commit.
By adding this setting, risk of “Unintentional commit which occurs when a connection with undefined operation returns to connection pool (commit while reusing a connection, implicit commit at the time of closing a connection etc)” can be reduced. However, since an error is likely to occur at the time of rollback, it should be noted that a risk of occurrence of unintentional commit is still a possibility.
Note
bean ID of PlatformTransactionManager
It is recommended to specify
transactionManager
in id attribute.If a value other than
transactionManager
is specified, the same value must be specified in transaction-manager attribute of<tx:annotation-driven>
tag.
When a transaction manager provided by application server is used, use org.springframework.transaction.jta.JtaTransactionManager
that performs transaction control by calling JTA API.
Configuration example is as given below.
projectName-env/src/main/resources/META-INF/spring/projectName-env.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jee="http://www.springframework.org/schema/jee" xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> <!-- omitted --> <!-- (1) --> <tx:jta-transaction-manager /> <!-- omitted --> </beans>
Sr. No. Description
If <tx:jta-transaction-manager />
is specified, bean definition of optimumJtaTransactionManager
is performed for the application server.
6.2.2.2.3. MyBatis-Spring settings¶
When MyBatis3 and Spring are integrated, it is necessary to carry out following
- Generation of
SqlSessionFactory
that customizes the processes necessary for integrating MyBatis3 and Spring - Generation of thread safe Mapper object (Proxy object of Mapped interface)
by using MyBatis-Spring components.
When a project is generated from a Blank project for MyBatis3 , the settings for integration of MyBatis3 and Spring are already configured.
Configuration example is as given below.
projectName-domain/src/main/resources/META-INF/spring/projectName-infra.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd"> <import resource="classpath:/META-INF/spring/projectName-env.xml" /> <!-- (1) --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <!-- (2) --> <property name="dataSource" ref="dataSource" /> <!-- (3) --> <property name="configLocation" value="classpath:/META-INF/mybatis/mybatis-config.xml" /> </bean> <!-- (4) --> <mybatis:scan base-package="com.example.domain.repository" /> </beans>
Sr. No. Description
Define the bean for SqlSessionFactoryBean
as the component for generatingSqlSessionFactory
.
Specify a bean of configured datasource in
dataSource
property.When SQL is executed in MyBatis3 process, the connection is fetched from the datasource specified here.
Specify MyBatis configuration file path in
configLocation
property.The file specified is read when generating
SqlSessionFactory
.
Define
<mybatis:scan>
for scanning Mapper interface and specify the base package that stores the Mapper interface inbase-package
attribute.The Mapper interface stored under specified package is scanned and a thread safe Mapper object (Proxy object of Mapper interface) is automatically generated.
[Package decided for each project should be the specified package]
Note
How to configure MyBatis3
When
SqlSessionFactoryBean
is used, MyBatis3 configuration can be specified directly in the bean property rather than MyBatis configuration file. However, in this guideline, it is recommended to specify MyBatis3 settings in the MyBatis standard configuration file.
6.2.2.3. MyBatis3 settings¶
Note
Storage location for MyBatis configuration file
In this guideline, it is recommended to store MyBatis configuration file in
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
.When a project is generated from a Blank project for MyBatis3 , the file described above is already stored.
6.2.2.3.1. fetchSize
settings¶
fetchSize
must be specified for JDBC driver.fetchSize
is a parameter that specifies data record count that can be fetched in a single communication between JDBC driver and database.Since default value of JDBC driver is used if fetchSize
is not specified,
following issues are likely to appear due to JDBC driver that is being used.
- “Performance degradation” when default value of JDBC driver is small
- “Out of memory” when default value of JDBC driver is large or has no restriction
MyBatis3 can specify fetchSize
by using 2 methods shown below to control the occurrences of these issues.
- Specifying “default
fetchSize
” applicable for all queries - Specifying “
fetchSize
for query unit” applicable to a specific query
Note
“Regarding default fetchSize”
“Default
fetchSize
”can be used in MyBatis3.3.0 and subsequent versions.
How to specify “default fetchSize
” is shown below.
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org/DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <!-- (1) --> <setting name="defaultFetchSize" value="100" /> </settings> </configuration>
Sr. No. Description
Specify data record count fetched in a single communication, in defaultFetchSize
.
Note
How to specify “fetchsize of query unit”
When
fetchSize
is to be specified in query unit, a value must be specified infetchSize
attribute of XML element (<select>
element) to describe SQL for search.
Note that, when a query for returning large volume of data is to be described, usage of “Implementation of ResultHandler” must also be explored.
6.2.2.3.2. SQL execution mode settings¶
MyBatis3 provides following three modes to execute SQL.
Sr. No. Mode Description
SIMPLE Creates a new ``java.sql.PreparedStatement`` for each SQL execution.
It is a default behavior for MyBatis wherein a Blank project for MyBatis3 also operates in a
SIMPLE
mode.
REUSE Caches and reuses
PreparedStatement
.If
REUSE
mode is used when same SQL is to be executed for multiple times in the same transaction, enhanced performance can be expected as compared toSIMPLE
mode.This is because it analyses SQL and reduces number of executions for the process that generates
PreparedStatement
.
BATCH Performs batch execution for Update SQL. (Executes SQL by using
java.sql.Statement#executeBatch()
)If
BATCH
mode is used to execute a large number of Update SQLs in succession, in the same transaction, improved performance can be expected as compared toSIMPLE
mode orREUSE
mode.This is because it reduces
- Number of executions for the process that generates
PreparedStatement
by analyzing the SQL- Number of communications with the server
However, when
BATCH
mode is used, MyBatis behavior operates inSIMPLE
mode or in a mode different fromSIMPLE
mode. Refer to “Precautions when using batch mode Repository” for basic differences and precautions.
6.2.2.3.3. TypeAlias settings¶
When TypeAlias is used, an alias (short name) can be assigned for the Java class specified in mapping file.
When TypeAlias is not used, it is necessary to specify the fully qualified class name (FQCN) of Java class in type
attribute, parameterType
attribute and resultType
attribute specified in a mapping file
As a result, decrease in description efficiency and increase in typographical errors of mapping file are the areas of concern.
In this guideline, it is recommended to use TypeAlias to improve description efficiency, reduce typographical errors and improve readability.
When project is generated from a Blank project for MyBatis3 for MyBatis3,
the class stored under the package (${projectPackage}.domain.model
) that stores Entity is considered as a target for TypeAlias.
Settings should be added as and when required.
How to configure a TypeAlias is given below.
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <typeAliases> <!-- (1) --> <package name="com.example.domain.model" /> </typeAliases> </configuration>
Sr. No. Description
In
name
attribute ofpackage
element, specify the package name in which the class that sets alias is stored.The part from which package is removed acts as an alias for the class that is stored under the specified package. In the above example, the alias for
com.example.domain.model.Account
class isAccount
.[Package decided for each project should be the specified package]
Tip
** How to configure Type Alias in class unit**
A method to configure TypeAlias in class unit and a method to clearly specify an alias are provided in Type Alias settings. Refer to “TypeAlias settings” of Appendix for details.
The description example of mapping file when using TypeAlias is as below.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.account.AccountRepository"> <resultMap id="accountResultMap" type="Account"> <!-- omitted --> </resultMap> <select id="findOne" parameterType="string" resultMap="accountResultMap"> <!-- omitted --> </select> <select id="findByCriteria" parameterType="AccountSearchCriteria" resultMap="accountResultMap"> <!-- omitted --> </select> </mapper>Tip
MyBatis3 standard Alias name
An alias name is already configured for general Java classes like primitive type and primitive wrapper type.
Refer to “MyBatis 3 REFERENCE DOCUMENTATION(Configuration XML-typeAliases-) ” for the alias names which are already configured.
6.2.2.3.4. Mapping settings of NULL value and JDBC type¶
null
value and specifying a recognizable JDBC type.null
value, if an error accompanied by following stack traces occurs, mapping of null
value and JDBC type becomes necessary.OTHER
is specified in MyBatis3. However, an error may occur in JDBC driver due to OTHER
.java.sql.SQLException: Invalid column type: 1111 at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3916) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0] at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4541) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0] at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4523) ~[ojdbc6-11.2.0.2.0.jar:11.2.0.2.0] ...
Note
Operations when using Oracle
When Oracle is used as a database and if default settings is used as is, it has been confirmed that errors occur. Although behavior may change depending on the version, it should be described as ‘change in the settings may be required when Oracle is used’.
The version wherein error is confirmed to occur in Oracle 11g R1. The error can be resolved by changing the settings wherein
NULL
type of JDBC type is mapped.
How to change the default behavior of MyBatis3 is given below.
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org/DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <!-- (1) --> <setting name="jdbcTypeForNull" value="NULL" /> </settings> </configuration>
Sr. No. Description
Specify JDBC type in jdbcTypeForNull.
In the above example,
NULL
type is specified as JDBC type ofnull
value.Tip
How to resolve at item level
As an alternate method of resolving the error, an appropriate JDBC type supporting the Java type is set in the inline parameters of property wherein
null
value may be set.However, when JDBC type is individually set in inline parameter, description content of mapping file and occurrence of specified mistakes may increase. Hence, in this guideline, it is recommended to resolve the errors in the overall configuration. If errors are not resolved even after changing the overall configuration, individual setting can be applied only for the property wherein an error has occurred.
6.2.2.3.5. TypeHandler settings¶
TypeHandler
is used when mapping Java class and JDBC type.
Basically, it is used when
- A Java class object is set as a bind parameter of
java.sql.PreparedStatement
while executing an SQL. - A value is fetched from
java.sql.ResultSet
that is obtained as SQL execution result.
A TypeHandler
is provided by MyBatis3 for general Java classes like primitive type and primitive wrapper type class.
Specific settings are not required.
Note
Regarding implementation for BLOB and CLOB
TypeHandler
added by MyBatis 3.4 implements conversion of BLOB andjava.io.InputStream
, and CLOB andjava.io.Reader
by using API added by JDBC 4.0 (Java 1.6). In case of a JDBC driver with JDBC 4.0 support, it is not necessary to implement a newTypeHandler
since type handler for conversion of BLOB⇔InputStream
and CLOB⇔Reader
is enabled by default.
Configuration while using JSR-310 Date and Time API
When a class which represents date and time offered by JSR-310 Date and Time API in MyBatis3 is used, TypeHandler
offered by a library different from that of MyBatis (mybatis-typehandlers-jsr310
) is used.
Note that, mybatis-typehandlers-jsr310
can be used by default in the blank project for MyBatis3 .
Further, since TypeHandler
is auto-detected in MyBatis3.4, it is not necessary to add TypeHandler
to configuration file of MyBatis.
Tip
Refer to “MyBatis 3 REFERENCE DOCUMENTATION(Configuration XML-typeHandlers-) ” for a TypeHandler
provided by MyBatis3.
Tip
Enum type mapping
Enum type is mapped with a constant identifier (string) of Enum in the default behavior of MyBatis3.
In case of Enum type shown below,
it is mapped with strings like WAITING_FOR_ACTIVE
, ACTIVE
, EXPIRED
, LOCKED
and stored in the table.
package com.example.domain.model; public enum AccountStatus { WAITING_FOR_ACTIVE, ACTIVE, EXPIRED, LOCKED }
In MyBatis, Enum type can be mapped with the numeric value (order in which the constants are defined). For how to map Enum type with a numeric value, refer to “MyBatis 3 REFERENCE DOCUMENTATION(Configuration XML-Handling Enums-) “.
Creating a TypeHandler
is required while mapping a Joda-Time class and JDBC type not supported by MyBatis3.
Basically, creating a TypeHandler
is required while mapping org.joda.time.DateTime
type of “Date Operations (Joda Time)” recommended by this guideline and TIMESTAMP
type of JDBC type.
For creation of TypeHandler
which maps Joda-Time class and JDBC type,
refer “Implementation of TypeHandler”.
How to apply a TypeHandler
thus created in MyBatis is explained below.
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org/DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <typeHandlers> <!-- (1) --> <package name="com.example.infra.mybatis.typehandler" /> </typeHandlers> </configuration>
Sr. No. Description
Configure
TypeHandler
in MyBatis configuration file.Specify a package name wherein the created
TypeHandler
is stored, in the name attribute ofpackage
element. TheTypeHandler
stored under specified package is automatically detected by MyBatis.Tip
In the above example, although
TypeHandler
stored under specified package is automatically detected by MyBatis, it can also be configured in class unit.
typeHandler
element is used when settingTypeHandler
in class unit.
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
<typeHandlers> <typeHandler handler="xxx.yyy.zzz.CustomTypeHandler" /> <package name="com.example.infra.mybatis.typehandler" /> </typeHandlers>Further, when a bean stored by DI container is to be used in
TypeHandler
,TypeHandler
can be specified in bean definition file.
projectName-domain/src/main/resources/META-INF/spring/projectName-infra.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd"> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="oracleDataSource" /> <property name="configLocation" value="classpath:/META-INF/mybatis/mybatis-config.xml" /> <property name="typeHandlers"> <list> <bean class="xxx.yyy.zzz.CustomTypeHandler" /> </list> </property> </bean> </beans>The mapping of Java class wherein
TypeHandler
is applied and JDBC type is specified as below.
- Specify as an attribute value of
typeHandler
element in MyBatis configuration file- Specify in
@org.apache.ibatis.type.MappedTypes
annotation and@org.apache.ibatis.type.MappedJdbcTypes
annotation- Specify by inheriting a base class (
org.apache.ibatis.type.BaseTypeHandler
) ofTypeHandler
provided by MyBatis3For details, refer to “MyBatis 3 REFERENCE DOCUMENTATION(Configuration XML-typeHandlers-) “.
Tip
Although each of the above example is a configuration method to be applied to overall application, an individual
TypeHandler
can also be specified for each field. It is used while overwriting aTypeHandler
that is applicable for overall application.<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.example.domain.repository.image.ImageRepository"> <resultMap id="resultMapImage" type="Image"> <id property="id" column="id" /> <!-- (2) --> <result property="imageData" column="image_data" typeHandler="XxxBlobInputStreamTypeHandler" /> <result property="createdAt" column="created_at" /> </resultMap> <select id="findOne" parameterType="string" resultMap="resultMapImage"> SELECT id ,image_data ,created_at FROM t_image WHERE id = #{id} </select> <insert id="create" parameterType="Image"> INSERT INTO t_image ( id ,image_data ,created_at ) VALUES ( #{id} /* (3) */ ,#{imageData,typeHandler=XxxBlobInputStreamTypeHandler} ,#{createdAt} ) </insert> </mapper>
Sr. No. Description
Specify a TypeHandler
that is applicable totypeHandler
attribute ofid
orresult
element while fetching the value from search result (ResultSet
).
Specify a TypeHandler
that is applicable totypeHandler
attribute of inline parameters while configuring a value in thePreparedStatement
.It is recommended to set TypeAlias in
TypeHandler
class when TypeHandler is to be individually specified for each field. Refer to “TypeAlias settings” for how to configure TypeAlias.
6.2.2.4. Implementation of database access process¶
A basic implementation method for accessing a database by using MyBatis3 function is explained below.
6.2.2.4.1. Creating Repository interface¶
A Repository interface is created for each Entity.
package com.example.domain.repository.todo; // (1) public interface TodoRepository { }
Sr. No. Description
Create a Repository interface as an interface for Java.
In the above example, a Repository interface is created for an Entity called
Todo
.
6.2.2.4.2. Creating Mapping file¶
A mapping file is created for Repository interface.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- (1) --> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> </mapper>
Sr. No. Description
Specify a fully qualified class name (FQCN) of Repository interface in namespace
attribute ofmapper
element.Note
Destination to store a mapping file
The mapping file can be stored at either of the locations given below.
- A directory that conforms to the determined rules to enable MyBatis3 to automatically read the mapping file
- An arbitrary directory
In this guideline, it is recommended to use a mechanism wherein mapping file is stored in the directory conforming to the rules determined by MyBatis3 thus enabling automatic reading of file.
It is necessary to store the mapping file on the class path at a level same as the package hierarchy of Repository interface to enable automatic reading of mapping file.
In particular, a mapping file (
TodoRepository.xml
) for Repository interface calledcom.example.domain.repository.todo.TodoRepository
should be stored inprojectName-domain/src/main/resources/com/example/domain/repository/todo
directory.
6.2.2.4.3. CRUD process implementation¶
How to implement a CRUD process and considerations when implementing SQL are explained here.
How to implement following processes for the basic CRUD operation is explained.
- How to map a JavaBean in Search results
- Search process for Entity
- Entity registration process
- Update process of Entity
- Delete process for Entity
- Implementing dynamic SQL
Note
It is important to note that the searched Entity is cached in the area called as local cache while implementing CRUD process by using MyBatis3.
Default behavior of local cache provided by MyBatis3 is as given below.
- Local cache is managed in a transaction unit.
- Entity is cached for each “statement ID + pattern of built SQL + parameter value bound to the built SQL + page position (fetch range)”.
In other words, when all the search APIs provided by MyBatis3 are called by the same parameter in a process within the same transaction, the instance of cached Entity is returned without executing the SQL from 2nd time onwards.
Here, it should be noted that Entity returned by MyBatis API and Entity managed by local cache consist of the same instance.
Tip
Local cache can also be changed so as to be managed in statement unit. When the local cache is to be managed in statement unit, MyBatis executes SQL each time and fetches the latest Entity.
The considerations while implementing SQL are explained below.
Before explaining the basic implementation, the components to be registered are explained below.
Sr. No. Component Description
Entity A JavaBean class that retains business process data handled by the application.
Refer to “Implementation of Entity” for details of Entity.
Repository interface An interface that defines the method to perform CRUD operation of Entity.
Refer to “Implementation of Repository” for details of Repository.
Service class A class for executing business process logic.
Refer to “Implementation of Service” for details of Service.
Note
In this guideline, Mapper interface of MyBatis3 is called as Repository interface in order to standardize the architecture terminology
The explanation hereafter is given presuming that the user has read “Implementation of Entity” “Implementation of Repository” and “Implementation of Service”.
6.2.2.5. How to map a JavaBean in Search results¶
How to map a JavaBean in the search results is explained before explaining the search process of Entity.
Two methods of automatic and manual, are provided in MyBatis3
to map JavaBean (Entity) in the search results (ResultSet
).
Since both the methods have distinct features, a mapping method to be used should be determined by considering the project features and features of SQL to be executed by the application.
Note
Mapping method to be used
The guideline provides two proposals such as
- Automatic mapping is used for simple mapping (mapping to a single object) whereas manual mapping is used in case of advanced mapping (mapping to related objects) is necessary.
- A uniform manual mapping is used
It is not mandatory to use any one of the two methods proposed above and they can be considered as one of the alternatives.
Architect should clearly identify the criteria for selecting manual mapping and automatic mapping for the programmers and look for a uniform mapping method for the entire application.
The respective features and examples for automatic mapping and manual mapping are explained below.
6.2.2.5.1. Automatic mapping for search results¶
In MyBatis3, a mechanism which automatically performs the mapping by matching column name and property name is provided
as a method to map search result (ResultSet
) column and JavaBean property.
Note
Features of automatic mapping
When automatic mapping is used, only SQL to be executed is described in the mapping file thus reducing the description content of mapping file.
By reducing the description, simple mistakes and modification locations while changing a column name or a property name can be reduced as well.
However, automatic mapping can only be used for single object. Manual mapping is required when mapping for the nested related objects.
Tip
Column names
Column name mentioned here does not signify the physical column name of a table but refers to the column which contains the search result (
ResultSet
) fetched by executing the SQL. Therefore, by using AS clause, matching a physical column name and a JavaBean property name is comparatively easier.
How to map search results in JavaBean using automatic mapping is shown below.
projectName-domain/src/main/resources/com/example/domain/repository/todo/TodoRepository.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <select id="findOne" parameterType="string" resultType="Todo"> SELECT todo_id AS "todoId", /* (1) */ todo_title AS "todoTitle", finished, /* (2) */ created_at AS "createdAt", version FROM t_todo WHERE todo_id = #{todoId} </select> </mapper>
Sr. No. Description
When physical column name of table and JavaBean property name are different, automatic mapping can be applied by using AS clause for matching.
When physical column name of table and JavaBean property name match, there is no need to specify the AS clause.
- JavaBean
package com.example.domain.model; import java.io.Serializable; import java.util.Date; public class Todo implements Serializable { private static final long serialVersionUID = 1L; private String todoId; private String todoTitle; private boolean finished; private Date createdAt; private long version; public String getTodoId() { return todoId; } public void setTodoId(String todoId) { this.todoId = todoId; } public String getTodoTitle() { return todoTitle; } public void setTodoTitle(String todoTitle) { this.todoTitle = todoTitle; } public boolean isFinished() { return finished; } public void setFinished(boolean finished) { this.finished = finished; } public Date getCreatedAt() { return createdAt; } public void setCreatedAt(Date createdAt) { this.createdAt = createdAt; } public long getVersion() { return version; } public void setVersion(long version) { this.version = version; } }Tip
How to map a column name separated by an underscore and a property name in camel case format
In the above example, the difference between a column name separated by an underscore and a property name in camel case format is resolved by using AS clause. However, it can be implemented by changing MyBatis3 configuration if only the difference between a column name separated by an underscore and a property name in camel case format is to be resolved.
When the physical column name of a table is separated by an underscore,
automatic mapping can be performed in JavaBean property in the camel case format
by adding following settings to MyBatis configuration file (mybatis-config.xml
).
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <!-- (3) --> <setting name="mapUnderscoreToCamelCase" value="true" /> </settings> </configuration>
Sr. No. Description
Add the settings to set
mapUnderscoreToCamelCase
to true.When it is set to true, the column name separated by an underscore is automatically converted to camel case format. As a typical example, when column name is
todo_id
, it is converted totodoId
and mapping is performed.
projectName-domain/src/main/resources/com/example/domain/repository/todo/TodoRepository.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <select id="findOne" parameterType="string" resultType="Todo"> SELECT todo_id, /* (4) */ todo_title, finished, created_at, version FROM t_todo WHERE todo_id = #{todoId} </select> </mapper>
Sr. No. Description
A simple SQL can be fetched since AS clause is not required to resolve the difference between the column name separated by an underscore and the property name in the camel case format.
6.2.2.5.2. Manual mapping of search results¶
MyBatis3 provides a mechanism to manually map search result (ResultSet
) column and JavaBean property
by defining their association in the mapping file.
Note
Features of manual mapping
When manual mapping is used, the association between search result (
ResultSet
) column and JavaBean property is defined for each item one by one in the mapping file. Therefore, mapping with extremely high flexibility and complexity can be achieved.Manual mapping is a method to effectively map the search results (
ResultSet
) column and JavaBean property for the cases given below.
- When data model (JavaBean) that handles the application and physical table layout do not match
- When JavaBean has a nested structure (separate JavaBean is nested)
Also, manual mapping can be mapped efficiently compared with automatic mapping. If prevail efficiency of processing, it is desirable to use manual mapping instead of automatic mapping.
For a hands-on implementation example, refer to
- “MyBatis 3 REFERENCE DOCUMENTATION(Mapper XML Files-Advanced Result Maps-) “
- “How to fetch a related Entity by a single SQL”
- “How to fetch a related Entity using a nested SQL”
projectName-domain/src/main/resources/com/example/domain/repository/todo/TodoRepository.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <!-- (1) --> <resultMap id="todoResultMap" type="Todo"> <!-- (2) --> <id column="todo_id" property="todoId" /> <!-- (3) --> <result column="todo_title" property="todoTitle" /> <result column="finished" property="finished" /> <result column="created_at" property="createdAt" /> <result column="version" property="version" /> </resultMap> <!-- (4) --> <select id="findOne" parameterType="string" resultMap="todoResultMap"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo WHERE todo_id = #{todoId} </select> </mapper>
Sr. No. Description
Define the mapping of search results (
ResultSet
) and JavaBean, in<resultMap>
element.Specify the ID to identify mapping in
id
attribute and the JavaBean class name (or alias) to be mapped, intype
attribute.Refer to “MyBatis 3 REFERENCE DOCUMENTATION(Mapper XML Files-resultMap-) ” for details of
<resultMap>
element, .
Map search results (
ResultSet
) ID (PK) column and JavaBean property.Specify mapping of ID (PK) by using
<id>
element. Specify search result (ResultSet
) column name incolumn
attribute and JavaBean property name inproperty
attribute.Refer to “MyBatis 3 REFERENCE DOCUMENTATION(Mapper XML Files-id & result-) ” for details of
<id>
element.
Map a column other than ID (PK) column of search results (
ResultSet
) and JavaBean property.Specify mapping for column other than ID (PK) using
<result>
element. Specify search result (ResultSet
) column name incolumn
attribute and JavaBean property name inproperty
attribute.Refer to “MyBatis 3 REFERENCE DOCUMENTATION(Mapper XML Files-id & result-) ” for details of
<result>
element.
Specify mapping definition ID to be applied, in resultMap
attribute of<select>
element.Note
How to use id element and result element
<id>
element and<result>
element can both be used for mapping search results (ResultSet
) column and JavaBean property. However, it is recommended to use<id>
element for mapping of ID (PK) column.This is because, when
<id>
element is used for the mapping of ID (PK) column, the performance of mapping process for related objects and the cache control process of objects provided by MyBatis3 can show overall improvement.
6.2.2.6. Search process for Entity¶
How to implement a search process of Entity for different purposes, is explained below.
Read “How to map a JavaBean in Search results” before reading how to implement the search process for Entity.
The explanation below is the example wherein a setting is enabled to automatically map column name separated by an underscore in property name with camel case.
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name="mapUnderscoreToCamelCase" value="true" /> </settings> </configuration>
6.2.2.6.1. Fetching a single key Entity¶
Implementation example wherein, a single Entity is fetched by specifying PK rather than configuring PK in a single column, is given below.
- Define method in Repository interface.
package com.example.domain.repository.todo; import com.example.domain.model.Todo; public interface TodoRepository { // (1) Todo findOne(String todoId); }
Sr. No. Description
In the above example, findOne
method is defined as the method to fetch a single Todo object matching withtodoId
(PK) specified in the argument.
- Define SQL in the mapping file.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <!-- (2) --> <select id="findOne" parameterType="string" resultType="Todo"> /* (3) */ SELECT todo_id, todo_title, finished, created_at, version FROM t_todo /* (4) */ WHERE todo_id = #{todoId} </select> </mapper>
Sr. No. Attribute Description
- Implements SQL in
select
element with search result 0 to 1 record.In the above example, the SQL fetching a record that matches with ID (PK) is implemented.
For details of
select
element, refer to “MyBatis3 REFERENCE DOCUMENTATION (Mapper XML Files-select-)”.id Specifies method name of the method defined in Repository interface. parameterType Specifies parameter fully qualified class name (or alias name). resultType Specifies the fully qualified class name (or alias) of JavaBean that maps the search results (
ResultSet
).When manual mapping is used, specify mapping definition to be applied, by using
resultMap
attribute in place ofresultType
attribute.Refer to “Manual mapping of search results” for manual mapping.
- Specify the column to be fetched.
In the above example, automatic mapping is used as the method to map search results (
ResultSet
) to JavaBean. Refer to “Automatic mapping for search results” for automatic mapping.
- Specify search conditions in WHERE clause.
Specify the value to be bound in search condition as the bind value of
#{variableName}
format. In the above example,#{todoId}
acts as the bind variable.When argument type of Repository interface is of simple type like
String
, any name can be specified as the bind variable name, however when the argument type is JavaBean, JavaBean property name must be specified in the bind variable name.Note
Simple type bind variable name
In case of a simple type like
String
, there is no restriction for the bind variable name, however, it is recommended to use the value same as the argument name of the method.
- Apply DI to Repository in Service class and call the interface method of Repository.
package com.example.domain.service.todo; import javax.inject.Inject; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.example.domain.model.Todo; import com.example.domain.repository.todo.TodoRepository; @Transactional @Service public class TodoServiceImpl implements TodoService { // (5) @Inject TodoRepository todoRepository; @Transactional(readOnly = true) @Override public Todo getTodo(String todoId) { // (6) Todo todo = todoRepository.findOne(todoId); if (todo == null) { // (7) throw new ResourceNotFoundException(ResultMessages.error().add( "e.ex.td.5001", todoId)); } return todo; } }
Sr. No. Description
Apply DI to Repository interface in Service class.
Call Repository interface method and fetch 1 Entity.
Since
null
is returned when the search result shows 0 records, if required, implement the process when Entity cannot be fetched.In the above example, when Entity cannot be fetched, “resource not detected” error is generated.
6.2.2.6.2. Fetching Entity of composite key¶
- Defining the method in Repository interface.
package com.example.domain.repository.order; import org.apache.ibatis.annotations.Param; import com.example.domain.model.OrderHistory; public interface OrderHistoryRepository { // (1) OrderHistory findOne(@Param("orderId") String orderId, @Param("historyId") int historyId); }
Sr. No. Description
Define an argument corresponding to the column that configures PK, in the method.
In the above example,
orderId
andhistoryId
are defined in the argument as PK for the table that manages change history of orders received.Tip
Bind variable name while specifying multiple method arguments
When multiple method arguments of Repository interface are specified, it is recommended to specify
@org.apache.ibatis.annotations.Param
annotation in the argument. “Bind variable name” specified while selecting the value from mapping file is specified in thevalue
attribute of@Param
annotation.As shown in the above example, the value specified in the argument can be bound in SQL by specifying
#{orderId}
and#{historyId}
from mapping file.<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.order.OrderHistoryRepository"> <select id="findOne" resultType="OrderHistory"> SELECT order_id, history_id, order_name, operation_type, created_at" FROM t_order_history WHERE order_id = #{orderId} AND history_id = #{historyId} </select> </mapper>Although it is not mandatory to specify
@Param
annotation, if it is not specified, a mechanical bind variable name needs to be specified as given below. The bind variable name when@Param
annotation is not specified is formed as, ” “param” + declared position of the argument(start from 1)”, and thus can hamper maintainability and readability of the source code.<!-- omitted --> WHERE order_id = #{param1} AND history_id = #{param2} <!-- omitted -->In MyBatis 3.4.1 and subsequent versions,
@Param
annotation can be omitted by using compile option added from JDK 8 (-parameters
).
6.2.2.6.3. Entity search¶
Implementation example is given below wherein a SQL with search results 0 to N records is executed and multiple records of Entity are fetched.
Warning
If the search results data is in a large quantity, using “Implementation of ResultHandler” should be considered.
- Defining the method for fetching multiple records of Entity.
package com.example.domain.repository.todo; import java.util.List; import com.example.domain.model.Todo; public interface TodoRepository { // (1) List<Todo> findAllByCriteria(TodoCriteria criteria); }
Sr. No. Description
In the above example, findAllByCriteria
is defined as the method to fetch multiple records of Todo object in a list format that matches with the JavaBean (TodoCriteria
) retaining the search conditions.Tip
In the above example, the return value of method is specified as
java.util.List
however, search results can also be received asjava.util.Map
.When the results are received in
Map
,
- PK value is stored in
key
ofMap
- Entity object is stored in
value
ofMap
.When search results are received by
Map
,java.util.HashMap
instance is returned. Hence, it should be noted that the alignment sequence ofMap
is not guaranteed.Implementation example is given below.
package com.example.domain.repository.todo; import java.util.Map; import com.example.domain.model.Todo; import org.apache.ibatis.annotations.MapKey; public interface TodoRepository { @MapKey("todoId") Map<String, Todo> findAllByCriteria(TodoCriteria criteria); }When search results are received by
Map
,@org.apache.ibatis.annotations.MapKey
annotation is specified in the method. Property name that is handled askey
ofMap
is specified in thevalue
attribute of annotation. In the above example, PK of Todo object (todoId
) is specified.
- Create JavaBean that retains the search conditions.
package com.example.domain.repository.todo; import java.io.Serializable; import java.util.Date; public class TodoCriteria implements Serializable { private static final long serialVersionUID = 1L; private String title; private Date createdAt; public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public Date getCreatedAt() { return createdAt; } public void setCreatedAt(Date createdAt) { this.createdAt = createdAt; } }Note
Creating a JavaBean for retaining search conditions
Although it is not mandatory to create a JavaBean for retaining search conditions, it is recommended to create one, to clearly identify the role of the stored value. However, implementation can also be performed without creating a JavaBean.
The decision standards of the cases for which JavaBean is created and those for which it is not created should be clearly stated to the programmers by the Architect, so that an overall uniform application can be created.
Implementation example when JavaBean is not created is given below.
package com.example.domain.repository.todo; import java.util.List; import com.example.domain.model.Todo; public interface TodoRepository { List<Todo> findAllByCriteria(@Param("title") String title, @Param("createdAt") Date createdAt); }When JavaBean is not created, the search conditions are declared one by one in an argument and “bind variable name” is specified in
value
attribute of@Param
annotation. Multiple search conditions can be passed to SQL by defining the method described above.
- Define SQL in the mapping file.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <!-- (2) --> <select id="findAllByCriteria" parameterType="TodoCriteria" resultType="Todo"> <![CDATA[ SELECT todo_id, todo_title, finished, created_at, version FROM t_todo WHERE todo_title LIKE #{title} || '%' ESCAPE '~' AND created_at < #{createdAt} /* (3) */ ORDER BY todo_id ]]> </select> </mapper>
Sr. No. Description
Implement the SQL with search results 0 to N records in
select
element.In the above example, Todo records that match with the conditions specified in
todo_title
andcreated_at
are fetched.
Specify sort condition.
When multiple records are to be fetched, sort condition is specified. Particularly, sort condition must be specified in the SQL that fetches the record displayed on the screen.
Tip
How to use CDATA section
When a XML character (“
<
” or “>
” etc.) that needs to be escaped in SQL is specified, the readability of SQL can be maintained by using CDATA section. When CDATA section is not used, entity reference characters such as<
,>
need to be specified, and may lead to reduced SQL readability.In the above example, CDATA section is specified since “
<
” is used as the condition forcreated_at
.
6.2.2.6.4. Fetching Entity records¶
The implementation example of fetching Entity records matching with search conditions is given below.
- Defining the method for fetching Entity records matching with search conditions.
package com.example.domain.repository.todo; public interface TodoRepository { // (1) long countByFinished(boolean finished); }
Sr. No. Description
Specify numeric type (
int
orlong
etc.) for the return value of method used to fetch records.In the above example,
long
is specified.
- Define SQL in the mapping file.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <!-- (2) --> <select id="countByFinished" parameterType="_boolean" resultType="_long"> SELECT COUNT(*) FROM t_todo WHERE finished = #{finished} </select> </mapper>
Sr. No. Description
Execute the SQL that fetches the records.
Type of return value is specified in
resultType
attribute.In the above example, primitive type alias name for specifying
long
is specified.Tip
Primitive type alias name
“
_
” (underscore) should be specified at the beginning of the primitive type alias name. When “_
” (underscore) is not specified, it is handled as primitive wrapper type (java.lang.Long
etc.) alias.
6.2.2.6.5. Pagination search of Entity (MyBatis3 standard method)¶
Implementation example to search an Entity by using MyBatis3 functionality for specifying the fetching scope, is given below.
org.apache.ibatis.session.RowBounds
class is provided in MyBatis as the class to specify the fetch range.
and in SQL, it is not necessary to describe the conditions for fetch range.
Warning
Precautions when large number of data records match the search conditions
Standard MyBatis method is to move the cursor and skip the data which is outside the fetch range of search results (
ResultSet
). Hence, in proportion to the data records that match with search conditions, issues like memory exhaustion or performance degradation of cursor movement are more likely to occur.According to JDBC result set type, the cursor movement processing supports following 2 types. Default behavior is dependent on the default result set type of JDBC driver.
- When result set type is
FORWARD_ONLY
,ResultSet#next()
is repeatedly called and data outside the fetching range is skipped.- When result set type is
SCROLL_SENSITIVE
orSCROLL_INSENSITIVE
,ResultSet#absolute(int)
is called and data outside the scope of fetching range is skipped.Performance degradation can be restricted to a minimum by using
ResultSet#absolute(int)
however, it is dependent on the implementation of JDBC driver. If process same asResultSet#next()
is performed internally, it is not possible to prevent memory exhaustion or performance deterioration.When there is a possibility of large number of data records matching the search conditions, SQL refine method should be adopted instead of pagination search which is a MyBatis3 standard method.
- Defining the method for performing Entity pagination search.
ackage com.example.domain.repository.todo; import java.util.List; import org.apache.ibatis.session.RowBounds; import com.example.domain.model.Todo; public interface TodoRepository { // (1) long countByCriteria(TodoCriteria criteria); // (2) List<Todo> findPageByCriteria(TodoCriteria criteria, RowBounds rowBounds); }
Sr. No. Description
Define the method that fetches total records of Entity matching with search conditions.
Define the method that extracts those Entities that fall in the fetching range from the Entities matching with search conditions.
RowBounds
that retains the information of fetch range (offset and limit) is specified as the argument of defined method.
Define SQL in the mapping file.
Since MyBatis3 performs the process to extract records of corresponding range from the search results, it is not necessary to filter the records within the fetch range using SQL.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <select id="countByCriteria" parameterType="TodoCriteria" resultType="_long"> <![CDATA[ SELECT COUNT(*) FROM t_todo WHERE todo_title LIKE #{title} || '%' ESCAPE '~' AND created_at < #{createdAt} ]]> </select> <select id="findPageByCriteria" parameterType="TodoCriteria" resultType="Todo"> <![CDATA[ SELECT todo_id, todo_title, finished, created_at, version FROM t_todo WHERE todo_title LIKE #{title} || '%' ESCAPE '~' AND created_at < #{createdAt} ORDER BY todo_id ]]> </select> </mapper>Note
Standardization of WHERE clause
When pagination search is performed, it is recommended to standardize the WHERE clause specified in “SQL that fetches total number of records for Entities matching with search condition” and “SQL that fetches the list of the Entities matching with search conditions”, using include function of MyBatis3.
A standardized WHERE clause of above SQL is defined as below. Refer to “Sharing SQL statement” for details.
<sql id="findPageByCriteriaWherePhrase"> <![CDATA[ WHERE todo_title LIKE #{title} || '%' ESCAPE '~' AND created_at < #{createdAt} ]]> </sql> <select id="countByCriteria" parameterType="TodoCriteria" resultType="_long"> SELECT COUNT(*) FROM t_todo <include refid="findPageByCriteriaWherePhrase"/> </select> <select id="findPageByCriteria" parameterType="TodoCriteria" resultType="Todo"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo <include refid="findPageByCriteriaWherePhrase"/> ORDER BY todo_id </select>Note
How to explicitly specify a result set type
Result set type is specified in
resultType
attribute when it is to be specified explicitly. When the default result set type of JDBC driver isFORWARD_ONLY
, it is recommended to specifySCROLL_INSENSITIVE
.<select id="findPageByCriteria" parameterType="TodoCriteria" resultType="Todo" resultSetType="SCROLL_INSENSITIVE"> <!-- omitted --> </select>
- Implementing pagination search process in Service class.
// omitted @Transactional @Service public class TodoServiceImpl implements TodoService { @Inject TodoRepository todoRepository; // omitted @Transactional(readOnly = true) @Override public Page<Todo> searchTodos(TodoCriteria criteria, Pageable pageable) { // (3) long total = todoRepository.countByCriteria(criteria); List<Todo> todos; if (0 < total) { // (4) RowBounds rowBounds = new RowBounds(pageable.getOffset(), pageable.getPageSize()); // (5) todos = todoRepository.findPageByCriteria(criteria, rowBounds); } else { // (6) todos = Collections.emptyList(); } // (7) return new PageImpl<>(todos, pageable, total); } // omitted }
Sr. No. Description
First, fetch the total Entity records matching with search condition.
Generate
RowBounds
object that specifies fetch range of pagination search when Entities matching with search conditions exist.Specify “skip record” in the first argument (
offset
) and “maximum fetch records” in the second argument (limit
) ofRowBounds
. For the values to be specified as argument, it is advisable to specify the values fetched by callinggetOffset
method andgetPageSize
method ofPageable
object provided by Spring Data Commons.Basically, the fetch range is
- Records 1st to 20th when “
0
” is specified in offset and20
is specified in limit- Records 21st to 40th when
20
is specified in offset and20
is specified in limit
Call Repository method and fetch Entities in the fetch range that match with search conditions.
When the Entities that match with search conditions do not exist, set empty list in the search results.
Create and return page information ( org.springframework.data.domain.PageImpl
).
6.2.2.6.6. Pagination search for Entity (SQL refinement method)¶
Implementation example to search an Entity by using range search mechanism provided by database, is given below.
Since SQL refinement method uses range search mechanism provided by database, Entity of fetch range can be fetched efficiently as compared to standard method of MyBatis3.
Note
It is recommended to adopt the SQL refining method when a large volume of data matching with search condition exists.
- Defining the method for performing Entity pagination search.
package com.example.domain.repository.todo; import java.util.List; import org.apache.ibatis.annotations.Param; import org.springframework.data.domain.Pageable; import com.example.domain.model.Todo; public interface TodoRepository { // (1) long countByCriteria( @Param("criteria") TodoCriteria criteria); // (2) List<Todo> findPageByCriteria( @Param("criteria") TodoCriteria criteria, @Param("pageable") Pageable pageable); }
Sr. No. Description
Define a method that fetches total Entity records matching with search conditions.
Define a method to extract entities that can be fetched from the Entities matching with search conditions.
org.springframework.data.domain.Pageable
that retains the information within the fetch range (offset and limit) is specified as an argument for defined method.Note
Reason why the argument specifies @Param annotation for a single method
In the above example, the argument specifies
@Param
annotation for a single method (countByCriteria
). This is to standardize WHERE clause and the SQL executed whenfindPageByCriteria
method is called.By specifying bind variable name in the argument using
@Param
annotation, nested structure of bind variable name specified in SQL is combined.A typical SQL implementation example is given below.
Define SQL in the mapping file.
Fetch range records are refined by SQL.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <sql id="findPageByCriteriaWherePhrase"> <![CDATA[ /* (3) */ WHERE todo_title LIKE #{criteria.title} || '%' ESCAPE '~' AND created_at < #{criteria.createdAt} ]]> </sql> <select id="countByCriteria" resultType="_long"> SELECT COUNT(*) FROM t_todo <include refid="findPageByCriteriaWherePhrase" /> </select> <select id="findPageByCriteria" resultType="Todo"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo <include refid="findPageByCriteriaWherePhrase" /> ORDER BY todo_id LIMIT #{pageable.pageSize} /* (4) */ OFFSET #{pageable.offset} /* (4) */ </select> </mapper>
Sr. No. Description
@Param("criteria")
is specified in the arguments ofcountByCriteria
andfindPageByCriteria
methods, Hence, the bind variable name specified in SQL is incriteria.field name
format.
Extract only necessary records by using the fetch range mechanism provided by database.
“Skip record” is stored in
offset
ofPageable
object whereas “maximum fetch records” is stored inpageSize
.Above example is the implementation example with H2 Database.
- Implement a pagination search process in the Service class.
// omitted @Transactional @Service public class TodoServiceImpl implements TodoService { @Inject TodoRepository todoRepository; // omitted @Transactional(readOnly = true) @Override public Page<Todo> searchTodos(TodoCriteria criteria, Pageable pageable) { long total = todoRepository.countByCriteria(criteria); List<Todo> todos; if (0 < total) { // (5) todos = todoRepository.findPageByCriteria(criteria, pageable); } else { todos = Collections.emptyList(); } return new PageImpl<>(todos, pageable, total); } // omitted }
Sr. No. Description
Call Repository method and fetch Entity within the fetch range matching with search condition.
Pageable
object received by the argument can be passed as it is when calling Repository method.
6.2.2.7. Entity registration process¶
how to register an Entity for different purposes is explained with implementation example.
6.2.2.7.1. Registering a single Entity record¶
Implementation example for registering a single Entity record is given below.
- Defining the method in Repository interface.
package com.example.domain.repository.todo; import com.example.domain.model.Todo; public interface TodoRepository { // (1) void create(Todo todo); }
Sr. No. Description
In the above example, create
method is defined as the method for registering a single Todo object specified in the argument.
Note
Return value of the method that registers Entity
Return value for the method that registers Entity can be
void
.However, when SQL that inserts selected results is executed,
boolean
or numeric value type (int
orlong
) should be set as the return value based on application requirements.
- When
boolean
is specified as return value,false
is returned when 0 records are registered andtrue
is returned when 1 or more records are registered.- When numeric value type is specified as return value, number of registered records is returned.
- Define SQL in the mapping file.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <!-- (2) --> <insert id="create" parameterType="Todo"> INSERT INTO t_todo ( todo_id, todo_title, finished, created_at, version ) /* (3) */ VALUES ( #{todoId}, #{todoTitle}, #{finished}, #{createdAt}, #{version} ) </insert> </mapper>
Sr. No. Description
Implement the INSERT SQL in the insert element.
Specify name of the method defined in Repository interface, in
id
attribute.For details of
insert
element, refer to “MyBatis3 REFERENCE DOCUMENTATION (Mapper XML Files-insert, update and delete-)”.
Specify configuration value at the time of record registration in VALUE clause.
The value to be bound in VALUE clause is specified as the bind variable of #{variableName} format. In the above example, since JavaBean (
Todo`
) is specified as an argument of Repository interface, JavaBean property name is specified in the bind variable name.
- Apply DI to Repository in Service class and call Repository interface method.
package com.example.domain.service.todo; import java.util.UUID; import javax.inject.Inject; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.terasoluna.gfw.common.date.jodatime.JodaTimeDateFactory; import com.example.domain.model.Todo; import com.example.domain.repository.todo.TodoRepository; @Transactional @Service public class TodoServiceImpl implements TodoService { // (4) @Inject TodoRepository todoRepository; @Inject JodaTimeDateFactory dateFactory; @Override public Todo create(Todo todo) { // (5) todo.setTodoId(UUID.randomUUID().toString()); todo.setCreatedAt(dateFactory.newDate()); todo.setFinished(false); todo.setVersion(1); // (6) todoRepository.create(todo); // (7) return todo; } }
Sr. No. Description
Apply DI to Repository interface in Service class.
Set the value for Entity object passed in the argument based on the application requirements.
In the above example,
- “UUID” as an ID
- “System date and time” as registration date and time”
- “
false
: Incomplete” in the completion flag- “
1
” in the versionare set.
Call Repository interface method and register the single Entity record.
Return registered Entity.
When registration value is set in the Service class process, it is recommended to return the registered Entity object as return value.
6.2.2.7.2. Generating key¶
An implementation example is given in “Registering a single Entity record” for generating key (ID) in Service class.
However, MyBatis3 provides a mechanism for generating key in mapping file.
Note
Cases wherein key generation functionality of MyBatis3 is used
When a database function (function or ID column etc.) is used for generating key, it is recommended to use the mechanism of MyBatis3 key generation functionality.
There are two kinds of methods for generating key.
- A method wherein the result obtained by calling the function etc. provided by database, is handled as the key
- A method wherein the result obtained by calling ID column provided by database (IDENTITY type, AUTO_INCREMENT type etc.) +
Statement#getGeneratedKeys()
added by JDBC3.0 is handled as the key.
Method wherein result obtained by calling the function etc. provided by database is handled as a key, is explained first. In the example given below, H2 Database is used as the database.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <insert id="create" parameterType="Todo"> <!-- (1) --> <selectKey keyProperty="todoId" resultType="string" order="BEFORE"> /* (2) */ SELECT RANDOM_UUID() </selectKey> INSERT INTO t_todo ( todo_id, todo_title, finished, created_at, version ) VALUES ( #{todoId}, #{todoTitle}, #{finished}, #{createdAt}, #{version} ) </insert> </mapper>
Sr. No. Attribute Description
- Implements the SQL to generate key in
selectKey
element.In the above example, UUID is fetched by using the function provided by database.
For details of
selectKey
, refer to “MyBatis3 REFERENCE DOCUMENTATION (Mapper XML Files-insert, update and delete-)”.keyProperty Specifies the property name of Entity that stores fetched key value.
In the above example, key that is generated in
todoId
property of Entity, is set.resultType Specifies the type of key value to be fetched by executing the SQL. order Specifies the timing when the SQL for key generation (
BEFORE
orAFTER
) is executed.
- When
BEFORE
is specified, INSERT statement is executed after the results obtained by executing SQL specified inselectKey
element are reflected in Entity.- When
AFTER
is specified, SQL specified inselectKey
element is executed after executing INSERT statement and fetched value is reflected in Entity.
- Implement the SQL for generating key.
In the above example, the function that generates UUID of H2 Database is called and key is generated. Implementation wherein value fetched from sequence object is formatted in a string can be cited as the typical example of key generation.
Next, the method wherein result obtained by calling ID column provided by database + Statement#getGeneratedKeys()
added by JDBC3.0ratedKeys() is handled as a key, is explained.
In the example given below, H2 Database is used as the database.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.audit.AuditLogRepository"> <!-- (3) --> <insert id="create" parameterType="Todo" useGeneratedKeys="true" keyProperty="logId"> INSERT INTO t_audit_log ( level, message, created_at, ) VALUES ( #{level}, #{message}, #{createdAt}, ) </insert> </mapper>
Sr. No. Attribute Description
useGeneratedKeys When
true
is specified, function that fetches key by calling ID column +Statement#getGeneratedKeys()
can be used.For details of
useGeneratedKeys
, refer to “MyBatis3 REFERENCE DOCUMENTATION (Mapper XML Files-insert, update and delete-)”.keyProperty Specify property name of the Entity that stores the key value which is automatically incremented in the database.
In the above example, key value fetched by
Statement#getGeneratedKeys()
inlogId
property of Entity, is set after executing INSERT statement.
6.2.2.7.3. Batch registration of Entity¶
Implementation example for registering Entity in a batch is shown below.
The methods to perform batch registration of Entity are as given below.
- Execute INSERT statement that registers multiple records at the same time.
- There is a method to use the JDBC batch update functionality
Refer to “Using batch mode” for details on how to use the JDBC batch update functionality.
How to execute the INSERT statement that registers multiple records at the same time, is explained below. In the example below, H2 Database is used as the database.
- Defining the method in Repository interface.
package com.example.domain.repository.todo; import java.util.List; import com.example.domain.model.Todo; public interface TodoRepository { // (1) void createAll(List<Todo> todos); }
Sr. No. Description
In the above example, createAll
method is defined as the method to perform batch registration for a list of Todo objects specified in the argument.
- Define the SQL in mapping file.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <insert id="createAll" parameterType="list"> INSERT INTO t_todo ( todo_id, todo_title, finished, created_at, version ) /* (2) */ VALUES /* (3) */ <foreach collection="list" item="todo" separator=","> ( #{todo.todoId}, #{todo.todoTitle}, #{todo.finished}, #{todo.createdAt}, #{todo.version} ) </foreach> </insert> </mapper>
Sr. No. Attribute Description
- Specifies the configuration value at the time of registering records in VALUE clause.
- Repeats the process for the list of Todo objects passed as argument, by using
foreach
element.For details of
foreach
details, refer to “MyBatis3 REFERENCE DOCUMENTATION (Dynamic SQL-foreach-)”.collection Specifies the collection for processing.
In the example given above, the process is repeated for the list of Repository method arguments. When
@Param
is not specified in the argument of Repository method,list
is specified. When@Param
is specified, the value specified invalue
attribute of@Param
is specified.item Specifies the local variable name that retains one element from the list.
JavaBean property can be accessed from the SQL in
foreach
element, in #{Local variable name.Property name} format.separator Specifies the string to separate elements in the list.
In the above example, by specifying “
,
” , the VALUE clause for each element is separated with “,
”.
Note
** Precautions when using SQL that registers multiple records at the same time**
When SQL that registers multiple records concurrently is executed, “Generating key” described earlier cannot be used.
- Following SQL is generated and executed.
INSERT INTO t_todo ( todo_id, todo_title, finished, created_at, version ) VALUES ( '99243507-1b02-45b6-bfb6-d9b89f044e2d', 'todo title 1', false, '09/17/2014 23:59:59.999', 1 ) , ( '66b096f1-791f-412f-9a0a-ee4a3a9186c2', 'todo title 2', 0, '09/17/2014 23:59:59.999', 1 )Tip
The support status and syntax for the SQL that performs batch registration differ depending on database and version. The links for reference pages of major databases are given below.
6.2.2.8. Update process of Entity¶
Entity update method for different purposes is explained with implementation example.
6.2.2.8.1. Updating a single Entity¶
Implementation example for updating a single Entity is given below.
Note
Hereafter, an implementation example is explained wherein optimistic locking is performed by using version column. However, process related to optimistic locking need not be performed when optimistic locking is not required.
Refer to “Exclusive Control for details of exclusive control.
- Defining the method in Repository interface.
package com.example.domain.repository.todo; import com.example.domain.model.Todo; public interface TodoRepository { // (1) boolean update(Todo todo); }
Sr. No. Description
In the above example, update
method is defined as the method to update single Todo object specified in the argument.
Note
Return value of the method that updates a single Entity
The return value of the method that updates single Entity can be
boolean
.However, when multiple records are obtained as update result and it is necessary to handle it as data mismatch error, numeric value type (
int
orlong
) needs to be specified as return value and it needs to be checked that a single update record exists. When main key is used as the update condition, return value can be set asboolean
since multiple records are not obtained as update result.
- When
boolean
is specified as return value,false
is returned when update records are 0 andtrue
is returned when update records are 1 or more.- When numeric value is specified as return value, number of update records is returned.
- Defining SQL in mapping file.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <!-- (2) --> <update id="update" parameterType="Todo"> UPDATE t_todo SET todo_title = #{todoTitle}, finished = #{finished}, version = version + 1 WHERE todo_id = #{todoId} AND version = #{version} </update> </mapper>
Sr. No. Description
Implement the UPDATE SQL in
update
element.Specify the method name defined in Repository interface, in
id
attribute.For details of
update
element, refer to “MyBatis3 REFERENCE DOCUMENTATION (Mapper XML Files-insert, update and delete-)”.The value to be bound in SET clause and WHERE clause is specified as the bind variable with #{variableName} format. In the above example, since a JavaBean (
Todo
) is specified as argument of Repository interface, JavaBean property name is specified in the bind variable name.
- Apply DI to Repository in Service class and call Repository interface method.
package com.example.domain.service.todo; import javax.inject.Inject; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.example.domain.model.Todo; import com.example.domain.repository.todo.TodoRepository; @Transactional @Service public class TodoServiceImpl implements TodoService { // (3) @Inject TodoRepository todoRepository; @Override public Todo update(Todo todo) { // (4) Todo currentTodo = todoRepository.findOne(todo.getTodoId()); if (currentTodo == null || currentTodo.getVersion() != todo.getVersion()) { throw new ObjectOptimisticLockingFailureException(Todo.class, todo .getTodoId()); } // (5) currentTodo.setTodoTitle(todo.getTodoTitle()); currentTodo.setFinished(todo.isFinished()); // (6) boolean updated = todoRepository.update(currentTodo); // (7) if (!updated) { throw new ObjectOptimisticLockingFailureException(Todo.class, currentTodo.getTodoId()); } currentTodo.setVersion(todo.getVersion() + 1); return currentTodo; } }
Sr. No. Description
Apply DI to Repository interface in Service class.
Fetch the Entity to be updated from database.
In the above example, when Entity is updated (records are deleted or version is updated), optimistic locking exception (
org.springframework.orm.ObjectOptimisticLockingFailureException
) provided by Spring Framework is generated.
Reflect update details for the Entity to be updated.
In the above example, “Title” and “Complete flag” are reflected. When there are few update items, the process can be performed as per the implementation example given above. However, when update items are more in number, it is recommended to use “Bean Mapping (Dozer)”.
Call the Repository interface method and update single Entity record.
Determine update results of Entity.
In the above example, when Entity is not updated (records are deleted or version is updated), optimistic locking exception (
org.springframework.orm.ObjectOptimisticLockingFailureException
) provided by Spring Framework is generated.Tip
In the above example, when update process is successful,
currentTodo.setVersion(todo.getVersion() + 1);is obtained.
It is a process to combine the version updated in database and the version that stores an Entity.
If database status and Entity status are not matched when referring a version in the call source (Controller or JSP etc.) process, data mismatch may occur and application is not executed as anticipated.
6.2.2.8.2. Batch update of Entity¶
Implementation example wherein Entity is updated in batch is given below.
The methods to update Entity in batch are as below.
- Execute UPDATE statement that updates multiple records simultaneously
- Use JDBC batch update functionality
Refer to “Using batch mode” for the details on how to use the JDBC batch update functionality.
How to execute UPDATE statement that concurrently updates multiple records is explained here.
- Defining the method in Repository interface.
package com.example.domain.repository.todo; import com.example.domain.model.Todo; import org.apache.ibatis.annotations.Param; import java.util.List; public interface TodoRepository { // (1) int updateFinishedByTodIds(@Param("finished") boolean finished, @Param("todoIds") List<String> todoIds); }
Sr. No. Description
In the above example, updateFinishedByTodIds
method is defined as the method to updatefinished
column of the records corresponding to list of IDs specified in the argument.Note
Return value for the method that updates Entity in batch
Return value of the method that updates Entity in batch should preferably be of numeric type (
int
orlong
). When it is set as numeric type return value, number of updated records can be fetched.
- Defining the SQL in mapping file.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <update id="updateFinishedByTodIds"> UPDATE t_todo SET finished = #{finished}, /* (2) */ version = version + 1 WHERE /* (3) */ <foreach item="todoId" collection="todoIds" open="todo_id IN (" separator="," close=")"> #{todoId} </foreach> </update> </mapper>
Sr. No. Attribute Description
- Updates version column when an optimistic locking is applied using a version column.
If the version is not updated, optimistic locking control does not operate properly. Refer to “Exclusive Control” for details of exclusive control.
- Specifies the update conditions for updating multiple records in WHERE clause. - Repeats process for the list of IDs passed by argument, using
foreach
element.In the above example, IN clause is generated from the list of IDs passed by argument.
Refer “MyBatis3 REFERENCE DOCUMENTATION (Dynamic SQL-foreach-)” for details of
foreach
.collection Specify collection for a process.
In the above example, the process is repeated for a list of IDs (
todoIds
) of Repository method arguments.item Specify local variable name that retains 1 element in the list. separator Specify the string for separating elements in the list.
In the above example, “
,
”, which is the separator character of IN clause, is specified.
6.2.2.9. Delete process for Entity¶
6.2.2.9.1. Deleting a single Entity¶
Implementation example for deleting a single Entity is given below.
Note
Explanation hereafter shows an implementation example wherein an optimistic locking is performed by using version column. However, it is not necessary to perform the processes related to optimistic locking when optimistic locking is not required.
Refer to “Exclusive Control” for details of exclusive control.
- Defining method in Repository interface.
package com.example.domain.repository.todo; import com.example.domain.model.Todo; public interface TodoRepository { // (1) boolean delete(Todo todo); }
Sr. No. Description
In the above example, delete
method is defined as the method to delete single Todo object specified in the argument.Note
** Return value for the method that deletes a single Entity**
The return value of the method that deletes a single Entity can be
boolean
.However, when the return value is to be handled as a data mismatch error due to multiple deletion results, it is necessary to set numeric value type (
int
orlong
) as the return value and to check whether a single deletion record exists. When main key is used as the delete condition, return value can be set toboolean
since, multiple deleted records are not obtained.
- When
boolean
is specified as return value,false
is returned when deleted records are 0 andtrue
is returned when deleted records are 1 or more.- When numeric value type is specified as a return value, the number of deleted records is returned.
- Defining a SQL in mapping file.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <!-- (2) --> <delete id="delete" parameterType="Todo"> DELETE FROM t_todo WHERE todo_id = #{todoId} AND version = #{version} </delete> </mapper>
Sr. No. Description
Implement DELETE SQL in
delete
element.Specify method name for the method defined in Repository interface, in
id
attribute.Refer to “MyBatis3 REFERENCE DOCUMENTATION (Mapper XML Files-insert, update and delete-)” for details of
delete
element.The value to be bound in WHERE clause is specified as a bind variable of #{variableName} format. In the above example, JavaBean property name is specified in the bind variable name since a JavaBean (
Todo
) is specified as argument of Repository interface.
- Apply DI to the Repository in Service class and call the method for Repository interface.
package com.example.domain.service.todo; import javax.inject.Inject; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.example.domain.model.Todo; import com.example.domain.repository.todo.TodoRepository; @Transactional @Service public class TodoServiceImpl implements TodoService { // (3) @Inject TodoRepository todoRepository; @Override public Todo delete(String todoId, long version) { // (4) Todo currentTodo = todoRepository.findOne(todoId); if (currentTodo == null || currentTodo.getVersion() != version) { throw new ObjectOptimisticLockingFailureException(Todo.class, todoId); } // (5) boolean deleted = todoRepository.delete(currentTodo); // (6) if (!deleted) { throw new ObjectOptimisticLockingFailureException(Todo.class, currentTodo.getTodoId()); } return currentTodo; } }
Sr. No. Description
Apply DI to Repository interface in Service class.
Fetch the Entity to be deleted from database.
In the above example, when Entity is updated (records are deleted or version is updated), optimistic locking exception (
org.springframework.orm.ObjectOptimisticLockingFailureException
) provided by Spring Framework is generated.
Call Repository interface method and delete a single Entity.
Determine the deletion result of Entity.
In the above example, when Entity is not deleted (records are deleted or version is updated) optimistic locking exception (
org.springframework.orm.ObjectOptimisticLockingFailureException
) provided by Spring Framework is generated.
6.2.2.9.2. Batch deletion of Entity¶
Implementation example wherein Entity is deleted in batch is given below.
The methods to delete Entity in batch are as given below.
- Execute a DELETE statement that concurrently deletes multiple records
- Use JDBC batch update functionality
Refer to “Using batch mode” for how to use JDBC batch update functionality.
The method to execute DELETE statement that concurrently deletes multiple records, is explained below.
- Defining method in Repository interface.
package com.example.domain.repository.todo; public interface TodoRepository { // (1) int deleteOlderFinishedTodo(Date criteriaDate); }
Sr. No. Description
In the above example, deleteOlderFinishedTodo
method is defined as the method to delete finished records that were created prior to the standard date.Note
Return value for the method that deletes Entity in batch
Return value for the method that deletes Entity in batch can be numeric value type (
int
orlong
). When the return value is of numeric type, number of deleted records can be fetched.
- Defining SQL in mapping file.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <delete id="deleteOlderFinishedTodo" parameterType="date"> <![CDATA[ DELETE FROM t_todo /* (2) */ WHERE finished = TRUE AND created_at < #{criteriaDate} ]]> </delete> </mapper>
Sr. No. Description
Specify delete conditions for updating multiple records in WHERE clause.
In the above example,
- Finished (
finished
isTRUE
)- Created before standard date (
created_at
before standard date)are specified as delete conditions.
6.2.2.10. Implementing dynamic SQL¶
An implementation example wherein dynamic SQL is built, is given below.
MyBatis3 provides a mechanism through which dynamic SQL is built by using OGNL base expression (Expression language) and XML elements for building dynamic SQL.
Refer to “MyBatis3 REFERENCE DOCUMENTATION (Dynamic SQL) <http://mybatis.github.io/mybatis-3/dynamic-sql.html>`_” for details of dynamic SQL.
MyBatis3 provides following XML elements for building a dynamic SQL.
Sr. No. Element name Description
if
Element that builds SQL only when it matches with the condition.
choose
Element that builds SQL by selecting one of the options from multiple options, that matches with the condition.
where
Element that assigns or removes prefix and suffix for the built WHERE clause.
set
Element that assigns or removes prefix or suffix for the built SET clause.
foreach
Element that repeats a process for a collection or an array
bind
Element that stores the results of OGNL expression in the variable.
Variable stored by using
bind
variable can be referred in SQL.Tip
Although it is not given in the list,
trim
element is provided as the XML element for building dynamic SQL.
trim
element is a more generalized XML element as compared towhere
element andset
element.In most of the cases,
where
element andset
element can meet the requirements. Hence, description oftrim
element is omitted in this guideline. Refer to “MyBatis3 REFERENCE DOCUMENTATION (Dynamic SQL-trim, where, set-)” when it is necessary to usetrim
element.
6.2.2.10.1. Implementation of if element¶
if
element is the XML element that builds SQL only when it matches with specified conditions.
<select id="findAllByCriteria" parameterType="TodoCriteria" resultType="Todo"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo WHERE todo_title LIKE #{todoTitle} || '%' ESCAPE '~' <!-- (1) --> <if test="finished != null"> AND finished = #{finished} </if> ORDER BY todo_id </select>
Sr. No. Description
Specify the condition in
test
attribute ofif
element.In the above example, when
finished
is specified as the search condition, conditions forfinished
column are added to SQL.
SQL (WHERE clause) generated by dynamic SQL described above consists of 2 patterns.
-- (1) finished == null ... WHERE todo_title LIKE ? || '%' ESCAPE '~' ORDER BY todo_id-- (2) finished != null ... WHERE todo_title LIKE ? || '%' ESCAPE '~' AND finished = ? ORDER BY todo_id
6.2.2.10.2. Implementation of choose element¶
choose
element is the XML element for building SQL by selecting one option that matches the condition from a set of conditions.
<select id="findAllByCriteria" parameterType="TodoCriteria" resultType="Todo"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo WHERE todo_title LIKE #{todoTitle} || '%' ESCAPE '~' <!-- (1) --> <choose> <!-- (2) --> <when test="createdAt != null"> AND created_at <![CDATA[ > ]]> #{createdAt} </when> <!-- (3) --> <otherwise> AND created_at <![CDATA[ > ]]> CURRENT_DATE </otherwise> </choose> ORDER BY todo_id </select>
Sr. No. Description
Specify the condition to build SQL by specifying when
element andotherwise
element inchoose
element.
Specify the condition in
test
attribute ofwhen
element.In the above example, when
createdAt
is specified as a search condition, a condition wherein, values ofcreate_at
column extract the records after the specified date, is added to SQL.
Specify all the SQLs in ``otherwise`` element that are built when the conditions do not match with
when
element .In the above example, the condition wherein
create_at
column values extract the records after the current date (records that are created on that day) is added to SQL.
SQL (WHERE clause) that is generated by dynamic SQL described above consists of 2 patterns.
-- (1) createdAt!=null ... WHERE todo_title LIKE ? || '%' ESCAPE '~' AND created_at > ? ORDER BY todo_id-- (2) createdAt==null ... WHERE todo_title LIKE ? || '%' ESCAPE '~' AND created_at > CURRENT_DATE ORDER BY todo_id
6.2.2.10.3. Implementation of where element¶
where
element is the XML element for dynamically generating WHERE clause.
When where
element is used,
- Assigning WHERE clause
- Removal of AND clause and OR clause
are performed. Hence, WHERE clause can be built easily.
<select id="findAllByCriteria2" parameterType="TodoCriteria" resultType="Todo"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo <!-- (1) --> <where> <!-- (2) --> <if test="finished != null"> AND finished = #{finished} </if> <!-- (3) --> <if test="createdAt != null"> AND created_at <![CDATA[ > ]]> #{createdAt} </if> </where> ORDER BY todo_id </select>
Sr. No. Description
Implement the dynamic SQL for building WHERE clause in
where
element.According to the SQL built in
where
element, processes such as assigning WHERE clause, removing AND clause and OR clause etc. can be performed.
Build dynamic SQL.
In the above example, when
finished
is specified as a search condition, the condition forfinished
column is added to SQL.
Build dynamic SQL.
In the above example, when
createdAt
is specified as a search condition, the condition forcreated_at
column is added to SQL.
The SQL (WHERE clause) that is generated by dynamic SQL described above consists of 4 patterns as given below.
-- (1) finished != null && createdAt != null ... FROM t_todo WHERE finished = ? AND created_at > ? ORDER BY todo_id-- (2) finished != null && createdAt == null ... FROM t_todo WHERE finished = ? ORDER BY todo_id-- (3) finished == null && createdAt != null ... FROM t_todo WHERE created_at > ? ORDER BY todo_id-- (4) finished == null && createdAt == null ... FROM t_todo ORDER BY todo_id
6.2.2.10.4. Implementation example for set element¶
set
element is the XML element for automatically generating SET clause.
When set
element is used,
- Assigning SET clause
- Removal of comma at the end
are performed. Hence SET clause can be easily built.
<update id="update" parameterType="Todo"> UPDATE t_todo <!-- (1) --> <set> version = version + 1, <!-- (2) --> <if test="todoTitle != null"> todo_title = #{todoTitle} </if> </set> WHERE todo_id = #{todoId} </update>
Sr. No. Description
Implement the dynamic SQL to build SET clause in
set
element.Assigning SET clause and removal of comma at the end is performed according to the SQL that is built in
set
element.
Build a dynamic SQL.
In the above example, when
todoTitle
is specified as an update item,todo_title
column is added to SQL as an update column.
SQL generated by dynamic SQL described above consists of 2 patterns as below.
-- (1) todoTitle != null UPDATE t_todo SET version = version + 1, todo_title = ? WHERE todo_id = ?-- (2) todoTitle == null UPDATE t_todo SET version = version + 1 WHERE todo_id = ?
6.2.2.10.5. Implementation example of foreach element¶
foreach
element is the XML element for repeating a process for a collection or an array.
<select id="findAllByCreatedAtList" parameterType="list" resultType="Todo"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo <where> <!-- (1) --> <if test="list != null"> <!-- (2) --> <foreach collection="list" item="date" separator="OR"> <![CDATA[ (created_at >= #{date} AND created_at < DATEADD('DAY', 1, #{date})) ]]> </foreach> </if> </where> ORDER BY todo_id </select>
Sr. No. Attribute Description
- Performs
null
check for the collection or array for which process is repeated.Null check is not necessary when
null
value is not obtained.
- Repeat the process for the collection or array and build the dynamic SQL, by using
foreach
element.In the above example, WHERE clause is built for searching the record wherein date of record creation matches with any of the specified dates (date list).
collection Specify the collection or array for which a process is repeated, in
collection
attribute.In the above example, collection specified in the Repository method argument is specified.
item Specify the local variable name that retains one element in the list, in
item
attribute.In the above example, since the date list is specified in
collection
attribute, variable name calleddate
is specified.separator Specify the separator string between elements in
separator
attribute.In the above example, WHERE clause of OR condition is built.
Tip
foreach
element consists of following attributes although they are not used in the above example.
Sr. No. Attribute Description
open Specify the string that is set before processing the elements that are at the beginning of the collection.
close Specify the string that is set after processing the elements at the end of the collection.
index Specify the variable name that stores the loop number. There are only a few cases that use
index
attribute, howeveropen
attribute andclose
attribute are used to generate IN clause etc. dynamically.How to use
foreach
element while creating an IN clause is explained.<foreach collection="list" item="statusCode" open="AND order_status IN (" separator="," close=")"> #{statusCode} </foreach>Following SQL is built.
-- list=['accepted','checking'] ... AND order_status IN (?,?)
-- (1) list=null or statusCodes=[] ... FROM t_todo ORDER BY todo_id-- (2) list=['2014-01-01'] ... FROM t_todo WHERE (created_at >= ? AND created_at < DATEADD('DAY', 1, ?)) ORDER BY todo_id-- (3) list=['2014-01-01','2014-01-02'] ... FROM t_todo WHERE (created_at >= ? AND created_at < DATEADD('DAY', 1, ?)) OR (created_at >= ? AND created_at < DATEADD('DAY', 1, ?)) ORDER BY todo_id
6.2.2.10.6. Implementation example for bind element¶
bind
element is the XML element for storing OGNL expression result in the variable.
<select id="findAllByCriteria" parameterType="TodoCriteria" resultType="Todo"> <!-- (1) --> <bind name="escapedTodoTitle" value="@org.terasoluna.gfw.common.query.QueryEscapeUtils@toLikeCondition(todoTitle)" /> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo WHERE /* (2) */ todo_title LIKE #{escapedTodoTitle} || '%' ESCAPE '~' ORDER BY todo_id </select>
Sr. No. Attribute Description
- Store results of OGNL expression in the variable, using
bind
element.In the above example, the results obtained by calling the method using OGNL expression, are stored in the variable.
name Specify variable name in
name
attribute.The variable specified here can be used as SQL bind variable.
value Specify OGNL expression in
value
attribute.Results obtained by executing OGNL expression are stored in the variable specified by
name
attribute.In the above example, the results obtained by calling method (
QueryEscapeUtils#toLikeCondition(String)
) provided by common library are stored in the variableescapedTodoTitle
.
- Specify the variable created by using
bind
element as the bind variable.In the above example, the variable created by using
bind
element (escapedTodoTitle
) is specified as the bind variable.Tip
In the above example, although the variable created by using
bind
variable is specified as the bind variable, it can also be used as substitution variable.Refer to “SQL Injection countermeasures” for bind variable and substitution variable.
6.2.2.11. Escape during LIKE search¶
When performing LIKE search, the value to be used as search condition should be escaped for LIKE search.
The escape process for LIKE search
can be implemented by using the method of org.terasoluna.gfw.common.query.QueryEscapeUtils
class provided by common library.
Refer to “Escaping during LIKE search” for specifications of the escape process provided by common library.
<select id="findAllByCriteria" parameterType="TodoCriteria" resultType="Todo"> <!-- (1) --> <bind name="todoTitleContainingCondition" value="@org.terasoluna.gfw.common.query.QueryEscapeUtils@toContainingCondition(todoTitle)" /> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo WHERE /* (2) (3) */ todo_title LIKE #{todoTitleContainingCondition} ESCAPE '~' ORDER BY todo_id </select>
Sr. No. Description
Call the Escape process method for LIKE search provided by common library, by using
bind
element (OGNL expression).In the above example, escape process is performed for partial match and is stored in
todoTitleContainingCondition
variable.QueryEscapeUtils@toContainingCondition(String)
is the method that assigns “%
” before and after the escaped string.
Specify the string that performs escape process for partial match, as bind variable of LIKE clause.
Specify escape character in ESCAPE clause.
Since
"~"
is used as an escape character in the Escape process provided by common library, “~
” is specified in ESCAPE clause.Tip
In the above example, a method that performs the Escape process for partial match is called. However, methods that perform the following processes are also provided.
- Escape for starting-with match (
QueryEscapeUtils@toStartingWithCondition(String)
)- Escape for ends-with match (
QueryEscapeUtils@toEndingWithCondition(String)
)- Escape only (
QueryEscapeUtils@toLikeCondition(String)
)Refer to “Escaping during LIKE search” for details.
Note
In the above example, the method that performs Escape process in the mapping file is called, however Escape process can also be called as a Service process before calling the Repository method.
As role of component, it is appropriate that Escape process is performed in mapping file. Hence, in this guideline, it is recommended to perform Escape process in mapping file.
6.2.2.12. SQL Injection countermeasures¶
It is important to take precautions when building SQL to avoid occurrence of SQL Injection.
MyBatis3 provides following two methods as the mechanism to embed values in SQL.
Sr. No. Method Description
Embedding value by using bind variable When this method is used, the value is embedded after building SQL by using
java.sql.PreparedStatement
. Hence, the value can be safely embedded.When the value entered by user is to be embedded in SQL, as a rule, bind variable should be used.
Embedding value by using substitution variable When this method is used, the value is substituted as a string while building SQL. Hence Safe embedding of value cannot be guaranteed. Warning
When the value entered by the user is embedded by using substitution variable, it should be noted that the risk of SQL Injection is high.
When the value entered by the user needs to be embedded by using a substitution variable, the input check must be performed in order to ensure that SQL Injection has not occurred.
Basically, it is strongly recommended not to use the value entered by the user as it is.
6.2.2.12.1. How to embed using a bind variable¶
How to use a bind variable is shown below.
<insert id="create" parameterType="Todo"> INSERT INTO t_todo ( todo_id, todo_title, finished, created_at, version ) VALUES ( /* (1) */ #{todoId}, #{todoTitle}, #{finished}, #{createdAt}, #{version} ) </insert>
Sr. No. Description
Enclose the property name of the property that stores bind value using #{
and “}
” and specify it as bind variable.Tip
A number of attributes can be specified in the bind variable.
The attributes that can be specified are as below.
- javaType
- jdbcType
- typeHandler
- numericScale
- mode
- resultMap
- jdbcTypeName
Basically, MyBatis simply selects an appropriate behavior just by specifying the property name. The attributes described above can be specified when MyBatis does not select an appropriate behavior.
Refer to “MyBatis3 REFERENCE DOCUMENTATION(Mapper XML Files-Parameters-) ” for how to use attributes.
6.2.2.12.2. How to embed using a substitution variable¶
How to use a substitution variable is described below.
- Defining the method in Repository interface.
public interface TodoRepository { List<Todo> findAllByCriteria(@Param("criteria") TodoCriteria criteria, @Param("direction") String direction); }
- Implementing SQL in mapping file.
<select id="findAllByCriteria" parameterType="TodoCriteria" resultType="Todo"> <bind name="todoTitleContainingCondition" value="@org.terasoluna.gfw.common.query.QueryEscapeUtils@toContainingCondition(criteria.todoTitle)" /> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo WHERE todo_title LIKE #{todoTitleContainingCondition} ESCAPE '~' ORDER BY /* (1) */ todo_id ${direction} </select>
Sr. No. Description
Enclose the property name of the property that stores the value to be substituted by ${
and “}
” and specify as substitution variable. In the above example,${direction}
part is substituted byDESC
orASC
.Warning
Embedding value by a substitution variable must be used only after ensuring that the value is safe for the application and by restricting its use to table name, column name and sort conditions.For example, the pair of code value and value to be embedded in SQL is stored in
Map
as shown below.Map<String, String> directionMap = new HashMap<String, String>(); directionMap.put("1", "ASC"); directionMap.put("2", "DESC");The value entered should be handled as code value and is expected to be converted to a safe value inside the process executing the SQL.
String direction = directionMap.get(directionCode); todoRepository.findAllByCriteria(criteria, direction);In the above example,
Map
is used. However, “Codelist ” provided by common library can also be used. If “Codelist ” is used, the value entered can be checked. Hence, the value can be safely embedded.
projectName-domain/src/main/resources/META-INF/spring/projectName-codelist.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> <bean id="CL_DIRECTION" class="org.terasoluna.gfw.common.codelist.SimpleMapCodeList"> <property name="map"> <map> <entry key="1" value="ASC" /> <entry key="2" value="DESC" /> </map> </property> </bean> </beans>Service class
@Inject @Named("CL_DIRECTION") CodeList directionCodeList; // ... public List<Todo> searchTodos(TodoCriteria criteria, String directionCode){ String direction = directionCodeList.asMap().get(directionCode); List<Todo> todos = todoRepository.findAllByCriteria(criteria, direction); return todos; }
6.2.3. How to extend¶
6.2.3.1. Sharing SQL statement¶
How to share a SQL statement in multiple SQLs is explained.
In MyBatis3, SQL statement (or a part of SQL statement) can be shared by using sql
element and include
element.
Note
How to use a shared SQL statement
When pagination search is to be performed, WHERE clause of “SQL that fetches total records of Entity matching with search conditions” and “SQL that fetches a list of Entities matching with search conditions” should be shared.
Implementation example of mapping file is as given below.
<!-- (1) --> <sql id="findPageByCriteriaWherePhrase"> <![CDATA[ WHERE todo_title LIKE #{title} || '%' ESCAPE '~' AND created_at < #{createdAt} ]]> </sql> <select id="countByCriteria" resultType="_long"> SELECT COUNT(*) FROM t_todo <!-- (2) --> <include refid="findPageByCriteriaWherePhrase"/> </select> <select id="findPageByCriteria" resultType="Todo"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo <!-- (2) --> <include refid="findPageByCriteriaWherePhrase"/> ORDER BY todo_id </select>
Sr. No. Description
Implement SQL statement to be shared by multiple SQLs, in
sql
element.Specify an ID unique to the mapping file, in
id
attribute.
Specify the INCLUDE SQL by using
include
element.Specify the INCLUDE SQL ID (value specified in
id
attribute ofsql
element), inrefid
attribute.
6.2.3.2. Implementation of TypeHandler¶
Creation of a unique TypeHandler
is required when mapping
with Joda-Time class not supported by MyBatis3 standards, is necessary.
This guideline explains how to implement TypeHandler
using “Implementing TypeHandler for Joda-Time” as an example.
Refer to “TypeHandler settings” for how to apply a created TypeHandler
in an application.
Note
Regarding implementation for BLOB and CLOB
TypeHandler
added by MyBatis 3.4 implements conversion of BLOB andjava.io.InputStream
, and CLOB andjava.io.Reader
by using API added by JDBC 4.0 (Java 1.6).In case of a JDBC driver of JDBC 4.0 support, it is not necessary to implement a new
TypeHandler
since type handler for the conversion of BLOB⇔InputStream
and CLOB⇔Reader
is enabled by default.If a JDBC driver incompatible with JDBC 4.0 is used, a
TypeHandler
should be created considering compatibility version of JDBC driver to be used.For example, some methods added from JDBC 4.0 are not yet implemented in JDBC driver (
postgresql-9.4-1212.jar
) for PostgreSQL9.4.
6.2.3.2.1. Implementing the TypeHandler for CLOB¶
MyBatis3 provides a TypeHandler
for mapping CLOB in java.lang.String
.
However, when the data to be handled is very large, it is necessary to map it in java.io.Reader
.
How to implement the TypeHandler
for mapping CLOB in java.io.Reader
is given below.
package com.example.infra.mybatis.typehandler; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import java.io.Reader; import java.sql.*; // (1) public class ClobReaderTypeHandler extends BaseTypeHandler<Reader> { // (2) @Override public void setNonNullParameter(PreparedStatement ps, int i, Reader parameter, JdbcType jdbcType) throws SQLException { ps.setClob(i, parameter); } // (3) @Override public Reader getNullableResult(ResultSet rs, String columnName) throws SQLException { return toReader(rs.getClob(columnName)); } // (3) @Override public Reader getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return toReader(rs.getClob(columnIndex)); } // (3) @Override public Reader getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return toReader(cs.getClob(columnIndex)); } private Reader toReader(Clob clob) throws SQLException { // (4) if (clob == null) { return null; } else { return clob.getCharacterStream(); } } }
Sr. No. Description
Specify
BaseTypeHandler
provided by MyBatis3 in parent class.In such cases, specify
Reader
in generic type ofBaseTypeHandler
.
Implement a process that sets Reader
inPreparedStatement
.
Fetch Reader
fromClob
that is fetched fromResultSet
orCallableStatement
and return it as the return value.
Since fetched
Clob
can becomenull
in the column that allowsnull
,Reader
needs to be fetched only after performingnull
check.In the implementation example described above, a private method is created since same process is required for all three methods.
6.2.3.2.2. Implementing TypeHandler for Joda-Time¶
org.joda.time.DateTime
, org.joda.time.LocalDateTime
, org.joda.time.LocalDate
etc.).TypeHandler
for Joda-Time.How to implement a TypeHandler
for mapping org.joda.time.DateTime
and java.sql.Timestamp
is shown below.
Note
Other classes provided by Joda-Time (
LocalDateTime
,LocalDate
,LocalTime
etc.) can also be implemented in the same way.package com.example.infra.mybatis.typehandler; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import org.joda.time.DateTime; // (1) public class DateTimeTypeHandler extends BaseTypeHandler<DateTime> { // (2) @Override public void setNonNullParameter(PreparedStatement ps, int i, DateTime parameter, JdbcType jdbcType) throws SQLException { ps.setTimestamp(i, new Timestamp(parameter.getMillis())); } // (3) @Override public DateTime getNullableResult(ResultSet rs, String columnName) throws SQLException { return toDateTime(rs.getTimestamp(columnName)); } // (3) @Override public DateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return toDateTime(rs.getTimestamp(columnIndex)); } // (3) @Override public DateTime getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return toDateTime(cs.getTimestamp(columnIndex)); } private DateTime toDateTime(Timestamp timestamp) { // (4) if (timestamp == null) { return null; } else { return new DateTime(timestamp.getTime()); } } }
Sr. No. Description
Specify the
BaseTypeHandler
provided by MyBatis3 in parent class.In such cases, specify
DateTime
in the generic type ofBaseTypeHandler
.
Convert DateTime
toTimestamp
and implement the process configured in ``PreparedStatement``.
Convert Timestamp
fetched fromResultSet
orCallableStatement
toDateTime
and return as a return value.
Since
Timestamp
can becomenull
in the column that allowsnull
, it needs to be converted toDateTime
only after performingnull
check.In the implementation example described above, a private method is created since same process is required for all three methods.
6.2.3.3. Implementation of ResultHandler¶
MyBatis3 provides a mechanism wherein search results are processed for each record.
When this mechanism is used and processes like,
- Value fetched by DB is processed in Java process
- Values etc. fetched by DB are aggregated as Java process
are performed, the amount of memory consumed simultaneously can be restricted to a minimum.
For example, when the process is to be implemented wherein, search results are downloaded as data in CSV format, it is advisable to process the search results per record.
Note
It is strongly recommended to use this mechanism when the quantity of search results may be very large and when it is necessary to process the search result for each record at a time in Java process.
When this mechanism of processing the search result for each record is not used, all the search result data, “Size of one data record * number of search result records”, is stored in the memory at the same time, and the data cannot be marked for GC till the process is completed for entire data.
In contrast, when a mechanism wherein the search results are processed one at a time, is used, only the “size of one data record” is stored in the memory and that one data record can be marked for GC once the process for that data record is completed.
For example, when “size of one data record” is
2KB
and “number of search results” are10,000
records, the concurrent memory consumption is as below.
20MB
memory is consumed while performing the process collectively2KB
memory is consumed while performing the process per recordNo particular issues have been observed in case of an application operated by a single thread. However, problems may occur in case of an application like Web application that is operated by multiple threads.
If the process is performed for 100 threads at the same time, the concurrent memory consumption is as below.
2GB
memory is consumed while performing the process collectively200KB
memory is consumed while performing the process per recordThe results are as below.
- When the process is performed collectively, depending on the maximum heap size specified, system failure due to memory exhaustion and performance degradation due to frequent occurrence of full GC are more likely to occur.
- When the process is performed per record, memory exhaustion or high-cost GC process can be controlled.
Please note that the numbers used above are just the guidelines and not the actual measured values.
How to implement a process wherein the search results are downloaded as CSV data is given below.
- Defining the method in Repository interface.
public interface TodoRepository { // (1) (2) void collectAllByCriteria(TodoCriteria criteria, ResultHandler<Todo> resultHandler); }
Sr. No. Description
Specify org.apache.ibatis.session.ResultHandler
as an argument of the method.
Specify
void
type as the return value of the method.Precaution should be taken as,
ResultHandler
is not called when a type other thanvoid
is specified.
- Defining SQL in mapping file.
<!-- (3) --> <select id="collectAllByCriteria" parameterType="TodoCriteria" resultType="Todo"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo <where> <if test="title != null"> <bind name="titleContainingCondition" value="@org.terasoluna.gfw.common.query.QueryEscapeUtils@toContainingCondition(title)" /> todo_title LIKE #{titleContainingCondition} ESCAPE '~' </if> <if test="createdAt != null"> <![CDATA[ AND created_at < #{createdAt} ]]> </if> </where> </select>
Sr. No. Description
Mapping file is implemented in the same way as the normal search process. Warning
Specifying fetchSize attribute
When a query to return a large amount of data is to be described, an appropriate value should be set in
fetchSize
attribute.fetchSize
is a parameter which specifies data record count to be fetched in a single communication between JDBC driver and database. Note that, “defaultfetchSize
” can be specified in MyBatis configuration file, in MyBatis3.3.0 and subsequent versions.Refer “fetchSize settings” for details of
fetchSize
.
- Apply DI to Repository in Service class and call Repository interface method.
public class TodoServiceImpl implements TodoService { private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormat.forPattern("yyyy/MM/dd"); @Inject TodoRepository todoRepository; public void downloadTodos(TodoCriteria criteria, final BufferedWriter downloadWriter) { // (4) ResultHandler<Todo> handler = new ResultHandler<Todo>() { @Override public void handleResult(ResultContext<? extends Todo> context) { Todo todo = context.getResultObject(); StringBuilder sb = new StringBuilder(); try { sb.append(todo.getTodoId()); sb.append(","); sb.append(todo.getTodoTitle()); sb.append(","); sb.append(todo.isFinished()); sb.append(","); sb.append(DATE_FORMATTER.print(todo.getCreatedAt().getTime())); downloadWriter.write(sb.toString()); downloadWriter.newLine(); } catch (IOException e) { throw new SystemException("e.xx.fw.9001", e); } } }; // (5) todoRepository.collectAllByCriteria(criteria, handler); } }
Sr. No. Description
Generate
ResultHandler
instance.Implement the process that is performed for each record in
handleResult
method ofResultHandler
.In the above example,
ResultHandler
implementation class is not created andResultHandler
is implemented as an anonymous object. Implementation class can also be created, however when it is not required to be shared by multiple processes, it need not be created.
Call the method of Repository interface.
When calling the method, specify
ResultHandler
instance generated in (4), in the argument.When
ResultHandler
is used, MyBatis repeats the following processes for the number of search results.
- Records are fetched from search results and are mapped to JavaBean.
handleResult(ResultContext)
method ofResultHandler
instance is called.Warning
Precautions while using ResultHandler
When
ResultHandler
is used, following two points should be considered.
- MyBatis3 provides a mechanism wherein the search results are stored in local cache and global binary cache to improve the efficiency of search process. However, the data returned from the method which uses
ResultHandler
as an argument, is not cached.- When
ResultHandler
is used for the statement that maps data of multiple rows in a single Java object by using manual mapping, an object in the incomplete state (the status of related Entity object prior to mapping) can be passed.Tip
ResultContext method
Following method is provided in
ResultContext
which is an argument ofResultHandler#handleResult
method.
Sr. No. Method Description
getResultObject A method for fetching object which mapped search results.
getResultCount A method for fetching the call count of ResultHandler#handleResult
method.
stop A method to notify MyBatis to stop the processing for the subsequent records. It is advisable to use this method when all the subsequent records are to be deleted. A method
isStopped
is also provided inResultContext
.However, its description is omitted since it is used by MyBatis.
6.2.3.4. Using SQL execution mode¶
Following three types of modes are provided in MyBatis3 to execute SQL. Default is SIMPLE
.
Here,
- How to use execution mode
- Precautions while using Repository of batch mode
6.2.3.4.1. Using PreparedStatement reuse mode¶
When the execution mode is changed from SIMPLE
to ``REUSE`` mode, the handling of PreparedStatement
in MyBatis changes.
However, there is no change in behavior (use method) of MyBatis.
How to change the execution mode from default (SIMPLE
) to REUSE
is shown below.
- Settings are added to
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
.
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <!-- (1) --> <setting name="defaultExecutorType" value="REUSE"/> </settings> </configuration>
Sr. No. Description
Change
defaultExecutorType
toREUSE
.When the settings given above are performed, default behavior changes to PreparedStatement reuse mode.
6.2.3.4.2. Using batch mode¶
When all the Update system methods of Mapper interface are to be called in a batch mode,
execution mode can be changed to BATCH
mode using the method same as “Using PreparedStatement reuse mode”
However, since batch mode has a number of constraints,
in actual application development, it is presumed to be used in combination with SIMPLE
or REUSE
mode.
For example,
- Batch mode is used in the process wherein the highest priority is to meet performance requirements associated with update of large amount of data.
SIMPLE
orREUSE
mode is used for a process wherein it is necessary to determine the update results to maintain data consistency such as in optimistic locking control etc.
Warning
** Precautions while using the execution mode in combination**
When multiple execution modes are to be used in the application, it should be noted that the execution mode cannot be changed within the same transaction.
For example, if multiple execution modes are used within the same transaction, MyBatis detects inconsistency and throws an error.
This signifies that the processes below cannot be performed within the same transaction
- Calling XxxRepository method in
BATCH
mode- Calling YyyRepository method in
REUSE
mode.Service or Repository acts as the transaction boundary for the application that is created based on these guidelines.
Hence, when multiple execution modes are to be used in the application, it is necessary to identify the execution mode while designing a Service or a Repository.
Transaction can be separated by specifying
@Transactional(propagation = Propagation.REQUIRES_NEW)
as method annotation for Service or Repository. Refer to “Regarding transaction management” for details of transaction control.
Hereafter,
- How to configure for using multiple execution modes in combination
- How to implement an application
are explained.
6.2.3.4.2.1. Settings for creating an individual batch mode Repository¶
When a batch mode Repository is to be created for a specific Repository,
a Bean can be defined for the Repository by using org.mybatis.spring.mapper.MapperFactoryBean
provided by MyBatis-Spring.
A Bean is registered for
- A repository of
REUSE
mode as a Repository for normal use - A Repository of
BATCH
mode for a specific Repository
in the configuration example below.
- Bean definition is added to -
projectName-domain/src/main/resources/META-INF/spring/projectName-infra.xml
.
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd"> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <property name="configLocation" value="classpath:META-INF/mybatis/mybatis-config.xml"/> </bean> <!-- (1) --> <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"> <constructor-arg index="0" ref="sqlSessionFactory"/> <constructor-arg index="1" value="REUSE"/> </bean> <mybatis:scan base-package="com.example.domain.repository" template-ref="sqlSessionTemplate"/> <!-- (2) --> <!-- (3) --> <bean id="batchSqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"> <constructor-arg index="0" ref="sqlSessionFactory"/> <constructor-arg index="1" value="BATCH"/> </bean> <!-- (4) --> <bean id="todoBatchRepository" class="org.mybatis.spring.mapper.MapperFactoryBean"> <!-- (5) --> <property name="mapperInterface" value="com.example.domain.repository.todo.TodoRepository"/> <!-- (6) --> <property name="sqlSessionTemplate" ref="batchSqlSessionTemplate"/> </bean> </beans>
Sr. No. Description
Define the Bean for SqlSessionTemplate
to be used in the Repository for normal use.
Scan the Repository for normal use and register a Bean.
Specify
SqlSessionTemplate
defined in (1), intemplate-ref
attribute.
Define a Bean for SqlSessionTemplate
in order to use in the Repository of batch mode.
Define a Bean for the Repository in batch mode.
Specify a value that does not overlap with the Bean name of Repository scanned in (2), in
id
attribute. The Bean name of Repository scanned in (2) is a value for which the interface name is changed to “lowerCamelCase”.In the above example,
TodoRepository
for batch mode is registered in a Bean calledtodoBatchRepository
.
Specify an interface name (FQCN) for Repository that uses a batch mode, in mapperInterface
property.
Specify SqlSessionTemplate
for batch mode that is defined in (3), insqlSessionTemplate
property.Note
If a Bean is defined for
SqlSessionTemplate
, following WARN log is output when the application is terminated.This is because
close
method is called while terminating ApplicationContext of Spring since ``java.io.Closeable`` is inherited bySqlSession
interface.21:12:35.999 [Thread-2] WARN o.s.b.f.s.DisposableBeanAdapter - Invocation of destroy method 'close' failed on bean with name 'sqlSessionTemplate' java.lang.UnsupportedOperationException: Manual close is not allowed over a Spring managed SqlSession at org.mybatis.spring.SqlSessionTemplate.close(SqlSessionTemplate.java:310) ~[mybatis-spring-1.2.2.jar:1.2.2] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_20] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_20] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_20] at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_20]If there are no specific issues related to system operation, this measure is not required since it does not affect the application behavior even after the log is output.
However, in case of any system operation issues like log monitoring etc, log output can be controlled by specifying the method (
destroy-method
attribute) that is called while terminating Spring ApplicationContext.In the example below, it is specified such that
getExecutorType
method is called.getExecutorType
is the method which is only used for returning the execution mode specified in constructor argument and has no impact on other operations.<bean id="batchSqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate" destroy-method="getExecutorType"> <constructor-arg index="0" ref="sqlSessionFactory"/> <constructor-arg index="1" value="BATCH"/> </bean>
6.2.3.4.2.2. Settings for creating Batch mode Repository in batch¶
When a batch mode Repository is to be created in a batch, a Bean can be defined for the Repository
by using the scan function (mybatis:scan
element) provided by MyBatis-Spring.
In the configuration example below, Bean is registered for the Repositories of REUSE
mode and BATCH
mode, with respect to all the Repositories.
- Create
BeanNameGenerator
.
package com.example.domain.repository; import org.springframework.beans.factory.config.BeanDefinition; import org.springframework.beans.factory.support.BeanDefinitionRegistry; import org.springframework.beans.factory.support.BeanNameGenerator; import org.springframework.util.ClassUtils; import java.beans.Introspector; // (1) public class BachRepositoryBeanNameGenerator implements BeanNameGenerator { // (2) @Override public String generateBeanName(BeanDefinition definition, BeanDefinitionRegistry registry) { String defaultBeanName = Introspector.decapitalize(ClassUtils.getShortName(definition .getBeanClassName())); return defaultBeanName.replaceAll("Repository", "BatchRepository"); } }
Sr. No. Description
Create a class that generates the Bean name to be registered in Spring ApplicationContext.
This class is necessary to avoid duplication in the bean name of
REUSE
mode Repository for normal use and the Bean name ofBATCH
mode.
Implement the method for generating a Bean name.
In the above example, duplication of Bean name for
REUSE
mode Repository for normal use can be prevented by settingBatchRepository
as the Bean name suffix.
- Bean definition is added to
projectName-domain/src/main/resources/META-INF/spring/projectName-infra.xml
.
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd"> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <property name="configLocation" value="classpath:META-INF/mybatis/mybatis-config.xml"/> </bean> <!-- ... --> <bean id="batchSqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"> <constructor-arg index="0" ref="sqlSessionFactory"/> <constructor-arg index="1" value="BATCH"/> </bean> <!-- (3) --> <mybatis:scan base-package="com.example.domain.repository" template-ref="batchSqlSessionTemplate" name-generator="com.example.domain.repository.BatchRepositoryBeanNameGenerator"/> </beans>
Sr. No. Attribute Description
- Register the Bean for batch mode Repository by using mybatis:scan
element.base-package Specify the base package that scans the Repository.
Repository interface that exists under specified package is scanned and a Bean is registered in Spring ApplicationContext.
template-ref Specify Bean of SqlSessionTemplate
for batch mode.name-generator Specify a class for generating the Bean name of scanned Repository.
Basically, specify the class name (FQCN) for the class created in (1).
If class name is not specified, Bean names are duplicated. Hence, batch mode Repository is not registered in Spring ApplicationContext.
6.2.3.4.2.3. How to use the Repository of batch mode¶
An implementation example on how to access database by using batch mode Repository is given below.
@Transactional @Service public class TodoServiceImpl implements TodoService { // (1) @Inject @Named("todoBatchRepository") TodoRepository todoBatchRepository; @Override public void updateTodos(List<Todo> todos) { for (Todo todo : todos) { // (2) todoBatchRepository.update(todo); } } }
Sr. No. Description
Inject the Repository of batch mode.
Call the method for batch mode Repository and update Entity.
In case of batch mode Repository, since the SQL is not executed in the timing when method is called. As a result, the update results returned from method need to be ignored.
The SQL for updating an Entity is executed in a batch immediately before committing a transaction and the transaction is committed if there is no error.
Note
Batch execution timing
Timing of SQL batch execution is as below.
- Immediately before the transaction is committed
- Immediately before executing the query (SELECT)
Notes of sequence of calling repository method refer to “Sequence of calling Repository method”.
6.2.3.4.3. Precautions when using batch mode Repository¶
It is important to note the following points in implementation of Service class when using batch mode Repository.
- Determination of update results
- How to detect the unique constraint violation
- Sequence of calling Repository method
6.2.3.4.3.1. Determination of update results¶
When batch mode Repository is used, the validity of update results cannot be checked.
Update results returned from Mapper interface method when a batch mode is used, are as follows.
- Fixed value(
org.apache.ibatis.executor.BatchExecutor#BATCH_UPDATE_RETURN_VALUE
) when return value is numeric (int
orlong
) false
when return value isboolean
This is due to the mechanism wherein SQL is not executed within the timing of calling Mapper interface method
but is queued for batch execution (java.sql.Statement#addBatch()
).
It signifies that the implementation below cannot be performed.
@Transactional @Service public class TodoServiceImpl implements TodoService { @Inject @Named("todoBatchRepository") TodoRepository todoBatchRepository; @Override public void updateTodos(List<Todo> todos) { for (Todo todo : todos) { boolean updateSuccess = todoBatchRepository.update(todo); // (1) if (!updateSuccess) { // ... } } } }
Sr. No. Description
When the implementation is performed as described below, update results are always returned as false
resulting in the execution of process at the time of update failure.
Based on the application requirement, it is also necessary to check the validity of update results executed in the batch. In such cases, “a method to execute SQL queued for batch execution” is provided in the Mapper interface.”
In MyBatis 3.2, flushStatements
method of org.apache.ibatis.session.SqlSession
interface must be called directly, however,
a method is supported in MyBatis 3.3.0 and subsequent versions wherein a method which assigns
@org.apache.ibatis.annotations.Flush
annotation in Mapper interface is created.
Warning
Regarding update results returned by JDBC driver while using batch mode
Although it has been described earlier that update results at the time of batch execution can be received when a method which assigns
@Flush
annotation (andflushStatements
method ofSqlSession
interface) is used, it cannot be guaranteed that the update results returned from JDBC driver can be used as “number of processed records”.Since it depends on implementation of JDBC driver to be used, the specifications of JDBC driver to be used must be checked in advance.
How to create and call a method which assigns @Flush
annotation is given below.
|
public interface TodoRepository { // ... @Flush // (1) List<BatchResult> flush(); }@Transactional @Service public class TodoServiceImpl implements TodoService { @Inject @Named("todoBatchRepository") TodoRepository todoBatchRepository; @Override public void updateTodos(List<Todo> todos) { for (Todo todo : todos) { todoBatchRepository.update(todo); } List<BatchResult> updateResults = todoBatchRepository.flush(); // (2) // Validate update results // ... } }
Sr. No. Description
Create a method which assigns
@Flush
annotation (hereafter referred to as “@Flush
method”).When it is necessary to determine update results, specify list type of
org.apache.ibatis.executor.BatchResult
as a return value. When it is not necessary to determine update results (when only a database error like a unique constraint violation is to be handled), the return value can bevoid
.
Call
@Flush
method within the timing in which SQL queued for batch execution is to be executed. If@Flush
method is called,flushStatements
method ofSqlSession
object associated with Mapper interface is called and SQL queued for batch execution is executed.When it is necessary to determine update results, validity of update results returned from
@Flush
method is checked.
6.2.3.4.3.2. How to detect the unique constraint violation¶
When batch mode Repository is used, it is not possible to detect database errors like unique constraint violation etc. as a Service process.
This is due to the mechanism wherein SQL is not executed within the timing of calling a Mapper interface method
and is queued for the batch execution (java.sql.Statement#addBatch()
).
It signifies that the implementation given below cannot be performed.
@Transactional @Service public class TodoServiceImpl implements TodoService { @Inject @Named("todoBatchRepository") TodoRepository todoBatchRepository; @Override public void storeTodos(List<Todo> todos) { for (Todo todo : todos) { try { todoBatchRepository.create(todo); // (1) } catch (DuplicateKeyException e) { // .... } } } }
Sr. No. Description
When the implementation is performed as described above,
org.springframework.dao.DuplicateKeyException
exception does not occur within that timing. Hence, the process afterDuplicateKeyException
supplement is not executed.This is because SQL batch execution is performed after termination of Service process (just before the transaction is committed).
Depending on application requirement, it is necessary to detect a unique constraint violation at the time of batch execution.
In such cases, “a method to execute SQL queued for batch execution (@Flush
method)” must be provided in Mapper interface.
Refer “Determination of update results” described earlier for details of @Flush
method.
6.2.3.4.3.3. Sequence of calling Repository method¶
Batch mode is used to improve the performance of update process. However, if the calling sequence of Repository method is incorrect, no improvement is observed in the performance.
It is important to understand the MyBatis specifications given below, in order to improve the performance using batch mode.
- When query (SELECT) is executed, SQL waiting in the queue till then is executed in batch.
PreparedStatement
is generated for each update process (Repository method) called in succession and SQL is queued.
It signifies that if the implementation is performed as below, advantages of using batch mode cannot be obtained.
- Example 1
@Transactional @Service public class TodoServiceImpl implements TodoService { @Inject @Named("todoBatchRepository") TodoRepository todoBatchRepository; @Override public void storeTodos(List<Todo> todos) { for (Todo todo : todos) { // (1) Todo currentTodo = todoBatchRepository.findOne(todo.getTodoId()); if (currentTodo == null) { todoBatchRepository.create(todo); } else{ todoBatchRepository.update(todo); } } } }
Sr. No. Description
When the implementation is performed as described in the above example, since a query is executed at the start of iteration process, SQL is executed in a batch for each record. This is almost same as the execution in simple mode (
SIMPLE
).When the process described above is necessary, it is more effective to use Repository of PreparedStatement reuse mode (
REUSE
).
- Example 2
@Transactional @Service public class TodoServiceImpl implements TodoService { @Inject @Named("todoBatchRepository") TodoRepository todoBatchRepository; @Override public void storeTodos(List<Todo> todos) { for (Todo todo : todos) { // (2) todoBatchRepository.create(todo); todoBatchRepository.createHistory(todo); } } }
Sr. No. Description
When the process described above is necessary, Repository method is called alternately. Hence,
PreparedStatement
is generated for each record. This is almost same as executing a process in simple mode (SIMPLE
).When the process described above is necessary, it is more effective to use PreparedStatement reuse mode Repository (
REUSE
).
6.2.3.5. Implementation of a stored procedure¶
How to call a stored procedure or function registered in database from MyBatis3 is explained.
A function registered in PostgreSQL is called in the implementation example explained below.
- Register a stored procedure (function).
/* (1) */ CREATE FUNCTION findTodo(pTodoId CHAR) RETURNS TABLE( todo_id CHAR, todo_title VARCHAR, finished BOOLEAN, created_at TIMESTAMP, version BIGINT ) AS $$ BEGIN RETURN QUERY SELECT t.todo_id, t.todo_title, t.finished, t.created_at, t.version FROM t_todo t WHERE t.todo_id = pTodoId; END; $$ LANGUAGE plpgsql;
Sr. No. Description
It is the function used to fetch records for specified ID.
- Defining the method in Repository interface.
// (2) public interface TodoRepository extends Repository { Todo findOne(String todoId); }
Sr. No. Description
The interface should be same as the interface used while executing the SQL.
- Call a stored procedure in the mapping file.
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <!-- (3) --> <select id="findOne" parameterType="string" resultType="Todo" statementType="CALLABLE"> <!-- (4) --> {call findTodo(#{todoId})} </select> </mapper>
Sr. No. Description
Implement the statement to call the stored procedure.
Specify
CALLABLE
instatementType
attribute when calling the stored procedure. WhenCALLABLE
is specified, the stored procedure is called by usingjava.sql.CallableStatement
.Specify
resultType
attribute orresultMap
attribute in order to map the OUT parameter in JavaBean.
Call the stored procedure.
When the stored procedure (faction) is to be called,
specify in
{call Procedure or Function name (IN parameter...)}
format.In the above example, the procedure is called by specifying an ID in the IN parameter for a function called
findTodo
.
6.2.4. Appendix¶
6.2.4.1. Mapper interface mechanism¶
How to create a Mapper interface
In this guideline, since it is presumed that the Mapper interface of MyBatis3 is used as Repository interface, interface name is in the format, “Entity name” +
Repository
.
package com.example.domain.repository.todo; import com.example.domain.model.Todo; public interface TodoRepository { Todo findOne(String todoId); }
How to create a mapping file
In the mapping file, FQCN (fully qualified class name) of Mapper interface is specified as namespace. Its association with SQL that is executed while calling the method defined in the Mapper interface can be formed by specifying a method name in id attribute of various statement tags (insert/update/delete/select tags).
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.domain.repository.todo.TodoRepository"> <resultMap id="todoResultMap" type="Todo"> <result column="todo_id" property="todoId" /> <result column="title" property="title" /> <result column="finished" property="finished" /> </resultMap> <select id="findOne" parameterType="String" resultMap="todoResultMap"> SELECT todo_id, title, finished FROM t_todo WHERE todo_id = #{todoId} </select> </mapper>
How to use Mapper interface in an application (Service)
When a method of Mapper interface is to be called from an application (Service), a method of Mapper object injected by Spring (DI container) is called. The application (Service) transparently executes SQL by calling Mapper object method and can obtain SQL execution results.
package com.example.domain.service.todo; import com.example.domain.model.Todo; import com.example.domain.repository.todo.TodoRepository; public class TodoServiceImpl implements TodoService { @Inject TodoRepository todoRepository; public Todo getTodo(String todoId){ Todo todo = todoRepository.findOne(todoId); if(todo == null){ throw new ResourceNotFoundException( ResultMessages.error().add("e.ex.td.5001" ,todoId)); } return todo; } }
The process flow up to SQL execution when Mapper interface method is called, is shown below.
Sr. No. Description
Application calls the method defined in Mapper interface.
The implementation class of Mapper interface (Proxy object of Mapper interface) is generated by MyBatis3 components at application startup time.
Proxy object of Mapper interface calls invoke method of
MapperProxy
.Role of
MapperProxy
is to handle the method calling of Mapper interface.
MapperProxy
generatesMapperMethod
corresponding to the called Mapper interface method and calls execute method.
MapperMethod
plays the role of callingSqlSession
method corresponding to the called Mapper interface method.
MapperMethod
callsSqlSession
method.When a
SqlSession
method is called, a key (hereafter referred to as “Statement ID”) is passed in order to specify the SQL statement to be executed.
SqlSession
fetches a SQL statement from a mapping file using the specified statement ID as a key.
SqlSession
sets the value in bind variable specified in the SQL statement fetched by mapping file and executes SQL.
Mapper interface (
SqlSession
) converts SQL execution results to JavaBean etc. and returns it to the application.When number of records or number of updated records are to be fetched, primitive type or primitive wrapper type etc. form the return values.
Tip
Statement ID
Statement ID is a key to specify the SQL statement to be executed. It is generated in accordance with the rule “FQCN of Mapper interface + “.” + name of the called Mapper interface method”.
When SQL statement corresponding to the statement ID generated by
MapperMethod
is to be defined in the mapping file, it is necessary to specify “FQCN of Mapper interface” in the namespace of mapping file and “method name of Mapper interface” in id attribute of various statement tags.
6.2.4.2. TypeAlias settings¶
TypeAlias should basically be configured per package by using package
element. However, following methods can also be used.
- A method to configure an alias name per class
- A method to overwrite the alias name which is assigned as default (a method that specifies an optional alias name)
6.2.4.2.1. Configuring TypeAlias per class¶
TypeAlias can also be configured per class.
projectName-domain/src/main/resources/META-INF/mybatis/mybatis-config.xml
<typeAliases> <!-- (1) --> <typeAlias type="com.example.domain.repository.account.AccountSearchCriteria" /> <package name="com.example.domain.model" /> </typeAliases>
Sr. No. Description
Specify the fully qualified class name (FQCN) of the class for which alias is to be set, in
type
attribute oftypeAlias
element.In the above example, alias name of
com.example.domain.repository.account.AccountSearchCriteria
class becomesAccountSearchCriteria
(part after removing package part).When an optional value is to be specified in the alias name, optional alias name can be specified in
alias
attribute oftypeAlias
element.
6.2.4.2.2. Overwriting alias name assigned as default¶
When alias is set by using package
element or
when alias is set by omitting alias
attribute of typeAlias
element,
alias for TypeAlias is a part obtained after removing the package part from fully qualified class name (FQCN).
When optional alias is to be used instead of default alias,
it can be specified by specifying @org.apache.ibatis.type.Alias
annotation in the class wherein TypeAlias to is to be configured.
- Java class for configuring alias
package com.example.domain.model.book; @Alias("BookAuthor") // (1) public class Author { // ... }package com.example.domain.model.article; @Alias("ArticleAuthor") // (1) public class Author { // ... }
Sr. No. Description
Specify the alias name in
value
attribute of@Alias
annotation.In the above example, the alias name for
com.example.domain.model.book.Author
class isBookAuthor
.When a class with same name is stored in a different package, different alias names can be configured for each class by using this method. However, in this guideline, it is recommended to design the class name such that duplication is avoided. In the above example,
BookAuthor
andArticleAuthor
should be considered as class names.Tip
An alias name for TypeAlias is applied in the following priority order.
- Value specified for
alias
attribute oftypeAlias
element- Value specified for
value
attribute of@Alias
annotation- Alias name assigned as default (part after removing the package name from fully qualified class name)
6.2.4.3. SQL switching by the database¶
MyBatis3 provides a mechanism (org.apache.ibatis.mapping.VendorDatabaseIdProvider
) wherein vendor information of database that is connected from JDBC driver, is fetched
and SQL to be used is switched.
This mechanism is effective when building an application that can support multiple databases as operating environment.
Note
In this guideline, it is recommended to manage the components and configuration file that are dependent on the environment by a sub project called [projectName]-env and create components and configuration file that are in execution environment at the time of building.
[projectName]-env is a sub-project to absorb differences in each of the following
- Development environment (local PC environment)
- Various test environments
- Commercial environment
It can also be used in the development of an application that supports multiple databases.
Basically, it is recommended to manage environment-dependent components and configuration file by using a sub-project called [projectName]-env. However, the mechanism below can also be used if only a minor SQL difference is to be absorbed.
Architects should try to achieve a uniform implementation for overall application by clearly identifying a guideline on how to implement SQL environment dependency based on the differences in the database.
- Bean is defined in
projectName-domain/src/main/resources/META-INF/spring/projectName-infra.xml
.
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd "> <import resource="classpath:/META-INF/spring/projectName-env.xml" /> <!-- (1) --> <bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider"> <!-- (2) --> <property name="properties"> <props> <prop key="H2">h2</prop> <prop key="PostgreSQL">postgresql</prop> </props> </property> </bean> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <!-- (3) --> <property name="databaseIdProvider" ref="databaseIdProvider"/> <property name="configLocation" value="classpath:/META-INF/mybatis/mybatis-config.xml" /> </bean> <mybatis:scan base-package="com.example.domain.repository" /> </beans>
Sr. No. Description
Define a Bean for
VendorDatabaseIdProvider
provided by MyBatis3.
VendorDatabaseIdProvider
is a class for handling a product name (java.sql.DatabaseMetaData#getDatabaseProductName()
) of a database that is fetched from JDBC driver, as a database ID.
Specify the mapping of database product name fetched from JDBC driver and database ID in
properties
property.Refer to “MyBatis3 REFERENCE DOCUMENTATION(Configuration-databaseIdProvider-)” for specifications of mapping.
Specify
DatabaseIdProvider
defined in (1) for thedatabaseIdProvider
property ofSqlSessionFactoryBean
that uses database IDBy specifying this, it is possible to refer the database ID from mapping file.
Note
In this guideline, it is recommended to use a method to map the product name of database and database ID, by specifying
properties
propertyThis is due to possible change in the product name of database that can be fetched from JDBC driver, based on the JDBC driver version.
When
properties
property is used, the difference between product names depending on the version of JDBC driver to be used can be managed at a single location.
- Implementing mapping file.
<insert id="create" parameterType="Todo"> <!-- (1) --> <selectKey keyProperty="todoId" resultType="string" order="BEFORE" databaseId="h2"> SELECT RANDOM_UUID() </selectKey> <selectKey keyProperty="todoId" resultType="string" order="BEFORE" databaseId="postgresql"> SELECT UUID_GENERATE_V4() </selectKey> INSERT INTO t_todo ( todo_id ,todo_title ,finished ,created_at ,version ) VALUES ( #{todoId} ,#{todoTitle} ,#{finished} ,#{createdAt} ,#{version} ) </insert>
Sr. No. Description
When statement elements (
select
element,update
element,sql
element etc.) are to be changed for each database, specify the database ID indatabaseId
attribute of each element.When
databaseId
attribute is specified, the statement element that matches with database ID is used.In the above example, ID is generated, by calling the UUID generation function specific for each database.
Tip
In the above example,
UUID_GENERATE_V4()
is called as the UUID generation function for PostgreSQL. However, this function belongs to a sub-module called uuid-ossp.When this function is to be used, uuid-ossp module should be enabled.
Tip
Database ID can also be referred in OGNL base expression (Expression language).
It signifies that database ID can be used as a condition for dynamic SQL. How to implement is given below.
<select id="findAllByCreatedAtBefore" parameterType="_int" resultType="Todo"> SELECT todo_id, todo_title, finished, created_at, version FROM t_todo WHERE <choose> <!-- (2) --> <when test="_databaseId == 'h2'"> <bind name="criteriaDate" value="'DATEADD(\'DAY\',#{days} * -1,#{currentDate})'"/> </when> <when test="_databaseId == 'postgresql'"> <bind name="criteriaDate" value="'#{currentDate}::DATE - (#{days} * INTERVAL \'1 DAY\')'"/> </when> </choose> <![CDATA[ created_at < ${criteriaDate} ]]> </select>
Sr. No. Description
Database ID is stored in a specific variable called
_databaseId
, in a OGNL base expression (Expression language).In the above example, the condition for extracting records that are created prior to “System date - specified date” is specified by using database function.