首页 > 代码库 > Mybatis（三）

Mybatis（三）

2024-09-24 07:52:09   215人阅读

1 Mybaits--动态SQL

  • 动态SQL是Mybatis强大特性之一。极大的简化我们拼装SQL的操作。
  • 动态SQL元素和使用JSTL或其他类似基于XML的文本处理器相似。
  • Mybatis采用功能强大的基于OGNL的表达式来简化操作。
    • if
    • choose(when ,otherwise)　
    • trim(where,set)
    • foreach

 

1.1 动态SQL之if

package cn.demo2;



/**
 * 描述：POJO
 */
public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;
    private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;

    }

    @Override
    public String toString() {
        return "Employee{" +
                "email=‘" + email + ‘\‘‘ +
                ", gender=‘" + gender + ‘\‘‘ +
                ", lastName=‘" + lastName + ‘\‘‘ +
                ", id=" + id +
                ‘}‘;
    }
}
package cn.demo2;


import java.util.List;

public interface IEmployeeDAO {


    public List<Employee> getConditionIf(Employee employee);

}
<?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.demo2.IEmployeeDAO">




    <select id="getConditionIf" resultType="cn.demo2.Employee" >
      SELECT id,last_name lastName,email,gender from employee
      WHERE
      <if test="id != null">
        id = #{id}
      </if>
      <if test="lastName != null and lastName != ‘‘">
          and last_name like #{lastName}
      </if>
      <if test="email != null and email.trim() != ‘‘">
          and email = #{email}
      </if>
       <if test="gender == ‘男‘ or gender == ‘女‘">
          and gender = #{gender}
       </if>
    </select>


</mapper>
package cn.test;


import cn.demo2.Employee;
import cn.demo2.IEmployeeDAO;
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.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest2 {


    /**
     * 测试查询
     * @throws IOException
     */
    @Test
    public  void demo1() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
        Employee employee = new Employee();
        employee.setId(5);
        List<Employee> employees = dao.getConditionIf(employee);
        System.out.print(employees);

        session.close();
    }
}

 

1.2 动态SQL之where

  • 如果上面的程序中的id没有传入，那么拼接的SQL会多出and。
    • 解决方案一：在where之后加上1=1。
    • 解决方案二：Mybatis推荐使用where标签来将所有的查询条件包括在内，Mybatis就会将where标签中多出的and或or删除自动删除。并且where标签只会去掉第一个多出的and或or等。
package cn.demo2;



/**
 * 描述：POJO
 */
public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;
    private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;

    }

    @Override
    public String toString() {
        return "Employee{" +
                "email=‘" + email + ‘\‘‘ +
                ", gender=‘" + gender + ‘\‘‘ +
                ", lastName=‘" + lastName + ‘\‘‘ +
                ", id=" + id +
                ‘}‘;
    }
}
<?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.demo2.IEmployeeDAO">




    <select id="getConditionIf" resultType="cn.demo2.Employee" >
      SELECT id,last_name lastName,email,gender from employee
      <where>

          <if test="id != null">
              id = #{id}
          </if>
          <if test="lastName != null and lastName != ‘‘">
              and last_name like #{lastName}
          </if>
          <if test="email != null and email.trim() != ‘‘">
              and email = #{email}
          </if>
          <if test="gender == ‘男‘ or gender == ‘女‘">
              and gender = #{gender}
          </if>

      </where>

    </select>


</mapper>
package cn.test;


import cn.demo2.Employee;
import cn.demo2.IEmployeeDAO;
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.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest2 {


    /**
     * 测试查询
     * @throws IOException
     */
    @Test
    public  void demo1() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
        Employee employee = new Employee();
        employee.setLastName("%呵%");
        List<Employee> employees = dao.getConditionIf(employee);
        System.out.print(employees);

        session.close();
    }
}

 

1.2 动态SQL之trim

package cn.demo2;



/**
 * 描述：POJO
 */
public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;
    private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;

    }

    @Override
    public String toString() {
        return "Employee{" +
                "email=‘" + email + ‘\‘‘ +
                ", gender=‘" + gender + ‘\‘‘ +
                ", lastName=‘" + lastName + ‘\‘‘ +
                ", id=" + id +
                ‘}‘;
    }
}
package cn.demo2;


import java.util.List;

