05—动态sql

1.创建表

CREATE TABLE tb_employee (
  ID INT(11) PRIMARY KEY AUTO_INCREMENT,
  loginname VARCHAR(18),
  PASSWORD VARCHAR(18),
  NAME VARCHAR(18) DEFAULT NULL,
  SEX CHAR(2) DEFAULT NULL,
  AGE INT(11) DEFAULT NULL,
  phone VARCHAR(21),
  sal DOUBLE,
  state VARCHAR(18)
 )
 
 INSERT INTO tb_employee(loginname,PASSWORD,NAME,sex,age,phone,sal,state)
 VALUES('jack','123456','杰克','男',30,'15012345678',5000,'1');
  INSERT INTO tb_employee(loginname,PASSWORD,NAME,sex,age,phone,sal,state)
 VALUES('rose','123456','露丝','女',28,'135612345678',4000,'1');

 2.创建实体
Employee.java

package org.fkit.domain;
import java.io.Serializable;
public class Employee implements Serializable {
    
    private Integer id;             // 主键id
    private String loginname;     // 登录名
    private String password;     // 密码
    private String name;         // 真实姓名
    private String sex;             // 性别
    private Integer age;         // 年龄
    private String phone;         // 电话
    private Double sal;             // 薪水
    private String state;          // 状态
    
