实验5 Mybatis之动态sql

一：实验目的

1、了解Mybatis基础知识；

2、理解Mybatis工作原理

3、掌握Mybatis基础程序编写及核心配置；

4、掌握Mybatis动态sql的构建方法。

二：实验内容

Mybatis数据库中的car表结构如下：

car表

属性	数据类型	长度	是否主键	可空性	备注
carNumber	char	10	主键	否	车牌号
brand	varchar	30			品牌
owner	char	10		否	车主
purchaseDate	date				购置日期
telephone	varchar	20			车主电话

在Mysql数据库创建car表：

CREATE TABLE car(
    carNumber VARCHAR(20) PRIMARY KEY AUTO_INCREMENT,
    brand VARCHAR(255),
    OWNER VARCHAR(40),
    purchaseDate DATE,
    telephone VARCHAR(20)
);

在pojo包下创建Car类：

package pojo;

import java.util.Date;

public class Car {

    private String carNumber;
    private String brand;
    private String owner;
    private String purchaseDate;
    private String telephone;
    private String startTime;
    private String endTime;

    @Override
    public String toString() {
        return "Car{" +
                "carNumber='" + carNumber + '\'' +
                ", brand='" + brand + '\'' +
                ", owner='" + owner + '\'' +
                ", purchaseDate=" + purchaseDate +
                ", telephone='" + telephone + '\'' +
                '}';
    }

    public String getCarNumber() {
        return carNumber;
    }

