首页 > 代码库 > MyBatis Tutorial – CRUD Operations and Mapping Relationships – Part 1---- reference

MyBatis Tutorial – CRUD Operations and Mapping Relationships – Part 1---- reference

2024-08-08 06:24:44   220人阅读

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
04 
05http://maven.apache.org/xsd/maven-4.0.0.xsd‘>
06 
07 <modelVersion>4.0.0</modelVersion>
08 
09 <groupId>com.sivalabs</groupId>
10 <artifactId>mybatis-demo</artifactId>
11 <version>0.0.1-SNAPSHOT</version>
12 <packaging>jar</packaging>
13 
14 <name>mybatis-demo</name>
15 <url>http://maven.apache.org</url>
16 
17 <properties>
18  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
19 </properties>
20 
21 <build>
22  <plugins>
23   <plugin>
24    <groupId>org.apache.maven.plugins</groupId>
25    <artifactId>maven-compiler-plugin</artifactId>
26    <version>2.3.2</version>
27    <configuration>
28     <source>1.6</source>
29     <target>1.6</target>
30     <encoding>${project.build.sourceEncoding}</encoding>
31    </configuration>
32   </plugin>
33  </plugins>
34 </build>
35 
36 <dependencies>
37  <dependency>
38   <groupId>junit</groupId>
39   <artifactId>junit</artifactId>
40   <version>4.10</version>
41   <scope>test</scope>
42  </dependency>
43 
44  <dependency>
45      <groupId>org.mybatis</groupId>
46      <artifactId>mybatis</artifactId>
47      <version>3.1.1</version>
48  </dependency>
49  <dependency>
50             <groupId>mysql</groupId>
51             <artifactId>mysql-connector-java</artifactId>
52             <version>5.1.21</version>
53             <scope>runtime</scope>
54         </dependency>
55 </dependencies>
56</project>

 
Step#2: Create the table USER and a Java domain Object User as follows:
 

1CREATE TABLE  user (
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,
7  PRIMARY KEY  (user_id),
8  UNIQUE KEY Index_2_email_uniq (email_id)
9) ENGINE=InnoDB DEFAULT CHARSET=latin1;
01package com.sivalabs.mybatisdemo.domain;
02public class User
03{
04 private Integer userId;
05 private String emailId;
06 private String password;
07 private String firstName;
08 private String lastName;
09 
10 @Override
11 public String toString() {
12  return ‘User [userId=‘ + userId + ‘, emailId=‘ + emailId
13    + ‘, password=‘ + password + ‘, firstName=‘ + firstName
14    + ‘, lastName=‘ + lastName + ‘]‘;
15 }
16 //setters and getters
17}

 
Step#3: Create MyBatis configuration files.

a) Create jdbc.properties file in src/main/resources folder

1jdbc.driverClassName=com.mysql.jdbc.Driver
2jdbc.url=jdbc:mysql://localhost:3306/mybatis-demo
3jdbc.username=root
4jdbc.password=admin

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‘>
05<configuration>
06 <properties resource=‘jdbc.properties‘/>
07 <typeAliases>
08  <typeAlias type=‘com.sivalabs.mybatisdemo.domain.User‘ alias=‘User‘></typeAlias>
09 </typeAliases>
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}‘/>
18    </dataSource>
19  </environment>
20  </environments>
21  <mappers>
22 <mapper resource=‘com/sivalabs/mybatisdemo/mappers/UserMapper.xml‘/>
23  </mappers>
24</configuration>

 
Step#4: Create an interface UserMapper.java in src/main/java folder in com.sivalabs.mybatisdemo.mappers package.
 

