Mybatis框架2 动态SQL if where choose when otherwise set foreach 多表操作 1对1 1对多

回顾:
	Mybatis操作数据库的步骤:
1.	创建工程导入坐标
2.	配置mybatis核心配置文件
3.	配置SQL语句的映射文件
4.	编写dao中的方法
5.	配置对应的sql语句
Mybatis中的核心API对象
	Resources: 加载配置文件
	SqlSessionFactoryBuilder: 创建SqlSessionFactory
	SqlSessionFactory: 工厂模式,线程安全的,单例对象
	SqlSession: 执行SQL语句,创建代理对象,不是线程安全,多例对象
#{}和${}区别:
	使用方式:
		#{}:传入对象:#{属性名},基本数据类型:#{随便写}
		${}:传入对象:${属性名},基本数据类型:${value}
	处理过程:
		#{}:占位符,内部经过preparStatment处理,经过预编译,没有SQL注入风险
		${}:SQL字符串拼接.内部经过statement处理.有SQL注入风险

1. 多参数查询
   根据用户名和性别查询
   1.1 方式一(了解)
   1.1.1 接口
   
   1.1.2 Sql映射文件
   
   1.2 方式二

通过@Param注解自定义别名
	@Param:定义到方法参数之前,自定义别名
		Value属性:别名

1.2.1 接口

1.2.2 SQL映射文件

1.3 方式三(重点)
将多个参数封装到一个对象中!
1.3.1 接口

1.3.2 SQL映射文件

2. 获取保存主键
保存之后,获取保存的主键ID
2.1 方式一
使用条件: 只能获取数据库中自动增长的id

2.2 方式二
先保存,再查询
Mysql函数: select last_insert_id() (获取最后保存id)

3. 动态SQL(重点)
在sql语句中完成判断,循环等java功能
3.1 if标签
判断标签
案例: 多条件查询

3.2 where标签

3.3 choose,when,otherwise标签
判断:获取唯一的条件
相当于java代码中的:if() elseif() else







3.4 set标签

3.5 trim标签(了解)
trim标签主要作用:替换where和set标签
用于sql代码的优化

3.6 foreach标签
案例: 多个id查询用户
作用: 在sql映射文件中循环迭代数组或者集合
3.6.1 传入集合

3.6.2 传入数组

3.6.3 传入对象

相关代码如下:

接口

package cn.itcast.dao;

import cn.itcast.domain.QueryVo;
import cn.itcast.domain.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserDao {
	/**
	 * 多个参数的查询:根据用户名和性别查询
	 *      方式一: 使用mybatis的默认参数别名(了解)
	 *          和传入的参数一一对应
	 *          第一个参数: arg0 或者 param1
	 *          第二个参数: arg1 或者 param2
	 */
	List<User> findByNameAndSex1(String username,String sex);

	/**
	 * 方式二: 使用@Param注解配置到方法参数之前,对传入的参数自定义别名
	 *          value:自定义的别名
	 */
	List<User> findByNameAndSex2(@Param("name") String username,@Param("userSex") String sex);

	/**
	 * 方式三:将多个参数封装到对象中
	 */
	List<User> findByNameAndSex3(User user);

	/**
	 * 保存用户
	 */
	void save(User user);

	/**
	 * 多条件查询
	 */
	List<User> findByUser(User user);

	/**
	 * 测试 choose , when otherwise
	 */
	List<User> findByUserChoose(User user);

	/**
	 * 更新用户
	 */
	void update(User user);

	/**
	 * 根据用户id的集合查询用户
	 */
	List<User> findByCollection(List<Integer> ids);

	/**
	 * 根据用户id的数组查询用户
	 */
	List<User> findByArray(int [] ids);

	/**
	 * 根据vo对象(封装了用户id的集合或者数组)查询用户
	 */
	List<User> findByVo(QueryVo vo);
}

实体类QueryVo

package cn.itcast.domain;

import java.util.List;

/**
 * vo:value object(值对象)
 *      参数传递
 */
public class QueryVo {
	private List<Integer> ids;

	public List<Integer> getIds() {
		return ids;
	}

	public void setIds(List<Integer> ids) {
		this.ids = ids;
	}
}

接口映射文件

