MyBatis的动态SQL实战

今天药忘吃喽~ 2021-07-24 15:06 519阅读 0赞

**一 设计数据表**

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','杰克','男',26,'13902019999',9800,'ACTIVE');
      INSERT INTO tb_employee(loginname,PASSWORD,NAME,sex,age,phone,sal,state)
    VALUES('rose','123456','露丝','女',21,'13902018888',6800,'ACTIVE');

**二 领域模型**

package org.fkit.domain;
    import java.io.Serializable;
    
    public class Employee implements Serializable {
         
         private static final long serialVersionUID = 1L;
         
         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();
               // TODO Auto-generated constructor stub
         }
         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 "Employee [id=" + id + ", loginname=" +  loginname
                         + ", password=" + password + ", name=" +  name + ", sex=" + sex
                         + ", age=" + age + ", phone=" + phone + ",  sal=" + sal
                         + ", state=" + state + "]";
         }
    }

**三 会话工厂**

package org.fkit.factory;
    
    import java.io.InputStream;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    public class FKSqlSessionFactory {
        
        private static SqlSessionFactory sqlSessionFactory = null;
        
        // 初始化创建SqlSessionFactory对象
        static{
            try (// 读取mybatis-config.xml文件
                    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
                ){
                sqlSessionFactory = new SqlSessionFactoryBuilder()
                        .build(is);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        // 获取SqlSession对象的静态方法
        public static SqlSession getSqlSession(){
            return sqlSessionFactory.openSession();
        }
    
        // 获取SqlSessionFactory的静态方法
        public static SqlSessionFactory getSqlSessionFactory() {
            return sqlSessionFactory;
        }
    
    }

**四 配置文件**

1 db.properties

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://127.0.0.1:3306/mybatis
    username=root
    password=123456

2 log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">  
    <log4j:configuration  xmlns:log4j="http://jakarta.apache.org/log4j/">  
        <appender name="STDOUT"  class="org.apache.log4j.ConsoleAppender">  
            <layout class="org.apache.log4j.PatternLayout">  
                <param name="ConversionPattern" value="%5p [%t] %m%n"  />  
            </layout>  
        </appender>   
         <logger name="org.fkit.mapper">  
            <level value="DEBUG" />  
        </logger>
        <root>  
            <level value="ERROR" />  
            <appender-ref ref="STDOUT" />  
        </root>  
    </log4j:configuration>

3 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 resource="db.properties"/>
         
         <settings>
               <setting name="logImpl" value="LOG4J"/>
               
               <setting name="lazyLoadingEnabled" value="true"/>
               <setting name="aggressiveLazyLoading" value="false"/>
         </settings>
         <environments default="mysql">
         
        <environment id="mysql">
        
          <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="org/fkit/mapper/EmployeeMapper.xml"/>
      </mappers>
    </configuration>

**五 mapper**

1 EmployeeMapper

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

2 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">
    
    <mapper namespace="org.fkit.mapper.EmployeeMapper">
      <select id="selectEmployeeWithId" parameterType="int"  resultType="org.fkit.domain.Employee">
          SELECT * FROM tb_employee where id = #{id}
      </select>
         
      
      <select id="selectEmployeeByIdLike"
          resultType="org.fkit.domain.Employee">
          SELECT * FROM tb_employee WHERE state = 'ACTIVE'
          
          <if test="id != null ">
               and id = #{id}
          </if>
      </select>
      
      
      <select id="selectEmployeeByLoginLike"
          resultType="org.fkit.domain.Employee">
          SELECT * FROM tb_employee WHERE state = 'ACTIVE'
          
          <if test="loginname != null and password != null">
               and loginname = #{loginname} and password =  #{password}
          </if>
      </select>
      
      
      <select id="selectEmployeeChoose"
          parameterType="hashmap"
          resultType="org.fkit.domain.Employee">
          SELECT * FROM tb_employee WHERE state = 'ACTIVE'
          
          <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>
      
      
      <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>
      
      
      <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>
      
      
      <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>
      
      
         <select id="selectEmployeeLikeName"   resultType="org.fkit.domain.Employee">
           <bind name="pattern" value="'%' + _parameter.getName() +  '%'" />
              SELECT * FROM tb_employee
              WHERE loginname LIKE #{pattern}
         </select>
    </mapper>

**六 测试代码**

package org.fkit.test;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    
    import org.apache.ibatis.session.SqlSession;
    import org.fkit.domain.Employee;
    import org.fkit.factory.FKSqlSessionFactory;
    import org.fkit.mapper.EmployeeMapper;
    
    
    public class DynamicSQLTest {
    
        public static void main(String[] args)  {
            
            // 定义SqlSession变量
            SqlSession sqlSession = null;
            try {
                // 创建SqlSession实例
                sqlSession = FKSqlSessionFactory.getSqlSession();
                
                DynamicSQLTest t = new DynamicSQLTest();
                
                t.testSelectEmployeeByIdLike(sqlSession);
                
    //            t.testSelectEmployeeByLoginLike(sqlSession);
                
    //            t.testSelectEmployeeChoose(sqlSession);
                
                // 没有使用where元素，如果没有传入state参数，会导致执行失败 的例子
    //            t.testFindEmployeeLike(sqlSession);
                
    //            t.testSelectEmployeeLike(sqlSession);
                
    //            t.testUpdateEmployeeIfNecessary(sqlSession);
                
    //            t.testSelectEmployeeIn(sqlSession);
                
    //            t.testSelectEmployeeLikeName(sqlSession);
                
                // 提交事务
                sqlSession.commit();
            } catch (Exception e) {
                // 回滚事务
                sqlSession.rollback();
                e.printStackTrace();
            }finally {
                // 关闭SqlSession
                if(sqlSession != null)
                    sqlSession.close();
            }
                    
        }
        
        // 测试<select id="selectEmployeeByIdLike" ...>
        public void testSelectEmployeeByIdLike(SqlSession sqlSession){
            // 获得EmployeeMapper接口的代理对象
            EmployeeMapper em = sqlSession.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 sqlSession){
            EmployeeMapper em = sqlSession.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 sqlSession){
            EmployeeMapper em = sqlSession.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));
        }
        
        // 没有使用where元素，如果没有传入state参数，会导致执行失败 的例子
        public void testFindEmployeeLike(SqlSession sqlSession){
            EmployeeMapper em = sqlSession.getMapper(EmployeeMapper.class);
            HashMap<String, Object> params = new HashMap<String, Object>();
            params.put("id", 1);
            params.put("loginname", "jack");
            params.put("password", "123456");
            List<Employee> list = em.findEmployeeLike(params);
            list.forEach(employee -> System.out.println(employee));
        }
        
        public void testSelectEmployeeLike(SqlSession sqlSession){
            EmployeeMapper em = sqlSession.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 sqlSession){
            EmployeeMapper em = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = em.selectEmployeeWithId(4);
            // 设置需要修改的属性
            employee.setLoginname("mary");
            employee.setPassword("123");
            employee.setName("玛丽");
            em.updateEmployeeIfNecessary(employee);
        }
        
        public void testSelectEmployeeIn(SqlSession sqlSession){
            EmployeeMapper em = sqlSession.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 sqlSession){
            EmployeeMapper em = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = new Employee();
            // 设置模糊查询的参数
            employee.setName("o");
            List<Employee> list = em.selectEmployeeLikeName(employee);
            list.forEach(result -> System.out.println(result));
        }
    }

