MyBatis implements dynamic SQL in IDEA (1)

1. Build MyBatis environment

1. Create a project project

2. Create the data table t_customer

3. Introduce dependencies in pom.xml

4. Introduce resources

mybatis core configuration file mybatis-config.xml

mybatis property file db.properties

log4j.xml file

6. User Profiles

Create entity class 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;
}

Create an interface mapper

package com.biem.mapper;

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

Create tool class 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 {
    //Use static (static) to belong to the class and not to the object, and it is globally unique
    private static SqlSessionFactory sqlSessionFactory = null;
    //Use the static block to instantiate sqlSessionFactory when initializing the class
    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 creates a new SqlSession object
     * @return SqlSession object
     */
    public static SqlSession openSession(boolean autoCommit){
        return sqlSessionFactory. openSession(autoCommit);
    }

    public static SqlSession openSession(){
        return sqlSessionFactory. openSession();
    }
    /**
     * Release a valid SqlSession object
     * @param session is ready to release the SqlSession object
     */
    public static void closeSession(SqlSession session){
        if(session != null){
            session. close();
        }
    }
}

The user configuration file creates the com/biem/mapper folder under resources and creates UserMapper.xml

2. tag (conditional judgment)

if tag syntax structure

<if test="judgment condition">
    SQL statement
</if>

Example of if tag implementation

1. Add methods in the interface com.biem.mapper.UserMapper.class

Add sql statement in the mapping file com/biem/mapper/CustomerMapper.xml

function test

Create a test class com.biem.TestCustomer.java in src/test/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(){
        // Obtain the SqlSession object through the tool class
        SqlSession session = MybatisUtil. openSession();
        // Create a Customer object and encapsulate the conditions that need to be combined to query
        Customer customer = new Customer();

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

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

        // Close SqlSession
        session. close();
    }

    @Test
    public void testFindCustomerByNameAndJobs(){
        // Obtain the SqlSession object through the tool class
        SqlSession session = MybatisUtil. openSession();
        // Create a Customer object and encapsulate the conditions that need to be combined to query
        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);

        // Close SqlSession
        session. close();
    }
}

3. tags

Grammatical structures

<choose>
    <when test="judgment condition 1">
        SQL statement 1
    </when>
    <when test="judgment condition 2">
        SQL statement 2
    </when>
    <when test="judgment condition 3">
        SQL statement 3
    </when>
    <otherwise>
        SQL statement 4
    </otherwise>
</choose>

Add method in interface com.biem.mapper.UserMapper.class

Add sql statement in the mapping file com/biem/mapper/CustomerMapper.xml

 <!-- public List<Customer> findCustomerByCondition(Customer customer);-->
    <select id="findCustomerByCondition" parameterType="customer" resultType="customer">
        select * from t_customer where 1=1
        <!--Condition judgment-->
        <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>

Create class com.biem.test.TestUser.java in src/test/java

Four. label (conditional judgment)

Grammatical structures

<where>
    <if test="judgment condition">
        AND/OR...
    </if>
</where>

Add in com.biem.mapper.CustomerMapper.class

 public List<Customer> findCustomerByIf(Customer customer);

    public List<Customer> findCustomerByWhere(Customer customer);

Create class com.biem.test.TestCustomer.java in src/test/java, the content is as follows

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(){
        // Obtain the SqlSession object through the tool class
        SqlSession session = MybatisUtil. openSession();
        // Create a Customer object and encapsulate the conditions that need to be combined to query
        Customer customer = new Customer();
        customer.setJobs("teacher");

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

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

        // Close SqlSession
        session. close();
    }

    @Test
    public void testFindCustomerByWhere(){
        // Obtain the SqlSession object through the tool class
        SqlSession session = MybatisUtil. openSession();
        // Create a Customer object and encapsulate the conditions that need to be combined to query
        Customer customer = new Customer();
        customer.setJobs("teacher");

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

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

        // Close SqlSession
        session. close();
    }

}

5. tag (remove redundant keywords)

Add methods in the interface class com.biem.mapper.CustomerMapper.class

public List<Customer> findCustomerByTrim(Customer customer);

Add in

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>

Functional test com.biem.test.TestMybatis.java

 @Test
    public void testFindCustomerByTrim(){
        // Obtain the SqlSession object through the tool class
        SqlSession session = MybatisUtil. openSession();
        // Create a Customer object and encapsulate the conditions that need to be combined to query
        Customer customer = new Customer();
        customer.setJobs("teacher");

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

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

        // Close SqlSession
        session. close();
    }