动态SQL基本语句用法

最新推荐文章于 2023-05-23 13:13:38 发布

行走的屌丝

最新推荐文章于 2023-05-23 13:13:38 发布

阅读量394

点赞数

分类专栏： Sql

原文链接：https://www.cnblogs.com/zhangsonglin/p/11178139.html

版权

Sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.if语句
如果empno不为空，则在WHERE参数后加上AND empno = #{empno},这里有1=1所以即使empno为null，WHERE后面也不会报错。

映射文件

<select id="getEmpById2" resultType="emp"> 
        SELECT * FROM emp WHERE 1=1
        <if test="empno != null">
            AND empno = #{empno}
        </if>   
    </select>

EmpMapper接口

public Emp getEmpById2(@Param(“empno”)Integer empno) throws IOException;
有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。而使用if标签时，只要test中的表达式为 true，就会执行 if 标签中的条件。MyBatis 提供了 choose 元素。if标签是与(and)的关系，而 choose 是或(or)的关系。

2.where语句和Choose(when,otherwise)
1.Where后面empno和ename为null，那where就不会出现在sql语句中。

choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。
映射文件

<select id="getEmpById3" resultType="emp" parameterType="emp">
        SELECT * FROM EMP 
        <where>
            <choose>
                <when test="empno != null">
                    AND empno like #{empno}
                </when>
                <when test="ename != null">
                    AND ename like #{ename}
                </when>
                <otherwise>
                    AND  job = "zz"
                </otherwise>
            </choose>
        </where>
    </select>

EmpMapper接口

public Emp getEmpById3(Emp emp) throws IOException;
3.set语句
set主要也是用来解决更新问题的。
映射文件

<update id="updateEmprById2" parameterType="emp">
        UPDATE emp
        <set>
            <if test="ename!=null"> ename=#{ename},</if>
            <if test="job!=null"> job=#{job},</if>
        </set>
        <where>
            <if test="empno!=null">
                empno=#{empno};
            </if>
        </where>
    </update>

EmpMapper接口

public Integer updateEmprById2(Emp emp) throws IOException;
4.trim
trim标记是一个格式化的标记，可以完成set或者是where标记的功能。
相关属性：
Prefix：前缀。
prefixOverrides：去掉第一个指定内容。
suffix：后缀。
suffixoverride：去掉最后一个指定内容。
映射文件

<!-- 代替where -->
    <select id="getEmpById4" resultType="emp" parameterType="emp">
        SELECT * FROM emp
        <!-- <where> <if test="username!=null"> and name = #{username} </if> </where> -->
        <trim prefix="where" prefixOverrides="AND |OR ">
            <if test="empno != null">
                and empno = #{empno}
            </if>
            <if test="ename!=null">
                AND ename = #{ename}
            </if>
        </trim>
    </select>
映射文件

<!-- 代替set -->
    <update id="updateEmprById3" parameterType="emp">
        update emp
        <trim prefix="set" suffixOverrides=",">
            <if test="ename!=null">
                ename = #{ename},
            </if>
            <if test="job != null">
                job = #{job}
            </if>
        </trim>
        <trim prefix="where" prefixOverrides="AND |OR ">
            <if test="empno != null">
                and empno = #{empno}
            </if>
        </trim>
    </update>

EmpMapper接口

public Emp getEmpById4(Emp emp) throws IOException;
public Integer updateEmprById3(Emp emp) throws IOException;

5.foreach语句
foreach用来遍历，遍历的对象可以是数组，也可以是集合。
相关属性：
Collection：collection属性的值有三个分别是list、array、map三种。
Open：前缀。
Close：后缀。
Separator：分隔符，表示迭代时每个元素之间以什么分隔。
Item：表示在迭代过程中每一个元素的别名。
Index：用一个变量名表示当前循环的索引位置。
映射文件

