......................................................................................................................................

......................................................................................................................................

MyBatis.org                                                                                                           2014-10-11


 


T a b l e o f C o n t e n t s                                                                                                                                                                                                                                                                  i

Table of Contents

.......................................................................................................................................

1. Table of Contents ...........................................................  i

1 Introduction.. 1

2 Getting Started.. 1

3 Configuration XML.. 7

4 Mapper XML Files. 26

5 Dynamic SQL.. 56

6 Java API 62

7 Statement Builders. 80

8 Logging.. 85

 


 

T a b l e o f C o n t e n t s                                                                                                                                                                                                                                                                ii


1 I n t r o d u c t i o n

1 Introduction

.......................................................................................................................................

1.1 Introduction

1.1.1 What is MyBatis?

MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records.

1.1.2 Help make this documentation better…

If you find this documentation lacking in any way, or missing documentation for a feature, then the best thing to do is learn about it and then write the documentation yourself!

Sources of this manual are available in xdoc format at  project's Git  Fork the repository, update them and send a pull request.

You’re the best author of this documentation, people like you have to read it!

1.1.3 Translations

Users can read about MyBatis in following translations:

     English

     Español

     ###

     ###

     ####

Do you want to read about MyBatis in your own native language? Fill an issue providing patches with your mother tongue documentation!

2 Getting Started

.......................................................................................................................................

2.1 Getting started

2.1.1 Installation

To use MyBatis you just need to include the  mybatis-x.x.x.jar  file in the classpath.

If you are using Maven just add the following dependency to your pom.xml:

<dependency>

  <groupId>org.mybatis</groupId>

  <artifactId>mybatis</artifactId>

  <version>x.x.x</version>

</dependency>

2.1.2 Building SqlSessionFactory from XML

Every MyBatis application centers around an instance of SqlSessionFactory. A SqlSessionFactory instance can be acquired by using the SqlSessionFactoryBuilder. SqlSessionFactoryBuilder can build a SqlSessionFactory instance from an XML configuration file, or from a custom prepared instance of the Configuration class.

Building a SqlSessionFactory instance from an XML file is very simple. It is recommended that you use a classpath resource for this configuration, but you could use any InputStream instance, including one created from a literal file path or a file:// URL. MyBatis includes a utility class, called Resources, that contains a number of methods that make it simpler to load resources from the classpath and other locations.

String resource = "org/mybatis/example/mybatis-config.xml";

InputStream inputStream = Resources.getResourceAsStream(resource);

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStre

The configuration XML file contains settings for the core of the MyBatis system, including a DataSource for acquiring database Connection instances, as well as a TransactionManager for determining how transactions should be scoped and controlled. The full details of the XML configuration file can be found later in this document, but here is a simple example:

<?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>

  <environments default="development">

    <environment id="development">

      <transactionManager type="JDBC"/>

      <dataSource type="POOLED">

        <property name="driver" value="${driver}"/>

        <property name="url" value="${url}"/>

        <property name="username" value="${username}"/>

        <property name="password" value="${password}"/>

      </dataSource>

    </environment>

  </environments>

  <mappers>

    <mapper resource="org/mybatis/example/BlogMapper.xml"/>

  </mappers>

</configuration>

While there is a lot more to the XML configuration file, the above example points out the most critical parts. Notice the XML header, required to validate the XML document. The body of the environment element contains the environment configuration for transaction management and connection pooling. The mappers element contains a list of mappers – the XML files and/or annotated Java interface classes that contain the SQL code and mapping definitions.

2.1.3 Building SqlSessionFactory without XML

If you prefer to directly build the configuration from Java, rather than XML, or create your own configuration builder, MyBatis provides a complete Configuration class that provides all of the same configuration options as the XML file.

DataSource dataSource = BlogDataSourceFactory.getBlogDataSource();

TransactionFactory transactionFactory = new JdbcTransactionFactory();

