Mybatis 学习笔记

1. Mybatis 介绍

　　Mybatis 是 sqlmap 技术，对 JDBC 进行封装，将大量的 SQL 语句外部化。

　　平时我们都用JDBC访问数据库，除了需要自己写 SQL 之外，还必须操作 Connection，Statment， ResultSet 这些其实只是手段的辅助类。不仅如此，访问不同的表，还会写很多雷同的代码，显得繁琐和枯燥。

　　那么用了 Mybatis 之后，只需要自己提供 SQL 语句，其他的工作，诸如建立连接，Statement， JDBC 相关异常处理等等都交给 Mybatis 去做了，那些重复性的工作 Mybatis 也给做掉了，我们只需要关注在增删改查等操作层面上，而把技术细节都封装在了我们看不见的地方。

2. Mybatis 使用流程

2.1 创建数据库和表

# 创建数据库，略

# 使用数据库
use database_name ; 

# 创建 user 表
create table users(id int primary key auto_increment , name varchar(20) , age int);

2.2 创建模块，添加 Maven 依赖

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <project xmlns="http://maven.apache.org/POM/4.0.0"
 3          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 5     <modelVersion>4.0.0</modelVersion>
 6 
 7     <groupId>com.share</groupId>
 8     <artifactId>mybatis</artifactId>
 9     <version>1.0-SNAPSHOT</version>
10 
11     <dependencies>
12 
13         <!-- MySQL依赖 -->
14         <dependency>
15             <groupId>mysql</groupId>
16             <artifactId>mysql-connector-java</artifactId>
17             <version>5.1.17</version>
18         </dependency>
19 
20         <!-- 单元测试依赖 -->
21         <dependency>
22             <groupId>junit</groupId>
23             <artifactId>junit</artifactId>
24             <version>4.11</version>
25         </dependency>
26 
27         <!-- mybatis依赖 -->
28         <dependency>
29             <groupId>org.mybatis</groupId>
30             <artifactId>mybatis</artifactId>
31             <version>3.2.1</version>
32         </dependency>
33 
34     </dependencies>
35 
36 </project>

pom.xml

2.3 创建实体类 User

 1 public class User {
 2 
 3     private Integer id;
 4     private String name;
 5     private int age;
 6 
 7     public Integer getId() {
 8         return id;
 9     }
10 
11     public void setId(Integer id) {
12         this.id = id;
13     }
14 
15     public String getName() {
16         return name;
17     }
18 
19     public void setName(String name) {
20         this.name = name;
21     }
22 
23     public int getAge() {
24         return age;
25     }
26 
27     public void setAge(int age) {
28         this.age = age;
29     }
30 }

View Code