    public void setCarNumber(String carNumber) {
        this.carNumber = carNumber;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getOwner() {
        return owner;
    }

    public void setOwner(String owner) {
        this.owner = owner;
    }

    public String getPurchaseDate() {
        return purchaseDate;
    }

    public void setPurchaseDate(String purchaseDate) {
        this.purchaseDate = purchaseDate;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public String getStartTime() {
        return startTime;
    }

    public void setStartTime(String startTime) {
        this.startTime = startTime;
    }

    public String getEndTime() {
        return endTime;
    }

    public void setEndTime(String endTime) {
        this.endTime = endTime;
    }
}

在pojo包下创建Customer类：

package pojo;

import org.apache.ibatis.session.RowBounds;

public class Customer extends RowBounds {
    private int id;
    private String username;
    private String jobs;
    private String phone;

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", jobs='" + jobs + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }

    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 getJobs() {
        return jobs;
    }

    public void setJobs(String jobs) {
        this.jobs = jobs;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

在utils包下创建MyBatisUtils工具类：

package utils;

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.Reader;

public class MyBatisUtils {

    private static SqlSessionFactory sqlSessionFactory = null;
    static {
        //创建sqlSessionFactory
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSession(){
        return sqlSessionFactory.openSession();
    }


}

在resources创建以下xml文件：

CarMapper.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:名称空间，由于映射文件可能有多个，为了防止crud语句的唯一标识
-->

<mapper namespace="CarMapper">
    <!--
        select:查询的statement(声明)，用来编写查询语句
        id:语句的唯一标识
        resultType:配置返回的结果集类型
        parameterType:传递的参数类型，可以省略
    -->

    <!--根据所给条件查询-->
    <select id="selectByCondition"
            resultType="car">
        select * from car
        <!--collection表示key,item表示value-->
        <where>
            <if test="carNumber!=null and carNumber.size()>0">
                or carNumber in
                <foreach collection="carNumber" item="roleMap" open="(" close=")" separator=",">
                    #{roleMap}
                </foreach>
            </if>
            <if test="brand!=null">
                or brand in
                <foreach collection="brand" item="roleMap" open="(" close=")" separator=",">
                    #{roleMap}
                </foreach>
            </if>
            <if test="owner!=null">
                or owner in
                <foreach collection="owner" item="roleMap" open="(" close=")" separator=",">
                    #{roleMap}
                </foreach>
            </if>
            <if test="startTime != null and endTime != null">
                or purchaseDate between #{startTime} and #{endTime}
            </if>
            <if test="telephone!=null">
                or telephone  in
                <foreach collection="telephone" item="roleMap" open="(" close=")" separator=",">
                    #{roleMap}
                </foreach>
            </if>
        </where>


    </select>

</mapper>

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

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

    <!--别名映射-->
    <typeAliases>
        <package name="pojo"/>
    </typeAliases>
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <!--数据库连接相关配置，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>

    <mappers>
        <mapper resource="CarMapper.xml"></mapper>
    </mappers>

</configuration>

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.itheima</groupId>
    <artifactId>mybatistest</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <!--   	        <scope>test</scope>-->
            <scope>compile</scope>
        </dependency>
    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>7</source>
                    <target>7</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

  请完成以下操作：

1、假设在系统的查询页面中，有5个文本框分别对应car表的5个字段。程序接收到用户在文本框中输入的查询条件后使用动态sql技术构造查询语句：

说明：①各文本框允许不填，全部不填时查询所有记录；

      ②输入日期的文本框输入格式为：yyyy-mm-dd~yyyy-mm-dd。在查询时需要解析为：purchaseDate between yyyy-mm-dd and yyyy-mm-dd；

# 最后在test/java目录下创建测试类MybatisTest:

import org.apache.ibatis.session.SqlSession;
import pojo.Car;
import pojo.Customer;
import utils.MyBatisUtils;

import java.util.*;

public class MyBatisTest {

    public static void main(String[] args) {

        //获取sqlSession核心对象
        SqlSession session = MyBatisUtils.getSession();

        Scanner sc = new Scanner(System.in);

        //准备查询的条件
        Map<String, Object> map = new HashMap<>();

        List<String> carNumbers = new ArrayList<>();
        List<String> brands = new ArrayList<>();
        List<String> owners = new ArrayList<>();
        List<String> purchaseDates = new ArrayList<>();
        List<String> telephones = new ArrayList<>();


        //准备查询的条件
        while (true) {
            System.out.println("请输入查询条件（1.车牌号、2.品牌、3.车主、4.只根据购买日期、5.电话、6.开始查询）：");
            String str = sc.next();
            if(str.equals("6")){
                System.out.println("查询结果如下：");
                break;
            }
            switch (str) {
                case "1":
                    System.out.println("请输入需要查询的车牌号（可添加多个,输入F退出）：");
                    while (true) {
                        String s = sc.next();
                        if (s.equals("F")) {
                            System.out.println("成功退出,可以继续选择查询条件！");
                            break;
                        }
                        carNumbers.add(s);
                    }
                    map.put("carNumber", carNumbers);
                    continue;
                case "2":
                    System.out.println("请输入需要查询的品牌（可添加多个,输入F退出）：");
                    while (true) {
                        String s = sc.next();
                        if (s.equals("F")) {
                            System.out.println("成功退出,可以继续选择查询条件！");
                            break;
                        }
                        brands.add(s);
                    }
                    map.put("brand", brands);
                    continue;
                case "3":
                    System.out.println("请输入需要查询的车主（可添加多个,输入F退出）：");
                    while (true) {
                        String s = sc.next();
                        if (s.equals("F")) {
                            System.out.println("成功退出,可以继续选择查询条件！");
                            break;
                        }
                        owners.add(s);
                    }
                    map.put("owner", owners);
                    continue;
                case "4":
                    System.out.println("请输入需要查询的购买日期（输入日期的文本框输入格式为：yyyy-mm-dd~yyyy-mm-dd,输入F进行查询：）：");
                    while (true) {
                        String date = sc.next();
                        if (date.equals("F")) {
                            System.out.println("只根据购买日期查询成功,请继续选择查询！");
                            break;
                        }
                        String[] s = date.split("~");
                        Car car = new Car();
                        car.setStartTime(s[0]);
                        car.setEndTime(s[1]);
                        List<Car> cars = session.selectList("CarMapper.selectByCondition", car);
                        for(Car c: cars){
                            System.out.println(c);
                        }
                    }
                    continue;
                case "5":
                    System.out.println("请输入需要查询的电话（可添加多个,输入F退出）：");
                    while (true) {
                        String s = sc.next();
                        if (s.equals("F")) {
                            System.out.println("成功退出,可以继续选择查询条件！");
                            break;
                        }
                        telephones.add(s);
                    }
                    map.put("telephone", telephones);
                    continue;
                default:
                    System.out.println("输入有误，请重新输入！");
                    continue;
            }

        }

        //执行sql
        System.out.println("根据所给条件查询出以下信息：");
        List<Car> cars = session.selectList("CarMapper.selectByCondition", map);
        for (Car c : cars) {
            System.out.println(c);
        }

        //释放资源
        session.close();

    }

   

}

运行结果如下：