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>

 1.3mybatis核心配置文件mybatis-config.xml（其中typeAliases中的name需要自己修改，mappers中的name也需要自己修改）

代码：

<?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=自己数据库的密码

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>

1.6创建package和文件夹 (结构如图)

1.7用户配置文件

实体类文件

代码： 

package org.example.pojo;
 
import lombok.*;
 
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Customer {
    private Integer id;
    private String username;
    private String jobs;
    private String phone;
}

 接口文件：

1.8用户配置文件(注: <mapper namespace="org.example.mapper.CustomerMapper">需要根据自己项目修改)

代码：

<?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为映射的根节点-->
<!-- mapper为映射的根节点，namespace指定Dao接口的完整类名
mybatis会依据这个接口动态创建一个实现类去实现这个接口，
而这个实现类是一个Mapper对象-->
<mapper namespace="org.example.mapper.CustomerMapper">
 
    
</mapper>

 1.9工具类

 代码：

package org.example.util;
 
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 java.io.IOException;
import java.io.InputStream;
 
public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory = null;
    //利用静态块在初始化类时实例化sqlSessionFactory
    static {
        InputStream is= null;
        try {
            is = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
            throw new ExceptionInInitializerError(e);
        }
    }
 
    /**
     * openSession 创建一个新的SqlSession对象
     * @return SqlSession对象
     */
    public static SqlSession openSession(boolean autoCommit){
        return sqlSessionFactory.openSession(autoCommit);
    }
 
    public static SqlSession openSession(){
        return sqlSessionFactory.openSession();
    }
    /**
     * 释放一个有效的SqlSession对象
     * @param session 准备释放SqlSession对象
     */
    public static void closeSession(SqlSession session){
        if(session != null){
            session.close();
        }
    }
}

 2.1使用set标签更新记录

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

public int updateCustomerBySet(Customer customer);

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中添加

public List<Customer> findCustomerByMap(Map<String, Object> map);

 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();
    }

运行结果：