<insert id="addEmp6">
    insert into emp(ename,job)values
    <foreach collection="emps" item="emp" separator=",">
        (#{emp.ename},#{emp.job})
    </foreach>
</insert>

EmpMapper接口

public int addEmp6(@Param(“emps”)List emps);
6.SQL块
映射文件

 <sql id="baseSql">
        empno,ename,job
    </sql>

<!-- 使用include引入sql块 -->
<select id="selEmp1" resultType="emp">
    select 
    <include refid="baseSql"/>
     from emp 
</select>

EmpMapper接口

public List selEmp1() throws IOException;
7.bind
映射文件

<select id="getEmpById6" resultType="emp">
        <!-- 声明了一个参数empno 在后面就可以使用了 -->
        <bind name="empno" value="7975" />
        select * from emp where empno=${empno}
    </select>

EmpMapper接口

public Emp getEmpById6()throws IOException;
全部代码：
EmpMapper接口

package com.zsl.dao;

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

import org.apache.ibatis.annotations.Param;

import com.zsl.pojo.Emp;

public interface EmpMapper {
public Integer addEmp(Emp emp) throws IOException;

public Integer deleteEmpById(Integer empno) throws IOException;
public Integer updateEmprById(Emp emp) throws IOException;

public Emp getEmpById(@Param("empno")Integer empno) throws IOException;

public Integer addEmp1(String ename,String job) throws IOException;
public Integer addEmp2(String ename,String job) throws IOException;

public Integer addEmp3(@Param("ename")String ename,@Param("job")String job) throws IOException;

public Integer addEmp4(@Param("ename")String ename,@Param("job")String job) throws IOException;

public List<Emp> selEmp() throws IOException;

public Emp getEmpById2(@Param("empno")Integer empno) throws IOException;

public Emp getEmpById3(Emp emp) throws IOException;
public Integer updateEmprById2(Emp emp) throws IOException;
public Emp getEmpById4(Emp emp) throws IOException;
public Integer updateEmprById3(Emp emp) throws IOException;
// 如果不指定@Param 默认是array
public List<Emp> getEmpById5(@Param("empnos")List<Integer> empno);
public int addEmp6(@Param("emps")List<Emp> emps);
public List<Emp> selEmp1() throws IOException;
public Emp getEmpById6()throws IOException;

}
EmpMapper.XML映射文件

<?xml version="1.0" encoding="UTF-8" ?>

   <delete id="deleteEmpById" parameterType="int"> 
        delete from emp where empno=#{empno}    
    </delete>
    <update id="updateEmprById" parameterType="emp"> 
        update emp set name = #{ename} where empno=#{empno}    
    </update>

<select id="getEmpById" resultType="emp"> 
    select * from emp where empno=${empno}   
</select>

<insert id="addEmp1">
    insert into emp(ename,job)values(#{arg0},#{arg1})
</insert>

<insert id="addEmp2">
    insert into emp(ename,job)values(#{param1},#{param2})
</insert>

<insert id="addEmp3">
    insert into emp(ename,job)values(${ename},${job})
</insert>

<insert id="addEmp4">
    insert into emp(ename,job)values(#{ename},#{job})
</insert>

<select id="selEmp3" resultType="emp"> 
    select empno empno,ename ename,job job,mgr mgrA from emp   
</select> 

<resultMap type="emp" id="baseMap">
    <id column="empno" property="empno" />
    <result property="ename" column="ename" />
    <result property="job" column="job" />
    <result property="mgrA" column="mgr" /> 
</resultMap> 


<select id="selEmp" resultType="emp" resultMap="baseMap"> 
    select * from emp   
</select>

<!-- useGeneratedKeys:使用生成的主键 keyProperty="id"：将生成的主键的值保存到对象的id属性中 -->
<insert id="addEmp" parameterType="emp" useGeneratedKeys="true"
    keyProperty="empno">
    insert into emp(ename,job)values(#{ename},#{job})
</insert> 

<insert id="addEmp6" parameterType="emp">
    <selectKey keyProperty="empno" resultType="int">
        select
        LAST_INSERT_ID()
    </selectKey>
    insert into emp(ename,job)values(#{ename},#{job})
</insert>

<select id="getEmpById2" resultType="emp"> 
    SELECT * FROM emp WHERE 1=1
    <if test="empno != null">
        AND empno = #{empno}
    </if>   
</select>

<select id="getEmpById3" resultType="emp" parameterType="emp">
    SELECT * FROM EMP 
    <where>
        <choose>
            <when test="empno != null">
                AND empno like #{empno}
            </when>
            <when test="ename != null">
                AND ename like #{ename}
            </when>
            <otherwise>
                AND  job = "zz"
            </otherwise>
        </choose>
    </where>
</select>


<update id="updateEmprById2" parameterType="emp">
    UPDATE emp
    <set>
        <if test="ename!=null"> ename=#{ename},</if>
        <if test="job!=null"> job=#{job},</if>
    </set>
    <where>
        <if test="empno!=null">
            empno=#{empno};
        </if>
    </where>
</update>

<!-- 代替where -->
<select id="getEmpById4" resultType="emp" parameterType="emp">
    SELECT * FROM emp
    <!-- <where> <if test="username!=null"> and name = #{username} </if> </where> -->
    <trim prefix="where" prefixOverrides="AND |OR ">
        <if test="empno != null">
            and empno = #{empno}
        </if>
        <if test="ename!=null">
            AND ename = #{ename}
        </if>
    </trim>
</select>

   <update id="updateEmprById3" parameterType="emp">
        update emp
        <trim prefix="set" suffixOverrides=",">
            <if test="ename!=null">
                ename = #{ename},
            </if>
            <if test="job != null">
                job = #{job}
            </if>
        </trim>
        <trim prefix="where" prefixOverrides="AND |OR ">
            <if test="empno != null">
                and empno = #{empno}
            </if>
        </trim>
    </update>

<select id="getEmpById5" resultType="emp">
    select * from emp where empno in
    <foreach collection="empnos" open="(" close=")" separator=","
        item="empno">
        #{empno}
    </foreach>
</select>

<insert id="addEmp6">
    insert into emp(ename,job)values
    <foreach collection="emps" item="emp" separator=",">
        (#{emp.ename},#{emp.job})
    </foreach>
</insert>

<!-- 定义重复使用的SQL内容 -->
<sql id="baseSql">
    empno,ename,job
</sql>

<!-- 使用include引入sql块 -->
<select id="selEmp1" resultType="emp">
    select 
    <include refid="baseSql"/>
     from emp 
</select>


<select id="getEmpById6" resultType="emp">
    <!-- 声明了一个参数empno 在后面就可以使用了 -->
    <bind name="empno" value="7975" />
    select * from emp where empno=${empno}
</select>

</mapper>

全局配置文件
mybatis-cfg.xml

<?xml version="1.0" encoding="UTF-8" ?>

<configuration>
 <!-- 导入外部资源 -->
 <properties resource="db.properties"/> 
 
  <!-- 自定义别名 -->
    <typeAliases>
        <typeAlias type="com.zsl.pojo.Emp" alias="emp"/>
    </typeAliases>
 <!-- 环境 -->
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${userName}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  <!-- 注册映射文件 -->
  <mappers>
    <mapper resource="EmpMapper.xml"/>
  </mappers>
</configuration>

Pojo

package com.zsl.pojo;

import java.util.List;

public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgrA;//这里与数据库字段名称不同，数据库字段为mgr
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}

public Emp() {
    super();
    // TODO Auto-generated constructor stub
}
public Integer getMgrA() {
    return mgrA;
}
public void setMgrA(Integer mgrA) {
    this.mgrA = mgrA;
}
public Emp(Integer empno, String ename, String job, Integer mgrA) {
    super();
    this.empno = empno;
    this.ename = ename;
    this.job = job;

// this.mgrA = mgrA;
}
@Override
public String toString() {
return “Emp [empno=” + empno + “, ename=” + ename + “, job=” + job + “]”;
//+ “, mgrA=” + mgrA
}
}
DBUtils

package com.zsl.utils;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class DbUtils {
public static SqlSession getSession() throws IOException {
// TODO Auto-generated method stub
// 1.通过Resources对象加载配置文件
InputStream inputStream = Resources.getResourceAsStream(“mybatis-cfg.xml”);
// 2.获取SqlSessionFactory对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
// 3.通过SqlSessionFactory对象获取SQLSession对象
SqlSession session = factory.openSession();
return session;
}

public static void commit(SqlSession session) {
    // TODO Auto-generated method stub
    session.commit();
}

public static void close(SqlSession session) {
    // TODO Auto-generated method stub
    session.close();
}

}
测试类Test

package com.zsl.test;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.util.ArrayList;
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 com.zsl.dao.EmpMapper;
import com.zsl.pojo.Emp;
import com.zsl.utils.DbUtils;

public class Test {
public static void main(String[] args) throws IOException {

    Test test = new Test();
    System.out.println(test.addUser3());
}

public Integer addUser1() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Integer integer = mapper.addEmp1("zsladd1", "程序员1");
    session.commit();
    session.close();
    return integer;
}

public Integer addUser2() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Integer integer = mapper.addEmp2("zsladd2", "程序员2");
    session.commit();
    session.close();
    return integer;
}

public Integer addUser3() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Integer integer = mapper.addEmp3("zsladd3", "程序员3");
    session.commit();
    session.close();
    return integer;
}

public Integer addUser4() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Integer integer = mapper.addEmp4("zsladd4", "程序员4");
    session.commit();
    session.close();
    return integer;
}

public Emp getEmpById() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Emp emp = mapper.getEmpById(7902);
    session.close();
    return emp;
}

public List<Emp> selEmp() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    List<Emp> list = mapper.selEmp();
    return list;
}

