MyBatis框架的 动态SQL

蔚落 2022-06-18 04:14 313阅读 0赞

代码：

Employee.java 

  1. package com.bean;
  3. import java.io.Serializable;
  5. public class Employee implements Serializable {
  6.     private Integer id;             // 主键id
  7.     private String loginname;     // 登录名
  8.     private String password;     // 密码
  9.     private String name;         // 真实姓名
  10.     private String sex;             // 性别
  11.     private Integer age;         // 年龄
  12.     private String phone;         // 电话
  13.     private Double sal;             // 薪水
  14.     private String state;          // 状态
  15.     public Employee() {
  16.         super();
  17.         // TODO Auto-generated constructor stub
  18.     }
  19.     public Integer getId() {
  20.         return id;
  21.     }
  22.     public void setId(Integer id) {
  23.         this.id = id;
  24.     }
  25.     public String getLoginname() {
  26.         return loginname;
  27.     }
  28.     public void setLoginname(String loginname) {
  29.         this.loginname = loginname;
  30.     }
  31.     public String getPassword() {
  32.         return password;
  33.     }
  34.     public void setPassword(String password) {
  35.         this.password = password;
  36.     }
  37.     public String getName() {
  38.         return name;
  39.     }
  40.     public void setName(String name) {
  41.         this.name = name;
  42.     }
  43.     public String getSex() {
  44.         return sex;
  45.     }
  46.     public void setSex(String sex) {
  47.         this.sex = sex;
  48.     }
  49.     public Integer getAge() {
  50.         return age;
  51.     }
  52.     public void setAge(Integer age) {
  53.         this.age = age;
  54.     }
  55.     public String getPhone() {
  56.         return phone;
  57.     }
  58.     public void setPhone(String phone) {
  59.         this.phone = phone;
  60.     }
  61.     public Double getSal() {
  62.         return sal;
  63.     }
  64.     public void setSal(Double sal) {
  65.         this.sal = sal;
  66.     }
  67.     public String getState() {
  68.         return state;
  69.     }
  70.     public void setState(String state) {
  71.         this.state = state;
  72.     }
  73.     @Override
  74.     public String toString() {
  75.         // TODO Auto-generated method stub
  76.         return "Employee [id=" + id + ", loginname=" + loginname + ", password=" + password + ", name=" + name + ", sex=" + sex
  77.                 + ", age=" + age + ", phone=" + phone + ", sal=" + sal + ", state=" + state + "]";
  78.     }
  80. }

EmployeeMapper.java 

  1. package com.mapper;
  3. import java.util.HashMap;
  4. import java.util.List;
  6. import com.bean.Employee;
  8. public interface EmployeeMapper {
  9.     List<Employee> selectEmployeeByIdLike(HashMap<String, Object> params);
  11.     List<Employee> selectEmployeeByLoginLike(HashMap<String, Object> params);
  13.     List<Employee> selectEmployeeChoose(HashMap<String, Object> params);
  15.     List<Employee> findEmployeeLike(HashMap<String, Object> params);
  17.     List<Employee> selectEmployeeLike(HashMap<String, Object> params);
  19.     Employee selectEmployeeWithId(Integer id);//根据id查询员工
  21.     void updateEmployeeIfNecessary(Employee employee);//动态更新员工
  23.     List<Employee> selectEmployeeIn(List<Integer> ids);//根据传入的id查询员工
  25.     List<Employee> selectEmployeeLikeName(Employee employee);//根据传入的参数进行模糊查询
  27. }

