快速入门

1.概述

  • 安装maven环境以及配置环境
  • 进入mybatis基础课程部分学习
  • mybatis基本介绍mybatis入门环境
  • mybatis实现数据库CRUD
  • mybatis中动态sql
  • mybatis关联关系
  • mybatis中resultType resultMap却别以及使用场景
  • mybatis中缓存

2.idea中的maven配置

image-20240328162754997

详细配置略

3.mybatis引言

mybatis官网：https://blog.mybatis.org/ mybatis中文网：https://mybatis.net.cn/

1
2
3
4
5
6
7
# 1.什么是 MyBatis？
- 定义：mybatis使用来完成数据库操作的半ORM框架   官方定义：MyBatis 是一款优秀的持久层（mysql,oracle）框架
   (Hibernate ORM) ==> 表t_user(id,name)    java对象转化 User id name
   ORM：Object Relationship Mapping 对象关系映射
   半ORM：mybatis自己在mapper配置文件中书写字段和属性映射关系
   
- 作用：用来操作数据库 mysql oracle sqlServel等，解决原始jdbc编程技术中代码冗余，方便访问数据。

4.第一个入门环境

1.创建空maven项目

image-20240328164831858

2.项目中引入mybatis依赖（pom.xml)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 注意 mysql-connector-java和mysql-connector-j区别
mysql驱动为mysql-connector-java
必须加版本号
mysql驱动依赖如下，如果不加版本号的话会报错，Cannot resolve mysql:mysql-connector-java:unknown 
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.27</version>
</dependency>
mysql驱动为mysql-connector-j
可以不加版本号
mysql-connector-j这个版本号是从8.0.31起步的，具体可以看上面的maven仓库，里面有版本选择列表
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<!--JUnit 是一个编写可重复测试的简单框架-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>RELEASE</version>
    <scope>test</scope>
</dependency>
<!--mybatis依赖-->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.6</version>
</dependency>
<!--引入mysql驱动jar-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>

3.mybatis的主配置文件(mybatis-cofig.xml)

主配置文件：核心配置文件 作用：用来创建sqlSessionFactory对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<!--环境就是操作的是哪个数据库 environments 环境复数 prod dev test ... default="xxx"里边是什么什么环境生效-->
<environments default="development">

    <!--生产环境-->
    <environment id="development">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
            <property name="username" value="root"/>
            <property name="password" value="liu1693522579"/>
        </dataSource>
    </environment>
</environments>

4.获取sqlSession

1
2
3
4
5
6
7
//读取mybatis-config.xml
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//创建mybatis核心对象SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//获取sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
System.out.println(sqlSession);
1
结果：org.apache.ibatis.session.defaults.DefaultSqlSession@6737fd8f

5.建表

1
2
3
4
5
6
7
CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(40) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `bir` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

image-20240328173259463

6.实体对象 entity(实体) model(模型) com.chabai.entity com.chabai.model

1
2
为什么POJO的属性必须要用包装类型呢？
《阿里巴巴Java开发手册》这样说明：数据库的查询结果可能是null，如果使用基本类型的话，因为要自动拆箱（将包装类型转为基本类型，比如说把Integer 对象转换成int值），就会抛出 NullPointerException 的异常。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
package com.chabai.entity;

import java.util.Date;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class User {
    private Integer id;
    private String name;
    private Integer age;
    private Date bir;


    public User() {
    }

    public User(Integer id, String name, Integer age, Date bir) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.bir = bir;
    }

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public Integer getAge() {
        return age;
    }

    public Date getBir() {
        return bir;
    }

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

    public void setName(String name) {
        this.name = name;
    }

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

    public void setBir(Date bir) {
        this.bir = bir;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", bir=" + bir +
                '}';
    }
}

7.开发DAO接口 mybatis只写接口实现是由mapper配置文件代替的

注意：mybatis要求接口中不能定义方法的重载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package com.chabai.dao;

import com.chabai.entity.User;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public interface UserDAO {

    //保存用户
    int save(User user);
}

8.开发mapper配置文件

注意：在mybatis中一个DAO接口对应一个Mapper配置文件 idea中建立配置文件目录使用/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?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">
<!--
    namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
    全限定名：包.类
-->
<mapper namespace="com.chabai.dao.UserDAO">

    <!--保存
       insert:插入操作
       id:方法名
       parameterType:参数类型 包.类
       注意：1.insert标签内部写sql语句
            2.#{对象中属性名}
       useGeneratedKeys="true"  使用数据库的自动生成id策略  默认就是true可以不加  只支持mysql
    -->
    <insert id="save" parameterType="com.chabai.entity.User" useGeneratedKeys="true">
        insert into t_user values (#{id},#{name},#{age},#{bir})
    </insert>

</mapper>

9.将mapper注册到mybatis-config.xml配置文件中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?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>

     <!--配置别名相关-->
    <typeAliases>
        <!--<typeAlias type="com.chabai.entity.User" alias="user"/>-->
        <!--用来给指定包下的所有类型起别名 name:书写的是起别名的包
            默认别名：一种是类全小写 一种是类名
        -->
        <package name="com.chabai.entity"/>   <!--即以后再此包下的类用User|user即可-->
    </typeAliases>
    
    
    <!--环境就是操作的是哪个数据库 environments 环境复数 prod dev test ... default="xxx"里边是什么什么环境生效-->
    <environments default="development">

        <!--生产环境-->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="liu1693522579"/>
            </dataSource>
        </environment>
    </environments>

    <!--注册项目中的mapper.xml配置-->
    <mappers>
        <mapper resource="com/chabai/mapper/UserDAO.xml"/>
    </mappers>
</configuration>

10.测试UserDAO（最好在src/test/java下建测试类）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.chabai;

import com.chabai.dao.UserDAO;
import com.chabai.entity.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 java.io.IOException;
import java.io.InputStream;
import java.util.Date;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class TestMybatis {
    public static void main(String[] args) throws IOException {
        //读取mybatis-config.xml
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        //创建mybatis核心对象SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //获取对应DAO对象
        UserDAO userDAO = sqlSession.getMapper(UserDAO.class);

        try {
            User user = new User();
            //一个个赋值可用set方法，整体赋值用有参构造更好
            //user.setId(1);//可以不设置id使用数据库自动生成id
            user.setName("chabai");
            user.setAge(22);
            user.setBir(new Date());
            int count = userDAO.save(user);
            System.out.println("影响的条数："+count);
            sqlSession.commit();//提交事务 注提交事务前已经预编译好了只等确认插入了
        }catch (Exception e){
            e.printStackTrace();
            sqlSession.rollback();//回滚事务
        }finally {
            sqlSession.close();//释放资源
        }
    }
}

11.最终项目结构

image-20240328183847853

12.可能遇到的问题

mybatis执行过程中数据库乱码问题

1
2
3
4
5
# 1.为什么会出现乱码？
- java中编码在通过不同操作系统底层传输过程中由于和操作系统的编码不一致就会出现乱码
# 解决办法
主配置文件
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?character=utf-8"/>

image-20240328182642040

mybatis中插入如何返回数据库自动生成的id

image-20240328183400015

image-20240328183445824

image-20240328183458977

5.mybatis中的CRUD

5.1记录的更新操作

UserDAO.java

image-20240328231917439

UserDAO.xml

image-20240328231935536

TestCRUD.java

image-20240328232046896

5.2记录的删除操作

UserDAO.java

1
2
//删除方法
int delete(Integer id);

UserDAO.xml

1
2
3
4
<!--删除方法-->
<delete id="delete" parameterType="Integer">
    delete from t_user where id=#{id}
</delete>

TestCRUD.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
//删除操作
@Test
public void delete() throws IOException {
    //读取mybatis-config.xml配置文件
    InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
    //创建mybatis核心对象SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    //获取sqlSession 执行sql语句
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
        //获取对应DAO对象
        UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
        int delete = userDAO.delete(7);
        System.out.println("删除的条数："+delete);
        sqlSession.commit();//提交事务
    } catch (Exception e) {
        e.printStackTrace();
        sqlSession.rollback();//事务回滚
    } finally {
        sqlSession.close();//事务关闭
    }
}

5.3封装工具类

MybatisUtils.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package com.chabai.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.InputStream;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    //静态代码块  static特点：类加载执行 只执行一次
    static {
        //读取mybatis-config.xml配置文件
        InputStream is = null;
        try {
            is = Resources.getResourceAsStream("mybatis-config.xml");
            //创建mybatis核心对象SqlSessionFactory
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                is.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    //提供sqlSession
    public static SqlSession getSqlSession(){
        //获取sqlSession 执行sql语句
        return sqlSessionFactory.openSession();
    }

    //关闭sqlSession
    public static void close(SqlSession sqlSession){
        sqlSession.close();
    }
}

优化后的删除操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//删除操作
@Test
public void delete() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    try {
        //获取对应DAO对象
        UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
        int delete = userDAO.delete(5);
        System.out.println("删除的条数："+delete);
        sqlSession.commit();//提交事务
    } catch (Exception e) {
        e.printStackTrace();
        sqlSession.rollback();//事务回滚
    } finally {
        MybatisUtils.close(sqlSession);//事务关闭
    }
}

5.4记录的查询操作

5.4.1查询所有

UserDAO.java

1
2
//查询所有方法
List<User> queryAll();

UserDAO.xml

1
2
3
4
5
6
<!--查询所有
      resultType: 要写list集合的泛型类型 com.chabai.User
    -->
<select id="queryAll" resultType="com.chabai.entity.User">
    select id,name,age,bir from t_user
</select>

TestQuery.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//查询所有
@Test
public void testQueryAll(){

    //获取sqlSession
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    //获取对应DAO对象
    UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
    List<User> users = userDAO.queryAll();
    //遍历
    users.forEach(user-> System.out.println(user));


}

结果

1
2
3
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
User{id=2, name='chabai2', age=22, bir=Thu Mar 28 18:17:10 GMT+08:00 2024}
User{id=6, name='chabai3', age=22, bir=Thu Mar 28 22:49:40 GMT+08:00 2024}

5.4.2根据id查询一个用户

UserDAO.java

1
2
//根据id查询一个用户
User queryId(Integer id);

UserDAO.xml

1
2
3
4
5
6
7
8
<!--sql标签：用来实现sql语句复用 id:相当于给sql标签定义了一个唯一标识-->
<sql id="userQuery">
    select id,name,age,bir from t_user
</sql>
<!--查询一个基于id-->
<select id="queryId" parameterType="Integer" resultType="com.chabai.entity.User">
    <include refid="userQuery"></include> where id=#{id}
</select>

TestQuery.java

1
2
3
4
5
6
7
8
9
10
11
12
13
//查询一个基于id
@Test
public void testQueryId(){

    //获取sqlSession
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    //获取对应DAO对象
    UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
    //参数是user的需要先new User再全部分开设值，参数就一个的直接给值即可
    User user = userDAO.queryId(2);
    System.out.println(user);
    MybatisUtils.close(sqlSession);
}

5.4.3模糊查询 where like %张%

UserDAO.java

1
2
//模糊查询
List<User> queryLikeByName(String name);

UserDAO.xml