01package com.sivalabs.mybatisdemo.mappers;
02 
03import java.util.List;
04import com.sivalabs.mybatisdemo.domain.User;
05 
06public interface UserMapper
07{
08 
09 public void insertUser(User user);
10 
11 public User getUserById(Integer userId);
12 
13 public List<User> getAllUsers();
14 
15 public void updateUser(User user);
16 
17 public void deleteUser(Integer userId);
18 
19}

 
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‘>
04 
05<mapper namespace=‘com.sivalabs.mybatisdemo.mappers.UserMapper‘>
06 
07  <select id=‘getUserById‘ parameterType=‘int‘ resultType=‘com.sivalabs.mybatisdemo.domain.User‘>
08     SELECT
09      user_id as userId,
10      email_id as emailId ,
11      password,
12      first_name as firstName,
13      last_name as lastName
14     FROM USER
15     WHERE USER_ID = #{userId}
16  </select>
17  <!-- Instead of referencing Fully Qualified Class Names we can register Aliases in mybatis-config.xml and use Alias names. -->
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‘/>  
24   </resultMap>
25 
26  <select id=‘getAllUsers‘ resultMap=‘UserResult‘>
27   SELECT * FROM USER
28  </select>
29 
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})
33  </insert>
34 
35  <update id=‘updateUser‘ parameterType=‘User‘>
36    UPDATE USER
37    SET
38     PASSWORD= #{password},
39     FIRST_NAME = #{firstName},
40     LAST_NAME = #{lastName}
41    WHERE USER_ID = #{userId}
42  </update>
43 
44  <delete id=‘deleteUser‘ parameterType=‘int‘>
45    DELETE FROM USER WHERE USER_ID = #{userId}
46  </delete>
47 
48</mapper>

 
Step#6: Create MyBatisUtil.java to instantiate SqlSessionFactory.
 

01package com.sivalabs.mybatisdemo.service;
02 
03import java.io.IOException;
04import java.io.Reader;
05import org.apache.ibatis.io.Resources;
06import org.apache.ibatis.session.SqlSessionFactory;
07import org.apache.ibatis.session.SqlSessionFactoryBuilder;
08 
09public class MyBatisUtil
10{
11 private static SqlSessionFactory factory;
12 
13 private MyBatisUtil() {
14 }
15 
16 static
17 {
18  Reader reader = null;
19  try {
20   reader = Resources.getResourceAsReader(‘mybatis-config.xml‘);
21  } catch (IOException e) {
22   throw new RuntimeException(e.getMessage());
23  }
24  factory = new SqlSessionFactoryBuilder().build(reader);
25 }
26 
27 public static SqlSessionFactory getSqlSessionFactory()
28 {
29  return factory;
30 }
31}

 
Step#7: Create UserService.java in src/main/java folder.
 

01package com.sivalabs.mybatisdemo.service;
02 
03import java.util.List;
04import org.apache.ibatis.session.SqlSession;
05import com.sivalabs.mybatisdemo.domain.User;
06import com.sivalabs.mybatisdemo.mappers.UserMapper;
07 
08public class UserService
09{
10 
11 public void insertUser(User user) {
12  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
13  try{
14  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
15  userMapper.insertUser(user);
16  sqlSession.commit();
17  }finally{
18   sqlSession.close();
19  }
20 }
21 
22 public User getUserById(Integer userId) {
23  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
24  try{
25  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
26  return userMapper.getUserById(userId);
27  }finally{
28   sqlSession.close();
29  }
30 }
31 
32 public List<User> getAllUsers() {
33  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
34  try{
35  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
36  return userMapper.getAllUsers();
37  }finally{
38   sqlSession.close();
39  }
40 }
41 
42 public void updateUser(User user) {
43  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
44  try{
45  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
46  userMapper.updateUser(user);
47  sqlSession.commit();
48  }finally{
49   sqlSession.close();
50  }
51 
52 }
53 
54 public void deleteUser(Integer userId) {
55  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
56  try{
57  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
58  userMapper.deleteUser(userId);
59  sqlSession.commit();
60  }finally{
61   sqlSession.close();
62  }
63 
64 }
65 
66}

 
Step#8: Create a JUnit Test class to test UserService methods.
 