EmployeeMapper.xml 

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  3. <!-- namespace指用户自定义的命名空间。 -->
  4. <mapper namespace="com.mapper.EmployeeMapper">
  5.    <select id="selectEmployeeWithId" parameterType="int" resultType="com.bean.Employee">
  6.            SELECT * FROM tb_employee where id = #{id}
  7.    </select>
  9.     <!-- if -->
  10.     <select id="selectEmployeeByIdLike" resultType="com.bean.Employee">
  11.          SELECT * FROM tb_employee WHERE state = 'ACTIVE'
  12.       <!-- 可选条件，如果传进来的参数有id属性，则加上id查询条件 -->
  13.       <if test="id != null ">
  14.            and id = #{id}
  15.       </if>
  16.     </select>
  18.     <!-- if -->
  19.    <select id="selectEmployeeByLoginLike" resultType="com.bean.Employee">
  20.             SELECT * FROM tb_employee WHERE state = 'ACTIVE'
  21.       <!-- 两个可选条件，例如登录功能的登录名和密码查询 -->
  22.       <if test="loginname != null and password != null">
  23.            and loginname = #{loginname} and password = #{password}
  24.       </if>
  25.   </select>
  27.   <!-- choose（when、otherwise） -->
  28.   <select id="selectEmployeeChoose" parameterType="hashmap" resultType="com.bean.Employee">
  29.       SELECT * FROM tb_employee WHERE state = 'ACTIVE'
  30.   <!-- 如果传入了id，就根据id查询，没有传入id就根据loginname和password查询，否则查询sex等于男的数据 -->
  31.   <choose>
  32.           <when test="id != null">
  33.               and id = #{id}
  34.           </when>
  35.           <when test="loginname != null and password != null">
  36.               and loginname = #{loginname} and password = #{password}
  37.           </when>
  38.           <otherwise>
  39.               and sex = '男'
  40.           </otherwise>
  41.       </choose>
  42.   </select>
  44.   <select id="findEmployeeLike" resultType="com.bean.Employee">
  45.            SELECT * FROM tb_employee WHERE 
  46.       <if test="state != null ">
  47.           state = #{state}
  48.       </if>
  49.       <if test="id != null ">
  50.           and id = #{id}
  51.       </if>
  52.       <if test="loginname != null and password != null">
  53.           and loginname = #{loginname} and password = #{password}
  54.       </if>
  55.   </select>
  57.   <!-- where -->
  58.   <select id="selectEmployeeLike" resultType="com.bean.Employee">
  59.      SELECT * FROM tb_employee  
  60.      <where>
  61.           <if test="state != null ">
  62.               state = #{state}
  63.           </if>
  64.           <if test="id != null ">
  65.               and id = #{id}
  66.           </if>
  67.           <if test="loginname != null and password != null">
  68.               and loginname = #{loginname} and password = #{password}
  69.           </if>
  70.       </where>
  71.   </select>
  73.   <!-- set -->
  74.   <update id="updateEmployeeIfNecessary" parameterType="com.bean.Employee">
  75.      update tb_employee
  76.       <set>
  77.           <if test="loginname != null">loginname=#{loginname},</if>
  78.           <if test="password != null">password=#{password},</if>
  79.           <if test="name != null">name=#{name},</if>
  80.           <if test="sex != null">sex=#{sex},</if>
  81.           <if test="age != null">age=#{age},</if>
  82.           <if test="phone != null">phone=#{phone},</if>
  83.           <if test="sal != null">sal=#{sal},</if>
  84.           <if test="state != null">state=#{state}</if>
  85.       </set>
  86.   where id=#{id}
  87.   </update>
  89.   <!-- foreach -->
  90.   <select id="selectEmployeeIn" resultType="com.bean.Employee">
  91.       SELECT * FROM tb_employee WHERE ID in
  92.       <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
  93.             #{item}
  94.       </foreach>
  95.   </select>
  97.   <!-- bind -->
  98.   <select id="selectEmployeeLikeName"  resultType="com.bean.Employee">
  99.   <bind name="pattern" value="'%' + _parameter.getName() + '%'" />
  100.           SELECT * FROM tb_employee WHERE loginname LIKE #{pattern}
  101.   </select>
  102. </mapper>

