spring-boot-route（八）整合mybatis操作数据库

怼烎@ 2021-09-18 06:00 275阅读 0赞

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Ordinary Java Object,普通的 Java对象)映射成数据库中的记录。

## 通过注解完成数据操作 ##

第一步：引入mysql依赖和mybatis依赖

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>LATEST</version>
    </dependency>

第二步：新建学生表及对应的实体类

CREATE TABLE `student` (
       `student_id` int(30) NOT NULL AUTO_INCREMENT,
       `age` int(1) DEFAULT NULL COMMENT '年龄',
       `name` varchar(45) DEFAULT NULL COMMENT '姓名',
       `sex` int(1) DEFAULT NULL COMMENT '性别：1：男，2：女，0：未知',
       `create_time` datetime DEFAULT NULL COMMENT '创建时间',
       `status` int(1) DEFAULT NULL COMMENT '状态：1：正常，-1：删除',
       PRIMARY KEY (`student_id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=617354 DEFAULT CHARSET=utf8mb4 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='学生表'

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class Student implements Serializable { 
    
        private static final long serialVersionUID = 6712540741269055064L;
    
        private Integer studentId;
        private Integer age;
        private String name;
        private Integer sex;
        private Date createTime;
        private Integer status;
    }

第三步：配置数据库连接信息

spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/simple_fast
        username: root
        password: root

### 增删改查 ###

@Mapper
    public interface StudentMapper { 
    
        @Select("select * from student where student_id = #{studentId}")
        Student findById(@Param("studentId") Integer studentId);
    
        @Insert("insert into student(age,name) values(#{age},#{name})")
        int addStudent(@Param("name") String name,@Param("age") Integer age);
    
        @Update("update student set name = #{name} where student_id = #{studentId}")
        int updateStudent(@Param("studentId") Integer studentId,@Param("name") String name);
    
        @Delete("delete from student where student_id = #{studentId}")
        int deleteStudent(@Param("studentId") Integer studentId);
    }

上面演示的传参方式是通过单个参数传递的，**如果想通过Map或实体类传参数，就不需要使用@Param来绑定参数了，将map中的key或者实体类中的属性与sql中的参数值对应上就可以了**。

## 通过XML配置完成数据操作 ##

**@Mapper和@MapperScan**

@Mapper加在数据层接口上，将其注册到ioc容器上，@MapperScan加在启动类上，需要指定扫描的数据层接口包。如下：

@Mapper
    public interface StudentMapper { }

@SpringBootApplication
    @MapperScan("com.javatrip.mybatis.mapper")
    public class MybatisApplication { 
        public static void main(String[] args) { 
            SpringApplication.run(MybatisApplication.class, args);
        }
    }

两个注解的作用一样，在开发中为了方便，通常我们会使用@MapperScan。

**指定mapper.xml的位置**

mybatis:
      mapper-locations: classpath:mybatis/*.xml

**开启数据实体映射驼峰命名**

mybatis:
      configuration:
        map-underscore-to-camel-case: true

**编写xml和与之对应的mapper接口**

<?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.javatrip.mybatis.mapper.StudentXMapper">
        <select id="findById" resultType="com.javatrip.mybatis.entity.Student">
            select * from student where student_id = #{ studentId}
        </select>
        <insert id="addStudent" parameterType="com.javatrip.mybatis.entity.Student">
            insert into student(name,age) values(#{ name},#{ age})
        </insert>
        
        <update id="updateStudent" parameterType="com.javatrip.mybatis.entity.Student">
            update student set name = #{ name} where  student_id = #{ studentId}
        </update>
    
        <delete id="deleteStudent" parameterType="Integer">
            delete from student where student_id = #{ studentId}
        </delete>
    </mapper>

@Mapper
    public interface StudentXMapper { 
    
        Student findById(@Param("studentId") Integer studentId);
    
        int addStudent(Student student);
    
        int updateStudent(@Param("studentId") Integer studentId,@Param("name") String name);
    
        int deleteStudent(@Param("studentId") Integer studentId);
    }

**编写测试类**

@SpringBootTest
    class MybatisApplicationTests { 
    
        @Autowired
        StudentMapper mapper;
    
        @Autowired
        StudentXMapper xMapper;
    
        @Test
        void testMapper() { 
    
            Student student = mapper.findById(10);
            mapper.addStudent("Java旅途",19);
            mapper.deleteStudent(31);
            mapper.updateStudent(10,"Java旅途");
        }
    
        @Test
        void contextLoads() { 
            
            Student student = xMapper.findById(10);
            Student studentDo = new Student();
            studentDo.setAge(18);
            studentDo.setName("Java旅途呀");
            xMapper.addStudent(studentDo);
            xMapper.deleteStudent(32);
            xMapper.updateStudent(31,"Java旅途");
        }
    }

这里有几个需要注意的点：mapper标签中namespace属性对应的是mapper接口；select标签的id对应mapper接口中的方法名字；select标签的resultType对应查询的实体类，使用全路径。

--------------------

#### 本文示例代码已上传至[github][]，点个`star`支持一下！ ####

## Spring Boot系列教程目录 ##

[spring-boot-route（一）Controller接收参数的几种方式][spring-boot-route_Controller]

[spring-boot-route（二）读取配置文件的几种方式][spring-boot-route]

[spring-boot-route（三）实现多文件上传][spring-boot-route 1]

[spring-boot-route（四）全局异常处理][spring-boot-route 2]

[spring-boot-route（五）整合swagger生成接口文档][spring-boot-route_swagger]

[spring-boot-route（六）整合JApiDocs生成接口文档][spring-boot-route_JApiDocs]

[spring-boot-route（七）整合jdbcTemplate操作数据库][spring-boot-route_jdbcTemplate]

[spring-boot-route（八）整合mybatis操作数据库][spring-boot-route_mybatis]

[spring-boot-route（九）整合JPA操作数据库][spring-boot-route_JPA]

[spring-boot-route（十）多数据源切换][spring-boot-route 3]

[spring-boot-route（十一）数据库配置信息加密][spring-boot-route 4]

[spring-boot-route（十二）整合redis做为缓存][spring-boot-route_redis]

[spring-boot-route（十三）整合RabbitMQ][spring-boot-route_RabbitMQ]

[spring-boot-route（十四）整合Kafka][spring-boot-route_Kafka]

[spring-boot-route（十五）整合RocketMQ][spring-boot-route_RocketMQ]

[spring-boot-route（十六）使用logback生产日志文件][spring-boot-route_logback]

[spring-boot-route（十七）使用aop记录操作日志][spring-boot-route_aop]

[spring-boot-route（十八）spring-boot-adtuator监控应用][spring-boot-route_spring-boot-adtuator]

[spring-boot-route（十九）spring-boot-admin监控服务][spring-boot-route_spring-boot-admin]

[spring-boot-route（二十）Spring Task实现简单定时任务][spring-boot-route_Spring Task]

[spring-boot-route（二十一）quartz实现动态定时任务][spring-boot-route_quartz]

[spring-boot-route（二十二）实现邮件发送功能][spring-boot-route 5]

[spring-boot-route（二十三）开发微信公众号][spring-boot-route 6]

[spring-boot-route（二十四）分布式session的一致性处理][spring-boot-route_session]

[spring-boot-route（二十五）两行代码实现国际化][spring-boot-route 7]

[spring-boot-route（二十六）整合webSocket][spring-boot-route_webSocket]

[github]: https://github.com/binzh303/spring-boot-route
[spring-boot-route_Controller]: https://javatip.cn/archives/66
[spring-boot-route]: https://javatip.cn/archives/72
[spring-boot-route 1]: https://javatip.cn/archives/74
[spring-boot-route 2]: https://javatip.cn/archives/79
[spring-boot-route_swagger]: https://javatip.cn/archives/90
[spring-boot-route_JApiDocs]: https://javatip.cn/archives/91
[spring-boot-route_jdbcTemplate]: https://javatip.cn/archives/92
[spring-boot-route_mybatis]: https://javatip.cn/archives/93
[spring-boot-route_JPA]: https://javatip.cn/archives/94
[spring-boot-route 3]: https://javatip.cn/archives/95
[spring-boot-route 4]: https://javatip.cn/archives/113
[spring-boot-route_redis]: https://javatip.cn/archives/114
[spring-boot-route_RabbitMQ]: https://javatip.cn/archives/115
[spring-boot-route_Kafka]: https://javatip.cn/archives/116
[spring-boot-route_RocketMQ]: https://javatip.cn/archives/117
[spring-boot-route_logback]: https://javatip.cn/archives/118
[spring-boot-route_aop]: https://javatip.cn/archives/119
[spring-boot-route_spring-boot-adtuator]: https://javatip.cn/archives/120
[spring-boot-route_spring-boot-admin]: https://javatip.cn/archives/121
[spring-boot-route_Spring Task]: https://javatip.cn/archives/122
[spring-boot-route_quartz]: https://javatip.cn/archives/123
[spring-boot-route 5]: https://javatip.cn/archives/124
[spring-boot-route 6]: https://javatip.cn/archives/125
[spring-boot-route_session]: https://javatip.cn/archives/284
[spring-boot-route 7]: https://javatip.cn/archives/256
[spring-boot-route_webSocket]: https://javatip.cn/archives/255