Mybatis入门2

作者: itzhouq的笔记 | 来源:发表于2019-04-12 15:44 被阅读0次

mybatis02

1. 映射文件

映射文件中<select>的两个参数重要参数：parameter和resultType
parameter：输入映射，定义输入到sql中的映射类型，${value}表示使用参数将${value}替换，做字符串拼接。如果是取简单数量类型的参数，括号中的值必须为value。
resultType：输出映射，定义结果映射类型。

1.1 输入映射

输入映射支持的数据类型：
- 基本数据类型：基础类型以及包装类、String
- POJO
- Map
- 包装的POJO：一个pojo中有pojo属性
输入映射参数为map类型举例
案例预览

/mybatis-day02/src/com/itzhouq/mybatis/mapper/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="com.itzhouq.mybatis.mapper.UserMapper">
  <!-- statementId -->
  <select id="findById" parameterType="int" resultType="com.itzhouq.mybatis.pojo.User" >
      select * from user where id = #{id}
  </select>
  
  <insert id="insertUser" parameterType="com.itzhouq.mybatis.pojo.User">
      <selectKey resultType="int" keyProperty="id" order="AFTER">
          <!-- 返回当前事务最后产生的id值 -->
          SELECT LAST_INSERT_ID();
      </selectKey>
      insert into user(username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address});
  </insert>
  <select id="findUserByMap" parameterType="map" resultType="com.itzhouq.mybatis.pojo.User">
      select * from user where username like '%${username}%' and sex = #{sex} and address=#{address}
  </select>
  
</mapper>

/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.java

package com.itzhouq.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.itzhouq.mybatis.pojo.User;

public interface UserMapper {
  public User findById(int id);
  
  public void insertUser(User user);
  //映射参数为map类型
  public List<User> findUserByMap(Map<String, Object> map);
}

/mybatis-day02/src/com/itzhouq/mybatis/test/UserMapperTest.java

package com.itzhouq.mybatis.test;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.itzhouq.mybatis.mapper.UserMapper;
import com.itzhouq.mybatis.pojo.User;

public class UserMapperTest {
  SqlSessionFactory sqlSessionFactory = null;
  @Before
  public void init() throws IOException {
      SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
      sqlSessionFactory = builder.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
  }
  
  @Test
  public void testFindById() {
      SqlSession sqlSession = sqlSessionFactory.openSession();
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      User user = mapper.findById(28);
      System.out.println(user);
      sqlSession.close();
  }
  
  @Test
  public void testFindByMap() {
      SqlSession sqlSession = sqlSessionFactory.openSession();
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      HashMap<String, Object> map = new HashMap<String, Object>();
//        select * from user where username like '%张%' and sex = '1' and address='河南郑州'
//        select * from user where username like '%${username}%' and sex = #{sex} and address=#{address}
      map.put("username", "张");
      map.put("sex", "1");
      map.put("address", "河南郑州");
      
      List<User> users= mapper.findUserByMap(map);
      System.out.println(users);
      sqlSession.close();
  }
  
}

测试testFindByMap得到两个user对象

输入映射参数为包装的POJO类型举例

在pojo中新建一个类QueryVo

/mybatis-day02/src/com/itzhouq/mybatis/pojo/QueryVo.java

package com.itzhouq.mybatis.pojo;

public class QueryVo {
  private User user;

  public User getUser() {
      return user;
  }
  public void setUser(User user) {
      this.user = user;
  }
}

接口中/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.java

package com.itzhouq.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.itzhouq.mybatis.pojo.QueryVo;
import com.itzhouq.mybatis.pojo.User;

public interface UserMapper {
  public User findById(int id);
  
  public void insertUser(User user);
  //映射参数为map类型
  public List<User> findUserByMap(Map<String, Object> map);
  //映射参数为包装的POJO类型
  public List<User> findUserByQueryVo(QueryVo queryVo);

}

测试文件中/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.xml添加


  <select id="findUserByQueryVo" parameterType="queryVo" resultType="user">
  <!-- queryVo
          user
              username -->
      select * from user where username like '%${user.username}%'
  </select>

测试类/mybatis-day02/src/com/itzhouq/mybatis/test/UserMapperTest.java

@Test
  public void testFindByQueryVo() {
      SqlSession sqlSession = sqlSessionFactory.openSession();
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      QueryVo queryVo = new QueryVo();
      User user = new User();
      user.setUsername("张");
      queryVo.setUser(user);
      List<User> users= mapper.findUserByQueryVo(queryVo);
      System.out.println(users);
      sqlSession.close();
  }

1.2 输出映射

输出映射支持的数据类型：
- 基本数据类型：基础类型以及包装类、String
- POJO
- Map
- List

输出映射参数为map类型举例

接口com.itzhouq.mybatis.mapper.UserMapper

package com.itzhouq.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.itzhouq.mybatis.pojo.QueryVo;
import com.itzhouq.mybatis.pojo.User;

