MyBatis基本使用——CRUD综合练习
大约 5 分钟
项目实践：MyBatis基本使用——CRUD综合练习
1．需求案例：完成MyBatis基本使用——CRUD综合练习实践练习。
2．项目架构：
项目实现
一、数据库表创建及初始化
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS user;
CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT,
username varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
password varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO user VALUES (1, 'Peng Yuning', '8WvErMonB8');
INSERT INTO user VALUES (2, 'Takahashi Kasumi', 'YUEsievZJM');
INSERT INTO user VALUES (3, 'Bruce Rodriguez', 'oSKkZSv4wm');
INSERT INTO user VALUES (4, 'Lau Ching Wan', 'F0WfooyAy2');
INSERT INTO user VALUES (5, 'Lui Sze Kwan', 'Msou1bJ0vv');
INSERT INTO user VALUES (6, 'Ernest Crawford', '6BZpnzERaC');
INSERT INTO user VALUES (7, 'Juan Castillo', '8enHa9Ay1D');
INSERT INTO user VALUES (8, 'Fu Wing Kuen', 'kQbmCoKWxa');
INSERT INTO user VALUES (9, 'Valerie Harrison', 'MaIuoHAGHQ');
INSERT INTO user VALUES (10, 'Margaret Henderson', 'pBCv42yItl');
SET FOREIGN_KEY_CHECKS = 1;
二、项目创建
1、导入相关技术依赖（pom.xml）
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>6.0.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter-api -->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.10.0</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/jakarta.annotation/jakarta.annotation-api -->
<dependency>
<groupId>jakarta.annotation</groupId>
<artifactId>jakarta.annotation-api</artifactId>
<version>2.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>6.0.6</version>
</dependency>
<!--数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<!--数据库连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<!--Spring JDBC-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>6.0.6</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
<!--spring-test-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>6.0.6</version>
</dependency>
<!--声明式事务依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>6.0.6</version>
</dependency>
<!--MyBatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.10</version>
</dependency>
<!--Servlet Api-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
2、创建User实体类（User.java）
package com.Ek0wraith.pojo;
import lombok.Data;
/**
* ClassName: User
* Package: com.Ek0wraith.pojo
* Description：TODO
*
* @Author Ek0wraith
* @Create 2024/5/9 11:24
* @Version 1.0
*/
@Data
public class User {
private Integer id;
private String username;
private String password;
}
3、UserMapper接口（UserMapper.java）
package com.Ek0wraith.mapper;
import com.Ek0wraith.pojo.User;
import java.util.List;
/**
* ClassName: UserMapper
* Package: com.Ek0wraith.mapper
* Description：TODO
*
* @Author Ek0wraith
* @Create 2024/5/9 11:24
* @Version 1.0
*/
public interface UserMapper {
int insertUser (User user);
int deleteUser(Integer id);
int updateUser(User user);
User selectUserById(Integer id);
List<User> selectAllUser();
}
4、mybatis配置文件（mybatis-config.xml）
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.Ek0wraith.pojo"/>
</typeAliases>
<!--environments表示配置MyBatis的开发环境，可以配置多个环境，在众多具体环境中，
使用default属性指定实际运行时使用的环境。default属性的取值是environment标签的id属性的值。-->
<environments default="development">
<!--environment表示配置MyBatis的一个具体的环境-->
<environment id="development">
<!--MyBatis的内置的事务管理器-->
<transactionManager type="JDBC"/> <!--tx-->
<!--配置数据源-->
<dataSource type="POOLED">
<!--建立数据库连接的具体信息-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/usermis"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--Mapper注册：指定MyBatis映射文件的具体位置-->
<!--mapper标签：配置一个具体的Mapper映射文件-->
<!--resource属性：指定Mapper映射文件的实际存储位置，这里需要使用一个以类路径根目录为基准的相对路径-->
<!--对Maven工程的目录结构来说，resources目录下的内容会直接放入类路径，所以这里可以以resources目录为基准-->
<mapper resource="mappers/UserMapper.xml"/>
</mappers>
</configuration>
5、MapperXML编写（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.Ek0wraith.mapper.UserMapper">
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into users(username, password) values(#{username}, #{password})
</insert>
<delete id="deleteUser">
delete from users where id = #{id}
</delete>
<update id="updateUser">
update users set username=#{username}, password=#{password} where id = #{id}
</update>
<select id="selectUserById" resultType="user">
select * from users where id = #{id}
</select>
<select id="selectAllUser" resultType="user">
select * from users
</select>
</mapper>
三、编写测试类
1、获取SqlSession连接
private static SqlSessionFactory sqlSessionFactory;
static {
try {
//绑定配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//获取SqlSession连接
SqlSession session = sqlSessionFactory.openSession();
2、编写CURD测试类（TestUser.java完整代码）
import com.Ek0wraith.mapper.UserMapper;
import com.Ek0wraith.pojo.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.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* ClassName: TestUpdateUser
* Package: PACKAGE_NAME
* Description：TODO
*
* @Author Ek0wraith
* @Create 2024/5/9 11:46
* @Version 1.0
*/
public class TestUser {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
//绑定配置文件
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//获取SqlSession连接
SqlSession session = sqlSessionFactory.openSession();
@Test
public void TestUpdateUser(){
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.selectUserById(1);
user.setUsername("admin");
user.setPassword("123123");
userMapper.updateUser(user);
user = userMapper.selectUserById(1);
System.out.println("user = " + user);
}
@Test
public void TestSelectById(){
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.selectUserById(1);
System.out.println("user = " + user);
}
@Test
public void TestAllUser(){
UserMapper userMapper = session.getMapper(UserMapper.class);
List<User> users = userMapper.selectAllUser();
System.out.println("users = " + users);
}
@Test
public void TestInsertUser(){
User user = new User();
user.setUsername("admin");
user.setPassword("123123");
UserMapper userMapper = session.getMapper(UserMapper.class);
userMapper.insertUser(user);
System.out.println("user = " + user);
}
@Test
public void TestDeleteUser(){
UserMapper userMapper = session.getMapper(UserMapper.class);
userMapper.deleteUser(2);
User user = userMapper.selectUserById(2);
System.out.println("User = " + user);
}
}
3、运行测试类
Checking to see if class com.Ek0wraith.pojo.User matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 2013768748.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7807ac2c]
==> Preparing: select * from users where id = ?
==> Parameters: 1(Integer)
<== Columns: id, username, password
<== Row: 1, Peng Yuning, 8WvErMonB8
<== Total: 1
==> Preparing: update users set username=?, password=? where id = ?
==> Parameters: admin(String), 123123(String), 1(Integer)
<== Updates: 1
==> Preparing: select * from users where id = ?
==> Parameters: 1(Integer)
<== Columns: id, username, password
<== Row: 1, admin, 123123
<== Total: 1
user = User(id=1, username=admin, password=123123)
Opening JDBC Connection
Created connection 1894698434.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@70eecdc2]
==> Preparing: insert into users(username, password) values(?, ?)
==> Parameters: admin(String), 123123(String)
<== Updates: 1
user = User(id=11, username=admin, password=123123)
Opening JDBC Connection
Created connection 876962272.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@344561e0]
==> Preparing: delete from users where id = ?
==> Parameters: 2(Integer)
<== Updates: 1
==> Preparing: select * from users where id = ?
==> Parameters: 2(Integer)
<== Total: 0
User = null
Opening JDBC Connection
Created connection 1388849499.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@52c8295b]
==> Preparing: select * from users
==> Parameters:
<== Columns: id, username, password
<== Row: 1, Peng Yuning, 8WvErMonB8
<== Row: 2, Takahashi Kasumi, YUEsievZJM
<== Row: 3, Bruce Rodriguez, oSKkZSv4wm
<== Row: 4, Lau Ching Wan, F0WfooyAy2
<== Row: 5, Lui Sze Kwan, Msou1bJ0vv
<== Row: 6, Ernest Crawford, 6BZpnzERaC
<== Row: 7, Juan Castillo, 8enHa9Ay1D
<== Row: 8, Fu Wing Kuen, kQbmCoKWxa
<== Row: 9, Valerie Harrison, MaIuoHAGHQ
<== Row: 10, Margaret Henderson, pBCv42yItl
<== Total: 10
users = [User(id=1, username=Peng Yuning, password=8WvErMonB8), User(id=2, username=Takahashi Kasumi, password=YUEsievZJM), User(id=3, username=Bruce Rodriguez, password=oSKkZSv4wm), User(id=4, username=Lau Ching Wan, password=F0WfooyAy2), User(id=5, username=Lui Sze Kwan, password=Msou1bJ0vv), User(id=6, username=Ernest Crawford, password=6BZpnzERaC), User(id=7, username=Juan Castillo, password=8enHa9Ay1D), User(id=8, username=Fu Wing Kuen, password=kQbmCoKWxa), User(id=9, username=Valerie Harrison, password=MaIuoHAGHQ), User(id=10, username=Margaret Henderson, password=pBCv42yItl)]
Opening JDBC Connection
Created connection 1389432760.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@52d10fb8]
==> Preparing: select * from users where id = ?
==> Parameters: 1(Integer)
<== Columns: id, username, password
<== Row: 1, Peng Yuning, 8WvErMonB8
<== Total: 1
user = User(id=1, username=Peng Yuning, password=8WvErMonB8)
Process finished with exit code 0
测试通过