Mybatis

Mybatis

1、mybatis介绍

mybatis是sqlmap技术，对jdbc进行封装，将大量的sql语句外部化。

2、体验mybatis

  1. 准备数据库和表

    mysql>create table users(id int primary key auto_increment , 
                         name varchar(20) , 
                         age int) ;

  2. 创建模块引入maven依赖

    [pom.xml]

    <?xml version="1.0" encoding="UTF-8"?>
<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.oldboy</groupId>
  <artifactId>mybatis</artifactId>
  <version>1.0-SNAPSHOT</version>

  <dependencies>
    <!-- mysql驱动 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.17</version>
    </dependency>
    <!--junit单元测试-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
    </dependency>
    <!-- mybatis类库 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.1</version>
    </dependency>
  </dependencies>
</project>

  3. 创建javabean

    /**
 * User类
 */
public class User {
  private Integer id;
  private String name ;
  private int age ;

  //get//set
}

  4. 创建映射文件

    [resources/UserMapper.xml]

    <?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="users">
  <!-- 定义insert语句 ,获得生成的id字段-->
  <insert id="insert" >
    insert into users(name,age) values(#{name},#{age})
  </insert>
</mapper>

  5. 创建配置文件

    [resources/mybatis-config.xml]

    <?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>
    <property name="driver" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/big12"/>
    <property name="username" value="root"/>
    <property name="password" value="root"/>
  </properties>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="UserMapper.xml"/>
  </mappers>
</configuration>

  6. 编写测试类，实现插入

@Test
public void testInsert() throws IOException {
//加载配置文件
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//创建会话工厂(builder模式)
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
//开启会话，相当于连接
SqlSession sess = sf.openSession();

User u = new User() ;
u.setName("tom");
u.setAge(12);
//执行插入操作
sess.insert("users.insert" , u) ;
sess.commit();
sess.close();
}

3、实现mybatis的基本操作

3.1 update

  1. 在映射文件中增加update语句

    <?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="users">
  <update id="update">
    update users set name = #{name} , age = #{age} where id = #{id}
  </update>
</mapper>

  2. 调用session.update方法

    ...
User u = new User() ;
u.setName("tomas");
u.setAge(22);
u.setId(1);
sess.update("users.update" , u) ;

3.2 select

3.2.1 查询一个

  1. 映射文件增加查询语句

    [userMapper.xml]

    ...
<select id="selectById" resultType="_User">
  select * from users where id = #{id}
</select>
...

  2. 配置文件添加别名

    [mybatis-config.xml]

    <?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>
  ...
  <typeAliases>
    <typeAlias type="com.oldboy.mybatis.domain.User" alias="_User"/>
  </typeAliases>
  ...
</configuration>

    ​

  3. API调用

    User u = sess.selectOne("users.selectById" , 1) ;
3.2.2 查询多个

  1. 添加sql语句

    [userMapper.xml]

    ...
<select id="selectAll" resultType="_User">
  select * from users
</select>
...

  2. API调用

    List<User> list = sess.selectList("users.selectAll" ) ;

3.3 delete

  1. 添加sql语句

    [UserMapper.xml]

    ...
<delete id="delete">
  delete from users where id = #{id}
</delete>
...

  2. API调用

    sess.delete("users.delete" , 1) ;

4、获取数据库主键值

  1. 数据表如果主键是自动增长的话，如果要在程序插入时得到主键值可修改映射文件，具体如下：

    [UserMapper.xml]

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
  ...
</insert>

    ​

  2. 测试代码：

User u = new User();
sess.insert("users.insert" , u) ;

5、关联关系

5.1 多对一关联

5.1.1 保存

  1. 创建订单表，并设置外键约束

    -- 创建表
mysql>create table orders(id int primary key auto_increment,
                          orderno varchar(20) ,
                          price float ,
                          cid int);
-- 添加外键约束
mysql>alter table orders add constraint foreign key fk_cid_user_id (cid) REFERENCES users (id) ;

  2. 创建Order类

    /**
 * 订单类
 */
public class Order {
  private Integer id ;
  private String orderNo ;
  private float price ;
  //建立多对一关联关系
  private User user ;
  //get/set
}

    ​

  3. 创建映射文件

    [OrderMapper.xml]

    <?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="orders">
  <insert id="insert">
    insert into orders(orderno , price , cid) values(#{orderNo} ,#{price} , 
    												#{user.id})
  </insert>
</mapper>

  4. API

    User u = new User();
u.setId(2);

Order o = new Order();
o.setOrderNo("no001");
o.setPrice(2000);
//设置关联关系
o.setUser(u);
sess.insert("orders.insert" , o) ;

    ​

5.5.2 查询

  1. 修改映射文件

    [OrderMapper.xml]

    <?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="orders">
  <insert id="insert">
    insert into orders(orderno , price , cid) values(#{orderNo} ,#{price} , #{user.id})
  </insert>
  <select id="selectOne" resultMap="rmOrder">
    select
      o.id oid , 
      o.orderno oorderno,  
      o.price oprice , 
      u.id uid ,
      u.name uname , 
      u.age uage
    from orders o
    	left outer join users u on o.cid = u.id
    where 
    	o.id = #{id}
  </select>

  <!-- 定义结果映射，将字段和类属性关联在一起 -->
  <resultMap id="rmOrder" type="_Order">
    <id column="oid" property="id"/>
    <result column="oorderno" property="orderNo" />
    <result column="oprice" property="price" />
    <association property="user" javaType="_User" column="uid">
      <id column="uid" property="id"/>
      <result column="uname" property="name" />
      <result column="uage" property="age" />
    </association>
  </resultMap>
</mapper>

    ​

  2. API查询

    sess.selectOne("orders.selectOne" , 3) ;

5.2 一对多关联

一对多关联使用collection集合方法进行关联，表示one方有多个many实例。

  1. 修改User类，添加orders集合属性

    class User{
  ...
  private List<Order> orders = new ArrayList<Order>() ;
  //get/set
}

    ​

  2. 修改UserMapper.xml文件

    <?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="users">
  <!-- 定义insert语句 ,获得生成的id字段-->
  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into users(name,age) values(#{name},#{age})
  </insert>

  <update id="update">
    update users set name = #{name} , age = #{age} where id = #{id}
  </update>

  <delete id="delete">
    delete from users where id = #{id}
  </delete>

  <select id="selectById" resultMap="rmUser">
    SELECT
    u.id uid,
    u.name uname,
    u.age uage ,
    o.id oid ,
    o.orderno oorderno ,
    o.price oprice
    from
    users u left OUTER join orders o on o.cid = u.id
    WHERE
    u.id = #{id}
  </select>
  <select id="selectAll" resultType="_User">
    select * from users
  </select>
  <!-- 用户映射 -->
  <resultMap id="rmUser" type="_User">
    <id column="uid" property="id" />
    <result column="uname" property="name" />
    <result column="uage" property="age"/>
    <collection property="orders" ofType="_Order" column="uid">
      <id column="oid" property="id" />
      <result column="oorderno" property="orderNo" />
      <result column="oprice" property="price"/>
    </collection>
  </resultMap>
</mapper>

    ​

  3. 测试用例

    sess.selectOne("users.selectById" , 2) ;

5.3 三级多对一关联

  1. 引入OrderItem（订单项）类

    class OrderItem{
  private Integer id ;
  private String iname ;
  private Order order ;
  //get/set
}

  2. 设置Order到OrderItem之间的关联

    class Order{
  //toOne
  private User user ;
  //toMany
  private List<OrderItem> items = new ArrayList<OrderItem>() ;
}

class User{
  ...
    private List<Order> orders = new ArrayList<Order>() ;
}

    ​

  3. 编写映射文件

    • 查询Order

      [OrderMapper.xml]

      <?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="orders">
  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into orders(orderno , price , cid) values(#{orderNo} ,#{price} , #{user.id})
  </insert>
  <select id="selectOne" resultMap="rmOrder">
    select
    	o.id oid , 
    	o.orderno oorderno,
    	o.price oprice, 
    	u.id uid , 
    	u.name uname ,
    	u.age uage , 
    	i.id iid , 
    	i.iname iiname
    from
    	orders o
    	left outer join users u on o.cid = u.id
    	left outer join orderitems i on i.oid = o.id
    where
    	o.id = #{id}
  </select>

  <resultMap id="rmOrder" type="_Order">
    <id column="oid" property="id"/>
    <result column="oorderno" property="orderNo" />
    <result column="oprice" property="price" />
    <association property="user" javaType="_User" column="uid">
      <id column="uid" property="id"/>
      <result column="uname" property="name" />
      <result column="uage" property="age" />
    </association>
    <collection property="items" ofType="_Item" column="oid">
      <id column="iid" property="id" />
      <result column="iiname" property="iname" />
    </collection>
  </resultMap>
</mapper>

    • 查询OrderItem

      [OrderItem.xml]

      <?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="orderitems">
  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into orderitems(iname, oid) values(#{iname} ,#{order.id})
  </insert>

  <select id="selectOne" resultMap="rmItem">
    select
    	o.id oid , 
    	o.orderno oorderno,
    	o.price oprice, 
    	u.id uid , 
    	u.name uname ,
    	u.age uage , 
    	i.id iid , 
    	i.iname iiname
    from
    	orderitems i
    	left outer join orders o on i.oid = o.id
    	left outer join users u on o.cid = u.id
    where 
    	i.id = #{id} ;
  </select>
  <resultMap id="rmItem" type="_Item">
    <id column="iid"  property="id" />
    <result column="iiname" property="iname"/>
    <association property="order" column="oid" javaType="_Order">
      <id column="oid"  property="id"/>
      <result column="oorderno"  property="orderNo"/>
      <result column="oprice" property="price"/>
      <association property="user" column="uid" javaType="_User">
        <id column="uid" property="id" />
        <result column="uname" property="name"/>
        <result column="uage" property="age" />
      </association>
    </association>
  </resultMap>
</mapper>

    • 查询User

      [UserMapper.xml]

      <?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="users">
  <!-- 定义insert语句 ,获得生成的id字段-->
  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into users(name,age) values(#{name},#{age})
  </insert>

  <update id="update">
    update users set name = #{name} , age = #{age} where id = #{id}
  </update>

  <delete id="delete">
    delete from users where id = #{id}
  </delete>

  <select id="selectById" resultMap="rmUser">
    select
    	o.id oid , 
    	o.orderno oorderno,
    	o.price oprice, 
    	u.id uid , 
    	u.name uname ,
    	u.age uage , 
    	i.id iid , 
    	i.iname iiname
    from
    	users u
   	 	left outer join orders o on o.cid = u.id
    	left outer join orderitems i on i.oid = o.id
    where 
    	u.id = #{id}
  </select>
  <select id="selectAll" resultType="_User">
    select * from users
  </select>
  <!-- 用户映射 -->
  <resultMap id="rmUser" type="_User">
    <id column="uid" property="id" />
    <result column="uname" property="name" />
    <result column="uage" property="age"/>
    <collection property="orders" ofType="_Order" column="uid">
      <id column="oid" property="id" />
      <result column="oorderno" property="orderNo" />
      <result column="oprice" property="price"/>
      <collection property="items" ofType="_Item" column="oid">
        <id column="iid" property="id"/>
        <result column="iiname" property="iname" />
      </collection>
    </collection>
  </resultMap>
</mapper>

5.4 多对一自关联

区域信息时典型的自关联信息，自己和自己进行关联。

5.4.1 表结构
id areaname parentid
1 全国 NULL
2 河北 1
3 河南 1
4 保定 2
5.4.2 类设计
public class Area {
  private Integer id ;
  private String areaName ;
  private Area parentArea ;
  private List<Area> children = new ArrayList<Area>();
  //get/set
}
5.4.3 映射方式
<?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="areas">
  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into areas(areaname, pid) values(#{areaName} , #{parentArea.id})
  </insert>

  <select id="selectOne" resultMap="rmArea">
    select
      a.id aid ,
      a.areaname aareaname ,
      b.id bid ,
      b.areaname bareaname ,
      c.id cid ,
      c.areaname careaname
    from
      areas a
      left OUTER  join areas b on b.pid = a.id
      left OUTER  join areas c on c.pid = b.id
    WHERE
      a.id = #{id}
  </select>

  <resultMap id="rmArea" type="_Area">
    <id column="aid" property="id"/>
    <result column="aareaname" property="areaName" />
    <collection property="children" ofType="_Area" column="aid">
      <id column="bid" property="id" />
      <result column="bareaname" property="areaName"/>
      <collection property="children" ofType="_Area" column="bid">
        <id column="cid" property="id" />
        <result column="careaname" property="areaName" />
      </collection>
    </collection>
  </resultMap>
</mapper>

5.5 一对一关联

5.5.1 主键关联

主键关联是一张表的主键和另一张表保持一致，因从属表的主键也是外键。

  1. 表结构

    [husbands_pk]

    id(主键) name ...
    1 tom .
    2 tomas .
    3 tomasLee .

    [wifes_pk]

    id(主键，外键) name ...
    1 jerry .
    2 jerry2 .
    3 jerry3 .

  2. 类设计

    public class Husband {
  private Integer id ;
  private String hname ;
  private Wife wife ;
}

public class Wife {
  private Husband husband ;
  private String wname ;
}

    ​

  3. 映射方式

    [HusbandPKMapper.xml]

    <?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="husbands">
  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into husbands(hname) values(#{hname})
  </insert>

  <select id="selectOne" resultMap="rmHusband">
    select
      h.id hid ,
      h.hname hhname ,
      w.wname wwname
    from
    	husbands h left OUTER  join wifes w on w.hid = h.id 
    WHERE 
		h.id = #{id}
  </select>
  <resultMap id="rmHusband" type="_Husband">
    <id column="hid" property="id" />
    <result column="hhname" property="hname"/>
    <association property="wife" javaType="_Wife" column="hid">
      <result column="wwname" property="wname" />
      <association property="husband" column="hid" javaType="_Husband">
        <id column="hid" property="id" />
        <result column="hhname" property="hname"/>
      </association>
    </association>
  </resultMap>
</mapper>

    ​

    [WifePKMapper.xml]

    <?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="wifes">
  <insert id="insert">
    insert into wifes(hid, wname) values(#{husband.id} , #{wname})
  </insert>
</mapper>

    ​

5.5.2 外键关联

  1. 表结构

    [husbands_fk]

    id(主键) name ..
    1 tom .
    2
    3

    [wifes_fk]

    id(主键) name hid(外键，唯一约束)
    1 1
    2 2
    3 3

    ​

  2. 类设计

    public class HusbandFK {
  private  Integer id ;
  private String hname ;
  private WifeFK wife ;
}
public class WifeFK {
  private Integer id ;
  private String wname ;
  private HusbandFK husband ;
}

    ​

  3. 映射关系

    [HusbandFKMapper.xml]

    <?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="husbands_fk">
  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into husbands_fk(hname) values(#{hname})
  </insert>
</mapper>

    ​

    [WifeFKMapper.xml]

    <?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="wifes_fk">
  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into wifes_fk(wname, hid) values(#{wname} , #{husband.id})
  </insert>
</mapper>

5.6 多对多关系

多对多关系需要引入中间表，使用联合主键分别和两端的表进行关联，典型是师生表。

  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)) ;

  2. 类设计

    public class Teacher {
  private Integer id ;
  private String tname ;
  private List<Student> stus = new ArrayList<Student>() ;
}
public class Student {
  private Integer id ;
  private String sname ;
  private List<Teacher> teas = new ArrayList<Teacher>() ;
}

    ​

  3. 映射方式

    插入数据需要包含循环操作，且要设置运行执行多条语句。

    • TeacherMapper.xml

      <?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="teas">
  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    insert into teas(tname) values(#{tname})
  </insert>

  <!-- 循环插入关系表 -->
  <insert id="insertLink">
    <foreach collection="stus" item="s">
      insert into links(tid,sid) values(#{id} , #{s.id}) ;
    </foreach>
  </insert>

  <select id="selectOne" resultMap="rmTeacher">
    select
    	t.id tid , t.tname ttname , s.id sid , s.sname ssname
    from
    	teas t
   		left outer join links l on l.tid = t.id
    	left outer join stus s on l.sid = s.id
    WHERE
    	t.id = #{id}
  </select>
  <resultMap id="rmTeacher" type="_Teacher">
    <id column="tid" property="id"/>
    <result column="ttname"  property="tname"/>
    <collection property="stus" ofType="_Student" column="tid">
      <id column="sid" property="id" />
      <result column="ssname" property="sname"/>
    </collection>
  </resultMap>
</mapper>

      ​

    • 配置执行多条语句

      [mybatis-config.xml]

      ...
<property name="url" value="jdbc:mysql://localhost:3306/big12?allowMultiQueries=true"/>
...
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/xupccc/p/9655343.html