public interface UserMapper {
  public User findById(int id);
  public void insertUser(User user);
  //映射参数为map类型
  public List<User> findUserByMap(Map<String, Object> map);
  //映射参数为包装的POJO类型
  public List<User> findUserByQueryVo(QueryVo queryVo);
  //输出映射类型为int型
  public int findUserCount();
  //输出映射参数为map类型
  public Map findById2(int id);
  
}

映射配置文件/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.xml中添加

<select id="findUserCount" resultType="int">
      select count(*) from user;
</select>

测试类

@Test
  public void testFindById2() {
      SqlSession sqlSession = sqlSessionFactory.openSession();
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      Map map = mapper.findById2(28);
      System.out.println(map);
      //{birthday=2019-04-09, address=芭蕉洞, sex=0, id=28, username=玉面狐狸}
      sqlSession.close();
  }

1.3 resultMap

resultMap作用：当实体类的属性名和表的字段名不一致时，需要手动映射。

复制数据库中的user表为user_copy1，更改其中的username和sex字段，这时候这两个字段和实体类User中的属性名就不一致了。

更改后的表和类需要正常映射的话，需要手动配置resultMap

<resultMap id="findById3ResultMap" type="user">
    <id column="id" property="id"/>
    <result column="user_name" property="username"/>
    <result column="gender" property="sex"/>
    <!-- <result column="birthday" property="birthday"/>
  <result column="address" property="address"/> -->
</resultMap>

<select id="findById3" parameterType="int" resultMap="findById3ResultMap" >
    select * from user_copy1 where id = #{id}
</select>

字段相同的列可以省略不用手动配置

2. 动态SQL

2.1 if和where

需求分析：实际开发过程中，会用到组合条件查询，条件的个数是不固定的，这就要求sql语句中的条件根据传入的参数动态改变。为此，mybatis引入了where和if标签产生动态sql。

示例：

UserMapper.java文件中添加一个方法

//动态Sql
public List<User> findListByUser(User user);

xml配置文件

<select id="findListByUser" parameterType="user" resultType="user">
    select * from user
    <where>
        <if test="username!=null and username!=''">
            username like '%${username}%'
        </if>
        <if test="sex!=null and sex!=''">
            and sex = #{sex}
        </if>
        <if test="address!=null and address!=''">
            and address=#{address}
        </if>
    </where>
</select>

if作用：判断
where作用：取出第一个and或者or

测试

@Test
public void testFindListByUser() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = new User();
    //        user.setUsername("张");
    user.setSex("1");
    user.setAddress("河南郑州");
    List<User> list= mapper.findListByUser(user);
    System.out.println(list);
    sqlSession.close();
}

mybatis可以根据user中条件个数动态生成sql语句

比如上面代码中两个条件下，生成的sql语句为：

DEBUG [main] - ==>  Preparing: select * from user WHERE sex = ? and address=?

2.2 foreach循环

需求分析：要查询用户id为1 16 22 的用户信息，相对应的sql语句为 SELECT * FROM user where id in (1, 16, 22)。查询的参数是数组，把这个数组放在包装类中传递。

接口文件中添加

//动态sql，foreach循环
public List<User> findListByArray(QueryVo queryVo);

包装类中添加数组ids

package com.itzhouq.mybatis.pojo;

public class QueryVo {
    private User user;

    int[] ids;

    public int[] getIds() {
        return ids;
    }
    public void setIds(int[] ids) {
        this.ids = ids;
    }
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
}

映射配置文件

<select id="findListByArray" parameterType="queryVo" resultType="user">
    <!-- queryVo -->
    <!-- ids = [ 1, 16, 22] -->
    <!-- SELECT * FROM `user` where id in (1, 16, 22) -->
    <!-- select * from user where id in -->
    SELECT * FROM `user` where id in 
    <foreach collection="ids" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</select>

测试类

@Test
public void testFindListByArray() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    QueryVo queryVo = new QueryVo();
    int[] ids = {1, 16, 22};
    queryVo.setIds(ids);
    List<User> list= mapper.findListByArray(queryVo);
    System.out.println(list);
    sqlSession.close();
}

生成的sql语句

DEBUG [main] - ==>  Preparing: SELECT * FROM user where id in ( ? , ? , ? )

2.3 sql片段 sql+include

sql片段主要是用于xml文件中的代码复用

<select id="findListByUser" parameterType="user" resultType="user">
    select * from user
    <include refid="findUserWhere"></include>
</select>
<sql id="findUserWhere">
    <where>
        <if test="username!=null and username!=''">
            username like '%${username}%'
        </if>
        <if test="sex!=null and sex!=''">
            and sex = #{sex}
        </if>
        <if test="address!=null and address!=''">
            and address=#{address}
        </if>
    </where>
</sql>

2.4 set

需求分析：在更新数据库操作时，有可能只是更改某几个字段，传递的数据类型为user时，如果user的某些属性没有赋值，相当于把这些数据库中的这些字段设为null或者默认值，这样不符合我们的需求。这就需要判断user中的属性是否为空了。但是结合sql语句，修改操作时，每个字段后面又一个逗号。这里需要需要使用set去掉逗号，保证生成sql语句的正确性。

接口代码

//动态sql，set
public void updateUser(User user);

映射文件中添加

