Java访问数据库（重点：SpringBoot整合Mybatis）

一、通过JDBC访问数据库

1、思路

• 可以把jdbc理解为访问数据库的接口，访问数据库的细节由驱动实现。驱动由数据库提供商遵守jdbc规范来实现。

2、示例

• maven引入mysql驱动

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

• 代码

public class MainClass {
    public static void main(String[] args) {
        try (Connection conn = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/learn_mybatis",
                        "root", "xxx")) {
            PreparedStatement selectStatement = conn.prepareStatement("select * from user where id =  ?");
            selectStatement.setInt(1, 1);
            ResultSet rs = selectStatement.executeQuery();
            while (rs.next()) {
                Integer id = rs.getInt("id");
                String lastName = rs.getString("name");
                Integer age = rs.getInt("age");
                UserDO userDO = new UserDO()
                        .setId(id)
                        .setName(lastName)
                        .setAge(age);
                System.out.println(userDO);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3、思考

• 从示例代码可知，实际开发中，直接通过JDBC进行增删改查太不方便了，例如，我们需要手动完成数据库的记录和Java实体类的映射。
• 因此，我们迫切需要ORM（对象关系映射）框架来简化开发。

二、通过ORM框架访问数据库（主要是Mybatis）

官方文档

1、示例

框架 = 配置 + API

1.1 配置

• src/main/resources/mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <!-- 配置日志   -->
    <settings>
        <setting name="logImpl" value="LOG4J2"/>
    </settings>
    <environments default="development">
        <!-- 配置连接数据库的基本信息       -->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/learn_mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="xxx"/>
            </dataSource>
        </environment>
    </environments>
    
    <!-- 配置映射文件（写SQL的地方）-->
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>

• src/main/resources/log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration xmlns="http://logging.apache.org/log4j/2.0/config">

    <Appenders>
        <Console name="stdout" target="SYSTEM_OUT">
            <PatternLayout pattern="%5level [%t] - %msg%n"/>
        </Console>
    </Appenders>

    <Loggers>
        <Logger name="com.forrest.learn.mapper.UserMapper" level="trace"/>
        <Root level="error" >
            <AppenderRef ref="stdout"/>
        </Root>
    </Loggers>

</Configuration>

1.2 SQL写在xxxMapper.xml中：mapper/UserMapper.xml

• src/main/resources/mapper/UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.forrest.learn.mapper.UserMapper">
   <select id="selectById" resultType="com.forrest.learn.pojo.UserDO">
       select id, name, age from user where id = #{id}
   </select>
</mapper>

• src/main/java/com/forrest/learn/pojo/UserDO.java

@Data
@Accessors(chain = true)
public class UserDO {
    private Integer id;
    private String name;
    private Integer age;
}

1.3 xxxMapper.xml对应的xxxMapper接口（Application通过该接口访问数据库）

• src/main/java/com/forrest/learn/mapper/UserMapper.java

public interface UserMapper {
    UserDO selectById(Integer id);
}

1.4 测试

• src/test/java/com/forrest/learn/mapper/UserMapperTest.java

public class UserMapperTest {

    @Test
    public void selectById() {
        try (InputStream is = Resources.getResourceAsStream("mybatis-config.xml")) {
            SqlSessionFactoryBuilder ssfBuilder = new SqlSessionFactoryBuilder();
            SqlSessionFactory ssFactory = ssfBuilder.build(is);
            SqlSession sqlSession = ssFactory.openSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            UserDO userDO = userMapper.selectById(1);
            System.out.println(userDO);
        } catch (IOException e) {
            System.out.println("读取配置文件失败, caused by " + e.getMessage());
        }
    }
}

• 结果：

DEBUG [main] - ==>  Preparing: select id, name, age from user where id = ?
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, name, age
TRACE [main] - <==        Row: 1, 张三, 18
DEBUG [main] - <==      Total: 1
UserDO(id=1, name=张三, age=18)

2、思考

• 上面的方式还不够方便（在增删改查前，还有一些模板代码），而实际开发中，直接注入UserMapper，就可以增删改查了，怎么实现的？

三、SpringBoot整合Mybatis

1、引入依赖

1.1 为什么需要引入spring-boot-starter-parent？（不引入会出现什么问题？）

<dependencies>
        <!-- web开发需要 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.6.3</version>
        </dependency>
        
        <!-- 整合mybatis需要 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        
        <!-- mybatis驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
    </dependencies>

• 两个starter会引入一堆依赖，如果没有“spring-boot-starter-parent”进行依赖管理，那么很容易导致依赖冲突：
  

1.2 引入spring-boot-starter-parent（进行依赖管理）

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.6.3</version>
</parent>

<dependencies>
    <!-- web开发需要 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- 整合mybatis需要 -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.2</version>
    </dependency>

    <!-- mybatis驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

• spring-boot-starter-parent和mybatis-spring-boot-starter版本怎么选？
• 如果用的是java8，那么可以选支持java8的最新版本。

• spring官网，支持java8的最新版本为：Spring Boot 2.7.18
  
  
  

• 通过官网可以找到合适的mybatis-spring-boot-starter版本：2.3.x（选最大的：2.3.2）
  

• 升级版本

<dependencies>
    <!-- web开发需要 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- 整合mybatis需要 -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.3.2</version>
    </dependency>

    <!-- mybatis驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
</dependencies>

• 升级版本后，mysql驱动必须指定版本了，估计是starter删除了对mysql驱动的依赖管理。那选什么版本呢？（8.0.33）
  

当然了，选版本不用精细到x.y.z的z。找到符合要求的x.y，z能在maven仓库中搜到即可。

2、写配置文件

2.1 application.properties

src/main/resources/application.properties

2.1.1 使用Spring Boot的默认配置

• 直接在application.properties（或application.yml）文件中配置数据库连接信息即可，而不用写mybatis-config.xml。

spring.datasource.url=jdbc:mysql://localhost:3306/learn_mybatis
spring.datasource.username=root
spring.datasource.password=xxx
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

当使用Spring Boot时，它会自动加载与spring.datasource.*相关的配置。数据源会自动注入到sqlSessionFactory中，而sqlSessionFactory会自动注入到Mapper中。因此，直接通过xxxMapper就能进行增删改查了。

3、xxxDO、xxxMapper.xml、xxxMapper.java

3.1 目录结构

• 基于"Free Mybatis Tool"生成UserDO（放model包下）、UserMapper.java（放mapper包下）、UserMapper.xml（resources/mapper）
  
  

检查生成的代码是否满足需求。

引入lombok（选最新版本，通常都是支持java8的）

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.30</version>
</dependency>

• 如果Mapper接口和Mapper XML文件放置在不同的包路径下（如上图所示），那么必须在MyBatis的配置中正确指定Mapper XML文件的位置。
• 在Spring Boot应用中，可以通过在application.properties或application.yml中配置mybatis.mapper-locations属性来指定Mapper XML文件的位置。

mybatis.mapper-locations=classpath:mapper/*.xml

不配置的话，必须保证：Mapper接口的包路径和Mapper XML文件的路径是一致的。

3.2 UserDO

@Data
public class UserDO implements Serializable {
    private static final long serialVersionUID = 3178995131329607398L;

    private Integer id;
    private String name;
    private Integer age;
}

3.3 UserMapper.java

@Mapper
public interface UserMapper {
    int insert(UserDO record);

    int deleteById(Integer id);

    int update(UserDO record);

    UserDO selectById(Integer id);
}

3.4 UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.forrest.springboot.mybatis.dao.mapper.UserMapper">
  <resultMap id="UserMap" type="com.forrest.springboot.mybatis.dao.model.UserDO">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="age" jdbcType="INTEGER" property="age" />
  </resultMap>

  <sql id="tb">
    user
  </sql>

  <sql id="cols_exclude_id">
    `name`, age
  </sql>

  <sql id="cols_all">
    id, <include refid="cols_exclude_id" />
  </sql>

  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.forrest.springboot.mybatis.dao.model.UserDO" useGeneratedKeys="true">
    insert into <include refid="tb" />
    (<include refid="cols_exclude_id" />)
    values
    (#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})
  </insert>

  <delete id="deleteById" parameterType="java.lang.Integer">
    delete from <include refid="tb" />
    where id = #{id,jdbcType=INTEGER}
  </delete>

  <update id="update" parameterType="com.forrest.springboot.mybatis.dao.model.UserDO">
    update <include refid="tb" />
    <set>
      <if test="name != null">
        `name` = #{name,jdbcType=VARCHAR},
      </if>
      <if test="age != null">
        age = #{age,jdbcType=INTEGER}
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>

  <select id="selectById" parameterType="java.lang.Integer" resultMap="UserMap">
    select
        <include refid="cols_all" />
    from
        <include refid="tb" />
    where
        id = #{id,jdbcType=INTEGER}
  </select>
</mapper>

我个人更喜欢这种xml的方式，java代码和sql代码分离。

4、测试

4.1 引入spring-boot-starter-test依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

• 注意：不要同时引入junit5依赖，否则可能因为版本冲突导致报错“TestEngine with ID ‘junit-jupiter‘ failed to discover test”。
  

4.2 测试类：UserMapperTest

src/test/java/com/forrest/springboot/mybatis/dao/mapper/UserMapperTest.java

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    void insert() {
        UserDO user = new UserDO();
        user.setName("John");
        user.setAge(30);
        userMapper.insert(user);

        assertNotNull(user.getId(), "Insert should generate ID");

        UserDO retrievedUser = userMapper.selectById(user.getId());
        assertNotNull(retrievedUser, "User should be retrieved after insertion");
        assertEquals("John", retrievedUser.getName(), "Name should match");
        assertEquals(30, retrievedUser.getAge(), "Age should match");
    }

    @Test
    void deleteById() {
        UserDO user = new UserDO();
        user.setName("Alice");
        user.setAge(25);
        userMapper.insert(user);

        Integer userId = user.getId();
        assertNotNull(userId, "Insert should generate ID");

        userMapper.deleteById(userId);

        UserDO deletedUser = userMapper.selectById(userId);
        assertNull(deletedUser, "User should be deleted");
    }

    @Test
    void update() {
        UserDO user = new UserDO();
        user.setName("Bob");
        user.setAge(35);
        userMapper.insert(user);

        Integer userId = user.getId();
        assertNotNull(userId, "Insert should generate ID");

        user.setName("UpdatedName");
        user.setAge(40);
        userMapper.update(user);

        UserDO updatedUser = userMapper.selectById(userId);
        assertNotNull(updatedUser, "User should exist after update");
        assertEquals("UpdatedName", updatedUser.getName(), "Name should be updated");
        assertEquals(40, updatedUser.getAge(), "Age should be updated");
    }

    @Test
    void selectById() {
        UserDO user = new UserDO();
        user.setName("Eve");
        user.setAge(28);
        userMapper.insert(user);

        Integer userId = user.getId();
        assertNotNull(userId, "Insert should generate ID");

        UserDO retrievedUser = userMapper.selectById(userId);
        assertNotNull(retrievedUser, "User should be retrieved by ID");
        assertEquals("Eve", retrievedUser.getName(), "Name should match");
        assertEquals(28, retrievedUser.getAge(), "Age should match");
    }
}

5、自定义mybatis配置

mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

• 为啥需要这两个？通过mybatis官方文档就知道了，需要一个配置文件和一堆映射文件。
  
• 为啥key是mybatis.config-location和mybatis.mapper-locations？这个要看：
  

5.1 mybatis-config.xml

习惯将mybatis的配置文件命名为mybatis-config.xml

• 具体怎么配置，看官方文档。