MyBatis 动态SQL（二）

1.1创建项目

 1.2导入依赖

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>
 
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
 
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.12</version>
        </dependency>
 
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.18</version>
        </dependency>
 
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        
    </dependencies>

创建package和文件夹
src/main/java/下创建package
com.biem.pojo
com.biem.mapper
com.biem.util
src/main/resources/下创建文件夹
com/biem/mapper
src/test/java下创建package
com.biem.test

1.3mybatis核心配置文件mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 环境配置 -->
    <!-- 加载类路径下的属性文件 -->
    <properties resource="db.properties"/>
 
    <typeAliases>
        <package name="org.example.pojo"/>
    </typeAliases>
 
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 数据库连接相关配置 ,db.properties文件中的内容-->
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}"/>
                <property name="url" value="${mysql.url}"/>
                <property name="username" value="${mysql.username}"/>
                <property name="password" value="${mysql.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- mapping文件路径配置 -->
    <mappers>
        <package name="org.example.mapper"/>
    </mappers>
 
</configuration>

1.4mybatis属性文件jdbc.properties

mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
mysql.username=root
mysql.password=123

  1.5log4j.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
 
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 
    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <param name="Encoding" value="UTF-8"/>
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n"/>
        </layout>
    </appender>
    <logger name="java.sql">
        <level value="debug"/>
    </logger>
    <logger name="org.apache.ibatis">
        <level value="info"/>
    </logger>
    <root>
        <level value="debug"/>
        <appender-ref ref="STDOUT"/>
    </root>
</log4j:configuration>

需要在pom.xml中引入lombok，简化原来的实体类的代码

1.6用户配置文件

实体类文件

package com.biem.pojo;
 
import lombok.*;
 
/**
 * ClassName: Customer
 * Package: com.biem.pojo
 * Description:
 *
 * @Create 2023/4/5 22:17
 * @Version 1.0
 */
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
public class Customer {
    private Integer id;
    private String username;
    private String jobs;
    private String phone;
}
 

 mybatis接口文件：

package org.example.mapper;
 
 
public interface CustomerMapper {
   
}

1.8mybatis用户配置文件

<?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.biem.mapper.CustomerMapper">
    <!-- namespace要和mapper接口的全类名保持一致，例如com.biem.mybatis.mapper.xxxMapper -->
 
    <!-- sql语句要和接口的方法名保持一致 -->
 
</mapper>

1.8工具类

 2.1使用set标签更新记录

2.2 org.example.mapper.CustomerMapper.class中添加

public List<Customer> findCustomerByArray(int [] ids);

2.2 org/example/mapper/CustomerMapper.xml中添加

 <update id="updateCustomerBySet" parameterType="customer">
        update t_customer
        <set>
            <if test="username!=null and username!=''">
                username=#{username}
            </if>
            <if test="jobs!=null and jobs !=''">
                jobs=#{jobs}
            </if>
            <if test="phone!=null and phone!=''">
                phone=#{phone}
            </if>
        </set>
    </update>

2.3 功能测试 org.example.test.TestMybatis.java添加测试方法

    @Test
    public void testUpdateCustomerBySet(){
        // 通过工具类获取SqlSession对象
        SqlSession session = MybatisUtil.openSession();
        // 创建Customer对象，封装需要组合查询的条件
        Customer customer = new Customer();
        customer.setId(3);
        customer.setPhone("15111001100");
        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        int rows = mapper.updateCustomerBySet(customer);
        if(rows>0){
            System.out.println("您成功的更新了"+rows+"条记录");
        }else{
            System.out.println("更新失败");
        }
        // 关闭SqlSession
        session.close();
    }

运行结果：

3.1foreach元素迭代数组

3.2 org.example.mapper.CustomerMapper.class中添加

public List<Customer> findCustomerByArray(int [] ids);

 

3.3 org/example/mapper/CustomerMapper.xml中添加

    <!--public List<Customer> findCustomerByArray(int [] arrays);-->
    <select id="findCustomerByArray" parameterType="java.util.Arrays" resultType="customer">
        select * from t_customer where id in
        <foreach item = "id" index = "index" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

3.4 功能测试 org.example.test.TestMybatis.java添加测试方法

    @Test
    public void testFindCustomerByArray(){
        // 通过工具类获取SqlSession对象
        SqlSession session = MybatisUtil.openSession();
        int[] ids={1,3};
        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByArray(ids);
        System.out.println("customers = " + customers);
        // 关闭SqlSession
        session.close();
    }

运行结果：

 

3.5foreach元素迭代List

3.6 org.example.mapper.CustomerMapper.class中添加

public List<Customer> findCustomerByList(List<Integer> ids);

3.7 org/example/mapper/CustomerMapper.xml中添加

    <!--public List<Customer> findCustomerByList(List<Integer> ids);-->
    <select id="findCustomerByList" parameterType="java.util.List" resultType="customer">
        select * from t_customer where id in
        <foreach item = "id" index = "index" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

3.8功能测试 org.example.test.TestMybatis.java添加测试方法

@Test
    public void testFindCustomerByList(){
        // 通过工具类获取SqlSession对象
        SqlSession session = MybatisUtil.openSession();
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByList(ids);
        System.out.println("customers = " + customers);
        // 关闭SqlSession
        session.close();
    }

3.9 foreach元素迭代Map 

3.10 org.example.mapper.CustomerMapper.class中添加

 

3.11 org/example/mapper/CustomerMapper.xml中添加

    <!--public List<Customer> findCustomerByMap(Map<String, Object> map);-->
    <select id="findCustomerByMap" parameterType="java.util.Map" resultType="customer">
        select * from t_customer where jobs=#{jobs} and id in
        <foreach item = "ids" index = "index" collection="id" open="(" separator="," close=")">
            #{ids}
        </foreach>
    </select>

3.12 功能测试 org.example.test.TestMybatis.java添加测试方法

    @Test
    public void testFindCustomerByMap(){
        // 通过工具类获取SqlSession对象
        SqlSession session = MybatisUtil.openSession();
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        Map<String, Object> maps = new HashMap<>();
        maps.put("id", ids);
        maps.put("jobs","teacher");
        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByMap(maps);
        System.out.println("customers = " + customers);
        // 关闭SqlSession
        session.close();
    }

运行结果：