Mybatis 动态SQL

if

新建表employee

Employee

package com.example.demo.domain;

import java.io.Serializable;

public class Employee implements Serializable {
    private Integer 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 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;
    }
}

View Code

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="com.example.demo.mapper.EmployeeMapper">

    <select id="selectEmployeeByIdLike" parameterType="int" resultType="com.example.demo.domain.Employee">
        SELECT  *  FROM tb_employee WHERE state="ACTIVE"
        <if test="id!=null">
            and id=#{id}
        </if>
    </select>
</mapper>

View Code

EmployeeMapper

package com.example.demo.mapper;

import com.example.demo.domain.Employee;

import java.util.HashMap;
import java.util.List;

public interface EmployeeMapper {
    List<Employee> selectEmployeeByIdLike(HashMap<String,Object> params);
}

View Code

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>
    <settings>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.31.146:3306/mydb"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="CardMapper.xml"></mapper>
        <mapper resource="PersonMapper.xml"></mapper>
        <mapper resource="ClazzMapper.xml"></mapper>
        <mapper resource="StudentMapper.xml"></mapper>
        <mapper resource="EmployeeMapper.xml"></mapper>
    </mappers>

</configuration>

View Code

DynamicSQLTest

package com.example.demo.test;

import com.example.demo.domain.Employee;
import com.example.demo.mapper.EmployeeMapper;
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 java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;

public class DynamicSQLTest {
    public static void main(String[] args) throws IOException {
        InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session=factory.openSession();
        EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
        HashMap<String,Object> map=new HashMap<String,Object>();
//        map.put("id",1);
        List<Employee> list=mapper.selectEmployeeByIdLike(map);
        list.forEach(employee -> System.out.println(employee.getName()+" "+employee.getPhone()));
    }
}

View Code

运行结果

把注释部分取消重新运行

多条件查询

<select id="selectEmployeeByLoginLike" resultType="com.example.demo.domain.Employee">
        SELECT  * FROM tb_employee WHERE state="ACTIVE"
        <if test="loginname!=null and password!=null">
            and loginname=#{loginname} and password=#{password}
        </if>
    </select>

View Code

EmployeeMapper

public interface EmployeeMapper {
    List<Employee> selectEmployeeByIdLike(HashMap<String,Object> params);
    List<Employee> selectEmployeeByLoginLike(HashMap<String,Object> params);
}

DynamicSQLTest

public static void testSelectEmployeeByLoginLike(SqlSession session){
        EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
        HashMap<String,Object> params=new HashMap<String,Object>();
        params.put("loginname","jack");
        params.put("password","123456");
        List<Employee> list=mapper.selectEmployeeByLoginLike(params);
        list.forEach(employee -> System.out.println(employee.getName()+" "+employee.getPhone()));
    }

View Code

Choose

<select id="selectEmployeeChoose" resultType="com.example.demo.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>

View Code

where

<select id="selectEmployeeLike" resultType="com.example.demo.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>

View Code

set

<update id="updateEmployeeIfNecessary" parameterType="com.example.demo.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>

View Code

运行时会出现中文插入乱码问题，把连接Url修改如下即可

<property name="url" value="jdbc:mysql://192.168.31.146:3306/mydb?useUnicode=true&amp;characterEncoding=UTF-8"/>

foreach

<select id="selectEmployeeIn" resultType="com.example.demo.domain.Employee">
        SELECT * FROM tb_employee WHERE id IN
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

View Code

bind

<select id="selectEmployeeLikeNmae" resultType="com.example.demo.domain.Employee">
        <bind name="pattern" value="'%'+_parameter.getName()+'%'"></bind>
        select * from tb_employee WHERE loginname LIKE #{pattern}
    </select>

View Code

完整代码

DynamicSQLTest

package com.example.demo.test;

