Статьи

Учебное пособие по MyBatis — Операции CRUD и отображения отношений — Часть 1

Январь 10, 2019

CRUD Операции
MyBatis — это инструмент SQL Mapper, который значительно упрощает программирование базы данных по сравнению с использованием JDBC напрямую.

Шаг 1. Создайте проект Maven и настройте зависимости MyBatis.

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

Шаг № 2: Создайте таблицу USER и пользователь объекта Java домена следующим образом:

1
2
3
4
5
6
7
8
9
CREATE TABLE  user (
  user_id int(10) unsigned NOT NULL auto_increment,
  email_id varchar(45) NOT NULL,
  password varchar(45) NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) default NULL,
  PRIMARY KEY  (user_id),
  UNIQUE KEY Index_2_email_uniq (email_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
package com.sivalabs.mybatisdemo.domain;
public class User
{
 private Integer userId;
 private String emailId;
 private String password;
 private String firstName;
 private String lastName;
 
 @Override
 public String toString() {
  return 'User [userId=' + userId + ', emailId=' + emailId
    + ', password=' + password + ', firstName=' + firstName
    + ', lastName=' + lastName + ']';
 }
 //setters and getters
}

Шаг № 3: Создайте файлы конфигурации MyBatis.

а) Создайте файл jdbc.properties в папке src / main / resources

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

б) Создайте файл mybatis-config.xml в папке src / main / resources

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?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>
 <properties resource='jdbc.properties'/>
 <typeAliases>
  <typeAlias type='com.sivalabs.mybatisdemo.domain.User' alias='User'></typeAlias>
 </typeAliases>
 <environments default='development'>
  <environment id='development'>
    <transactionManager type='JDBC'/>
    <dataSource type='POOLED'>   
   <property name='driver' value='${jdbc.driverClassName}'/>
   <property name='url' value='${jdbc.url}'/>
   <property name='username' value='${jdbc.username}'/>
   <property name='password' value='${jdbc.password}'/>
    </dataSource>
  </environment>
  </environments>
  <mappers>
 <mapper resource='com/sivalabs/mybatisdemo/mappers/UserMapper.xml'/>
  </mappers>
</configuration>

Шаг № 4: Создайте интерфейс UserMapper.java в папке src / main / java в пакете com.sivalabs.mybatisdemo.mappers.

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

Шаг № 5: Создайте файл UserMapper.xml в папке src / main / resources в пакете com.sivalabs.mybatisdemo.mappers.

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

Шаг № 6: Создайте MyBatisUtil.java для создания экземпляра SqlSessionFactory.

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

Шаг № 7: Создайте UserService.java в папке src / main / java.

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

Шаг № 8: Создайте класс JUnit Test для тестирования методов UserService.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
package com.sivalabs.mybatisdemo;
 
import java.util.List;
 
import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
 
import com.sivalabs.mybatisdemo.domain.User;
import com.sivalabs.mybatisdemo.service.UserService;
 
public class UserServiceTest
{
 private static UserService userService;
 
 @BeforeClass
    public static void setup()
 {
  userService = new UserService();
 }
 
 @AfterClass
    public static void teardown()
 {
  userService = null;
 }
 
    @Test
 public void testGetUserById()
 {
  User user = userService.getUserById(1);
  Assert.assertNotNull(user);
  System.out.println(user);
 }
 
    @Test
    public void testGetAllUsers()
    {
  List<User> users = userService.getAllUsers();
  Assert.assertNotNull(users);
  for (User user : users)
  {
   System.out.println(user);
  }
 
 }
 
    @Test
    public void testInsertUser()
    {
     User user = new User();
     user.setEmailId('test_email_'+System.currentTimeMillis()+'@gmail.com');
     user.setPassword('secret');
     user.setFirstName('TestFirstName');
     user.setLastName('TestLastName');
 
     userService.insertUser(user);
  Assert.assertTrue(user.getUserId() != 0);
  User createdUser = userService.getUserById(user.getUserId());
  Assert.assertNotNull(createdUser);
  Assert.assertEquals(user.getEmailId(), createdUser.getEmailId());
  Assert.assertEquals(user.getPassword(), createdUser.getPassword());
  Assert.assertEquals(user.getFirstName(), createdUser.getFirstName());
  Assert.assertEquals(user.getLastName(), createdUser.getLastName());
 
 }
 
    @Test
    public void testUpdateUser()
    {
     long timestamp = System.currentTimeMillis();
  User user = userService.getUserById(2);
  user.setFirstName('TestFirstName'+timestamp);
     user.setLastName('TestLastName'+timestamp);
     userService.updateUser(user);
  User updatedUser = userService.getUserById(2);
  Assert.assertEquals(user.getFirstName(), updatedUser.getFirstName());
  Assert.assertEquals(user.getLastName(), updatedUser.getLastName());
 }
 
   @Test
   public void testDeleteUser()
   {
     User user = userService.getUserById(4);
     userService.deleteUser(user.getUserId());
  User deletedUser = userService.getUserById(4);
  Assert.assertNull(deletedUser);  
 
 }
}

Теперь я объясню, как выполнять операции CRUD с использованием поддержки аннотаций MyBatis без необходимости настройки запросов в файлах сопоставления XML.

Шаг № 1 : Создайте таблицу BLOG и Java Object Object Blog.

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

Шаг № 2 : Создайте интерфейс UserMapper.java с запросами SQL в аннотациях.

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

Шаг № 3 : Настройте BlogMapper в mybatis-config.xml

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?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>
 <properties resource='jdbc.properties'/>
 <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/mybatis-demo'/>
   <property name='username' value='root'/>
   <property name='password' value='admin'/> -->
   <property name='driver' value='${jdbc.driverClassName}'/>
   <property name='url' value='${jdbc.url}'/>
   <property name='username' value='${jdbc.username}'/>
   <property name='password' value='${jdbc.password}'/>
    </dataSource>
  </environment>
  </environments>
  <mappers>
    <mapper class='com.sivalabs.mybatisdemo.mappers.BlogMapper'/>
  </mappers>
</configuration>

Шаг № 4 : Создать BlogService.java

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

Шаг № 5 : Создайте тест JUnit для методов BlogService

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

Ссылка: Учебное пособие MyBatis : Часть 1 — Операции CRUD от нашего партнера JCG Шивы Редди в блоге «Мои эксперименты по технологии»

95
Share: