14 动态SQL

14 动态SQL

张创琦 2022.03.13

减少拼接SQL语句的麻烦之处。

1 where 标签在 select 中的作用

原来的动态SQL写法，十分麻烦，容易出错。

    /*原有的多条件分析：都是通过java中的字符串拼接实现String sql="select * from team    where 1 = 1 ";
    // 如果用户输入了名称，就模糊查询
    and teamName like '%?%'
    // 如果用户输入了日期，按照日期区间查询
    and createTime> ? and createTime< ?
    //如果输入了地区，按照地区查询and location =?";*/
    if(vo.getName()!=null && !"".equals(vo.getName().trim())){ 
        sql+=" and teamName like '%"+vo.getName().trim()+"%'";
    }
    if(vo.getBeginTime()!=null ){
        sql+=" and getEndTime>"+vo.getBeginTime();
    }
    if(vo.getBeginTime()!=null ){
        sql+=" and createTime<="+vo.getEndTime();
    }
    if(vo.getLocation()!=null && !"".equals(vo.getLocation().trim())){
        sql+=" and location ="+vo.getLocation().trim();
    }

QueryTeamVO.java

package com.kkb.pojo; 
import java.util.Date;
/**
 * ClassName: QueryVO
 * 自己封装的球队查询条件
 * @author wanglina
 * @version 1.0
 */
public class QueryTeamVO {
    private String name;
    private Date beginTime;
    private Date endTime;
    private String location;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Date getBeginTime() {
        return beginTime;
    }
    public void setBeginTime(Date beginTime) {
        this.beginTime = beginTime;
    }
    public Date getEndTime() {
        return endTime;
    }
    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }
    public String getLocation() {
        return location;
    }
    public void setLocation(String location) {
        this.location = location;
    }
}

TeamMapper.java接口添加：

List<Team> queryByVO(QueryTeamVO vo);

TeamMapper.xml映射文件添加：

