1. application.properties
# 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=root
spring.datasource.password=root
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/*.xml # sql代码存放位置
mybatis.type-aliases-package=com.xx.xx.entity # 实体类位置
mybatis.configuration.useGeneratedKeys=true
mybatis.configuration.mapUnderscoreToCamelCase=true
2. UserMapper.java
@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);
}
3.user-mapper.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.zx.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>
4. 测试MapperTest.java
@RunWith(SpringRunner.class)
@SpringBootTest
@ContextConfiguration(classes = CommunityApplication.class)
public class MapperTest {
@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("liubei101@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.commity.com/101.png");
user.setCreateTime(new Date());
int rows = userMapper.insertUser(user);
System.out.println(rows);
System.out.println(user.getId());
}
@Test
public void testUpdateUser() {
int rows = userMapper.updateStatus(150,1);
System.out.println(rows);
rows = userMapper.updateHeader(150,"http://www.commity.com/102.png");
System.out.println(rows);
rows = userMapper.updatePassword(150,"hello");
System.out.println(rows);
}
}