当前位置： 首页 > article >正文

MyBatis映射文件SQL深入(动态SQL)

article 2025/3/1 3:27:35

目录

一、创建项目

二、pom文件

三、前期准备

1.mybatis-config.xml

2.db.properties

3.实体类

四、if标签

1.UserMapper接口

2.UserMapper.xml

3.测试类

4.运行

五、where标签

1.UserMapper接口

2.UserMapper.xml

3.测试类

4.运行

六、foreach标签

1.场景一

(1)User类

(2)UserMapper接口

(3)UserMapper.xml

(4)测试类

(5)运行

2.场景二

(1)UserMapper接口

(2)UserMapper.xml

(3)测试类

(4)运行

七、提取公用的sql语句

1.UserMapper接口

2.UserMapper.xml

3.测试类

4.运行

一、创建项目

二、pom文件

<?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.qcby</groupId>
    <artifactId>mybatis2</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <!--mybatis核心-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <!--junit测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

</project>

三、前期准备

1.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"></properties>

    <!--起别名-->
    <typeAliases>
        <package name="com.qcby.pojo"/>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <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>
    <mappers>
        <package name="com.qcby.mapper"/>
        <!--        <mapper resource="org/mybatis/example/BlogMapper.xml"/>-->
        <!-- <mapper resource="mapper/UserMapper.xml"></mapper> -->
    </mappers>
</configuration>

2.db.properties

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis_demo2
mysql.username=root
mysql.password=2020

3.实体类

User类

package com.qcby.pojo;

import java.util.Date;

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private Integer age;
    private String address;
    private Double money;

    public User() {
    }

    public User(Integer id, String username, Date birthday, String sex, Integer age, String address, Double money) {
        this.id = id;
        this.username = username;
        this.birthday = birthday;
        this.sex = sex;
        this.age = age;
        this.address = address;
        this.money = money;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                ", money=" + money +
                '}';
    }
}

四、if标签

1.UserMapper接口

package com.qcby.mapper;

import com.qcby.pojo.User;

import java.util.List;

public interface UserMapper {
    /*
    * 条件查询--If
    * */
    public List<User> findByIf(User user);  
}

2.UserMapper.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.qcby.mapper.UserMapper"> <!--对谁进行操作就写谁-->

    <!--条件查询if：public List<User> findByIf(User user);-->
    <select id="findByIf" parameterType="user" resultType="user">
        select * from user where 1=1
        <if test="username!=null and username!=''">
            and username like #{username}
        </if>
        <if test="birthday!=null">
            and birthday=#{birthday}
        </if>
        <if test="sex!=null and sex!=''">
            and sex=#{sex}
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
        <if test="address!=null and address!=''">
            and address=#{address}
        </if>
        <if test="money!=null">
            and money=#{money}
        </if>
    </select>
</mapper>

3.测试类

package com.qcby.test;

import com.qcby.mapper.UserMapper;
import com.qcby.pojo.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class UserTest {
    private InputStream inputStream;
    private SqlSession sqlSession;
    private UserMapper userMapper;
    @Before
    public void init() throws IOException {
        //加载配置文件
        inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        //创建工厂对象
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        //创建session对象
        sqlSession=sqlSessionFactory.openSession();
        //获取到代理对象
        userMapper=sqlSession.getMapper(UserMapper.class);
    }

    /*
    * 条件查询--if
    * */
    @Test
    public void findByIfTest(){
        User user=new User();
        user.setUsername("%张%");
        user.setSex("男");

        List<User> users=userMapper.findByWhere(user);
        for (User user1:users){
            System.out.println(user1);
        }
    }


    @After
    public void destory() throws IOException {
        inputStream.close();
        sqlSession.close();
    }

}

4.运行

五、where标签

1.UserMapper接口

/*
 * 条件查询--where
 * */
public List<User> findByWhere(User user);

2.UserMapper.xml

<!--条件查询where：public List<User> findByWhere(User user);-->
<select id="findByWhere" parameterType="user" resultType="user">
    select * from user
    <where>
        <if test="username!=null and username!=''">
            and username like #{username}
        </if>
        <if test="birthday!=null">
            and birthday=#{birthday}
        </if>
        <if test="sex!=null and sex!=''">
            and sex=#{sex}
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
        <if test="address!=null and address!=''">
            and address=#{address}
        </if>
        <if test="money!=null">
            and money=#{money}
        </if>
    </where>
</select>

3.测试类

/*
* 条件查询--where
* */
@Test
public void findByWhereTest(){
    User user=new User();
    user.setAddress("石家庄");
    user.setMoney(100.0);

    List<User> users=userMapper.findByWhere(user);
    for (User user1:users){
        System.out.println(user1);
    }
}