Environment environment = new Environment("development", transactionFactory, dataSou Configuration configuration = new Configuration(environment); configuration.addMapper(BlogMapper.class); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configura

Notice in this case the configuration is adding a mapper class. Mapper classes are Java classes that contain SQL Mapping Annotations that avoid the need for XML. However, due to some limitations of Java Annotations and the complexity of some MyBatis mappings, XML mapping is still required for the most advanced mappings (e.g. Nested Join Mapping). For this reason, MyBatis will automatically look for and load a peer XML file if it exists (in this case, BlogMapper.xml would be loaded based on the classpath and name of BlogMapper.class). More on this later.

2.1.4 Acquiring a SqlSession from SqlSessionFactory

Now that you have a SqlSessionFactory, as the name suggests, you can acquire an instance of SqlSession. The SqlSession contains absolutely every method needed to execute SQL commands against the database. You can execute mapped SQL statements directly against the SqlSession instance. For example:

SqlSession session = sqlSessionFactory.openSession(); try {   Blog blog = session.selectOne("org.mybatis.example.BlogMapper.selectBlog", 101); } finally {   session.close();

}

While this approach works, and is familiar to users of previous versions of MyBatis, there is now a cleaner approach. Using an interface (e.g. BlogMapper.class) that properly describes the parameter and return value for a given statement, you can now execute cleaner and more type safe code, without error prone string literals and casting.

For example:

SqlSession session = sqlSessionFactory.openSession(); try {   BlogMapper mapper = session.getMapper(BlogMapper.class);

  Blog blog = mapper.selectBlog(101); } finally {   session.close();

}

Now let's explore what exactly is being executed here.

2.1.5 Exploring Mapped SQL Statements

At this point you may be wondering what exactly is being executed by the SqlSession or Mapper class. The topic of Mapped SQL Statements is a big one, and that topic will likely dominate the majority of this documentation. But to give you an idea of what exactly is being run, here are a couple of examples.

In either of the examples above, the statements could have been defined by either XML or Annotations. Let's take a look at XML first. The full set of features provided by MyBatis can be realized by using the XML based mapping language that has made MyBatis popular over the years. If you've used MyBatis before, the concept will be familiar to you, but there have been numerous improvements to the XML mapping documents that will become clear later. Here is an example of an XML based mapped statement that would satisfy the above SqlSession calls.

<?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="org.mybatis.example.BlogMapper">   <select id="selectBlog" resultType="Blog">     select * from Blog where id = #{id}   </select>

</mapper>

While this looks like a lot of overhead for this simple example, it is actually very light. You can define as many mapped statements in a single mapper XML file as you like, so you get a lot of mileage out of the XML header and doctype declaration. The rest of the file is pretty self explanatory. It defines a name for the mapped statement “selectBlog”, in the namespace “org.mybatis.example.BlogMapper”, which would allow you to call it by specifying the fully qualified name of “org.mybatis.example.BlogMapper.selectBlog”, as we did above in the following example:

Blog blog = session.selectOne("org.mybatis.example.BlogMapper.selectBlog", 101);

Notice how similar this is to calling a method on a fully qualified Java class, and there's a reason for that. This name can be directly mapped to a Mapper class of the same name as the namespace, with a method that matches the name, parameter, and return type as the mapped select statement. This allows you to very simply call the method against the Mapper interface as you saw above, but here it is again in the following example:

BlogMapper mapper = session.getMapper(BlogMapper.class);

Blog blog = mapper.selectBlog(101);

The second approach has a lot of advantages. First, it doesn't depend on a string literal, so it's much safer. Second, if your IDE has code completion, you can leverage that when navigating your mapped SQL statements.

NOTE A note about namespaces.

Namespaces were optional in previous versions of MyBatis, which was confusing and unhelpful. Namespaces are now required and have a purpose beyond simply isolating statements with longer, fully-qualified names.

Namespaces enable the interface bindings as you see here, and even if you don’t think you’ll use them today, you should follow these practices laid out here in case you change your mind. Using the namespace once, and putting it in a proper Java package namespace will clean up your code and improve the usability of MyBatis in the long term.

Name Resolution: To reduce the amount of typing, MyBatis uses the following name resolution rules for all named configuration elements, including statements, result maps, caches, etc.

     Fully qualified names (e.g. “com.mypackage.MyMapper.selectAllThings”) are looked up directly and used if found.

     Short names (e.g. “selectAllThings”) can be used to reference any unambiguous entry. However if there are two or more (e.g. “com.foo.selectAllThings and com.bar.selectAllThings”), then you will receive an error reporting that the short name is ambiguous and therefore must be fully qualified.

There's one more trick to Mapper classes like BlogMapper. Their mapped statements don't need to be mapped with XML at all. Instead they can use Java Annotations. For example, the XML above could be eliminated and replaced with:

package org.mybatis.example; public interface BlogMapper {   @Select("SELECT * FROM blog WHERE id = #{id}")

  Blog selectBlog(int id);

}

The annotations are a lot cleaner for simple statements, however, Java Annotations are both limited and messier for more complicated statements. Therefore, if you have to do anything complicated, you're better off with XML mapped statements.

It will be up to you and your project team to determine which is right for you, and how important it is to you that your mapped statements be defined in a consistent way. That said, you're never locked into a single approach. You can very easily migrate Annotation based Mapped Statements to XML and vice versa.

2.1.6 Scope and Lifecycle

It's very important to understand the various scopes and lifecycles classes we've discussed so far. Using them incorrectly can cause severe concurrency problems.

NOTE Object lifecycle and Dependency Injection Frameworks

Dependency Injection frameworks can create thread safe, transactional SqlSessions and mappers and inject them directly into your beans so you can just forget about their lifecycle. You may want to have a look at MyBatis-Spring or MyBatis-Guice sub-projects to know more about using MyBatis with DI frameworks.

2.1.6.1 SqlSessionFactoryBuilder

This class can be instantiated, used and thrown away. There is no need to keep it around once you've created your SqlSessionFactory. Therefore the best scope for instances of SqlSessionFactoryBuilder is method scope (i.e. a local method variable). You can reuse the SqlSessionFactoryBuilder to build multiple SqlSessionFactory instances, but it's still best not to keep it around to ensure that all of the XML parsing resources are freed up for more important things.

2.1.6.2 SqlSessionFactory

Once created, the SqlSessionFactory should exist for the duration of your application execution. There should be little or no reason to ever dispose of it or recreate it. It's a best practice to not rebuild the SqlSessionFactory multiple times in an application run. Doing so should be considered a “bad smell”. Therefore the best scope of SqlSessionFactory is application scope. This can be achieved a number of ways. The simplest is to use a Singleton pattern or Static Singleton pattern.

2.1.6.3 SqlSession

Each thread should have its own instance of SqlSession. Instances of SqlSession are not to be shared and are not thread safe. Therefore the best scope is request or method scope. Never keep references to a SqlSession instance in a static field or even an instance field of a class. Never keep references to a SqlSession in any sort of managed scope, such as HttpSession of the Servlet framework. If you're using a web framework of any sort, consider the SqlSession to follow a similar scope to that of an HTTP request. In other words, upon receiving an HTTP request, you can open a SqlSession, then upon returning the response, you can close it. Closing the session is very important. You should always ensure that it's closed within a finally block. The following is the standard pattern for ensuring that SqlSessions are closed:

SqlSession session = sqlSessionFactory.openSession(); try {   // do work } finally {   session.close(); }

Using this pattern consistently throughout your code will ensure that all database resources are properly closed.

2.1.6.4 Mapper Instances

Mappers are interfaces that you create to bind to your mapped statements. Instances of the mapper interfaces are acquired from the SqlSession. As such, technically the broadest scope of any mapper instance is the same as the SqlSession from which they were requested. However, the best scope for mapper instances is method scope. That is, they should be requested within the method that they are used, and then be discarded. They do not need to be closed explicitly. While it's not a problem to keep them around throughout a request, similar to the SqlSession, you might find that managing too many resources at this level will quickly get out of hand. Keep it simple, keep Mappers in the method scope. The following example demonstrates this practice.

SqlSession session = sqlSessionFactory.openSession(); try {   BlogMapper mapper = session.getMapper(BlogMapper.class);

  // do work } finally {   session.close();

}


3 Configuration XML

.......................................................................................................................................

3.1 Configuration

The MyBatis configuration contains settings and properties that have a dramatic effect on how MyBatis behaves. The high level structure of the document is as follows:

     configuration

     properties

     settings

     typeAliases

     typeHandlers

     objectFactory

     plugins

     environments

     environment

     transactionManager

     dataSource

     databaseIdProvider

     mappers

3.1.1 properties

These are externalizable, substitutable properties that can be configured in a typical Java Properties file instance, or passed in through sub-elements of the properties element. For example:

<properties resource="org/mybatis/example/config.properties">

  <property name="username" value="dev_user"/>

  <property name="password" value="F2Fa3!33TYyg"/>

</properties>

The properties can then be used throughout the configuration files to substitute values that need to be dynamically configured. For example:

<dataSource type="POOLED">

  <property name="driver" value="${driver}"/>

  <property name="url" value="${url}"/>

  <property name="username" value="${username}"/>

  <property name="password" value="${password}"/>

</dataSource>

The username and password in this example will be replaced by the values set in the properties elements. The driver and url properties would be replaced with values contained from the config.properties file. This provides a lot of options for configuration.

Properties can also be passed into the SqlSessionBuilder.build() methods. For example:

SqlSessionFactory factory = sqlSessionFactoryBuilder.build(reader, props); // ... or ...

SqlSessionFactory factory = sqlSessionFactoryBuilder.build(reader, environment, prop

If a property exists in more than one of these places, MyBatis loads them in the following order:

     Properties specified in the body of the properties element are read first,

     Properties loaded from the classpath resource or url attributes of the properties element are read second, and override any duplicate properties already specified,

     Properties passed as a method parameter are read last, and override any duplicate properties that may have been loaded from the properties body and the resource/url attributes.

Thus, the highest priority properties are those passed in as a method parameter, followed by resource/ url attributes and finally the properties specified in the body of the properties element.

3.1.2 settings

These are extremely important tweaks that modify the way that MyBatis behaves at runtime. The following table describes the settings, their meanings and their default values.

Setting

Description

Valid Values

Default

cacheEnabled

Globally enables or disables any caches configured in any mapper under this configuration.

true | false

true

lazyLoadingEnabled

Globally enables or disables lazy loading. When enabled, all

relations will be lazily loaded. This value can be superseded for an specific relation by using the fetchType attribute on it.

true | false

false

aggressiveLazyLoading

When enabled, an object with lazy loaded properties will be loaded entirely upon a call to any of the lazy properties. Otherwise, each property is loaded on demand.

true | false

true

multipleResultSetsEnabled Allows or disallows multiple ResultSets to be returned from a single statement (compatible driver required).

true | false

true

 

useColumnLabel

Uses the column label instead of the column name. Different drivers behave differently in this respect. Refer to the driver documentation, or test out both modes to determine how your driver behaves.

true | false

true

useGeneratedKeys

Allows JDBC support for generated keys. A compatible driver is required. This setting forces generated keys to be used if set to true, as some drivers deny compatibility but still work (e.g. Derby).

true | false

False

autoMappingBehavior

Specifies if and how MyBatis should automatically map columns to fields/ properties. NONE disables auto-mapping. PARTIAL will only automap results with no nested result mappings defined inside. FULL will auto-map result mappings of any complexity (containing nested or otherwise).

NONE, PARTIAL, FULL

PARTIAL

defaultExecutorType

Configures the default executor. SIMPLE executor does nothing special. REUSE executor reuses prepared statements. BATCH executor reuses statements and batches updates.

SIMPLE REUSE BATCH

SIMPLE

defaultStatementTimeout

Sets the number of seconds the driver will wait for a response from the database.

Any positive integer

Not Set (null)

safeRowBoundsEnabled

Allows using RowBounds on nested statements.

true | false

False

mapUnderscoreToCamelCaseEnables automatic mapping from classic database column names A_COLUMN to camel case classic Java property names aColumn.

true | false

False

localCacheScope

MyBatis uses local    SESSION | STATEMENT cache to prevent circular references and speed up repeated nested queries. By default (SESSION) all queries executed during

a session are cached. If

localCacheScope=STATEMENT

local session will be used just for statement execution, no data will be shared between two different calls to the same SqlSession.

SESSION

jdbcTypeForNull

Specifies the JDBC type for null values when no specific JDBC type was provided for the parameter. Some drivers require specifying the column JDBC type but others work with generic values like NULL, VARCHAR or OTHER.

JdbcType enumeration.

Most common are: NULL,

VARCHAR and OTHER

OTHER

lazyLoadTriggerMethods

Specifies which Object's methods trigger a lazy load

A method name list separated by commas

equals,clone,hashCode,toString

defaultScriptingLanguage

Specifies the language used by default for dynamic SQL generation.

A type alias or fully qualified class name.

 

callSettersOnNulls

Specifies if setters or map's put method will be called when a retrieved value is null. It is useful when you rely on Map.keySet() or null value initialization. Note primitives such as (int,boolean,etc.) will not be set to null.

true | false

false

logPrefix

Specifies the prefix string that MyBatis will add to the logger names.

Any String

Not set

logImpl

Specifies which logging implementation MyBatis should use. If this setting is not present logging implementation will be autodiscovered.

SLF4J | LOG4J | LOG4J2

| JDK_LOGGING |

COMMONS_LOGGING

| STDOUT_LOGGING |

NO_LOGGING

Not set

proxyFactory

Specifies the proxy tool that MyBatis will use for creating lazy loading capable objects.

CGLIB | JAVASSIST

CGLIB

org.apache.ibatis.scripting.xmltags.XMLDynam

An example of the settings element fully configured is as follows:

<settings>

  <setting name="cacheEnabled" value="true"/>

  <setting name="lazyLoadingEnabled" value="true"/>

  <setting name="multipleResultSetsEnabled" value="true"/>

  <setting name="useColumnLabel" value="true"/>

  <setting name="useGeneratedKeys" value="false"/>

  <setting name="autoMappingBehavior" value="PARTIAL"/>

  <setting name="defaultExecutorType" value="SIMPLE"/>

  <setting name="defaultStatementTimeout" value="25"/>

  <setting name="safeRowBoundsEnabled" value="false"/>

  <setting name="mapUnderscoreToCamelCase" value="false"/>

  <setting name="localCacheScope" value="SESSION"/>

  <setting name="jdbcTypeForNull" value="OTHER"/>

  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>

</settings>

3.1.3 typeAliases

A type alias is simply a shorter name for a Java type. It's only relevant to the XML configuration and simply exists to reduce redundant typing of fully qualified classnames. For example:

<typeAliases>

  <typeAlias alias="Author" type="domain.blog.Author"/>

  <typeAlias alias="Blog" type="domain.blog.Blog"/>

  <typeAlias alias="Comment" type="domain.blog.Comment"/>

  <typeAlias alias="Post" type="domain.blog.Post"/>

  <typeAlias alias="Section" type="domain.blog.Section"/>

  <typeAlias alias="Tag" type="domain.blog.Tag"/>

</typeAliases>

With this configuration, Blog can now be used anywhere that domain.blog.Blog could be.

You can also specify a package where MyBatis will search for beans. For example:

<typeAliases>

  <package name="domain.blog"/>

</typeAliases>

Each bean found in domain.blog , if no annotation is found, will be registered as an alias using uncapitalized non-qualified class name of the bean. Thas is domain.blog.Author will be registered as author. If the @Alias annotation is found its value will be used as an alias. See the example below:

@Alias("author") public class Author {     ...

}

There are many built-in type aliases for common Java types. They are all case insensitive, note the special handling of primitives due to the overloaded names.

Alias

Mapped Type

_byte

byte

_long

long

_short

short

_int

int

_integer

int

_double

double

_float

float

_boolean

boolean

string

String

byte

Byte

long

Long

short

Short

int

Integer

integer

Integer

double

Double

float

Float

boolean

Boolean

date

Date

decimal

BigDecimal

bigdecimal

BigDecimal

object

Object

map

Map

hashmap

HashMap

list

List

arraylist

ArrayList

collection

Collection

iterator

Iterator

3.1.4 typeHandlers

Whenever MyBatis sets a parameter on a PreparedStatement or retrieves a value from a ResultSet, a TypeHandler is used to retrieve the value in a means appropriate to the Java type. The following table describes the default TypeHandlers.

Type Handler

Java Types

JDBC Types

BooleanTypeHandler

java.lang.Boolean, boolean

Any compatible BOOLEAN

ByteTypeHandler

java.lang.Byte, byte

Any compatible NUMERIC or

BYTE

ShortTypeHandler

java.lang.Short, short

Any compatible NUMERIC or

SHORT INTEGER

IntegerTypeHandler

java.lang.Integer, int

Any compatible NUMERIC or

INTEGER

LongTypeHandler

java.lang.Long, long

Any compatible NUMERIC or

LONG INTEGER

FloatTypeHandler

java.lang.Float, float

Any compatible NUMERIC or

FLOAT

DoubleTypeHandler

java.lang.Double, double

Any compatible NUMERIC or

DOUBLE

BigDecimalTypeHandler

java.math.BigDecimal

Any compatible NUMERIC or

DECIMAL

StringTypeHandler

java.lang.String

CHAR, VARCHAR

ClobTypeHandler

java.lang.String

CLOB, LONGVARCHAR

NStringTypeHandler

java.lang.String

NVARCHAR, NCHAR

NClobTypeHandler

java.lang.String

NCLOB

ByteArrayTypeHandler

byte[]

Any compatible byte stream type

BlobTypeHandler

byte[]

BLOB, LONGVARBINARY

DateTypeHandler

java.util.Date

TIMESTAMP

DateOnlyTypeHandler

java.util.Date

DATE

TimeOnlyTypeHandler

java.util.Date

TIME

SqlTimestampTypeHandler

java.sql.Timestamp

TIMESTAMP

SqlDateTypeHandler

java.sql.Date

DATE

SqlTimeTypeHandler

java.sql.Time

TIME

ObjectTypeHandler

Any

OTHER, or unspecified

EnumTypeHandler

Enumeration Type

VARCHAR any string compatible type, as the code is stored (not index).

EnumOrdinalTypeHandler

Enumeration Type

Any compatible NUMERIC or DOUBLE, as the position is stored (not the code itself).

You can override the type handlers or create your own to deal with unsupported or non-standard types. To do so, implement the interface org.apache.ibatis.type.TypeHandler or extend the convenience class org.apache.ibatis.type.BaseTypeHandler and optionally map it to a JDBC type. For example:

// ExampleTypeHandler.java @MappedJdbcTypes(JdbcType.VARCHAR) public class ExampleTypeHandler extends BaseTypeHandler<String> {   @Override

    ps.setString(i, parameter);   }   @Override

    return rs.getString(columnName);   }   @Override

    return rs.getString(columnIndex);   }   @Override

    return cs.getString(columnIndex);   }

}

  public void setNonNullParameter(PreparedStatement ps, int i, String parameter, Jdb

  public String getNullableResult(ResultSet rs, String columnName) throws SQLExcepti

  public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException

  public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLE

<!-- mybatis-config.xml -->

<typeHandlers>

  <typeHandler handler="org.mybatis.example.ExampleTypeHandler"/>

</typeHandlers>

Using such a TypeHandler would override the existing type handler for Java String properties and VARCHAR parameters and results. Note that MyBatis does not introspect upon the database metadata to determine the type, so you must specify that it’s a VARCHAR field in the parameter and result mappings to hook in the correct type handler. This is due to the fact that MyBatis is unaware of the data type until the statement is executed.

MyBatis will know the the Java type that you want to handle with this TypeHandler by introspecting its generic type, but you can override this behavior by two means:

     Adding a javaType attribute to the typeHandler element (for example: javaType="String")

     Adding a @MappedTypes annotation to your TypeHandler class specifying the list of java types to associate it with. This annotation will be ignored if the javaType attribute as also been specified.

Associated JDBC type can be specified by two means:

     Adding a jdbcType attribute to the typeHandler element (for example:

jdbcType="VARCHAR").

     Adding a @MappedJdbcTypes annotation to your TypeHandler class specifying the list of JDBC types to associate it with. This annotation will be ignored if the jdbcType attribute as also been specified.

And finally you can let MyBatis search for your TypeHandlers:

<!-- mybatis-config.xml -->

<typeHandlers>

  <package name="org.mybatis.example"/>

</typeHandlers>

Note that when using the autodiscovery feature JDBC types can only be specified with annotations.

You can create a generic TypeHandler that is able to handle more than one class. For that purpose add a constructor that receives the class as a parameter and MyBatis will pass the actual class when constructing the TypeHandler.

//GenericTypeHandler.java public class GenericTypeHandler<E extends MyObject> extends BaseTypeHandler<E> {

  private Class<E> type;

  public GenericTypeHandler(Class<E> type) {

    this.type = type;   }   ...

    if (type == null) throw new IllegalArgumentException("Type argument cannot be nu

EnumTypeHandler and EnumOrdinalTypeHandler are generic TypeHandlers. We will learn about them in the following section.

3.1.5 Handling Enums

If you want to map an Enum, you'll need to use either EnumTypeHandler or EnumOrdinalTypeHandler.

For example, let's say that we need to store the rounding mode that should be used with some number if it needs to be rounded. By default, MyBatis uses EnumTypeHandler to convert the Enum values to their names.

Note EnumTypeHandler is special in the sense that unlike other handlers, it does not handle just one specific class, but any class that extends Enum

However, we may not want to store names. Our DBA may insist on an integer code instead. That's just as easy: add EnumOrdinalTypeHandler to the typeHandlers in your config file, and now each RoundingMode will be mapped to an integer using its ordinal value.

<!-- mybatis-config.xml -->

<typeHandlers>

  <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="jav

</typeHandlers>

But what if you want to map the same Enum to a string in one place and to integer in another?

The auto-mapper will automatically use EnumOrdinalTypeHandler, so if we want to go back to using plain old ordinary EnumTypeHandler, we have to tell it, by explicitly setting the type handler to use for those SQL statements.

(Mapper files aren't covered until the next section, so if this is your first time reading through the documentation, you may want to skip this for now and come back to it later.)

> 

> 

<!DOCTYPE mapper

    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!DOCTYPE mapper

    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.apache.ibatis.submitted.rounding.Mapper">

<resultMap type="org.apache.ibatis.submitted.rounding.User" id="usermap"

<id column="id" property="id"/>

<result column="name" property="name"/>

<result column="funkyNumber" property="funkyNumber"/>

<result column="roundingMode" property="roundingMode"/>

</resultMap>

<select id="getUser" resultMap="usermap"> select * from users </select> <insert id="insert">     insert into users (id, name, funkyNumber, roundingMode) values (      #{id}, #{name}, #{funkyNumber}, #{roundingMode}

    )

</insert>

<resultMap type="org.apache.ibatis.submitted.rounding.User" id="usermap2"

<id column="id" property="id"/>

<result column="name" property="name"/>

<result column="funkyNumber" property="funkyNumber"/>

< 

</resultMap> <select id="getUser2" resultMap="usermap2"> select * from users2 </select> <insert id="insert2">     insert into users2 (id, name, funkyNumber, roundingMode) values (

    )

</insert>

</mapper>

result column="roundingMode" property="roundingMode" typeHandler="org.apache.ibatis

     #{id}, #{name}, #{funkyNumber}, #{roundingMode, typeHandler=org.apache.ibatis.t

Note that this forces us to use a resultMap instead of a resultType in our select statements.

3.1.6 objectFactory

Each time MyBatis creates a new instance of a result object, it uses an ObjectFactory instance to do so. The default ObjectFactory does little more than instantiate the target class with a default constructor, or a parameterized constructor if parameter mappings exist. If you want to override the default behaviour of the ObjectFactory, you can create your own. For example:

// ExampleObjectFactory.java public class ExampleObjectFactory extends DefaultObjectFactory {   public Object create(Class type) {     return super.create(type);   }

    return super.create(type, constructorArgTypes, constructorArgs);   }   public void setProperties(Properties properties) {     super.setProperties(properties);   }   public <T> boolean isCollection(Class<T> type) {     return Collection.class.isAssignableFrom(type);

  }}

  public Object create(Class type, List<Class> constructorArgTypes, List<Object> con

<!-- mybatis-config.xml -->

<objectFactory type="org.mybatis.example.ExampleObjectFactory">

  <property name="someProperty" value="100"/>

</objectFactory>

The ObjectFactory interface is very simple. It contains two create methods, one to deal with the default constructor, and the other to deal with parameterized constructors. Finally, the setProperties method can be used to configure the ObjectFactory. Properties defined within the body of the objectFactory element will be passed to the setProperties method after initialization of your ObjectFactory instance.

3.1.7 plugins

MyBatis allows you to intercept calls to at certain points within the execution of a mapped statement. By default, MyBatis allows plug-ins to intercept method calls of:

     Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)

     ParameterHandler (getParameterObject, setParameters)

     ResultSetHandler (handleResultSets, handleOutputParameters)

     StatementHandler (prepare, parameterize, batch, update, query)

The details of these classes methods can be discovered by looking at the full method signature of each, and the source code which is available with each MyBatis release. You should understand the behaviour of the method you’re overriding, assuming you’re doing something more than just monitoring calls. If you attempt to modify or override the behaviour of a given method, you’re likely to break the core of MyBatis. These are low level classes and methods, so use plug-ins with caution.

Using plug-ins is pretty simple given the power they provide. Simply implement the Interceptor interface, being sure to specify the signatures you want to intercept.

// ExamplePlugin.java @Intercepts({@Signature(   type= Executor.class,   method = "update",   args = {MappedStatement.class,Object.class})}) public class ExamplePlugin implements Interceptor {   public Object intercept(Invocation invocation) throws Throwable {     return invocation.proceed();   }   public Object plugin(Object target) {     return Plugin.wrap(target, this);   }   public void setProperties(Properties properties) {   }

}

