springboot整合mybatis

一、新建springboot工程

https://blog.csdn.net/wjg8209/article/details/94546110

二、mysql搭建

下载mysql，直接安装，sequel pro连接数据库 密码连接问题：

系统偏好设置，mysql，点击Initialize Database，输入你的新密码，记住这个密码，用于后期链接数据库的登陆使用。选择‘Use legacy password‘。重启mysql服务。

CREATE TABLE mobile_info (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键id’,
mobile_no varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT ‘设备编号’,
owner varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT ‘手机持有者’,
model varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT ‘手机型号’,
create_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT ‘创建时间’,
update_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT ‘更新时间’,
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=‘测试机信息表’;

AUTO_INCREMENT=1，主键从1开始自增。

四、springboot整合mybatis

https://baijiahao.baidu.com/s?id=1653043822915271722&wfr=spider&for=pc

https://blog.csdn.net/iku5200/article/details/82878893

五、注意事项

1、加载依赖

<!-- 加载mybatis整合springboot-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<!-- MySQL的jdbc驱动包-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!--实体类可以自动生成get，set方法-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.10</version>
</dependency>

配置数据库连接

#数据库连接:注意要加上时区
server:
  port:8080
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/tesmobile?serverTimezone=GMT%2B8
    username: root
    password: xxxxx
    driver-class-name: com.mysql.jdbc.Driver
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: com.liu.sptest.bean

2、创建实体类，MobileInfo,使用lombok，和数据库一一对应

3、创建mapper接口类，涵盖增删改功能

4、在resource下创建mapper xml文件

xml文件中将数据库中字段和实体类中进行一一对应，sqlid与mapper接口类中的增删改关联

在mapper xml文件实现sql语句

5、编写service，service实现要实现的逻辑

6、controller实现接口

7、启动类

添加@MapperScan(“com.winter.dao”)注解以后，com.winter.dao包下面的接口类，在编译之后都会生成相应的实现类

mybastis单元测试

import com.alibaba.fastjson.JSON;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.liu.sptest.service.MobileInfoService;
import javax.annotation.Resource;

/**
 * @Author: beauty
 * @Date: 2020/4/23 11:22 上午
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class MysqlTest {
    @Resource
    private MobileInfoService mobileInfoService;
    
    @Test
    public void testMybatis() {
        String str = JSON.toJSONString(mobileInfoService.getMobileInfo("oppo"));
        System.out.println("==================" + str);
    }
}

出现使用resultMap不能匹配到数据库中带下划线的字段情况

<resultMap id="BaseResultMap" type="com.liu.sptest.bean.MobileInfo">
    <result column="id" jdbcType="INTEGER" property="id" />
    <result column="mobile_no" jdbcType="VARCHAR" property="mobileNo" />
    <result column="owner" jdbcType="VARCHAR" property="owner" />
    <result column="phone_no" jdbcType="VARCHAR" property="phoneNo" />
    <result column="model" jdbcType="VARCHAR" property="model" />
    <result column="create_time"  property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
</resultMap>

mobile_no和create_time不能匹配到

sql加这个就好了，之前是resultType=“MobileInfo”，匹配不全，resultType和resultMap必须有配置1个

resultMap="BaseResultMap"

<?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.liu.sptest.mapper.MobileInfoMapper">

    <resultMap id="BaseResultMap" type="com.liu.sptest.bean.MobileInfo">
        <result column="id" jdbcType="INTEGER" property="id" />
        <result column="mobile_no" jdbcType="VARCHAR" property="mobileNo" />
        <result column="owner" jdbcType="VARCHAR" property="owner" />
        <result column="phone_no" jdbcType="VARCHAR" property="phoneNo" />
        <result column="model" jdbcType="VARCHAR" property="model" />
        <result column="create_time"  property="createTime" />
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    </resultMap>
    <sql id="Base_Column_List" >
    id, mobile_no, owner, phone_no, model, create_time, update_time
    </sql>

    <select id="queryMobileByModel" parameterType="java.lang.String" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from mobile_infot where model = #{model}
    </select>
</mapper>

将返回结果封装成统一的json