MyBatis config and CRUD


Quick note about MyBatis configuration and CRUD.

Mybatis configuration

  • <environments> contains the environment configuration for transaction management and connection pooling.

  • <dataSource> configures the source of JDBC Connection objects using the standard JDBC DataSource interface.

  • <mappers> contains a list of mappers – the XML files and/or annotated Java interface classes that contain SQL statements and mapping definitions.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
    PUBLIC "-// Config 3.0//EN"


  	<environments default="development">
    	<environment id="development">
      		<transactionManager type="JDBC"></transactionManager>
      		<dataSource type="POOLED">
        		<property name="driver" value="com.mysql.jdbc.Driver"/>
        		<property name="url" value="jdbc:mysql://localhost:3306/dbname"/>
        		<property name="username" value="root"/>
        		<property name="password" value="123"/>

  		<mapper class=""/>
	    <mapper resource="com/dong/map/xmlMapper.xml"/>



One SqlSessionFactory instance per database.

String resource = "path/to/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

SqlSession session = sqlSessionFactory.openSession();

try {
} catch(Exception e) {
} finally {

If <transactionManager> is set as JDBC. We could call directly commit() and rollback() to control transaction.



Before CRUD, better to know <typeAliases>. It sets a shorter name for a Java type to reduce redundant typing of fully qualified classnames.

	<typeAlias alias="User" type="com.dong.demo.User"/>

So in all the following example, we use directly short name instead of full qualified classname.


We could do single row query or multiple row query:

  • session.selectOne(statementID, selectParameter) to return one result
  • session.selectList(statementID, selectParameter) to return a list of results

Some properties:

  • parameterType - Type of parameter that will be passed into statement.
  • resultType - return type of statement. In case of collections, it’s the type that the collection contains.
  • resultMap - A named reference to an external resultMap. To solve complex mapping cases. Can not use with resultType together.
  • For other properties, check here.


<!-- #{id} type is int, and return type is User -->
<select id="findById" parameterType="int" resultType="User">
	select * from user where id=#{id}

<!-- return type User is the type that the collection contains -->
<select id="selectList" resultType="User">
	select * from user

<!-- HashMap -->
<!-- #{userName} and #(password) use key values in hashmap to set values -->
<select id="login" parameterType="hashmap" resultType="User">
	select * from user where userName=#{userName} and password=#{password}

<!-- Object as parameter type -->
<!-- #{userName} and #(password) use the same name as properties of User object -->
<select id="login" parameterType="User" resultType="User">
	select * from user where userName=#{userName} and password=#{password}

<!-- resultMap -->
<!-- A simple resultMap to tell which property in User object matches which column -->
<!-- in databse. It could solve column name mismatches -->
<resultMap id="userMap" type="User"> 
	<id property="id" column="id"/>
	<result property="userName" column="userName"/> 
	<result property="password" column="password"/> 
<select id="selectUsers" resultMap="userMap"> 
	select id, userName, password from User

After including above xml file in mybatis configuration file. We could defined SQL statement from java code:

// Select one
User user = session.selectOne("findById", 1); // 1 will be the value of #{id} in statement

// Select list
List<User> list = session.selectList("selectList");

// By hashmap
HashMap<String, String> hm = new HashMap();
hm.put("userName", "dong");
User user = session.selectOne("login", hm);

// By object
User user = new User();
User result = session.selectOne("login2", user);

// By resultMap
List<User> listUsers = session.selectList("selectUsers");


XML sql statement definition:

<update id="updateUser" parameterType="User">
	WHERE id = #{id}

Java code to call it:

User user = new User(); 

session.update("updateUser", user);


XML sql statement definition:

<insert id="insertUser" parameterType="User" statementType="PREPARED"
	keyProperty="id" useGeneratedKeys="true">
	insert into user
	(userName,password) values

Java code to call it:

User user = new User(); 

session.insert("insertUser", user);

keyProperty="id" and useGeneratedKeys="true" are used to tell mybatis to use JDBC getGeneratedKeys to get ID generated inner DB and set it to property id.


XML sql statement definition:

<delete id="deleteAuthor" parameterType="int">
  	delete from User where id = #{id}

Java code to call it:

session.delete("deleteAuthor", 1);

By annotation

To use annotation instead of xml definition. We need to define an interface to declare all CRUD operations:

public interface AnnotationMapper {

	@Delete("delete from User where id=#{id}")
	public void deleteUser(Integer id);


Same as including mapper xml files, we need to include above class in configuration file:

  	<mapper class=""/>

Java code to call it:

AnnotationMapper test = session.getMapper(AnnotationMapper.class);


Post Directory