1. 功能描述
① 现有一个user表，包含以下字段：username, password, salt, email, type, status, activation_code, header_url, create_time。
② 实现增删改查功能。
③ 采用 Spring Boot 和 MyBatis框架实现。
2. Entity
src.main.java.com.nowcoder.community.entity.User.java
实体类User.java
类中的私有成员变量与数据库中user表中的字段对应。
package com.nowcoder.community.entity;
import java.util.Date;
public class User {
private int id;
private String username;
private String password;
private String salt;
private String email;
private int type;
private int status;
private String activationCode;
private String headerUrl;
private Date createTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSalt() {
return salt;
}
public void setSalt(String salt) {
this.salt = salt;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public String getActivationCode() {
return activationCode;
}
public void setActivationCode(String activationCode) {
this.activationCode = activationCode;
}
public String getHeaderUrl() {
return headerUrl;
}
public void setHeaderUrl(String headerUrl) {
this.headerUrl = headerUrl;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", salt='" + salt + '\'' +
", email='" + email + '\'' +
", type=" + type +
", status=" + status +
", activationCode='" + activationCode + '\'' +
", headerUrl='" + headerUrl + '\'' +
", createTime=" + createTime +
'}';
}
}
3. DAO
src.main.java.com.nowcoder.community.dao.UserMapper.java
接口类UserMapper.java
package com.nowcoder.community.dao;
import com.nowcoder.community.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper {
User selectById(int id);
User selectByName(String username);
User selectByEmail(String email);
int insertUser(User user);
int updateStatus(int id, int status);
int updateHeader(int id, String headerUrl);
int updatePassword(int id, String password);
}
4. XML映射
src.main.resources.mapper.user-mapper.xml
user-mapper.xml
xml 文件包含增删改查等 sql 语句：
<?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.nowcoder.community.dao.UserMapper">
<sql id="insertFields">
username, password, salt, email, type, status, activation_code, header_url, create_time
</sql>
<sql id="selectFields">
id, username, password, salt, email, type, status, activation_code, header_url, create_time
</sql>
<select id="selectById" resultType="User">
select <include refid="selectFields"></include>
from user
where id = #{id}
</select>
<select id="selectByName" resultType="User">
select <include refid="selectFields"></include>
from user
where username = #{username}
</select>
<select id="selectByEmail" resultType="User">
select <include refid="selectFields"></include>
from user
where email = #{email}
</select>
<insert id="insertUser" parameterType="User" keyProperty="id">
insert into user (<include refid="insertFields"></include>)
values(#{username}, #{password}, #{salt}, #{email}, #{type}, #{status}, #{activationCode}, #{headerUrl}, #{createTime})
</insert>
<update id="updateStatus">
update user set status = #{status} where id = #{id}
</update>
<update id="updateHeader">
update user set header_url = #{headerUrl} where id = #{id}
</update>
<update id="updatePassword">
update user set password = #{password} where id = #{id}
</update>
</mapper>
其中，
① 代码<sql id="xxx"></sql>可以实现字段的复用。
② #{字段}可以自动替换为指定的值
③ 区别：${}是拼接符，传入的内容将拼接在sql中且不进行jdbc类型的转换
#{}是占位符，可以设置值并自动进行java和jdbc类型转换
5. 配置文件
src.resources.application.properties
application.properties
# ServerProperties
server.port=8080
server.servlet.context-path=/community
# ThymeleafProperties
spring.thymeleaf.cache=false
# DataSourceProperties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/community?characterEncoding=utf-8&useSSL=false&serverTimezone=Hongkong
spring.datasource.username=xxxx
spring.datasource.password=xxxxxx
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.maximum-pool-size=15
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000
# MybatisProperties
mybatis.mapper-locations=classpath:mapper
其中，mybatis.configuration.mapUnderscoreToCamelCase=true的作用是将带有下划线的表字段映射为驼峰格式的实体类属性。
6. 测试功能
src.test.java.com.nowcoder.community.MapperTests.java
MapperTests.java
package com.nowcoder.community;
import com.nowcoder.community.dao.UserMapper;
import com.nowcoder.community.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ContextConfiguration;
import java.util.Date;
@SpringBootTest
@ContextConfiguration(classes = CommunityApplication.class)
public class MapperTests {
@Autowired
private UserMapper userMapper;
@Test
public void testSelectUser() {
User user = userMapper.selectById(101);
System.out.println(user);
user = userMapper.selectByName("liubei");
System.out.println(user);
user = userMapper.selectByEmail("nowcoder101@sina.com");
System.out.println(user);
}
@Test
public void testInsertUser() {
User user = new User();
user.setUsername("test");
user.setPassword("123456");
user.setSalt("abc");
user.setEmail("test@qq.com");
user.setHeaderUrl("http://www.nowcoder.com/101.png");
user.setCreateTime(new Date());
int rows = userMapper.insertUser(user);
System.out.println(rows);
System.out.println(user.getId());
}
@Test
public void updataUser() {
int rows = userMapper.updateStatus(150, 1);
System.out.println(rows);
rows = userMapper.updateHeader(150, "http://www.nowcoder.com/102.png");
System.out.println(rows);
rows = userMapper.updatePassword(150, "newpsw");
System.out.println(rows);
}
}
7.总结
MyBatis核心组件：
① SqlSessionFactory：用于创建 SqlSession 的工厂类
② SqlSession：Mybatis 的核心，用于向数据库执行 SQL
③ 主配置文件：XML 配置，可以对 MyBatis 的底层行为进行详细配置
④ Mapper接口：即DAO接口，在 MyBatis 中习惯称之为Mapper
⑤ Mapper映射器：用于编写 SQL 并将 SQL 和实体类映射的组件，采用 XML、注解均可实现。
|