http://www.javacodegeeks.com/2012/11/mybatis-tutorial-crud-operations-and-mapping-relationships-part-1.html
CRUD Operations
MyBatis is an SQL Mapper tool which greatly simplifies the database programing when compared to using JDBC directly.
Step1: Create a Maven project and configure MyBatis dependencies.
01 | < project xmlns = ‘http://maven.apache.org/POM/4.0.0‘ |
02 | xmlns:xsi = ‘http://www.w3.org/2001/XMLSchema-instance‘ |
03 | xsi:schemaLocation=‘http://maven.apache.org/POM/4.0.0 |
05 | http://maven.apache.org/xsd/maven-4.0.0.xsd‘> |
07 | < modelVersion >4.0.0</ modelVersion > |
09 | < groupId >com.sivalabs</ groupId > |
10 | < artifactId >mybatis-demo</ artifactId > |
11 | < version >0.0.1-SNAPSHOT</ version > |
12 | < packaging >jar</ packaging > |
14 | < name >mybatis-demo</ name > |
15 | < url >http://maven.apache.org</ url > |
18 | < project.build.sourceEncoding >UTF-8</ project.build.sourceEncoding > |
24 | < groupId >org.apache.maven.plugins</ groupId > |
25 | < artifactId >maven-compiler-plugin</ artifactId > |
26 | < version >2.3.2</ version > |
30 | < encoding >${project.build.sourceEncoding}</ encoding > |
38 | < groupId >junit</ groupId > |
39 | < artifactId >junit</ artifactId > |
40 | < version >4.10</ version > |
45 | < groupId >org.mybatis</ groupId > |
46 | < artifactId >mybatis</ artifactId > |
47 | < version >3.1.1</ version > |
50 | < groupId >mysql</ groupId > |
51 | < artifactId >mysql-connector-java</ artifactId > |
52 | < version >5.1.21</ version > |
53 | < scope >runtime</ scope > |
Step#2: Create the table USER and a Java domain Object User as follows:
2 | user_id int ( 10 ) unsigned NOT NULL auto_increment, |
3 | email_id varchar( 45 ) NOT NULL, |
4 | password varchar( 45 ) NOT NULL, |
5 | first_name varchar( 45 ) NOT NULL, |
6 | last_name varchar( 45 ) default NULL, |
8 | UNIQUE KEY Index_2_email_uniq (email_id) |
9 | ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
01 | package com.sivalabs.mybatisdemo.domain; |
04 | private Integer userId; |
05 | private String emailId; |
06 | private String password; |
07 | private String firstName; |
08 | private String lastName; |
11 | public String toString() { |
12 | return ‘User [userId=‘ + userId + ‘, emailId=‘ + emailId |
13 | + ‘, password=‘ + password + ‘, firstName=‘ + firstName |
14 | + ‘, lastName=‘ + lastName + ‘]‘ ; |
Step#3: Create MyBatis configuration files.
a) Create jdbc.properties file in src/main/resources folder
1 | jdbc.driverClassName=com.mysql.jdbc.Driver |
2 | jdbc.url=jdbc:mysql://localhost:3306/mybatis-demo |
b) Create mybatis-config.xml file in src/main/resources folder
01 | <? xml version = ‘1.0‘ encoding = ‘UTF-8‘ ?> |
02 | <!DOCTYPE configuration |
03 | PUBLIC ‘-//mybatis.org//DTD Config 3.0//EN‘ |
04 | ‘http://mybatis.org/dtd/mybatis-3-config.dtd‘> |
06 | < properties resource = ‘jdbc.properties‘ /> |
08 | < typeAlias type = ‘com.sivalabs.mybatisdemo.domain.User‘ alias = ‘User‘ ></ typeAlias > |
10 | < environments default = ‘development‘ > |
11 | < environment id = ‘development‘ > |
12 | < transactionManager type = ‘JDBC‘ /> |
13 | < dataSource type = ‘POOLED‘ > |
14 | < property name = ‘driver‘ value = ‘${jdbc.driverClassName}‘ /> |
15 | < property name = ‘url‘ value = ‘${jdbc.url}‘ /> |
16 | < property name = ‘username‘ value = ‘${jdbc.username}‘ /> |
17 | < property name = ‘password‘ value = ‘${jdbc.password}‘ /> |
22 | < mapper resource = ‘com/sivalabs/mybatisdemo/mappers/UserMapper.xml‘ /> |
Step#4: Create an interface UserMapper.java in src/main/java folder in com.sivalabs.mybatisdemo.mappers package.
01 | package com.sivalabs.mybatisdemo.mappers; |
04 | import com.sivalabs.mybatisdemo.domain.User; |
06 | public interface UserMapper |
09 | public void insertUser(User user); |
11 | public User getUserById(Integer userId); |
13 | public List<User> getAllUsers(); |
15 | public void updateUser(User user); |
17 | public void deleteUser(Integer userId); |
Step#5: Create UserMapper.xml file in src/main/resources folder in com.sivalabs.mybatisdemo.mappers package.
01 | <? xml version = ‘1.0‘ encoding = ‘UTF-8‘ ?> |
02 | <!DOCTYPE mapper PUBLIC ‘-//mybatis.org//DTD Mapper 3.0//EN‘ |
03 | ‘http://mybatis.org/dtd/mybatis-3-mapper.dtd‘> |
05 | < mapper namespace = ‘com.sivalabs.mybatisdemo.mappers.UserMapper‘ > |
07 | < select id = ‘getUserById‘ parameterType = ‘int‘ resultType = ‘com.sivalabs.mybatisdemo.domain.User‘ > |
12 | first_name as firstName, |
15 | WHERE USER_ID = #{userId} |
18 | < resultMap type = ‘User‘ id = ‘UserResult‘ > |
19 | < id property = ‘userId‘ column = ‘user_id‘ /> |
20 | < result property = ‘emailId‘ column = ‘email_id‘ /> |
21 | < result property = ‘password‘ column = ‘password‘ /> |
22 | < result property = ‘firstName‘ column = ‘first_name‘ /> |
23 | < result property = ‘lastName‘ column = ‘last_name‘ /> |
26 | < select id = ‘getAllUsers‘ resultMap = ‘UserResult‘ > |
30 | < insert id = ‘insertUser‘ parameterType = ‘User‘ useGeneratedKeys = ‘true‘ keyProperty = ‘userId‘ > |
31 | INSERT INTO USER(email_id, password, first_name, last_name) |
32 | VALUES(#{emailId}, #{password}, #{firstName}, #{lastName}) |
35 | < update id = ‘updateUser‘ parameterType = ‘User‘ > |
38 | PASSWORD= #{password}, |
39 | FIRST_NAME = #{firstName}, |
40 | LAST_NAME = #{lastName} |
41 | WHERE USER_ID = #{userId} |
44 | < delete id = ‘deleteUser‘ parameterType = ‘int‘ > |
45 | DELETE FROM USER WHERE USER_ID = #{userId} |
Step#6: Create MyBatisUtil.java to instantiate SqlSessionFactory.
01 | package com.sivalabs.mybatisdemo.service; |
03 | import java.io.IOException; |
05 | import org.apache.ibatis.io.Resources; |
06 | import org.apache.ibatis.session.SqlSessionFactory; |
07 | import org.apache.ibatis.session.SqlSessionFactoryBuilder; |
09 | public class MyBatisUtil |
11 | private static SqlSessionFactory factory; |
13 | private MyBatisUtil() { |
20 | reader = Resources.getResourceAsReader( ‘mybatis-config.xml‘ ); |
21 | } catch (IOException e) { |
22 | throw new RuntimeException(e.getMessage()); |
24 | factory = new SqlSessionFactoryBuilder().build(reader); |
27 | public static SqlSessionFactory getSqlSessionFactory() |
Step#7: Create UserService.java in src/main/java folder.
01 | package com.sivalabs.mybatisdemo.service; |
04 | import org.apache.ibatis.session.SqlSession; |
05 | import com.sivalabs.mybatisdemo.domain.User; |
06 | import com.sivalabs.mybatisdemo.mappers.UserMapper; |
08 | public class UserService |
11 | public void insertUser(User user) { |
12 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
14 | UserMapper userMapper = sqlSession.getMapper(UserMapper. class ); |
15 | userMapper.insertUser(user); |
22 | public User getUserById(Integer userId) { |
23 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
25 | UserMapper userMapper = sqlSession.getMapper(UserMapper. class ); |
26 | return userMapper.getUserById(userId); |
32 | public List<User> getAllUsers() { |
33 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
35 | UserMapper userMapper = sqlSession.getMapper(UserMapper. class ); |
36 | return userMapper.getAllUsers(); |
42 | public void updateUser(User user) { |
43 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
45 | UserMapper userMapper = sqlSession.getMapper(UserMapper. class ); |
46 | userMapper.updateUser(user); |
54 | public void deleteUser(Integer userId) { |
55 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
57 | UserMapper userMapper = sqlSession.getMapper(UserMapper. class ); |
58 | userMapper.deleteUser(userId); |
Step#8: Create a JUnit Test class to test UserService methods.
01 | package com.sivalabs.mybatisdemo; |
05 | import org.junit.AfterClass; |
06 | import org.junit.Assert; |
07 | import org.junit.BeforeClass; |
10 | import com.sivalabs.mybatisdemo.domain.User; |
11 | import com.sivalabs.mybatisdemo.service.UserService; |
13 | public class UserServiceTest |
15 | private static UserService userService; |
18 | public static void setup() |
20 | userService = new UserService(); |
24 | public static void teardown() |
30 | public void testGetUserById() |
32 | User user = userService.getUserById( 1 ); |
33 | Assert.assertNotNull(user); |
34 | System.out.println(user); |
38 | public void testGetAllUsers() |
40 | List<User> users = userService.getAllUsers(); |
41 | Assert.assertNotNull(users); |
42 | for (User user : users) |
44 | System.out.println(user); |
50 | public void testInsertUser() |
52 | User user = new User(); |
53 | user.setEmailId( ‘test_email_‘ +System.currentTimeMillis()+ ‘@gmail.com‘ ); |
54 | user.setPassword( ‘secret‘ ); |
55 | user.setFirstName( ‘TestFirstName‘ ); |
56 | user.setLastName( ‘TestLastName‘ ); |
58 | userService.insertUser(user); |
59 | Assert.assertTrue(user.getUserId() != 0 ); |
60 | User createdUser = userService.getUserById(user.getUserId()); |
61 | Assert.assertNotNull(createdUser); |
62 | Assert.assertEquals(user.getEmailId(), createdUser.getEmailId()); |
63 | Assert.assertEquals(user.getPassword(), createdUser.getPassword()); |
64 | Assert.assertEquals(user.getFirstName(), createdUser.getFirstName()); |
65 | Assert.assertEquals(user.getLastName(), createdUser.getLastName()); |
70 | public void testUpdateUser() |
72 | long timestamp = System.currentTimeMillis(); |
73 | User user = userService.getUserById( 2 ); |
74 | user.setFirstName( ‘TestFirstName‘ +timestamp); |
75 | user.setLastName( ‘TestLastName‘ +timestamp); |
76 | userService.updateUser(user); |
77 | User updatedUser = userService.getUserById( 2 ); |
78 | Assert.assertEquals(user.getFirstName(), updatedUser.getFirstName()); |
79 | Assert.assertEquals(user.getLastName(), updatedUser.getLastName()); |
83 | public void testDeleteUser() |
85 | User user = userService.getUserById( 4 ); |
86 | userService.deleteUser(user.getUserId()); |
87 | User deletedUser = userService.getUserById( 4 ); |
88 | Assert.assertNull(deletedUser); |
Now, I will explain how to perform CRUD operations using MyBatis Annotation support without need of Queries configuration in XML mapper files.
Step#1: Create a table BLOG and a java domain Object Blog.
2 | blog_id int ( 10 ) unsigned NOT NULL auto_increment, |
3 | blog_name varchar( 45 ) NOT NULL, |
4 | created_on datetime NOT NULL, |
6 | ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
01 | package com.sivalabs.mybatisdemo.domain; |
07 | private Integer blogId; |
08 | private String blogName; |
09 | private Date createdOn; |
12 | public String toString() { |
13 | return ‘Blog [blogId=‘ + blogId + ‘, blogName=‘ + blogName |
14 | + ‘, createdOn=‘ + createdOn + ‘]‘ ; |
Step#2: Create UserMapper.java interface with SQL queries in Annotations.
01 | package com.sivalabs.mybatisdemo.mappers; |
05 | import org.apache.ibatis.annotations.Delete; |
06 | import org.apache.ibatis.annotations.Insert; |
07 | import org.apache.ibatis.annotations.Options; |
08 | import org.apache.ibatis.annotations.Result; |
09 | import org.apache.ibatis.annotations.Results; |
10 | import org.apache.ibatis.annotations.Select; |
11 | import org.apache.ibatis.annotations.Update; |
13 | import com.sivalabs.mybatisdemo.domain.Blog; |
15 | public interface BlogMapper |
17 | @Insert ( ‘INSERT INTO BLOG(BLOG_NAME, CREATED_ON) VALUES(#{blogName}, #{createdOn})‘ ) |
18 | @Options (useGeneratedKeys= true , keyProperty= ‘blogId‘ ) |
19 | public void insertBlog(Blog blog); |
21 | @Select ( ‘SELECT BLOG_ID AS blogId, BLOG_NAME as blogName, CREATED_ON as createdOn FROM BLOG WHERE BLOG_ID=#{blogId}‘ ) |
22 | public Blog getBlogById(Integer blogId); |
24 | @Select ( ‘SELECT * FROM BLOG ‘ ) |
26 | @Result (id= true , property= ‘blogId‘ , column= ‘BLOG_ID‘ ), |
27 | @Result (property= ‘blogName‘ , column= ‘BLOG_NAME‘ ), |
28 | @Result (property= ‘createdOn‘ , column= ‘CREATED_ON‘ ) |
30 | public List<Blog> getAllBlogs(); |
32 | @Update ( ‘UPDATE BLOG SET BLOG_NAME=#{blogName}, CREATED_ON=#{createdOn} WHERE BLOG_ID=#{blogId}‘ ) |
33 | public void updateBlog(Blog blog); |
35 | @Delete ( ‘DELETE FROM BLOG WHERE BLOG_ID=#{blogId}‘ ) |
36 | public void deleteBlog(Integer blogId); |
Step#3: Configure BlogMapper in mybatis-config.xml
01 | <? xml version = ‘1.0‘ encoding = ‘UTF-8‘ ?> |
02 | <!DOCTYPE configuration |
03 | PUBLIC ‘-//mybatis.org//DTD Config 3.0//EN‘ |
04 | ‘http://mybatis.org/dtd/mybatis-3-config.dtd‘> |
06 | < properties resource = ‘jdbc.properties‘ /> |
07 | < environments default = ‘development‘ > |
08 | < environment id = ‘development‘ > |
09 | < transactionManager type = ‘JDBC‘ /> |
10 | < dataSource type = ‘POOLED‘ > |
15 | < property name = ‘driver‘ value = ‘${jdbc.driverClassName}‘ /> |
16 | < property name = ‘url‘ value = ‘${jdbc.url}‘ /> |
17 | < property name = ‘username‘ value = ‘${jdbc.username}‘ /> |
18 | < property name = ‘password‘ value = ‘${jdbc.password}‘ /> |
23 | < mapper class = ‘com.sivalabs.mybatisdemo.mappers.BlogMapper‘ /> |
Step#4: Create BlogService.java
01 | package com.sivalabs.mybatisdemo.service; |
05 | import org.apache.ibatis.session.SqlSession; |
07 | import com.sivalabs.mybatisdemo.domain.Blog; |
08 | import com.sivalabs.mybatisdemo.mappers.BlogMapper; |
10 | public class BlogService |
13 | public void insertBlog(Blog blog) { |
14 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
16 | BlogMapper blogMapper = sqlSession.getMapper(BlogMapper. class ); |
17 | blogMapper.insertBlog(blog); |
24 | public Blog getBlogById(Integer blogId) { |
25 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
27 | BlogMapper blogMapper = sqlSession.getMapper(BlogMapper. class ); |
28 | return blogMapper.getBlogById(blogId); |
34 | public List<Blog> getAllBlogs() { |
35 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
37 | BlogMapper blogMapper = sqlSession.getMapper(BlogMapper. class ); |
38 | return blogMapper.getAllBlogs(); |
44 | public void updateBlog(Blog blog) { |
45 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
47 | BlogMapper blogMapper = sqlSession.getMapper(BlogMapper. class ); |
48 | blogMapper.updateBlog(blog); |
55 | public void deleteBlog(Integer blogId) { |
56 | SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(); |
58 | BlogMapper blogMapper = sqlSession.getMapper(BlogMapper. class ); |
59 | blogMapper.deleteBlog(blogId); |
Step#5: Create JUnit Test for BlogService methods
01 | package com.sivalabs.mybatisdemo; |
06 | import org.junit.AfterClass; |
07 | import org.junit.Assert; |
08 | import org.junit.BeforeClass; |
11 | import com.sivalabs.mybatisdemo.domain.Blog; |
12 | import com.sivalabs.mybatisdemo.service.BlogService; |
14 | public class BlogServiceTest |
16 | private static BlogService blogService; |
19 | public static void setup() |
21 | blogService = new BlogService(); |
25 | public static void teardown() |
31 | public void testGetBlogById() |
33 | Blog blog = blogService.getBlogById( 1 ); |
34 | Assert.assertNotNull(blog); |
35 | System.out.println(blog); |
39 | public void testGetAllBlogs() |
41 | List<Blog> blogs = blogService.getAllBlogs(); |
42 | Assert.assertNotNull(blogs); |
43 | for (Blog blog : blogs) |
45 | System.out.println(blog); |
51 | public void testInsertBlog() |
53 | Blog blog = new Blog(); |
54 | blog.setBlogName( ‘test_blog_‘ +System.currentTimeMillis()); |
55 | blog.setCreatedOn( new Date()); |
57 | blogService.insertBlog(blog); |
58 | Assert.assertTrue(blog.getBlogId() != 0 ); |
59 | Blog createdBlog = blogService.getBlogById(blog.getBlogId()); |
60 | Assert.assertNotNull(createdBlog); |
61 | Assert.assertEquals(blog.getBlogName(), createdBlog.getBlogName()); |
66 | public void testUpdateBlog() |
68 | long timestamp = System.currentTimeMillis(); |
69 | Blog blog = blogService.getBlogById( 2 ); |
70 | blog.setBlogName( ‘TestBlogName‘ +timestamp); |
71 | blogService.updateBlog(blog); |
72 | Blog updatedBlog = blogService.getBlogById( 2 ); |
73 | Assert.assertEquals(blog.getBlogName(), updatedBlog.getBlogName()); |
77 | public void testDeleteBlog() |
79 | Blog blog = blogService.getBlogById( 4 ); |
80 | blogService.deleteBlog(blog.getBlogId()); |
81 | Blog deletedBlog = blogService.getBlogById( 4 ); |
82 | Assert.assertNull(deletedBlog); |
MyBatis Tutorial – CRUD Operations and Mapping Relationships – Part 1---- reference