欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

【SSM - MyBatis篇10】动态SQL语句案例实现

程序员文章站 2022-05-16 23:50:10
案例描述  创建customer用户表,有三个属性字段,id、username、job,通过动态SQL语句对其进行增删查改。创建数据库表customer-- ------------------------------ customer表-- ----------------------------DROP TABLE IF EXISTS `customer`;CREATE TABLE `customer` ( `id` int(11) NOT NULL AUTO_INCREMENT,...

案例描述

  创建customer用户表,有三个属性字段,id、username、job,通过动态SQL语句对其进行增删查改。

准备条件

1. 创建数据库表customer

-- ----------------------------
-- customer表
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `job` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

-- ----------------------------
-- 初始数据
-- ----------------------------
INSERT INTO `customer` VALUES ('1', '张三', '程序员');
INSERT INTO `customer` VALUES ('2', '李四', '项目经理');
INSERT INTO `customer` VALUES ('3', '王五', '测试员');
INSERT INTO `customer` VALUES ('4', '赵六', '开发人员');

2. 创建javabean对象

public class Customer {

    private Integer id;         //id主键自动增长
    private String username;    //用户名
    private String job;         //工作
    //省略getter/setter方法
}

3. 创建dao层接口

public interface CustomerMapper {

//    不用标签,普通方法测试- 通过name和job查询Customer
    List<Customer> getCustomerByNameAndJob(Customer customer);
//    通过if标签实现查询
    List<Customer> getCustomerByNameAndJobForIf(Customer customer);
//    通过choose标签实现查询
    List<Customer> getCustomerByNameAndJobForChoose(Customer customer);
//    where标签查询
    List<Customer> getCustomerByNameAndJobForWhere(Customer customer);
//    trim标签
    List<Customer> getCustomerByNameAndJobForTrim(Customer customer);
//    set标签更新数据
    int updateForSet(Customer customer);
//  通过trim+set实现更新数据,添加忽略前后缀实现
    int updateForTrim(Customer customer);
//   通过foreach标签查询 - 参数是list集合id (相当于select * from tableName where id in {1,2,3} 查询结果为集合)
    List<Customer> getCustomerByIdsForeach(List<Integer> ids);
//  bind标签,解决可移植性问题 ,解决不同数据库拼接函数或连接符号的不同(防止sql注入)
    List<Customer> getCustomerByNameAndJobForBind(Customer customer);
//  通过foreach和insert,实现foreach循环批量插入
    boolean insertCustomerList(List<Customer> customerList);
}

4. 创建连接数据库的属性文件db.properties(键值对形式)

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&useSSL=false
jdbc.username = root
jdbc.password = 861221293

5. spring整合MyBatis,核心配置文件applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">


    <!--1. 引入jdbc的属性文件,在配置中通过占位使用 -->
    <context:property-placeholder location="classpath*:db.properties" />

    <!--2. <context:component-scan>扫描包中注解所标注的类(@Component@Service@Controller@Repository) -->
    <context:component-scan base-package="com.xgf.dynamic_sql"/>

    <!--3. 由spring管理    配置数据源数据库连接(从jdbc属性文件中读取参数) -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="driverClassName" value="${jdbc.driver}"/>
    </bean>

    <!--  通过spring来管理Mybatis的sqlSessionFactory对象创建  -->
    <!--4. 通过完全限定名匹配查找  创建SqlSessionFactoryBean  -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- 5. mybatis提供的一个注解扫描标签(搜索映射器 Mapper 接口),通过自动扫描注解的机制,创建每个dao接口定义的bean  -->
    <mybatis:scan base-package="com.xgf.dynamic_sql.dao"/>

</beans>

6. 创建测试类,加载核心配置文件

public class CustomerMapperTest {

    private static ApplicationContext applicationContext = null;
    private static CustomerMapper customerMapper = null;
    private static Customer customer = new Customer("赵六","开发人员");//初始化customer数据

    //只加载一次  @BeforeClass@BeforeClass只在类中执行一次, 必须声明为public static
    @BeforeClass
    public static void init(){
        //加载配置文件
        applicationContext = new ClassPathXmlApplicationContext("com/xgf/dynamic_sql/config/applicationContext.xml");
        //获取bean的两种方式
        // 1.类名首字母小写
//        customerMapper = (CustomerMapper) applicationContext.getBean("customerMapper");
        // 2.类.class
        customerMapper = (CustomerMapper) applicationContext.getBean(CustomerMapper.class);
    }
}



动态SQL语句测试

1. 测试getCustomerByNameAndJob()不用动态sql实现查询

//    不用标签,普通方法测试- 通过name和job查询Customer
    @Test
    public void getCustomerByNameAndJob() {
        System.out.println(customerMapper.getCustomerByNameAndJob(customer));;
    }
<!--正常查询 多条件存在查询-->
    <select id="getCustomerByNameAndJob" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        where username like concat('%',#{username},'%') and job = #{job}
    </select>

运行sql:Preparing: select id,username,job from customer where username like concat(’%’,?,’%’) and job = ?
运行参数:Parameters: 赵六(String), 开发人员(String)
运行结果:[Customer{id=4, username=‘赵六’, job=‘开发人员’}]

2. 通过if标签实现查询

@Test
    public void getCustomerByNameAndJobForIf() {
        System.out.println(customerMapper.getCustomerByNameAndJobForIf(customer));
    }
<!-- if 查询,满足test条件就执行里面的代码,程序拼接and/or需要有一个where 1=1 的前提来拼接 -->
    <select id="getCustomerByNameAndJobForIf" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        where 1=1
        <if test="username!=null and username!=''">
            and username like concat('%',#{username},'%')
        </if>
        <if test="job!=null and job!=''">
            and job=#{job}
        </if>

    </select>

运行sql:Preparing: select id,username,job from customer where 1=1 and username like concat(’%’,?,’%’) and job=?
运行参数:Parameters: 赵六(String), 开发人员(String)
运行结果: [Customer{id=4, username=‘赵六’, job=‘开发人员’}]

3. 通过choose标签查询

//    通过choose标签实现查询 这里讲customer的job和username设为空值,然后choose中的when条件都不满足,执行otherwise
    @Test
    public void getCustomerByNameAndJobForChoose() {
        customer.setJob(null);
        customer.setUsername(null);
        System.out.println(customerMapper.getCustomerByNameAndJobForChoose(customer));
    }
<!-- choose 查询 满足一个条件,就忽略后面的条件,没有一个条件满足,就输出所有-->
    <select id="getCustomerByNameAndJobForChoose" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        where 1=1
        <choose>
            <when test="username!=null and username!=''">
                and username like concat('%',#{username},'%')
            </when>
            <when test="job!=null and job!=''">
                and job=#{job}
            </when>
            <otherwise>
                order by username desc
            </otherwise>
        </choose>
    </select>

运行的sql:Preparing: select id,username,job from customer where 1=1 order by username desc **
运行参数:
(空值,没有参数)**
运行结果: [Customer{id=4, username=‘赵六’, job=‘开发人员’}, Customer{id=3, username=‘王五’, job=‘测试员’}, Customer{id=2, username=‘李四’, job=‘项目经理’}, Customer{id=1, username=‘张三’, job=‘程序员’}]
  因为条件都不满足,所以按照username的desc倒序输出所有customer(排序输出不能对汉字排序,但是会将汉字识别为拼音,进行字母排序)

4. where标签查询

//    where标签查询
    @Test
    public void getCustomerByNameAndJobForWhere() {
        System.out.println(customerMapper.getCustomerByNameAndJobForWhere(customer));
    }
<!--where标签,如果后面没有条件(条件都不满足)不加where,如果后面有条件,会自动增加一个where 
    会将多余的and、or去掉,会自动填充第一个缺失的and、or-->
    <select id="getCustomerByNameAndJobForWhere"
            parameterType="com.xgf.dynamic_sql.bean.Customer"
            resultType="com.xgf.dynamic_sql.bean.Customer" useCache="true">
        select id,username,job
        from customer
        <where>
            <if test="username!=null and username!=''">
                and username like concat('%',#{username},'%')
            </if>
            <if test="job!=null and job!=''">
                and job=#{job}
            </if>
        </where>

    </select>

运行sql:Preparing: select id,username,job from customer WHERE username like concat(’%’,?,’%’) and job=?
运行参数:Parameters: 赵六(String), 开发人员(String)
运行结果:[Customer{id=4, username=‘赵六’, job=‘开发人员’}]

5. trim标签查询

//trim标签查询
    @Test
    public void getCustomerByNameAndJobForTrim() {
        System.out.println(customerMapper.getCustomerByNameAndJobForTrim(customer));
    }
	<!--trim 可以添加前后缀(prefix和suffix) 覆盖前后缀(prefixOverrides和suffixOverrides)
        这里覆盖了前缀and|or,添加了前缀where
    -->
    <select id="getCustomerByNameAndJobForTrim" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        <trim prefix="where" prefixOverrides="and|or">
            <if test="username!=null and username!=''">
                or username like concat('%',#{username},'%')
            </if>
            <if test="job!=null and job!=''">
                and job=#{job}
            </if>
        </trim>

    </select>

运行sql:Preparing: select id,username,job from customer where username like concat(’%’,?,’%’) and job=?
运行参数:Parameters: 赵六(String), 开发人员(String)
运行结果:[Customer{id=4, username=‘赵六’, job=‘开发人员’}]

6. set用于更新

//set标签更新数据
    @Test
    public void updateForSet() {
        customer.setId(4);//前面初始化数据没有id
        customer.setUsername("赵六数据更新");
        customer.setJob("赵六当老板");
        System.out.println(customerMapper.updateForSet(customer));
    }
<!--set在动态update语句中,可以使用<set>元素动态更新列 ->
            set 元素可以用于动态包含需要更新的列,忽略其它不更新的列(如果其它列数据为null,就不更新)。
        (返回结果是整数,更新删除增加不需要写resultType)
    -->
    <update id="updateForSet" parameterType="com.xgf.dynamic_sql.bean.Customer" flushCache="false">
        update customer
        <set>
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="job!=null and job!=''">
                job=#{job},
            </if>
        </set>
        where id=#{id}
    </update>

运行sql:Preparing: update customer SET username=?, job=? where id=?
运行参数:Parameters: 赵六数据更新(String), 赵六当老板(String), 4(Integer)
运行结果:Updates: 1 (更新影响数据1条,更新成功)
【SSM - MyBatis篇10】动态SQL语句案例实现

7. trim+set实现更新数据

//通过trim+set实现更新数据,通过添加和忽略前后缀实现
    @Test
    public void updateForTrim() {
        customer.setId(4);//前面初始化数据没有id
        customer.setUsername("赵六通过trim更新");
        customer.setJob("赵六董事长了哟");
        System.out.println(customerMapper.updateForTrim(customer));
    }
<!--通过trim+set实现更新数据,通过添加和忽略前后缀实现  添加前缀set,忽略后缀的逗号,-->
    <update id="updateForTrim" parameterType="com.xgf.dynamic_sql.bean.Customer">
        update customer
        <trim prefix="set" suffixOverrides=",">
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="job!=null and job!=''">
                job=#{job},
            </if>
        </trim>
        where id=#{id}
    </update>

运行sql:Preparing: update customer set username=?, job=? where id=?
运行参数:Parameters: 赵六通过trim更新(String), 赵六董事长了哟(String), 4(Integer)
运行结果:Updates: 1(更新数据一条,更新成功)
【SSM - MyBatis篇10】动态SQL语句案例实现

8. foreach标签查询

//通过foreach标签查询 - 参数是list集合id (相当于select * from tableName where id in {1,2,3} 查询结果为集合)
    @Test
    public void getCustomerByIdsForeach() {
        ArrayList<Integer> idList = new ArrayList<>();
        idList.add(2);
        idList.add(3);
        idList.add(4);
        System.out.println(customerMapper.getCustomerByIdsForeach(idList));

    }
<!--foreach标签
        collection:传入的集合   item:循环出来的数据起别名   open:以什么开始 
        colose:以什么结束  separator: 分隔符 index: 循环计数器
        通过where标签和if标签 判断如果传入的列表为空,则后面的sql语句不添加,就查询所有customer
    -->
    <select id="getCustomerByIdsForeach" parameterType="list" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        <where>
            <if test="list!=null and list.size()>0">
                id in
                <foreach collection="list" item="id" open="(" close=")" separator="," index="index" >
                    #{id}
                </foreach>
            </if>
        </where>

    </select>

运行sql:Preparing: select id,username,job from customer WHERE id in ( ? , ? , ? )
运行参数:Parameters: 2(Integer), 3(Integer), 4(Integer)
运行结果:[Customer{id=2, username=‘李四’, job=‘项目经理’}, Customer{id=3, username=‘王五’, job=‘测试员’}, Customer{id=4, username=‘赵六通过trim更新’, job=‘赵六董事长了哟’}]

9. bind标签

//bind标签,解决可移植性问题 ,解决不同数据库拼接函数或连接符号的不同(防止sql注入)
    @Test
    public void getCustomerByNameAndJobForBind() {
        customer.setUsername("李四");
        customer.setJob("项目经理");
        System.out.println(customerMapper.getCustomerByNameAndJobForBind(customer));
    }
<!--  bind标签 解决可移植性问题  解决不同数据库拼接函数或连接符号的不同
            concat()数据库连接,将字符串拼接成一个字符串,是MySQL数据库独有的,在别的数据库中不能识别
            bind标签,name取名给其它地方调用,可以随便取, _parameter.getXXX()调用get方法
    -->
    <select id="getCustomerByNameAndJobForBind" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        <if test="username!=null and username!=''">
            <bind name="pattern" value="'%' + _parameter.getUsername() + '%'" />
        </if>
        select id,username,job
        from customer
        <where>
            <if test="username!=null and username!=''">
                and username like #{pattern}
            </if>
            <if test="job!=null and job!=''">
                and job=#{job}
            </if>
        </where>

    </select>

运行sql:Preparing: select id,username,job from customer WHERE username like ? and job=?
运行参数:Parameters: %李四%(String), 项目经理(String)
运行结果:[Customer{id=2, username=‘李四’, job=‘项目经理’}]

10. 通过foreach和insert,实现foreach循环批量插入

//通过foreach和insert,实现foreach循环批量插入
    @Test
    public void insertCustomerList() {
        //customer表的主键id自动增长所以不需要赋值
        Customer customer1 = new Customer("钱一","大数据工程师");
        Customer customer2 = new Customer("孙二","AI工程师");
        Customer customer3 = new Customer("马三","运维工程师");

        ArrayList<Customer> customerList = new ArrayList<>();
        customerList.add(customer1);
        customerList.add(customer2);
        customerList.add(customer3);

        System.out.println(customerMapper.insertCustomerList(customerList));
    }
<!--foreach和insert共同使用  实现foreach循环批量插入
        item是每次从集合中取出一个对象的别名-->
    <insert id="insertCustomerList" parameterType="list">
        insert into customer(username,job) values
        <foreach collection="list" separator="," item="customer">
             (#{customer.username},#{customer.job})
         </foreach>
    </insert>

运行sql:Preparing: insert into customer(username,job) values (?,?) , (?,?) , (?,?)
运行参数:Parameters: 钱一(String), 大数据工程师(String), 孙二(String), AI工程师(String), 马三(String), 运维工程师(String)
运行结果:true(Updates: 3) (更新了三条sql语句,实现循环批量出入成功)
【SSM - MyBatis篇10】动态SQL语句案例实现
运行sql:Preparing: select id,username,job from customer WHERE username like ? and job=?
运行参数:Parameters: %李四%(String), 项目经理(String)
运行结果:[Customer{id=2, username=‘李四’, job=‘项目经理’}]

代码地址:https://github.com/strive-xgf/SSM/commit/0ca1f098051de82289d59e67aa2a5c88aacffcff

本文地址:https://blog.csdn.net/qq_40542534/article/details/108838091