且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事
您所在的位置：首页 > Java：MyBatis动态SQL实践

Java：MyBatis动态SQL实践

更新时间：2022-08-17 14:49:40

项目结构

$ tree
.
├── README.md
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── mouday
    │   │           ├── mapper
    │   │           │   ├── PersonMapper.java
    │   │           │   └── PersonMapper.xml
    │   │           ├── pojo
    │   │           │   └── Person.java
    │   │           └── util
    │   │               └── MyBatisUtil.java
    │   └── resources
    │       ├── db.properties
    │       ├── mybatis-config.xml
    │       └── sql
    │           └── person.sql
    └── test
        └── java
            └── com
                └── mouday
                    └── PersonTest.java

依赖 pom.xml

<?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.mouday</groupId>
    <artifactId>mybatis-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <build>
        <resources>
            <!--编译src/main/java目录下的xml文件-->
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

数据库配置

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/data
username=root
password=123456

MyBatis配置 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" />

    <!-- 打印sql日志 -->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!-- 指定整个包下的类都是别名 -->
    <typeAliases>
        <package name="com.mouday.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.mouday.mapper.PersonMapper"/>
    </mappers>
</configuration>

建表插入数据 person.sql

-- 建表
create table person(
    id int not null primary key auto_increment,
    name varchar(20),
    age int
);

-- 插入数据
insert into person(name, age) values('刘备', 25);
insert into person(name, age) values('关羽', 24);
insert into person(name, age) values('张飞', 23);
insert into person(name, age) values('曹操', 25);
insert into person(name, age) values('许褚', 24);
insert into person(name, age) values('孙权', 25);
insert into person(name, age) values('周瑜', 24);

工具类 MyBatisUtil.java

