Mybatis动态SQL(十二)

CodeWang_NC

已于 2022-10-13 19:36:01 修改

阅读量191

点赞数

分类专栏： mybatis 文章标签： mybatis sql java

于 2022-10-11 11:54:24 首次发布

本文链接：https://blog.csdn.net/qq_45056135/article/details/127258646

版权

mybatis 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

介绍&步骤

视频教程: https://www.bilibili.com/video/BV1WZ4y1P7Bp?p=167
官方笔记链接：https://pan.baidu.com/s/1dnL5hwOPHPMNgb81yzQIOQ
提取码：2022
目录结构:
在这里插入图片描述

1.准备工作

1.1导入坐标

<dependencies>
    <!--mybatis 依赖-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.5</version>
    </dependency>

    <!--mysql 驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.46</version>
    </dependency>

    <!--junit 单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
    </dependency>

    <!-- 添加slf4j日志api -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.20</version>
    </dependency>
    <!-- 添加logback-classic依赖 -->
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-classic</artifactId>
        <version>1.2.3</version>
    </dependency>
    <!-- 添加logback-core依赖 -->
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-core</artifactId>
        <version>1.2.3</version>
    </dependency>
</dependencies>

1.2 User 数据库表和实体类

创建数据库表

drop table if exists user;

create table user(
	id int primary key auto_increment,
	username varchar(20),
	password varchar(20)
);

insert into user values (null, "zhangsan", "123");
insert into user values (null, "lisi", "123");
insert into user values (null, "wangwu", "123");
select * from user;

实体类

public class User {
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

1.3 resource下的配置文件

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

</mapper>

jdbc.properties文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///csdn?useSSL=false&useServerPrepStmts=true
# useSSL 忽略警告信息， userServer 开启PrepareStatement的预编译功能
jdbc.username=root
jdbc.password=123456
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

sqlMapConfig.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="jdbc.properties"></properties>

    <!--  定义别名 -->
    <typeAliases>
        <package name="com.itheima.domain"/>
    </typeAliases>
<!--    <typeAlias type="com.itheima.domain.User" alias="User"></typeAlias>-->


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--  配置文件的路径地址 就是Usermapper的地址  这里要用斜杠 不能用点 -->
        <mapper resource="com/itheima/mapper/UserMapper.xml"/>
    </mappers>

</configuration>

log4j.properties文件

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=info, stdout

2. 动态SQL if&foreach标签

2.1 UserMapper接口

public interface UserMapper {

    //条件查询
    List<User> findByCondition(User user);
    
	// 多个id查询
    List<User> findByIds(List<Integer> ids);
}

2.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.itheima.mapper.UserMapper">

    <!-- if test里面的id, username, password 就是参数类型User的属性名称  和数据库中的字段名没什么关系  -->
    <select id="findByCondition"  parameterType="User" resultType="com.itheima.domain.User">
        select * from user
        <where>
            <if test="id != 0 and id != null">
                and id = #{id}
            </if>
            <if test="username != '' and username != null">
                and username = #{username}
            </if>
            <if test="password != null and password != ''">
                and password = #{password}
            </if>
        </where>
    </select>
    <!-- foreach list表示ids的类型 如果是数组就是array -->
    <select id="findByIds" parameterType="list" resultType="com.itheima.domain.User">
        select * from user
        <where>
            <if test="list != null">
                <foreach collection="list" item="id" open="id in ("  separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
</mapper>

2.3 测试类

import com.itheima.domain.User;
import com.itheima.mapper.UserMapper;
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.Test;

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

@Test
public void test1() throws IOException {
    //创建dao层对象
    String resource = "sqlMapConfig.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession(true);
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    User user = new User();
//        user.setId(1);
//        user.setUsername("zhangsan");
//        user.setPassword("123");

    List<User> userList = mapper.findByCondition(user);

    System.out.println(userList);

}
@Test
public void testFindByIds() throws IOException {
    //创建dao层对象
    String resource = "sqlMapConfig.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession(true);
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    //模拟ids的数据
    List<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);

    List<User> userList = mapper.findByIds(ids);
    System.out.println("userList: " + userList);
}

2.4 SQL片段抽取

抽取语法：

<!--  sql语句的抽取  -->
<sql id="selectUser">select * from user</sql>

使用:

<include refid="selectUser"></include>

抽取之后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.itheima.mapper.UserMapper">

    <!--  sql语句的抽取  -->
    <sql id="selectUser">select * from user</sql>

    <!--if 标签中 test填写的直接是id 而不是#{id} if test里面的id, username, password 就是参数类型User的属性名称  和数据库中的字段名没什么关系 -->
    <select id="findByCondition" parameterType="user" resultType="com.itheima.domain.User">
        <include refid="selectUser"></include>
        <where>
            <if test="id != null and id != 0">
                 and id = #{id}
            </if>
            <if test="username != null and username != ''">
                and username = #{username}
            </if>
            <if test="password != null and password != ''">
                and password = #{password}
            </if>
        </where>

    </select>
	<!-- foreach list表示ids的类型 如果是数组就是array -->
    <select id="findByIds" parameterType="list" resultType="com.itheima.domain.User">
        select * from user
        <where>
            <if test="list != null">
                <foreach collection="list" open="id in (" close=")" separator="," item="id">
                    #{id}
                </foreach>
            </if>
        </where>

    </select>

</mapper>