MyBatis动态SQL(一)

目录

目录

一、<if>标签

1.1if语法结构

1.2数据库端建表

1.3创建项目

1.4导入依赖(pom.xml) 

1.5创建package和文件夹

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

1.7mybatis属性文件jdbc.properties

 1.8log4j.xml文件

 1.9创建实体类

1.10mybatis接口类

1.11用户配置文件

1.12mybatis工具类

1.13 If标签功能测试

 choose-when-otherwise标签

where标签功能

<trim>标签 


一、<if>标签

1.1if语法结构

1.2数据库端建表

1.3创建项目

1.4导入依赖(pom.xml) 

1.5创建package和文件夹

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

1.7mybatis属性文件jdbc.properties

 1.8log4j.xml文件

 1.9创建实体类

1.10mybatis接口类

1.11用户配置文件

1.12mybatis工具类

1.13 If标签功能测试

 choose-when-otherwise标签-完整案例

where标签功能

<trim>标签 


一、<if>标签

1.1if语法结构

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

1.2数据库端建表

 

具体内容:

use mybatis;
# 创建一个名称为t_customer的表
CREATE TABLE t_customer (
    id int(32) PRIMARY KEY AUTO_INCREMENT,
    username varchar(50),
    jobs varchar(50),
    phone varchar(16)
);
# 插入3条数据
INSERT INTO t_customer VALUES ('1', 'joy', 'teacher', '13733333333');
INSERT INTO t_customer VALUES ('2', 'jack', 'teacher', '13522222222');
INSERT INTO t_customer VALUES ('3', 'tom', 'worker', '15111111111');

1.3创建项目

1.4导入依赖(pom.xml) 

具体内容:

<dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </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.12</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.18</version>
        </dependency>
    </dependencies>

1.5创建package和文件夹

1.6mybatis核心配置文件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.7mybatis属性文件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.8log4j.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.9创建实体类

具体内容:

package org.example.pojo;

import lombok.*;

@Setter
@Getter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Customer {
    private Integer id;
    private String username;
    private String jobs;
    private String phone;
}

1.10mybatis接口类

具体内容:

package org.example.mapper;

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

此处namespac要与自己的对应 

1.11用户配置文件

 具体内容:

<?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 namespace="org.example.mapper.CustomerMapper">
    <!-- namespace要和mapper接口的全类名保持一致,例如com.biem.mybatis.mapper.xxxMapper -->

    <!-- sql语句要和接口的方法名保持一致 -->

</mapper>

其中mapper namespace="com.example.mapper.CustomerMappe需要根据自己的修改

1.12mybatis工具类

具体内容:

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;

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


1.13 If标签功能测试

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

public List<Customer> findCustomerByNameAndJobs(Customer customer);

 org/example/mapper/CustomerMapper.xml中添加

    <select id="findCustomerByNameAndJobs" parameterType="customer" resultType="customer">
        select * from t_customer where 1=1
        <if test="username !=null and username != ''">
            and username like concat('%', #{username}, '%')
        </if>
        <if test="jobs !=null and jobs != ''">
            and jobs=#{jobs}
        </if>
    </select>

 在src/test/java中创建类org.example.test.TestCustomer.java

具体内容:

package org.example.test;

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

import java.util.List;

public class CustomTest {

/**
 * ClassName: TestCustomer
 * Package: com.biem.test
 * Description:
 *
 * @Create 2023/4/5 22:32
 * @Version 1.0
 */
    @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标签

CustomerMapper.class中添加

public List<Customer> findCustomerByCondition(Customer customer);

在CustomerMapper.xml中添加

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

test中添加

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

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

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

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

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

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

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

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

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

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

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

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

where标签功能

CustomerMapper.class中添加

    public List<Customer> findCustomerByIf(Customer customer);

    public List<Customer> findCustomerByWhere(Customer customer);

CustomerMapper.xml中添加 

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

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

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

CustomerMapper.class中添加 

public List<Customer> findCustomerByTrim(Customer customer);

CustomerMapper.xml中添加 

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

test文件中添加

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

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值