**七 测试效果**

1 测试testSelectEmployeeByIdLike

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE  state = 'ACTIVE' and id = ?
    DEBUG [main] ==> Parameters: 1(Integer)
    DEBUG [main] <==      Total: 1
    Employee [id=1, loginname=jack, password=123456, name=杰克,  sex=男, age=26, phone=13902019999, sal=9800.0, state=ACTIVE]

2 测试testSelectEmployeeByLoginLike

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE  state = 'ACTIVE' and loginname = ? and password = ?
    DEBUG [main] ==> Parameters: jack(String), 123456(String)
    DEBUG [main] <==      Total: 1
    Employee [id=1, loginname=jack, password=123456, name=杰克,  sex=男, age=26, phone=13902019999, sal=9800.0, state=ACTIVE]

3 测试testSelectEmployeeChoose

4 测试testFindEmployeeLike

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE and  id = ? and loginname = ? and password = ?
    DEBUG [main] ==> Parameters: 1(Integer), jack(String),  123456(String)
    org.apache.ibatis.exceptions.PersistenceException:
    ### Error querying database.  Cause:  com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You  have an error in your SQL syntax; check the manual that  corresponds to your MySQL server version for the right syntax to  use near 'and id = 1

5 测试testSelectEmployeeLike

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE id  = ? and loginname = ? and password = ?
    DEBUG [main] ==> Parameters: 1(Integer), jack(String),  123456(String)
    DEBUG [main] <==      Total: 1
    Employee [id=1, loginname=jack, password=123456, name=杰克,  sex=男, age=26, phone=13902019999, sal=9800.0, state=ACTIVE]

6 测试testUpdateEmployeeIfNecessary

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee where id  = ?
    DEBUG [main] ==> Parameters: 1(Integer)
    DEBUG [main] <==      Total: 1
    DEBUG [main] ==>  Preparing: update tb_employee SET loginname=?,  password=?, name=?, sex=?, age=?, phone=?, sal=?, state=? where  id=?
    DEBUG [main] ==> Parameters: mary(String), 123(String), 玛丽(String), 男(String), 26(Integer), 13902019999(String),  9800.0(Double), ACTIVE(String), 1(Integer)
    DEBUG [main] <==    Updates: 1

7 测试testSelectEmployeeIn

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE ID  in ( ? , ? )
    DEBUG [main] ==> Parameters: 1(Integer), 2(Integer)
    DEBUG [main] <==      Total: 2
    Employee [id=1, loginname=mary, password=123, name=玛丽, sex=男,  age=26, phone=13902019999, sal=9800.0, state=ACTIVE]
    Employee [id=2, loginname=rose, password=123456, name=露丝,  sex=女, age=21, phone=13902018888, sal=6800.0, state=ACTIVE]

8 测试testSelectEmployeeLikeName

DEBUG [main] ==>  Preparing: SELECT * FROM tb_employee WHERE  loginname LIKE ?
    DEBUG [main] ==> Parameters: %o%(String)
    DEBUG [main] <==      Total: 1
    Employee [id=2, loginname=rose, password=123456, name=露丝,  sex=女, age=21, phone=13902018888, sal=6800.0, state=ACTIVE]