01package com.sivalabs.mybatisdemo;
02 
03import java.util.List;
04 
05import org.junit.AfterClass;
06import org.junit.Assert;
07import org.junit.BeforeClass;
08import org.junit.Test;
09 
10import com.sivalabs.mybatisdemo.domain.User;
11import com.sivalabs.mybatisdemo.service.UserService;
12 
13public class UserServiceTest
14{
15 private static UserService userService;
16 
17 @BeforeClass
18    public static void setup()
19 {
20  userService = new UserService();
21 }
22 
23 @AfterClass
24    public static void teardown()
25 {
26  userService = null;
27 }
28 
29    @Test
30 public void testGetUserById()
31 {
32  User user = userService.getUserById(1);
33  Assert.assertNotNull(user);
34  System.out.println(user);
35 }
36 
37    @Test
38    public void testGetAllUsers()
39    {
40  List<User> users = userService.getAllUsers();
41  Assert.assertNotNull(users);
42  for (User user : users)
43  {
44   System.out.println(user);
45  }
46 
47 }
48 
49    @Test
50    public void testInsertUser()
51    {
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‘);
57 
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());
66 
67 }
68 
69    @Test
70    public void testUpdateUser()
71    {
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());
80 }
81 
82   @Test
83   public void testDeleteUser()
84   {
85     User user = userService.getUserById(4);
86     userService.deleteUser(user.getUserId());
87  User deletedUser = userService.getUserById(4);
88  Assert.assertNull(deletedUser);  
89 
90 }
91}

 
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.
 

1CREATE TABLE  blog (
2  blog_id int(10) unsigned NOT NULL auto_increment,
3  blog_name varchar(45) NOT NULL,
4  created_on datetime NOT NULL,
5  PRIMARY KEY  (blog_id)
6) ENGINE=InnoDB DEFAULT CHARSET=latin1;
01package com.sivalabs.mybatisdemo.domain;
02 
03import java.util.Date;
04 
05public class Blog {
06 
07 private Integer blogId;
08 private String blogName;
09 private Date createdOn;
10 
11 @Override
12 public String toString() {
13  return ‘Blog [blogId=‘ + blogId + ‘, blogName=‘ + blogName
14    + ‘, createdOn=‘ + createdOn + ‘]‘;
15 }
16 //Seeters and getters
17}

 
Step#2: Create UserMapper.java interface with SQL queries in Annotations.
 

