[大数据]MyBatis详解（二）模糊查询#{}和${}区别、动态sql、多表关系

模糊查询#{}

映射文件：

<!--
     //根据名称模糊查询
    public List<TUser> findByName();
    parameterType="" 一个参数可省略不写   写的话 全限定类名
    SELECT * FROM USER WHERE username LIKE '%王%';
    -->
    <select id="findByName" resultType="com.llz.domain.TUser" parameterType="java.lang.String">
        SELECT * FROM USER WHERE username LIKE #{随便写}
    </select>

测试类：

 @Test
 public void testFindByName(){
     UserDao userDao = sqlSession.getMapper(UserDao.class);
     List<TUser> userList = userDao.findByName("%王%");
     for (TUser tUser : userList) {
         System.out.println(tUser);
     }
 }

模糊查询${}

映射文件：

<!--
     //根据名称模糊查询
    public List<TUser> findByName2(String username);

    SELECT * FROM USER WHERE username LIKE %#{随便写}% 报错
    SELECT * FROM USER WHERE username LIKE '%#{随便写}%' 报错
        Parameter index out of range (1 > number of parameters, which is 0).
        '#{}' 将语法也变成字符串
    SELECT * FROM USER WHERE username LIKE '%'?'%'   语法对 但查询不出数据 %就是个字符
    SELECT * FROM USER WHERE username LIKE concat('%',#{随便写},'%') ok 拼接字符串


    参数类型 有且仅有一个值 ${}   value  不能随便写
    参数类型有多个  请参考 #{} 的语法  一模一样
        例如参数类型是user  支持 ${username} ${gender}
    -->
    <select id="findByName2" resultType="com.itheima.domain.TUser" parameterType="java.lang.String">
        SELECT * FROM USER WHERE username LIKE '%${value}%'
    </select>

测试类：

 @Test
 public void testFindByName2(){
     UserDao userDao = sqlSession.getMapper(UserDao.class);
     List<TUser> userList = userDao.findByName2("王");
     for (TUser tUser : userList) {
         System.out.println(tUser);
     }
 }

问题: 为什么要出现两种语法  #{}   ${}
区别:
    #{} :  SELECT * FROM USER WHERE username LIKE ?   Parameters: %王%(String)
        底层使用的是preparedStatement预处理对象 发送sql的时候是?
        因此 '#{}' 当作字符串处理 不能采取字符串拼接
    ${} :  SELECT * FROM USER WHERE username LIKE '%王%'   Parameters:
        底层使用statement , 可以进行字符串拼接   '%${value}%'  采用的字符串拼接
        效果等同于   concat('%',#{随便写},'%')

#{}和${}区别

添加后返回组件id

方式一

映射文件：

<!--
      //保存用户
    void save(TUser tUser);
        useGeneratedKeys="true"  表示新增使用 需要使用数据库的主键
        keyProperty="id"  拿到数据库的主键时  将主键放入id字段
        不支持oracel   使用用于mysql
    -->
    <insert id="save" useGeneratedKeys="true" keyProperty="id" parameterType="com.llz.domain.TUser">
        insert into user values(#{id} , #{username} , #{birthday} , #{sex} , #{address})
    </insert>

方式二

使用 SELECT LAST_INSERT_ID() 再查询一次id
映射文件：

<!--
        再查询一次 , 查询最后一条新增的记录
         #查询  最后添加 id  必须放在事务中  不能单独运行
         SELECT LAST_INSERT_ID();

         <selectKey> 查询主键
            order="AFTER" 表示执行sql语句之后 执行标签内部代码
            keyProperty="id"   查询到主键后放入实体类中哪个字段
            resultType="java.lang.Integer"   返回的数据类型

            keyColumn="" 数据库字段名称  可省略

                 SELECT LAST_INSERT_ID();
    -->
    <insert id="save" parameterType="com.llz.domain.TUser">
        <selectKey order="AFTER" keyProperty="id" resultType="java.lang.Integer" keyColumn="id" >
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into user values(#{id} , #{username} , #{birthday} , #{sex} , #{address})
    </insert>

测试类：

@Test
 public void testSave(){
     UserDao userDao = sqlSession.getMapper(UserDao.class);
     TUser tUser =  new TUser();
     tUser.setUsername("张三");
     tUser.setAddress("上海");
     tUser.setSex("嫐");
     tUser.setBirthday(new Date());
     userDao.save(tUser);
     System.out.println(tUser);
 }

动态sql

sql语句不是一成不变的，而是需要动态的变化。

if

映射文件：

<!--
    //条件不确定有可能是id 和 地址
    List<TUser> findByCondition(TUser tUser);
    sql语句可能要支持的情况
    1. 只有id的情况   select * from user where id >#{id}
    2. 只有地址的情况  select * from user where address=#{address}
    3. id和地址都有的情况  select * from user where id >#{id} and address=#{address}
    4. id和 地址都没有的情况  select * from user
    <if test="条件表达式">
        条件成功的情况
    </if> 没有else if
    <if test="id != null and id != 0"> 没有equals 都是值判断  没有 && 或者 || 都是用and和 or
          where id >#{id}
        </if>
    -->
    <select id="findByCondition" parameterType="com.llz.domain.TUser"  resultType="com.llz.domain.TUser">
        select * from user
        <if test="id != null and id != 0">
          where id >#{id}
        </if>

where

<!--
    //条件不确定有可能是id 和 地址
    List<TUser> findByCondition(TUser tUser);
    sql语句可能要支持的情况
    1. 只有id的情况   select * from user where id >#{id}
    2. 只有地址的情况  select * from user where address=#{address}
    3. id和地址都有的情况  select * from user where id >#{id} and address=#{address}
    4. id和 地址都没有的情况  select * from user

    <if test="条件表达式">
        条件成功的情况
    </if> 没有else if
    <if test="id != null and id != 0"> 没有equals 都是值判断  没有 && 或者 || 都是用and和 or
          where id >#{id}
        </if>
      select * from 表名  where 条件 and 条件  and 条件 and 条件 and 条件
      第一个条件 不能够影响 sql执行 又必须存在(目的先将where引入出来)
    -->
    <select id="findByCondition" parameterType="com.llz.domain.TUser"  resultType="com.llz.domain.TUser">
        select * from user where 1=1
        <if test="id != null and id != 0">
            and id >#{id}
        </if>
        <if test="address !=null and address!=''">
            and address = #{address}
        </if>
    </select>

 <!--
    //条件不确定有可能是id 和 地址
    List<TUser> findByCondition(TUser tUser);
    sql语句可能要支持的情况
    1. 只有id的情况   select * from user where id >#{id}
    2. 只有地址的情况  select * from user where address=#{address}
    3. id和地址都有的情况  select * from user where id >#{id} and address=#{address}
    4. id和 地址都没有的情况  select * from user
    <if test="条件表达式">
        条件成功的情况
    </if> 没有else if
    <if test="id != null and id != 0"> 没有equals 都是值判断  没有 && 或者 || 都是用and和 or
          where id >#{id}
        </if>
      select * from 表名  where 条件 and 条件  and 条件 and 条件 and 条件
      第一个条件 不能够影响 sql执行 又必须存在(目的先将where引入出来)
       select * from user where 1=1
        <if test="id != null and id != 0">
            and id >#{id}
        </if>
        <if test="address !=null and address!=''">
            and address = #{address}
        </if>
  <where> 引出where , 将拼接的条件 第一个and 修改成where
    select * from user and 条件 and 条件 and 条件 and 条件
    select * from user where 条件 and 条件 and 条件 and 条件
    如果没有任何条件 不修改
    -->
    <select id="findByCondition" parameterType="com.llz.domain.TUser"  resultType="com.llz.domain.TUser">
        select * from user
        <where>
            <if test="id != null and id != 0">
                and id >#{id}
            </if>
            <if test="address !=null and address!=''">
                and address = #{address}
            </if>
        </where>
    </select>

set

<!--
        //修改用户
    void update(TUser tUser);
    用户修改信息 有的不想改  有的想改
    ,sql语句也需要动态变化
    sql语句中最后一个结束的if标签不允许有逗号

    <set></set> 跟where标签类似
    <set> 替换set语法
    1.加入set的关键字
    2.将条件中的最后一个逗号删除
    -->
    <update id="update" parameterType="com.llz.domain.TUser">
        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>

foreach

映射文件：

<!--
       //根据数组查询
    List<TUser> findByArray(Integer[] ids);
    select * from 表名 where id in (52 ,53 , 54 , 55 )
     parameterType="integer[]" 别名的写法
     parameterType="int[]"  别名的写法
        this.registerAlias("int[]", Integer[].class);  已经提前声明
        this.registerAlias("integer[]", Integer[].class); 已经提前声明
     parameterType : 全限定类名
     parameterType="java.lang.String" 全限定类名
     parameterType="string" 别名
     基本类型 在mybatis中都有设置别名
     Configuration : 配置类
     中定义了 TypeAliasRegistry : Type类型 别名Alias 注册器  初始化了一些别名
     select * from 表名 where id in (52 ,53 , 54 , 55 )
     Integer [] ids = {52, 53 , 54 ,55};
     String sql = " select * from user where id in ";
     sql+= " ( ";     open="循环开始"
     for(int i = 0 ; i < ids.length ; i ++){
        sql += ids[i]  +","
        //判断 如果是最后一次循环 不加逗号
     }
     sql+= " ) ";  close=")"
     //整个循环走完 删除最后一个逗号
     select * from 表名 where id in (52 ,53 , 54 , 55)
     /
    <foreach
    collection="遍历的集合array"  写array 表示遍历的对象是数组
    open="循环开始"  sql+= " ( ";
    close="循环结束"  sql+= " ) ";
    item="每一次循环的变量"  相当于i   获得值的写法  #{变量的名称}
    separator="" 每一次遍历需要拼接的内容 (分隔符)    +","
    ></foreach>
    -->
    <select id="findByArray" resultType="com.llz.domain.TUser" parameterType="int[]">
      select * from user where id in
      <foreach collection="array" open="(" close=")" separator="," item="tempId">
          #{tempId}
      </foreach>
    </select>

测试类：

@Test
    public void testFindByArray(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);

        Integer [] ids = {52, 53 , 54 ,55};

        List<TUser> users = userDao.findByArray(ids);
        for (TUser user : users) {
            System.out.println(user);
        }

    }

参数类型-集合

<!--
        //根据集合查询
    List<TUser> findByList(List<Integer> myList);

        collection=""  collection="list" 表示遍历的是list集合
        collection=""  collection="collection" 表示遍历的是集合
    -->
    <select id="findByList" resultType="com.llz.domain.TUser" parameterType="list">
        select * from user where id in
        <foreach collection="list" open="(" close=")" separator="," item="tempId">
            #{tempId}
        </foreach>
    </select>

参数类型-pojo：

<!--
        //根据QueryVo查询 其本质 其实还是数组 或者 list
    List<TUser> findByQueryVo(QueryVo queryVo);
        parameterType="" 类的全限定类名 可以给别名 但是我们现在没有
        传入的参数是对象 #{对象中的属性名称或者get方法}
        collection="" 传入的是对象的情况 写入的不是类型  而是 对象中的 属性名称(get方法去掉get首字母小写名称)
        使用对象包装条件 好处在于:我们不需要考虑类型方面的问题 只需要考虑 属性的名称即可
    -->
    <select id="findByQueryVo" resultType="com.llz.domain.TUser" parameterType="com.llz.domain.QueryVo">
        select * from user where id in
        <foreach collection="myList"  open="(" close=")" separator="," item="tempId" >
            #{tempId}
        </foreach>
    </select>

QueryVo类：

/**
 * 自定义的专门用来封装数据库查询条件
 */
public class QueryVo {
    private Integer [] myArray;

    private List<Integer> myList;

    public Integer[] getMyArray() {
        return myArray;
    }

    public void setMyArray(Integer[] myArray) {
        this.myArray = myArray;
    }

    public List<Integer> getMyList() {
        return myList;
    }

    public void setMyList(List<Integer> myList) {
        this.myList = myList;
    }
}

sql和include

sql:定义基本sql
include:引入sql

 <select id="findByQueryVo" resultType="com.llz.domain.TUser" parameterType="com.llz.domain.QueryVo">
        <include refid="baseSql"></include>
        where id in
        <foreach collection="myList"  open="(" close=")" separator="," item="tempId" >
            #{tempId}
        </foreach>
    </select>


    <!--
     <include refid="baseSql"></include>  包含 引入xxxsql   refid="" reference 指向 id
    -->
    <!--定义一条公共sql语句    id="" 命名  base基本的Sql -->
    <sql id="baseSql">
        select id , username ,birthday , sex ,address from user
    </sql>

总结:

动态sql : 指的是sql语句 会根据传入的内容不同 sql语句动态的变化
if : 判断条件是否成立 一般用于 查询的条件 用于新增 用于修改
where : 给查询使用 将第一个出现的and关键字替换成 where关键字
set : 用于修改, 只修改部分字段 先加上set关键字 , 去掉 key=value,key=value , 的最后一个逗号 使语法完整
foreach : 遍历 数组 , 集合 (注意类型) , 遍历值对象中的属性 (不需要考虑类型 只需要考虑变量的名称)
sql和include : sql提取公共的sql语句 而使用include可以引入公共sql语句

多表关系

在数据阶段 一对多和多对多重要
mybatis的核心 一对一和一对多重要
案例:用户和账户
一对一: 账户和用户 每一个账户只对应一个用户
一对多: 用户和账户 一个用户具有多个账户
多对多: 两个一对多之间的关系

数据准备

#用户表
CREATE TABLE `tbl_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) DEFAULT NULL,
`password` VARCHAR(50) DEFAULT NULL,
`gender` VARCHAR(11) DEFAULT NULL,
`email` VARCHAR(50) DEFAULT NULL,
`telephone` VARCHAR(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#账户表
CREATE TABLE `tbl_account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`money` INT(11) DEFAULT NULL,
`address` VARCHAR(32) DEFAULT NULL,
`aname` VARCHAR(32) DEFAULT NULL,
`uid` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#添加数据
INSERT INTO tbl_user VALUES(NULL,'张三','1234' , '男','zhangsan@qq.cn'
,'123456789');
INSERT INTO tbl_user VALUES(NULL,'李四','2234' , '男','lisi@aa.cn'
,'123456789');
INSERT INTO tbl_user VALUES(NULL,'王五','3234' , '女','wangwu@cc.cn'
,'123456789');
INSERT INTO tbl_user VALUES(NULL,'赵六','4234' , '男','zhaoliu@dd.cn'
,'123456789');
INSERT INTO tbl_user VALUES(NULL,'田七','9999' , '女','tianqi@ee.cn'
,'123456789');
INSERT INTO tbl_account VALUES(NULL,2000,'北京顺义招商分行地址','招商银行顺义分行' ,
1);
INSERT INTO tbl_account VALUES(NULL,3000,'北京海淀招商分行地址','招商银行顺义分行' ,
1);
INSERT INTO tbl_account VALUES(NULL,4000,'北京昌平招商分行地址','招商银行顺义分行' ,
1);
INSERT INTO tbl_account VALUES(NULL,5000,'北京通州招商分行地址','招商银行顺义分行' ,
1);
INSERT INTO tbl_account VALUES(NULL,6000,'北京大兴招商分行地址','招商银行顺义分行' ,
1);
INSERT INTO tbl_account VALUES(NULL,2000,'天津招商分行地址1','招商银行天津分行' , 2);
INSERT INTO tbl_account VALUES(NULL,3000,'天津招商分行地址2','招商银行天津分行' , 2);
INSERT INTO tbl_account VALUES(NULL,4000,'天津招商分行地址3','招商银行天津分行' , 2);
INSERT INTO tbl_account VALUES(NULL,5000,'天津招商分行地址4','招商银行天津分行' , 2);
INSERT INTO tbl_account VALUES(NULL,6000,'天津招商分行地址5','招商银行天津分行' , 2);
INSERT INTO tbl_account VALUES(NULL,2000,'河北招商分行地址6','招商银行河北分行' , 3);
INSERT INTO tbl_account VALUES(NULL,3000,'河北招商分行地址7','招商银行河北分行' , 3);
INSERT INTO tbl_account VALUES(NULL,4000,'河北招商分行地址8','招商银行河北分行' , 3);
INSERT INTO tbl_account VALUES(NULL,5000,'河北招商分行地址9','招商银行河北分行' , 3);
INSERT INTO tbl_account VALUES(NULL,6000,'河北招商分行地址10','招商银行河北分行',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="com.llz.dao.AccountDao">

  <!--
    resultMap 手动封装映射数据
       type="com.llz.domain.Account" 手动封装后的结果是什么类型
        <id column="数据库列名" property="实体类的属性名"></id>

        <association property=""> 一对一  封装的是一个对象
        property="" 封装的哪个属性名称
        javaType="" 封装属性的类型 全限定类名
  -->
  <resultMap id="baseAccountUser" type="com.llz.domain.Account">
      <!--1.主键-->
      <id column="id" property="id"></id>
      <!--2.普通字段-->
      <result column="money" property="money"></result>
      <result column="address" property="address"></result>
      <result column="aname" property="aname"></result>
      <result column="uid" property="uid"></result>
      <!--3.外键 association 配置一对一的标签 -->
      <association property="user" javaType="com.llz.domain.User">
          <!--3.1 主键-->
          <id column="id" property="id"></id>
          <!--3.2 普通字段-->
          <result column="username" property="username"></result>
          <result column="password" property="password"></result>
          <result column="gender" property="gender"></result>
          <result column="email" property="email"></result>
          <result column="telephone" property="telephone"></result>
          <!--3.3 外键-->
      </association>
  </resultMap>
  <!--
  //查询所有的account数据
    public List<Account> findAll();
      `resultType="com.llz.domain.Account" 自动映射
    -->
  <select id="findAll" resultMap="baseAccountUser" >
    SELECT * FROM tbl_account ta
	  LEFT JOIN tbl_user tu ON ta.uid=tu.id
  </select>
</mapper>

一对多配置

查询客户，以客户为主

映射文件

<?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.llz.dao.UserDao">


    <!--
         <collection > 配置一对多 集合对象
            property=""   封装到对象中的属性名称
            javaType=""   集合的外面类型   List
            ofType=""     集合内部的类型   全限定类名
    -->
    <resultMap id="baseUserAccount" type="com.llz.domain.User">
        <!--主键-->
        <id column="id" property="id"></id>
        <!--普通字段-->
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="gender" property="gender"></result>
        <result column="email" property="email"></result>
        <result column="telephone" property="telephone"></result>
        <!--配置外键-->
        <collection property="accounts" javaType="list" ofType="com.llz.domain.Account">
            <!--主键-->
            <id column="id" property="id"></id>
            <!--普通字段-->
            <result column="money" property="money"></result>
            <result column="address" property="address"></result>
            <result column="aname" property="aname"></result>
            <result column="uid" property="uid"></result>
            <!--外键-->
        </collection>
    </resultMap>
  <!--
      //查询所有的用户信息
    public List<User> findAll();
     resultType="com.llz.domain.User" 自动封装
  -->
  <select id="findAll" resultMap="baseUserAccount">
     SELECT * FROM tbl_user tu
	    LEFT JOIN  tbl_account ta ON ta.uid=tu.id
  </select>
</mapper>

别名设置

在主配置文件中加入如下信息

<!-- 自己设置别名 TypeAlias 别名设置 只能在主配置文件中配置-->
  <!--
    方式1:
    <typeAlias type="全限定类名"  alias="设置的别名 建议别名和类名一致"></typeAlias>
    方式2:
    <package name="配置到包的路径下即可 该包下所有的类自动使用别名"></package>
    别名自动设置 , 以类名的方式设置别名(不区分大小写)
  -->
  <typeAliases>
    <!--<typeAlias type="com.llz.domain.Account" alias="account"></typeAlias>
    <typeAlias type="com.llz.domain.User" alias="user"></typeAlias>-->
    <package name="com.llz.domain"></package>
  </typeAliases>

主配置文件的映射配置

<!--
    如果有一万个xml  如下代码得配置一万次
    三种方式配置  ,xml目的是为了描述接口
    1. 配置xml  <mapper resource="com/llz/dao/AccountDao.xml"></mapper>  配置xml
    2. 配置到接口下   <mapper class="com.llz.dao.AccountDao"></mapper>  没用 为了第三种铺垫的
      条件前提 : xml的名称必须跟接口名称一模一样 , 必须放在跟接口同名的目录结构下
    3. 配置到包下  <package name="com.llz.dao"></package>
    条件前提 : xml的文件名称必须跟接口文件名称一模一样 , 必须放在跟接口同名的目录结构下
  -->
  <mappers>
      <!--映射文件所在的位置-->
      <!--<mapper resource="com/llz/dao/AccountDao.xml"></mapper>-->
      <!--<mapper class="com.llz.dao.AccountDao"></mapper>-->
      <package name="com.llz.dao"></package>
  </mappers>

总结

多表关系: 其实本质还是单表
一对一 (重点配置 )
一对多 (重点配置)
?核心思想:

1. 保证查询出来的就是多个表信息
2. 在实体类中准备字段 属性 对象 封装数据 一对一采用的对象存储数据 一对多采用的是集合存储数据
3. 必须手动映射 将所需要的数据封装到指定字段中