<update id="updateUser" parameterType="user">
    <!-- update user set username=#{username},sex=#{sex},
   birthday=#{birthday},address=#{address} where id=#{id} -->
    update user
    <set>
        <if test="username != null and username != ''">
            username=#{username},
        </if>
        <if test="sex != null and sex != ''">
            sex=#{sex},
        </if>
        <if test="birthday != null">
            birthday=#{birthday},
        </if>
        <if test="address != null and address != ''">
            address=#{address},
        </if>
    </set>
    where id=#{id}
</update>

测试类

@Test
public void testUpdateUser() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = new User();
    user.setId(28);
    user.setUsername("铁扇公主");
    mapper.updateUser(user);
    sqlSession.commit();
    sqlSession.close();
}

生成的sql语句

DEBUG [main] - ==>  Preparing: update user SET username=? where id=?

4. 关联查询

4.1 商品订单数据模型

商品订单数据模型

4.2 一对一查询

案例：查询所有订单信息，关联查询下单用户信息
因为一个订单信息只会是一个人下的订单，所以从查询订单信息出发关联用户信息为一对一查询。如果从用户信息出发下的订单则会为一对多查询，因为一个用户可以下多个订单。

4.2.1 方法一：

使用resultType，定义订单信息pojo类，此pojo类中包括了订单信息和用户信息。

sql语句

select o.*,u.username,u.address,u.birthday,u.sex from orders o left join user u on o.user_id = u.id

新建pojo类，OrdersUser，这个类中应该包括sql查询出来的所有字段

public class OrdersUser {
    private int id;
    private int user_id;
    private String number;
    private Date createtime;
    private String note;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;

OrdersMapper.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="com.itzhouq.mybatis.mapper.OrdersMapper">
    <!-- statementId -->
    <select id="findOrdersUserList" resultType="ordersUser" >
        select o.*,u.username,u.address,u.birthday,u.sex from
        orders o left join user u on o.user_id = u.id
    </select>
</mapper>

测试类OrdersMapperTest.java

package com.itzhouq.mybatis.test;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.itzhouq.mybatis.mapper.OrdersMapper;
import com.itzhouq.mybatis.mapper.UserMapper;
import com.itzhouq.mybatis.pojo.OrdersUser;
import com.itzhouq.mybatis.pojo.QueryVo;
import com.itzhouq.mybatis.pojo.User;

public class OrdersMapperTest {
    SqlSessionFactory sqlSessionFactory = null;
    @Before
    public void init() throws IOException {
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        sqlSessionFactory = builder.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
    }

    @Test
    public void testFindOrdersUserList() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
        List<OrdersUser> ordersUserList = mapper.findOrdersUserList();
        System.out.println(ordersUserList);
        sqlSession.close();

    }
}

查询结果与数据库中相同

结果

定义专门的pojo类作为输出类型，其中定了sql查询结果集所有的字段，此方法较为简单。

4.2.2 方法2:

使用resultMap,定义resultMap用于映射一对一的查询结果。

select o.*,u.username,u.address,u.birthday,u.sex from orders o left join user u on o.user_id = u.id

定义pojo类：早Orders类中加入Users属性，user属性中用于存储关联查询的用户信息，因为订单关联查询用户是一对一的关系，所以这使用单个User对象存储关联查询的用户信息。
```
public class Orders {
    private int id;
    private int user_id;
    private String number;
    private Date createtime;
    private String note;

    private User user;

    public int getId() {
        return id;
    }
```

映射文件OrdersMapper.xml

<resultMap id="ordersResultMap" type="orders" >
    <id column="id" property="id"/>
    <result column="user_id" property="user_id"/>
    <result column="number" property="number"/>
    <result column="createtime" property="createtime"/>
    <result column="note" property="note"/>
    <!-- 构建一对一级联关系 -->
    <association property="user" javaType="com.itzhouq.mybatis.pojo.User">
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="address" property="address"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
    </association>
</resultMap>

<select id="findOrdersList" resultMap="ordersResultMap" >
    select o.*,u.username,u.address,u.birthday,u.sex from
    orders o left join user u on o.user_id = u.id
</select>

这里resultMap指定ordersResultMap。

<association>:表示进行关联查询的单条记录

property:表示关联查询到结果存储在com.itzhouq.mybatis.pojo.User中

javaType:表示关联查询的结果类型

<id column="user_id" property="id"/>：查询结果的user_id列对象关联对象的id属性，这里是<id/>表示user_id是关联对象的唯一标识。

OrdersMapper接口

public interface OrdersMapper {
    public List<OrdersUser> findOrdersUserList();

    public List<Orders> findOrdersList();
}

测试

@Test
public void testFindOrdersList() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
    List<Orders> ordersList = mapper.findOrdersList();
    System.out.println(ordersList);
    sqlSession.close();
}

总结：使用association完成关联查询，将关联查询信息映射到pojo对象中。

4.3 一对多查询

案例：查询所有用户信息及用户相关联的订单信息
用户信息和订单信息为一对多的关系
sql语句

定义pojo类：在User中添加

public class User implements Serializable {
    private int id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址

    private List<Orders> ordersList;