package com.mouday.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 factory = null;

    // 使用static静态代码块，随着类的加载而加载，只执行一次
    static {
        try {
            String resource = "mybatis-config.xml";
            // 加载MyBatis的主配置文件
            InputStream inputStream = Resources.getResourceAsStream(resource);
            // 通过构建器（SqlSessionFactoryBuilder）构建一个SqlSessionFactory工厂对象
            factory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() throws IOException {
        return factory.openSession();
    }
}

实体Person.java

package com.mouday.pojo;

import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
public class Person {
    private Integer id;
    private String name;
    private Integer age;
}

mapper接口 PersonMapper.java

package com.mouday.mapper;

import com.mouday.pojo.Person;

import java.util.List;

public interface PersonMapper {
    List<Person> selectAll();

    /**
     * 根据输入的信息进行条件检索
     * 1. 当只输入用户名时， 使用用户名进行 【模糊检索】
     * 2. 当只输入年龄时， 使用性别进行 【完全匹配】
     * 3. 当用户名和年龄都存在时， 用这两个条件进行查询匹配的用
     */
    List<Person> selectByPersonSelective(Person person);

    /**
     * 更新非空属性
     */
    int updateByPrimaryKeySelective(Person person);

    /**
     * 插入非空字段
     */
    int insertSelective(Person person);

    /**
     * 当 name 没有值时， 使用 name 进行查询
     * 否则使用 id 进行查询
     */
    List<Person> selectByNameOrId(Person person);

}

mapper映射文件 PersonMapper.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 namespace="com.mouday.mapper.PersonMapper">

    <sql id="Base_Column_List">
        id, name, age
    </sql>

    <select id="selectAll" resultType="Person">
    select
    <include refid="Base_Column_List"/>
    from person
    </select>


    <select id="selectByPersonSelective" resultType="Person" parameterType="Person">
        select
        <include refid="Base_Column_List" />
        from person
        <where>
            <if test="name != null and name !=''">
                and name like concat('%', #{name}, '%')
            </if>
            <if test="age != null">
                and age=#{age}
            </if>
        </where>
    </select>


    <update id="updateByPrimaryKeySelective" parameterType="Person">
    update person
    <set>
        <if test="name != null">
            `name` = #{name,jdbcType=VARCHAR},
        </if>

        <if test="age != null">
            `age` = #{age,jdbcType=INTEGER},
        </if>

    </set>
    where id = #{id,jdbcType=INTEGER}
    </update>


    <insert id="insertSelective" parameterType="Person">
        insert into person
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="name != null">
                `name`,
            </if>
            <if test="age != null">
                age,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=INTEGER},
            </if>
            <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
            <if test="age != null">
                #{age,jdbcType=INTEGER},
            </if>

        </trim>
    </insert>



    <select id="selectByNameOrId" resultType="Person" parameterType="Person">
        select
        <include refid="Base_Column_List" />
        from person
        where 1=1
        <choose>
            <when test="id != null">
                and id=#{id}
            </when>

            <otherwise>
                and name=#{name}
            </otherwise>
        </choose>
    </select>
</mapper>

测试文件PersonTest.java

package com.mouday;

import com.mouday.mapper.PersonMapper;
import com.mouday.pojo.Person;
import com.mouday.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;

public class PersonTest {

    private SqlSession session;
    private PersonMapper mapper;

    @Before
    public void init() throws IOException {
        this.session = MyBatisUtil.getSqlSession();
        this.mapper = this.session.getMapper(PersonMapper.class);
    }

    @After
    public void destroy() {
        this.session.close();
    }

    @Test
    public void testSelect() {
        System.out.println(mapper.selectAll());
    }

    /**
     * 选择数据
     */
    @Test
    public void testSelectByStudentSelective() {

        Person person = new Person();
        System.out.println(mapper.selectByPersonSelective(person));
        // select id, name, age from person

        person.setName("操");
        System.out.println(mapper.selectByPersonSelective(person));
        // select id, name, age from person WHERE name like concat('%', ?, '%')

        person.setAge(25);
        System.out.println(mapper.selectByPersonSelective(person));
        // select id, name, age from person WHERE name like concat('%', ?, '%') and age=?
    }

    /**
     * 更新数据
     */
    @Test
    public void testUpdateByPrimaryKeySelective() {
        Person person = new Person();
        person.setId(1);
        person.setAge(26);

        mapper.updateByPrimaryKeySelective(person);
        // update person SET `age` = ? where id = ?
        session.commit();

        person.setName("刘禅");
        mapper.updateByPrimaryKeySelective(person);
        session.commit();
        // update person SET `name` = ?, `age` = ? where id = ?
    }

    /**
     * 插入数据
     */
    @Test
    public void testInsertSelective() {
        Person person = new Person();
        person.setName("司马懿");


        mapper.insertSelective(person);
        // insert into person ( `name` ) values ( ? )
        session.commit();

        person.setAge(26);
        mapper.insertSelective(person);
        // insert into person ( `name`, age ) values ( ?, ? )

        session.commit();
    }

    /**
     * 选择查询
     */
    @Test
    public void testSelectByNameOrId() {
        Person person = new Person();
        person.setName("司马懿");

        mapper.selectByNameOrId(person);
        // select id, name, age from person where 1=1 and name=?


        person.setId(1);
        mapper.selectByNameOrId(person);
        // select id, name, age from person where 1=1 and id=?
    }
}

Where

set 和 where 其实都是 trim 标签的一种类型

where 等价于

<trim prefix="where" prefixOverrides="AND |OR">
</trim>

表示当 trim 中含有内容时， 添加 where， 且第一个为 and 或 or 时， 会将其去掉。

而如果没有内容， 则不添加 where。

set 等价于

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

表示当 trim 中含有内容时， 添加 set， 且最后的内容为 , 时， 会将其去掉。而没有内容， 不添加 set

trim 的几个属性

prefix: 当 trim 元素包含有内容时， 增加 prefix 所指定的前缀
prefixOverrides: 当 trim 元素包含有内容时， 去除 prefixOverrides 指定的 前缀
suffix: 当 trim 元素包含有内容时， 增加 suffix 所指定的后缀
suffixOverrides：当 trim 元素包含有内容时， 去除 suffixOverrides 指定的后缀

参考

  1. 你用过Mybatis的动态SQL后，就知道写SQL有多爽了！
  2. https://mybatis.org/mybatis-3/zh/dynamic-sql.html


上一篇 : ：may have been in progress in another thread when fork() was called.下一篇 : SpringBoot学习笔记-6：第六章 Spring Boot 与数据访问

相关阅读

推荐文章

网站首页网站地图Copyright © 2021-2022 且购网

免责声明：本站文章部分来源于互联网,如有侵权请联系站长,我们将在72小时内删除。