<!-- mybatis-config.xml -->

<plugins>

  <plugin interceptor="org.mybatis.example.ExamplePlugin">

    <property name="someProperty" value="100"/>

  </plugin>

</plugins>

The plug-in above will intercept all calls to the "update" method on the Executor instance, which is an internal object responsible for the low level execution of mapped statements.

NOTE Overriding the Configuration Class

In addition to modifying core MyBatis behaviour with plugins, you can also override the

Configuration class entirely. Simply extend it and override any methods inside, and pass it into the call to the sqlSessionFactoryBuilder.build(myConfig) method. Again though, this could have a severe impact on the behaviour of MyBatis, so use caution.

3.1.8 environments

MyBatis can be configured with multiple environments. This helps you to apply your SQL Maps to multiple databases for any number of reasons. For example, you might have a different configuration for your Development, Test and Production environments. Or, you may have multiple production databases that share the same schema, and you’d like to use the same SQL maps for both. There are many use cases.

One important thing to remember though: While you can configure multiple environments, you can only choose ONE per SqlSessionFactory instance.

So if you want to connect to two databases, you need to create two instances of SqlSessionFactory, one for each. For three databases, you’d need three instances, and so on. It’s really easy to remember:

One SqlSessionFactory instance per database

To specify which environment to build, you simply pass it to the SqlSessionFactoryBuilder as an optional parameter. The two signatures that accept the environment are:

SqlSessionFactory factory = sqlSessionFactoryBuilder.build(reader, environment);

