目录
- 一、通过JDBC访问数据库
- 1、思路
- 2、示例
- 3、思考
- 二、通过ORM框架访问数据库(主要是Mybatis)
- 1、示例
- 1.1 配置
- 1.2 SQL写在xxxMapper.xml中:mapper/UserMapper.xml
- 1.3 xxxMapper.xml对应的xxxMapper接口(Application通过该接口访问数据库)
- 1.4 测试
- 2、思考
- 三、SpringBoot整合Mybatis
- 1、引入依赖
- 1.1 为什么需要引入spring-boot-starter-parent?(不引入会出现什么问题?)
- 1.2 引入spring-boot-starter-parent(进行依赖管理)
- 2、写配置文件
- 2.1 application.properties
- 2.1.1 使用Spring Boot的默认配置
- 3、xxxDO、xxxMapper.xml、xxxMapper.java
- 3.1 目录结构![在这里插入图片描述](https://s2.51cto.com/images/blog/202406/27035158_667c715ed2b0c12039.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_30,g_se,x_10,y_10,shadow_20,type_ZmFuZ3poZW5naGVpdGk=)
- 3.2 UserDO
- 3.3 UserMapper.java
- 3.4 UserMapper.xml
- 4、测试
- 4.1 引入spring-boot-starter-test依赖
- 4.2 测试类:UserMapperTest
- 5、自定义mybatis配置
- 5.1 mybatis-config.xml
一、通过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