    public Employee() {
        super();
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getLoginname() {
        return loginname;
    }
    public void setLoginname(String loginname) {
        this.loginname = loginname;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public Double getSal() {
        return sal;
    }
    public void setSal(Double sal) {
        this.sal = sal;
    }
    public String getState() {
        return state;
    }
    public void setState(String state) {
        this.state = state;
    }
    @Override
    public String toString() {
        return "data [id=" + id + ", loginname=" + loginname
                + ", password=" + password + ", name=" + name + ", sex=" + sex
                + ", age=" + age + ", phone=" + phone + ", sal=" + sal
                + ", state=" + state + "]";
    }    
}

3.mapper创建

EmployeeMapper.java

package org.fkit.mapper;
import java.util.HashMap;
import java.util.List;
import org.fkit.domain.Employee;
public interface EmployeeMapper {
    List<Employee> selectEmployeeByIdLike(HashMap<String, Object> params);
    List<Employee> selectEmployeeByLoginLike(HashMap<String, Object> params);
    List<Employee> selectEmployeeChoose(HashMap<String, Object> params);
    List<Employee> findEmployeeLike(HashMap<String, Object> params);
    List<Employee> selectEmployeeLike(HashMap<String, Object> params);
    Employee selectEmployeeWithId(Integer id);
    void updateEmployeeIfNecessary(Employee employee);
    List<Employee> selectEmployeeIn(List<Integer> ids);
    List<Employee> selectEmployeeLikeName(Employee employee);
}

EmployeeMapper.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">
<!-- namespace指用户自定义的命名空间。 -->
<mapper namespace="org.fkit.mapper.EmployeeMapper">

    <select id="selectEmployeeWithId" parameterType="int" resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee where id = #{id}
  </select>
    
  <!-- if -->
  <select id="selectEmployeeByIdLike" 
      resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee WHERE state = '1'
      <!-- 可选条件，如果传进来的参数有id属性，则加上id查询条件 -->
      <if test="id != null ">
          and id = #{id}
      </if>
  </select>
  
  <!-- if -->
  <select id="selectEmployeeByLoginLike" 
      resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee WHERE state = '1'
      <!-- 两个可选条件，例如登录功能的登录名和密码查询 -->
      <if test="loginname != null and password != null">
          and loginname = #{loginname} and password = #{password}
      </if>
  </select>
  
  <!-- choose（when、otherwise） -->
  <select id="selectEmployeeChoose" 
      parameterType="hashmap" 
      resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee WHERE state = '1'
      <!-- 如果传入了id，就根据id查询，没有传入id就根据loginname和password查询，否则查询sex等于男的数据 -->
      <choose>
          <when test="id != null">
              and id = #{id}
          </when>
          <when test="loginname != null and password != null">
              and loginname = #{loginname} and password = #{password}
          </when>
          <otherwise>
              and sex = '男'
          </otherwise>
      </choose>
  </select>
  
  <select id="findEmployeeLike"  
      resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee WHERE 
      <if test="state != null ">
          state = #{state}
      </if>
      <if test="id != null ">
          and id = #{id}
      </if>
      <if test="loginname != null and password != null">
          and loginname = #{loginname} and password = #{password}
      </if>
  </select>
  
  <!-- where -->
  <select id="selectEmployeeLike" 
      resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee  
      <where>
          <if test="state != null ">
              state = #{state}
          </if>
          <if test="id != null ">
              and id = #{id}
          </if>
          <if test="loginname != null and password != null">
              and loginname = #{loginname} and password = #{password}
          </if>
      </where>
  </select>
  
  <!-- set -->
  <update id="updateEmployeeIfNecessary" 
      parameterType="org.fkit.domain.Employee">
      update tb_employee
        <set>
          <if test="loginname != null">loginname=#{loginname},</if>
          <if test="password != null">password=#{password},</if>
          <if test="name != null">name=#{name},</if>
          <if test="sex != null">sex=#{sex},</if>
          <if test="age != null">age=#{age},</if>
          <if test="phone != null">phone=#{phone},</if>
          <if test="sal != null">sal=#{sal},</if>
          <if test="state != null">state=#{state}</if>
        </set>
      where id=#{id}
    </update>
  
  <!-- foreach -->
  <select id="selectEmployeeIn" resultType="org.fkit.domain.Employee">
      SELECT *
      FROM tb_employee
      WHERE ID in
      <foreach item="item" index="index" collection="list"
          open="(" separator="," close=")">
            #{item}
      </foreach>
  </select>
  
  <!-- bind -->
    <select id="selectEmployeeLikeName"  resultType="org.fkit.domain.Employee">
      <bind name="pattern" value="'%' + _parameter.getName() + '%'" />
          SELECT * FROM tb_employee
          WHERE loginname LIKE #{pattern}
    </select>
</mapper>

知识点描述：

（1）.if

if用来判断字段是否有值，值是否为空，或者判断一些值不会让sql语句出错等

<!-- if -->
  <select id="selectEmployeeByIdLike" 
      resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee WHERE state = '1'
      <!-- 可选条件，如果传进来的参数有id属性，则加上id查询条件 -->
      <if test="id != null ">
          and id = #{id}
      </if>
  </select>
  
  <!-- if -->
  <select id="selectEmployeeByLoginLike" 
      resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee WHERE state = '1'
      <!-- 两个可选条件，例如登录功能的登录名和密码查询 -->
      <if test="loginname != null and password != null">
          and loginname = #{loginname} and password = #{password}
      </if>
  </select>

（2）.choose(when、otherwise)
有时候，我们不想用所有的条件语句，而只想从中选择其一二，对与这种情况，mybatis提供了chooose元素，它有点像java中的switch。

<!-- choose（when、otherwise） -->
  <select id="selectEmployeeChoose" 
      parameterType="hashmap" 
      resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee WHERE state = '1'
      <!-- 如果传入了id，就根据id查询，没有传入id就根据loginname和password查询，否则查询sex等于男的数据 -->
      <choose>
          <when test="id != null">
              and id = #{id}
          </when>
          <when test="loginname != null and password != null">
              and loginname = #{loginname} and password = #{password}
          </when>
          <otherwise>
              and sex = '男'
          </otherwise>
      </choose>
  </select>

（3）.where

 <!-- where -->
  <select id="selectEmployeeLike" 
      resultType="org.fkit.domain.Employee">
      SELECT * FROM tb_employee  
      <where>
          <if test="state != null ">
              state = #{state}
          </if>
          <if test="id != null ">
              and id = #{id}
          </if>
          <if test="loginname != null and password != null">
              and loginname = #{loginname} and password = #{password}
          </if>
      </where>
  </select>

（4）.set

动态更新语句使用set元素，set元素可以被用于动态包含需要更新字段。

<!-- set -->
  <update id="updateEmployeeIfNecessary" 
      parameterType="org.fkit.domain.Employee">
      update tb_employee
        <set>
          <if test="loginname != null">loginname=#{loginname},</if>
          <if test="password != null">password=#{password},</if>
          <if test="name != null">name=#{name},</if>
          <if test="sex != null">sex=#{sex},</if>
          <if test="age != null">age=#{age},</if>
          <if test="phone != null">phone=#{phone},</if>
          <if test="sal != null">sal=#{sal},</if>
          <if test="state != null">state=#{state}</if>
        </set>
      where id=#{id}
  </update>

（5）.foreach

对一个集合操作的时间使用，通常发生在构建in条件语句时。

<!-- foreach -->
  <select id="selectEmployeeIn" resultType="org.fkit.domain.Employee">
      SELECT *
      FROM tb_employee
      WHERE ID in
      <foreach item="item" index="index" collection="list"
          open="(" separator="," close=")">
            #{item}
      </foreach>
  </select>

（6）.bind
bind元素可以从ognl表达式中创建一个变量并将其绑定到上下文中。

 <!-- bind -->
    <select id="selectEmployeeLikeName"  resultType="org.fkit.domain.Employee">
      <bind name="pattern" value="'%' + _parameter.getName() + '%'" />
          SELECT * FROM tb_employee
          WHERE loginname LIKE #{pattern}
    </select>

4.mybatis-config.xml

log4j.properties

# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.org.fkit.mapper.EmployeeMapper=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

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">
  <!--  XML 配置文件包含对 MyBatis 系统的核心设置 -->
<configuration>
    <!-- 指定 MyBatis 所用日志的具体实现 -->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
        <!-- 要使延迟加载生效必须配置下面两个属性 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    <environments default="mysql">
    <!-- 环境配置，即连接的数据库。 -->
    <environment id="mysql">
    <!--  指定事务管理类型，type="JDBC"指直接简单使用了JDBC的提交和回滚设置 -->
      <transactionManager type="JDBC"/>
      <!--  dataSource指数据源配置，POOLED是JDBC连接对象的数据源连接池的实现。 -->
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
      </dataSource>
    </environment>
  </environments>
  <!-- mappers告诉了MyBatis去哪里找持久化类的映射文件 -->
  <mappers>
      <mapper resource="org/fkit/mapper/EmployeeMapper.xml"/>
  </mappers>
</configuration>

5.测试

package org.fkit.test;
import java.io.InputStream;
import java.util.ArrayList;
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.fkit.domain.Employee;
import org.fkit.mapper.EmployeeMapper;

public class DynamicSQLTest {
    public static void main(String[] args) throws Exception {
        // 读取mybatis-config.xml文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        // 初始化mybatis，创建SqlSessionFactory类的实例
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(inputStream);
        // 创建Session实例
        SqlSession session = sqlSessionFactory.openSession();
        DynamicSQLTest t = new DynamicSQLTest();
        t.testSelectEmployeeByIdLike(session);
               //t.testSelectEmployeeByLoginLike(session);
               //t.testSelectEmployeeChoose(session);
               //t.testFindEmployeeLike(session);
               //t.testSelectEmployeeLike(session);
               //t.testUpdateEmployeeIfNecessary(session);
               //t.testSelectEmployeeIn(session);
        //t.testSelectEmployeeLikeName(session);
        // 提交事务
        session.commit();
        // 关闭Session
        session.close();
    }
    
    // 测试<select id="selectEmployeeByIdLike" ...>
    public void testSelectEmployeeByIdLike(SqlSession session){
        // 获得EmployeeMapper接口的代理对象
        EmployeeMapper em = session.getMapper(EmployeeMapper.class);
        // 创建一个HashMap存储参数
        HashMap<String, Object> params = new HashMap<String, Object>();
        // 设置id属性
        //params.put("id", 1);
        // 调用EmployeeMapper接口的selectEmployeeByIdLike方法
        List<Employee> list = em.selectEmployeeByIdLike(params);
        // 查看返回结果
        list.forEach(employee -> System.out.println(employee));
    }
    
    public void testSelectEmployeeByLoginLike(SqlSession session){
        EmployeeMapper em = session.getMapper(EmployeeMapper.class);
        HashMap<String, Object> params = new HashMap<String, Object>();
        // 设置loginname和password属性
        params.put("loginname", "jack");
        params.put("password", "123456");
        List<Employee> list = em.selectEmployeeByLoginLike(params);
        list.forEach(employee -> System.out.println(employee));
    }
    
    public void testSelectEmployeeChoose(SqlSession session){
        EmployeeMapper em = session.getMapper(EmployeeMapper.class);
        HashMap<String, Object> params = new HashMap<String, Object>();
        // 设置id属性
//        params.put("id", 1);
//        params.put("loginname", "jack");
//        params.put("password", "123456");
        List<Employee> list = em.selectEmployeeChoose(params);
        list.forEach(employee -> System.out.println(employee));
    }
    
    public void testFindEmployeeLike(SqlSession session){
        EmployeeMapper em = session.getMapper(EmployeeMapper.class);
        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("id", 1);
        List<Employee> list = em.findEmployeeLike(params);
        list.forEach(employee -> System.out.println(employee));
    }
    
    public void testSelectEmployeeLike(SqlSession session){
        EmployeeMapper em = session.getMapper(EmployeeMapper.class);
        HashMap<String, Object> params = new HashMap<String, Object>();
        // 设置state属性和id属性
        params.put("id", 1);
        params.put("loginname", "jack");
        params.put("password", "123456");
        List<Employee> list = em.selectEmployeeLike(params);
        list.forEach(employee -> System.out.println(employee));
    }
    
    public void testUpdateEmployeeIfNecessary(SqlSession session){
        EmployeeMapper em = session.getMapper(EmployeeMapper.class);
        Employee employee = em.selectEmployeeWithId(4);
        // 设置需要修改的属性
        employee.setLoginname("mary");
        employee.setPassword("123");
        employee.setName("玛丽");
        em.updateEmployeeIfNecessary(employee);
    }
    
    public void testSelectEmployeeIn(SqlSession session){
        EmployeeMapper em = session.getMapper(EmployeeMapper.class);
        // 创建List集合
        List<Integer> ids = new ArrayList<Integer>();
        // 往List集合中添加两个测试数据
        ids.add(1);
        ids.add(2);
        List<Employee> list = em.selectEmployeeIn(ids);
        list.forEach(employee -> System.out.println(employee));
    }
    
    public void testSelectEmployeeLikeName(SqlSession session){
        EmployeeMapper em = session.getMapper(EmployeeMapper.class);
        Employee employee = new Employee();
        // 设置模糊查询的参数
        employee.setName("o");
        List<Employee> list = em.selectEmployeeLikeName(employee);
        list.forEach(result -> System.out.println(result));
    }
}