【牛客论坛项目预热】mybatis之处理增删改查

---

一、配置application.properties【数据库】

server.servlet.context-path=/community

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/community?characterEncoding=utf-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=123456
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

mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.nowcoder.community.resume.entity
mybatis.configuration.use-generated-keys=true
mybatis.configuration.map-underscore-to-camel-case=true

logging.level.com.nowcoder.community.resume=debug

二、创建实体类

package com.nowcoder.community.resume.entity;

import com.mysql.cj.conf.PropertyDefinitions;

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 +
                '}';
    }
}

三、创建接口UserMapper

@Mapper
public interface UseMapper {
    User selectById(int id);
    User selectByName(String name);
    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);
}

四、配置UserMapper.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.resume.dao.UseMapper">
    <sql id="selectField">
        id,username,password,salt,email,type,status,activation_code,header_url,create_time
    </sql>

    <sql id="insertField">
        username,password,salt,email,type,status,activation_code,header_url,create_time
    </sql>

    <select id="selectById" resultType="User">
        select <include refid="selectField"></include>
        from user
        where id = #{id}
    </select>

    <select id="selectByName" resultType="User">
        select <include refid="selectField"></include>
        from user
        where username = #{username}
    </select>

    <select id="selectByEmail" resultType="User">
        select <include refid="selectField"></include>
        from user
        where email = #{email}
    </select>


    <insert id="insertUser" parameterType="User" keyProperty="id">
        insert into user (<include refid="insertField"></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>

五、测试类

@RunWith(SpringRunner.class)
@SpringBootTest
@ContextConfiguration(classes = ResumeApplication.class)
public class MapperTest {
    @Autowired
    private UseMapper useMapper;

    @Test
    public void testSelectUser(){
        User user = useMapper.selectById(101);
        System.out.println(user);

        user = useMapper.selectByName("liubei");
        System.out.println(user);

        user = useMapper.selectByEmail("nowcoder101@sina.com");
        System.out.println(user);
    }

    @Test
    public void testInsertUser(){

        User user = new User();
        user.setUsername("baiwenyan");
        user.setPassword("111111111");
        user.setSalt("abc");
        user.setEmail("test@qq.com");
        user.setHeaderUrl("http://www.nowcoder.com/101.png");
        user.setCreateTime(new Date());
        int i = useMapper.insertUser(user);
        System.out.println(i);
        System.out.println(user.getId());
    }

    @Test
    public void updateUser(){
        int rows = useMapper.updateStatus(150,1);
        System.out.println(rows);

        rows = useMapper.updateHeader(150,"http://www.nowcoder.com/101.png");
        System.out.println(rows);

        rows = useMapper.updatePassword(150, "111111111");
        System.out.println(rows);

    }
}

---