<!--多条件查询：
    模糊查询的写法可以使用3种方式：
    方式1： and teamName like #{name} ,传递参数的时候带上%，例如vo.setName("%人%")
    方式2： and teamName like ‘%${name}%’ 传递参数的时候没有%，例如vo.setName("人")
    方式3： and teamName like concat(concat('%',#{name}),'%') 例如vo.setName("人") concat(str1,str2)函数是字符串拼接使用-->
    <select id="queryByVO" parameterType="QueryVO" resultMap="baseResultMap">
        select * from team
        <where>
            <!-- 如果用户输入了名称，就模糊查询    and teamName like '%?%'-->
            <if test="name!=null ">
                and teamName like concat(concat('%',#{name}),'%')
            </if>
            <if test="beginTime!=null ">
                and createTime>=#{beginTime}
            </if>
            <if test="endTime!=null ">
                and createTime&lt;=#{endTime}
            </if>
            <if test="location!=null ">
                and location=#{location}
            </if>
        </where>
    </select>

TestSQL.java


package com.kkb.test;
import com.kkb.mapper.TeamMapper;
import com.kkb.pojo.QueryTeamVO;
import com.kkb.pojo.Team;
import com.kkb.utils.MybatisUtil;
import org.junit.Test;
import java.util.Date; import java.util.List;
/**
 * ClassName: TestSQL
 * 测试类
 * @author wanglina
 * @version 1.0
 */
public class TestSQL {
    private TeamMapper teamMapper = MybatisUtil.getSqlSession().getMapper(TeamMapper.class);
    @Test
    public void test1(){
        QueryTeamVO vo=new QueryTeamVO();
        vo.setName("人");// 依次添加查询条件添加控制台的SQL语句
        //vo.setEndTime(new Date());//依次添加查询条件观察控制台的SQL语句
        //vo.setLocation("加利福尼亚州洛杉矶");//依次添加查询条件观察控制台的SQL语句
        List<Team> teams = teamMapper.queryByVO(vo);
        for (Team team : teams) { System.out.println(team);
        }
    }
}

运行结果：

Preparing: select * from team WHERE teamName like concat(concat('%',?),'%')

2 set 标签在 update 中的作用

1 更新的原有写法

TeamMapper.java接口中的方法：

int update(Team team);

TeamMapper.xml映射文件对应的内容：

<update id="update" parameterType="com.kkb.pojo.Team">
    update team set teamName=#{teamName},location=#{location},createTime=#{createTime} where teamId=#{teamId}
</update>

测试类中添加测试方法：

TeamMapper teamMapper=MybatisUtil.getSqlSession().getMapper(TeamMapper.class);
@Test
public void  test2(){ 
    Team team=new Team();
    team.setTeamId(1055); team.setTeamName("lina");
    int update = teamMapper.update2(team); 
    MybatisUtil.getSqlSession().commit(); 
    System.out.println(update);
}

测试结果：

DEBUG [main] - ==>  Preparing: update team set teamName=?,location=?,createTime=? where teamId=?
DEBUG [main] - ==> Parameters: lina(String), null, null, 1063(Integer)
DEBUG [main] - <==    Updates: 1
DEBUG [main] - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@64cd705f]
1

弊端：没有赋值的属性会被赋值为空。

2 使用set标签构建动态的SQL语句

TeamMapper.java接口中添加方法：

int update1(Team team);

TeamMapper.xml映射文件对应的内容：

<update id="update1" parameterType="com.kkb.pojo.Team"> update team
    <set>
        <if test="teamName!=null"> teamName=#{teamName},</if>
        <if test="location!=null"> location=#{location},</if>
        <if test="createTime!=null"> createTime=#{createTime},</if>
    </set>
    where teamId=#{teamId}
</update>

测试代码

@Test
public void test2(){ 
    Team team=new Team();
    team.setTeamId(1055);
    team.setTeamName("lina");
    int update = teamMapper.update1(team);
    MybatisUtil.getSqlSession().commit();
    System.out.println(update);
}

测试结果

DEBUG [main] - ==>  Preparing: update team SET teamName=? where teamId=?
DEBUG [main] - ==> Parameters: lina(String), 1055(Integer)
DEBUG [main] - <==    Updates: 0
DEBUG [main] - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@20deea7f]
0

优点：动态SQL只更新非空属性列。

3 forEach 标签

1 批量添加

TeamMapper.java接口中添加方法：

void addList(List<Team> list);

TeamMapper.xml映射文件对应的内容：

    <!--批量添加-->
    <insert id="addList" parameterType="arraylist"> INSERT INTO team (teamName,location) VALUES
        <!--collection:要遍历的集合；参数是集合类型，直接写list
        item:遍历的集合中的每一个数据separator：将遍历的结果用，分割-->
        <foreach collection="list" item="t" separator=","> (#{t.teamName},#{t.location})
        </foreach>
    </insert>

编写测试类

@Test
    public void test3(){
        List<Team> list=new ArrayList<>();
        for(int i=1;i<=3;i++){
            Team team=new Team();
            team.setTeamName("lina"+i);
            team.setLocation("bj"+i);
            list.add(team);
        }
        teamMapper.addList(list);
        MybatisUtil.getSqlSession().commit();
    }

运行结果

DEBUG [main] - ==>  Preparing: INSERT INTO team (teamName,location) VALUES (?,?) , (?,?) , (?,?)
DEBUG [main] - ==> Parameters: lina1(String), bj1(String), lina2(String), bj2(String), lina3(String), bj3(String)
DEBUG [main] - <==    Updates: 3
DEBUG [main] - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3835c46]

2 批量删除

TeamMapper.java接口中添加方法：

void delList(List<Integer> list);

TeamMapper.xml映射文件对应的内容：

    <!-- 批量删除 -->
    <delete id="delList" >
        delete from team where teamId in
        <!--collection:要遍历的集合；参数是集合类型，直接写list item:遍历的集合中的每一个数据
        separator：将遍历的结果用，分割
        open="(" close=")"：表示将遍历结果用open close包裹起来-->
        <foreach collection="list" item="teamId" separator="," open="(" close=")">
            #{teamId}
        </foreach>
    </delete>

编写测试类

@Test
    public void test4() {
        List<Integer> list = new ArrayList<>();
        list.add(1109);
        list.add(1110); 
        list.add(1111);
        teamMapper.delList(list);
        MybatisUtil.getSqlSession().commit();
    }

代码结果

DEBUG [main] - ==>  Preparing: delete from team where teamId in ( ? , ? , ? )
DEBUG [main] - ==> Parameters: 1109(Integer), 1110(Integer), 1111(Integer)
DEBUG [main] - <==    Updates: 0
DEBUG [main] - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3835c46]