MyBatis 动态SQL

一、环境搭建

1.1创建项目

1.2导入依赖pom.xml

<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

<?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=root

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用户配置文件

实体类文件CustomerMapper

 

 代码如下：

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

 接口文件：

 

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

 1.8用户配置文件

 

<?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">
 
    
</mapper>

1.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 {
 
    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);
        }
    }
 
    
    public static SqlSession openSession(boolean autoCommit){
        return sqlSessionFactory.openSession(autoCommit);
    }
 
    public static SqlSession openSession(){
        return sqlSessionFactory.openSession();
    }
   
    public static void closeSession(SqlSession session){
        if(session != null){
            session.close();
        }
    }
 
 
}

二、使用set标签更新记录

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

 

  public int updateCustomerBySet(Customer customer);

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

 2.4运行结果

三、foreach元素迭代数组

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

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

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

foreach元素迭代List

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

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

3.5 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.6功能测试 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();
    }

foreach元素迭代Map 

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

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

3.8org/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.9功能测试 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();
    }

运行结果：