MyBatis注解的使用（MySQL数据库）

1、pom.xml

<!-- mysql驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<!-- mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.1</version>
</dependency>

2、application.yml

# 连接数据库
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost/all?characterEncoding=utf-8&useSSL=false
    username: root
    password: root

#  控制台打印sql语句（mybatis）
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

#  控制台打印sql语句（日志），com.ws.all.bean.mapper是mapper所在的包名
#logging:
#  level:
#    com.ws.all.bean.mapper: trace

3、Student.java实体类

package com.ws.all.bean;

import lombok.Data;

/**
 * @Data
 * [lombok的使用](https://app.yinxiang.com/shard/s16/nl/19291223/0dfbeb41-cb35-41e7-8935-15f512f79a9c?title=Spring%20Boot%E4%B8%8B%E7%9A%84lombok%E5%AE%89%E8%A3%85%E4%BB%A5%E5%8F%8A%E4%BD%BF%E7%94%A8%E7%AE%80%E4%BB%8B%20-%20CSDN%E5%8D%9A%E5%AE%A2%20lombok%E7%9A%84%E4%BD%BF%E7%94%A8)
 */
@Data
public class Student {

    private String studentNumber;
    private String studentName;
    private Integer age;

    public Student() {
    }

    public Student(String studentNumber, String studentName, Integer age) {
        this.studentNumber = studentNumber;
        this.studentName = studentName;
        this.age = age;
    }
}

4、StudentMapper.java

package com.ws.all.bean.mapper;

import com.ws.all.bean.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

public interface StudentMapper {

    /**
     * 通过对象新增
     * @param student
     * @return
     */
    @Insert("insert into student(student_number, student_name, age) values (#{studentNumber, jdbcType=VARCHAR}, #{studentName, jdbcType=VARCHAR}, #{age, jdbcType=INTEGER})")
    int insertByObject(Student student);

    /**
     * 通过map新增
     * @param map
     * @return
     */
    @Insert("insert into student(student_number, student_name, age) values (#{studentNumber, jdbcType=VARCHAR}, #{studentName, jdbcType=VARCHAR}, #{age, jdbcType=INTEGER})")
    int insertByMap(Map<String, Object> map);

    /**
     * 通过编号查询
     * @param studentNumber
     * @return
     */
    @Select("select * from student where student_number = #{studentNumber}")
    @Results({
            @Result(column = "student_number", property = "studentNumber"),
            @Result(column = "student_name", property = "studentName"),
            @Result(column = "age", property = "age")
    })
    Student findByStudentNumber(String studentNumber);

    /**
     * 通过姓名查询
     * @param studentName
     * @return
     */
    @Select("select * from student where student_name = #{studentName}")
    @Results({
            @Result(column = "student_number", property = "studentNumber"),
            @Result(column = "student_name", property = "studentName"),
            @Result(column = "age", property = "age")
    })
    List<Student> findByStudentName(String studentName);

    /**
     * 通过编码删除
     * @param studentNumber
     * @return
     */
    @Delete("delete from student where student_number = #{studentNumber}")
    int deleteByStudentNumber(String studentNumber);

    /**
     * 通过对象修改
     * @param student
     * @return
     */
    @Update("update student set student_name = #{studentName}, age = #{age} where student_number = #{studentNumber}")
    int updateByObject(Student student);

    /**
     * 通过编码修改名字
     * 传多个参数时必须使用@Param("")
     * @param studentName
     * @param StudentNumber
     * @return
     */
    @Update("update student set student_name = #{studentName} where student_number = #{studentNumber}")
    int updateStudentNameByStudentNumber(@Param("studentName") String studentName, @Param("studentNumber") String StudentNumber);

}

5、测试类

package com.ws.all.bean.mapper;

import com.ws.all.bean.Student;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.junit.Assert.*;

@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentMapperTest {

    @Autowired
    private StudentMapper studentMapper;

    @Test
    public void insertByMap() throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("studentNumber", "1001");
        map.put("studentName", "小白");
        map.put("age", 22);
        int result = studentMapper.insertByMap(map);
        Assert.assertEquals(1, result);
    }

    @Test
    public void insertByObject() throws Exception {
        Student student = new Student("1002", "小黑", 23);
        int result = studentMapper.insertByObject(student);
        Assert.assertEquals(1, result);
    }

    @Test
    public void findByStudentNumber() {
        Student student = studentMapper.findByStudentNumber("1001");
        Assert.assertEquals("1001", student.getStudentNumber());
    }

    @Test
    public void findByStudentName() {
        List<Student> studentList = studentMapper.findByStudentName("小白");
        Assert.assertNotEquals(0, studentList.size());
    }

    @Test
    public void deleteByStudentNumber() {
        int result = studentMapper.deleteByStudentNumber("1003");
        Assert.assertEquals(1, result);
    }

    @Test
    public void updateByStudentNumber() {
        Student student = new Student("1002", "小白白", 10);
        int result = studentMapper.updateByObject(student);
        Assert.assertNotEquals(0, result);
    }

    @Test
    public void updateStudentNameByStudentNumber() {
        int result = studentMapper.updateStudentNameByStudentNumber("小黑黑", "1002");
        Assert.assertNotEquals(0, result);
    }
}

6、在SpringBoot的启动类上添加mapper所在的包名

@MapperScan(basePackages = "com.ws.all.bean.mapper")

如：

package com.ws.all;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(basePackages = "com.ws.all.bean.mapper")
public class AllApplication {

    public static void main(String[] args) {
        SpringApplication.run(AllApplication.class, args);
    }
}

7、数据库

CREATE TABLE `student` (
  `student_number` varchar(20) NOT NULL,
  `student_name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;