Introduction to MyBatis

last modified July 6, 2020

This is MyBatis Java tutorial. This tutorial covers the basics of MySQL programming with Java and MyBatis.

ZetCode has a complete e-book for MySQL Java, which contains a MyBatis chapter: MySQL Java programming e-book.

MyBatis

MyBatis is a Java persistence framework that couples objects with stored procedures or SQL statements using an XML descriptor or annotations. Unlike ORM frameworks, MyBatis does not map Java objects to database tables but Java methods to SQL statements. MyBatis allows to use all database functionality like stored procedures, views, queries of any complexity and vendor proprietary features.

The benefits of using MyBatis are:

out-of-the-box table/query caching
reduction of much of the JDBC boilerplate
increased productivity
separation of SQL code from Java classes

About MySQL database

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. MySQL comes in two versions: MySQL server system and MySQL embedded system.

Maven dependencies

In the pom.xml file, we add the following dependencies:

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.40</version>
    </dependency>    
    
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.2</version>
    </dependency>      
    
</dependencies>

The POM file has two dependencies: the MyBatis libraries and MySQL driver.

MyBooks table

Some examples in this tutorial use the MyBooks table.

mybooks.sql

CREATE TABLE MyBooks(Id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  Author VARCHAR(30), Title VARCHAR(60),  Published INTEGER, Remark VARCHAR(150));
INSERT INTO MyBooks(Author, Title, Published, Remark) VALUES ('Leo Tolstoy', 'War and Peace', 1869, 'Napoleonic wars');    
INSERT INTO MyBooks(Author, Title, Published, Remark) VALUES ('Leo Tolstoy', 'Anna Karenina', 1878, 'Greatest book of love');
INSERT INTO MyBooks(Author, Title, Published, Remark) VALUES ('Jeff Prosise', 'Programming Windows with MFC', 1999, 'Classic book about MFC');
INSERT INTO MyBooks(Author, Title, Published, Remark) VALUES ('Tom Marrs', 'JBoss at Work', 2005, 'JBoss practical guide');
INSERT INTO MyBooks(Author, Title, Published, Remark) VALUES ('Debu Panda', 'EJB3 in Action', 2007, 'Introduction to Enterprice Java Beans');

These SQL commands create a MyBooks table in MySQL testdb database.

MySQL version

In the first example, we get the version of MySQL. In this example we map objects to SQL statements using annotations.

This is the project structure in NetBeans.

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/testdb"/>
                <property name="username" value="testuser"/>
                <property name="password" value="test623"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

Each MyBatis project has a primary XML configuration file. Here we have defined a datasource for MySQL.

MyMapper.java

package com.zetcode.version;

import org.apache.ibatis.annotations.Select;

public interface MyMapper {

    @Select("SELECT VERSION()")
    public String getMySQLVersion();
}

With the @Select annotation, we map the getMySQLVersion method to the SQL statement specified in the annotation. The SQL statement to get the version of MySQL is SELECT VERSION.

MyBatisMySQLVersion.java

package com.zetcode.version;

import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisMySQLVersion {

    private static SqlSessionFactory factory = null;

    public static void main(String[] args) throws IOException {

        String resource = "mybatis-config.xml";
        Reader reader = null;
        SqlSession session = null;

        reader = Resources.getResourceAsReader(resource);

        factory = new SqlSessionFactoryBuilder().build(reader);
        factory.getConfiguration().addMapper(MyMapper.class);
        
        reader.close();

        try {
            session = factory.openSession();
            String version = session.selectOne("getMySQLVersion");
            System.out.println(version);

        } finally {

            if (session != null) {
                session.close();
            }
        }
    }
}

We connect to the database and get the version of MySQL.

String resource = "mybatis-config.xml";
Reader reader = null;
SqlSession session = null;

reader = Resources.getResourceAsReader(resource);

The configuration file is read.

factory = new SqlSessionFactoryBuilder().build(reader);

The SqlSessionFactoryBuilder is used to build SqlSession instances.

factory.getConfiguration().addMapper(MyMapper.class);

With the addMapper method, we add the mapping class to the factory.

session = factory.openSession();

