2016년 2월 2일 화요일

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 &gt;= 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 &gt;= #{startDate}
</if>
<if test="endDate != null">
AND end_date &lt;= #{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.

Which Mobile App Development Option is Better?

Different alternatives to native code development have their own advantages and philosophy behind. No one tool or approach can be clearly ma...