public Integer addUser() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Emp emp = new Emp();
    emp.setEname("zsl00");
    emp.setJob("程序员00");
    Integer integer = mapper.addEmp(emp);
    System.out.println("该条记录主键：" + emp.getEmpno());
    session.commit();
    session.close();
    return integer;
}

public Emp getEmpById2() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Emp emp = mapper.getEmpById2(7900);
    session.close();
    return emp;
}

public Emp getEmpById3() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Emp emp2 = new Emp();
    emp2.setEname("JAMES");
    Emp emp = mapper.getEmpById3(emp2);
    return emp;
}

public Integer updateEmprById2() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Emp emp = new Emp();
    emp.setEmpno(7941);
    emp.setEname("zsl55");
    emp.setJob("程序员55");
    Integer integer = mapper.updateEmprById2(emp);
    session.commit();
    session.close();
    return integer;
}

public Integer updateEmprById3() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Emp emp = new Emp();
    emp.setEmpno(7941);
    emp.setEname("zsl77");
    emp.setJob("程序员77");
    Integer integer = mapper.updateEmprById2(emp);
    session.commit();
    session.close();
    return integer;
}

public Emp getEmpById4() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Emp emp2 = new Emp();
    emp2.setEname("JAMES");
    Emp emp = mapper.getEmpById3(emp2);
    session.close();
    return emp;
}

