Question

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

You might also want to check Java tutorial , MySQL Java tutorial , MySQL tutorial , or Spring JdbcTemplate tutorial on ZetCode.

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.39</version>
  </dependency>  
  
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.0</version>
  </dependency>    
  
</dependencies>       

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

MySQL version

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

Figure: MyBatisMySQLVersion project structure

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 {

  public 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);

    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 {

  public 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();
      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.

Books

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

The example uses a Books table.

books.sql

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

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

Figure: MyBatisMySQLBooks project structure

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 yearPublished;
  private String remark;
  
  public Book() {};

  public Book(String author, String title, int published, 
      String remark) {
    
    this.author = author;
    this.title = title;
    this.yearPublished = 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 getYearPublished() {
    return yearPublished;
  }

  public void setYearPublished(int yearPublished) {
    this.yearPublished = yearPublished;
  }

  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 + ", yearPublished=" + yearPublished 
        + ", 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.books;

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

In the MyMapper interface, we have three annotations.

@Select("SELECT * FROM Books 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 Books 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 Books(Author, Title, Published, Remark) "
    + "VALUES(#{author}, #{title}, #{yearPublished}, #{remark})")
public void insertBook(String author, String title, int yearPublished, 
    String remark);

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

MyBatisMySQLBooks.java

package com.zetcode.books;

import com.zetcode.books.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 MyBatisMySQLBooks {

  public 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);

    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 , MongoDB Java tutorial , or MySQL tutorial .