DynamicSQLTest.java 

  1. package com.test;
  3. import java.io.IOException;
  4. import java.io.InputStream;
  5. import java.util.ArrayList;
  6. import java.util.HashMap;
  7. import java.util.List;
  9. import org.apache.ibatis.io.Resources;
  10. import org.apache.ibatis.session.SqlSession;
  11. import org.apache.ibatis.session.SqlSessionFactory;
  12. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  14. import com.bean.Employee;
  15. import com.mapper.EmployeeMapper;
  17. public class DynamicSQLTest {
  19.     public static void main(String[] args) throws Exception {
  20.         // TODO Auto-generated method stub
  21.         // 读取mybatis-config.xml文件
  22.         InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  23.         // 初始化mybatis，创建SqlSessionFactory类的实例
  24.         SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
  25.         // 创建Session实例
  26.         SqlSession session=sqlSessionFactory.openSession();
  27.         DynamicSQLTest t = new DynamicSQLTest();
  28.       //t.testSelectEmployeeByIdLike(session);
  29.       //t.testSelectEmployeeByLoginLike(session);
  30.       //t.testSelectEmployeeChoose(session);
  31.       //t.testFindEmployeeLike(session);
  32.       //t.testSelectEmployeeLike(session);
  33.       //t.testUpdateEmployeeIfNecessary(session);
  34.       //t.testSelectEmployeeIn(session);
  35.         t.testSelectEmployeeLikeName(session);
  36.         session.commit();
  37.         session.close();
  38.     }
  40.     // 测试<select id="selectEmployeeByIdLike" >
  41.     public void testSelectEmployeeByIdLike(SqlSession session){
  42.         // 获得EmployeeMapper接口的代理对象
  43.         EmployeeMapper em=session.getMapper(EmployeeMapper.class);
  44.         // 创建一个HashMap存储参数
  45.         HashMap<String, Object> params=new HashMap<String,Object>();
  46.         // 设置id属性
  47.         params.put("id", 1);
  48.         // 调用EmployeeMapper接口的selectEmployeeByIdLike方法
  49.         List<Employee> list=em.selectEmployeeByIdLike(params);
  50.         // 查看返回结果
  51.         list.forEach(employee -> System.out.println(employee));
  52.     }
  54.     public void testSelectEmployeeByLoginLike(SqlSession session){
  55.         EmployeeMapper em=session.getMapper(EmployeeMapper.class);
  56.         HashMap<String, Object> params = new HashMap<String, Object>();
  57.         // 设置loginname和password属性
  58.         params.put("loginname", "jack");
  59.         params.put("password", "123456");
  60.         List<Employee> list = em.selectEmployeeByLoginLike(params);
  61.         list.forEach(employee -> System.out.println(employee));
  62.     }
  64.     public void testSelectEmployeeChoose(SqlSession session){
  65.         EmployeeMapper em = session.getMapper(EmployeeMapper.class);
  66.         HashMap<String, Object> params = new HashMap<String, Object>();
  67.         // 设置id属性
  68. //        params.put("id", 1);
  69. //        params.put("loginname", "jack");
  70. //        params.put("password", "123456");
  71.         List<Employee> list = em.selectEmployeeChoose(params);
  72.         list.forEach(employee -> System.out.println(employee));
  73.     }
  75.     public void testFindEmployeeLike(SqlSession session){
  76.         EmployeeMapper em = session.getMapper(EmployeeMapper.class);
  77.         HashMap<String, Object> params = new HashMap<String, Object>();
  78.         params.put("id", 1);
  79.         List<Employee> list = em.findEmployeeLike(params);
  80.         list.forEach(employee -> System.out.println(employee));
  81.     }
  83.     public void testSelectEmployeeLike(SqlSession session){
  84.         EmployeeMapper em = session.getMapper(EmployeeMapper.class);
  85.         HashMap<String, Object> params = new HashMap<String, Object>();
  86.         // 设置state属性和id属性
  87.         params.put("id", 3);
  88.         params.put("loginname", "jack");
  89.         params.put("password", "123456");
  90.         List<Employee> list = em.selectEmployeeLike(params);
  91.         list.forEach(employee -> System.out.println(employee));
  92.     }
  94.     public void testUpdateEmployeeIfNecessary(SqlSession session){
  95.         EmployeeMapper em=session.getMapper(EmployeeMapper.class);
  96.         //查询id为3的员工
  97.         Employee employee=em.selectEmployeeWithId(3);
  98.         //设置需要修改的属性
  99.         employee.setLoginname("mary");
  100.         employee.setPassword("1234");
  101.         employee.setName("玛丽");
  102.         em.updateEmployeeIfNecessary(employee);
  103.     }
  105.     public void testSelectEmployeeIn(SqlSession session){
  106.         EmployeeMapper em = session.getMapper(EmployeeMapper.class);
  107.         // 创建List集合
  108.         List<Integer> ids = new ArrayList<Integer>();
  109.         // 往List集合中添加两个测试数据
  110.         ids.add(1);
  111.         ids.add(2);
  112.         List<Employee> list = em.selectEmployeeIn(ids);
  113.         list.forEach(employee -> System.out.println(employee));
  114.     }
  116.     public void testSelectEmployeeLikeName(SqlSession session){
  117.         EmployeeMapper em = session.getMapper(EmployeeMapper.class);
  118.         Employee employee = new Employee();
  119.         // 设置模糊查询的参数
  120.         employee.setName("o");
  121.         List<Employee> list = em.selectEmployeeLikeName(employee);
  122.         list.forEach(result -> System.out.println(result));
  123.     }
  124. }

截图（发最后一个：模糊查询）

Center

发表评论

表情：
评论列表 （有 0 条评论，313人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Mybatis的动态sql

    前言：本项目是使用idea进行开发，数据库使用的是Mysql。 本案例是基于对users表进行动态的增删改查的案例。 项目模块图 ![在这里插入图片描述][wate

    傷城~傷城~/ 0 赞/ 547 阅读