The openSession method creates an SqlSession. SqlSession is the primary Java interface for working with MyBatis. Through this interface we execute commands, get mappers and manage transactions.

String version = session.selectOne("getMySQLVersion");

The selectOne method retrieves a single row mapped from the statement key. The statement key is the name of the method in the mapper class.

System.out.println(version);

The version is printed to the console.

} finally {

    if (session != null) {
        session.close();
    }
}

In the end, the session is closed.

MySQL version 2

In the second example, we are going to retrieve the version of MySQL too; this time we use an XML mapper instead of annotations.

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/testdb"/>
                <property name="username" value="testuser"/>
                <property name="password" value="test623"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="mymapper.xml"/>
    </mappers>    
    
</configuration>

With the <mappers> tag, we specify the mapping file.

mymapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
<mapper namespace="com.zetcode">
    
    <select id="mysqlVersion" resultType="String">
        SELECT VERSION()
    </select>
    
</mapper>

We define the mapping with the <select> tag.

MyBatisMySQLVersion2.java

package com.zetcode.version2;

import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisMySQLVersion2 {

    private static SqlSessionFactory factory = null;

    public static void main(String[] args) throws IOException {

        String resource = "mybatis-config.xml";
        Reader reader = null;
        SqlSession session = null;

        reader = Resources.getResourceAsReader(resource);

        factory = new SqlSessionFactoryBuilder().build(reader);
        
        reader.close();

        try {
            session = factory.openSession();
            String version = session.selectOne("mysqlVersion");
            System.out.println(version);

        } finally {

            if (session != null) {
                session.close();
            }
        }
    }
}

This is the main class. The difference is that we do not add a mapper with the addMapper, but it is read from the configuration file.

MyBatis Java Config

It is possible to configure MyBatis in pure Java without using XML. In the following example, we are going to find out the number of books in the table.

MyMapper.java

package com.zetcode.map;

import org.apache.ibatis.annotations.Select;

public interface MyMapper {

    @Select("SELECT COUNT(*) FROM MyBooks")
    public int getNumberOfBooks();
}

MyMapper contains SQL code that counts the number of rows in the MyBooks table.

MyDataSourceFactory.java

package com.zetcode.jconfig;

import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.datasource.DataSourceFactory;
import org.apache.ibatis.datasource.pooled.PooledDataSource;

public class MyDataSourceFactory implements DataSourceFactory {
    
    private Properties prop;

    @Override
    public DataSource getDataSource() {

        PooledDataSource ds = new PooledDataSource();
        
        ds.setDriver(prop.getProperty("driver"));
        ds.setUrl(prop.getProperty("url"));
        ds.setUsername(prop.getProperty("user"));
        ds.setPassword(prop.getProperty("password"));
        
        return ds;
    }

    @Override
    public void setProperties(Properties prprts) {
         
        prop = prprts;
    }
}

MyDataSourceFactory creates a PooledDataSource from the given properties. PooledDataSource is a simple, synchronous, thread-safe database connection pool.

MyBatisJavaConfClient.java

package com.zetcode.client;

import com.zetcode.jconfig.MyDataSourceFactory;
import com.zetcode.jconfig.MyMapper;
import java.io.IOException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

public class MyBatisJavaConfClient {

    private static SqlSessionFactory sesFact = null;

    public static void main(String[] args) throws IOException {

        Properties prop = new Properties();
        prop.setProperty("driver", "com.mysql.jdbc.Driver");
        prop.setProperty("url", "jdbc:mysql://localhost:3306/testdb");
        prop.setProperty("user", "testuser");
        prop.setProperty("password", "test623");
        
        MyDataSourceFactory mdsf = new MyDataSourceFactory();
        mdsf.setProperties(prop);
        DataSource ds = mdsf.getDataSource();
        
        TransactionFactory trFact = new JdbcTransactionFactory();
        Environment environment = new Environment("development", trFact, ds);
        Configuration config = new Configuration(environment);
        config.addMapper(MyMapper.class);
        
        sesFact = new SqlSessionFactoryBuilder().build(config);

        try (SqlSession session = sesFact.openSession()) {
            
            int numOfBooks = session.selectOne("getNumberOfBooks");
            System.out.format("There are %d books", numOfBooks);
        }
    }
}

