Java Persistence with MyBatis 3

What is MyBatis?

Mybatis is an open source persistence framework that simplifies the implementation of the persistence layer by abstracting a lot of JDBC boilerplate code and provides a simple and easy-to-user API to interact with the database.

Why MyBatis?

• It Eliminates a lot of JDBC boilerplate code

• It has a low learning curve

• It works well with legacy databases

• It embraces SQL

• It provides support for integration with Spring and Guice frameworks

• It provides support for integration with third-party cache libraries

• It induces better performance

Now let us see how we can implement the preceding methods using MyBatis:

1. Configure the queries in a SQL Mapper config file, say StudentMapper.xml.

<select id="findStudentById" parameterType="int"

resultType=" Student">

SELECT STUD_ID AS studId, NAME, EMAIL, DOB

FROM STUDENTS WHERE STUD_ID=#{Id}

</select>

<insert id="insertStudent" parameterType="Student">

INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,DOB)

VALUES(#{studId},#{name},#{email},#{dob})

</insert>

2. Create a StudentMapper interface.

public interface StudentMapper

{

Student findStudentById(Integer id);

void insertStudent(Student student);

}

3. In Java code, you can invoke these statements as follows:

SqlSession session = getSqlSessionFactory().openSession();

StudentMapper mapper =

session.getMapper(StudentMapper.class);

// Select Student by Id

Student student = mapper.selectStudentById(1);

//To insert a Student record

mapper.insertStudent(student);

Mapper XMLs and Mapper interfaces

Let us now see how the findStudentById mapped statement can be configured in StudentMapper.xml, which is iin the com.mybatis3.mappers package, using the following code:

<?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.mybatis3.mappers.StudentMapper">

<select id="findStudentById" parameterType="int"

resultType="Student">

select stud_id as studId, name, email, dob from Students where

stud_id=#{studId}

</select>

</mapper>

We can invoke the mapped statement as follows:

public Student findStudentById(Integer studId)

{

SqlSession sqlSession = MyBatisUtil.getSqlSession();

try

{

Student student =

sqlSession.selectOne("com.mybatis3.mappers.StudentMapper.

findStudentById", studId);

return student;

} finally {

sqlSession.close();

}

MyBatis provides a better way of invoking mapped statements by using Mapper interfaces.Once we have configured the mapped statements in the Mapper XML file, we can create Mapper interface with a fully qualified name theat is the same as the namespace and add the method signatures whith matching statemtn IDs, input parameters, and return types.

For the preceding StudentMapper.xml file, we can create a Mapper interface

StudentMapper.java as follows:

package com.mybatis3.mappers;

public interface StudentMapper

{

Student findStudentById(Integer id);

}

Using Mapper interfaces, you can invoke mapped statements in a type safe manner

as follows:

public Student findStudentById(Integer studId)

{

SqlSession sqlSession = MyBatisUtil.getSqlSession();

try {

StudentMapper studentMapper =

sqlSession.getMapper(StudentMapper.class);

return studentMapper.findStudentById(studId);

} finally {

sqlSession.close();

}

}

Autogenerated keys

In the preceding INSERT statement, we are inserting the value for the

STUD_ID column that is an auto_generated primary key column. We can use

the useGeneratedKeys and keyProperty attributes to let the database generate

the auto_increment column value and set that generated value into one of the

input object properties as follows:

<insert id="insertStudent" parameterType="Student"

useGeneratedKeys="true" keyProperty="studId">

INSERT INTO STUDENTS(NAME, EMAIL, PHONE)

VALUES(#{name},#{email},#{phone})

</insert>

Some databases such as Oracle don't support AUTO_INCREMENT columns and use

SEQUENCE to generate the primary key values.

Assume we have a SEQUENCE called STUD_ID_SEQ to generate the STUD_ID primary

key values. Use the following code to generate the primary key:

<insert id="insertStudent" parameterType="Student">

<selectKey keyProperty="studId" resultType="int" order="BEFORE">

SELECT ELEARNING.STUD_ID_SEQ.NEXTVAL FROM

</selectKey>

INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL, PHONE)

VALUES(#{studId},#{name},#{email},#{phone})

</insert>

Extending ResultMaps

We can estend one <resultMap> query from another <resultMap> query, thereby inheriting the column to do property mappings from the one that is being extended.

<resultMap type="Student" id="StudentResult">

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

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

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

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

</resultMap>

<resultMap type="Student" id="StudentWithAddressResult"

extends="StudentResult">

<result property="address.addrId" column="addr_id"/>

<result property="address.street" column="street"/>

<result property="address.city" column="city"/>

<result property="address.state" column="state"/>

<result property="address.zip" column="zip"/>

<result property="address.country" column="country"/>

</resultMap>

<select id="findStudentById" parameterType="int"

resultMap="StudentResult">

SELECT * FROM STUDENTS WHERE STUD_ID=#{studId}

</select>

<select id="selectStudentWithAddress" parameterType="int"

resultMap="StudentWithAddressResult">

SELECT STUD_ID, NAME, EMAIL, PHONE, A.ADDR_ID, STREET, CITY,

STATE, ZIP, COUNTRY

FROM STUDENTS S LEFT OUTER JOIN ADDRESSES A on

S.ADDR_ID=A.ADDR_ID

WHERE STUD_ID=#{studId}

</select>

One-to-many mapping

In the preceding table data, the tutor John teaches one course whereas the tutor Ying

teaches two courses.

The JavaBeans for Course and Tutor are as follows:

public class Course

{

private Integer courseId;

private String name;

private String description;

private Date startDate;

private Date endDate;

private Integer tutorId;

//setters & getters

}

public class Tutor

{

private Integer tutorId;

private String name;

private String email;

private Address address;

private List<Course> courses;

/setters & getters

}

One-to-many mapping with nested ResultMap

We can get the tutor along with the courses' details using a nested ResultMap

as follows:

<resultMap type="Course" id="CourseResult">

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

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

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

<result column="start_date" property="startDate"/>

<result column="end_date" property="endDate"/>

</resultMap>

<resultMap type="Tutor" id="TutorResult">

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

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

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

<collection property="courses" resultMap="CourseResult"/>

</resultMap>

<select id="findTutorById" parameterType="int"

resultMap="TutorResult">

SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL, C.COURSE_ID,

C.NAME, DESCRIPTION, START_DATE, END_DATE

FROM TUTORS T LEFT OUTER JOIN ADDRESSES A on T.ADDR_ID=A.ADDR_ID

LEFT OUTER JOIN COURSES C on T.TUTOR_ID=C.TUTOR_ID

WHERE T.TUTOR_ID=#{tutorId}

</select>

Here we are fetching the tutor along with the courses' details using a single Select

query with JOINS. The <collection> element's resultMap is set to the resultMap

ID CourseResult that contains the mapping for the Course object's properties.

One-to-many mapping with nested select

We can get the tutor along with the courses' details using a nested select query

as follows:

<resultMap type="Course" id="CourseResult">

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

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

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

<result column="start_date" property="startDate"/>

<result column="end_date" property="endDate"/>

</resultMap>

<resultMap type="Tutor" id="TutorResult">

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

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

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

<association property="address" resultMap="AddressResult"/>

<collection property="courses" column="tutor_id"

select="findCoursesByTutor"/>

</resultMap>

<select id="findTutorById" parameterType="int"

resultMap="TutorResult">

SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL

FROM TUTORS T WHERE T.TUTOR_ID=#{tutorId}

</select>

<select id="findCoursesByTutor" parameterType="int"

resultMap="CourseResult">

SELECT * FROM COURSES WHERE TUTOR_ID=#{tutorId}

</select>

Dynamic SQL

Thew If condition

<select id="searchCourses" parameterType="hashmap"

resultMap="CourseResult">

SELECT * FROM COURSES

WHERE TUTOR_ID= #{tutorId}

<if test="courseName != null">

AND NAME LIKE #{courseName}

</if>

<if test="startDate != null">

AND START_DATE >= #{startDate}

</if>

<if test="endDate != null">

AND END_DATE <= #{endDate}

</if>

</select>

<select id="searchCourses" parameterType="hashmap"

resultMap="CourseResult">

SELECT * FROM COURSES

<choose>

<when test="searchBy == 'Tutor'">

WHERE TUTOR_ID= #{tutorId}

</when>

<when test="searchBy == 'CourseName'">

WHERE name like #{courseName}

</when>

<otherwise>

WHERE TUTOR start_date >= now()

</otherwise>

</choose>

</select>

The where condition

<select id="searchCourses" parameterType="hashmap"

resultMap="CourseResult">

SELECT * FROM COURSES

<where>

<if test=" tutorId != null ">

TUTOR_ID= #{tutorId}

</if>

<if test="courseName != null">

AND name like #{courseName}

</if>

<if test="startDate != null">

AND start_date >= #{startDate}

</if>

<if test="endDate != null">

AND end_date <= #{endDate}

</if>

</where>

</select>

The trim condition

<select id="searchCourses" parameterType="hashmap"

resultMap="CourseResult">

SELECT * FROM COURSES

<trim prefix="WHERE" prefixOverrides="AND | OR">

<if test=" tutorId != null ">

TUTOR_ID= #{tutorId}

</if>

<if test="courseName != null">

AND name like #{courseName}

</if>

</trim>

</select>

The foreach loop

<select id="searchCoursesByTutors" parameterType="map"

resultMap="CourseResult">

SELECT * FROM COURSES

<if test="tutorIds != null">

<where>

<foreach item="tutorId" collection="tutorIds">

OR tutor_id=#{tutorId}

</foreach>

</where>

</if>

</select>

<select id="searchCoursesByTutors" parameterType="map"

resultMap="CourseResult">

SELECT * FROM COURSES

<if test="tutorIds != null">

<where>

tutor_id IN

<foreach item="tutorId" collection="tutorIds"

open="(" separator="," close=")">

#{tutorId}

</foreach>

</where>

</if>

</select>

The set condition

<update id="updateStudent" parameterType="Student">

update students

<set>

<if test="name != null">name=#{name},</if>

<if test="email != null">email=#{email},</if>

<if test="phone != null">phone=#{phone},</if>

</set>

where stud_id=#{id}

</update>

Handling the CLOB/BLOB types

CREATE TABLE USER_PICS

(

ID INT(11) NOT NULL AUTO_INCREMENT,

NAME VARCHAR(50) DEFAULT NULL,

PIC BLOB,

BIO LONGTEXT,

PRIMARY KEY (ID)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;

By default, MyBatis maps CLOB type columns to the java.lang.String type

and BLOB type columns to the byte[] type.

public class UserPic

{

private int id;

private String name;

private byte[] pic;

private String bio;

//setters & getters

}

<insert id="insertUserPic" parameterType="UserPic">

INSERT INTO USER_PICS(NAME, PIC,BIO)

VALUES(#{name},#{pic},#{bio})

</insert>

<select id="getUserPic" parameterType="int" resultType="UserPic">

SELECT * FROM USER_PICS WHERE ID=#{id}

</select>

public void insertUserPic()

{

byte[] pic = null;

try {

File file = new File("C:\\Images\\UserImg.jpg");

InputStream is = new FileInputStream(file);

pic = new byte[is.available()];

is.read(pic);

is.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

String name = "UserName";

String bio = "put some lenghty bio here";

UserPic userPic = new UserPic(0, name, pic , bio);

SqlSession sqlSession = MyBatisUtil.openSession();

try {

UserPicMapper mapper =

sqlSession.getMapper(UserPicMapper.class);

mapper.insertUserPic(userPic);

sqlSession.commit();

}

finally {

sqlSession.close();

}

public void getUserPic()

{

UserPic userPic = null;

SqlSession sqlSession = MyBatisUtil.openSession();

try {

UserPicMapper mapper =

sqlSession.getMapper(UserPicMapper.class);

userPic = mapper.getUserPic(1);

}

finally {

sqlSession.close();

}

byte[] pic = userPic.getPic();

try {

OutputStream os = new FileOutputStream(new

File("C:\\Images\\UserImage_FromDB.jpg"));

os.write(pic);

os.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

}

Paginated ResultSets using RowBounds

Sometimes, we may need to work with huge volumes of data, such as with tables with

millions of records. Loading all these records may not be possible due to memory

constraints, or we may need only a fragment of data. Typically in web applications,

pagination is used to display large volumes of data in a page-by-page style.

MyBatis can load table data page by page using RowBounds. The RowBounds object

can be constructed using the offset and limit parameters. The parameter offset

refers to the starting position and limit refers to the number of records.

Suppose if you want to load and display 25 student records per page, you can use

the following query:

<select id="findAllStudents" resultMap="StudentResult">

select * from Students

</select>

Then, you can load the first page (first 25 records) as follows:

int offset =0 , limit =25;

RowBounds rowBounds = new RowBounds(offset, limit);

List<Student> = studentMapper.getStudents(rowBounds);

To display the second page, use offset=25 and limit=25; for the third page,

use offset=50 and limit=25.