public interface IEmployeeDAO {


    public List<Employee> getConditionIf(Employee employee);

}
<?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.demo2.IEmployeeDAO">




    <select id="getConditionIf" resultType="cn.demo2.Employee" >
      SELECT id,last_name lastName,email,gender from employee
      <!--
        where标签职能消除前面多余的and或者or之类的语句，而如果and或or在之后，那么where就无能为力了
        trim可以来解决这样的问题
            prefix表示在拼接的SQL之前加前缀
            prefixOverrides是将前面多余的and或or等消除
            suffix和prefix相反
            prefixOverrides和prefixOverrides相反
      -->
      <trim prefix="where" suffixOverrides="and">

          <if test="id != null">
              id = #{id} and
          </if>
          <if test="lastName != null and lastName != ‘‘">
            last_name like #{lastName} and
          </if>
          <if test="email != null and email.trim() != ‘‘">
             email = #{email} and
          </if>
          <if test="gender == ‘男‘ or gender == ‘女‘">
              and gender = #{gender}
          </if>

      </trim>

    </select>


</mapper>
package cn.test;


import cn.demo2.Employee;
import cn.demo2.IEmployeeDAO;
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.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest2 {


    /**
     * 测试查询
     * @throws IOException
     */
    @Test
    public  void demo1() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
        Employee employee = new Employee();
        employee.setLastName("%呵%");
        List<Employee> employees = dao.getConditionIf(employee);
        System.out.print(employees);

        session.close();
    }
}

 

1.3 动态SQL之分支查询（和Java中的switch--case相似）

package cn.demo2;



/**
 * 描述：POJO
 */
public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;
    private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;

    }

    @Override
    public String toString() {
        return "Employee{" +
                "email=‘" + email + ‘\‘‘ +
                ", gender=‘" + gender + ‘\‘‘ +
                ", lastName=‘" + lastName + ‘\‘‘ +
                ", id=" + id +
                ‘}‘;
    }
}
package cn.demo2;


import java.util.List;

public interface IEmployeeDAO {


    public List<Employee> getConditionIf(Employee employee);

}
<?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.demo2.IEmployeeDAO">




    <select id="getConditionIf" resultType="cn.demo2.Employee" >
      SELECT id,last_name lastName,email,gender from employee
      <where>
          <choose>
              <when test="id != null">
                  id = #{id}
              </when>
              <when test="lastName != null">
                  last_name like  #{lastName}
              </when>
              <when test="email != null">
                  email = #{email}
              </when>
              <otherwise>
                  gender = #{gender}
              </otherwise>
          </choose>
      </where>
    </select>


</mapper>
package cn.test;


import cn.demo2.Employee;
import cn.demo2.IEmployeeDAO;
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.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTest2 {


    /**
     * 测试查询
     * @throws IOException
     */
    @Test
    public  void demo1() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
        Employee employee = new Employee();
        employee.setLastName("%呵%");
        List<Employee> employees = dao.getConditionIf(employee);
        System.out.print(employees);

        session.close();
    }
}

技术分享

 

1.4 动态SQL之更新

 public void updateEmployee(Employee employee);
    <update id="updateEmployee" >
        update employee
        <set>
            <if test="email != null">
                email = #{email},
            </if>
            <if test="lastName != null">
                last_name = #{lastName}
            </if>
            <if test="gender != null">
                gender = #{gender}
            </if>
        </set>
        where id = #{id}
    </update>
 @Test
    public  void demo1() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
        Employee employee = new Employee();
        employee.setId(5);
        employee.setLastName("哈哈");
        dao.updateEmployee(employee);
        session.commit();
        session.close();
    }

 

1.5 动态SQL--forEach

public List<Employee> getEmpsByConditionForEach(List<Integer> ids);
<select id="getEmpsByConditionForEach" resultType="cn.demo2.Employee" >
        select * from employee where id IN
        <foreach collection="list" item="item_id" separator="," open="(" close=")" >
            #{item_id}
        </foreach>
    </select>
 /**
     * 测试查询
     * @throws IOException
     */
    @Test
    public  void demo1() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
        dao.getEmpsByConditionForEach(Arrays.asList(5,6,7));
        session.close();
    }

 

 

 

 

 

 

 

　　

　　

 

Mybatis（三）


联系
我们