IDEA中 MyBatis实现动态SQL（一）

一.进行MyBatis环境搭建

1.创建project工程

2.创建数据表t_customer

 3.在pom.xml中引入依赖

 4.引入资源

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

 mybatis属性文件db.properties

 log4j.xml文件

 6.用户配置文件

创建实体类pojo

 

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

创建接口mapper

 

package com.biem.mapper;

/**
 * ClassName: CustomerMapper
 * Package: com.biem.mapper
 * Description:
 *
 * @Create 2023/4/5 22:19
 * @Version 1.0
 */
public interface CustomerMapper {
}

创建工具类util

 

package com.biem.utils;

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;

/**
 * ClassName: MybatisUtil
 * Package: com.biem.utils
 * Description:
 *
 * @Create 2023/4/5 22:23
 * @Version 1.0
 */
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();
        }
    }
}

用户配置文件在resources下创建com/biem/mapper文件夹并创建UserMapper.xml

 二.<if>标签（条件判断）

if标签语法结构

<if test="判断条件">
    SQL语句
</if>

if标签实施案例

1.在接口com.biem.mapper.UserMapper.class中添加方法

 在映射文件com/biem/mapper/CustomerMapper.xml中添加sql语句

 

 功能测试

在src/test/java中创建测试类com.biem.TestCustomer.java

 

package com.biem.test;

import com.biem.mapper.CustomerMapper;
import com.biem.pojo.Customer;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.biem.utils.MybatisUtil;

import java.util.List;

/**
 * ClassName: TestCustomer
 * Package: com.biem.test
 * Description:
 *
 * @Create 2023/4/5 22:32
 * @Version 1.0
 */
public class TestCustomer {

    @Test
    public void testFindAll(){
        // 通过工具类获取SqlSession对象
        SqlSession session = MybatisUtil.openSession();
        // 创建Customer对象，封装需要组合查询的条件
        Customer customer = new Customer();

        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByNameAndJobs(customer);

        System.out.println("customers = " + customers);

        // 关闭SqlSession
        session.close();
    }

    @Test
    public void testFindCustomerByNameAndJobs(){
        // 通过工具类获取SqlSession对象
        SqlSession session = MybatisUtil.openSession();
        // 创建Customer对象，封装需要组合查询的条件
        Customer customer = new Customer();
        customer.setUsername("jack");
        customer.setJobs("teacher");

        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByNameAndJobs(customer);

        System.out.println("customers = " + customers);

        // 关闭SqlSession
        session.close();
    }
}

三.<choose><when><otherwise>标签

语法结构

<choose>
    <when test="判断条件1">
        SQL语句1
    </when >
    <when test="判断条件2">
        SQL语句2
    </when >
    <when test="判断条件3">
        SQL语句3
    </when >
    <otherwise>
        SQL语句4
    </otherwise>
</choose>

在接口com.biem.mapper.UserMapper.class中添加方法

 

在映射文件com/biem/mapper/CustomerMapper.xml中添加sql语句

 

    <!-- public List<Customer> findCustomerByCondition(Customer customer);-->
    <select id="findCustomerByCondition" parameterType="customer" resultType="customer">
        select * from t_customer where 1=1
        <!--条件判断-->
        <choose>
            <when test="username!=null and username!=''">
                and username like concat('%',#{username},'%')
            </when>
            <when test="jobs!=null and jobs!=''">
                and jobs=#{jobs}
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
    </select>

 

在src/test/java中创建类com.biem.test.TestUser.java

四.<where>标签（条件判断）

语法结构

<where>
    <if test="判断条件">
        AND/OR ...
    </if>
</where>

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

    public List<Customer> findCustomerByIf(Customer customer);

    public List<Customer> findCustomerByWhere(Customer customer);

 在src/test/java中创建类com.biem.test.TestCustomer.java，内容如下

package com.biem.test;

import com.biem.mapper.CustomerMapper;
import com.biem.pojo.Customer;
import com.biem.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * ClassName: TestCustomer
 * Package: com.biem.test
 * Description:
 *
 * @Create 2023/4/5 22:32
 * @Version 1.0
 */
public class TestCustomer {


    @Test
    public void testFindCustomerByIf(){
        // 通过工具类获取SqlSession对象
        SqlSession session = MybatisUtil.openSession();
        // 创建Customer对象，封装需要组合查询的条件
        Customer customer = new Customer();
        customer.setJobs("teacher");

        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByIf(customer);

        System.out.println("customers = " + customers);

        // 关闭SqlSession
        session.close();
    }

    @Test
    public void testFindCustomerByWhere(){
        // 通过工具类获取SqlSession对象
        SqlSession session = MybatisUtil.openSession();
        // 创建Customer对象，封装需要组合查询的条件
        Customer customer = new Customer();
        customer.setJobs("teacher");

        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByWhere(customer);

        System.out.println("customers = " + customers);

        // 关闭SqlSession
        session.close();
    }

}

五.<trim>标签（去除多余关键字）

 在接口类com.biem.mapper.CustomerMapper.class中添加方法

public List<Customer> findCustomerByTrim(Customer customer);

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

    <!--public List<Customer> findCustomerByTrim(Customer customer);-->
    <select id="findCustomerByTrim" parameterType="customer" resultType="customer">
        select * from t_customer
        <trim prefix="where" prefixOverrides="and">
            <if test="username !=null and username != ''">
                and username like concat('%', #{username}, '%')
            </if>
            <if test="jobs !=null and jobs != ''">
                and jobs=#{jobs}
            </if>
        </trim>

    </select>

功能测试 com.biem.test.TestMybatis.java

    @Test
    public void testFindCustomerByTrim(){
        // 通过工具类获取SqlSession对象
        SqlSession session = MybatisUtil.openSession();
        // 创建Customer对象，封装需要组合查询的条件
        Customer customer = new Customer();
        customer.setJobs("teacher");

        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByTrim(customer);

        System.out.println("customers = " + customers);

        // 关闭SqlSession
        session.close();
    }