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

于 2025-01-16 20:23:47 发布
阅读量555 收藏 3
点赞数 3
分类专栏： mybatis 文章标签： mybatis sql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/2201_76081438/article/details/145191117
版权

目录

一、创建项目

二、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.运行

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值