In the MyBatisJavaConfClient, we configure MyBatis with Java code, build the SQL session, and execute the getNumberOfBooks method.

Properties prop = new Properties();
prop.setProperty("driver", "com.mysql.jdbc.Driver");
prop.setProperty("url", "jdbc:mysql://localhost:3306/testdb");
prop.setProperty("user", "testuser");
prop.setProperty("password", "test623");

Here we set up the database properties.

MyDataSourceFactory mdsf = new MyDataSourceFactory();
mdsf.setProperties(prop);
DataSource ds = mdsf.getDataSource();

We get the data source with MyDataSourceFactory.

TransactionFactory trFact = new JdbcTransactionFactory();
Environment environment = new Environment("development", trFact, ds);
Configuration config = new Configuration(environment);
config.addMapper(MyMapper.class);

This code replaces the XML configuration. We use JdbcTransactionFactory, Environment, and Configuration classes.

sesFact = new SqlSessionFactoryBuilder().build(config);

The instance of the Configuration class is passed to the SqlSessionFactoryBuilder's build method.

Dynamic SQL

Dynamic SQL allows us to create dynamic SQL queries with tags such as <if>, <where>, or <foreach>.

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    
    <typeAliases>
        <typeAlias alias="Book" 
                   type="com.zetcode.mybatisdynamicsql.bean.Book"/>  
    </typeAliases>      
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" 
                          value="jdbc:mysql://localhost:3306/testdb"/>
                <property name="username" value="testuser"/>
                <property name="password" value="test623"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="mymapper.xml"/>
    </mappers>    
    
</configuration>

First we provide the mybatis-config.xml configuration file.

mymapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
<mapper namespace="com.zetcode">
    
    <select id = "getBook" resultType = "Book">
        SELECT * FROM MyBooks
    
        <where>
            <if test = "_parameter != null">
                Id = #{id}
            </if>
        </where>
        
    </select>    
            
</mapper>

The mymapper.xml contains the dynamic SQL expression.

<where>
    <if test = "_parameter != null">
        Id = #{id}
    </if>
</where>

The content of the <where> tag is included only if the Id parameter is not null. In effect, the SQL expression returns one book identified by its ID or all books otherwise.

Book.java

package com.zetcode.mybatisdynamicsql.bean;

public class Book {

    private Long id;
    private String author;
    private String title;
    private int published;
    private String remark;
    
    public Book() {};

    public Book(String author, String title, int published, 
            String remark) {
        
        this.author = author;
        this.title = title;
        this.published = published;
        this.remark = remark;
    }    

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public int getPublished() {
        return published;
    }

    public void setPublished(int published) {
        this.published = published;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }
    
    @Override
    public String toString() {
        return "Book{" + "id=" + id + ", author=" + author + ", "
                + "title=" + title + ", published=" + published 
                + ", remark=" + remark + '}';
    }    
}

This is the Book bean that is mapped to our result data.

Book.java

package com.zetcode.mybatisdynamicsql;

import com.zetcode.mybatisdynamicsql.bean.Book;
import java.io.IOException;
import java.io.Reader;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisDynamicSQL {

    private static SqlSessionFactory factory = null;

    public static void main(String[] args) throws IOException {

        String resource = "mybatis-config.xml";
        Reader reader = null;
        SqlSession session = null;

        reader = Resources.getResourceAsReader(resource);

        factory = new SqlSessionFactoryBuilder().build(reader);

        try {
            session = factory.openSession();
            Book book = session.selectOne("getBook", 1);
            System.out.println(book);
            
            List<Book> books = session.selectList("getBook");

            for (Book b : books) {
                System.out.println(b);
            }            

        } finally {

            if (session != null) {
                session.close();
            }
        }
    }
}

Using one statement key, we retrieve one particular book and all books.

Book book = session.selectOne("getBook", 1);

Here we retrieve one book identified by its ID.

List<Book> books = session.selectList("getBook");

Here we retrieve all books; the second parameter is not passed.

Books

In the next example, we will insert and read books from a database table.

This is the project structure in NetBeans.

Book.java

package com.zetcode.books.bean;

public class Book {

    private Long id;
    private String author;
    private String title;
    private int published;
    private String remark;
    