4.运行

六、foreach标签

1.场景一

select * from user where id=1 or id=2 or id=3

(1)User类

在User类中添加属性

package com.qcby.pojo;

import java.util.Date;
import java.util.List;

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private Integer age;
    private String address;
    private Double money;

    private List<Integer> ids;

    public User() {
    }

    public User(Integer id, String username, Date birthday, String sex, Integer age, String address, Double money, List<Integer> ids) {
        this.id = id;
        this.username = username;
        this.birthday = birthday;
        this.sex = sex;
        this.age = age;
        this.address = address;
        this.money = money;
        this.ids = ids;
    }

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                ", money=" + money +
                ", ids=" + ids +
                '}';
    }
}

(2)UserMapper接口

/*
 * 条件查询--foreach--场景一(or)
 * */
public List<User> findByIds(User user);

(3)UserMapper.xml

<!--条件查询where：public List<User> findByIds(User user);-->
<!--select * from user where id=1 or id=2 or id=3-->
<select id="findByIds" parameterType="user" resultType="user">
    select * from user
    <where>
        <foreach collection="ids" item="id" open="id = " separator="or id = ">
            #{id}
        </foreach>
    </where>
</select>

(4)测试类

/*
 * 条件查询--foreach--场景一(or)
 * */
@Test
public void findByIds(){
    User user=new User();
    List<Integer> ids=new ArrayList<>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    user.setIds(ids);
    System.out.println(ids);

    List<User> users=userMapper.findByIds(user);
    for (User user1:users){
        System.out.println(user1);
    }
}

(5)运行

2.场景二

select * from user where id in (1,2,3)

(1)UserMapper接口

/*
*条件查询--foreach--场景二(in)
* */
public List<User> findByIds1(User user);

(2)UserMapper.xml

<!--条件查询where：public List<User> findByIds1(User user);-->
<!--select * from user where id in (1,2,3)-->
<select id="findByIds1" parameterType="user" resultType="user">
    select * from user
    <where>
        <foreach collection="ids" item="id" open="id in ( " separator="," close=")">
            #{id}
        </foreach>
    </where>
</select>

(3)测试类

/*
 * 条件查询--foreach--场景二(in)
 * */
@Test
public void findByIds1(){
    User user=new User();
    List<Integer> ids=new ArrayList<>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    user.setIds(ids);
    System.out.println(ids);

    List<User> users=userMapper.findByIds1(user);
    for (User user1:users){
        System.out.println(user1);
    }
}

(4)运行

七、提取公用的sql语句

1.UserMapper接口

/*
* 查询所有---用公用SQL
* */
public List<User> findAll();

2.UserMapper.xml

<!--提取公用的sql-->
<sql id="findAllSql">
    select * from user
</sql>
<!--查询所有,用公用SQL:public List<User> findAll();-->
<select id="findAll" resultType="user">
    <include refid="findAllSql"/>
</select>

3.测试类

/*
 * 查询所有---用公用SQL
 * */
@Test
public void findAllTest(){
    List<User> users=userMapper.findAll();
    for (User user:users){
        System.out.println(user);
    }
}

4.运行


http://www.kler.cn/a/506583.html

相关文章：

  • Windows部署NVM并下载多版本Node.js的方法（含删除原有Node的方法）
  • K8S集群常用命令
  • 使用 MailKit 从 Outlook.Office365.com 下载邮件附件的教程
  • 以太坊（概念与原理）
  • 使用防抖与节流优化 Vue 中的异步函数调用
  • 使用 Golang 编译 Linux 可运行文件
  • 面试之《js的垃圾回收机制》
  • golang运维开发-gopsutil(1)
  • 【Leetcode 每日一题 - 扩展】3097. 或值至少为 K 的最短子数组 II
  • 最佳股票买卖时机问题
  • Redis Cluster和Sentinel模式，如何选择？
  • 【HarmonyOS NAPI 深度探索6】使用 N-API 创建第一个 Hello World 原生模块
  • 前端开发：盒子模型、块元素
  • 【机器学习实战入门项目】使用Python创建自己的表情符号
  • 鸿蒙UI开发——基于onTouch事件实现表情选择胶囊
  • 如何学习网络安全？有哪些小窍门？
  • 计算机网络（五）——传输层
  • 【Linux入门】一、权限的理解
  • 使用vnstat监控网络流量和带宽占用
  • ＜OS 有关＞Ubuntu 24 安装 openssh-server, tailscale+ssh 慢增加