public List<Emp> getEmpById5() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    List<Integer> list = new ArrayList<>();
    list.add(7969);
    list.add(7970);
    list.add(7971);
    List<Emp> empList = mapper.getEmpById5(list);
    session.close();
    return empList;
}

public Integer addEmp6() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    List<Emp> emps = new ArrayList<>();
    Emp emp = new Emp();
    emp.setEname("zsl88");
    emp.setJob("程序员88");
    emps.add(emp);
    Emp empA = new Emp();
    empA.setEname("zsl99");
    empA.setJob("程序员99");
    emps.add(empA);
    int addEmp6 = mapper.addEmp6(emps);
    session.commit();
    session.close();
    return addEmp6;
}

public List<Emp> selEmp1() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    List<Emp> list = mapper.selEmp1();
    session.close();
    return list;
}

public Emp getEmpById6() throws IOException {
    // TODO Auto-generated method stub
    SqlSession session = DbUtils.getSession();
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    Emp emp = mapper.getEmpById6();
    session.close();
    return emp;

}

}

行走的屌丝

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
动态SQL基本语句用法

1.if语句如果empno不为空，则在WHERE参数后加上AND empno = #{empno},这里有1=1所以即使empno为null，WHERE后面也不会报错。映射文件 SELECT * FROM emp WHERE 1=1 AND empno = #{empno} EmpMapper接口...
复制链接

扫一扫