SqlSessionFactory factory = sqlSessionFactoryBuilder.build(reader, environment,prope

If the environment is omitted, then the default environment is loaded, as follows:

SqlSessionFactory factory = sqlSessionFactoryBuilder.build(reader);

SqlSessionFactory factory = sqlSessionFactoryBuilder.build(reader,properties);

The environments element defines how the environment is configured.

<environments default="development">

  <environment id="development">

    <transactionManager type="JDBC">

      <property name="..." value="..."/>

    </transactionManager>

    <dataSource type="POOLED">

      <property name="driver" value="${driver}"/>

      <property name="url" value="${url}"/>

      <property name="username" value="${username}"/>

      <property name="password" value="${password}"/>

    </dataSource>

  </environment>

</environments>

Notice the key sections here:

     The default Environment ID (e.g. default="development").

     The Environment ID for each environment defined (e.g. id="development").

     The TransactionManager configuration (e.g. type="JDBC")

     The DataSource configuration (e.g. type="POOLED")

The default environment and the environment IDs are self explanatory. Name them whatever you like, just make sure the default matches one of them.

transactionManager

There are two TransactionManager types (i.e. type="[JDBC|MANAGED]") that are included with MyBatis:

     JDBC – This configuration simply makes use of the JDBC commit and rollback facilities directly. It relies on the connection retrieved from the dataSource to manage the scope of the transaction.

     MANAGED – This configuration simply does almost nothing. It never commits, or rolls back a connection. Instead, it lets the container manage the full lifecycle of the transaction (e.g. a JEE Application Server context). By default it does close the connection. However, some containers don’t expect this, and thus if you need to stop it from closing the connection, set the "closeConnection" property to false. For example:

<transactionManager type="MANAGED">

  <property name="closeConnection" value="false"/>

</transactionManager>

NOTE If you are planning to use MyBatis with Spring there is no need to configure any

TransactionManager because the Spring module will set its own one overriding any previously set configuration.

Neither of these TransactionManager types require any properties. However, they are both Type Aliases, so in other words, instead of using them, you could put your own fully qualified class name or Type Alias that refers to your own implementation of the TransactionFactory interface.

public interface TransactionFactory {   void setProperties(Properties props);   Transaction newTransaction(Connection conn);

  Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level,

}

Any properties configured in the XML will be passed to the setProperties() method after instantiation. Your implementation would also need to create a Transaction implementation, which is also a very simple interface:

public interface Transaction {   Connection getConnection() throws SQLException;   void commit() throws SQLException;   void rollback() throws SQLException;   void close() throws SQLException; }

Using these two interfaces, you can completely customize how MyBatis deals with Transactions. dataSource

The dataSource element configures the source of JDBC Connection objects using the standard JDBC DataSource interface.

     Most MyBatis applications will configure a dataSource as in the example. However, it’s not required. Realize though, that to facilitate Lazy Loading, this dataSource is required.

There are three build-in dataSource types (i.e. type="????"):

UNPOOLED – This implementation of DataSource simply opens and closes a connection each time it is requested. While it’s a bit slower, this is a good choice for simple applications that do not require the performance of immediately available connections. Different databases are also different in this performance area, so for some it may be less important to pool and this configuration will be ideal. The UNPOOLED DataSource is configured with only five properties:

     driver – This is the fully qualified Java class of the JDBC driver (NOT of the DataSource class if your driver includes one).

     url – This is the JDBC URL for your database instance.

     username – The database username to log in with.

     password - The database password to log in with.

     defaultTransactionIsolationLevel – The default transaction isolation level for connections.

Optionally, you can pass properties to the database driver as well. To do this, prefix the properties with driver., for example:

     driver.encoding=UTF8

This will pass the property encoding, with the value UTF8, to your database driver via the DriverManager.getConnection(url, driverProperties) method.

POOLED – This implementation of DataSource pools JDBC Connection objects to avoid the initial connection and authentication time required to create a new Connection instance. This is a popular approach for concurrent web applications to achieve the fastest response.

In addition to the (UNPOOLED) properties above, there are many more properties that can be used to configure the POOLED datasource:

     poolMaximumActiveConnections – This is the number of active (i.e. in use) connections that can exist at any given time. Default: 10

     poolMaximumIdleConnections – The number of idle connections that can exist at any given time.

     poolMaximumCheckoutTime – This is the amount of time that a Connection can be "checked out" of the pool before it will be forcefully returned. Default: 20000ms (i.e. 20 seconds)

     poolTimeToWait – This is a low level setting that gives the pool a chance to print a log status and re-attempt the acquisition of a connection in the case that it’s taking unusually long (to avoid failing silently forever if the pool is misconfigured). Default: 20000ms (i.e. 20 seconds)

     poolPingQuery – The Ping Query is sent to the database to validate that a connection is in good working order and is ready to accept requests. The default is "NO PING QUERY SET", which will cause most database drivers to fail with a decent error message.

     poolPingEnabled – This enables or disables the ping query. If enabled, you must also set the poolPingQuery property with a valid SQL statement (preferably a very fast one). Default: false.

     poolPingConnectionsNotUsedFor – This configures how often the poolPingQuery will be used. This can be set to match the typical timeout for a database connection, to avoid unnecessary pings. Default: 0 (i.e. all connections are pinged every time – but only if poolPingEnabled is true of course).

JNDI – This implementation of DataSource is intended for use with containers such as EJB or Application Servers that may configure the DataSource centrally or externally and place a reference to it in a JNDI context. This DataSource configuration only requires two properties:

     initial_context – This property is used for the Context lookup from the InitialContext (i.e. initialContext.lookup(initial_context)). This property is optional, and if omitted, then the data_source property will be looked up against the InitialContext directly.

     data_source – This is the context path where the reference to the instance of the DataSource can be found. It will be looked up against the context returned by the initial_context lookup, or against the InitialContext directly if no initial_context is supplied.

Similar to the other DataSource configurations, it’s possible to send properties directly to the InitialContext by prefixing those properties with env., for example:

     env.encoding=UTF8

This would send the property encoding with the value of UTF8 to the constructor of the InitialContext upon instantiation.

You can plug any 3rd party DataSource by implementing the interface org.apache.ibatis.datasource.DataSourceFactory:

public interface DataSourceFactory {   void setProperties(Properties props);   DataSource getDataSource();

}

org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory can be used as super class to build new datasource adapters. For example this is the code needed to plug C3P0:

import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory; import com.mchange.v2.c3p0.ComboPooledDataSource;         public class C3P0DataSourceFactory extends UnpooledDataSourceFactory {

  public C3P0DataSourceFactory() {     this.dataSource = new ComboPooledDataSource();   }

}

To set it up, add a property for each setter method you want MyBatis to call. Follows below a sample configuration which connects to a PostgreSQL database:

<dataSource type="org.myproject.C3P0DataSourceFactory">

  <property name="driver" value="org.postgresql.Driver"/>

  <property name="url" value="jdbc:postgresql:mydb"/>

  <property name="username" value="postgres"/>

  <property name="password" value="root"/>

</dataSource>

3.1.9 databaseIdProvider

MyBatis is able to execute different statements depending on your database vendor. The multi-db vendor support is based on the mapped statements databaseId attribute. MyBatis will load all statements with no databaseId attribute or with a databaseId that matches the current one. If case the same statement if found with and without the databaseId the latter will be discarded. To enable the multi vendor support add a databaseIdProvider to mybatis-config.xml file as follows:

<databaseIdProvider type="DB_VENDOR" />

The DB_VENDOR implementation databaseIdProvider sets as databaseId the String returned by

DatabaseMetaData#getDatabaseProductName(). Given that usually that string is too long and that different versions of the same product may return different values, you may want to convert it to a shorter one by adding properties like follows:

<databaseIdProvider type="DB_VENDOR">

  <property name="SQL Server" value="sqlserver"/>

  <property name="DB2" value="db2"/>

  <property name="Oracle" value="oracle" />

</databaseIdProvider>

When properties are provided, the DB_VENDOR databaseIdProvider will search the property value corresponding to the first key found in the returned database product name or "null" if there is not a matching property. In this case, if getDatabaseProductName() returns "Oracle (DataDirect)" the databaseId will be set to "oracle".

You can build your own DatabaseIdProvider by implementing the interface org.apache.ibatis.mapping.DatabaseIdProvider and registering it in mybatis-config.xml:

public interface DatabaseIdProvider {   void setProperties(Properties p);   String getDatabaseId(DataSource dataSource) throws SQLException;

}

3.1.10 mappers

Now that the behavior of MyBatis is configured with the above configuration elements, we’re ready to define our mapped SQL statements. But first, we need to tell MyBatis where to find them. Java doesn’t really provide any good means of auto-discovery in this regard, so the best way to do it is to simply tell MyBatis where to find the mapping files. You can use class path relative resource references, fully qualified url references (including file:/// URLs), class names or package names. For example:

<!-- Using classpath relative resources -->

<mappers>

  <mapper resource="org/mybatis/builder/AuthorMapper.xml"/>

  <mapper resource="org/mybatis/builder/BlogMapper.xml"/>

  <mapper resource="org/mybatis/builder/PostMapper.xml"/>

</mappers>

<!-- Using url fully qualified paths -->

<mappers>

  <mapper url="file:///var/mappers/AuthorMapper.xml"/>

  <mapper url="file:///var/mappers/BlogMapper.xml"/>

  <mapper url="file:///var/mappers/PostMapper.xml"/>

</mappers>

<!-- Using mapper interface classes -->

<mappers>

  <mapper class="org.mybatis.builder.AuthorMapper"/>

  <mapper class="org.mybatis.builder.BlogMapper"/>

  <mapper class="org.mybatis.builder.PostMapper"/>

</mappers>

<!-- Register all interfaces in a package as mappers -->

<mappers>

  <package name="org.mybatis.builder"/>

</mappers>

These statement simply tell MyBatis where to go from here. The rest of the details are in each of the SQL Mapping files, and that’s exactly what the next section will discuss.


4 Mapper XML Files

.......................................................................................................................................

4.1 Mapper XML Files

The true power of MyBatis is in the Mapped Statements. This is where the magic happens. For all of their power, the Mapper XML files are relatively simple. Certainly if you were to compare them to the equivalent JDBC code, you would immediately see a savings of 95% of the code. MyBatis was built to focus on the SQL, and does its best to stay out of your way.

The Mapper XML files have only a few first class elements (in the order that they should be defined):

     cache – Configuration of the cache for a given namespace.

     cache-ref – Reference to a cache configuration from another namespace.

     resultMap – The most complicated and powerful element that describes how to load your objects from the database result sets.

     parameterMap – Deprecated! Old-school way to map parameters. Inline parameters are preferred and this element may be removed in the future. Not documented here.

     sql – A reusable chunk of SQL that can be referenced by other statements.

     insert – A mapped INSERT statement.

     update – A mapped UPDATE statement.

     delete – A mapped DELETE statement.

     select – A mapped SELECT statement.

The next sections will describe each of these elements in detail, starting with the statements themselves.

4.1.1 select

The select statement is one of the most popular elements that you'll use in MyBatis. Putting data in a database isn't terribly valuable until you get it back out, so most applications query far more than they modify the data. For every insert, update or delete, there is probably many selects. This is one of the founding principles of MyBatis, and is the reason so much focus and effort was placed on querying and result mapping. The select element is quite simple for simple cases. For example:

<select id="selectPerson" parameterType="int" resultType="hashmap">

  SELECT * FROM PERSON WHERE ID = #{id}

</select>

This statement is called selectPerson, takes a parameter of type int (or Integer), and returns a HashMap keyed by column names mapped to row values.

Notice the parameter notation:

#{id}

This tells MyBatis to create a PreparedStatement parameter. With JDBC, such a parameter would be identified by a "?" in SQL passed to a new PreparedStatement, something like this:

// Similar JDBC code, NOT MyBatis…

String selectPerson = "SELECT * FROM PERSON WHERE ID=?"; PreparedStatement ps = conn.prepareStatement(selectPerson); ps.setInt(1,id);

Of course, there's a lot more code required by JDBC alone to extract the results and map them to an instance of an object, which is what MyBatis saves you from having to do. There's a lot more to know about parameter and result mapping. Those details warrant their own section, which follows later in this section.

The select element has more attributes that allow you to configure the details of how each statement should behave.

<select   id="selectPerson"   parameterType="int"   parameterMap="deprecated"   resultType="hashmap"   resultMap="personResultMap"   flushCache="false"   useCache="true"   timeout="10000"   fetchSize="256"   statementType="PREPARED"   resultSetType="FORWARD_ONLY">

Attribute

Description

id

A unique identifier in this namespace that can be used to reference this statement.

parameterType

The fully qualified class name or alias for the parameter that will be passed into this statement. This attribute is optional because MyBatis can calculate the TypeHandler to use out of the actual parameter passed to the statement. Default is unset.

parameterMap

This is a deprecated approach to referencing an external parameterMap. Use inline parameter mappings and the parameterType attribute.

resultType

The fully qualified class name or alias for the expected type that will be returned from this statement. Note that in the case of collections, this should be the type that the collection contains, not the type of the collection itself. Use resultType OR resultMap, not both.

resultMap

A named reference to an external resultMap. Result maps are the most powerful feature of MyBatis, and with a good understanding of them, many difficult mapping cases can be solved. Use resultMap OR resultType, not both.

flushCache

Setting this to true will cause the local and 2nd level caches to be flushed whenever this statement is called. Default: false for select statements.

useCache

Setting this to true will cause the results of this statement to be cached in 2nd level cache. Default: true for select statements.

timeout

This sets the number of seconds the driver will wait for the database to return from a request, before throwing an exception. Default is unset (driver dependent).

fetchSize

This is a driver hint that will attempt to cause the driver to return results in batches of rows numbering in size equal to this setting. Default is unset (driver dependent).

statementType

Any one of STATEMENT, PREPARED or CALLABLE.

This causes MyBatis to use Statement,

PreparedStatement or CallableStatement respectively. Default: PREPARED.

resultSetType

Any one of FORWARD_ONLY|

SCROLL_SENSITIVE| SCROLL_INSENSITIVE.

Default is unset (driver dependent).

databaseId

In case there is a configured databaseIdProvider, MyBatis will load all statements with no databaseId attribute or with a databaseId that matches the current one. If case the same statement if found with and without the databaseId the latter will be discarded.

resultOrdered

This is only applicable for nested result select statements: If this is true, it is assumed that nested results are contained or grouped together such that when a new main result row is returned, no references to a previous result row will occur anymore. This allows nested results to be filled much more memory friendly. Default: false.

resultSets

This is only applicable for multiple result sets. It lists the result sets that will be returned by the statement and gives a name to each one. Names are separated by commas.

Select Attributes

4.1.2 insert, update and delete

The data modification statements insert, update and delete are very similar in their implementation:

<insert   id="insertAuthor"   parameterType="domain.blog.Author"   flushCache="true"   statementType="PREPARED"   keyProperty=""   keyColumn=""   useGeneratedKeys=""   timeout="20">

<update   id="insertAuthor"   parameterType="domain.blog.Author"   flushCache="true"   statementType="PREPARED"   timeout="20">

<delete   id="insertAuthor"   parameterType="domain.blog.Author"   flushCache="true"   statementType="PREPARED"   timeout="20">

Attribute

Description

id

A unique identifier in this namespace that can be used to reference this statement.

parameterType

The fully qualified class name or alias for the parameter that will be passed into this statement. This attribute is optional because MyBatis can calculate the TypeHandler to use out of the actual parameter passed to the statement. Default is unset.

parameterMap

This is a deprecated approach to referencing an external parameterMap. Use inline parameter mappings and the parameterType attribute.

flushCache

Setting this to true will cause the 2nd level and local caches to be flushed whenever this statement is called. Default: true for insert, update and delete statements.

timeout

This sets the maximum number of seconds the driver will wait for the database to return from a request, before throwing an exception. Default is unset (driver dependent).

statementType

Any one of STATEMENT, PREPARED or CALLABLE.

This causes MyBatis to use Statement,

PreparedStatement or CallableStatement respectively. Default: PREPARED.

useGeneratedKeys

(insert and update only) This tells MyBatis to use the

JDBC getGeneratedKeys method to retrieve keys generated internally by the database (e.g.

auto increment fields in RDBMS like MySQL or SQL Server). Default: false

keyProperty

(insert and update only) Identifies a property into which MyBatis will set the key value returned by

getGeneratedKeys, or by a selectKey child element of the insert statement. Default: unset. Can be a comma separated list of property names if multiple generated columns are expected.

keyColumn

(insert and update only) Sets the name of the column in the table with a generated key. This is only required in certain databases (like PostgreSQL) when the key column is not the first column in the table. Can be a comma separated list of columns names if multiple generated columns are expected.

databaseId

In case there is a configured databaseIdProvider, MyBatis will load all statements with no databaseId attribute or with a databaseId that matches the current one. If case the same statement if found with and without the databaseId the latter will be discarded.

Insert, Update and Delete Attributes

The following are some examples of insert, update and delete statements.

<insert id="insertAuthor">   insert into Author (id,username,password,email,bio)   values (#{id},#{username},#{password},#{email},#{bio}) </insert>

<update id="updateAuthor">   update Author set     username = #{username},     password = #{password},     email = #{email},     bio = #{bio}   where id = #{id} </update>

<delete id="deleteAuthor">   delete from Author where id = #{id}

</delete>

As mentioned, insert is a little bit more rich in that it has a few extra attributes and sub-elements that allow it to deal with key generation in a number of ways.

First, if your database supports auto-generated key fields (e.g. MySQL and SQL Server), then you can simply set useGeneratedKeys="true" and set the keyProperty to the target property and you're done. For example, if the Author table above had used an auto-generated column type for the id, the statement would be modified as follows:

<insert id="insertAuthor" useGeneratedKeys="true"     keyProperty="id">   insert into Author (username,password,email,bio)   values (#{username},#{password},#{email},#{bio}) </insert>

MyBatis has another way to deal with key generation for databases that don't support auto-generated column types, or perhaps don't yet support the JDBC driver support for auto-generated keys.

Here's a simple (silly) example that would generate a random ID (something you'd likely never do, but this demonstrates the flexibility and how MyBatis really doesn't mind):

<insert id="insertAuthor">   <selectKey keyProperty="id" resultType="int" order="BEFORE">     select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1   </selectKey>   insert into Author     (id, username, password, email,bio, favourite_section)   values     (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=

</insert>

In the example above, the selectKey statement would be run first, the Author id property would be set, and then the insert statement would be called. This gives you a similar behavior to an autogenerated key in your database without complicating your Java code.

The selectKey element is described as follows:

<selectKey   keyProperty="id"   resultType="int"   order="BEFORE"   statementType="PREPARED">

Attribute

Description

keyProperty

The target property where the result of the selectKey statement should be set. Can be a comma separated list of property names if multiple generated columns are expected.

keyColumn

The column name(s) in the returned result set that match the properties. Can be a comma separated list of column names if multiple generated columns are expected.

resultType

The type of the result. MyBatis can usually figure this out, but it doesn't hurt to add it to be sure. MyBatis allows any simple type to be used as the key, including Strings. If you are expecting multiple generated columns, then you can use an Object that contains the expected properties, or a Map.

order

This can be set to BEFORE or AFTER. If set to BEFORE, then it will select the key first, set the keyProperty and then execute the insert statement. If set to AFTER, it runs the insert statement and then the selectKey statement – which is common with databases like Oracle that may have embedded sequence calls inside of insert statements.

statementType

Same as above, MyBatis supports STATEMENT, PREPARED and CALLABLE statement types that map to Statement, PreparedStatement and CallableStatement respectively.

selectKey Attributes

4.1.3 sql

This element can be used to define a reusable fragment of SQL code that can be included in other statements. For example:

<sql id="userColumns"> id,username,password </sql>

The SQL fragment can then be included in another statement, for example:

<select id="selectUsers" resultType="map">   select <include refid="userColumns"/>   from some_table   where id = #{id} </select>

4.1.4 Parameters

In all of the past statements, you've seen examples of simple parameters. Parameters are very powerful elements in MyBatis. For simple situations, probably 90% of the cases, there's not much too them, for example:

<select id="selectUsers" resultType="User">   select id, username, password   from users   where id = #{id} </select>

The example above demonstrates a very simple named parameter mapping. The parameterType is set to int, so therefore the parameter could be named anything. Primitive or simply data types such as Integer and String have no relevant properties, and thus will replace the full value of the parameter entirely. However, if you pass in a complex object, then the behavior is a little different. For example:

<insert id="insertUser" parameterType="User">   insert into users (id, username, password)   values (#{id}, #{username}, #{password})

</insert>

If a parameter object of type User was passed into that statement, the id, username and password property would be looked up and their values passed to a PreparedStatement parameter.

That's nice and simple for passing parameters into statements. But there are a lot of other features of parameter maps.

First, like other parts of MyBatis, parameters can specify a more specific data type.

#{property,javaType=int,jdbcType=NUMERIC}

Like the rest of MyBatis, the javaType can almost always be determined from the parameter object, unless that object is a HashMap. Then the javaType should be specified to ensure the correct TypeHandler is used.

NOTE The JDBC Type is required by JDBC for all nullable columns, if null is passed as a value. You can investigate this yourself by reading the JavaDocs for the PreparedStatement.setNull() method.

To further customize type handling, you can also specify a specific TypeHandler class (or alias), for example:

#{age,javaType=int,jdbcType=NUMERIC,typeHandler=MyTypeHandler}

So already it seems to be getting verbose, but the truth is that you'll rarely set any of these.

For numeric types there's also a numericScale for determining how many decimal places are relevant.

#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}

Finally, the mode attribute allows you to specify IN, OUT or INOUT parameters. If a parameter is OUT or INOUT, the actual value of the parameter object property will be changed, just as you would expect if you were calling for an output parameter. If the mode=OUT (or INOUT) and the jdbcType=CURSOR (i.e. Oracle REFCURSOR), you must specify a resultMap to map the ResultSet to the type of the parameter. Note that the javaType attribute is optional here, it will be automatically set to ResultSet if left blank with a CURSOR as the jdbcType.

#{department, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=departmentRes

MyBatis also supports more advanced data types such as structs, but you must tell the statement the type name when registering the out parameter. For example (again, don't break lines like this in practice):

#{middleInitial, mode=OUT, jdbcType=STRUCT, jdbcTypeName=MY_TYPE, resultMap=departme

Despite all of these powerful options, most of the time you'll simply specify the property name, and MyBatis will figure out the rest. At most, you'll specify the jdbcType for nullable columns.

#{firstName}

#{middleInitial,jdbcType=VARCHAR}

#{lastName}

4.1.4.1 String Substitution

By default, using the #{} syntax will cause MyBatis to generate PreparedStatement properties and set the values safely against the PreparedStatement parameters (e.g. ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject a string unmodified into the SQL Statement. For example, for ORDER BY, you might use something like this:

ORDER BY ${columnName}

Here MyBatis won't modify or escape the string.

NOTE It's not safe to accept input from a user and supply it to a statement unmodified in this way. This leads to potential SQL Injection attacks and therefore you should either disallow user input in these fields, or always perform your own escapes and checks.

4.1.5 Result Maps

The resultMap element is the most important and powerful element in MyBatis. It's what allows you to do away with 90% of the code that JDBC requires to retrieve data from ResultSets, and in some cases allows you to do things that JDBC does not even support. In fact, to write the equivalent code for something like a join mapping for a complex statement could probably span thousands of lines of code. The design of the ResultMaps is such that simple statements don't require explicit result mappings at all, and more complex statements require no more than is absolutely necessary to describe the relationships.

You've already seen examples of simple mapped statements that don't have an explicit resultMap. For example:

<select id="selectUsers" resultType="map">   select id, username, hashedPassword   from some_table   where id = #{id} </select>

Such a statement simply results in all columns being automatically mapped to the keys of a HashMap, as specified by the resultType attribute. While useful in many cases, a HashMap doesn't make a very good domain model. It's more likely that your application will use JavaBeans or POJOs (Plain

Old Java Objects) for the domain model. MyBatis supports both. Consider the following JavaBean:

package com.someapp.model; public class User {   private int id;   private String username;   private String hashedPassword;     public int getId() {     return id;   }   public void setId(int id) {     this.id = id;   }   public String getUsername() {     return username;   }   public void setUsername(String username) {     this.username = username;   }   public String getHashedPassword() {     return hashedPassword;   }   public void setHashedPassword(String hashedPassword) {     this.hashedPassword = hashedPassword;   }

}

Based on the JavaBeans specification, the above class has 3 properties: id, username, and hashedPassword. These match up exactly with the column names in the select statement.

Such a JavaBean could be mapped to a ResultSet just as easily as the HashMap.

<select id="selectUsers" resultType="com.someapp.model.User">   select id, username, hashedPassword   from some_table   where id = #{id} </select>

And remember that TypeAliases are your friend. Use them so that you don't have to keep typing the fully qualified path of your class out. For example:

<!-- In Config XML file -->

<typeAlias type="com.someapp.model.User" alias="User"/>

<!-- In SQL Mapping XML file --> <select id="selectUsers" resultType="User">   select id, username, hashedPassword   from some_table   where id = #{id}

</select>

In these cases MyBatis is automatically creating a ResultMap behind the scenes to auto-map the columns to the JavaBean properties based on name. If the column names did not match exactly, you could employ select clause aliases (a standard SQL feature) on the column names to make the labels match. For example:

<select id="selectUsers" resultType="User">   select     user_id             as "id",     user_name           as "userName",     hashed_password     as "hashedPassword"   from some_table   where id = #{id} </select>

The great thing about ResultMaps is that you've already learned a lot about them, but you haven't even seen one yet! These simple cases don't require any more than you've seen here. Just for example sake, let's see what this last example would look like as an external resultMap, as that is another way to solve column name mismatches.

<resultMap id="userResultMap" type="User">

  <id property="id" column="user_id" />

  <result property="username" column="user_name"/>

  <result property="password" column="hashed_password"/>

</resultMap>

And the statement that references it uses the resultMap attribute to do so (notice we removed the resultType attribute). For example:

<select id="selectUsers" resultMap="userResultMap">   select user_id, user_name, hashed_password   from some_table   where id = #{id} </select>

Now if only the world were always that simple.

4.1.5.1 Advanced Result Maps

MyBatis was created with one idea in mind: Databases aren't always what you want or need them to be. While we'd love every database to be perfect 3rd normal form or BCNF, they aren't. And it would be great if it was possible to have a single database map perfectly to all of the applications that use it, it's not. Result Maps are the answer that MyBatis provides to this problem.

For example, how would we map this statement?

<!-- Very Complex Statement --> <select id="selectBlogDetails" resultMap="detailedBlogResultMap">   select        B.id as blog_id,

       B.title as blog_title,

       B.author_id as blog_author_id,

       A.id as author_id,

       A.username as author_username,

       A.password as author_password,

       A.email as author_email,

       A.bio as author_bio,

       A.favourite_section as author_favourite_section,

       P.id as post_id,

       P.blog_id as post_blog_id,

       P.author_id as post_author_id,

       P.created_on as post_created_on,

       P.section as post_section,

       P.subject as post_subject,

       P.draft as draft,

       P.body as post_body,

       C.id as comment_id,

       C.post_id as comment_post_id,

       C.name as comment_name,

       C.comment as comment_text,

       T.id as tag_id,

       T.name as tag_name   from Blog B        left outer join Author A on B.author_id = A.id        left outer join Post P on B.id = P.blog_id        left outer join Comment C on P.id = C.post_id        left outer join Post_Tag PT on PT.post_id = P.id        left outer join Tag T on PT.tag_id = T.id   where B.id = #{id}

</select>

You'd probably want to map it to an intelligent object model consisting of a Blog that was written by an Author, and has many Posts, each of which may have zero or many Comments and Tags. The following is a complete example of a complex ResultMap (assume Author, Blog, Post, Comments and Tags are all type aliases). Have a look at it, but don't worry, we're going to go through each step. While it may look daunting at first, it's actually very simple.

<!-- Very Complex Result Map -->

<resultMap id="detailedBlogResultMap" type="Blog">

  <constructor>

    <idArg column="blog_id" javaType="int"/>

  </constructor>

  <result property="title" column="blog_title"/>

  <association property="author" javaType="Author">

    <id property="id" column="author_id"/>

    <result property="username" column="author_username"/>

    <result property="password" column="author_password"/>

    <result property="email" column="author_email"/>

    <result property="bio" column="author_bio"/>

    <result property="favouriteSection" column="author_favourite_section"/>

  </association>

  <collection property="posts" ofType="Post">

    <id property="id" column="post_id"/>

    <result property="subject" column="post_subject"/>

    <association property="author" javaType="Author"/>

    <collection property="comments" ofType="Comment">

      <id property="id" column="comment_id"/>

    </collection>

    <collection property="tags" ofType="Tag" >

      <id property="id" column="tag_id"/>

    </collection>

    <discriminator javaType="int" column="draft">

      <case value="1" resultType="DraftPost"/>

    </discriminator>

  </collection>

</resultMap>

The resultMap element has a number of sub-elements and a structure worthy of some discussion. The following is a conceptual view of the resultMap element.

4.1.5.2 resultMap

     constructor - used for injecting results into the constructor of a class upon instantiation

     idArg - ID argument; flagging results as ID will help improve overall performance

     arg - a normal result injected into the constructor

     id – an ID result; flagging results as ID will help improve overall performance

     result – a normal result injected into a field or JavaBean property

     association – a complex type association; many results will roll up into this type

     nested result mappings – associations are resultMaps themselves, or can refer to one

     collection – a collection of complex types

     nested result mappings – collections are resultMaps themselves, or can refer to one

     discriminator – uses a result value to determine which resultMap to use

     case – a case is a result map based on some value

     nested result mappings – a case is also a result map itself, and thus can contain many of these same elements, or it can refer to an external resultMap.

Attribute

Description

id

A unique identifier in this namespace that can be used to reference this result map.

type

A fully qualified Java class name, or a type alias (see the table above for the list of built-in type aliases).

autoMapping

If present, MyBatis will enable or disable the automapping for this ResultMap. This attribute overrides the global autoMappingBehavior. Default: unset.

ResultMap Attributes

Best Practice Always build ResultMaps incrementally. Unit tests really help out here. If you try to build a gigantic resultMap like the one above all at once, it's likely you'll get it wrong and it will be hard to work with. Start simple, and evolve it a step at a time. And unit test! The downside to using frameworks is that they are sometimes a bit of a black box (open source or not). Your best bet to ensure that you're achieving the behaviour that you intend, is to write unit tests. It also helps to have them when submitting bugs.

The next sections will walk through each of the elements in more detail.

4.1.5.3 id & result

<id property="id" column="post_id"/>

<result property="subject" column="post_subject"/>

These are the most basic of result mappings. Both id, and result map a single column value to a single property or field of a simple data type (String, int, double, Date, etc.).

The only difference between the two is that id will flag the result as an identifier property to be used when comparing object instances. This helps to improve general performance, but especially performance of caching and nested result mapping (i.e. join mapping).

Each has a number of attributes:

Attribute

Description

property

The field or property to map the column result to. If a matching JavaBeans property exists for the given name, then that will be used. Otherwise, MyBatis will look for a field of the given name. In both cases you can use complex property navigation using the usual dot notation. For example, you can map to something simple like: username, or to something more complicated like: address.street.number.

column

The column name from the database, or the aliased column label. This is the same string that would normally be passed to resultSet.getString(columnName).

javaType

A fully qualified Java class name, or a type alias (see the table above for the list of built-in type aliases). MyBatis can usually figure out the type if you're mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behaviour.

jdbcType

The JDBC Type from the list of supported types that follows this table. The JDBC type is only required for nullable columns upon insert, update or delete. This is a JDBC requirement, not a MyBatis one. So even if you were coding JDBC directly, you'd need to specify this type – but only for nullable values.

typeHandler

We discussed default type handlers previously in this documentation. Using this property you can override the default type handler on a mapping-by-mapping basis. The value is either a fully qualified class name of a TypeHandler implementation, or a type alias.

Id and Result Attributes

4.1.5.4 Supported JDBC Types

For future reference, MyBatis supports the following JDBC Types via the included JdbcType enumeration.

BIT

FLOAT

CHAR

TIMESTAMP    OTHER

UNDEFINED

TINYINT

REAL

VARCHAR

BINARY       BLOG

NVARCHAR

SMALLINT

DOUBLE

LONGVARCHAR

VARBINARY    CLOB

NCHAR

INTEGER

NUMERIC

DATE

LONGVARBINARYBOOLEAN

NCLOB

BIGINT

DECIMAL

TIME

NULL         CURSOR

ARRAY

4.1.5.5 constructor

<constructor>

   <idArg column="id" javaType="int"/>

   <arg column="username" javaType="String"/>

</constructor>

While properties will work for most Data Transfer Object (DTO) type classes, and likely most of your domain model, there may be some cases where you want to use immutable classes. Often tables that contain reference or lookup data that rarely or never changes is suited to immutable classes. Constructor injection allows you to set values on a class upon instantiation, without exposing public methods. MyBatis also supports private properties and private JavaBeans properties to achieve this, but some people prefer Constructor injection. The constructor element enables this.

Consider the following constructor:

public class User {    //...

   public User(int id, String username) {      //...

  } //...

}

In order to inject the results into the constructor, MyBatis needs to identify the constructor by the type of its parameters. Java has no way to introspect (or reflect) on parameter names. So when creating a constructor element, ensure that the arguments are in order, and that the data types are specified.

<constructor>

   <idArg column="id" javaType="int"/>

   <arg column="username" javaType="String"/>

</constructor>

The rest of the attributes and rules are the same as for the regular id and result elements.

Attribute

Description

column

The column name from the database, or the aliased column label. This is the same string that would normally be passed to resultSet.getString(columnName).

javaType

A fully qualified Java class name, or a type alias (see the table above for the list of built-in type aliases). MyBatis can usually figure out the type if you're mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behaviour.

jdbcType

The JDBC Type from the list of supported types that follows this table. The JDBC type is only required for nullable columns upon insert, update or delete. This is a JDBC requirement, not an MyBatis one. So even if you were coding JDBC directly, you'd need to specify this type – but only for nullable values.

typeHandler

We discussed default type handlers previously in this documentation. Using this property you can override the default type handler on a mapping-by-mapping basis. The value is either a fully qualified class name of a TypeHandler implementation, or a type alias.

select

The ID of another mapped statement that will load the complex type required by this property mapping. The values retrieved from columns specified in the column attribute will be passed to the target select statement as parameters. See the Association element for more.

resultMap

This is the ID of a ResultMap that can map the nested results of this argument into an appropriate object graph. This is an alternative to using a call to another select statement. It allows you to join multiple tables together into a single ResultSet. Such a ResultSet will contain duplicated, repeating groups of data that needs to be decomposed and mapped properly to a nested object graph. To facilitate this, MyBatis lets you "chain" result maps together, to deal with the nested results. See the Association element below for more.

4.1.5.6 association

<association property="author" javaType="Author">

  <id property="id" column="author_id"/>

  <result property="username" column="author_username"/>

</association>

The association element deals with a "has-one" type relationship. For example, in our example, a Blog has one Author. An association mapping works mostly like any other result. You specify the target property, the javaType of the property (which MyBatis can figure out most of the time), the jdbcType if necessary and a typeHandler if you want to override the retrieval of the result values.

Where the association differs is that you need to tell MyBatis how to load the association. MyBatis can do so in two different ways:

     Nested Select: By executing another mapped SQL statement that returns the complex type desired.

     Nested Results: By using nested result mappings to deal with repeating subsets of joined results.

First, let's examine the properties of the element. As you'll see, it differs from a normal result mapping only by the select and resultMap attributes.

Attribute

Description

property

The field or property to map the column result to. If a matching JavaBeans property exists for the given name, then that will be used. Otherwise, MyBatis will look for a field of the given name. In both cases you can use complex property navigation using the usual dot notation. For example, you can map to something simple like: username, or to something more complicated like: address.street.number.

javaType

A fully qualified Java class name, or a type alias (see the table above for the list of built- in type aliases). MyBatis can usually figure out the type if you're mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behaviour.

jdbcType

The JDBC Type from the list of supported types that follows this table. The JDBC type is only required for nullable columns upon insert, update or delete. This is a JDBC requirement, not an MyBatis one. So even if you were coding JDBC directly, you'd need to specify this type – but only for nullable values.

typeHandler

We discussed default type handlers previously in this documentation. Using this property you can override the default type handler on a mapping-by-mapping basis. The value is either a fully qualified class name of a TypeHandler implementation, or a type alias.

4.1.5.7 Nested Select for Association

Attribute

Description

column

The column name from the database, or the aliased column label that holds the value that will be passed to the nested statement as an input parameter. This is the same string that would normally be passed to resultSet.getString(columnName).

Note: To deal with composite keys, you can specify multiple column names to pass to the nested select statement by using the syntax column="{prop1=col1,prop2=col2}".

This will cause prop1 and prop2 to be set against the parameter object for the target nested select statement.

select

The ID of another mapped statement that will load the complex type required by this property mapping. The values retrieved from columns specified in the column attribute will be passed to the target select statement as parameters. A detailed example follows this table. Note: To deal with composite keys, you can specify multiple column names to pass to the nested select statement by using the syntax column="{prop1=col1,prop2=col2}".

This will cause prop1 and prop2 to be set against the parameter object for the target nested select statement.

fetchType

Optional. Valid values are lazy and eager. If present, it supersedes the global configuration parameter lazyLoadingEnabled for this mapping.

For example:

<resultMap id="blogResult" type="Blog">   <association property="author" column="author_id" javaType="Author" select="select

</resultMap>

<select id="selectBlog" resultMap="blogResult">

  SELECT * FROM BLOG WHERE ID = #{id}

</select>

<select id="selectAuthor" resultType="Author">

  SELECT * FROM AUTHOR WHERE ID = #{id}

</select>

That's it. We have two select statements: one to load the Blog, the other to load the Author, and the Blog's resultMap describes that the selectAuthor statement should be used to load its author property.

All other properties will be loaded automatically assuming their column and property names match.

While this approach is simple, it will not perform well for large data sets or lists. This problem is known as the "N+1 Selects Problem". In a nutshell, the N+1 selects problem is caused like this:

     You execute a single SQL statement to retrieve a list of records (the "+1").

     For each record returned, you execute a select statement to load details for each (the "N").

This problem could result in hundreds or thousands of SQL statements to be executed. This is not always desirable.

The upside is that MyBatis can lazy load such queries, thus you might be spared the cost of these statements all at once. However, if you load such a list and then immediately iterate through it to access the nested data, you will invoke all of the lazy loads, and thus performance could be very bad.

And so, there is another way.

4.1.5.8 Nested Results for Association

Attribute

Description

resultMap

This is the ID of a ResultMap that can map the nested results of this association into an appropriate object graph. This is an alternative to using a call to another select statement. It allows you to join multiple tables together into a single ResultSet. Such a ResultSet will contain duplicated, repeating groups of data that needs to be decomposed and mapped properly to a nested object graph. To facilitate this, MyBatis lets you "chain" result maps together, to deal with the nested results. An example will be far easier to follow, and one follows this table.

columnPrefix

When joining multiple tables, you would have to use column alias to avoid duplicated column names in the ResultSet. Specifying columnPrefix allows you to map such columns to an external resultMap. Please see the example explained later in this section.

notNullColumn

By default a child object is created only if at least one of the columns mapped to the child's properties is non null. With this attribute you can change this behaviour by specifiying which columns must have a value so MyBatis will create a child object only if any of those columns is not null. Multiple column names can be specified using a comma as a separator. Default value: unset.

autoMapping

If present, MyBatis will enable or disable automapping when mapping the result to this property. This attribute overrides the global autoMappingBehavior. Note that it has no effect on an external resultMap, so it is pointless to use it with select or resultMap attribute. Default value: unset.

You've already seen a very complicated example of nested associations above. The following is a far simpler example to demonstrate how this works. Instead of executing a separate statement, we'll join the Blog and Author tables together, like so:

<select id="selectBlog" resultMap="blogResult">   select     B.id            as blog_id,

    B.title         as blog_title,

    B.author_id     as blog_author_id,

    A.id            as author_id,

    A.username      as author_username,

    A.password      as author_password,

    A.email         as author_email,

    A.bio           as author_bio   from Blog B left outer join Author A on B.author_id = A.id   where B.id = #{id}

</select>

Notice the join, as well as the care taken to ensure that all results are aliased with a unique and clear name. This makes mapping far easier. Now we can map the results:

<resultMap id="blogResult" type="Blog">

  <id property="id" column="blog_id" />

  <result property="title" column="blog_title"/>

  <association property="author" resultMap="authorResult" />

</resultMap>

<resultMap id="authorResult" type="Author">

  <id property="id" column="author_id"/>

  <result property="username" column="author_username"/>

  <result property="password" column="author_password"/>

  <result property="email" column="author_email"/>

  <result property="bio" column="author_bio"/>

</resultMap>

In the example above you can see at the Blog's "author" association delegates to the "authorResult" resultMap to load the Author instance.

Very Important: id elements play a very important role in Nested Result mapping. You should always specify one or more properties that can be used to uniquely identify the results. The truth is that MyBatis will still work if you leave it out, but at a severe performance cost. Choose as few properties as possible that can uniquely identify the result. The primary key is an obvious choice (even if composite).

Now, the above example used an external resultMap element to map the association. This makes the Author resultMap reusable. However, if you have no need to reuse it, or if you simply prefer to colocate your result mappings into a single descriptive resultMap, you can nest the association result mappings. Here's the same example using this approach:


<resultMap id="blogResult" type="Blog">

  <id property="id" column="blog_id" />

  <result property="title" column="blog_title"/>

  <association property="author" javaType="Author">

    <id property="id" column="author_id"/>

    <result property="username" column="author_username"/>

    <result property="password" column="author_password"/>

    <result property="email" column="author_email"/>

    <result property="bio" column="author_bio"/>

  </association>

</resultMap>

What if the blog has a co-author? The select statement would look like:

<select id="selectBlog" resultMap="blogResult">   select     B.id            as blog_id,

    B.title         as blog_title,

    A.id            as author_id,

    A.username      as author_username,

    A.password      as author_password,

    A.email         as author_email,

    A.bio           as author_bio,

    CA.id           as co_author_id,

    CA.username     as co_author_username,

    CA.password     as co_author_password,

    CA.email        as co_author_email,     CA.bio          as co_author_bio   from Blog B   left outer join Author A on B.author_id = A.id   left outer join Author CA on B.co_author_id = CA.id   where B.id = #{id}

</select>

Recall that the resultMap for Author is defined as follows.

<resultMap id="authorResult" type="Author">

  <id property="id" column="author_id"/>

  <result property="username" column="author_username"/>

  <result property="password" column="author_password"/>

  <result property="email" column="author_email"/>   <result property="bio" column="author_bio"/>

</resultMap>

Because the column names in the results differ from the columns defined in the resultMap, you need to specify columnPrefix to reuse the resultMap for mapping co-author results.

<resultMap id="blogResult" type="Blog">

  <id property="id" column="blog_id" />

  <result property="title" column="blog_title"/>   <association property="author"     resultMap="authorResult" />   <association property="coAuthor"     resultMap="authorResult"     columnPrefix="co_" /> </resultMap>

4.1.5.9 Multiple ResultSets for Association

Attribute

Description

column

When using multiple resultset this attribute specifies the columns (separated by commas) that will be correlated with the foreignColumn to identify the parent and the child of a relationship.

foreignColumn

Identifies the name of the columns that contains the foreing keys which values will be matched against the values of the columns specified in the column attibute of the parent type.

resultSet

Identifies the name of the result set where this complex type will be loaded from.

Starting from version 3.2.3 MyBatis provides yet another way to solve the N+1 problem.

Some databases allow stored procedures to return more than one resultset or execute more than one statement at once and return a resultset per each one. This can be used to hit the database just once and return related data without using a join.

In the example, the stored procedure executes the following queries and returns two result sets. The first will contain Blogs and the second Authors.

SELECT * FROM BLOG WHERE ID = #{id}

SELECT * FROM AUTHOR WHERE ID = #{id}

A name must be given to each result set by adding a resultSets attribute to the mapped statement with a list of names separated by commas.

<select id="selectBlog" resultSets="blogs,authors" resultMap="blogResult"

  {call getBlogsAndAuthors(#{id,jdbcType=INTEGER,mode=IN})}

</select>

> 

Now we can specify that the data to fill the "author" association comes in the "authors" result set:

<resultMap id="blogResult" type="Blog">

  <id property="id" column="id" />

  <result property="title" column="title"/>

  <association property="author" javaType="Author" resultSet="authors" column="autho

    <id property="id" column="id"/>

    <result property="username" column="username"/>

    <result property="password" column="password"/>

    <result property="email" column="email"/>

    <result property="bio" column="bio"/>

  </association>

</resultMap>

You've seen above how to deal with a "has one" type association. But what about "has many"? That's the subject of the next section.

4.1.5.10 collection

<collection property="posts" ofType="domain.blog.Post">

  <id property="id" column="post_id"/>

  <result property="subject" column="post_subject"/>

  <result property="body" column="post_body"/>

</collection>

The collection element works almost identically to the association. In fact, it's so similar, to document the similarities would be redundant. So let's focus on the differences.

To continue with our example above, a Blog only had one Author. But a Blog has many Posts. On the blog class, this would be represented by something like:

private List<Post> posts;

To map a set of nested results to a List like this, we use the collection element. Just like the association element, we can use a nested select, or nested results from a join.

4.1.5.11 Nested Select for Collection

First, let's look at using a nested select to load the Posts for the Blog.

<resultMap id="blogResult" type="Blog">

  <collection property="posts" javaType="ArrayList" column="id" ofType="Post" select

</resultMap>

<select id="selectBlog" resultMap="blogResult">

  SELECT * FROM BLOG WHERE ID = #{id}

</select>

<select id="selectPostsForBlog" resultType="Post">

  SELECT * FROM POST WHERE BLOG_ID = #{id}

</select>

There are a number things you'll notice immediately, but for the most part it looks very similar to the association element we learned about above. First, you'll notice that we're using the collection element. Then you'll notice that there's a new "ofType" attribute. This attribute is necessary to distinguish between the JavaBean (or field) property type and the type that the collection contains. So you could read the following mapping like this:

<collection property="posts" javaType="ArrayList" column="id" ofType="Post" select="

Read as: "A collection of posts in an ArrayList of type Post."

The javaType attribute is really unnecessary, as MyBatis will figure this out for you in most cases.

So you can often shorten this down to simply:

<collection property="posts" column="id" ofType="Post" select="selectPostsForBlog"/

4.1.5.12 Nested Results for Collection

By this point, you can probably guess how nested results for a collection will work, because it's exactly the same as an association, but with the same addition of the ofType attribute applied.

First, let's look at the SQL:

<select id="selectBlog" resultMap="blogResult">   select   B.id as blog_id,

  B.title as blog_title,

  B.author_id as blog_author_id,

  P.id as post_id,

  P.subject as post_subject,

  P.body as post_body,   from Blog B   left outer join Post P on B.id = P.blog_id   where B.id = #{id}

</select>

Again, we've joined the Blog and Post tables, and have taken care to ensure quality result column labels for simple mapping. Now mapping a Blog with its collection of Post mappings is as simple as:

<resultMap id="blogResult" type="Blog">

  <id property="id" column="blog_id" />

  <result property="title" column="blog_title"/>

  <collection property="posts" ofType="Post">

    <id property="id" column="post_id"/>

    <result property="subject" column="post_subject"/>

    <result property="body" column="post_body"/>

  </collection>

</resultMap>

Again, remember the importance of the id elements here, or read the association section above if you haven't already.

Also, if you prefer the longer form that allows for more reusability of your result maps, you can use the following alternative mapping:

<resultMap id="blogResult" type="Blog">

  <id property="id" column="blog_id" />

  <result property="title" column="blog_title"/>

  <collection property="posts" ofType="Post" resultMap="blogPostResult" columnPrefix

</resultMap>

<resultMap id="blogPostResult" type="Post">

  <id property="id" column="id"/>

  <result property="subject" column="subject"/>

  <result property="body" column="body"/>

</resultMap>

4.1.5.13 Multiple ResultSets for Collection

As we did for the association, we can call an stored procedure that executes two queries and returns two result sets, one with Blogs and another with Posts:

SELECT * FROM BLOG WHERE ID = #{id}

SELECT * FROM POST WHERE BLOG_ID = #{id}

A name must be given to each result set by adding a resultSets attribute to the mapped statement with a list of names separated by commas.

<select id="selectBlog" resultSets="blogs,posts" resultMap="blogResult"

  {call getBlogsAndPosts(#{id,jdbcType=INTEGER,mode=IN})}

</select>

> 

We specify that the "posts" collection will be filled out of data contained in the result set named "posts":

<resultMap id="blogResult" type="Blog">

  <id property="id" column="id" />

  <result property="title" column="title"/>

  <collection property="posts" ofType="Post" resultSet="posts" column="id" foreignCo

    <id property="id" column="id"/>

    <result property="subject" column="subject"/>

    <result property="body" column="body"/>

  </collection>

</resultMap>

NOTE There's no limit to the depth, breadth or combinations of the associations and collections that you map. You should keep performance in mind when mapping them. Unit testing and performance testing of your application goes a long way toward discovering the best approach for your application. The nice thing is that MyBatis lets you change your mind later, with very little (if any) impact to your code.

Advanced association and collection mapping is a deep subject. Documentation can only get you so far. With a little practice, it will all become clear very quickly.

4.1.5.14 discriminator

<discriminator javaType="int" column="draft">

  <case value="1" resultType="DraftPost"/>

</discriminator>

Sometimes a single database query might return result sets of many different (but hopefully somewhat related) data types. The discriminator element was designed to deal with this situation, and others, including class inheritance hierarchies. The discriminator is pretty simple to understand, as it behaves much like a switch statement in Java.

A discriminator definition specifies column and javaType attributes. The column is where MyBatis will look for the value to compare. The javaType is required to ensure the proper kind of equality test is performed (although String would probably work for almost any situation). For example:

<resultMap id="vehicleResult" type="Vehicle">

  <id property="id" column="id" />

  <result property="vin" column="vin"/>

  <result property="year" column="year"/>

  <result property="make" column="make"/>

  <result property="model" column="model"/>

  <result property="color" column="color"/>

  <discriminator javaType="int" column="vehicle_type">

    <case value="1" resultMap="carResult"/>

    <case value="2" resultMap="truckResult"/>

    <case value="3" resultMap="vanResult"/>

    <case value="4" resultMap="suvResult"/>

  </discriminator>

</resultMap>

In this example, MyBatis would retrieve each record from the result set and compare its vehicle type value. If it matches any of the discriminator cases, then it will use the resultMap specified by the case. This is done exclusively, so in other words, the rest of the resultMap is ignored (unless it is extended, which we talk about in a second). If none of the cases match, then MyBatis simply uses the resultMap as defined outside of the discriminator block. So, if the carResult was declared as follows:

<resultMap id="carResult" type="Car">

  <result property="doorCount" column="door_count" />

</resultMap>

Then ONLY the doorCount property would be loaded. This is done to allow completely independent groups of discriminator cases, even ones that have no relationship to the parent resultMap. In this case we do of course know that there's a relationship between cars and vehicles, as a Car is-a Vehicle. Therefore, we want the rest of the properties loaded too. One simple change to the resultMap and we're set to go.

<resultMap id="carResult" type="Car" extends="vehicleResult">

  <result property="doorCount" column="door_count" />

</resultMap>

Now all of the properties from both the vehicleResult and carResult will be loaded.

Once again though, some may find this external definition of maps somewhat tedious. Therefore there's an alternative syntax for those that prefer a more concise mapping style. For example:


<resultMap id="vehicleResult" type="Vehicle">

  <id property="id" column="id" />

  <result property="vin" column="vin"/>

  <result property="year" column="year"/>

  <result property="make" column="make"/>

  <result property="model" column="model"/>

  <result property="color" column="color"/>

  <discriminator javaType="int" column="vehicle_type">

    <case value="1" resultType="carResult">

      <result property="doorCount" column="door_count" />

    </case>

    <case value="2" resultType="truckResult">

      <result property="boxSize" column="box_size" />

      <result property="extendedCab" column="extended_cab" />

    </case>

    <case value="3" resultType="vanResult">

      <result property="powerSlidingDoor" column="power_sliding_door" />

    </case>

    <case value="4" resultType="suvResult">

      <result property="allWheelDrive" column="all_wheel_drive" />

    </case>

  </discriminator>

</resultMap>

NOTE Remember that these are all Result Maps, and if you don't specify any results at all, then MyBatis will automatically match up columns and properties for you. So most of these examples are more verbose than they really need to be. That said, most databases are kind of complex and it's unlikely that we'll be able to depend on that for all cases.

4.1.6 Auto-mapping

As you have already seen in the previous sections, in simple cases MyBatis can auto-map the results for you and in others you will need to build a result map. But as you will see in this section you can also mix both strategies. Let's have a deeper look at how auto-mapping works.

When auto-mapping results MyBatis will get the column name and look for a property with the same name ignoring case. That means that if a column named ID and property named id are found, MyBatis will set the id property with the ID column value.

Usually database columns are named using uppercase letters and underscores between words and java properties often follow the camelcase naming covention. To enable the auto-mapping between them set the setting mapUnderscoreToCamelCase to true.

Auto-mapping works even when there is an specific result map. When this happens, for each result map, all columns that are present in the ResultSet that have not a manual mapping will be automapped, then manual mappings will be processed. In the following sample id and userName columns will be auto-mapped and hashed_password column will be mapped.

<select id="selectUsers" resultType="User">   select     user_id             as "id",     user_name           as "userName",     hashed_password   from some_table   where id = #{id} </select>

<resultMap id="userResultMap" type="User">

  <result property="password" column="hashed_password"/>

</resultMap>

There are three auto-mapping levels:

     NONE - disables auto-mapping. Only manually mapped properties will be set.

     PARTIAL - will auto-map results except those that have nested result mappings defined inside (joins).

     FULL - auto-maps everything.

The default value is PARTIAL, and it is so for a reason. When FULL is used auto-mapping will be performed when processing join results and joins retrieve data of several different entities in the same row hence this may result in undesired mappings. To understand the risk have a look at the following sample:

<select id="selectBlog" resultMap="blogResult">   select     B.id,

    B.title,

    A.username,   from Blog B left outer join Author A on B.author_id = A.id   where B.id = #{id}

</select>

<resultMap id="blogResult" type="Blog">

  <association property="author" javaType="Author" resultMap="authorResult"/>

</resultMap>

<resultMap id="authorResult" type="Author">

  <result property="username" column="author_username"/>