2.4 创建映射文件

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3      "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4     <!-- 定义名字空间 -->
 5     <mapper namespace="users">
 6     <!-- 定义insert语句 ,获得生成的id字段-->
 7     <insert id="insert" >
 8         insert into users(name,age) values(#{name},#{age})
 9     </insert>
10 </mapper>

UserMapper.xml

2.5 创建配置文件

 1 <?xml version = "1.0" encoding="UTF-8" ?>
 2     <!DOCTYPE configuration
 3             PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4             "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5 <configuration>
 6     <properties>
 7         <property name="driver" value="com.mysql.jdbc.Driver"/>
 8         <property name="url" value="jdbc:mysql://localhost:3306/database_name"/>
 9         <property name="username" value="root"/>
10         <property name="password" value="root"/>
11     </properties>
12 
13     <environments default="development">
14         <environment id="development">
15             <transactionManager type="JDBC"/>
16             <dataSource type="POOLED">
17             <property name="driver" value="${driver}"/>
18             <property name="url" value="${url}"/>
19             <property name="username" value="${username}"/>
20             <property name="password" value="${password}"/>
21             </dataSource>
22         </environment>
23     </environments>
24 
25     <mappers>
26         <mapper resource="UserMapper.xml"/>
27     </mappers>
28 
29 </configuration>

mybatis-config.xml

2.6 测试

 1 /**
 2 * 测试 insert
 3 */
 4 @Test
 5 public void tastInsert() throws Exception {
 6     //加载配置文件
 7     InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
 8     //创建会话工厂(Builder 模式)
 9     SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
10     //开启会话，相当于连接
11     SqlSession sess = sf.openSession();
12 
13     User u = new User();
14     u.setName("sam");
15     u.setAge(17);
16 
17     sess.insert("users.insert", u);
18     sess.commit();
19     sess.close();
20     System.out.println("insert success !!!");
21 }

3. CRUD

3.1 修改 UserMapper.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 <!-- 定义名字空间 -->
 5 <mapper namespace="users">
 6     <!-- 定义insert语句 ,获得生成的id字段-->
 7     <insert id="insert" useGeneratedKeys="true" keyProperty="id">
 8         insert into users(name,age) values(#{name},#{age})
 9     </insert>
10     
11     <update id="update">
12         update users set name = #{name} , age = #{age} where id = #{id}
13     </update>
14 
15     <delete id="delete">
16         delete from users where id = #{id}
17     </delete>
18     
19     <select id="selectById" resultMap="rmUser">
20       SELECT
21         u.id uid,
22         u.name uname,
23         u.age uage ,
24         o.id oid ,
25         o.orderno oorderno ,
26         o.price oprice
27       from
28         users u left OUTER join orders o on o.cid = u.id
29       WHERE
30         u.id = #{id}
31     </select>
32     <select id="selectAll" resultType="_User">
33       select * from users
34     </select>
35     <!-- 用户映射 -->
36     <resultMap id="rmUser" type="_User">
37         <id column="uid" property="id" />
38         <result column="uname" property="name" />
39         <result column="uage" property="age"/>
40         <collection property="orders" ofType="_Order" column="uid">
41             <id column="oid" property="id" />
42             <result column="oorderno" property="orderNo" />
43             <result column="oprice" property="price"/>
44         </collection>
45     </resultMap>
46 </mapper>

UserMapper.xml

3.2 修改 mybatis-config.xml

 1 <?xml version = "1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration
 3         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5 <configuration>
 6     <properties>
 7         <property name="driver" value="com.mysql.jdbc.Driver"/>
 8         <property name="url" value="jdbc:mysql://localhost:3306/database_name?allowMultiQueries=true"/>
 9         <property name="username" value="root"/>
10         <property name="password" value="123456"/>
11     </properties>
12 
13     <!--添加别名-->
14     <typeAliases>
15         <typeAlias type="com.share.mybatis.domain.User" alias="_User"/>
16     </typeAliases>
17 
18     <environments default="development">
19         <environment id="development">
20             <transactionManager type="JDBC"/>
21             <dataSource type="POOLED">
22                 <property name="driver" value="${driver}"/>
23                 <property name="url" value="${url}"/>
24                 <property name="username" value="${username}"/>
25                 <property name="password" value="${password}"/>
26             </dataSource>
27         </environment>
28     </environments>
29 
30     <mappers>
31         <mapper resource="UserMapper.xml"/>
32     </mappers>
33 
34 </configuration>

mybatis-config.xml

3.3 测试

  1 /**
  2      * 测试 insert
  3      */
  4     @Test
  5     public void tastInsert() throws Exception {
  6         //加载配置文件
  7         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
  8         //创建会话工厂(Builder 模式)
  9         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
 10         //开启会话，相当于连接
 11         SqlSession sess = sf.openSession();
 12 
 13         Order o = new Order();
 14         o.setOrderNo("2");
 15         o.setPrice(160);
 16         o.setId(5);
 17         User user = new User();
 18         user.setId(5);
 19         o.setUser(user);
 20 
 21 
 22         sess.insert("orders.insert", o);
 23         sess.commit();
 24         sess.close();
 25         System.out.println("insert success !!!");
 26     }
 27 
 28     /**
 29      * 测试 update
 30      */
 31     @Test
 32     public void tastUpdate() throws Exception {
 33         //加载配置文件
 34         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
 35         //创建会话工厂(Builder 模式)
 36         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
 37         //开启会话，相当于连接
 38         SqlSession sess = sf.openSession();
 39 
 40         User u = new User();
 41         u.setName("Amy");
 42         u.setAge(18);
 43         u.setId(2);
 44 
 45         sess.update("users.update", u);
 46         sess.commit();
 47         sess.close();
 48         System.out.println("update success !!!");
 49     }
 50 
 51     /**
 52      * 测试 delete
 53      */
 54     @Test
 55     public void tastDelete() throws Exception {
 56         //加载配置文件
 57         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
 58         //创建会话工厂(Builder 模式)
 59         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
 60         //开启会话，相当于连接
 61         SqlSession sess = sf.openSession();
 62 
 63         User u = new User();
 64         u.setId(3);
 65 
 66         sess.delete("users.delete", u);
 67         sess.commit();
 68         sess.close();
 69         System.out.println("delete success !!!");
 70     }
 71 
 72     /**
 73      * 测试 SelectOne
 74      */
 75     @Test
 76     public void testSelectOne() throws IOException {
 77         //加载配置文件
 78         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
 79         //创建会话工厂(builder模式)
 80         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
 81         //开启会话，相当于连接
 82         SqlSession sess = sf.openSession();
 83         User u = sess.selectOne("users.selectById", 5);
 84         sess.commit();
 85         System.out.println(u.getName());
 86         sess.close();
 87     }
 88 
 89     /**
 90      * 测试 SelectAll
 91      */
 92     @Test
 93     public void testSelectAll() throws IOException {
 94         //加载配置文件
 95         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
 96         //创建会话工厂(builder模式)
 97         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
 98         //开启会话，相当于连接
 99         SqlSession sess = sf.openSession();
100         List<User> list = sess.selectList("users.selectAll");
101         sess.commit();
102         System.out.println(list.size());
103         sess.close();
104     }

4. 一对一

4.1 说明

一对一的本质为：　多对一 + 唯一性约束

4.2 创建表，加外键和唯一约束

# 删除 wifes_fk 表
drop table if exists wifes_fk ;

# 删除 husbands_fk 表
drop table if exists husbands_fk ;

# 创建 husbands_fk 表
create table husbands_fk(id int primary key auto_increment, hname varchar(20)) ;

# 创建 wifes_fk 表
create table wifes_fk(id int primary key auto_increment, wname varchar(20) , hid int , foreign key (hid) references husbands_fk(id) , unique (hid)) ;

# 插入数据
insert into husbands_fk(hname) values('tomas') ;

# 插入数据
insert into wifes_fk(wname , hid) values('jerry' , 1) ;

5. 一对多

待补充。。。

6. 多对一

待补充。。。

7. 多对多

7.1 准备表

# 创建教师表
create table teas(id int primary key auto_increment, tname varchar(20)) ;

# 创建学生表
create table stus(id int primary key auto_increment, sname varchar(20)) ;

# 创建链接表
create table links(tid int , sid int , primary key (tid,sid) , foreign key (tid) references teas (id) , foreign key (sid) references stus (id)) ;

7.2 创建类

　　Teacher.java

 1 import java.util.ArrayList;
 2 import java.util.List;
 3 
 4 /**
 5  * 教师类
 6  */
 7 public class Teacher {
 8 
 9     private Integer id;
10     private String tname;
11     private List<Student> stus = new ArrayList<Student>();
12 
13     /**
14      * 方便关联关系
15      */
16     public void addStudents(Student... stus) {
17         for (Student s : stus) {
18             this.getStus().add(s);
19             s.getTeas().add(this);
20         }
21     }
22 
23     public List<Student> getStus() {
24         return stus;
25     }
26 
27     public void setStus(List<Student> stus) {
28         this.stus = stus;
29     }
30 
31     public Integer getId() {
32         return id;
33     }
34 
35     public void setId(Integer id) {
36         this.id = id;
37     }
38 
39     public String getTname() {
40         return tname;
41     }
42 
43     public void setTname(String tname) {
44         this.tname = tname;
45     }
46 }

Teacher.java

　　Student.java

 1 import java.util.ArrayList;
 2 import java.util.List;
 3 
 4 /**
 5  * 学生类
 6  */
 7 public class Student {
 8 
 9     private Integer id;
10     private String sname;
11     private List<Teacher> teas = new ArrayList<Teacher>() ;
12 
13     public List<Teacher> getTeas() {
14         return teas;
15     }
16  
17     public void setTeas(List<Teacher> teas) {
18         this.teas = teas;
19     }
20 
21     public Integer getId() {
22         return id;
23     }
24 
25     public void setId(Integer id) {
26         this.id = id;
27     }
28 
29     public String getSname() {
30         return sname;
31     }
32 
33     public void setSname(String sname) {
34         this.sname = sname;
35     }
36 }

Student.class

7.3 映射文件

　　TeacherMapper.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 <!-- 定义名字空间 -->
 5 <mapper namespace="teas">
 6     <insert id="insert" useGeneratedKeys="true" keyProperty="id">
 7         insert into teas(tname) values(#{tname})
 8     </insert>
 9     
10     <insert id="insertLink">
11       <foreach collection="stus" item="s">
12           insert into links(tid,sid) values(#{id} , #{s.id}) ;
13       </foreach>
14     </insert>
15 
16     <select id="selectOne" resultMap="rmTeacher">
17         select
18           t.id tid , t.tname ttname , s.id sid , s.sname ssname
19         from
20           teas t
21           left outer join links l on l.tid = t.id
22           left outer join stus s on l.sid = s.id
23         WHERE
24           t.id = #{id}
25     </select>
26     <resultMap id="rmTeacher" type="_Teacher">
27         <id column="tid" property="id"/>
28         <result column="ttname"  property="tname"/>
29         <collection property="stus" ofType="_Student" column="tid">
30             <id column="sid" property="id" />
31             <result column="ssname" property="sname"/>
32         </collection>
33     </resultMap>
34 </mapper>

TeacherMapper.xml

　　StudentMapper.xml

1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
3         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
4 <!-- 定义名字空间 -->
5 <mapper namespace="stus">
6     <insert id="insert" useGeneratedKeys="true" keyProperty="id">
7         insert into stus(sname) values(#{sname})
8     </insert>
9 </mapper>

StudentMapper.xml

7.4 修改配置文件，允许执行多条语句

在 mybatis-config.xml 文件中的 properties 里的 url 中添加 ?allowMultiQueries=true"

允许执行多条语句

<property name="url" value="jdbc:mysql://localhost:3306/DataBase_Name?allowMultiQueries=true"/>

添加别名

<!--添加别名-->
<typeAliases>
    <typeAlias type="com.share.mybatis.domain.Student" alias="_Student"/>
    <typeAlias type="com.share.mybatis.domain.Teacher" alias="_Teacher"/>
</typeAliases>

指定 mappers

<mappers>
    <mapper resource="StudentMapper.xml"/>
    <mapper resource="TeacherMapper.xml"/>
</mappers>

7.5 执行插入

 1 public class More2More {
 2     @Test
 3     public void testInsert() throws IOException {
 4         //加载配置文件
 5         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
 6         //创建会话工厂(Builder 模式)
 7         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
 8         //开启会话，相当于连接
 9         SqlSession sess = sf.openSession();
10 
11         //创建对象
12         Teacher t1 = new Teacher();
13         Teacher t2 = new Teacher();
14 
15         Student s1 = new Student();
16         Student s2 = new Student();
17         Student s3 = new Student();
18         Student s4 = new Student();
19 
20         //设置关联
21         t1.addStudents(s1, s2, s3);
22         t2.addStudents(s2, s3, s4);
23 
24         sess.insert("teas.insert", t1);
25         sess.insert("teas.insert", t2);
26 
27         sess.insert("stus.insert", s1);
28         sess.insert("stus.insert", s2);
29         sess.insert("stus.insert", s3);
30         sess.insert("stus.insert", s4);
31 
32         //插入关系
33         sess.insert("teas.insertLink", t1);
34         sess.insert("teas.insertLink", t2);
35 
36         sess.commit();
37         sess.close();
38         System.out.println("ok");
39 
40     }
41 }

More2More.java

7.6 查询教师

在 TeacherMapper.xml 中添加查询

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 <!-- 定义名字空间 -->
 5 <mapper namespace="teas">
 6     <insert id="insert" useGeneratedKeys="true" keyProperty="id">
 7         insert into teas(tname) values(#{tname})
 8     </insert>
 9     
10     <insert id="insertLink">
11       <foreach collection="stus" item="s">
12           insert into links(tid,sid) values(#{id} , #{s.id}) ;
13       </foreach>
14     </insert>
15 
16     <select id="selectOne" resultMap="rmTeacher">
17         select
18           t.id tid , t.tname ttname , s.id sid , s.sname ssname
19         from
20           teas t
21           left outer join links l on l.tid = t.id
22           left outer join stus s on l.sid = s.id
23         WHERE
24           t.id = #{id}
25     </select>
26     <resultMap id="rmTeacher" type="_Teacher">
27         <id column="tid" property="id"/>
28         <result column="ttname"  property="tname"/>
29         <collection property="stus" ofType="_Student" column="tid">
30             <id column="sid" property="id" />
31             <result column="ssname" property="sname"/>
32         </collection>
33     </resultMap>
34 </mapper>

TeacherMapper.xml

查询部分代码

@Test
public void testSelectOne() throws IOException {
    InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
    SqlSession sess = sf.openSession();
    Teacher t = sess.selectOne("teas.selectOne" , 1);
    sess.commit();
    System.out.println("ok");
}

且将新火试新茶，诗酒趁年华。