【MyBatis】SQL动态操作

MybatisUtils.java工具类可以访问获取:【MyBatis】测试链接数据库增删改查 - 小鼻涕孩 - 博客园 (cnblogs.com)

CustomerMapper.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.xiaobiti.pojo.Customer">
    <!-- 查询 -->
    <select id="findCustomerByNameAndJobs" parameterType="com.xiaobiti.pojo.Customer" resultType="com.xiaobiti.pojo.Customer">
        <!-- 使用<trim>元素避免语句冲突 prefixOverrides语句前缀，以下的语句前缀是"and" -->
        SELECT * from customer
        <trim prefix="where" prefixOverrides="and">
            <choose>
                <when test="name != null and name != ''">
                    and name like concat('%',#{name},'%')
                </when>
                <when test="jobs != null and jobs != ''">
                    and jobs = #{jobs}
                </when>
                <otherwise>
                    and phone != ''
                </otherwise>
            </choose>
        </trim>

            <!-- 使用<where>元素避免查询语句冲突 -->
<!--        SELECT * from customer-->
<!--        <where>-->
<!--            <choose>-->
<!--                <when test="name != null and name != ''">-->
<!--                    and name like concat('%',#{name},'%')-->
<!--                </when>-->
<!--                <when test="jobs != null and jobs != ''">-->
<!--                    and jobs = #{jobs}-->
<!--                </when>-->
<!--                <otherwise>-->
<!--                    and phone != ''-->
<!--                </otherwise>-->
<!--            </choose>-->
<!--        </where>-->

<!--        SELECT * from customer where 1=1 -->
<!--        <if test="name != null and name != ''">-->
<!--            and name like concat('%',#{name},'%')-->
<!--        </if>-->
<!--        <if test="jobs != null and jobs != ''">-->
<!--            and jobs = #{jobs}-->
<!--        </if>-->
    </select>

    <!-- 更新 -->
    <update id="edit" parameterType="com.xiaobiti.pojo.Customer">
        <!-- 使用<trim>元素避免语句冲突 suffixOverrides语句后缀，以下的语句后缀是"," -->
        update customer
        <trim prefix="set" suffixOverrides=",">
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="jobs != null and jobs != ''">
                jobs = #{jobs},
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone},
            </if>
        </trim>
        where id = #{id}
    </update>

    <!-- 多条ID查询 -->
    <select id="findByArray" parameterType="java.util.ArrayList" resultType="com.xiaobiti.pojo.Customer">
        select * from customer where id in
            <!-- 使用List对象 -->
<!--        <foreach collection="list" item="id" index="index" open="(" separator="," close=")" >-->
<!--            #{id}-->
<!--        </foreach>-->

        <!-- 使用Map对象 -->
        <foreach collection="id" item="roleMap" index="index" open="(" separator="," close=")">
            #{roleMap}
        </foreach>
    </select>
</mapper>

 

Customer.java

package com.xiaobiti.test;

import com.xiaobiti.pojo.Customer;
import com.xiaobiti.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MybatisTest {
    @Test
    //查询
    public void findCustomerByNameAndJobs(){
        SqlSession session = MybatisUtils.getSession();
        Customer customer = new Customer();
        customer.setName("jo");
        customer.setJobs("海洋学家");
        List<Customer> list = session.selectList("findCustomerByNameAndJobs",customer);
        for(Customer c:list){
            System.out.println(c);
        }
    }

    @Test
    //更新
    public void edit(){
        SqlSession session = MybatisUtils.getSession();
        Customer customer = new Customer();
        customer.setId(4);
        customer.setName("jojo");
        customer.setJobs("海洋学家");
        customer.setPhone("123");
        session.update("edit",customer);
        session.commit();
    }

    @Test
    //多条ID查询
    public void findByArray(){
        SqlSession session = MybatisUtils.getSession();
        //Integer[] arr = {1,2,3};
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        Map<String,Object> map = new HashMap<String, Object>();
        map.put("id",ids);
        map.put("jobs","nurse");
        List<Customer> list = session.selectList("findByArray",map);
        for(Customer c:list){
            System.out.println(c);
        }
    }
}