01package com.sivalabs.mybatisdemo.mappers;
02 
03import java.util.List;
04 
05import org.apache.ibatis.annotations.Delete;
06import org.apache.ibatis.annotations.Insert;
07import org.apache.ibatis.annotations.Options;
08import org.apache.ibatis.annotations.Result;
09import org.apache.ibatis.annotations.Results;
10import org.apache.ibatis.annotations.Select;
11import org.apache.ibatis.annotations.Update;
12 
13import com.sivalabs.mybatisdemo.domain.Blog;
14 
15public interface BlogMapper
16{
17 @Insert(‘INSERT INTO BLOG(BLOG_NAME, CREATED_ON) VALUES(#{blogName}, #{createdOn})‘)
18 @Options(useGeneratedKeys=true, keyProperty=‘blogId‘)
19 public void insertBlog(Blog blog);
20 
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);
23 
24 @Select(‘SELECT * FROM BLOG ‘)
25 @Results({
26  @Result(id=true, property=‘blogId‘, column=‘BLOG_ID‘),
27  @Result(property=‘blogName‘, column=‘BLOG_NAME‘),
28  @Result(property=‘createdOn‘, column=‘CREATED_ON‘) 
29 })
30 public List<Blog> getAllBlogs();
31 
32 @Update(‘UPDATE BLOG SET BLOG_NAME=#{blogName}, CREATED_ON=#{createdOn} WHERE BLOG_ID=#{blogId}‘)
33 public void updateBlog(Blog blog);
34 
35 @Delete(‘DELETE FROM BLOG WHERE BLOG_ID=#{blogId}‘)
36 public void deleteBlog(Integer blogId);
37 
38}

 
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‘>
05<configuration>
06 <properties resource=‘jdbc.properties‘/>
07 <environments default=‘development‘>
08  <environment id=‘development‘>
09    <transactionManager type=‘JDBC‘/>
10    <dataSource type=‘POOLED‘>
11   <!-- <property name=‘driver‘ value=http://www.mamicode.com/‘com.mysql.jdbc.Driver‘/>
12   <property name=‘url‘ value=http://www.mamicode.com/‘jdbc:mysql://localhost:3306/mybatis-demo‘/>
13   <property name=‘username‘ value=http://www.mamicode.com/‘root‘/>
14   <property name=‘password‘ value=http://www.mamicode.com/‘admin‘/> -->
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}‘/>
19    </dataSource>
20  </environment>
21  </environments>
22  <mappers>
23    <mapper class=‘com.sivalabs.mybatisdemo.mappers.BlogMapper‘/>
24  </mappers>
25</configuration>

 
Step#4: Create BlogService.java
 

01package com.sivalabs.mybatisdemo.service;
02 
03import java.util.List;
04 
05import org.apache.ibatis.session.SqlSession;
06 
07import com.sivalabs.mybatisdemo.domain.Blog;
08import com.sivalabs.mybatisdemo.mappers.BlogMapper;
09 
10public class BlogService
11{
12 
13 public void insertBlog(Blog blog) {
14  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
15  try{
16  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
17  blogMapper.insertBlog(blog);
18  sqlSession.commit();
19  }finally{
20   sqlSession.close();
21  }
22 }
23 
24 public Blog getBlogById(Integer blogId) {
25  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
26  try{
27  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
28  return blogMapper.getBlogById(blogId);
29  }finally{
30   sqlSession.close();
31  }
32 }
33 
34 public List<Blog> getAllBlogs() {
35  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
36  try{
37  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
38  return blogMapper.getAllBlogs();
39  }finally{
40   sqlSession.close();
41  }
42 }
43 
44 public void updateBlog(Blog blog) {
45  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
46  try{
47  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
48  blogMapper.updateBlog(blog);
49  sqlSession.commit();
50  }finally{
51   sqlSession.close();
52  } 
53 }
54 
55 public void deleteBlog(Integer blogId) {
56  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
57  try{
58  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
59  blogMapper.deleteBlog(blogId);
60  sqlSession.commit();
61  }finally{
62   sqlSession.close();
63  }
64 
65 }
66 
67}

 
Step#5: Create JUnit Test for BlogService methods
 

01package com.sivalabs.mybatisdemo;
02 
03import java.util.Date;
04import java.util.List;
05 
06import org.junit.AfterClass;
07import org.junit.Assert;
08import org.junit.BeforeClass;
09import org.junit.Test;
10 
11import com.sivalabs.mybatisdemo.domain.Blog;
12import com.sivalabs.mybatisdemo.service.BlogService;
13 
14public class BlogServiceTest
15{
16 private static BlogService blogService;
17 
18 @BeforeClass
19    public static void setup()
20 {
21  blogService = new BlogService();
22 }
23 
24 @AfterClass
25    public static void teardown()
26 {
27  blogService = null;
28 }
29 
30    @Test
31 public void testGetBlogById()
32 {
33  Blog blog = blogService.getBlogById(1);
34  Assert.assertNotNull(blog);
35  System.out.println(blog);
36 }
37 
38    @Test
39    public void testGetAllBlogs()
40    {
41  List<Blog> blogs = blogService.getAllBlogs();
42  Assert.assertNotNull(blogs);
43  for (Blog blog : blogs)
44  {
45   System.out.println(blog);
46  }
47 
48 }
49 
50    @Test
51    public void testInsertBlog()
52    {
53     Blog blog = new Blog();
54     blog.setBlogName(‘test_blog_‘+System.currentTimeMillis());
55     blog.setCreatedOn(new Date());
56 
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());
62 
63 }
64 
65    @Test
66    public void testUpdateBlog()
67    {
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());
74 }
75 
76   @Test
77   public void testDeleteBlog()
78   {
79     Blog blog = blogService.getBlogById(4);
80     blogService.deleteBlog(blog.getBlogId());
81  Blog deletedBlog = blogService.getBlogById(4);
82  Assert.assertNull(deletedBlog);
83 }
84}

 

MyBatis Tutorial – CRUD Operations and Mapping Relationships – Part 1---- reference


联系
我们