    public Book() {};

    public Book(String author, String title, int published, 
            String remark) {
        
        this.author = author;
        this.title = title;
        this.published = published;
        this.remark = remark;
    }    

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public int getPublished() {
        return published;
    }

    public void setPublished(int published) {
        this.published = published;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }
    
    @Override
    public String toString() {
        return "Book{" + "id=" + id + ", author=" + author + ", "
                + "title=" + title + ", published=" + published 
                + ", remark=" + remark + '}';
    }    
}

This is the Book bean. MyBatis will map table columns to this class. Notice the explicit usage of empty constructor.

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  
    <typeAliases>
        <typeAlias alias="Book" type="com.zetcode.books.bean.Book"/>  
    </typeAliases>  

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

</configuration>

In the mybatis-config.xml file, we define the new Book type with the <typeAlias> tag.

MyMapper.java

package com.zetcode.map;

import com.zetcode.books.bean.Book;
import java.util.List;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

public interface MyMapper {

    @Select("SELECT * FROM MyBooks WHERE Id = #{id}")
    public Book getBookById(Long id);
    
    @Select("SELECT * FROM MyBooks WHERE Author = #{author}")
    public List<Book> getBooksByAuthor(String author);   
    
    @Insert("INSERT INTO MyBooks(Author, Title, Published, Remark) "
            + "VALUES(#{author}, #{title}, #{published}, #{remark})")
    public void insertBook(String author, String title, int published, 
            String remark);
}

In the MyMapper interface, we have three annotations.

@Select("SELECT * FROM MyBooks WHERE Id = #{id}")
public Book getBookById(Long id);

This annotation maps the getBookById method to the specified SELECT statement; the method returns a Book object.

@Select("SELECT * FROM MyBooks WHERE Author = #{author}")
public List<Book> getBooksByAuthor(String author);

We map a SELECT statement to a list of getBooksByAuthor method, which returns a list of book objects.

@Insert("INSERT INTO MyBooks(Author, Title, Published, Remark) "
        + "VALUES(#{author}, #{title}, #{published}, #{remark})")
public void insertBook(String author, String title, int published, 
        String remark);

With the @Insert annotation, we map an INSERT statement to the insertBook method name.

MyBatisBooks.java

package com.zetcode.client;

import com.zetcode.map.MyMapper;
import com.zetcode.books.bean.Book;
import com.zetcode.util.MyBatisUtils;
import java.io.IOException;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

public class MyBatisBooks {

    private static SqlSessionFactory factory = null;

    public static void main(String[] args) throws IOException {

        SqlSession session = null;
        
        factory = MyBatisUtils.getSqlSessionFactory();
        factory.getConfiguration().addMapper(MyMapper.class);
        
        try {
            session = factory.openSession();
            Book book = session.selectOne("getBookById", 4L);
            System.out.println(book);

            List<Book> books = session.selectList("getBooksByAuthor", "Leo Tolstoy");

            for (Book b : books) {
                System.out.println(b);
            }

            Book newBook = new Book("Miguel de Cervantes", "Don Quixote",
                    1605, "First modern novel");

            session.update("insertBook", newBook);
            session.commit();

        } finally {

            if (session != null) {
                session.close();
            }
        }
    }
}

In the main class, we select a book by its ID, select all books from an author, and insert a new book into the table.

Book book = session.selectOne("getBookById", 4L);

A new book is retrieved using session's selectOne method.

List<Book> books = session.selectList("getBooksByAuthor", "Leo Tolstoy");

for (Book b : books) {
    System.out.println(b);
}

All books from Leo Tolstoy are retrieved using session's selectList method.

session.update("insertBook", newBook);
session.commit();

A new books is inserted with the session's update method. The method takes a Book instance as the second parameter. The changes are committed to the database with commit.

This was the MyBatis tutorial. You might be also interested in JDBI tutorial, PostgreSQL Java tutorial, Java MongoDB tutorial, or MySQL tutorial.

Ebooks

Introduction to MyBatis

MyBatis

About MySQL database

Maven dependencies

MyBooks table

MySQL version

MySQL version 2

MyBatis Java Config

Dynamic SQL

Books