下面是一个使用 MyBatis 完整操作一张表的 CURD 的操作的示例：
- 创建一张表并插入数据：
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT,
email VARCHAR(50)
);
INSERT INTO user (name, age, email) VALUES ('Alice', 20, '[email protected]');
INSERT INTO user (name, age, email) VALUES ('Bob', 25, '[email protected]');
- 创建一个 User 实体类，对应数据库表中的一行数据：
public class User {
private Integer id;
private String name;
private Integer age;
private String email;
// getter and setter methods
}
- 编写 MyBatis Mapper 接口，定义对 user 表的操作：
public interface UserMapper {
@Select("SELECT * FROM user")
List<User> getAllUsers();
@Select("SELECT * FROM user WHERE id = #{id}")
User getUserById(Integer id);
@Insert("INSERT INTO user (name, age, email) VALUES (#{name}, #{age}, #{email})")
@Options(useGeneratedKeys = true, keyProperty = "id")
void insertUser(User user);
@Update("UPDATE user SET name = #{name}, age = #{age}, email = #{email} WHERE id = #{id}")
void updateUser(User user);
@Delete("DELETE FROM user WHERE id = #{id}")
void deleteUser(Integer id);
}
@Select注解用于查询操作，其中的 SQL 语句对应数据库的查询语句；@Insert注解用于插入操作，其中的 SQL 语句对应数据库的插入语句；@Options注解用于指定插入操作的主键生成方式；@Update注解用于更新操作，其中的 SQL 语句对应数据库的更新语句；@Delete注解用于删除操作，其中的 SQL 语句对应数据库的删除语句。
- 编写 MyBatis Mapper XML 文件，定义对 user 表的操作：
<mapper namespace="com.example.mapper.UserMapper">
<select id="getAllUsers" resultType="com.example.model.User">
SELECT * FROM user
</select>
<select id="getUserById" resultType="com.example.model.User" parameterType="java.lang.Integer">
SELECT * FROM user WHERE id = #{id}
</select>
<insert id="insertUser" parameterType="com.example.model.User" useGeneratedKeys="true" keyProperty="id">
INSERT INTO user (name, age, email) VALUES (#{name}, #{age}, #{email})
</insert>
<update id="updateUser" parameterType="com.example.model.User">
UPDATE user SET name = #{name}, age = #{age}, email = #{email} WHERE id = #{id}
</update>
<delete id="deleteUser" parameterType="java.lang.Integer">
DELETE FROM user WHERE id = #{id}
</delete>
</mapper>
<mapper>标签用于定义 MyBatis Mapper；<select>标签用于查询操作，其中的 SQL 语句对应数据库的查询语句；<insert>标签用于插入操作，其中的 SQL 语句对应数据库的插入语句；<update>标签用于更新操作，其中的 SQL 语句对应数据库的更新语句；<delete>标签用于删除操作，其中的 SQL 语句对应数据库的删除语句；resultType属性用于指定查询结果的类型；parameterType属性用于指定参数的类型；useGeneratedKeys属性用于指定插入操作的主键生成方式；keyProperty属性用于指定插入操作的主键属性。
- 编写 MyBatis 配置文件，指定 Mapper 文件的位置和数据库连接信息：
<configuration>
<mappers>
<mapper resource="com/example/mapper/UserMapper.xml"/>
</mappers>
<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/mybatis_example"/>
<property name="username" value="root"/>
<property name="password" value="password"/>
</dataSource>
</environment>
</environments>
</configuration>
<mappers>标签用于指定 Mapper 文件的位置；<mapper>标签的resource属性用于指定 Mapper 文件的路径；<environments>标签用于指定数据库连接信息；<transactionManager>标签用于指定事务管理器的类型；<dataSource>标签用于指定数据源的类型和连接信息；<property>标签用于指定连接信息的具体值。
- 在 Spring Boot 中配置 MyBatis，指定 MyBatis 配置文件的位置和 Mapper 扫描的包：
@Configuration
@MapperScan("com.example.mapper")
public class MyBatisConfig {
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean(DataSource dataSource) {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
try {
sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResources("classpath*:com/example/mapper/*.xml"));
} catch (IOException e) {
e.printStackTrace();
}
return sqlSessionFactoryBean;
}
}
@Configuration注解用于将该类注册为 Spring 配置类；@MapperScan注解用于指定 Mapper 扫描的包；SqlSessionFactoryBean用于创建SqlSessionFactory实例；setDataSource方法用于指定数据源；setMapperLocations方法用于指定 Mapper 文件的位置；PathMatchingResourcePatternResolver和ResourcePatternResolver用于扫描 Mapper 文件。
- 编写一个测试类，用于验证 MyBatis 的 CURD 操作：
@SpringBootTest
class UserMapperTest {
@Autowired
private UserMapper userMapper;
@Test
void testGetAllUsers() {
List<User> userList = userMapper.getAllUsers();
System.out.println(userList);
}
@Test
void testGetUserById() {
User user = userMapper.getUserById(1);
System.out.println(user);
}
@Test
void testInsertUser() {
User user = new User();
user.setName("Charlie");
user.setAge(30);
user.setEmail("[email protected]");
userMapper.insertUser(user);
System.out.println(user);
}
@Test
void testUpdateUser() {
User user = userMapper.getUserById(1);
user.setName("Alice2");
user.setAge(21);
user.setEmail("[email protected]");
userMapper.updateUser(user);
System.out.println(userMapper.getUserById(1));
}
@Test
void testDeleteUser() {
userMapper.deleteUser(1);
System.out.println(userMapper.getUserById(1));
}
}
- 运行测试类，观察控制台输出，可以看到查询结果和操作结果。
完整代码如下：
User.java：
public class User {
private Integer id;
private String name;
private Integer age;
private String email;
// getter and setter methods
}
UserMapper.java：
public interface UserMapper {
@Select("SELECT * FROM user")
List<User> getAllUsers();
@Select("SELECT * FROM user WHERE id = #{id}")
User getUserById(Integer id);
@Insert("INSERT INTO user (name, age, email) VALUES (#{name}, #{age}, #{email})")
@Options(useGeneratedKeys = true, keyProperty = "id")
void insertUser(User user);
@Update("UPDATE user SET name = #{name}, age = #{age}, email = #{email} WHERE id = #{id}")
void updateUser(User user);
@Delete("DELETE FROM user WHERE id = #{id}")
void deleteUser(Integer id);
}
UserMapper.xml：
<mapper namespace="com.example.mapper.UserMapper">
<select id="getAllUsers" resultType="com.example.model.User">
SELECT * FROM user
</select>
<select id="getUserById" resultType="com.example.model.User" parameterType="java.lang.Integer">
SELECT * FROM user WHERE id = #{id}
</select>
<insert id="insertUser" parameterType="com.example.model.User" useGeneratedKeys="true" keyProperty="id">
INSERT INTO user (name, age, email) VALUES (#{name}, #{age}, #{email})
</insert>
<update id="updateUser" parameterType="com.example.model.User">
UPDATE user SET name = #{name}, age = #{age}, email = #{email} WHERE id = #{id}
</update>
<delete id="deleteUser" parameterType="java.lang.Integer">
DELETE FROM user WHERE id = #{id}
</delete>
</mapper>
MyBatisConfig.java：
@Configuration
@MapperScan("com.example.mapper")
public class MyBatisConfig {
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean(DataSource dataSource) {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
try {
sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResources("classpath*:com/example/mapper/*.xml"));
} catch (IOException e) {
e.printStackTrace();
}
return sqlSessionFactoryBean;
}
}
UserMapperTest.java：
@SpringBootTest
class UserMapperTest {
@Autowired
private UserMapper userMapper;
@Test
void testGetAllUsers() {
List<User> userList = userMapper.getAllUsers();
System.out.println(userList);
}
@Test
void testGetUserById() {
User user = userMapper.getUserById(1);
System.out.println(user);
}
@Test
void testInsertUser() {
User user = new User();
user.setName("Charlie");
user.setAge(30);
user.setEmail("[email protected]");
userMapper.insertUser(user);
System.out.println(user);
}
@Test
void testUpdateUser() {
User user = userMapper.getUserById(1);
user.setName("Alice2");
user.setAge(21);
user.setEmail("[email protected]");
userMapper.updateUser(user);
System.out.println(userMapper.getUserById(1));
}
@Test
void testDeleteUser() {
userMapper.deleteUser(1);
System.out.println(userMapper.getUserById(1));
}
}