1
2
3
4
5
6
7
<!--基于名字模糊查询-->
<!--oracle:'%'||#{name}||'%'
        mysql:concat('%',#{name},'%')
    -->
<select id="queryLikeByName" parameterType="String" resultType="com.chabai.entity.User">
    <include refid="userQuery"></include> where name like concat('%',#{name},'%')
</select>

TestQuery.java

1
2
3
4
5
6
7
8
9
10
11
12
13
//模糊查询
@Test
public void testQueryByName(){

    //获取sqlSession
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    //获取对应DAO对象
    UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
    List<User> users = userDAO.queryLikeByName("白");
    //遍历
    users.forEach(user-> System.out.println(user));
    MybatisUtils.close(sqlSession);
}

**5.4.4分页查询 select … from … limit 起始条数 ，每页显示记录数 **

UserDAO.java

mybatis dao方法参数通过注解起别名

1
2
//分页查询 //参数1：起始位置 参数2：每页显示记录数  //mybatis dao方法参数通过注解起别名 mybatis的注解
List<User> queryByPage(@Param("start") Integer start,@Param("rows")Integer rows);

UserDAO.xml

1
2
3
4
5
<!--分页查询-->
<!--多个参数不写parameterType，通过起别名-->
<select id="queryByPage" resultType="com.chabai.entity.User">
    <include refid="userQuery"></include> limit #{start},#{rows}
</select>

TestQuery.java

1
2
3
4
5
6
7
8
9
10
11
12
13
//分页查询
@Test
public void testQueryByPage(){

    //获取sqlSession
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    //获取对应DAO对象
    UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
    //mysql起始条数从0开始 当前页：1 起始位置：0   当前页：2 起始位置：2    start:（当前页-1）*每页显示记录数
    List<User> users = userDAO.queryByPage(2, 2);//从位置2开始显示两条
    users.forEach(user-> System.out.println(user));
    MybatisUtils.close(sqlSession);
}

5.4.5查询总条数

UserDAO.java

1
2
//查询总条数
Long queryTotalCounts();

UserDAO.xml

1
2
3
4
<!--查询总条数-->
<select id="queryTotalCounts" resultType="Long">
    select count(id) from t_user
</select>

TestQuery.java

1
2
3
4
5
6
7
8
9
10
11
12
//查询总条数
@Test
public void testQueryTotalCounts(){

    //获取sqlSession
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    //获取对应DAO对象
    UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
    Long counts = userDAO.queryTotalCounts();
    System.out.println("总记录数为："+counts);
    MybatisUtils.close(sqlSession);
}

5.4.6ResultType和ResultMap区别

总结resultType,resultMap都是对数据库中返回的结果进行封装的

区别：

  • resultType：只能封装简单类型的对像 简单类型对象：对象中的属性全是八种基本数据类型+String+日期类型，即对象中没有对象类型的属性

  • resultMap：可以封装复杂类型的对象 处理库表管理关系===>对一对一，一对多，多对多关系时封装对象处理

resultType

1
2
3
4
5
6
7
8
9
10
11
<!--sql标签：用来实现sql语句复用 id:相当于给sql标签定义了一个唯一标识-->
<sql id="userQuery">
    select id,name as uname,age,bir from t_user
</sql>

<!--查询所有  用resultType
  resultType: 要写list集合的泛型类型 com.chabai.User
-->
<select id="queryAll" resultType="com.chabai.entity.User">
   <include refid="userQuery"></include><!--include：包含哪个sql片段 refid:包含片段的id-->
</select>

resultMap

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<!--sql标签：用来实现sql语句复用 id:相当于给sql标签定义了一个唯一标识-->
<sql id="userQuery">
    select id,name as uname,age,bir from t_user
</sql>
<!--查询所有用resultMap
结果映射 id:resultMap标签起一个唯一标识  type:指定封装对象的类型
注：列和属性名严格一致才能自动封装不一致，要手动写映射
-->
<resultMap id="userResultMap" type="com.chabai.entity.User">
    <!--映射-->
    <!--主键封装：id标签-->
    <!--参数1:查询的列名，参数2对应封装属性名-->
    <!--主键封装：id标签 不能出现2个id情况-->
    <id column="id" property="id"/>
    <!--普通列封装：result标签-->
    <result column="uname" property="name"/>
    <result column="age" property="age"/>
    <result column="bir" property="bir"/>
</resultMap>
<select id="queryAll" resultMap="userResultMap">
    <include refid="userQuery"></include><!--include：包含哪个sql片段 refid:包含片段的id-->
</select>

image-20240329151023723

6.mybatis处理数据库中关联关系

数据库关联关系：一对一关联关系、一对多关联关系、多对多关联关系

6.1一对一关联关系

用户信息===》身份信息

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//一对一关联关系
//用户表
CREATE TABLE t_person(
  id INT(6) PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(40),
  age INT(3),
  cardno VARCHAR(18) REFERENCES t_info(cardno)//数据库通过外键角度体现关系，而后面java我们通过对象，即关系属性体现关系
);
//用户信息表
CREATE TABLE t_info(
  id INT(6) PRIMARY KEY AUTO_INCREMENT,
  cardno VARCHAR(18),
  address VARCHAR(100)
);

mybatis保存用户信息的同时保存身份信息

  • 身份信息保存

    Info

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    package com.chabai.entity;

    /**
     * @author 刘晔
     * @version 1.0
     * description:
     */
    public class Info {
        private Integer id;
        private String cardno;//身份证
        private  String address;//地址

        public Info() {
        }

        public Info(Integer id, String cardon, String address) {
            this.id = id;
            this.cardno = cardno;
            this.address = address;
        }

        @Override
        public String toString() {
            return "Info{" +
                    "id=" + id +
                    ", cardon='" + cardno + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }

        public Integer getId() {
            return id;
        }

        public String getCardon() {
            return cardno;
        }

        public String getAddress() {
            return address;
        }

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

        public void setCardon(String cardon) {
            this.cardno = cardon;
        }

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

    }

    InfoDAO

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    package com.chabai.dao;

    import com.chabai.entity.Info;

    /**
     * @author 刘晔
     * @version 1.0
     * description:
     */
    public interface InfoDAO {
        //保存身份信息的方法
        int save(Info info);
    }

    InfoDAO.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    <?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">
    <!--
        namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
        全限定名：包.类
    -->
    <mapper namespace="com.chabai.dao.InfoDAO">

        <!--save-->
        <insert id="save" parameterType="com.chabai.entity.Info" keyProperty="id" useGeneratedKeys="true">
            insert into t_info values (#{id},#{cardno},#{address})
        </insert>

    </mapper>

    Mybatis-config.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    <?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>

        <!--环境就是操作的是哪个数据库 environments 环境复数 prod dev test ... default="xxx"里边是什么什么环境生效-->
        <environments default="development">

            <!--生产环境-->
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatis?character=utf-8"/>
                    <property name="username" value="root"/>
                    <property name="password" value="liu1693522579"/>
                </dataSource>
            </environment>
        </environments>

        <!--注册项目中的mapper.xml配置-->
        <mappers>
            <!--身份信息-->
            <mapper resource="com/chabai/mapper/InfoDAO.xml"></mapper>
        </mappers>
    </configuration>

    TestInfoDAO

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    package com.chabai.test;

    import com.chabai.dao.InfoDAO;
    import com.chabai.entity.Info;
    import com.chabai.utils.MybatisUtils;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;

    /**
     * @author 刘晔
     * @version 1.0
     * description:
     */
    public class TestInfoDAO {

        //保存身份信息
        @Test
        public void testSaveInfo(){
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            InfoDAO infoDAO = sqlSession.getMapper(InfoDAO.class);
            try {
                Info info = new Info();
                info.setCardon("111111111111111111");
                info.setAddress("河南省郑州市");
                infoDAO.save(info);
                sqlSession.commit();
            } catch (Exception e) {
                sqlSession.rollback();//回滚事务放在抛出异常前，放后边不对，异常已抛出了，无法执行
                throw new RuntimeException(e);
            } finally {
                MybatisUtils.close(sqlSession);
            }
        }
    }

  • 用户信息保存

    Person

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    package com.chabai.entity;

    /**
     * @author 刘晔
     * @version 1.0
     * description:
     */
    public class Person {

        private Integer id;
        private String name;
        private Integer age;
        private String cardno; //外键信息


        public Person() {
        }

        public Person(Integer id, String name, Integer age, String cardno) {
            this.id = id;
            this.name = name;
            this.age = age;
            this.cardno = cardno;
        }

        @Override
        public String toString() {
            return "Person{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", age=" + age +
                    ", cardno='" + cardno + '\'' +
                    '}';
        }

        public Integer getId() {
            return id;
        }

        public String getName() {
            return name;
        }

        public Integer getAge() {
            return age;
        }

        public String getCardno() {
            return cardno;
        }

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

        public void setName(String name) {
            this.name = name;
        }

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

        public void setCardno(String cardno) {
            this.cardno = cardno;
        }
    }

    PersonDAO

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    package com.chabai.dao;

    import com.chabai.entity.Person;

    /**
     * @author 刘晔
     * @version 1.0
     * description:
     */
    public interface PersonDAO {
        //保存用户信息
        int save(Person person);
    }

    PerDAO.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    <?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">
    <!--
        namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
        全限定名：包.类
    -->
    <mapper namespace="com.chabai.dao.PersonDAO">

        <!--save-->
        <insert id="save" parameterType="com.chabai.entity.Person" useGeneratedKeys="true" keyProperty="id">
            insert into t_person values(#{id},#{name},#{age},#{cardno})
        </insert>


    </mapper>

    Mybatis-config.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    <?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>

        <!--环境就是操作的是哪个数据库 environments 环境复数 prod dev test ... default="xxx"里边是什么什么环境生效-->
        <environments default="development">

            <!--生产环境-->
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatis?character=utf-8"/>
                    <property name="username" value="root"/>
                    <property name="password" value="liu1693522579"/>
                </dataSource>
            </environment>
        </environments>

        <!--注册项目中的mapper.xml配置-->
        <mappers>
            <!--身份信息-->
            <mapper resource="com/chabai/mapper/InfoDAO.xml"></mapper>
            <!--用户信息-->
            <mapper resource="com/chabai/mapper/PersonDAO.xml"></mapper>
        </mappers>
    </configuration>

    TestPersonDAO

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    package com.chabai.test;

    import com.chabai.dao.PersonDAO;
    import com.chabai.entity.Person;
    import com.chabai.utils.MybatisUtils;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;

    /**
     * @author 刘晔
     * @version 1.0
     * description:
     */
    public class TestPersonDAO {

        //保存用户信息
        @Test
        public void testSavePerson(){
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            PersonDAO personDAO = sqlSession.getMapper(PersonDAO.class);
            try {
                Person person = new Person();
                person.setName("茶白");
                person.setAge(22);
                //外键信息
                person.setCardno("111111111111111111");
                personDAO.save(person);
                sqlSession.commit();
            } catch (Exception e) {
                sqlSession.rollback();
                throw new RuntimeException(e);
            } finally {
                MybatisUtils.close(sqlSession);
            }
        }
    }

mybatis处理一对一关联关系的查询

  • 根据用户信息并将他的身份信息一并查询

    Person

    image-20240329183254699

    PersonDAO

    image-20240329183307654

    PersonDAO.xml

    image-20240329183332991

    TestPersonDAO

    image-20240329183347950

6.2一对多关联关系

部门信息===》员工信息

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--部门表
CREATE TABLE t_dept(
  id INT(6) PRIMARY KEY,
  NAME VARCHAR(40)
);
--员工表
CREATE TABLE t_emp(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(40),
  age INT(3),
  bir TIMESTAMP,
  deptid int(6) references t_dept(id)//在数据库通过外键外键体现关联关系，而java我们通过对象，即关系属性体现关系
);
-- 注意：一对多关联关系时外键最好在多的一方

信息保存(保存操作)，我这里为了简单直接通过工具建了

image-20240329205724789

image-20240329205741560

==根据一查多的一方==

Dept

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
package com.chabai.entity;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class Dept {
    private Integer id;
    private String name;

    public Dept() {
    }

    public Dept(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Emp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package com.chabai.entity;

import java.util.Date;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class Emp {
    private Integer id;
    private String name;
    private Integer age;
    private Date bir;

    public Emp() {
    }

    public Emp(Integer id, String name, Integer age, Date bir) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.bir = bir;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", bir=" + bir +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

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

    public Date getBir() {
        return bir;
    }

    public void setBir(Date bir) {
        this.bir = bir;
    }
}

DeptDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.chabai.dao;
import com.chabai.entity.Dept;

import java.util.List;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public interface DeptDAO {

    //查询所有部门并将每个部门的员工信息查询出来
    List<Dept> queryAll();

}

EmpDAO

1
2
3
4
5
6
7
8
9
10
package com.chabai.dao;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public interface EmpDAO {
}

DeptDAO.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?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">
<!--
    namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
    全限定名：包.类
-->
<mapper namespace="com.chabai.dao.DeptDAO">

    <!--查询所有部门:由简入深，先写达成简单的查询，再改-->
    <select id="queryAll" resultType="com.chabai.entity.Dept">
    select id,name from t_dept
    </select>
</mapper>

mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?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>

    <!--环境就是操作的是哪个数据库 environments 环境复数 prod dev test ... default="xxx"里边是什么什么环境生效-->
    <environments default="development">

        <!--生产环境-->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?character=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="liu1693522579"/>
            </dataSource>
        </environment>
    </environments>

    <!--注册项目中的mapper.xml配置-->
    <mappers>
        <!--注册身份信息-->
        <mapper resource="com/chabai/mapper/InfoDAO.xml"></mapper>
        <!--注册用户信息-->
        <mapper resource="com/chabai/mapper/PersonDAO.xml"></mapper>
        <!--注册部门信息-->
        <mapper resource="com/chabai/mapper/DeptDAO.xml"></mapper>
    </mappers>
</configuration>

TestDeptDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
package com.chabai.test;

import com.chabai.dao.DeptDAO;
import com.chabai.entity.Dept;
import com.chabai.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class TestDeptDAO {

    //查询所有
    @Test
    public void TestQueryAll(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        DeptDAO deptDAO = sqlSession.getMapper(DeptDAO.class);
        List<Dept> depts = deptDAO.queryAll();//调用方法得到返回值
        for (Dept dept : depts) {//循环遍历所有记录
            System.out.println("部门信息："+dept);
        }//也可以用lambda表达式遍历
        MybatisUtils.close(sqlSession);
    }
}

结果

1
2
部门信息：Dept{id=1, name='研发部'}
部门信息：Dept{id=2, name='教学部'}

开始更改 DeptDAO.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?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">
<!--
    namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
    全限定名：包.类
-->
<mapper namespace="com.chabai.dao.DeptDAO">

    <!--查询所有部门:由简入深，先写达成简单的查询，再改-->
<!--    <select id="queryAll" resultType="com.chabai.entity.Dept">-->
<!--        select id,name from t_dept-->
<!--    </select>-->


    <!--查询所有部门并将每个部门的员工信息查询出来 先在sql中写成功查到再粘过来-->
    <!--这里受到返回值的影响查到的还只是部门信息-->
    <select id="queryAll" resultType="com.chabai.entity.Dept">
            SELECT
                d.id,
                d.name,
                e.id eid,
                e.name,
                e.age,
                e.bir
            FROM t_dept d
            LEFT JOIN t_emp e
            ON d.id=e.deptid
        </select>

</mapper>

结果

1
2
3
部门信息：Dept{id=1, name='研发部'}
部门信息：Dept{id=1, name='研发部'}
部门信息：Dept{id=2, name='教学部'}

数据库通过外键我们通过对象，即关系属性，体现一个部门下有多个员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
package com.chabai.entity;

import java.util.List;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class Dept {
    private Integer id;
    private String name;

    //这里更改
    //对象 关系属性
    private List<Emp> emps;//员工的关系属性 //一个部门下放了一个员工的集合，装着这个部门所有员工的信息==》可体现一个部门下的所有员工

    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }

    public Dept() {
    }

    public Dept(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

TestDeptDAO

image-20240329215857371

再改 DeptDAO.xml 复杂关联关系要换返回值标签为resultMap

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?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">
<!--
    namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
    全限定名：包.类
-->
<mapper namespace="com.chabai.dao.DeptDAO">
    <!--查询所有部门并将每个部门的员工信息查询出来-->
    <resultMap id="deptMap" type="com.chabai.entity.Dept">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <!--封装员工信息 collection是用来处理一对多关联关系时的标签
            property:封装关系属性名
            javaType:关系属性类型
            ofType:关系属性中泛型的类型
        -->
        <collection property="emps" javaType="list" ofType="com.chabai.entity.Emp">
            <id column="eid" property="id"></id>
            <result column="ename" property="name"></result>
            <result column="age" property="age"></result>
            <result column="bir" property="bir"></result>
        </collection>
    </resultMap>


    <select id="queryAll" resultMap="deptMap">
        SELECT
            d.id,
            d.name,
            e.id eid,
            e.name ename,
            e.age,
            e.bir
        FROM t_dept d
                 LEFT JOIN t_emp e
                           ON d.id=e.deptid
    </select>



</mapper>

此时已经赋值再次TestDeptDAO即可成功

1
2
3
4
5
部门信息：Dept{id=1, name='研发部'}
    员工信息：Emp{id=3, name='小陈', age=24, bir=Fri Mar 29 20:54:45 GMT+08:00 2024}
    员工信息：Emp{id=1, name='小张', age=22, bir=Fri Mar 29 20:54:25 GMT+08:00 2024}
部门信息：Dept{id=2, name='教学部'}
    员工信息：Emp{id=2, name='小李', age=23, bir=Fri Mar 29 20:54:27 GMT+08:00 2024}

==根据多查一 查询员工并查询每个员工的部门==

Emp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package com.chabai.entity;

import java.util.Date;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class Emp {
    private Integer id;
    private String name;
    private Integer age;
    private Date bir;

    public Emp() {
    }

    public Emp(Integer id, String name, Integer age, Date bir) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.bir = bir;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", bir=" + bir +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

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

    public Date getBir() {
        return bir;
    }

    public void setBir(Date bir) {
        this.bir = bir;
    }
}

EmpDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.chabai.dao;

import com.chabai.entity.Emp;

import java.util.List;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public interface EmpDAO {

    //查询所有员工并查询每个员工部门
    List<Emp> queryAll();
}

EmpDAO.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?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">
<!--
    namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
    全限定名：包.类
-->
<mapper namespace="com.chabai.dao.EmpDAO">

    <!--查询所有员工:由简入深，先写达成简单的查询，再改-->
        <select id="queryAll" resultType="com.chabai.entity.Emp">
            select id,name,age,bir from t_emp
        </select>

</mapper>

mybaris-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?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>

    <!--环境就是操作的是哪个数据库 environments 环境复数 prod dev test ... default="xxx"里边是什么什么环境生效-->
    <environments default="development">

        <!--生产环境-->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?character=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="liu1693522579"/>
            </dataSource>
        </environment>
    </environments>

    <!--注册项目中的mapper.xml配置-->
    <mappers>
        <!--注册身份信息-->
        <mapper resource="com/chabai/mapper/InfoDAO.xml"></mapper>
        <!--注册用户信息-->
        <mapper resource="com/chabai/mapper/PersonDAO.xml"></mapper>
        <!--注册部门信息-->
        <mapper resource="com/chabai/mapper/DeptDAO.xml"></mapper>
        <!--注册员工信息-->
        <mapper resource="com/chabai/mapper/EmpDAO.xml"></mapper>

    </mappers>
</configuration>

TestEmpDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package com.chabai.test;

import com.chabai.dao.EmpDAO;
import com.chabai.entity.Emp;
import com.chabai.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class TestEmpDAO {

    @Test
    public void testQueryAll(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        EmpDAO empDAO = sqlSession.getMapper(EmpDAO.class);
        for (Emp emp : empDAO.queryAll()) {
            System.out.println("当前员工信息："+emp);
        }
    }
}

结果

1
2
3
Emp{id=1, name='小张', age=22, bir=Fri Mar 29 20:54:25 GMT+08:00 2024}
Emp{id=2, name='小李', age=23, bir=Fri Mar 29 20:54:27 GMT+08:00 2024}
Emp{id=3, name='小陈', age=24, bir=Fri Mar 29 20:54:45 GMT+08:00 2024}

开始更改 EmpDAO.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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">
<!--
    namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
    全限定名：包.类
-->
<mapper namespace="com.chabai.dao.EmpDAO">
    

    <!--查询所有部门并将每个部门的员工信息查询出来 先在sql中写成功查到再粘过来-->
    <!--这里受到返回值的影响查到的还只是部门信息-->
    <!--更改1，先在sql中写成功查到再粘过来-->
    <select id="queryAll" resultType="com.chabai.entity.Emp">
      select e.id,e.name,e.age,e.bir ,d.id did,d.name dname from t_emp e
      left join t_dept d
      on e.deptid=d.id
    </select>

</mapper>

数据库通过外键我们通过对象，即关系属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
package com.chabai.entity;

import java.util.Date;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class Emp {
    private Integer id;
    private String name;
    private Integer age;
    private Date bir;

    //关系属性 一个员工只能由一个部门==》这里定义对象即可   区别一对多
    private Dept dept; //代表员工部门信息

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    public Emp() {
    }

    public Emp(Integer id, String name, Integer age, Date bir) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.bir = bir;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", bir=" + bir +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

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

    public Date getBir() {
        return bir;
    }

    public void setBir(Date bir) {
        this.bir = bir;
    }
}

TestEmpDAO 此时只声明并没有赋值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package com.chabai.test;

import com.chabai.dao.EmpDAO;
import com.chabai.entity.Emp;
import com.chabai.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class TestEmpDAO {

    @Test
    public void testQueryAll(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        EmpDAO empDAO = sqlSession.getMapper(EmpDAO.class);
        for (Emp emp : empDAO.queryAll()) {
            System.out.println("当前员工信息："+emp+"     部门信息："+emp.getDept());
        }
    }
}

结果

1
2
3
当前员工信息：Emp{id=1, name='小张', age=22, bir=Fri Mar 29 20:54:25 GMT+08:00 2024}     部门信息：null
当前员工信息：Emp{id=2, name='小李', age=23, bir=Fri Mar 29 20:54:27 GMT+08:00 2024}     部门信息：null
当前员工信息：Emp{id=3, name='小陈', age=24, bir=Fri Mar 29 20:54:45 GMT+08:00 2024}     部门信息：null

再改 EmpDAO.xml 复杂关联关系要换返回值标签为resultMap

image-20240329233454206

此时已经赋值再次TestEmpDAO即可成功

1
2
3
当前员工信息：Emp{id=1, name='小张', age=22, bir=Fri Mar 29 20:54:25 GMT+08:00 2024}     部门信息：Dept{id=1, name='研发部'}
当前员工信息：Emp{id=2, name='小李', age=23, bir=Fri Mar 29 20:54:27 GMT+08:00 2024}     部门信息：Dept{id=2, name='教学部'}
当前员工信息：Emp{id=3, name='小陈', age=24, bir=Fri Mar 29 20:54:45 GMT+08:00 2024}     部门信息：Dept{id=1, name='研发部'}

6.3多对多关联关系

课程信息===》学生信息

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--学生表
create table t_student(
    id int(6) primary key auto_increment,
    name varchar(2)
);
--课程表
create table t_course(
    id int(6) primary key auto_increment,
    name varchar(2)
);
--中间表
create table t_student_course(
    id int(6) primary key auto_increment,
    sid int(6) references t_student(id),
    cid int(6) references t_course(id)
);
--数据库处理不了多对多，实际还是一对多处理，由中间表把他们拆成了两个一对多

==查询学生信息并查询所选课程==

Student

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
package com.chabai.entity;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class Student {
    private Integer id;
    private String name;

    public Student() {
    }

    public Student(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

StudentDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.chabai.dao;

import com.chabai.entity.Student;

import java.util.List;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public interface StudentDAO {

    //查询学生信息并查询所选课程
    Student queryById(Integer id);
}

StudentDAO.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?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">
<!--
    namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
    全限定名：包.类
-->
<mapper namespace="com.chabai.dao.StudentDAO">

    <!--select-->
    <select id="queryById" parameterType="Integer" resultType="com.chabai.entity.Student">
        select id,name from t_student where id=#{id}
    </select>

</mapper>

mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?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>

    <!--环境就是操作的是哪个数据库 environments 环境复数 prod dev test ... default="xxx"里边是什么什么环境生效-->
    <environments default="development">

        <!--生产环境-->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?character=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="liu1693522579"/>
            </dataSource>
        </environment>
    </environments>

    <!--注册项目中的mapper.xml配置-->
    <mappers>
        <!--注册身份信息-->
        <mapper resource="com/chabai/mapper/InfoDAO.xml"></mapper>
        <!--注册用户信息-->
        <mapper resource="com/chabai/mapper/PersonDAO.xml"></mapper>
        <!--注册部门信息-->
        <mapper resource="com/chabai/mapper/DeptDAO.xml"></mapper>
        <!--注册员工信息-->
        <mapper resource="com/chabai/mapper/EmpDAO.xml"></mapper>
        <!--注册学生信息-->
        <mapper resource="com/chabai/mapper/StudentDAO.xml"></mapper>

    </mappers>
</configuration>

TestStudentDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.chabai.test;

import com.chabai.dao.StudentDAO;
import com.chabai.entity.Student;
import com.chabai.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class TestStudentDAO {

    @Test
    public void testQueryById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
        Student student = studentDAO.queryById(12);
        System.out.println("学生信息："+student);
        MybatisUtils.close(sqlSession);
    }
}

结果

1
学生信息：Student{id=12, name='李四'}

先确定sql语句，正确了再写到java

1
2
3
4
5
6
SELECT s.id,s.name,c.id cid,c.name cname FROM t_student s
LEFT JOIN t_student_course tc
ON s.id=tc.sid
LEFT JOIN t_course c
ON tc.cid=c.id
WHERE s.id=11

结果

image-20240330002139936

开始更改 mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?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">
<!--
    namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
    全限定名：包.类
-->
<mapper namespace="com.chabai.dao.StudentDAO">
    <!--更改1-->
    <select id="queryById" parameterType="Integer" resultType="com.chabai.entity.Student">
        SELECT s.id,s.name,c.id cid,c.name cname FROM t_student s
        LEFT JOIN t_student_course tc
        ON s.id=tc.sid
        LEFT JOIN t_course c
        ON tc.cid=c.id
        WHERE s.id=#{id}
    </select>
</mapper>

数据库通过外键我们通过对象，即关系属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package com.chabai.entity;

import java.util.List;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class Student {
    private Integer id;
    private String name;
    
    //关系属性 课程 选择哪些课==》集合
    private List<Course> course;

    public List<Course> getCourse() {
        return course;
    }

    public void setCourse(List<Course> course) {
        this.course = course;
    }

    public Student() {
    }

    public Student(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

TestStudentDAO 此时只声明并没有赋值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com.chabai.test;

import com.chabai.dao.StudentDAO;
import com.chabai.entity.Course;
import com.chabai.entity.Student;
import com.chabai.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * @author 刘晔
 * @version 1.0
 * description:
 */
public class TestStudentDAO {

    @Test
    public void testQueryById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
        Student student = studentDAO.queryById(11);
        System.out.println("学生信息："+student);
        List<Course> courses = student.getCourse();
        for (Course course : courses) {
            System.out.println("          课程信息："+course);
        }
        MybatisUtils.close(sqlSession);
    }
}

再改 StudentDAO.xml 复杂关联关系要换返回值标签为resultMap

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?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">
<!--
    namespace属性：命名空间  用来书写当前mapper文件是对哪个DAO接口实现
    全限定名：包.类
-->
<mapper namespace="com.chabai.dao.StudentDAO">
    <resultMap id="studentMap" type="com.chabai.entity.Student">
        <!--封装学生信息-->
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <!--封装关系属性 多-->
        <collection property="course" javaType="list" ofType="com.chabai.entity.Course">
            <id column="cid" property="id"></id>
            <result column="cname" property="name"></result>
        </collection>
    </resultMap>


    <select id="queryById" parameterType="Integer" resultMap="studentMap">
        SELECT s.id,s.name,c.id cid,c.name cname FROM t_student s
        LEFT JOIN t_student_course tc
        ON s.id=tc.sid
        LEFT JOIN t_course c
        ON tc.cid=c.id
        WHERE s.id=#{id}
    </select>
</mapper>

此时已经赋值再次TestStudentDAO即可成功

1
2
3
学生信息：Student{id=11, name='张三'}
          课程信息：Course{id=1, name='语文'}
          课程信息：Course{id=2, name='数学'}

==当前课程被哪些学生选中，同理==

7.mybatis中缓存

概述

为什么使用缓存?
缓存（即cache）的作用是为了减去数据库的压力，提高数据库的性能。缓存实现的原理是从数据库中查询出来的对象在使用完后不销毁，而是存储在内存（缓存）中，当再次需要获取该对象时，直接从内存中获取，不再向数据库执行select语句，减少对数据库的查询次数，提高了数据库的性能。缓存是使用Map集合存储数据。

MyBatis缓存

注意：缓存针对于查询功能

MyBatis有一级缓存和二级缓存之分。

一级缓存的作用域是同一个SqlSession，在同一个SqlSession中两次执行相同的sql语句，第一次执行完毕会将数据库查询的数据写到缓存（内存），第二次会从缓存中获取数据而不进行数据库查询，大大提高了查询效率。当一个SqlSession结束后该SqlSession中的一级缓存也就不存在了。MyBtais默认启动一级缓存。

二级缓存是多个SqlSession共享的，其作用域是mapper的同一个namespace，不同的sqlSession两次执行相同namespace下的sql语句且向sql中传递的参数也相同时，第一次执行完毕会将数据库中查询到的数据写到缓存（内存），第二次会直接从缓存中获取，从而提高了查询效率。MyBatis默认不开启二级缓存，需要在MyBtais全局配置文件中进行setting配置开启二级缓存。

一级缓存

MyBatis默认开启一级缓存，一级缓存只是相对于同一个SqlSession而言。所以在参数个sql完全一致的情况下，我们使用同一个SqlSession对象调用一个Mapper方法，往往只执行一次sql，使用SqlSession第一次查询后，MyBatis会将其放在缓存中，之后再查询时若没有缓存失效或超时，SqlSession都会取出当前缓存的数据，不会再发送sql到数据库。

我们做一个查询的测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
/**
* 批量查询缓存
* 缓存:将数据临时存储在(本地硬盘,内存),减少对数据的访问
*
* 一级缓存是SqlSession级别的,MyBatis默认开启
* 在同一个SqlSession中可以将第一次查询的数据缓存到SqlSession
* 第二次查询相同数据时,就可以直接从SqlSession获取
*/
@Test
public void test5() {
    try {
        Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sessionFactory.openSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        //处理,假设从客户端传递过来要删除的多个参数为一个集合
        List<Integer> list = new ArrayList<>();
        list.add(1);
        //查询
        System.out.println("同一个sqlSession中第一次查询id=1的数据为"+userDao.findUser(list));
        System.out.println("---------------");
        //第二次查询	
        System.out.println("---------------");
        System.out.println("同一个sqlSession中第二次查询id=1的数据为"+userDao.findUser(list));
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    } catch (IOException e) {
    	e.printStackTrace();
    }
}

image-20240330142538670

一级缓存的生命周期
一级缓存的工作过程如图所示

image-20240330142558788

MyBatis开启一个数据库会话时，会创建一个新的SqlSession对象，其中有一个新的Executor对象。Executor对象中持有一个新的PerpetualCache对象，当会话结束时，SqlSession对象及其内部的Executor对象还有PerpetualCache对象会一并释放掉。

缓存失效

0.不同的sqlsession对应不同一级缓存。

1.当SqlSession调用close（）方法时，SqlSession对象关闭，直接会释放掉一级缓存PerpetualCache对象，一级缓存将不可用。

2.如果SqlSession调用了clearCache（），会清空PerpetualCache对象中的数据，但是SQlSession对象还可以用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
/**
* 缓存失效: close clear 执行新增,修改,删除操作会清空一级缓存
*/
@Test
public void test5() {
    try {
        Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sessionFactory.openSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        //处理,假设从客户端传递过来要删除的多个参数为一个集合
        List<Integer> list = new ArrayList<>();
        list.add(1);
        //查询
        System.out.println("同一个sqlSession中第一次查询id=1的数据为"+userDao.findUser(list));
        System.out.println("---------------");
        //清空SqlSession缓存(一级缓存)
        sqlSession.clearCache();
        System.out.println("清空一级缓存");
        //第二次查询	
        System.out.println("---------------");
        System.out.println("同一个sqlSession中第二次查询id=1的数据为"+userDao.findUser(list));
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    } catch (IOException e) {
    	e.printStackTrace();
    }
}

image-20240330142632444

3.SqlSession中执行了任何一个update（修改）、delete（删除）、insert（新增）操作，都会清空PerpetualCache对象中的数据，但是SqlSession对象仍然可用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/**
* 缓存失效: close clear 执行新增,修改,删除操作会清空一级缓存
*/
@Test
public void test5() {
    try {
        Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sessionFactory.openSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        //处理,假设从客户端传递过来要删除的多个参数为一个集合
        List<Integer> list = new ArrayList<>();
        list.add(1);
        //查询
        System.out.println("同一个sqlSession中第一次查询id=1的数据为"+userDao.findUser(list));
        System.out.println("---------------");
        //执行修改语句后缓存会失效
        User user = new User();
        user.setId(1);
        user.setAge(20);
        userDao.updateUser(user);
        System.out.println("执行修改语句缓存失效");
        //第二次查询	
        System.out.println("---------------");
        System.out.println("同一个sqlSession中第二次查询id=1的数据为"+userDao.findUser(list));
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    } catch (IOException e) {
    	e.printStackTrace();
    }
}

image-20240330142652924

4.同一个sqlsession但是查询条件不同。

二级缓存

二级缓存的作用域是SqlSessionFactory级别，整个应用程序只有一个，通过同一个SqlSessionFactory创建的sqlsession查询结果会被缓存，此后再次执行相同查询语句，会从缓存中获取。二级缓存区域是根据mapper的namespace划分的，相同的namespace的mapper查询的数据缓存在同一个区域，如果使用mapper代理方法每一个mapper的namespace都不同，此时可以理解为二级缓存区域是根据mapper进行划分的。

每次查询都会先从缓存区域查找，如果找不到则从数据库进行查询，并将查询到的数据写入缓存。MyBtais内部缓存使用HashMap，key为hashCode+sqlid+sql语句，value为从查询出来映射生成的java对象。SqlSession执行任何一个update（修改）、delete（删除）、insert（新增）操作commit提交后都会清空缓存区域，防止脏读。

工作模式

image-20240330142727637

配置二级缓存

1.在MyBtais全局配置文件中开启二级缓存，具体视版本而言。

image-20240330142748049

1
2
3
4
5
<!--mybatis的全局设置-->
<settings>
    <!--开启二级缓存,默认是开启的为true-->
    <setting name="cacheEnabled" value="true"/>
</settings>

2.配置映射文件

在Mapper.xml映射文件中添加cache标签，表示次mapper开启二级缓存。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<!--
    表示此namespace使用二级缓存
    cache标签的属性:
        eviction="LRU" :清除策略,默认为LRU,移除最长时间不被使用的对象。
        flushInterval="60000" :刷新间隔,以毫秒为单位的合理时间量
        size="512" :引用数目
        readOnly="true" :只读的缓存会给所有调用者返回缓存对象的相同实例
-->
<cache></cache>
<!--按照学号迭代查询-->
<!--
    二级缓存
        属性useCache="false",sql语句自己可以决定是否使用二级缓存
        属性flushCache="false",sql语句自己可以决定是否刷新缓存,多用与新增,修改,删除语句
-->
<select id="findUser" resultType="User">
    SELECT * FROM t_user WHERE id in
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
</select>

3.二级缓存必须在SqlSession关闭或提交之后有效

4.查询的数据所转换的实体类类型必须实现序列化的接口

POJO序列化，将所有的POJO类（bean）实现序列化接口java.io.Serializable。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
package com.cwd.mybatis.bean;
import org.apache.ibatis.type.Alias;
import java.io.Serializable;
import java.util.Date;

@Alias("User")
public class User implements Serializable {
    //生成实体类序列化id
    private static final long serialVersionUID = -7383964035746655660L;

    private Integer id;
    private String name;//姓名
    private Integer age;//年龄
    private Date birthday;//生日

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

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

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/**
 * 二级缓存是SqlSessionFactory级别的，整个应用程序只有一个
 * 以namespace划分缓存区域
 */
@Test
public void test6() {
    try {
        Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);

        //SqlSession1,使用SqlSession查询第一次查询数据
        SqlSession sqlSession1 = sessionFactory.openSession();
        UserDao userDao1 = sqlSession1.getMapper(UserDao.class);
        //处理,假设从客户端传递过来要删除的多个参数为一个集合
        List<Integer> list1 = new ArrayList<>();
        list1.add(1);
        list1.add(2);
        System.out.println("SqlSession1查询id=1,2的数据为"+userDao1.findUser(list1));
        sqlSession1.commit();
        sqlSession1.close();//SqlSession关闭时会将数据写入二级缓存
        System.out.println("----------------------------------");

        //SqlSession2,使用SqlSession查询第二次查询数据
        SqlSession sqlSession2 = sessionFactory.openSession();
        UserDao userDao2 = sqlSession2.getMapper(UserDao.class);
        //处理,假设从客户端传递过来要删除的多个参数为一个集合
        List<Integer> list2 = new ArrayList<>();
        list2.add(1);
        list2.add(2);
        System.out.println("SqlSession2查询id=1,2的数据为"+userDao2.findUser(list1));
        sqlSession2.commit();
        sqlSession2.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

image-20240330142845557

二级缓存失效

使二级缓存失效的情况：两次查询之间执行了任意的增删改，会使一级和二级缓存同时失效

二级缓存的相关配置

  • 在mapper配置文件中添加的cache标签可以设置一些属性
  • eviction属性：缓存回收策略
    • LRU（Least Recently Used） – 最近最少使用的：移除最长时间不被使用的对象。
    • FIFO（First in First out） – 先进先出：按对象进入缓存的顺序来移除它们。
    • SOFT – 软引用：移除基于垃圾回收器状态和软引用规则的对象。
    • WEAK – 弱引用：更积极地移除基于垃圾收集器状态和弱引用规则的对象。
    • 默认的是 LRU
  • flushInterval属性：刷新间隔，单位毫秒
    • 默认情况是不设置，也就是没有刷新间隔，缓存仅仅调用语句（增删改）时刷新
  • size属性：引用数目，正整数
    • 代表缓存最多可以存储多少个对象，太大容易导致内存溢出
  • readOnly属性：只读，true/false
    • true：只读缓存；会给所有调用者返回缓存对象的相同实例。因此这些对象不能被修改。这提供了很重要的性能优势。
    • false：读写缓存；会返回缓存对象的拷贝（通过序列化）。这会慢一些，但是安全，因此默认是false

MyBatis缓存查询的顺序

  • 先查询二级缓存，因为二级缓存中可能会有其他程序已经查出来的数据，可以拿来直接使用
  • 如果二级缓存没有命中，再查询一级缓存
  • 如果一级缓存也没有命中，则查询数据库
  • SqlSession关闭之后，一级缓存中的数据会写入二级缓存

详细参考

1.引言

Mybatis是一个基于java语言的持久层的框架，主要用来简化数据库的访问操作，内部封装了原来的jdbc代码，替换了原有项目开发中的jdbc技术，它可以自动完成对象与关系的映射(ORM)，极大地简化了我们的开发，提高了开发效率。

MyBatis官方网址：mybatis – MyBatis 3 | Introduction

MyBatis中文网： MyBatis中文网

2.JDBC存在的问题

  1. 大量的代码冗余 (处理结果集的时候存在大量的代码冗余)

  2. 不能完成数据库和实体的自动转换 (需要手动封装实体，不能自动封装实体类)

image-20240327231739289

实体与数据库之间的转换需要通过setXxx或者构造方法完成

3.Mybatis框架的开发思路

需要2个配置文件：

  • mybatis-config.xml：存储数据库连接相关参数，获取连接对象
  • mapper映射文件：因为映射文件中的id代表方法名，是识别方法的唯一标识，所以注意DAO中的方法名不能相同，不能重载。

image-20240328154908799

4.使用IDEA中的Maven进行MyBatis环境搭建

项目整体结构如下：

image-20240328151150888

1.新建Maven-archetype-webapp项目，命名为mybatis01

image-20240327232049909

2.src—main路径下新建java、resources文件夹

image-20240327232437982

3.pom.xml导入相关依赖，刷新Maven

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
<?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>org.example</groupId>
   <artifactId>mybatis01</artifactId>
   <version>1.0-SNAPSHOT</version>
   <packaging>war</packaging>
 
   <name>mybatis01 Maven Webapp</name>
   <!-- FIXME change it to the project's website -->
   <url>http://www.example.com</url>
 
   <properties>
     <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
     <maven.compiler.source>1.7</maven.compiler.source>
     <maven.compiler.target>1.7</maven.compiler.target>
   </properties>
 
   <dependencies>
     <!--mybatis核心包-->
     <dependency>
       <groupId>org.mybatis</groupId>
       <artifactId>mybatis</artifactId>
       <version>3.4.1</version>
     </dependency>
     
     <!--mysql驱动包-->
     <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>6.0.6</version>
     </dependency>
     
     <!--junit测试包-->
     <dependency>
       <groupId>junit</groupId>
       <artifactId>junit</artifactId>
       <version>4.11</version>
       <scope>test</scope>
     </dependency>
     <dependency>
       <groupId>junit</groupId>
       <artifactId>junit</artifactId>
       <version>4.12</version>
       <scope>compile</scope>
     </dependency>
   </dependencies>
 
   <build>
     <finalName>mybatis01</finalName>
     <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
       <plugins>
         <plugin>
           <artifactId>maven-clean-plugin</artifactId>
           <version>3.1.0</version>
         </plugin>
         <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
         <plugin>
           <artifactId>maven-resources-plugin</artifactId>
           <version>3.0.2</version>
         </plugin>
         <plugin>
           <artifactId>maven-compiler-plugin</artifactId>
           <version>3.8.0</version>
         </plugin>
         <plugin>
           <artifactId>maven-surefire-plugin</artifactId>
           <version>2.22.1</version>
         </plugin>
         <plugin>
           <artifactId>maven-war-plugin</artifactId>
           <version>3.2.2</version>
         </plugin>
         <plugin>
           <artifactId>maven-install-plugin</artifactId>
           <version>2.5.2</version>
         </plugin>
         <plugin>
           <artifactId>maven-deploy-plugin</artifactId>
           <version>2.8.2</version>
         </plugin>
       </plugins>
     </pluginManagement>
   </build>
 </project>

4.利用SQLyog创建数据库（mybatis）、表（user）、新增数据

image-20240327232535709

image-20240327232630117

5.在IDEA java路径下新建entity包、service包

（1）entity包下新建与表user对应的实体类User，并添加get、set、toString方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package entity;

/**
 * @ClassName User
 * @Description 用户实体类，对应mybatis数据库中的user表
 * @Author chabai
 * @Date 2022/4/5 10:42
 * @Version 1.0
 */
public class User {
    private int uid;
    private String uname;
    private int uage;

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public int getUage() {
        return uage;
    }

    public void setUage(int uage) {
        this.uage = uage;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", uname='" + uname + '\'' +
                ", uage=" + uage +
                '}';
    }
}

（2）在service包下新建接口UserService，编写findById方法，不用编写对应的实现类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
package service;

import entity.User;

/**
 * @ClassName UserService
 * @Description TODO
 * @Author chabai
 * @Date 2022/4/5 11:56
 * @Version 1.0
 */
public interface UserService {
    User findById(int id);
}

6.在resources路径下新建mapper文件夹，添加mapper映射文件UserMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?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为映射的根节点，用来管理DAO接口
      namespace指定DAO接口的完整类名，表示mapper配置文件管理哪个DAO接口(包.接口名)
      mybatis会依据这个接口动态创建一个实现类去实现这个接口，而这个实现类是一个Mapper对象
   -->
  <mapper namespace="entity.User">
      <!--
          id = "接口中的方法名"
          parameterType = "接口中传入方法的参数类型"
          resultType = "返回实体类对象：包.类名"  处理结果集 自动封装
          注意:sql语句后不要出现";"号
              查询：select标签
              增加：insert标签
              修改：update标签
              删除：delete标签
      -->
      <select id="findById" parameterType="int" resultType="entity.User">
          select * from user where uid = #{id}
      </select>
  </mapper>

7.在resources路径下新建mybatis配置文件mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<?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" >
  <!--配置mybatis环境-->
  <configuration>
      <!--配置连接使用的相关参数
          default为默认使用的环境：development 测试环境
                                product     生产环境
      -->
      <environments default="development">
          <!--测试环境-->
          <environment id="development">
              <!--事务管理类型：指定事务管理的方式 JDBC-->
              <transactionManager type="JDBC"/>
              <!--数据库连接相关配置，动态获取config.properties文件里的内容-->
              <!--数据源类型：POOLED 表示支持JDBC数据源连接池
                            UNPOOLED 表示不支持数据源连接池
                            JNDI 表示支持外部数据源连接池
              -->
              <dataSource type="POOLED">
                  <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                  <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC&amp;useSSL=false"/>
                  <property name="username" value="root"/>
                  <property name="password" value="123456"/>
              </dataSource>
          </environment>
      </environments>
  
      <!--注册mapper配置文件（mapper文件路径配置）
          注意：映射配置文件位置要和映射器位置一样，如：映射器在com.mycode.dao里，
                那么配置文件就应该在resources的com/mycode/dao目录下，否则会报
                Could not find resource com.mycode.dao.UserMapper.xml类似错误
      -->
      <mappers>
          <mapper resource="mapper/UserMapper.xml"/>
      </mappers>
      
  </configuration>

8.测试Mybatis环境是否搭建成功，在java路径下新建test包，包中编写单元测试类TestMyBatis

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package test;

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;
import java.io.Reader;
import java.sql.SQLException;

/**
 * @ClassName TestMyBatis
 * @Description 测试MyBatis环境是否搭建成功
 * @Author chabai
 * @Date 2022/4/5 17:45
 * @Version 1.0
 */
public class TestMyBatis {
    public static void main(String[] args) {
        //MyBatis读取配置文件的方式
        //InputStream is = TestMyBatis.class.getResourceAsStream("mybatis-config.xml");
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        //创建SQLSessionFactory对象实例
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //获取Session, session中包含connection
        SqlSession session = sessionFactory.openSession();
        System.out.println(session);
        try {
            //一般不从session中获取connection
            session.getConnection().commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

输出结果不报错，表示环境搭建成功：

1
2
org.apache.ibatis.session.defaults.DefaultSqlSession@6e3c1e69
  Tue Apr 05 18:04:27 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

9.test中编写单元测试类UserTest

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package test;

import entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
import org.junit.Test;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;

/**
 * @ClassName UserTest
 * @Description 单元测试
 * @Author chabai
 * @Date 2022/4/5 11:58
 * @Version 1.0
 */
public class UserTest {
    @Test
    public void userFindByIdTest() {
        //配置文件名称
        String resources = "mybatis-config.xml";
        //创建流
        Reader reader = null;
        try {
            //使用MyBatis提供的Resources类加载mybatis的配置文件到reader对象中
            reader= Resources.getResourceAsReader(resources);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //初始化mybatis，创建sqlSessionFactory类的实例
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建session实例
        SqlSession session= sessionFactory.openSession();
        //传入参数查询，返回结果
        User user = session.selectOne("findById",1);
        System.out.println(user);
        //关闭session
        session.close();
    }
}

测试结果如下：

1
2
Tue Apr 05 13:35:42 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
  User{uid=1, uname='zhangsan', uage=20}

5. 代码结构调整

5.1 建表

– 新建表

1
2
3
4
5
6
7
8
9
DROP TABLE IF EXISTS `t_user`;
 CREATE TABLE `t_user` (
   `id` int(11) NOT NULL auto_increment COMMENT '用户ID',
   `name` varchar(32) NOT NULL COMMENT '用户名',
   `birthday` datetime default NULL COMMENT '生日',
   `sex` char(1) default NULL COMMENT '性别',
   `address` varchar(256) default NULL COMMENT '地址',
   PRIMARY KEY(`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

– 插入数据

1
2
3
4
5
6
7
insert  into `t_user`(`id`,`name`,`birthday`,`sex`,`address`) values
 (1,'老王','2018-02-27 17:47:08','男','北京'),
 (2,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),
 (3,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),
 (4,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),
 (5,'老王','2018-03-07 17:37:26','男','北京'),
 (6,'小马宝莉','2018-03-08 11:44:00','女','北京修正');

5.2 新建maven项目并在pom.xml中引入依赖

  • pom.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<!--mybatis-->
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.4.1</version>
</dependency>

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

<!--junit测试包-->
<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.11</version>
  <scope>test</scope>
</dependency>

<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.12</version>
  <scope>compile</scope>
</dependency>

刷新Maven！

5.3 开发实体类(entity)

  • User.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
package com.study.entity;

import java.io.Serializable;
import java.util.Date;

/**
 * @ClassName User
 * @Description 用户实体类，对应mybatis数据库中表t_user
 * @Author chabai
 * @Date 2022/8/13 17:39
 * @Version 1.0
 */
public class User implements Serializable {
    private Integer id;
    private String name;
    private Date birthday;
    private Character sex;
    private String address;

    public User() {
    }

    public User(Integer id, String name, Date birthday, Character sex, String address) {
        this.id = id;
        this.name = name;
        this.birthday = birthday;
        this.sex = sex;
        this.address = address;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public Character getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

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

5.4 开发持久层(Dao)

  • IUserDao.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.study.dao;

import com.study.entity.User;

import java.util.List;

/**
 * @ClassName IUserDao
 * @Description 用户持久层接口UserDao
 * @Author chabai
 * @Date 2022/8/13 17:43
 * @Version 1.0
 */
public interface IUserDao {
    /**
     * @MethodName findAll
     * @Description 查询所有用户
     * @return: java.util.List<com.study.entity.User>
     * @Author chabai
     * @Date 17:44 2022/8/13
     */
    List<User> findAll();
}

5.5 开发mapper映射文件

  • IUserDaoMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
<?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.study.dao.IUserDao">
   <!--findAll-->
   <select id="findAll" resultType="com.study.entity.User">
     select
         id,name,birthday,sex,address
     from t_user
   </select>
 </mapper>

5.6 开发mybatis主配置文件

  • mybatis-config.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?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" >

<!--mybatis的主配置文件-->
<configuration>
    <!--配置环境
            development 测试环境（默认）
            product     生产环境
    -->
    <environments default="development">
        <!--测试环境-->
        <environment id="development">
            <!--事务管理类型：指定事务管理的方式 JDBC-->
            <transactionManager type="JDBC"/>
            <!--数据库连接相关配置，动态获取config.properties文件里的内容-->
            <!--数据源类型：POOLED 表示支持JDBC数据源连接池
                          UNPOOLED 表示不支持数据源连接池
                          JNDI 表示支持外部数据源连接池
            -->
            <dataSource type="POOLED">
                <!-- 配置连接数据库的基本信息：
                     此处使用的是MySQL数据库，使用Oracle数据库时需要修改，仔细检查各项参数是否正确，里面配置了时区、编码方式、SSL，用以防止中文查询乱码，导致查询结果为null及SSL警告等问题-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC&amp;useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <!--指定映射文件的位置：即注册mapper配置文件（mapper文件路径配置）
        注意：映射配置文件位置要和映射器位置一样，如：映射器在com.mycode.dao里，
             那么配置文件就应该在resources的com/mycode/dao目录下，否则会报
             Could not find resource com.mycode.dao.UserMapper.xml类似错误
    -->
    <mappers>
        <!--下面编写mapper映射文件↓↓↓↓↓ 参考格式：<mapper resource="dao/UserMapper.xml"/> -->
        <mapper resource="com/study/mapper/IUserDaoMapper.xml"/>
    </mappers>
</configuration>

5.7 开发测试类

  • IUserDaoTest.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
package com.study.test;

import com.study.dao.IUserDao;
import com.study.entity.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.Test;

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

/**
 * @ClassName IUserDaoTest
 * @Description TODO
 * @Author chabai
 * @Date 2022/8/13 17:58
 * @Version 1.0
 */
public class IUserDaoTest {
    /**
     * @MethodName findAllTest
     * @Description 测试查询所有
     * @Author chabai
     * @Date 18:05 2022/8/13
     */
    @Test
    public void findAllTest() throws IOException {
        //1.读取mybatis主配置文件
        InputStream is = Resources.getResourceAsStream("com/study/config/mybatis-config.xml");
        //2.创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        //3.使用工厂生产SqlSession对象
        SqlSession sqlSession = factory.openSession();
        //4.使用SqlSession创建Dao接口的代理对象
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        //5.使用代理对象执行方法
        List<User> userList = userDao.findAll();
        for (User user : userList) {
            System.out.println("user = " + user);
        }
        //释放资源
        sqlSession.close();
        is.close();
    }
}

测试结果：

1
2
3
4
5
6
user = User{id=1, name='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex=男, address='北京'}
 user = User{id=2, name='小二王', birthday=Fri Mar 02 15:09:37 CST 2018, sex=女, address='北京金燕龙'}
 user = User{id=43, name='小二王', birthday=Sun Mar 04 11:34:34 CST 2018, sex=女, address='北京金燕龙'}
 user = User{id=45, name='传智播客', birthday=Sun Mar 04 12:04:06 CST 2018, sex=男, address='北京金燕龙'}
 user = User{id=46, name='老王', birthday=Wed Mar 07 17:37:26 CST 2018, sex=男, address='北京'}
 user = User{id=48, name='小马宝莉', birthday=Thu Mar 08 11:44:00 CST 2018, sex=女, address='北京修正'}

5.8 最终项目结构

image-20240327234017319

5.9 测试增删改基本操作

  • IUserDao.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
package com.study.dao;
 
 import com.study.entity.User;
 import org.apache.ibatis.annotations.Param;
 
 import java.util.List;
 
 /**
  * @ClassName IUserDao
  * @Description TODO
  * @Author chabai
  * @Date 2022/8/13 20:16
  * @Version 1.0
  */
 public interface IUserDao {
     /**
      * @MethodName findAll
      * @Description 查询所有
      * @return: java.util.List<com.study.entity.User>
      * @Author chabai
      * @Date 20:31 2022/8/13
      */
     List<User> findAll();
 
     /**
      * @MethodName findById
      * @Description 根据Id查询
      * @param: id
      * @return: com.study.entity.User
      * @Author chabai
      * @Date 20:32 2022/8/13
      */
     User findById(Integer id);
 
     /**
      * @MethodName findByIdAndName
      * @Description 多参数查询
      * @param: id
      * @param: name
      * @return: com.study.entity.User
      * @Author chabai
      * @Date 20:37 2022/8/13
      */
     User findByIdAndName(@Param("id") Integer id,@Param("name")String name);
 
     /**
      * @MethodName add
      * @Description 新增用户
      * @param: user
      * @return: java.lang.Integer
      * @Author chabai
      * @Date 20:45 2022/8/13
      */
     Integer add(User user);
 
     /**
      * @MethodName deleteById
      * @Description 根据id删除用户
      * @param: id
      * @return: java.lang.Integer
      * @Author chabai
      * @Date 21:05 2022/8/13
      */
     Integer deleteById(Integer id);
 
     /**
      * @MethodName updateById
      * @Description 根据id修改用户
      * @param: id
      * @return: java.lang.Integer
      * @Author chabai
      * @Date 21:09 2022/8/13
      */
     Integer update(User user);
 }
  • IUserDaoMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
<?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.study.dao.IUserDao">
    <!--findAll-->
  <select id="findAll" resultType="com.study.entity.User">
    select
        id,name,birthday,sex,address
    from t_user
  </select>

    <!--findById-->
    <select id="findById" parameterType="Integer" resultType="com.study.entity.User">
        select
            id,name,birthday,sex,address
        from t_user
        where id=#{id}
    </select>

    <!--findByIdAndName-->
    <select id="findByIdAndName" resultType="com.study.entity.User">
        select
            id,name,birthday,sex,address
        from t_user
        where id=#{id} and name=#{name}
    </select>

    <!--add-->
    <insert id="add" parameterType="com.study.entity.User" >
        insert into t_user values(#{id},#{name},#{birthday},#{sex},#{address})
    </insert>

    <!--deleteById-->
    <delete id="deleteById" parameterType="Integer" >
        delete from t_user where id=#{id}
    </delete>

    <!--update-->
    <update id="update" parameterType="com.study.entity.User">
        update t_user set name=#{name},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
    </update>
</mapper>
  • IUserDaoTest.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
package com.study.test;

import com.study.dao.IUserDao;
import com.study.entity.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.Test;

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

/**
 * @ClassName IUserDaoTest
 * @Description TODO
 * @Author chabai
 * @Date 2022/8/13 20:18
 * @Version 1.0
 */
public class IUserDaoTest {
    /**
     * @MethodName findAllTest
     * @Description 测试查询所有
     * @Author chabai
     * @Date 20:59 2022/8/13
     */
    @Test
    public void findAllTest() throws IOException {
        InputStream is = Resources.getResourceAsStream("com/study/config/mybatis-config.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        SqlSession sqlSession = factory.openSession();
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        List<User> userList = userDao.findAll();
        for (User user : userList) {
            System.out.println("user = " + user);
        }
        //释放资源
        sqlSession.close();
        is.close();
    }

    /**
     * @MethodName findByIdTest
     * @Description 根据id查询
     * @Author chabai
     * @Date 20:59 2022/8/13
     */
    @Test
    public void findByIdTest() throws IOException {
        InputStream is = Resources.getResourceAsStream("com/study/config/mybatis-config.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        SqlSession sqlSession = factory.openSession();
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        User user = userDao.findById(1);
        System.out.println("user = " + user);
    }

    /**
     * @MethodName findByIdAndNameTest
     * @Description 根据id和name查询
     * @Author chabai
     * @Date 20:59 2022/8/13
     */
    @Test
    public void findByIdAndNameTest() throws IOException {
        InputStream is = Resources.getResourceAsStream("com/study/config/mybatis-config.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        SqlSession sqlSession = factory.openSession();
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        User user = userDao.findByIdAndName(2, "小二王");
        System.out.println("user = " + user);
    }

    /**
     * @MethodName addTest
     * @Description 新增用户
     * @Author chabai
     * @Date 21:00 2022/8/13
     */
    @Test
    public void addTest() throws IOException {
        SqlSession sqlSession = null;
        try {
            InputStream is = Resources.getResourceAsStream("com/study/config/mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(is);
            sqlSession = factory.openSession();
            IUserDao userDao = sqlSession.getMapper(IUserDao.class);
            Integer n = userDao.add(new User(9, "皮卡丘", new Date(), '女', "黑龙江"));
            System.out.println("受影响的行数 = " + n);
            mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
            sqlSession.commit();//新增需要提交事务
        } catch (IOException e) {
            e.printStackTrace();
            sqlSession.rollback();;
        }
    }

    /**
     * @MethodName deleteByIdTest
     * @Description 根据id删除
     * @Author chabai
     * @Date 21:11 2022/8/13
     */
    @Test
    public void deleteByIdTest() {
        SqlSession sqlSession = null;
        try {
            InputStream is = Resources.getResourceAsStream("com/study/config/mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(is);
            sqlSession = factory.openSession();
            IUserDao userDao = sqlSession.getMapper(IUserDao.class);
            Integer n = userDao.deleteById(7);
            System.out.println("受影响的行数 = " + n);
            //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
            sqlSession.commit();//新增需要提交事务
        } catch (IOException e) {
            e.printStackTrace();
            sqlSession.rollback();
        }
    }

    /**
     * @MethodName updateByIdTest
     * @Description 根据id修改
     * @Author chabai
     * @Date 21:11 2022/8/13
     */
    @Test
    public void updateTest() {
        SqlSession sqlSession = null;
        try {
            InputStream is = Resources.getResourceAsStream("com/study/config/mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(is);
            sqlSession = factory.openSession();
            IUserDao userDao = sqlSession.getMapper(IUserDao.class);
            Integer n = userDao.update(new User(3, "喜洋洋", new Date(), '男', "羊村"));
            System.out.println("受影响的行数 = " + n);
            //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
            sqlSession.commit();//新增需要提交事务
        } catch (IOException e) {
            e.printStackTrace();
            sqlSession.rollback();
        }
    }
}

5.10 MybatisUtil封装

  • MyBatisUtil.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
package com.study.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.InputStream;

/**
 * @ClassName MybatisUtil
 * @Description TODO
 * @Author chabai
 * @Date 2022/8/13 21:22
 * @Version 1.0
 */
public class MybatisUtil {
    //声明工厂
    private static SqlSessionFactory sqlSessionFactory;

    //使用ThreadLocal保证service与dao层session一致
    private static final ThreadLocal<SqlSession> t = new ThreadLocal<>();

    //静态块，保证只加载一次
    static {
        try {
            InputStream is = Resources.getResourceAsStream("com/study/config/mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * @MethodName getSession
     * @Description 获得SqlSession对象
     * @return: org.apache.ibatis.session.SqlSession
     * @Author chabai
     * @Date 21:31 2022/8/13
     */
    public static SqlSession getSession(){
        SqlSession sqlSession = t.get();
        if(sqlSession==null){
            sqlSession = sqlSessionFactory.openSession();
            t.set(sqlSession);
        }
        return sqlSession;
    }

    /**
     * @MethodName close
     * @Description 关闭SqlSession对象
     * @Author chabai
     * @Date 21:34 2022/8/13
     */
    public static void close(){
        SqlSession sqlSession = t.get();
        if(sqlSession!=null){
            sqlSession.close();
            t.remove();
        }
    }

    /**
     * @MethodName commit
     * @Description 提交事务
     * @Author chabai
     * @Date 21:35 2022/8/13
     */
    public static void commit(){
        getSession().commit();
        close();
    }

    /**
     * @MethodName rollback
     * @Description 回滚事务
     * @Author chabai
     * @Date 21:36 2022/8/13
     */
    public static void rollback(){
        getSession().rollback();
        close();
    }
}
  • IUserDaoTest2.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
package com.study.test;

import com.study.dao.IUserDao;
import com.study.entity.User;
import com.study.utils.MybatisUtil;
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.Date;
import java.util.List;

/**
 * @ClassName IUserDaoTest
 * @Description TODO
 * @Author chabai
 * @Date 2022/8/13 20:18
 * @Version 1.0
 */
public class IUserDaoTest2 {
    /**
     * @MethodName findAllTest
     * @Description 测试查询所有
     * @Author chabai
     * @Date 20:59 2022/8/13
     */
    @Test
    public void findAllTest() throws IOException {
        SqlSession sqlSession = MybatisUtil.getSession();
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        List<User> userList = userDao.findAll();
        for (User user : userList) {
            System.out.println("user = " + user);
        }
        sqlSession.close();//释放资源
    }

    /**
     * @MethodName findByIdTest
     * @Description 根据id查询
     * @Author chabai
     * @Date 20:59 2022/8/13
     */
    @Test
    public void findByIdTest() throws IOException {
        SqlSession sqlSession = MybatisUtil.getSession();
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        User user = userDao.findById(1);
        System.out.println("user = " + user);
        sqlSession.close();//释放资源
    }

    /**
     * @MethodName findByIdAndNameTest
     * @Description 根据id和name查询
     * @Author chabai
     * @Date 20:59 2022/8/13
     */
    @Test
    public void findByIdAndNameTest() throws IOException {
        SqlSession sqlSession = MybatisUtil.getSession();
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        User user = userDao.findByIdAndName(2, "小二王");
        System.out.println("user = " + user);
        sqlSession.close();//释放资源
    }

    /**
     * @MethodName addTest
     * @Description 新增用户
     * @Author chabai
     * @Date 21:00 2022/8/13
     */
    @Test
    public void addTest() throws IOException {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSession();
            IUserDao userDao = sqlSession.getMapper(IUserDao.class);
            Integer n = userDao.add(new User(10, "熊大", new Date(), '男', "哈尔滨"));
            System.out.println("受影响的行数 = " + n);
            mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
            sqlSession.commit();//新增需要提交事务
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();;
        }
    }

    /**
     * @MethodName deleteByIdTest
     * @Description 根据id删除
     * @Author chabai
     * @Date 21:11 2022/8/13
     */
    @Test
    public void deleteByIdTest() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSession();
            IUserDao userDao = sqlSession.getMapper(IUserDao.class);
            Integer n = userDao.deleteById(10);
            System.out.println("受影响的行数 = " + n);
            //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
            sqlSession.commit();//新增需要提交事务
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }
    }

    /**
     * @MethodName updateByIdTest
     * @Description 根据id修改
     * @Author chabai
     * @Date 21:11 2022/8/13
     */
    @Test
    public void updateTest() {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtil.getSession();
            IUserDao userDao = sqlSession.getMapper(IUserDao.class);
            Integer n = userDao.update(new User(10, "美羊羊", new Date(), '女', "羊村"));
            System.out.println("受影响的行数 = " + n);
            //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
            sqlSession.commit();//新增需要提交事务
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }
    }
}

5.11 优化后项目结构

image-20240327234340765

6.Mybatis中增删改查使用

1.查(select)

mybatis数据库中表student：

image-20240327234646415

image-20240327234658012

java：entity.Student.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
package entity;

/**
 * @ClassName Student
 * @Description TODO
 * @Author chabai
 * @Date 2022/4/7 9:17
 * @Version 1.0
 */
public class Student {
    private int id;
    private String name;
    private int age;

    public Student() {
    }

    public Student(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
   }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

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

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

java：dao.StudentDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package dao;
 
 import entity.Student;
 import org.apache.ibatis.annotations.Param;
 
 import java.util.List;
 
 /**
  * @ClassName StudentDAO
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/7 9:18
  * @Version 1.0
  */
 public interface StudentDAO {
     //根据id查询学生
     public Student findById(int id);
 
     //根据name查询学生
     public Student findByName(String name);
 
     //多参数查询（注意接口中的参数必须使用@Param注解绑定）：根据id和name查询学生
     public Student findByIdAndName(@Param("id")int id,@Param("name")String name);
     
     //查询所有
     public List<Student> findAll();
 
 }

resources: dao/StudentDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<?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为映射的根节点，用来管理DAO接口
     namespace指定DAO接口的完整类名，表示mapper配置文件管理哪个DAO接口(包.接口名)
     mybatis会依据这个接口动态创建一个实现类去实现这个接口，而这个实现类是一个Mapper对象
  -->
 <mapper namespace="dao.StudentDAO">
     <!--
         id = "接口中的方法名"
         parameterType = "接口中传入方法的参数类型"
         resultType = "返回实体类对象：包.类名"  处理结果集 自动封装
         注意:sql语句后不要出现";"号
             查询：select标签
             增加：insert标签
             修改：update标签
             删除：delete标签
     -->
     <!--根据id查询学生-->
     <select id="findById" parameterType="int" resultType="entity.Student">
         select id,name,age from student where id=#{id}
     </select>
 
     <!--根据name查询学生-->
     <select id="findByName" parameterType="String" resultType="entity.Student">
         select id,name,age from student where name=#{name}
     </select>
 
     <!--多参数查询（注意接口中的参数必须使用@Param注解绑定）：根据id和name查询学生，select标签中parameterType省略不写-->
     <select id="findByIdAndName" resultType="entity.Student">
         select id,name,age from student where id=#{id} and name=#{name}
     </select>
 
     <!--查询所有：无论查询一个还是多个，最终resultType的值都是要封装的实体类全名（包名.类名）-->
     <select id="findAll" resultType="entity.Student">
         select id,name,age from student
     </select>
 
 </mapper>

resources: mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?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" >
 <!--配置mybatis环境-->
 <configuration>
     <!--配置连接使用的相关参数
         default为默认使用的环境：development 测试环境
                               product     生产环境
     -->
     <environments default="development">
         <!--测试环境-->
         <environment id="development">
             <!--事务管理类型：指定事务管理的方式 JDBC-->
             <transactionManager type="JDBC"/>
             <!--数据库连接相关配置，动态获取config.properties文件里的内容-->
             <!--数据源类型：POOLED 表示支持JDBC数据源连接池
                           UNPOOLED 表示不支持数据源连接池
                           JNDI 表示支持外部数据源连接池
             -->
             <dataSource type="POOLED">
                 <!--此处使用的是MySQL数据库，使用Oracle数据库时需要修改，仔细检查各项参数是否正确-->
                 <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                 <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC&amp;useSSL=false"/>
                 <property name="username" value="root"/>
                 <property name="password" value="123456"/>
             </dataSource>
         </environment>
     </environments>
 
     <!--注册mapper配置文件（mapper文件路径配置）
         注意：映射配置文件位置要和映射器位置一样，如：映射器在com.mycode.dao里，
         那么配置文件就应该在resources的com/mycode/dao目录下，否则会报
         Could not find resource com.mycode.dao.UserMapper.xml类似错误
     -->
     <mappers>
         <!--下面编写mapper映射文件↓↓↓↓↓ 参考格式：<mapper resource="mapper/UserMapper.xml"/> -->
         <mapper resource="dao/StudentDAOMapper.xml"/>
     </mappers>
 
 </configuration>

java：test.StudentDAOTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
package test;
  
  import dao.StudentDAO;
  import entity.Student;
  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.Reader;
  import java.util.List;
  
  /**
   * @ClassName StudentDAOTest
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/7 9:26
   * @Version 1.0
   */
  public class StudentDAOTest {
      //根据id查询
      @Test
      public void findByIdTest() throws IOException {
          Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
          SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
          SqlSession session = sessionFactory.openSession();
          StudentDAO studentDAO = session.getMapper(StudentDAO.class);
          Student student = studentDAO.findById(1);
          System.out.println(student);
      }
  
      //根据name查询
      @Test
      public void findByNameTest() throws IOException {
          Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
          SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
          SqlSession session = sessionFactory.openSession();
          StudentDAO studentDAO = session.getMapper(StudentDAO.class);
          Student student = studentDAO.findByName("张三");
          //Student student = studentDAO.findByName("zhangsan");
          System.out.println(student);
      }
  
      //根据id和name查询
      @Test
      public void findByIdAndNameTest() throws IOException {
          Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
          SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
          SqlSession session = sessionFactory.openSession();
          StudentDAO studentDAO = session.getMapper(StudentDAO.class);
          Student student = studentDAO.findByIdAndName(1,"张三");
          //Student student = studentDAO.findByIdAndName(2,"zhangsan");
          System.out.println(student);
      }
  
      //查询所有
      @Test
      public void findAllTest() throws IOException {
          Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
          SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
          SqlSession session = sessionFactory.openSession();
          StudentDAO studentDAO = session.getMapper(StudentDAO.class);
          List<Student> students= studentDAO.findAll();
          for (Student student : students) {
              System.out.println(student);
          }
      }
  }

测试结果：

（1）根据id查询

1
Student{id=1, name='张三', age=15}

（2）根据id和name查询

1
Student{id=1, name='张三', age=15}

（3）查询所有

1
2
3
4
5
Student{id=1, name='张三', age=15}
Student{id=2, name='zhangsan', age=20}
Student{id=3, name='李四', age=25}
Student{id=4, name='王五', age=30}
Student{id=5, name='赵六', age=35}

小结
1.单个参数查询

a. 查询结果自动封装为实体对象，要求查询结果的列名必须与实体中属性名一致，实体中属性一定要有set、get方法，实体必须存在无参数构造；

b. 当查询结果与实体类中的属性名不一致时，可使用数据库别名的方式解决；

2.多参数查询

a. 在使用多个参数时，select标签中的parameterType属性省略不写；

b. 在select标签内获取多个参数的值，要求接口中的参数必须使用@Param注解绑定

3.查询所有

a. select标签中的parameterType属性省略不写

b. 无论是查询一个还是查询多个，最终resultType的值都是要封装的实体类全名（包名.类名）

c. 当进行单表简单查询时且返回值类型是基本类型时，一般尽量使用resultType；当进行表关联查询时，或者说xml中定义了相关的resultMap标签，那么就一般尽量使用resultMap；且resultType和resultMap不能同时使用。

4.注意事项

a. mybatis报错：Could not find resource com.study.dao.UserMapper.xml

可能原因：

映射配置文件位置要与映射器位置不一致，如：映射器在java下面的com.study.dao里，那么配置文件就应该在resources下面的com/study/dao目录下，两者要对应起来，否则找不到。注意mappers标签里面的mapper中resource内的层级结构用/而不使用.，例如com/study/dao/UserMapper.xml而不是com.study.dao.UserMapper.xml。
b. mybatis使用中文作为查询条件查询时，数据库有结果，但查询结果为null

可能原因：

1.中文在查询时变成乱码，导致查询不到结果，解决办法是在数据库连接时配置中文查询 characterEncoding=utf-8，具体使用时添加”&characterEncoding=utf-8”或者”& amp;characterEncoding=utf-8”(提示：&后面多加了个空格使用时要删掉，此处使用时存在转义)

2.java实体类的属性名与查询结果的列名（表的字段名）不一致，没有一一对应，表字段名太长时使用别名

3.sql语句有问题，仔细检查后修改

c. 从数据库中进行查询或者进行其他操作时控制台会出现以下警告：WARN: Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
可能原因：

1.需要在配置文件中连接数据库的url后面添加”&useSSL=false”，注意有时候要考虑转义字符，此时要添加”& amp;useSSL=false”(提示：&后面多加了个空格使用时要删掉，此处使用时存在转义)

2.如果上面配置正确，则可能是连接数据库时的驱动版本问题，在pom.xml中更换成其他版本的依赖即可。

完整的mysql数据库url value：

1
jdbc:mysql://localhost:3306/mybatisuseUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC&amp;useSSL=false

2.增(insert)

StudentDAO.java

1
2
//插入学生信息
public void insert(Student student);

StudentDAOMapper.xml

1
2
3
4
<!--插入学生信息-->
<insert id="insert" parameterType="entity.Student">
    insert into student values(#{id},#{name},#{age})
</insert>

StudentDAOTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
//插入学生信息
     @Test
     public void insertTest() throws IOException {
         SqlSession session = null;
         try {
             Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
             SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
             session = sessionFactory.openSession();
             StudentDAO studentDAO = session.getMapper(StudentDAO.class);
             studentDAO.insert(new Student(5,"小猪猪",28));
             //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
             session.commit();
         } catch (IOException e) {
             e.printStackTrace();
             //失败回滚
             session.rollback();
         }
     }

3.删(delete)

StudentDAO.java

1
2
//根据id删除学生信息
 public void delete(int id);

StudentDAOMapper.xml

1
2
3
4
<!--根据id删除学生信息-->
<delete id="delete" parameterType="int">
    delete from student where id=#{id}
</delete>

StudentDAOTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
//根据id删除学生信息
      @Test
      public void deleteTest() throws IOException {
          SqlSession session = null;
          try {
              Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
              SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
              session = sessionFactory.openSession();
              StudentDAO studentDAO = session.getMapper(StudentDAO.class);
              studentDAO.delete(5);
              //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
              session.commit();
          } catch (IOException e) {
              e.printStackTrace();
              //失败回滚
              session.rollback();
          }
      }

4.改(update)

StudentDAO.java

1
2
//更新学生信息
  public void update(Student student);

StudentDAOMapper.xml

1
2
3
4
<!--更新学生信息-->
  <update id="update" parameterType="entity.Student">
      update student set name=#{name},age=#{age} where id=#{id};
  </update>

StudentDAOTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
//更新学生信息
      @Test
      public void updateTest() throws IOException {
          SqlSession session = null;
          try {
              Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
              SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
              session = sessionFactory.openSession();
              StudentDAO studentDAO = session.getMapper(StudentDAO.class);
              studentDAO.update(new Student(2,"小呆呆",18));
              //mybatis执行增删改时默认事务提交方式是手动的（setAutoCommit(false)），此处需要进行提交
              session.commit();
          } catch (IOException e) {
              e.printStackTrace();
              //失败回滚
              session.rollback();
          }
      }

小结

mybatis在进行增删改操作时默认事务是以手动方式提交的（setAutoCommit(false)），此时需要进行手动提交（session.commit()），否则不能使数据库内容发生改变。

5.MybatisUtil封装

image-20240328135640331

具体实现：

MybatisUtil.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
package 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.Reader;
  
 /**
  * @ClassName MybatisUtil
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/8 10:01
  * @Version 1.0
  */
 public class MybatisUtil {
     //工厂
     private static SqlSessionFactory sqlSessionFactory;
  
     //ThreadLocal 保证service与dao层session一致
     private static final ThreadLocal<SqlSession> t = new ThreadLocal<SqlSession>();
  
     static {
         try {
             Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
             sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
         } catch (IOException e) {
             e.printStackTrace();
         }
     }
  
     /**
      * @MethodName getSqlSession
      * @Description 返回sqlSession对象
      * @return: org.apache.ibatis.session.SqlSession
      * @Author chabai
      * @Date 2022/4/8 10:06
      */
     public static SqlSession getSqlSession(){
         SqlSession sqlSession = t.get();
         if(sqlSession == null){
             sqlSession = sqlSessionFactory.openSession();
             t.set(sqlSession);
         }
         return sqlSession;
     }
  
     /**
      * @MethodName close
      * @Description 关闭sqlSession对象
      * @Author chabai
      * @Date 2022/4/8 10:09
      */
     public static void close(){
         SqlSession sqlSession = t.get();
         if(sqlSession != null){
             sqlSession.close();
             t.remove();
         }
     }
  
     /**
      * @MethodName commit
      * @Description 提交事务
      * @Author chabai
      * @Date 2022/4/8 10:13
      */
     public static void commit(){
         getSqlSession().commit();
         close();
     }
  
     /**
      * @MethodName rollback
      * @Description 回滚事务
      * @Author chabai
      * @Date 2022/4/8 10:14
      */
     public static void rollback(){
         getSqlSession().rollback();
         close();
     }
  
 }

StudentDAOTest2.java（优化StudentDAOTest）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
package test;
   
  import dao.StudentDAO;
  import entity.Student;
  import org.apache.ibatis.session.SqlSession;
  import org.junit.Test;
  import util.MybatisUtil;
   
  import java.util.List;
   
  /**
   * @ClassName StudentDAOTest2
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/8 10:15
   * @Version 1.0
   */
  public class StudentDAOTest2 {
      /**
       * @MethodName findByIdTest
       * @Description 测试根据id查询学生信息
       * @Author chabai
       * @Date 2022/4/8 10:40
       */
      @Test
      public void findByIdTest(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
          Student student = studentDAO.findById(1);
          System.out.println(student);
          sqlSession.close();
      }
   
      /**
       * @MethodName findByNameTest
       * @Description 测试根据name查询学生信息
       * @Author chabai
       * @Date 2022/4/8 10:43
       */
      @Test
      public void findByNameTest(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
          Student student = studentDAO.findByName("小呆呆");
          System.out.println(student);
          sqlSession.close();
      }
   
      /**
       * @MethodName findByIdAndNameTest
       * @Description 测试根据id和name查询学生信息
       * @Author chabai
       * @Date 2022/4/8 10:44
       */
      @Test
      public void findByIdAndNameTest(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
          Student student = studentDAO.findByIdAndName(1, "张三");
          System.out.println(student);
          sqlSession.close();
      }
   
      /**
       * @MethodName findAllTest
       * @Description 测试查询所有
       * @Author chabai
       * @Date 2022/4/8 10:47
       */
      @Test
      public void findAllTest(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
          List<Student> students = studentDAO.findAll();
          for (Student student : students) {
              System.out.println(student);
          }
          sqlSession.close();
      }
   
      /**
       * @MethodName updateTest
       * @Description 测试更新数据
       * @Author chabai
       * @Date 2022/4/8 10:49
       */
      @Test
      public void updateTest(){
          SqlSession sqlSession = null;
          try {
              sqlSession = MybatisUtil.getSqlSession();
              StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
              studentDAO.update(new Student(3,"光头强",60));
              sqlSession.commit();
          } catch (Exception e) {
              e.printStackTrace();
              sqlSession.rollback();
          }
          sqlSession.close();
      }
   
      /**
       * @MethodName deleteTest
       * @Description 测试根据id删除
       * @Author chabai
       * @Date 2022/4/8 10:57
       */
      @Test
      public void deleteTest(){
          SqlSession sqlSession = null;
          try {
              sqlSession = MybatisUtil.getSqlSession();
              StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
              studentDAO.delete(4);
              sqlSession.commit();
          } catch (Exception e) {
              e.printStackTrace();
              sqlSession.rollback();
          }
          sqlSession.close();
      }
   
      /**
       * @MethodName insertTest
       * @Description 测试插入学生信息
       * @Author chabai
       * @Date 2022/4/8 11:00
       */
      @Test
      public void insertTest(){
          SqlSession sqlSession = null;
          try {
              sqlSession = MybatisUtil.getSqlSession();
              StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
              studentDAO.insert(new Student(4,"喜洋洋",6));
              sqlSession.commit();
          } catch (Exception e) {
              e.printStackTrace();
              sqlSession.rollback();
          }
          sqlSession.close();
      }
  }

6.insert、update细节

image-20240328135825004

注意：

Mybatis框架在执行插入（insert）、更新（update）操作时，默认不允许插入或修改NULL值到数据库中，解决办法：

1.尝试设置一个值进去，实质并没有解决问题

2.再取值语句中设置一个jdbcType类型

例如：#{age,jdbcType=INTEGER}

​ #{name,jdbcType=VARCHAR}

​ #{birthday,jdbcType=DATE}

附：Mybatis中支持的jdbcType

image-20240328135941526

7. 总结

image-20240328140005774

7.log4j使用

image-20240328140307750

相关jar包：

1
2
3
4
5
6
7
8
9
10
<dependency>
   <groupId>org.apache.logging.log4j</groupId>
   <artifactId>log4j-core</artifactId>
   <version>2.14.1</version>
 </dependency>
 <dependency>
   <groupId>org.slf4j</groupId>
   <artifactId>slf4j-log4j12</artifactId>
   <version>1.7.25</version>
 </dependency>

视频中log4j.properties

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
## 根日志
## 日志级别
log4j.rootLogger=ERROR,stdout
## 输出位置
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
## 布局
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
## 格式
log4j.appender.stdout.layout.conversionPattern=[%p] %d{yyyy-MM-dd} %t %c %m%n

## 子日志
## 日志级别
log4j.logger.dao=DEBUG
## 监听spring框架的日志级别
log4j.logger.org.springframework=ERROR

StudentDAOTest2.java运行findByIdTest结果

1
2
3
4
5
[DEBUG] 2022-04-12 main dao.StudentDAO.findById ==>  Preparing: select id,name,age from student where id=? 
[DEBUG] 2022-04-12 main dao.StudentDAO.findById ==> Parameters: 1(Integer)
[DEBUG] 2022-04-12 main dao.StudentDAO.findById <==      Total: 1
Student{id=1, name='张三', age=15}
Student{id=1, name='张三', age=15}

（待完善）若进行如下细节设置，测试还存在问题：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
### 设置###
 log4j.rootLogger = debug,stdout,D,E
  
 ### 输出信息到控制抬 ###
 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 = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n
  
 ### 输出DEBUG级别以上的日志到=D://logs/debug.log ###
 log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
 log4j.appender.D.File = D://Software_Development/IDEA_code/logs/debug.log           //日志输出路径 可更改
 log4j.appender.D.Append = true
 log4j.appender.D.Threshold = DEBUG
 log4j.appender.D.layout = org.apache.log4j.PatternLayout
 log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n
  
 ### 输出ERROR级别以上的日志到=E://logs/error.log ###
 log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
 log4j.appender.E.File =D://Software_Development/IDEA_code/logs/error.log
 log4j.appender.E.Append = true
 log4j.appender.E.Threshold = ERROR
 log4j.appender.E.layout = org.apache.log4j.PatternLayout
 log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

测试代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package log4j;
  
 import org.apache.log4j.Logger;
  
 /**
  * @ClassName TestLog4j
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/9 22:00
  * @Version 1.0
  */
 public class TestLog4j {
     private static final Logger logger = Logger.getLogger(Logger.class);
     public static void main(String[] args) {
         //记录info级别的信息
         logger.info("这是info级别的信息");
         //记录debug级别的信息
         logger.debug("这是debug级别的信息");
         //记录error级别的信息
         logger.error("这是error级别的信息");
     }
 }

运行结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
log4j:ERROR setFile(null,true) call failed.
 java.io.FileNotFoundException: D:\Software_Development\IDEA_code\logs\debug.log           \ÈÕÖ¾Êä³ö·¾¶ ¿É¸ü¸Ä (系统找不到指定的路径。)
     at java.io.FileOutputStream.open0(Native Method)
     at java.io.FileOutputStream.open(FileOutputStream.java:270)
     at java.io.FileOutputStream.<init>(FileOutputStream.java:213)
     at java.io.FileOutputStream.<init>(FileOutputStream.java:133)
     at org.apache.log4j.FileAppender.setFile(FileAppender.java:294)
     at org.apache.log4j.FileAppender.activateOptions(FileAppender.java:165)
     at org.apache.log4j.DailyRollingFileAppender.activateOptions(DailyRollingFileAppender.java:223)
     at org.apache.log4j.config.PropertySetter.activate(PropertySetter.java:307)
     at org.apache.log4j.config.PropertySetter.setProperties(PropertySetter.java:172)
     at org.apache.log4j.config.PropertySetter.setProperties(PropertySetter.java:104)
     at org.apache.log4j.PropertyConfigurator.parseAppender(PropertyConfigurator.java:842)
     at org.apache.log4j.PropertyConfigurator.parseCategory(PropertyConfigurator.java:768)
     at org.apache.log4j.PropertyConfigurator.configureRootCategory(PropertyConfigurator.java:648)
     at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurator.java:514)
     at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurator.java:580)
     at org.apache.log4j.helpers.OptionConverter.selectAndConfigure(OptionConverter.java:526)
     at org.apache.log4j.LogManager.<clinit>(LogManager.java:127)
     at org.apache.log4j.Logger.getLogger(Logger.java:117)
     at log4j.TestLog4j.<clinit>(TestLog4j.java:13)
 log4j:ERROR Either File or DatePattern options are not set for appender [D].
 [INFO ] 2022-04-09 23:05:48,318 method:log4j.TestLog4j.main(TestLog4j.java:17)
 这是info级别的信息
 [DEBUG] 2022-04-09 23:05:48,320 method:log4j.TestLog4j.main(TestLog4j.java:19)
 这是debug级别的信息
 [ERROR] 2022-04-09 23:05:48,320 method:log4j.TestLog4j.main(TestLog4j.java:21)
 这是error级别的信息

image-20240328140540198

8.排序（order by）

sql语句：

格式：select 字段1，字段2，… from 表名 order by 字段名 asc/desc
其中，asc表示升序（默认的排序方式，可省略不写），desc表示降序
例：SELECT id,name,age FROM student ORDER BY name
查询结果：

image-20240328140733868

9.分页查询(limit)

分页前一般先排序，这里以age进行降序排列，每条显示5条数据为例，进行说明

降序排列：SELECT id,NAME,age FROM student ORDER BY age DESC

image-20240328140816732

语法：limit 起始值 页面的大小
格式：select 字段1，字段2，… from 表名 order by 字段名 limit (n-1)*pageSize,pageSize

其中，pageSize表示页面大小，(n-1)pageSize表示起始值，n表示当前页，总页数=数据总数/页面大小

以每页显示5条数据为例
查询第1页 (1-1)5=0,5 第1页从0开始，范围为0-4，对应第1到第5条数据
SELECT id,name,age FROM student ORDER BY age DESC LIMIT 0,5

image-20240328140909939

查询第2页 (2-1)*5=5,5 第2页从5开始，范围为5-9，对应第6到第10条数据
SELECT id,NAME,age FROM student ORDER BY age DESC LIMIT 5,5

image-20240328141042177

查询第3页 (3-1)*5=10,5 第3页从10开始，范围为10-14，对应第11到第15条数据
SELECT id,NAME,age FROM student ORDER BY age DESC LIMIT 10,5

image-20240328141056789

StudentDAO.java

1
2
3
4
5
6
7
8
9
10
/**
 * @MethodName selectByPage
 * @Description
 * @param: pageNow 当前页码
 * @param: pageSize 每页显示信息条数
 * @return: java.util.List<entity.Student>
 * @Author chabai
 * @Date 2022/4/10 16:16
 */
 List<Student> selectByPage(Map<String,Integer> map);

StudentDAOMapper.xml

1
2
3
4
<!--分页查询-->
  <select id="selectByPage" parameterType="map" resultType="entity.Student">
      select id,name,age from student order by age desc limit #{pageStart},#{pageSize}
  </select>

注意：limit后面只能这样传入两个参数，多加操作会报错（就目前认知来看）

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/**
   * @MethodName selectByPage
   * @Description 分页查询
   * @Author chabai
   * @Date 2022/4/10 16:23
   */
  @Test
  public void selectByPage(){
      SqlSession sqlSession = MybatisUtil.getSqlSession();
      StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
      HashMap<String,Integer> map = new HashMap<>();
      map.put("pageStart",0);//第1页从0开始，范围为0-4，对应第1到第5条数据 
      map.put("pageSize",5);
      List<Student> students1 = studentDAO.selectByPage(map);
      System.out.println("---------------第1页----------------");
      students1.forEach(student -> System.out.println(student));
      map.put("pageStart",5);//第2页从5开始，范围为5-9，对应第6到第10条数据
      map.put("pageSize",5);
      List<Student> students2 = studentDAO.selectByPage(map);
      System.out.println("---------------第2页----------------");
      students2.forEach(student -> System.out.println(student));
      map.put("pageStart",10);//第3页从10开始，范围为10-14，对应第11到第15条数据
      map.put("pageSize",5);
      List<Student> students3 = studentDAO.selectByPage(map);
      System.out.println("---------------第3页----------------");
      students3.forEach(student -> System.out.println(student));
      sqlSession.close();
  }

输出结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
---------------第1页----------------
 Student{id=13, name='慢羊羊', age=70}
 Student{id=3, name='光头强', age=37}
 Student{id=5, name='小猪猪', age=28}
 Student{id=6, name='猪猪侠', age=25}
 Student{id=9, name='熊大', age=20}
 ---------------第2页----------------
 Student{id=7, name='猪小呆', age=19}
 Student{id=2, name='小呆呆', age=18}
 Student{id=10, name='熊二', age=16}
 Student{id=1, name='张三', age=15}
 Student{id=12, name='暖羊羊', age=14}
 ---------------第3页----------------
 Student{id=11, name='沸羊羊', age=12}
 Student{id=4, name='喜洋洋', age=6}
 Student{id=8, name='皮卡丘', age=5}

10模糊查询(like)

以在name中模糊查询“猪”为例，sql语句：

– 格式：select 字段1，字段2，… from 表名 where 字段名 like ’%要查询的模糊词%‘
– %模糊词： 表示前模糊
– 模糊词%： 表示后模糊
– %模糊词%：表示中间模糊
SELECT id,name,age FROM student WHERE name LIKE ‘%猪%’

数据库中查询结果：

image-20240328141301383

StudentDAO.java

1
2
//模糊查询
  List<Student> selectByLike(String keyWords);

StudentDAOMapper.xml

1
2
3
4
<!--模糊查询-->
<select id="selectByLike" parameterType="String" resultType="entity.Student">
    select id,name,age from student where name like "%"#{keyWords}"%";
</select>

注意：

1.模糊查询时要使用“ ”将%括起来而不是’ ‘

2.查询中涉及到中文时，需要在连接数据库时配置characterEncoding=UTF-8

以上两种任一种不设置错误或设置错误均会导致查询不到结果或结果为null！！！

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
       * @MethodName selectByLikeTest
       * @Description 模糊查询
       * @Author chabai
       * @Date 2022/4/10 12:21
       */
      @Test
      public void selectByLikeTest(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
          List<Student> students = studentDAO.selectByLike("小");
          for (Student student : students) {
              System.out.println(student);
          }
      }

输出结果：

1
2
3
Student{id=5, name='小猪猪', age=28}
Student{id=6, name='猪猪侠', age=25}
Student{id=7, name='猪小呆', age=19}

11.实体类别名设置

image-20240328141533962

实体类起别名对应代码：

1
2
3
4
5
<!--给实体类起别名，方便在XxxDAOMapeer.xml文件中进行简写-->
  <typeAliases>
      <!--type为要起别名的类对应的全限定名，alias为起的别名-->
      <typeAlias type="entity.Student" alias="Student"/>
  </typeAliases>

12.引入properties配置文件

image-20240328141636567

jdbc.properties

1
2
3
4
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/mybatis
username = root
password = 123456

mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<!--配置mybatis环境-->
<configuration>
    <!--引入jdbc.properties配置文件-->
    <properties resource="jdbc.properties"/>
 
    <!--
        配置连接数据库使用的相关参数,default为默认使用的环境
        其中，development表示测试环境，可简写为dev
             produc表示生产环境，可简写为prod
    -->
    <environments default="development">
        <!--测试环境-->
        <environment id="development">
            <!--事务管理类型：指定事务管理的方式 JDBC-->
            <transactionManager type="JDBC"/>
            <!--数据库连接相关配置，动态获取config.properties文件里的内容-->
            <!--数据源类型：POOLED 表示支持JDBC数据源连接池
                          UNPOOLED 表示不支持数据源连接池
                          JNDI 表示支持外部数据源连接池
            -->
            <dataSource type="POOLED">
                <!--此处使用的是MySQL数据库，使用Oracle数据库时需要修改，仔细检查各项参数是否正确-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

13.动态sql（sql、include、if、where、choose、when、otherwise、set、trim、foreach）

image-20240502104159938

image-20240502104039411

具体实现：

t_book表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE IF NOT EXISTS `t_book`(
  `id` VARCHAR(40) PRIMARY KEY,
  `name` VARCHAR(40),
  `publishDate` DATE,
  `price` DOUBLE(7,3),
  `author` VARCHAR(50)
)ENGINE=INNODB CHARSET=utf8;

INSERT INTO `t_book` VALUES
('1','六脉神剑',SYSDATE(),120.23,'小黑'),
('2','如来神掌',SYSDATE(),110.23,'小名'),
('3','葵花宝典',SYSDATE(),80.23,'无名'),
('4','星星点灯',SYSDATE(),90.34,'小牛'),
('5','黯然销魂掌',SYSDATE(),67.23,'小伟');

Book.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
package entity;
  
 import java.util.Date;
  
 /**
  * @ClassName Book
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/12 15:25
  * @Version 1.0
  */
 public class Book {
     private String id;
     private String name;
     private Date publishDate;
     private Double price;
     private String author;
  
     @Override
     public String toString() {
         return "Book{" +
                 "id='" + id + '\'' +
                 ", name='" + name + '\'' +
                 ", publishDate=" + publishDate +
                 ", price=" + price +
                 ", author='" + author + '\'' +
                 '}';
     }
  
     public Book(String id, String name, Date publishDate, Double price, String author) {
         this.id = id;
         this.name = name;
         this.publishDate = publishDate;
         this.price = price;
         this.author = author;
     }
  
     public Book() {
     }
  
     public String getId() {
         return id;
     }
  
     public void setId(String id) {
         this.id = id;
     }
  
     public String getName() {
         return name;
     }
  
     public void setName(String name) {
         this.name = name;
     }
  
     public Date getPublishDate() {
         return publishDate;
     }
  
     public void setPublishDate(Date publishDate) {
         this.publishDate = publishDate;
     }
  
     public Double getPrice() {
         return price;
     }
  
     public void setPrice(Double price) {
         this.price = price;
     }
  
     public String getAuthor() {
         return author;
     }
  
     public void setAuthor(String author) {
         this.author = author;
     }
 }

a. sql标签

BookDAO.java

1
2
3
4
5
6
7
8
/**
   * @MethodName selectAll
   * @Description 测试查询所有
   * @return: java.util.List<entity.Book>
   * @Author chabai
   * @Date 2022/4/12 15:27
   */
  List<Book> selectAll();

BookDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<!--sql语句片段复用标签
         id：代表片段的唯一标识
 -->
 <sql id="commonFields">
     id,name,publishDate,price,author
 </sql>
  
 <!--查询所有-->
 <select id="selectAll" resultType="Book">
     /* include标签用来指定使用哪个sql片段
         refid：表示所使用片段的id
     */
     select <include refid="commonFields"/> from t_book
 </select>

TestBookDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/**
 * @MethodName testSelectAll
 * @Description 测试查询所有   
 * @Author chabai
 * @Date 2022/4/12 16:13
 */
@Test
public void testSelectAll(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BookDAO bookDAO = sqlSession.getMapper(BookDAO.class);
    List<Book> books = bookDAO.selectAll();
    books.forEach(book -> System.out.println(book));
    MybatisUtil.close();
}

b. if标签

BookDAO.java

1
2
3
4
5
6
7
8
9
/**
 * @MethodName selectByFields
 * @Description 用来测试动态sql：if、where
 * @param: book 此处一定要传入对象
 * @return: java.util.List<entity.Book>
 * @Author chabai
 * @Date 2022/4/12 16:08
 */
List<Book> selectByFields(Book book);

BookDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
<!--根据字段查询：测试动态sql：if、where-->
  <select id="selectByFields" parameterType="Book" resultType="Book">
      select <include refid="commonFields"/> from t_book
      <where>
          <if test="name!=null and name!=''">
              name=#{name}
          </if>
          <if test="author!=null and author!=''">
              and author=#{author}
          </if>
      </where>
  </select>

TestBookDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
 * @MethodName testSelectByFields
 * @Description 测试 where if
 * @Author chabai
 * @Date 2022/4/12 16:13
 */
@Test
public void testSelectByFields(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BookDAO bookDAO = sqlSession.getMapper(BookDAO.class);
    List<Book> books = bookDAO.selectByFields(new Book(null, "六脉神剑", null, null, "小黑"));
    books.forEach(book -> System.out.println("book = " + book));
}

c.choose when otherwise标签

BookDAO.java

1
2
3
4
5
6
7
8
9
/**
 * @MethodName selectByChoose
 * @Description 用来测试动态sql：choose、when、otherwise
 * @param: book
 * @return: java.util.List<entity.Book>
 * @Author chabai
 * @Date 2022/4/12 16:32
 */
List<Book> selectByChoose(Book book);

BookDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!--根据选择查询：测试动态sql：choose、when、otherwise-->
  <select id="selectByChoose" parameterType="Book" resultType="Book">
      select <include refid="commonFields"/> from t_book
      where
      <choose>
          <when test="name!=null and name!=''">
              name=#{name}
          </when>
          <when test="price!=null">
              price=#{price}
          </when>
          <otherwise>
              author=#{author}
          </otherwise>
      </choose>
  </select>

TestBookDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
 * @MethodName testSelectByChoose
 * @Description 测试 choose、when、otherwise
 * @Author chabai
 * @Date 2022/4/12 16:39
 */
@Test
public void testSelectByChoose(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BookDAO bookDAO = sqlSession.getMapper(BookDAO.class);
    //List<Book> books = bookDAO.selectByChoose(new Book(null, "六脉神剑",null , null, null));
    //List<Book> books = bookDAO.selectByChoose(new Book(null, null,null , null, "小黑"));
    List<Book> books = bookDAO.selectByChoose(new Book(null, null,null , 110.23, "小黑"));
    books.forEach(book -> System.out.println("book = " + book));
}

d. set标签

BookDAO.java

1
2
3
4
5
6
7
8
*
   * @MethodName update
   * @Description 测试set标签
   * @Author chabai
   * @Date 2022/4/12 16:59
   * @param book
   */
  void update(Book book);

BookDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
<!--测试set标签-->
 <update id="update" parameterType="Book">
     update t_book
     <set>
         <if test="name!=null and name!=''">
             name=#{name},
         </if>
         <if test="author!=null and author!=''">
             author=#{author}
         </if>
     </set>
     where id=#{id}
 </update>

TestBookDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
   * @MethodName testUpdate
   * @Description 测试 set
   * @Author chabai
   * @Date 2022/4/12 17:13
   */
  @Test
  public void testUpdate(){
      SqlSession sqlSession = MybatisUtil.getSqlSession();
      BookDAO bookDAO = sqlSession.getMapper(BookDAO.class);
      bookDAO.update(new Book("1","九阴真经",null,null,"小猫"));
      MybatisUtil.commit();
  }

e. trim标签

BookDAO.java

1
2
3
4
5
6
7
8
/**
   * @MethodName update
   * @Description 测试trim标签
   * @Author chabai
   * @Date 2022/4/12 16:59
   * @param book
   */
  void update(Book book);

BookDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<!--测试trim标签-->
  <update id="update" parameterType="Book">
      update t_book
      <!--
          prefix：加入前缀
          prefixOverrides：去掉前缀
          suffix：加入后缀
          suffixOverrides：去掉后缀
      -->
      <trim prefix="set" prefixOverrides="," suffix="where">
          <if test="name!=null and name!=''">
              name=#{name},
          </if>
          <if test="author!=null and author!=''">
              author=#{author}
          </if>
      </trim>
      id=#{id}
  </update>

TestBookDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
   * @MethodName testUpdate
   * @Description 测试 trim
   * @Author chabai
   * @Date 2022/4/12 17:13
   */
  @Test
  public void testUpdate(){
      SqlSession sqlSession = MybatisUtil.getSqlSession();
      BookDAO bookDAO = sqlSession.getMapper(BookDAO.class);
      bookDAO.update(new Book("1","九阴真经",null,null,"小猫"));
      MybatisUtil.commit();
  }

f. foreach标签

  • 批量插入数据
    • – 批量插入数据使用的sql语句：insert into 表名 (字段一,字段二,字段三) values(aaa,bbb,ccc),(ddd,eee,fff)，(ggg,hhh,lll)

BookDAO.java

1
2
3
4
5
6
7
8
/**
 * @MethodName insertAll
 * @Description 测试foreach遍历
 * @param: books
 * @Author chabai
 * @Date 2022/4/12 17:44
 */
public void insertAll(List<Book> books);

BookDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<!-- 批量插入数据 
          SELECT LAST_INSERT_ID()：获得刚insert进去记录的主键值，只适用于自增主键
          keyProperty：将查询到主键值设置到parameterType指定的对象的那个属性
          order：SELECT LAST_INSERT_ID()执行顺序，相对于insert语句来讲它的执行顺序
          resultType：指定SELECTLAST_INSERT_ID()的结果类型
          参考链接： http://www.javashuo.com/article/p-fcnlzrmg-nw.html
   
          collection:用来书写遍历集合类型
          index : 当前遍历的索引，从0开始
          item  : 当前遍历元素的别名
          open  : 在第一次遍历之前加入的语句
          close : 在最后一次遍历之后加入的语句
          separator: 在每一次遍历之后加入的语句，最后一次不加入
  -->
  <insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="true">
      <selectKey resultType="long" keyProperty="id" order="AFTER">
          SELECT LAST_INSERT_ID()
      </selectKey>
      insert into t_book
      (<include refid="commonFields"/>)
      values
      <foreach collection="list" item="book" index="index" separator=",">
          (
              #{book.id},#{book.name},#{book.publishDate},#{book.price},#{book.author}
          )
      </foreach>
  </insert>

TestBookDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
 * @MethodName testInsertBatch
 * @Description 测试foreach遍历  批量插入数据
 * @Author chabai
 * @Date 2022/4/12 17:52
 */
@Test
public void testInsertBatch(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BookDAO bookDAO = sqlSession.getMapper(BookDAO.class);
    bookDAO.insertBatch(Arrays.asList(
            new Book(UUID.randomUUID().toString(), "海边的卡夫卡", new Date(), 23.45, "村上春树"),
            new Book(UUID.randomUUID().toString(),"阿弥陀佛么么哒",new Date(),25.36,"大冰"),
            new Book(UUID.randomUUID().toString(),"兄弟",new Date(),34.56,"余华")
    ));
    MybatisUtil.commit();
}

批量更新数据

  • – 批量更新数据使用的sql语句：

    UPDATE 表名

    SET aa = CASE id

    WHEN 1 THEN ‘aaa’

    WHEN 2 THEN ‘bbb’

    WHEN 3 THEN ‘ccc’

    END　

    ,SET bb = CASE id

    WHEN 1 THEN ‘ddd’

    WHEN 2 THEN ‘eee’

    WHEN 3 THEN ‘fff’

    END

    WHERE id IN (1,2,3)

BookDAO.java

1
2
3
4
5
6
7
8
9
 
/**
 * @MethodName updateBatch
 * @Description 测试foreach遍历 批量更新数据
 * @param: books
 * @Author chabai
 * @Date 2022/4/12 17:44
 */
public void updateBatch(List<Book> books);

BookDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<!-- 批量更新数据 -->
  <update id="updateBatch">
      update t_book set
      name =
      <foreach collection="list" item="book" index="index"
               separator=" " open="case id" close="end">
          when #{book.id} then #{book.name}
      </foreach>
      ,price =
      <foreach collection="list" item="book" index="index"
               separator=" " open="case id" close="end">
      when #{book.id} then #{book.price}
      </foreach>
      ,author =
      <foreach collection="list" item="book" index="index"
               separator=" " open="case id" close="end">
          when #{book.id} then #{book.author}
      </foreach>
      where id in
      <foreach collection="list" item="book" index="index"
               separator="," open="(" close=")">
          #{book.id}
      </foreach>
  </update>

TestBookDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

/**
 * @MethodName testUpdateBatch
 * @Description 测试foreach遍历 批量更新数据
 * @Author chabai
 * @Date 2022/4/12 17:52
 */
@Test
public void testUpdateBatch(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    BookDAO bookDAO = sqlSession.getMapper(BookDAO.class);
    bookDAO.updateBatch(Arrays.asList(
            new Book("1", "喜羊羊", new Date(),11.11, "猪猪侠"),
            new Book("2","暖羊羊",new Date(),22.22,"小呆呆"),
            new Book("3","沸羊羊",new Date(),33.33,"波比")
    ));
    MybatisUtil.commit();
}

14.关联查询

1 一对一关联关系

image-20240328142833167

1.1 建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- 身份信息表
 DROP TABLE IF EXISTS `t_card`;
 CREATE TABLE IF NOT EXISTS `t_card`(
   `id` VARCHAR(36) PRIMARY KEY,
   `no` VARCHAR(18),
   `address` VARCHAR(100),
   `fork` VARCHAR(30)
 )ENGINE=INNODB CHARSET=utf8;
  
 -- 用户表
 DROP TABLE IF EXISTS `t_users`;
 CREATE TABLE IF NOT EXISTS `t_users`(
   `id` VARCHAR(36) PRIMARY KEY,
   `name` VARCHAR(40),
   `age` INTEGER,
   `bir` DATE,
   `cardId` VARCHAR(36) REFERENCES `t_card`(`id`)
 )ENGINE=INNODB CHARSET=utf8;
  
 -- 插入t_card数据
 INSERT INTO t_card VALUES
 ('1','1','北京市海淀区','汉族'),
 ('2','2','北京市昌平区','满族');
  
 -- 插入t_users数据
 INSERT INTO t_users VALUES
 ('1','小黑',15,CURDATE(),'1'),
 ('2','小白',23,CURDATE(),'2');
  
 -- 查看表中数据
 SELECT * FROM t_card;
 SELECT * FROM t_users;
  
 -- 进行连接查询
 SELECT u.`id`,u.`name`,u.`age`,u.`bir`,c.`no`,c.`address`,c.`fork` FROM t_users u
 LEFT JOIN t_card c
 ON u.`cardId`=c.`id`;
 -- 或
 SELECT u.id userId,u.name,u.age,u.bir,
        c.id cardId,c.no,c.fork,c.address
 FROM t_users u
 LEFT JOIN t_card c
 ON u.cardId=c.id;
 -- 或
 SELECT c.id cardId,c.no,c.address,c.fork,
        u.id userId,u.name,u.age,u.bir
 FROM t_card c
 LEFT JOIN t_users u
 ON c.id=u.cardId;

查询结果：

image-20240328142937810

1.2 创建实体类

User

注意：先添加User的属性、无参有参构造方法、set、get方法、toString方法，再添加Card属性以及get、set方法，防止在toString方法里面产生死环，打印时异常，栈溢出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
package entity;
  
 import java.util.Date;
  
 /**
  * @ClassName User
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/16 14:31
  * @Version 1.0
  */
 public class User {
     private String id;
     private String name;
     private Integer age;
     private Date bir;
  
     //关系属性，需要额外添加get、set方法
     //注意此属性不能出现在toString方法里面，否则会产生死环，打印时异常，栈溢出
     private Card card;
  
     public Card getCard() {
         return card;
     }
  
     public void setCard(Card card) {
         this.card = card;
     }
  
     public User(String id, String name, Integer age, Date bir) {
         this.id = id;
         this.name = name;
         this.age = age;
         this.bir = bir;
     }
  
     public User() {
     }
  
     public String getId() {
         return id;
     }
  
     public void setId(String id) {
         this.id = id;
     }
  
     public String getName() {
         return name;
     }
  
     public void setName(String name) {
         this.name = name;
     }
  
     public Integer getAge() {
         return age;
     }
  
     public void setAge(Integer age) {
         this.age = age;
     }
  
     public Date getBir() {
         return bir;
     }
  
     public void setBir(Date bir) {
         this.bir = bir;
     }
  
     @Override
     public String toString() {
         return "User{" +
                 "id='" + id + '\'' +
                 ", name='" + name + '\'' +
                 ", age=" + age +
                 ", bir=" + bir +
                 '}';
     }
 }

Card

注意：先添加Card的属性、无参有参构造方法、set、get方法、toString方法，再添加User属性以及get、set方法，防止在toString方法里面产生死环，打印时异常，栈溢出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
package entity;
  
 /**
  * @ClassName Card
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/16 14:37
  * @Version 1.0
  */
 public class Card {
     private String id;
     private String no;
     private String address;
     private String fork;
  
     //关系属性，需要额外添加get、set方法
     // 注意此属性不能出现在toString方法里面，否则会产生死环，打印时异常，栈溢出
     private User user;
  
     public User getUser() {
         return user;
     }
  
     public void setUser(User user) {
         this.user = user;
     }
  
     public Card() {
     }
  
     public Card(String id, String no, String address, String fork) {
         this.id = id;
         this.no = no;
         this.address = address;
         this.fork = fork;
     }
  
     public String getId() {
         return id;
     }
  
     public void setId(String id) {
         this.id = id;
     }
  
     public String getNo() {
         return no;
     }
  
     public void setNo(String no) {
         this.no = no;
     }
  
     public String getAddress() {
         return address;
     }
  
     public void setAddress(String address) {
         this.address = address;
     }
  
     public String getFork() {
         return fork;
     }
  
     public void setFork(String fork) {
         this.fork = fork;
     }
  
     @Override
     public String toString() {
         return "Card{" +
                 "id='" + id + '\'' +
                 ", no='" + no + '\'' +
                 ", address='" + address + '\'' +
                 ", fork='" + fork + '\'' +
                 '}';
     }
 }

1.3 创建DAO组件

UserDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package dao;
 
 import entity.User;
 
 import java.util.List;
 
 /**
  * @ClassName UserDAO
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/16 14:42
  * @Version 1.0
  */
 public interface UserDAO {
     /**
      * @MethodName selectAll
      * @Description 查询用户信息及身份信息
      * @return: java.util.List<entity.User>
      * @Author chabai
      * @Date 2022/4/16 14:43
      */
     List<User> selectAll();
 }

CardrDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package dao;
 
 import entity.Card;
 
 import java.util.List;
 
 /**
  * @ClassName CardDAO
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/16 16:03
  * @Version 1.0
  */
 public interface CardDAO {
     /**
      * @MethodName selectAll
      * @Description 查询身份信息及用户信息
      * @return: java.util.List<entity.Card>
      * @Author chabai
      * @Date 2022/4/16 16:03
      */
     List<Card> selectAll();
 }

1.4 创建mapper配置文件

UserDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<?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="dao.UserDAO">
     <!--
         处理关联关系时，resultType无法将关系属性进行自动封装，其只能处理单表简单类型（对象）
         处理关系属性赋值时，要使用resultMap
             type：封装对象类型
                   1.如果是一个对象，直接写对象的全名
                   2.如果是多个对象，同样要写泛型的类型
             id：resultMap的名字
     -->
     <resultMap id="userMap" type="entity.User">
         <!--id：用来封装外部表的主键，column：数据库中的列名（字段名），property：实体类中的属性名-->
         <id column="userId" property="id"/>
         <!--result：用来封装外部表的普通属性值，column：数据库中的列名（字段名），property：实体类中的属性名-->
         <result column="name" property="name"/>
         <result column="age" property="age"/>
         <result column="bir" property="bir"/>
  
         <!--
             关系属性封装：一对一
             association：用来处理一对一关系属性封装
             property：关系属性名
             javaType：关系属性的类型
         -->
         <association property="card" javaType="entity.Card">
             <!--规则同上-->
             <id column="cardId" property="id"/>
             <result column="no" property="no"/>
             <result column="address" property="address"/>
             <result column="fork" property="fork"/>
         </association>
     </resultMap>
  
     <!--
         selectAll
         resultMap：定义封装对象时，外部使用哪个resultMap进行封装，书写resultMap标签的id属性
     -->
     <select id="selectAll" resultMap="userMap">
         select u.id userId,u.name,u.age,u.bir,
                c.id cardId,c.no,c.fork,c.address
         from t_users u
         left join t_card c
         on u.cardId=c.id
     </select>
  
 </mapper>

CardDAOMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?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="dao.CardDAO">
     <resultMap id="cardMap" type="entity.Card">
         <id column="userId" property="id"/>
         <result column="no" property="no"/>
         <result column="address" property="address"/>
         <result column="fork" property="fork"/>
  
         <association property="user" javaType="entity.User">
             <id column="userId" property="id"/>
             <result column="name" property="name"/>
             <result column="age" property="age"/>
             <result column="bir" property="bir"/>
         </association>
     </resultMap>
  
     <!--selectAll-->
     <select id="selectAll" resultMap="cardMap">
         select c.id cardId,c.no,c.address,c.fork,
                u.id userId,u.name,u.age,u.bir
         from t_card c
         left join t_users u
         on c.id=u.cardId
     </select>
  
 </mapper>

1.5 mybatis-config.xml配置mapper

1
2
3
4
<mappers>
      <mapper resource="mapper/UserDAOMapper.xml"/>
      <mapper resource="mapper/CardDAOMapper.xml"/>
  </mappers>

1.6 测试连接查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
package test;
   
  import dao.CardDAO;
  import dao.UserDAO;
  import org.apache.ibatis.session.SqlSession;
  import org.junit.Test;
  import util.MybatisUtil;
   
  /**
   * @ClassName TestDAO
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/16 16:37
   * @Version 1.0
   */
  public class TestDAO {
      /**
       * @MethodName testUserDAO
       * @Description 测试UserDAO组件连接查询
       * @Author chabai
       * @Date 2022/4/16 16:38
       */
      @Test
      public void testUserDAO(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
          userDAO.selectAll().forEach(user -> System.out.println("user = " + user + " " + user.getCard()));
          MybatisUtil.close();
      }
   
      /**
       * @MethodName testCardDAO
       * @Description 测试CardDAO组件连接查询
       * @Author chabai
       * @Date 2022/4/16 16:39
       */
      @Test
      public void testCardDAO(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          CardDAO cardDAO = sqlSession.getMapper(CardDAO.class);
          cardDAO.selectAll().forEach(card -> System.out.println("card = " + card + " " + card.getUser()));
          MybatisUtil.close();
      }
  }

测试结果：

（1）UserDAO

1
2
3
4
5
[DEBUG] 2022-04-16 main dao.UserDAO.selectAll ==>  Preparing: select u.id userId,u.name,u.age,u.bir, c.id cardId,c.no,c.fork,c.address from t_users u left join t_card c on u.cardId=c.id 
  [DEBUG] 2022-04-16 main dao.UserDAO.selectAll ==> Parameters: 
  [DEBUG] 2022-04-16 main dao.UserDAO.selectAll <==      Total: 2
  user = User{id='1', name='小黑', age=15, bir=Sat Apr 16 00:00:00 CST 2022} Card{id='1', no='1', address='北京市海淀区', fork='汉族'}
  user = User{id='2', name='小白', age=23, bir=Sat Apr 16 00:00:00 CST 2022} Card{id='2', no='2', address='北京市昌平区', fork='满族'}

（2）CardDAO

1
2
3
4
5
[DEBUG] 2022-04-16 main dao.CardDAO.selectAll ==>  Preparing: select c.id cardId,c.no,c.address,c.fork, u.id userId,u.name,u.age,u.bir from t_card c left join t_users u on c.id=u.cardId 
  [DEBUG] 2022-04-16 main dao.CardDAO.selectAll ==> Parameters: 
  [DEBUG] 2022-04-16 main dao.CardDAO.selectAll <==      Total: 2
  card = Card{id='1', no='1', address='北京市海淀区', fork='汉族'} User{id='1', name='小黑', age=15, bir=Sat Apr 16 00:00:00 CST 2022}
  card = Card{id='2', no='2', address='北京市昌平区', fork='满族'} User{id='2', name='小白', age=23, bir=Sat Apr 16 00:00:00 CST 2022}

2 一对多关联关系

image-20240328143533538

2.1 建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 部门表
 DROP TABLE IF EXISTS t_dept;
 CREATE TABLE IF NOT EXISTS `t_dept`(
   `id` VARCHAR(36) PRIMARY KEY,
   `name` VARCHAR(40)
 )ENGINE=INNODB CHARSET=utf8;
  
 -- 员工表
 DROP TABLE IF EXISTS t_emps;
 CREATE TABLE IF NOT EXISTS `t_emps`(
   `id` VARCHAR(36) PRIMARY KEY,
   `name` VARCHAR(40),
   `age` INTEGER,
   `bir` DATE,
   `salary` DOUBLE(7,2),
   `deptId` VARCHAR(36) REFERENCES `t_dept`(`id`)
 )ENGINE=INNODB CHARSET=utf8;
  
 -- 部门测试数据
 INSERT INTO t_dept VALUES
 ('1','教学部'),
 ('2','研发部');
  
 -- 员工的测试数据
 INSERT INTO t_emps VALUES
 ('1','小黑',12,CURDATE(),23000.13,'1'),
 ('2','小三',13,CURDATE(),24000.14,'2'),
 ('3','小黄',14,CURDATE(),25000.15,'1'),
 ('4','小牛',15,CURDATE(),26000.16,'1'),
 ('5','小金',16,CURDATE(),27000.17,'1'),
 ('6','小陈',17,CURDATE(),28000.18,'2');
  
 -- 查看表数据
 SELECT * FROM t_dept;
 SELECT * FROM t_emps;
  
 -- 根据部门找员工信息
 SELECT 
   d.id,d.`name`,
   e.`id`,e.`name`,e.`age`,e.`bir`,e.`salary`,e.`deptId`
 FROM t_dept d
 LEFT JOIN t_emps e
 ON d.`id`=e.`deptId`;

2.2 创建实体类

Dept

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
package entity;
   
  import java.util.List;
   
  /**
   * @ClassName Dept
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/16 20:19
   * @Version 1.0
   */
  public class Dept {
      private String id;
      private String name;
   
      //关系属性
      private List<Emp> emps;
   
      public List<Emp> getEmps() {
          return emps;
      }
   
      public void setEmps(List<Emp> emps) {
          this.emps = emps;
      }
   
      public Dept() {
      }
   
      public Dept(String id, String name) {
          this.id = id;
          this.name = name;
      }
   
      public String getId() {
          return id;
      }
   
      public void setId(String id) {
          this.id = id;
      }
   
      public String getName() {
          return name;
      }
   
      public void setName(String name) {
          this.name = name;
      }
   
      @Override
      public String toString() {
          return "Dept{" +
                  "id='" + id + '\'' +
                  ", name='" + name + '\'' +
                  '}';
      }
  }

Emp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
package entity;
  
 import java.util.Date;
  
 /**
  * @ClassName Emp
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/16 20:21
  * @Version 1.0
  */
 public class Emp {
     private String id;
     private String name;
     private Integer age;
     private Double salary;
     private Date bir;
  
     //关系属性
     private Dept dept;
  
     public Dept getDept() {
         return dept;
     }
  
     public void setDept(Dept dept) {
         this.dept = dept;
     }
  
     public Emp() {
     }
  
     public Emp(String id, String name, Integer age, Double salary, Date bir) {
         this.id = id;
         this.name = name;
         this.age = age;
         this.salary = salary;
         this.bir = bir;
     }
  
     public String getId() {
         return id;
     }
  
     public void setId(String id) {
         this.id = id;
     }
  
     public String getName() {
         return name;
     }
  
     public void setName(String name) {
         this.name = name;
     }
  
     public Integer getAge() {
         return age;
     }
  
     public void setAge(Integer age) {
         this.age = age;
     }
  
     public Double getSalary() {
         return salary;
     }
  
     public void setSalary(Double salary) {
         this.salary = salary;
     }
  
     public Date getBir() {
         return bir;
     }
  
     public void setBir(Date bir) {
         this.bir = bir;
     }
  
     @Override
     public String toString() {
         return "Emp{" +
                 "id='" + id + '\'' +
                 ", name='" + name + '\'' +
                 ", age=" + age +
                 ", salary=" + salary +
                 ", bir=" + bir +
                 '}';
     }
 }

2.3 创建DAO组件

DeptDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package dao;
  
 import entity.Dept;
  
 import java.util.List;
  
 /**
  * @ClassName DeptDAO
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/16 20:25
  * @Version 1.0
  */
 public interface DeptDAO {
     /**
      * @MethodName selectAll
      * @Description 查询部门的同时将部门中所有的员工信息一并查到
      * @return: java.util.List<entity.Dept>
      * @Author chabai
      * @Date 2022/4/16 20:26
      */
     List<Dept> selectAll();
 }

EmpDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package dao;
  
 import entity.Emp;
  
 import java.util.List;
  
 /**
  * @ClassName EmpDAO
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/16 20:53
  * @Version 1.0
  */
 public interface EmpDAO {
     /**
      * @MethodName selectAll
      * @Description 查询员工信息的同时将部门的信息一并查到
      * @return: java.util.List<entity.Emp>
      * @Author chabai
      * @Date 2022/4/16 20:54
      */
     List<Emp> selectAll();
 }

2.4 创建mapper配置文件

DeptDAOMapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
<?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="dao.DeptDAO">
     <!--
         id = "接口中的方法名"
         parameterType = "接口中传入方法的参数类型" 基本类型和String正常使用，引用类型使用类的全限定名(包名.类名)
         resultType = "返回实体类对象：包.类名"  处理结果集 自动封装
         注意:
             1.sql语句后不要出现";"号
             2.Mybatis框架在执行插入(insert)、更新(update)操作时，默认不允许插入或修改NULL值到数据库中，
         要在sql语句的取值处设置一个jdbcType类型(类型全部字母大写))，例如：#{age,jdbcType=INTEGER}
     -->
     <resultMap id="deptMap" type="entity.Dept">
         <id column="id" property="id"/>
         <result column="name" property="name"/>
  
         <!-- 处理关系属性的封装
             collection：用来处理封装集合类型的管理属性，用来处理一对多这种情况
             property: 关系属性名
             javaType: 关系属性类型
             ofType  : 集合中泛型类型  全名
          -->
         <collection property="emps" javaType="list" ofType="entity.Emp">
             <id column="eid" property="id"/>
             <result column="ename" property="name"/>
             <result column="age" property="age"/>
             <result column="salary" property="salary"/>
             <result column="bir" property="bir"/>
         </collection>
     </resultMap>
  
     <!--selectAll-->
     <select id="selectAll" resultMap="deptMap">
         select
             d.id,d.name,
             e.id eid,e.name ename,e.age,e.salary,e.bir
         from t_dept d
         left join t_emps e
         on d.id=e.deptId
     </select>
 </mapper>

EmpDAOMapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
<?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="dao.EmpDAO">
     <!--
         id = "接口中的方法名"
         parameterType = "接口中传入方法的参数类型" 基本类型和String正常使用，引用类型使用类的全限定名(包名.类名)
         resultType = "返回实体类对象：包.类名"  处理结果集 自动封装
         注意:
             1.sql语句后不要出现";"号
             2.Mybatis框架在执行插入(insert)、更新(update)操作时，默认不允许插入或修改NULL值到数据库中，
         要在sql语句的取值处设置一个jdbcType类型(类型全部字母大写))，例如：#{age,jdbcType=INTEGER}
     -->
     <resultMap id="empMap" type="entity.Emp">
         <id column="id" property="id"/>
         <result column="name" property="name"/>
         <result column="age" property="age"/>
         <result column="salary" property="salary"/>
         <result column="bir" property="bir"/>
  
         <!-- 处理关系属性的封装
             collection：用来处理封装集合类型的管理属性，用来处理一对多这种情况
             property: 关系属性名
             javaType: 关系属性类型
             ofType  : 集合中泛型类型  全名
          -->
         <association property="dept" javaType="entity.Dept">
             <id column="did" property="id"/>
             <result column="dname" property="name"/>
         </association>
     </resultMap>
  
     <!--selectAll-->
     <select id="selectAll" resultMap="empMap">
         select
             e.id,e.name,e.age,e.salary,e.bir,
             d.id did,d.name dname
         from t_dept d
         left join t_emps e
         on d.id=e.deptId
     </select>
 </mapper>

2.5 mybatis-config.xml配置mapper

1
2
3
4
<mappers>
      <mapper resource="mapper/DeptDAOMapper.xml"/>
      <mapper resource="mapper/EmpDAOMapper.xml"/>
  </mappers>

2.6 测试连接查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package test;
   
  import dao.CardDAO;
  import dao.DeptDAO;
  import dao.EmpDAO;
  import dao.UserDAO;
  import org.apache.ibatis.session.SqlSession;
  import org.junit.Test;
  import util.MybatisUtil;
   
  /**
   * @ClassName TestDAO
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/16 16:37
   * @Version 1.0
   */
  public class TestDAO {
      /**
       * @MethodName testDeptDAO
       * @Description 测试DeptDAO组件连接查询
       * @Author chabai
       * @Date 2022/4/16 20:50
       */
      @Test
      public void testDeptDAO(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          DeptDAO deptDAO = sqlSession.getMapper(DeptDAO.class);
          deptDAO.selectAll().forEach(dept -> System.out.println("dept = " + dept + " " + dept.getEmps()));
          MybatisUtil.close();
      }
   
      /**
       * @MethodName testEmpDAO
       * @Description 测试EmpDAO组件连接查询
       * @Author chabai
       * @Date 2022/4/16 20:50
       */
      @Test
      public void testEmpDAO(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          EmpDAO empDAO = sqlSession.getMapper(EmpDAO.class);
          empDAO.selectAll().forEach(emp -> System.out.println("emp = " + emp + " " + emp.getDept()));
          MybatisUtil.close();
      }
  }

测试结果：

（1）DeptDAO

1
2
3
4
5
[DEBUG] 2022-04-16 main dao.DeptDAO.selectAll ==>  Preparing: select d.id,d.name, e.id eid,e.name ename,e.age,e.salary,e.bir from t_dept d left join t_emps e on d.id=e.deptId 
 [DEBUG] 2022-04-16 main dao.DeptDAO.selectAll ==> Parameters: 
 [DEBUG] 2022-04-16 main dao.DeptDAO.selectAll <==      Total: 6
 dept = Dept{id='1', name='教学部'} [Emp{id='1', name='小黑', age=12, salary=23000.13, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='3', name='小黄', age=14, salary=25000.15, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='4', name='小牛', age=15, salary=26000.16, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='5', name='小金', age=16, salary=27000.17, bir=Sat Apr 16 00:00:00 CST 2022}]
 dept = Dept{id='2', name='研发部'} [Emp{id='2', name='小三', age=13, salary=24000.14, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='6', name='小陈', age=17, salary=28000.18, bir=Sat Apr 16 00:00:00 CST 2022}]

（2）EmpDAO

1
2
3
4
5
6
7
8
9
[DEBUG] 2022-04-16 main dao.EmpDAO.selectAll ==>  Preparing: select e.id,e.name,e.age,e.salary,e.bir, d.id did,d.name dname from t_dept d left join t_emps e on d.id=e.deptId 
  [DEBUG] 2022-04-16 main dao.EmpDAO.selectAll ==> Parameters: 
  [DEBUG] 2022-04-16 main dao.EmpDAO.selectAll <==      Total: 6
  emp = Emp{id='1', name='小黑', age=12, salary=23000.13, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'}
  emp = Emp{id='2', name='小三', age=13, salary=24000.14, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='2', name='研发部'}
  emp = Emp{id='3', name='小黄', age=14, salary=25000.15, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'}
  emp = Emp{id='4', name='小牛', age=15, salary=26000.16, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'}
  emp = Emp{id='5', name='小金', age=16, salary=27000.17, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'}
  emp = Emp{id='6', name='小陈', age=17, salary=28000.18, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='2', name='研发部'}

3 多对多关联关系

image-20240328144055040

思路：多对多拆分成一对多，添加关系表

3.1 建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- 学生表
 DROP TABLE IF EXISTS `t_student`;
 CREATE TABLE IF NOT EXISTS `t_student`(
   `id` VARCHAR(36) PRIMARY KEY,
   `name` VARCHAR(40),
   `age` INTEGER
 )ENGINE=INNODB CHARSET=utf8;
  
 -- 课程表
 DROP TABLE IF EXISTS `t_course`;
 CREATE TABLE IF NOT EXISTS `t_course`(
   `id` VARCHAR(36) PRIMARY KEY,
   `name` VARCHAR(36)
 )ENGINE=INNODB CHARSET=utf8;
  
 -- 学生选课表（关系表:主键作为字段）
 DROP TABLE IF EXISTS `t_student_course`;
 CREATE TABLE IF NOT EXISTS `t_student_course`(
   `sid` VARCHAR(36) REFERENCES t_student(`id`),
   `cid` VARCHAR(36) REFERENCES t_course(`id`)
 )ENGINE=INNODB CHARSET=utf8;
  
 -- 插入测试数据
 INSERT INTO t_student VALUES 
 ('11','小明',23),
 ('22','小王',23);
  
 INSERT INTO t_course VALUES
 ('1','语文'),
 ('2','数学'),
 ('3','政治');
  
 INSERT INTO t_student_course VALUES
 ('11','1'),
 ('11','2'),
 ('11','3'),
 ('22','1'),
 ('22','3');
  
 -- 查看表数据
 SELECT * FROM t_student;
 SELECT * FROM t_course;
 SELECT * FROM t_student_course;
  
 -- 关联查询 （t_student+t_student_course）+t_course
 SELECT 
   s.id,s.name,s.age,
   c.id cid,c.name cname
 FROM t_student s
 LEFT JOIN t_student_course sc
 ON s.`id`=sc.`sid`
 LEFT JOIN t_course c
 ON sc.`cid`=c.`id`;

3.2 创建实体类

Student

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
package entity;
   
  import java.util.List;
   
  /**
   * @ClassName Student
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/7 9:17
   * @Version 1.0
   */
  public class Student {
      private String id;
      private String name;
      private int age;
   
      //关联属性
      List<Course> courses;
   
      public List<Course> getCourses() {
          return courses;
      }
   
      public void setCourses(List<Course> courses) {
          this.courses = courses;
      }
   
      public Student() {
      }
   
      public Student(String id, String name, int age) {
          this.id = id;
          this.name = name;
          this.age = age;
      }
   
      public String getId() {
          return id;
      }
   
      public void setId(String id) {
          this.id = id;
      }
   
      public String getName() {
          return name;
      }
   
      public void setName(String name) {
          this.name = name;
      }
   
      public int getAge() {
          return age;
      }
   
      public void setAge(int age) {
          this.age = age;
      }
   
      @Override
      public String toString() {
          return "Student{" +
                  "id='" + id + '\'' +
                  ", name='" + name + '\'' +
                  ", age=" + age +
                  '}';
      }
  }

Course

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
package entity;
   
  import java.util.List;
   
  /**
   * @ClassName Course
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/16 21:53
   * @Version 1.0
   */
  public class Course {
      private String id;
      private String name;
   
      //关联属性
      List<Student> students;
   
      public List<Student> getStudents() {
          return students;
      }
   
      public void setStudents(List<Student> students) {
          this.students = students;
      }
   
      public Course() {
      }
   
      public Course(String id, String name) {
          this.id = id;
          this.name = name;
      }
   
      public String getId() {
          return id;
      }
   
      public void setId(String id) {
          this.id = id;
      }
   
      public String getName() {
          return name;
      }
   
      public void setName(String name) {
          this.name = name;
      }
   
      @Override
      public String toString() {
          return "Course{" +
                  "id='" + id + '\'' +
                  ", name='" + name + '\'' +
                  '}';
      }
  }

3.3 创建DAO组件

StudentDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package dao;
   
  import entity.Student;
  import org.apache.ibatis.annotations.Param;
   
  import java.util.List;
  import java.util.Map;
   
  /**
   * @ClassName StudentDAO
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/7 9:18
   * @Version 1.0
   */
  public interface StudentDAO {
      /**
       * @MethodName selectAll
       * @Description 查询学生信息的同时也把课程的信息一并查出来
       * @return: java.util.List<entity.Student>
       * @Author chabai
       * @Date 2022/4/16 21:55
       */
      List<Student> selectAll();
  }

CourseDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package dao;
   
  import entity.Course;
   
  import java.util.List;
   
  /**
   * @ClassName CourseDAO
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/16 21:58
   * @Version 1.0
   */
  public interface CourseDAO {
      /**
       * @MethodName selectAll
       * @Description 查询课程信息的同时把学生信息也一并查出来
       * @return: java.util.List<entity.Course>
       * @Author chabai
       * @Date 2022/4/16 21:58
       */
      List<Course> selectAll();
  }

3.4 创建mapper配置文件

StudentDAOMapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?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="dao.StudentDAO">
      <!--
          id = "接口中的方法名"
          parameterType = "接口中传入方法的参数类型" 基本类型和String正常使用，引用类型使用类的全限定名(包名.类名)
          resultType = "返回实体类对象：包.类名"  处理结果集 自动封装
          注意:
              1.sql语句后不要出现";"号
              2.Mybatis框架在执行插入(insert)、更新(update)操作时，默认不允许插入或修改NULL值到数据库中，
          要在sql语句的取值处设置一个jdbcType类型(类型全部字母大写))，例如：#{age,jdbcType=INTEGER}
      -->
      <resultMap id="studentMap" type="entity.Student">
          <id column="id" property="id"/>
          <result column="name" property="name"/>
          <result column="age" property="age"/>
          <!-- 封装课程信息 一对多   collection类型 -->
          <collection property="courses" javaType="list" ofType="entity.Course">
              <id column="cid" property="id"/>
              <result column="cname" property="name"/>
          </collection>
      </resultMap>
      
      <!--selectAll-->
      <select id="selectAll" resultMap="studentMap">
          SELECT 
              s.id,s.name,s.age,
              c.id cid,c.name cname
          FROM t_student s
          LEFT JOIN t_student_course sc
          ON s.`id`=sc.`sid`
          LEFT JOIN t_course c
          ON sc.`cid`=c.`id`
      </select>
   
  </mapper>

CourseDAOMappe

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<?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="dao.CourseDAO">
      <!--
          id = "接口中的方法名"
          parameterType = "接口中传入方法的参数类型" 基本类型和String正常使用，引用类型使用类的全限定名(包名.类名)
          resultType = "返回实体类对象：包.类名"  处理结果集 自动封装
          注意:
              1.sql语句后不要出现";"号
              2.Mybatis框架在执行插入(insert)、更新(update)操作时，默认不允许插入或修改NULL值到数据库中，
          要在sql语句的取值处设置一个jdbcType类型(类型全部字母大写))，例如：#{age,jdbcType=INTEGER}
      -->
      <resultMap id="courseMap" type="entity.Course">
          <id column="id" property="id"/>
          <result column="name" property="name"/>
          <!-- 封装学生信息 一对多   collection类型 -->
          <collection property="students" javaType="list" ofType="entity.Student">
              <id column="sid" property="id"/>
              <result column="sname" property="name"/>
              <result column="age" property="age"/>
          </collection>
      </resultMap>
      <!--selectAll-->
      <select id="selectAll" resultMap="courseMap">
          SELECT
              c.id,c.name,
              s.id sid,s.name sname,s.age
          FROM t_student s
          LEFT JOIN t_student_course sc
          ON s.`id`=sc.`sid`
          LEFT JOIN t_course c
          ON sc.`cid`=c.`id`
      </select>
  </mapper>

3.5 mybatis-config.xml配置mapper

1
2
3
4
<mappers>
     <mapper resource="mapper/StudentDAOMapper.xml"/>
     <mapper resource="mapper/CourseDAOMapper.xml"/>
 </mappers>

3.6 测试连接查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
package test;
   
  import dao.*;
  import entity.Course;
  import org.apache.ibatis.session.SqlSession;
  import org.junit.Test;
  import util.MybatisUtil;
   
  /**
   * @ClassName TestDAO
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/16 16:37
   * @Version 1.0
   */
  public class TestDAO {
      /**
       * @MethodName testStudentDAO
       * @Description 测试StudentDAO组件连接查询
       * @Author chabai
       * @Date 2022/4/16 22:05
       */
      @Test
      public void testStudentDAO(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
          studentDAO.selectAll().forEach(student -> System.out.println("student = " + student + " " + student.getCourses()));
          MybatisUtil.close();
      }
   
      /**
       * @MethodName testCourseDAO
       * @Description 测试CourseDAO组件连接查询
       * @Author chabai
       * @Date 2022/4/16 22:12
       */
      @Test
      public void testCourseDAO(){
          SqlSession sqlSession = MybatisUtil.getSqlSession();
          CourseDAO courseDAO = sqlSession.getMapper(CourseDAO.class);
          courseDAO.selectAll().forEach(course -> System.out.println("course = " + course + " " + course.getStudents()));
          MybatisUtil.close();
      }
  }

测试结果：

（1）StudentDAO

1
2
3
4
5
DEBUG] 2022-04-16 main dao.StudentDAO.selectAll ==>  Preparing: SELECT s.id,s.name,s.age, c.id cid,c.name cname FROM t_student s LEFT JOIN t_student_course sc ON s.`id`=sc.`sid` LEFT JOIN t_course c ON sc.`cid`=c.`id` 
 [DEBUG] 2022-04-16 main dao.StudentDAO.selectAll ==> Parameters: 
 [DEBUG] 2022-04-16 main dao.StudentDAO.selectAll <==      Total: 5
 student = Student{id='11', name='小明', age=23} [Course{id='1', name='语文'}, Course{id='2', name='数学'}, Course{id='3', name='政治'}]
 student = Student{id='22', name='小王', age=23} [Course{id='1', name='语文'}, Course{id='3', name='政治'}]

（2）CourseDAO

1
2
3
4
5
6
[DEBUG] 2022-04-16 main dao.CourseDAO.selectAll ==>  Preparing: SELECT c.id,c.name, s.id sid,s.name sname,s.age FROM t_student s LEFT JOIN t_student_course sc ON s.`id`=sc.`sid` LEFT JOIN t_course c ON sc.`cid`=c.`id` 
  [DEBUG] 2022-04-16 main dao.CourseDAO.selectAll ==> Parameters: 
  [DEBUG] 2022-04-16 main dao.CourseDAO.selectAll <==      Total: 5
  course = Course{id='1', name='语文'} [Student{id='11', name='小明', age=23}, Student{id='22', name='小王', age=23}]
  course = Course{id='2', name='数学'} [Student{id='11', name='小明', age=23}]
  course = Course{id='3', name='政治'} [Student{id='11', name='小明', age=23}, Student{id='22', name='小王', age=23}]

Spring+Mybatis+Struts2练习项目

作业：

image-20240328144709860

用户与身份信息展示分析：image-20240328144838692

用户和身份信息添加分析：

image-20240328144853269

1. 创建Maven-webapp项目，新建java、resources目录以及test目录下的java、resources

2. 在pom.xml中引入依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.11</version>
    <scope>test</scope>
  </dependency>
   
  <!--spring相关-->
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-core</artifactId>
    <version>4.3.2.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-beans</artifactId>
    <version>4.3.2.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-web</artifactId>
    <version>4.3.2.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-expression</artifactId>
    <version>4.3.2.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aop</artifactId>
    <version>4.3.2.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.3.2.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context-support</artifactId>
    <version>4.3.2.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aspects</artifactId>
    <version>4.3.2.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.2.RELEASE</version>
  </dependency>
   
  <!--mybatis-->
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.4</version>
  </dependency>
   
  <!--mybatis-spring-->
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.0.4</version>
  </dependency>
   
  <!--mysql-->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
  </dependency>
   
  <!--druid-->
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.19</version>
  </dependency>
   
  <!--struts相关 2.3.16版本一致-->
  <dependency>
    <groupId>org.apache.struts</groupId>
    <artifactId>struts2-core</artifactId>
    <version>2.3.16</version>
  </dependency>
   
  <!--struts2-spring-plugin 2.3.16版本一致-->
  <dependency>
    <groupId>org.apache.struts</groupId>
    <artifactId>struts2-spring-plugin</artifactId>
    <version>2.3.16</version>
  </dependency>
   
  <!--servlet-api-->
  <dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>servlet-api</artifactId>
    <version>2.5</version>
    <scope>provided</scope>
  </dependency>
   
  <!--jstl-->
  <dependency>
    <groupId>jstl</groupId>
    <artifactId>jstl</artifactId>
    <version>1.2</version>
  </dependency>
   
  <!--fastjson-->
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.62</version>
  </dependency>
   
  <!--log4j-->
  <dependency>
    <groupId>org.apache.logging.log4j</groupId>
    <artifactId>log4j-core</artifactId>
    <version>2.10.0</version>
  </dependency>
  <dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.7.25</version>
  </dependency>

3. 建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 一对一
 -- 用户表
 CREATE TABLE IF NOT EXISTS `table_user`(
     `id` VARCHAR(36) PRIMARY KEY,
     `name` VARCHAR(40),
     `age` INTEGER,
     `bir` DATE
 )ENGINE=INNODB DEFAULT CHARSET=utf8;
  
 -- 身份信息表
 CREATE TABLE IF NOT EXISTS `table_card`(
     `id` VARCHAR(36) PRIMARY KEY,
     `code` VARCHAR(18),
     `address` VARCHAR(100),
     `fork` VARCHAR(30),
     `userId` VARCHAR(36) REFERENCES `table_user`(`id`)  -- 关系属性
 )ENGINE=INNODB DEFAULT CHARSET=utf8;
  
 -- 插入数据
 INSERT INTO table_user VALUES
 ('1','张三',20,CURDATE()),
 ('2','李四',30,CURDATE());
 INSERT INTO table_card VALUES
 ('1','111111111111111111','北京市','汉族','1'),
 ('2','222222222222222222','上海市','满族','2');
  
 -- 查询数据
 SELECT * FROM table_user;
 SELECT * FROM table_card;
  
 -- 连接查询用户信息+身份信息SQL语句
 select
     u.id,u.name,u.age,u.bir,
     c.id cid,c.code,c.address,c.fork
 from table_user u
 left join table_card c
 on u.id=c.userId;

4.创建实体类

User

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
package com.study.entity;
   
  import java.util.Date;
   
  /**
   * @ClassName User
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/17 20:32
   * @Version 1.0
   */
  public class User {
      private String id;
      private String name;
      private Integer age;
      private Date bir;
   
      //关系属性
      private Card card = new Card();//实例化
   
      public Card getCard() {
          return card;
      }
   
      public void setCard(Card card) {
          this.card = card;
      }
   
      public User() {
      }
   
      public User(String id, String name, Integer age, Date bir) {
          this.id = id;
          this.name = name;
          this.age = age;
          this.bir = bir;
      }
   
      public String getId() {
          return id;
      }
   
      public void setId(String id) {
          this.id = id;
      }
   
      public String getName() {
          return name;
      }
   
      public void setName(String name) {
          this.name = name;
      }
   
      public Integer getAge() {
          return age;
      }
   
      public void setAge(Integer age) {
          this.age = age;
      }
   
      public Date getBir() {
          return bir;
      }
   
      public void setBir(Date bir) {
          this.bir = bir;
      }
   
      @Override
      public String toString() {
          return "User{" +
                  "id='" + id + '\'' +
                  ", name='" + name + '\'' +
                  ", age=" + age +
                  ", bir=" + bir +
                  '}';
      }
  }

Card

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
package com.study.entity;
   
  /**
   * @ClassName Card
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/17 20:32
   * @Version 1.0
   */
  public class Card {
      private String id;
      private String code;
      private String address;
      private String fork;
   
      //关系属性
      private User user;
   
      public User getUser() {
          return user;
      }
   
      public void setUser(User user) {
          this.user = user;
      }
   
      public Card() {
      }
   
      public Card(String id, String code, String address, String fork) {
          this.id = id;
          this.code = code;
          this.address = address;
          this.fork = fork;
      }
   
      public String getId() {
          return id;
      }
   
      public void setId(String id) {
          this.id = id;
      }
   
      public String getCode() {
          return code;
      }
   
      public void setCode(String code) {
          this.code = code;
      }
   
      public String getAddress() {
          return address;
      }
   
      public void setAddress(String address) {
          this.address = address;
      }
   
      public String getFork() {
          return fork;
      }
   
      public void setFork(String fork) {
          this.fork = fork;
      }
   
      @Override
      public String toString() {
          return "Card{" +
                  "id='" + id + '\'' +
                  ", code='" + code + '\'' +
                  ", address='" + address + '\'' +
                  ", fork='" + fork + '\'' +
                  '}';
      }
  }

5.创建DAO接口

UserDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
package com.study.dao;
 
import com.study.entity.User;
 
import java.util.List;
 
/**
 * @ClassName UserDAO
 * @Description TODO
 * @Author chabai
 * @Date 2022/4/17 22:01
 * @Version 1.0
 */
public interface UserDAO {
    /**
     * @MethodName selectAll
     * @Description 查询所有用户的同时一并查出用户的身份信息
     * @return: java.util.List<com.study.entity.User>
     * @Author chabai
     * @Date 2022/4/18 8:27
     */
    List<User> selectAll();
 
    /**
     * @MethodName insertUser
     * @Description 添加用户
     * @param: user
     * @Author chabai
     * @Date 2022/4/18 10:01
     */
    void insertUser(User user);
 
    /**
     * @MethodName selectUserNoCard
     * @Description 查询没有身份信息的用户，通过用户获得用户id用于添加身份信息
     * @return: java.util.List<com.study.entity.User>
     * @Author chabai
     * @Date 2022/4/18 15:35
     */
    List<User> selectUserByNoCard();
 
    /**
     * @MethodName selectUserByKeywords
     * @Description 根据关键字查询用户信息
     * @param: user 用作与关键字进行匹配
     * @return: java.util.List<com.study.entity.User>
     * @Author chabai
     * @Date 2022/4/19 16:05
     */
    List<User> selectUserByKeywords(User user);
}

CardDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.study.dao;
  
 import com.study.entity.Card;
  
 /**
  * @ClassName CardDAO
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/18 15:16
  * @Version 1.0
  */
 public interface CardDAO {
     /**
      * @MethodName insertCard
      * @Description 添加身份信息
      * @Author chabai
      * @Date 2022/4/18 15:16
      */
     void insertCard(Card card);
 }

6.创建Mapper配置文件

UserDAOMapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
<?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.study.dao.UserDAO">
      <!--userMap-->
      <resultMap id="userMap" type="User">
          <id column="id" property="id"/>
          <result column="name" property="name"/>
          <result column="age" property="age"/>
          <result column="bir" property="bir"/>
          <!--一对一-->
          <association property="card" javaType="Card">
              <id column="cid" property="id"/>
              <result column="code" property="code"/>
              <result column="address" property="address"/>
              <result column="fork" property="fork"/>
          </association>
      </resultMap>
   
      <!--selectAll-->
      <select id="selectAll" resultMap="userMap">
          select
              u.id,u.name,u.age,u.bir,
              c.id cid,c.code,c.address,c.fork
          from table_user u
          left join table_card c
          on u.id=c.userId;
      </select>
   
      <!--insertUser-->
      <insert id="insertUser" parameterType="User">
          insert into table_user values (#{id},#{name},#{age},#{bir})
      </insert>
   
      <!--selectUserByNoCard-->
      <select id="selectUserByNoCard" resultMap="userMap">
          select u.id,u.name,u.age,u.bir
          from table_user u
          left join table_card c
          on u.id=c.userId
          where c.userId is null;
      </select>
   
      <!--selectUserByKeywords-->
      <select id="selectUserByKeywords" parameterType="User" resultMap="userMap">
          select u.id,u.name,u.age,u.bir,
          c.id cid,c.address,c.code,c.fork
          from table_user u
          left join table_card c
          on u.id = c.userId
          <where>
              <if test="name!=null and name!=''">
                  u.name like "%"#{name}"%"
              </if>
              <if test="age!=null">
                  or u.age = #{age}
              </if>
              <if test="bir!=null">
                  or u.bir = #{bir}
              </if>
              <if test="card.address!=null and card.address!=''">
                  or c.address like "%"#{card.address}"%"
              </if>
              <if test="card.fork!=null and card.fork!=''">
                  or c.fork = #{card.fork}
              </if>
              <if test="card.code!=null and card.code!=''">
                  or c.code = #{card.code}
              </if>
          </where>
      </select>
  </mapper>

CardDAOMapper

1
2
3
4
5
6
7
8
9
10
11
12
<?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.study.dao.CardDAO">
     <!--insertCard-->
     <insert id="insertCard" parameterType="Card">
         insert into table_card values(#{id},#{code},#{address},#{fork},#{user.id,jdbcType=VARCHAR})
     </insert>
  
 </mapper>

7.创建Service接口及其实现类

UserService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package com.study.service;
  
 import com.study.entity.User;
  
 import java.util.List;
  
 /**
  * @ClassName UserService
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/17 22:11
  * @Version 1.0
  */
 public interface UserService {
     /**
      * @MethodName selectAll
      * @Description 查询用户信息的同时一并查出身份信息
      * @return: java.util.List<com.study.entity.User>
      * @Author chabai
      * @Date 2022/4/18 8:28
      */
     List<User> selectAll();
  
     /**
      * @MethodName insertUser
      * @Description 插入用户
      * @param: user
      * @Author chabai
      * @Date 2022/4/18 10:05
      */
     void insertUser(User user);
  
     /**
      * @MethodName selectUserByNoCard
      * @Description 查询没有身份信息的用户
      * @return: java.util.List<com.study.entity.User>
      * @Author chabai
      * @Date 2022/4/18 16:21
      */
     List<User> selectUserByNoCard();
 }

UserServiceImpl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.study.service;
   
   
  import com.study.dao.UserDAO;
  import com.study.entity.User;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.stereotype.Service;
  import org.springframework.transaction.annotation.Propagation;
  import org.springframework.transaction.annotation.Transactional;
   
  import java.util.List;
   
  /**
   * @ClassName UserServiceImpl
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/17 22:13
   * @Version 1.0
   */
  @Service("userService")
  @Transactional
  public class UserServiceImpl implements  UserService{
      //UserService依赖UserDAO组件
      @Autowired
      private UserDAO userDAO;
   
      @Override
      @Transactional(propagation = Propagation.SUPPORTS)
      public List<User> selectAll() {
          //处理业务
          return userDAO.selectAll();
      }
   
      @Override
      public void insertUser(User user) {
          //处理插入用户业务
          user.setId(UUID.randomUUID().toString());
          userDAO.insertUser(user);
      }
   
      @Override
      @Transactional(propagation = Propagation.SUPPORTS)
      public List<User> selectUserByNoCard() {
          //处理查询没有身份信息的用户
          return userDAO.selectUserByNoCard();
      }
  }

CardService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package com.study.service;
  
 import com.study.entity.Card;
  
 /**
  * @ClassName CardService
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/18 15:20
  * @Version 1.0
  */
 public interface CardService {
     /**
      * @MethodName insertCard
      * @Description 插入身份信息
      * @param: card
      * @Author chabai
      * @Date 2022/4/18 15:21
      */
     void insertCard(Card card);
 }

CardServiceImpl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
package com.study.service;
  
 import com.study.dao.CardDAO;
 import com.study.entity.Card;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Service;
 import org.springframework.transaction.annotation.Transactional;
  
 import java.util.UUID;
  
 /**
  * @ClassName CardServiceImpl
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/18 15:21
  * @Version 1.0
  */
 @Service("cardService")
 @Transactional
 public class CardServiceImpl implements CardService{
     @Autowired
     private CardDAO cardDAO;
  
     @Override
     public void insertCard(Card card) {
         card.setId(UUID.randomUUID().toString());
         cardDAO.insertCard(card);
     }
 }

8.编写spring.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<?xml version="1.0" encoding="UTF-8"?>
 <beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
        xmlns:aop="http://www.springframework.org/schema/aop"
        xmlns:context="http://www.springframework.org/schema/context"
        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
  
     <!--开启注解扫描-->
     <context:component-scan base-package="com.study"/>
  
     <!--创建数据源-->
     <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
         <property name="url" value="jdbc:mysql://localhost:3306/mybatis?
         useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC&amp;useSSL=false"/>
         <property name="username" value="root"/>
         <property name="password" value="123456"/>
     </bean>
  
     <!--创建SqlSessionFactory-->
     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
         <property name="dataSource" ref="dataSource"/>
         <property name="mapperLocations" value="classpath:com/study/mapper/*.xml"/>
         <property name="typeAliasesPackage" value="com.study.entity"/>
     </bean>
  
     <!--创建DAO组件-->
     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
         <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
         <property name="basePackage" value="com.study.dao"/>
     </bean>
  
     <!--创建事务管理器-->
     <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
         <property name="dataSource" ref="dataSource"/>
     </bean>
  
     <!--开启注解式事务生效-->
     <tx:annotation-driven transaction-manager="transactionManager"/>
 </beans>

9.测试Spring与Mybatis是否整合成功

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.study.test;
  
 import com.study.service.UserService;
 import org.springframework.context.ApplicationContext;
 import org.springframework.context.support.ClassPathXmlApplicationContext;
  
 /**
  * @ClassName TestSpringMybatis
  * @Description TODO
  * @Author chabai
  * @Date 2022/4/18 8:36
  * @Version 1.0
  */
 public class TestSpringMybatis {
     public static void main(String[] args) {
         ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
         UserService userService = (UserService) context.getBean("userService");
         userService.selectAll().forEach(user-> System.out.println("user = " + user + " " + user.getCard()));
     }
 }

10. 配置web.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<!DOCTYPE web-app PUBLIC
   "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
   "http://java.sun.com/dtd/web-app_2_3.dtd" >
   
  <web-app>
    <display-name>Archetype Created Web Application</display-name>
   
    <!--配置工厂配置文件的位置-->
    <context-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>classpath:spring.xml</param-value>
    </context-param>
   
    <!--配置struts2中核心filter-->
    <filter>
      <filter-name>struts2</filter-name>
      <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
    </filter>
    <filter-mapping>
      <filter-name>struts2</filter-name>
      <url-pattern>/*</url-pattern>
    </filter-mapping>
   
    <!--配置spring监听器-->
    <listener>
      <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
  </web-app>

11.创建Action组件

UserAction

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
package com.study.action;
   
  import com.opensymphony.xwork2.Action;
  import com.opensymphony.xwork2.ActionSupport;
  import com.study.entity.User;
  import com.study.service.UserService;
  import com.study.service.UserServiceImpl;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.context.annotation.Scope;
  import org.springframework.stereotype.Controller;
  import org.springframework.stereotype.Repository;
   
  import java.beans.ConstructorProperties;
  import java.lang.reflect.InvocationTargetException;
  import java.text.ParseException;
  import java.util.List;
  import java.util.UUID;
   
  /**
   * @ClassName UserAction
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/17 22:16
   * @Version 1.0
   */
  @Controller("userAction")
  @Scope("prototype")
  public class UserAction extends ActionSupport {
      @Autowired
      private UserService userService;
   
      //接收参数
      private List<User> users;
      public List<User> getUsers() {
          return users;
      }
      public void setUsers(List<User> users) {
          this.users = users;
      }
   
      private User user;
      public User getUser() {
          return user;
      }
      public void setUser(User user) {
          this.user = user;
      }
   
      //接收搜索的选中列
      private List<String> cols;
      public List<String> getCols() {
          return cols;
      }
      public void setCols(List<String> cols) {
          this.cols = cols;
      }
   
      //搜索条件，多个条件之间使用“,”分隔
      private String keywords;
      public String getKeywords() {
          return keywords;
      }
      public void setKeywords(String keywords) {
          this.keywords = keywords;
      }
   
      /**
       * @MethodName selectAll
       * @Description 处理查询用户所有信息
       * @return: java.lang.String
       * @Author chabai
       * @Date 2022/4/18 10:47
       */
      public String selectAll() {
          this.users = userService.selectAll();
          return Action.SUCCESS;
      }
   
      /**
       * @MethodName insertUser
       * @Description 插入用户信息
       * @return: java.lang.String
       * @Author chabai
       * @Date 2022/4/18 10:47
       */
      public String insertUser(){
          userService.insertUser(user);
          return Action.SUCCESS;
      }
   
      /**
       * @MethodName selectUserByNoCard
       * @Description 查询没有身份信息的用户
       * @return: java.lang.String
       * @Author chabai
       * @Date 2022/4/18 16:23
       */
      public String selectUserByNoCard(){
          this.users = userService.selectUserByNoCard();
          return Action.SUCCESS;
      }
  
      /**     
       * @MethodName selectUserByKeywords    
       * @Description 根据关键字查找用户信息     
       * @return: java.lang.String     
       * @Author chabai     
       * @Date 2022/4/18 16:23     
       */ 
      public String selectUserByKeywords() throws InvocationTargetException, NoSuchMethodException, IllegalAccessException, ParseException, NoSuchFieldException {
          this.users = userService.selectUserByKeywords(cols,keywords);
          return Action.SUCCESS;
      }
  }

CardAction

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.study.action;
   
  import com.opensymphony.xwork2.Action;
  import com.opensymphony.xwork2.ActionSupport;
  import com.study.entity.Card;
  import com.study.service.CardService;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.stereotype.Controller;
   
  /**
   * @ClassName CardAction
   * @Description TODO
   * @Author chabai
   * @Date 2022/4/18 15:26
   * @Version 1.0
   */
  @Controller("cardAction")
  public class CardAction extends ActionSupport {
      @Autowired
      private CardService cardService;
      private Card card;
   
      public Card getCard() {
          return card;
      }
   
      public void setCard(Card card) {
          this.card = card;
      }
   
      /**
       * @MethodName insertCard
       * @Description 插入身份信息
       * @return: java.lang.String
       * @Author chabai
       * @Date 2022/4/18 15:28
       */
      public String insertCard(){
          cardService.insertCard(card);
          return Action.SUCCESS;
      }
  }
  1. 引入struts.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<?xml version="1.0" encoding="UTF-8"?>
  
 <!DOCTYPE struts PUBLIC
         "-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
         "http://struts.apache.org/dtds/struts-2.3.dtd">
  
 <struts>
     <!--处理用户信息模块-->
     <package name="user" extends="struts-default" namespace="/user">
         <!--selectAll：http://localhost:8888/mybatis2/user/selectAll-->
         <action name="selectAll" class="userAction" method="selectAll">
             <result name="success">/user/showAllUsers.jsp</result>
         </action>
  
         <!--insertUser：http://localhost:8888/mybatis2/user/insertUser-->
         <action name="insertUser" class="userAction" method="insertUser">
             <result name="success" type="redirectAction">/user/selectAll</result>
         </action>
  
         <!--selectUserByNoCard：http://localhost:8888/mybatis2/user/selectUserByNoCard-->
         <action name="selectUserByNoCard" class="userAction" method="selectUserByNoCard">
             <result name="success">/card/addUserCard.jsp</result>
         </action>
  
         <!--selectUserByKeywords：http://localhost:8888/mybatis2/user/selectUserByKeywords-->
         <action name="selectUserByKeywords" class="userAction" method="selectUserByKeywords">
             <result name="success">/user/showAllUsers.jsp</result>
         </action>
     </package>
  
     <!--处理身份信息模块-->
     <package name="card" extends="struts-default" namespace="/card">
         <!--insertCard：http://localhost:8888/mybatis2/card/insertCard-->
         <action name="insertCard" class="cardAction" method="insertCard">
             <result name="success" type="redirectAction">
                 <!--跨包跳转，注意上面是redirectAction而不是redirect-->
                 <param name="namespace">/user</param>
                 <param name="actionName">selectAll</param>
             </result>
         </action>
     </package>
 </struts>

13.jsp文件

addUser.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
  <%@ taglib prefix="s" uri="/struts-tags" %>
  <%
  String path = request.getContextPath();
  String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
  %>
   
  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <html>
    <head>
      <base href="<%=basePath%>">
      
      <title>My JSP 'index.jsp' starting page</title>
      <meta http-equiv="pragma" content="no-cache">
      <meta http-equiv="cache-control" content="no-cache">
      <meta http-equiv="expires" content="0">    
      <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
      <meta http-equiv="description" content="This is my page">
      <!--
      <link rel="stylesheet" type="text/css" href="styles.css">
      -->
    </head>
    <body>
      <form action="<s:url value="/user/insertUser"/>" method="post">
          用户名:<input type="text" name="user.name"/>
          年龄:<input type="text" name="user.age"/>
          生日:<input type="text" name="user.bir"/><!-- struts2 默认接收日期格式   yyyy-MM-dd -->
          性别:<input type="text" name="user.sex"/>
          <input type="submit" value="提交"/>
      </form>
    </body>
  </html>

showAllUsersCard.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
  <%@ taglib prefix="s" uri="/struts-tags" %>
  <%
  String path = request.getContextPath();
  String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
  %>
   
  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <html>
    <head>
      <base href="<%=basePath%>">
      
      <title>My JSP 'index.jsp' starting page</title>
      <meta http-equiv="pragma" content="no-cache">
      <meta http-equiv="cache-control" content="no-cache">
      <meta http-equiv="expires" content="0">    
      <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
      <meta http-equiv="description" content="This is my page">
      <!--
      <link rel="stylesheet" type="text/css" href="styles.css">
      -->
      <style>
          select{
              width:120px;
          }
          input[type="text"]{
              width:400px;
          }
      </style>
    </head>
    <body>
    <h1>搜索:</h1>
    <form action="<s:url value="/user/selectUserByKeywords"/>" method="post">
        选择搜索的列:
        <select name="cols" multiple="multiple">
            <option value="name">用户名</option>
            <option value="age">年龄</option>
            <option value="bir">生日</option>
            <option value="fork">民族</option>
            <option value="code">身份证号</option>
            <option value="address">住址</option>
        </select>
        <input type="text" name="keywords" />
        <input type="submit" value="搜索"/>
    </form>
    <hr>
    <h1>所有用户信息如下:</h1>
    <table>
        <tr>
            <td>用户id</td>
            <td>用户名</td>
            <td>年龄</td>
            <td>生日</td>
            <td>身份证号</td>
            <td>民族</td>
            <td>住址</td>
        </tr>
        <s:iterator value="users">
            <tr>
                <td><s:property value="id"/></td>
                <td><s:property value="name"/></td>
                <td><s:property value="age"/></td>
                <td><s:date name="bir" format="yyyy-MM-dd"/></td>
                <td><s:property value="card.code"/></td>
                <td><s:property value="card.fork"/></td>
                <td><s:property value="card.address"/></td>
            </tr>
        </s:iterator>
    </table>
    <hr>
    <a href="<s:url value="/user/addUser.jsp"/>">添加用户信息</a>
    <a href="<s:url value="/user/selectUserByNoCard"/>">添加身份信息</a>
    </body>
  </html>

addUserCard.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">    
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <!--
    <link rel="stylesheet" type="text/css" href="styles.css">
    -->
  </head>
  <body>
    <form action="<s:url value="/card/insertCard"/>" method="post">
        请选择用户信息:
        <select name="card.user.id">
            <s:iterator value="users">
                <option value="<s:property value="id"/>"><s:property value="name"/></option>
            </s:iterator>
        </select>
        身份编号:<input type="text" name="card.code"/>
        地址:<input type="text" name="card.address"/>
        民族:<input type="text" name="card.fork"/><!-- struts2 默认接收日期格式   yyyy-MM-dd -->
        <input type="submit" value="提交"/>       
    </form>
  </body>
</html>

14.引入log4j.properties

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
## 根日志
 ## 日志级别
 log4j.rootLogger=ERROR,stdout
 ## 输出位置
 log4j.appender.stdout=org.apache.log4j.ConsoleAppender
 ## 布局
 log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
 ## 格式
 log4j.appender.stdout.layout.conversionPattern=[%p] %d{yyyy-MM-dd} %m%n
  
  
 ## 子日志
 ## 日志级别
 log4j.logger.com.study.dao=DEBUG
 ## 监听spring框架的日志级别
 ## log4j.logger.org.springframework=ERROR

15.部署项目到tomcat服务器进行测试

部署项目：

image-20240328145821825

测试页面：

image-20240328145835483

最终项目结构：

image-20240328145849731

image-20240328145901307