MyBatis框架之动态sql

动态条件组合查询:

封装查询对象:

package com.rl.model1;

import java.util.Date;

public class QueryCondition {

    private Integer gender;
    
    private Date birthday;
    
    private String name;
    
    private String address;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
}

组装PersonMapper.xml配置文件中的查询语句: 需要先在sqlMapConfig.xml中配置自定义别名qc

<select id="selectPersonByCondition" parameterType="qc" resultMap="BaseResultMap">
        select * from person t 
	        <where>
	            <if test="name != null">
	                t.name like '%${name}%'
	            </if>
	            <if test="gender != null">
	                and t.gender = #{gender}
	            </if>
	            <if test="address != null">
	                and t.person_addr like '%${address}%'
	            </if>
	            <if test="birthday != null">
	            <![CDATA[
	                and t.birthday < #{birthday}
	                ]]>
	            </if>
	        </where>
    </select>

测试类:

package com.rl.test;

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

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.Before;
import org.junit.Test;

import com.rl.model1.Person;
import com.rl.model1.QueryCondition;

public class MyBatisTest4 {

    SqlSessionFactory sqlSessionFactory;
    
    @Before
    public void setUp() throws Exception {
        //加载配置文件
        InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
        //初始化
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    }

    @Test
    public void test() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        QueryCondition qc = new QueryCondition();
        qc.setAddress("上");
        qc.setName("忠");
        qc.setGender(1);
        qc.setBirthday(new Date());
        try {
            List<Person> pList = sqlSession.selectList("com.rl.mapper.PersonMapper.selectPersonByCondition", qc);
            System.out.println(pList);
        } finally{
            sqlSession.close();
        }
    }
}

输出结果:

[Person [personId=4, name=黄忠, gender=1, personAddr=上海, birthday=Sun Sep 09 00:00:00 CST 2018]]

提醒: <where>标签是可以智能去除后面多余的"and"

如果name如果为空, 则造成下面的gender判断语句前面多了一个"and", 此时会自动滤除掉"and"

此时是采用预编译的形式生成sql语句, 不会出现sql注入的问题

动态条件组合修改: 解决了当其中某个字段为空会覆盖掉之前的值的情况

配置文件:

<update id="dynamicUpdate" parameterType="person">
        update person t
        <set>
            t.person_id = #{personId},
            <if test="name != null">
                t.name = #{name},
            </if>
            <if test="gender != null">
                t.gender = #{gender},
            </if>
            <if test="personAddr != null">
                t.person_addr = #{personAddr},
            </if>
            <if test="birthday != null">
                t.birthday = #{birthday}
            </if>
        </set>
        where t.person_id = #{personId}
    </update>

测试类(部分代码):

@Test
    public void test2() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Person p = new Person();
        p.setPersonId(2);
        p.setGender(2);
        p.setBirthday(new Date());
        p.setPersonAddr("上海");
        p.setName("晁盖");
        try {
            sqlSession.update("com.rl.mapper.PersonMapper.dynamicUpdate", p);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            sqlSession.close();
        }
    }

提醒: <set>标签能去掉多余的","

如果birthday为空的话, 此时将多出一个",", 框架会自动滤除该",", 另外, 加上"t.person_id = #{personId},"的目的在于防止除了id其他字段都不传导致报错的情况.

集合查询:

配置文件:

<!-- 
        open: 左括号
        close: 右括号
        item: 自定义变量名为personId
        separator: 以","为分隔
        index: 自定义索引号变量名(暂时用不到)
     -->
    <select id="selectPersonByIn" parameterType="map" resultMap="BaseResultMap">
        select * from person t where t.person_id in
        <foreach collection="ids" open="(" close=")" item="personId" separator="," index="index">
            #{personId}
        </foreach>
    </select>

测试代码(部分):

@Test
    public void test3() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        //定义一个Integer数组用于存储personId
        Integer[] ids = {1, 2, 4};
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("ids", ids);
        try {
            List<Person> pList = sqlSession.selectList("com.rl.mapper.PersonMapper.selectPersonByIn", map);
            for(Person p: pList){
                System.out.println(p);
            }
        } finally{
            sqlSession.close();
        }
    }

批量添加:

配置文件:

<insert id="insertBatch" parameterType="map">
        insert into person (person_id, name, gender, person_addr, birthday)
        values
        <foreach collection="pList" item="person" separator=",">
            (#{person.personId},
            #{person.name},
            #{person.gender},
            #{person.personAddr},
            #{person.birthday})
        </foreach>
    </insert>

测试代码(部分):

@Test
    public void test5() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<Person> pList = new ArrayList<Person>();
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            for(int i = 0; i < 1000008; i++){
                Person p = new Person();
                p.setBirthday(new Date());
                p.setGender(1);
                p.setName("宋江"+i);
                p.setPersonAddr("梁山"+i);
                pList.add(p);
                if(i%100 == 0){
                    map.put("pList", pList);
                    sqlSession.insert("com.rl.mapper.PersonMapper.insertBatch", map);
                    pList.clear();
                }
            }
            map.put("pList", pList);
            sqlSession.insert("com.rl.mapper.PersonMapper.insertBatch", map);
            sqlSession.commit();//一定要做最后一步提交操作
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            sqlSession.close();
        }
    }

批量删除:

配置文件:

<delete id="deleteBatch" parameterType="map">
        delete from person where person_id in 
        <foreach collection="ids" open="(" close=")" index="index" item="personId" separator=",">
            #{personId}
        </foreach>
    </delete>

测试代码(部分):

@Test
    public void test6() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<Integer> pList = new ArrayList<Integer>();
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            for(int i = 2000008; i <= 3000015; i++){
                pList.add(i);
                if(i%100 == 0){
                    map.put("ids", pList);
                    sqlSession.delete("com.rl.mapper.PersonMapper.deleteBatch", map);
                    pList.clear();
                }
            }
            map.put("ids", pList);
            sqlSession.delete("com.rl.mapper.PersonMapper.deleteBatch", map);
            sqlSession.commit();//最后一步提交操作
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            sqlSession.close();
        }
    }