MyBatis 动态SQL（二）

一、

1.创建项目

2.导入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.biem</groupId>
    <artifactId>mybatis02</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>20</maven.compiler.source>
        <maven.compiler.target>20</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

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

</project>

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

 4.mybatis属性文件db.properties

 5.log4j.xml文件

 6.创建package和文件夹

 7.用户配置文件

com.biem.pojo

 接口文件com.biem.mapper CustomerMapper

 

 8.用户配置文件

com/biem/mapper  CustomerMapper.xml

<?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="com.biem.mapper.CustomerMapper">
    <!--public int updateCustomerBySet(Customer customer);-->
    <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>
        where id = #{id}
    </update>
    <!--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>
    <!--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>


</mapper>

9.工具类com.biem.util MyBatisUtil

package com.biem.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 {
    //利用static(静态)属于类不属于对象,且全局唯一
    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.1com.biem.mapper.CustomerMapper.class中添加

  public int updateCustomerBySet(Customer customer);

2.2com/biem.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功能测试 com.biem.test.TestMybatis.java

 @Test
    public void testFindCustomerByNameAndJobs(){
        SqlSession session = MyBatisUtil.openSession();
        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
 
        Customer customer = new Customer();
        customer.setId(1);
        customer.setUsername("Jack");
        int result =mapper.updateCustomerBySet(customer);
 
    }

3.foreach元素迭代数组

3.1com.biem.mapper.CustomerMapper.class中添加

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

3.2com/biem.mapper/CustomerMapper.xml中添加

<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.3功能测试 com.biem.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();
    }

4.foreach元素迭代List

4.1com.biem.mapper.CustomerMapper.class中添加

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

4.2com/biem.mapper/CustomerMapper.xml中添加

<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>

4.3功能测试 com.biem.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();
    }

5.foreach元素迭代Map

5.1com.biem.mapper.CustomerMapper.class中添加

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

5.2com/biem.mapper/CustomerMapper.xml中添加

<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>

5.3功能测试 com.biem.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();
    }

6.运行结果