<?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="cn.itcast.dao.UserDao">

    <!--
        多个参数的查询:根据用户名和性别查询
    -->
    <select id="findByNameAndSex1" resultType="cn.itcast.domain.User">
        select * from user where  sex=#{arg1} and username=#{arg0}
    </select>

    <select id="findByNameAndSex2" resultType="cn.itcast.domain.User">
        select * from user where  sex=#{userSex} and username=#{name}
    </select>

    <select id="findByNameAndSex3" resultType="cn.itcast.domain.User" parameterType="cn.itcast.domain.User">
        select * from user where  sex=#{sex} and username=#{username}
    </select>

    <!--
        保存用户,获取保存之后的主键ID
            方式一: 使用useGeneratedKeys结合keyProperty获取
                useGeneratedKeys: 配置是否获取自动增长的id (false|true)
                keyProperty: 主键id需要赋值到的对象属性
            * 只能获取数据库中自动增长的id

    <insert id="save" useGeneratedKeys="true" keyProperty="id" parameterType="cn.itcast.domain.User">
        insert into user
          (username,birthday,sex,address)
        values
          (#{username},#{birthday},#{sex},#{address})
    </insert>
     -->

    <!--
        保存用户,获取保存之后的主键ID
             方式二: 先保存,再更新(last_insert_id())
                selectKey : 获取保存之后的主键id
                        order : 当前sql语句的执行顺序(BEFORE|AFTER)
                        resultType : 配置返回值类型
                        keyProperty :主键id需要赋值到的对象属性

    -->
    <insert id="save" useGeneratedKeys="true" keyProperty="id" parameterType="cn.itcast.domain.User">
      <selectKey order="AFTER" resultType="int" keyProperty="id">
          SELECT LAST_INSERT_ID()
      </selectKey>
        insert into user
        (username,birthday,sex,address)
        values
        (#{username},#{birthday},#{sex},#{address})
    </insert>

    <!--
        多条件查询
           动态sql:if标签
           String sql = "select * from user where 1=1"
           if(username!=null && username!=""){
                and username=?
           }
           if(sex!=null && sex!='') {
                and sex=?
           }
           动态sql:where标签
              用于SQL优化,根据判断体中的内容自动的添加where条件,
              对前面多余的and进行优化(删除)
    -->
    <select id="findByUser" parameterType="cn.itcast.domain.User" resultType="cn.itcast.domain.User">
      select * from user
        <!--<where>-->
          <trim prefix="where"  prefixOverrides="and">
            <if test="username!=null and username!=''">
                and username=#{username}
            </if>
            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
          </trim>
       <!-- </where>-->
    </select>

    <!--
        动态sql: choose,when,otherwise
        相当于java代码中的  if,else if ,else
    -->
    <select id="findByUserChoose" parameterType="cn.itcast.domain.User" resultType="cn.itcast.domain.User">
      select * from user
        <where>
            <choose>
                <when test="username!=null and username!=''">
                    and username=#{username}
                </when>
                <when test="sex!=null and sex!=''">
                    and sex=#{sex}
                </when>
                <otherwise>
                    and id=45
                </otherwise>
            </choose>
        </where>
    </select>

    <!--
        更新用户
             动态sql: set
                添加更新的set语句,优化更新语句中最后多余的","
             动态sql:trim
                替换where和set
                    prefix : 自动生成标签,在判断体之前
                    suffix : 自动生成标签,在判断体之后
                    prefixOverrides : 优化SQL语句中前面多余的sql片段(, and)
                    suffixOverrides : 优化SQL语句中后面多余的sql片段(, and)
    -->
    <update id="update" parameterType="cn.itcast.domain.User">
        update user
           <!-- <set>-->
            <trim prefix="set"  suffixOverrides=",">
                <if test="username!=null and username!=''">
                    username=#{username},
                </if>
                <if test="birthday!=null">
                    birthday=#{birthday},
                </if>
                <if test="address!=null and address!=''">
                    address=#{address},
                </if>
                <if test="sex!=null and sex!=''">
                    sex=#{sex},
                </if>
            </trim>
           <!-- </set>-->
        where id=#{id}
    </update>
    
    
    <!--
        根据用户id的集合查询用户
        SQL: SELECT * FROM USER WHERE id IN (41,42,48)
        String sql = "SELECT * FROM USER  WHERE "
        sql+= "id IN (";
        for(Integer id : list) {
            sql+= id;
            sql+= ","
        }
        //处理多余的","(省略)
        sql+=")"
        动态sql : foreach标签(循环迭代)
              collection : 描述参数类型
                    传入集合数据类型: collection
                    传入数组类型(int[],String[]): array
                    传入的java对象(包含数组|集合属性): 数组|集合属性的名称
               open: 进入循环体之前执行的sql片段
               close: 执行循环体之后执行的sql片段
               item:对循环中每个元素设置别名
               separator: 拼接多个字符串的分割符号
    -->
    <select id="findByCollection" resultType="cn.itcast.domain.User">
      SELECT * FROM USER  WHERE
      <foreach collection="array" open="id IN (" close=")" item="id" separator=",">
          #{id}
      </foreach>
    </select>

    <!--
        传入数组
            collection : array
    -->
    <select id="findByArray" resultType="cn.itcast.domain.User">
        SELECT * FROM USER WHERE id in
        <foreach collection="collection" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </select>

    <!--
        传入java对象
            collection : 对象中集合或者属性的属性名
    -->
    <select id="findByVo" resultType="cn.itcast.domain.User" parameterType="cn.itcast.domain.QueryVo">
        SELECT * FROM USER WHERE id in
        <foreach collection="ids" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </select>

</mapper>

测试类

package cn.itcast.test;

import cn.itcast.dao.UserDao;
import cn.itcast.domain.QueryVo;
import cn.itcast.domain.User;
import cn.itcast.utils.BaseTestUtil;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDaoTest extends BaseTestUtil {

	@Test
	public void testFindByNameAndSex() {
		//1.创建动态代理对象(dao接口的实现类)
		UserDao userDao = session.getMapper(UserDao.class);
		//2.操作数据库
		User user = new User();
		user.setSex("男");
		user.setUsername("老王");

		List<User> list = userDao.findByNameAndSex3(user);

		for (User user1 : list) {
			System.out.println(user1);
		}
	}

	//测试保存
	@Test
	public void testSave() {
		//1.创建动态代理对象(dao接口的实现类)
		UserDao userDao = session.getMapper(UserDao.class);
		//2.操作数据库
		User user = new User();
		user.setUsername("王者荣耀");
		user.setBirthday(new Date());
		user.setSex("女");
		user.setAddress("幼儿园");
		userDao.save(user);
		//获取保存之后的id
		System.out.println("主键="+user.getId());
	}

	//测试多条件查询(if标签)
	@Test
	public void testFindByUser() {
		User user = new User();
		user.setUsername("王者荣耀");
		user.setSex("女");

		//1.创建动态代理对象(dao接口的实现类)
		UserDao userDao = session.getMapper(UserDao.class);
		//2.操作数据库
		List<User> users = userDao.findByUser(user);
		for (User user1 : users) {
			System.out.println(user1);
		}
	}

	//测试choose,when,otherwise
	@Test
	public void testFindByUserChoose() {
		User user = new User();
		//user.setUsername("王者荣耀");
		//user.setSex("女");

		//1.创建动态代理对象(dao接口的实现类)
		UserDao userDao = session.getMapper(UserDao.class);
		//2.操作数据库
		List<User> users = userDao.findByUserChoose(user);
		for (User user1 : users) {
			System.out.println(user1);
		}
	}

	//测试更新
	@Test
	public void testUpdate() {
		User user = new User();
		user.setUsername("LOL1");
		user.setSex("男");
		user.setId(243);

		//1.创建动态代理对象(dao接口的实现类)
		UserDao userDao = session.getMapper(UserDao.class);
		//2.操作数据库
		userDao.update(user);
	}

	//测试foreach标签,传递集合
	@Test
	public void testFindByCollection() {
		List<Integer> ids = new ArrayList<>();
		ids.add(41);
		ids.add(42);
		ids.add(48);

		//1.创建动态代理对象(dao接口的实现类)
		UserDao userDao = session.getMapper(UserDao.class);
		//2.操作数据库
		List<User> list = userDao.findByCollection(ids);

		for (User user : list) {
			System.out.println(user);
		}
	}

	//测试foreach标签,传递数组
	@Test
	public void testFindByArray() {
		int [] ids = new int[]{41,42,48};

		//1.创建动态代理对象(dao接口的实现类)
		UserDao userDao = session.getMapper(UserDao.class);
		//2.操作数据库
		List<User> list = userDao.findByArray(ids);

		for (User user : list) {
			System.out.println(user);
		}
	}

	//测试foreach标签,传递对象
	@Test
	public void testFindByVo() {
		List<Integer> ids = new ArrayList<>();
		ids.add(41);
		ids.add(42);
		ids.add(48);
		QueryVo vo = new QueryVo();
		vo.setIds(ids);

		//1.创建动态代理对象(dao接口的实现类)
		UserDao userDao = session.getMapper(UserDao.class);
		//2.操作数据库
		List<User> list = userDao.findByVo(vo);

		for (User user : list) {
			System.out.println(user);
		}
	}
}

4. Mybatis中的多表操作
   4.1 表关系
   一对一
   一对多: 通过外键描述
   案例: 用户和账户
   
   多对多: 通过中间表描述
   案例: 用户和角色
   
   4.2 一对多查询
   4.2.1 账户到用户的多对一关系(一对一关系)

   案例: 查询所有的账户以及每个账户所属的用户
   SQL: SELECT a.id aid,a.uid,a.money,u.* FROM account a,USER u WHERE a.uid=u.id

4.2.1.1 接口
4.2.1.2 映射文件

测试类:

4.2.2 用户到账户的一对多关系

案例: 查询所有用户,以及每个用户的所有账户
SQL: SELECT u.*, a.id aid,a.uid,a.money FROM USER u LEFT JOIN account a ON u.id=a.uid

4.2.2.2 接口

4.2.2.3 映射文件

测试类:

4.3 多对多查询
4.3.1 用户到角色的一对多

案例: 查询所有用户以及每个用户的所有角色
SQL: SELECT u.*,r.id rid,r.role_desc ,r.role_name FROM USER u LEFT JOIN user_role ur ON u.id=ur.uid LEFT JOIN role r ON ur.rid=r.id

4.3.1.1 接口

4.3.1.2 映射文件

测试类:

4.3.2 角色到用户的一对多(作业)

面试问题
	主键获取方式: (selectKey)
	动态SQL 
		If,where,set,foreach
作业
1.	完成今日代码
2.	研究一对多查询的其他两种方式
3.	角色到用户的一对多