import com.example.demo.domain.Employee;
import com.example.demo.mapper.EmployeeMapper;
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 java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class DynamicSQLTest {
    public static void main(String[] args) throws IOException {
        InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session=factory.openSession();
        testSelectEmployeeLikeNmae(session);
        session.commit();
        session.close();
    }
    public static void testSelectEmployeeLikeNmae(SqlSession session){
        EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
        Employee employee=new Employee();
        employee.setName("o");
        List<Employee> list=mapper.selectEmployeeLikeNmae(employee);
        list.forEach(e -> System.out.println(e.getName()+" "+e.getPhone()));
    }
    public static void testSelectEmployeeIn(SqlSession session){
        EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
        List<Integer> ids=new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        List<Employee> list=mapper.selectEmployeeIn(ids);
        list.forEach(employee -> System.out.println(employee.getName()+" "+employee.getPhone()));
    }
    public static void testSelectEmployeeByIdLike(SqlSession session){
        EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
        HashMap<String,Object> map=new HashMap<String,Object>();
        map.put("id",1);
        List<Employee> list=mapper.selectEmployeeByIdLike(map);
        list.forEach(employee -> System.out.println(employee.getName()+" "+employee.getPhone()));
    }

    public static void testSelectEmployeeByLoginLike(SqlSession session){
        EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
        HashMap<String,Object> params=new HashMap<String,Object>();
        params.put("loginname","jack");
        params.put("password","123456");
        List<Employee> list=mapper.selectEmployeeByLoginLike(params);
        list.forEach(employee -> System.out.println(employee.getName()+" "+employee.getPhone()));
    }

    public static void testSelectEmployeeChoose(SqlSession session){
        EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
        HashMap<String,Object> params=new HashMap<String,Object>();
        List<Employee> list=mapper.selectEmployeeChoose(params);
        list.forEach(employee -> System.out.println(employee.getName()+" "+employee.getPhone()));
    }
    public static void testSelectEmployeeLike(SqlSession session){
        EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
        HashMap<String,Object> params=new HashMap<String,Object>();
        List<Employee> list=mapper.selectEmployeeLike(params);
        list.forEach(employee -> System.out.println(employee.getName()+" "+employee.getPhone()));
    }
    public static void testUpdateEmployeeIfNecessary(SqlSession session){
        EmployeeMapper mapper=session.getMapper(EmployeeMapper.class);
        Employee employee=mapper.selectEmployeeById(1);
        employee.setAge(18);
        mapper.updateEmployeeIfNecessary(employee);
    }
}

View Code

EmployeeMapper

package com.example.demo.mapper;

import com.example.demo.domain.Employee;

import java.util.HashMap;
import java.util.List;

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> selectEmployeeLike(HashMap<String,Object> params);
    Employee selectEmployeeById(Integer id);
    void updateEmployeeIfNecessary(Employee employee);
    List<Employee> selectEmployeeIn(List<Integer> ids);
    List<Employee> selectEmployeeLikeNmae(Employee employee);
}

View Code

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="com.example.demo.mapper.EmployeeMapper">
    <select id="selectEmployeeById" parameterType="int" resultType="com.example.demo.domain.Employee">
        SELECT  *  FROM tb_employee WHERE  id=#{id}
    </select>
    <select id="selectEmployeeByIdLike" parameterType="int" resultType="com.example.demo.domain.Employee">
        SELECT  *  FROM tb_employee WHERE state="ACTIVE"
        <if test="id!=null">
            and id=#{id}
        </if>
    </select>
    <select id="selectEmployeeByLoginLike" resultType="com.example.demo.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" resultType="com.example.demo.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="selectEmployeeLike" resultType="com.example.demo.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="com.example.demo.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="com.example.demo.domain.Employee">
        SELECT * FROM tb_employee WHERE id IN
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
    <select id="selectEmployeeLikeNmae" resultType="com.example.demo.domain.Employee">
        <bind name="pattern" value="'%'+_parameter.getName()+'%'"></bind>
        select * from tb_employee WHERE loginname LIKE #{pattern}
    </select>
</mapper>

View Code