第6节-如何实现数据库操作通用mapper？

本节介绍怎么给通过mybatis自动生成数据库映射文件和实现接口增删改查功能。

1. 引入依赖

引入数据库连接池依赖

<dependency>
    <groupId>com.wueasy</groupId>
    <artifactId>wueasy-data-datasource</artifactId>
    <version>最新版本</version>
</dependency>

引入mysql驱动

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

引入自动生成工具

<dependency>
    <groupId>com.wueasy</groupId>
    <artifactId>wueasy-auto</artifactId>
    <version>最新版本</version>
    <scope>test</scope>
</dependency>

2 自动生成mybatis映射

通过自动生成功能，映射数据库实体文件

2.1 自动生成配置

对应配置文件generatorConfig.xml，需要修改配置文件中数据库连接、实体路径、生成的文件路径、生成表信息

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">

        <property name="javaFileEncoding" value="UTF-8"/>
        <property name="useMapperCommentGenerator" value="true"/>

<!--         <plugin type="org.mybatis.generator.plugins.SerializablePlugin" /> -->

        <plugin type="tk.mybatis.mapper.generator.MapperPlugin">
            <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
<!--             <property name="beginningDelimiter" value="`"/> -->
<!--             <property name="endingDelimiter" value="`"/> -->
            <property name="caseSensitive" value="true"/>
            <property name="forceAnnotation" value="false"/>
            <property name="generateColumnConsts" value="false"/>
            <property name="generateDefaultInstanceMethod" value="false"/>
            <property name="lombok" value="Data"/>
        </plugin>

        <!--通用代码生成器插件-->
        <!--mapper接口-->
        <plugin type="tk.mybatis.mapper.generator.TemplateFilePlugin">
            <property name="targetProject" value="src/main/java"/>
            <property name="targetPackage" value="com.wueasy.demo.mapper"/>
            <property name="templatePath" value="generator/mapper.ftl"/>
            <property name="mapperSuffix" value="Mapper"/>
            <property name="fileName" value="${tableClass.shortClassName}${mapperSuffix}.java"/>
        </plugin>
        <!--mapper.xml-->
        <plugin type="tk.mybatis.mapper.generator.TemplateFilePlugin">
            <property name="targetProject" value="src/main/resources"/>
            <property name="targetPackage" value="mybatis.demo"/>
            <property name="mapperPackage" value="com.wueasy.demo.mapper"/>
            <property name="templatePath" value="generator/mapperXml-wueasy.ftl"/>
            <property name="mapperSuffix" value="Mapper"/>
            <property name="fileName" value="${tableClass.shortClassName}${mapperSuffix}.xml"/>
        </plugin>


        <!-- 数据库连接 -->
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf-8"
                        userId="root"
                        password="123456">
        </jdbcConnection>
<!--         <jdbcConnection driverClass="oracle.jdbc.driver.OracleDriver" -->
<!--                         connectionURL="jdbc:oracle:thin:@localhost:1521:orcl" -->
<!--                         userId="think_framework" -->
<!--                         password="think_framework"> -->
<!--             针对oracle数据库增加注释 -->
<!--             <property name="remarksReporting" value="true"></property> -->
<!--         </jdbcConnection> -->


        <javaTypeResolver type="com.wueasy.auto.resolver.MyJavaTypeResolverDefaultImpl"/>

        <!--模型配置-->
        <javaModelGenerator targetPackage="com.wueasy.demo.entity" targetProject="src/main/java">
             <property name="enableSubPackages" value="true"/>
        </javaModelGenerator>


        <!-- 表配置 -->
        <table tableName="test">
            <generatedKey column="id" sqlStatement="JDBC"/>
        </table>
    </context>
</generatorConfiguration>

2.2 执行生成

通过执行类，操作生成，执行成功后，自动生成Test、TestMapper.java、TestMapper.xml

public class Generator {


    public static InputStream getResourceAsStream(String path){
        return Thread.currentThread().getContextClassLoader().getResourceAsStream(path);
    }

    public static void main(String[] args) throws Exception {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(getResourceAsStream("generatorConfig.xml"));
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
        for (String warning : warnings) {
            System.out.println(warning);
        }
    }
}

3. 接口开发

开发数据库增删改查操作

/**
 * test
 * @author: fallsea
 * @version 1.0
 */
@RestController
@RequestMapping("/test")
public class TestController
{
    @Autowired
    private TestMapper testMapper;

    /**
     * 新增
     * @author: fallsea
     * @param dto
     * @return
     */
    @RequestMapping(value = "/add", method = { RequestMethod.POST }, produces = MediaType.APPLICATION_JSON_VALUE)
    public Result<Void> add(@RequestBody @Valid TestAddDto dto) {
        Test test = new Test();
        BeanUtils.copyProperties(dto, test);
        test.setCreatedDate(new Date());
        testMapper.insertSelective(test);
        return new Result<Void>();
    }

    /**
     * 修改
     * @author: fallsea
     * @param dto
     * @return
     */
    @RequestMapping(value = "/edit", method = { RequestMethod.POST }, produces = MediaType.APPLICATION_JSON_VALUE)
    public Result<Void> edit(@RequestBody @Valid TestEditDto dto) {
        Test test = new Test();
        BeanUtils.copyProperties(dto, test);
        testMapper.updateByPrimaryKeySelective(test);
        test.setCreatedDate(new Date());
        return new Result<Void>();
    }

    /**
     * 查询单个
     * @author: fallsea
     * @param dto
     * @return
     */
    @RequestMapping(value = "/get", method = { RequestMethod.POST }, produces = MediaType.APPLICATION_JSON_VALUE)
    public Result<TestVo> get(@RequestBody @Valid TestIdDto dto) {
        Test test = testMapper.selectByPrimaryKey(dto.getId());
        if(null==test) {
            throw new InvokeException(-1, "记录不存在");
        }
        TestVo vo = new TestVo();
        BeanUtils.copyProperties(test, vo);
        return new Result<TestVo>().setData(vo);
    }

    /**
     * 删除
     * @author: fallsea
     * @param dto
     * @return
     */
    @RequestMapping(value = "/delete", method = { RequestMethod.POST }, produces = MediaType.APPLICATION_JSON_VALUE)
    public Result<Void> delete(@RequestBody @Valid TestIdDto dto) {
        Test test = testMapper.selectByPrimaryKey(dto.getId());
        if(null==test) {
            throw new InvokeException(-1, "记录不存在");
        }
        testMapper.deleteByPrimaryKey(dto.getId());
        return new Result<Void>();
    }

    /**
     * 分页
     * @author: fallsea
     * @param dto
     * @return
     */
    @RequestMapping(value = "/queryPage", method = { RequestMethod.POST }, produces = MediaType.APPLICATION_JSON_VALUE)
    public Result<Page<TestVo>> queryPage(@RequestBody @Valid TestQueryPageDto dto) {
        PageHelper.startPage(dto.getPageNum(), dto.getPageSize());
        List<Test> list = testMapper.selectList(dto.getName());
        Page<Test> page = PageHelper.getPage(list);

        //旧的转新的page
        Page<TestVo> newPage = new Page<>();
        BeanUtils.copyProperties(page, newPage);
        if(null!=page.getList() && !page.getList().isEmpty()) {
            List<TestVo> newList = new ArrayList<>();
            for (Test test : page.getList()) {
                TestVo vo = new TestVo();
                BeanUtils.copyProperties(test, vo);
                newList.add(vo);
            }
            newPage.setList(newList);
        }
        return new Result<Page<TestVo>>().setData(newPage);
    }


}

4. 数据库配置

修改数据库连接配置信息。

wueasy :
    datasource:
      demo :
        isLog: true #是否输出日志，true是，默认false
        mybatis:
          basePackage: com.wueasy.demo.mapper
          mapperLocations: classpath*:mybatis/demo/*.xml
        connection:
          url : jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf-8
          username : root
          password : 123456
          #连接池配置
          initialSize : 10  #初始化大小
          minIdle : 10  #最小连接
          maxActive : 20  #最大连接
          maxWait : 60000  #配置获取连接等待超时的时间
          timeBetweenEvictionRunsMillis : 60000  #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
          minEvictableIdleTimeMillis : 300000  #配置一个连接在池中最小生存的时间，单位是毫秒
          maxEvictableIdleTimeMillis : 600000  #配置一个连接在池中最大生存的时间，单位是毫秒
          validationQuery : SELECT 1
          testWhileIdle : true
          testOnBorrow : false
          testOnReturn : false
          keepAlive : true

5. 修改网关权限

修改网关接口权限，示例：登录后才可以调用接口。在login-urls中增加接口配置/demo/test/*

wueasy :
  gateway:
    interceptor:
      security:
        login-urls: #登录权限
        - /demo/get
        - /demo/test/*

6. 数据库表sql

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

7. 启动服务

分别启动 注册中心、微服务、网关

8. 测试接口

8.1 增加接口

• 接口地址：http://127.0.0.1:8088/demo/test/add
• 请求类型：POST
• 请求参数：

  {
    "name":"测试3"
  }
  

• 成功示例：

  {
    "errorNo": 0
  }
  

8.2 查询单个数据接口

• 接口地址：http://127.0.0.1:8088/demo/test/get
• 请求类型：POST
• 请求参数：

  {
    "id":1
  }
  

• 成功示例：

  {
    "errorNo": 0,
    "data": {
        "id": 3,
        "name": "测试2",
        "createdDate": 1662273532000
    }
  }
  

8.3 修改数据接口

• 接口地址：http://127.0.0.1:8088/demo/test/edit
• 请求类型：POST
• 请求参数：

  {
    "id":3,
    "name":"test"
  }
  

• 成功示例：

  {
    "errorNo": 0
  }
  

8.4 分页查询接口

• 接口地址：http://127.0.0.1:8088/demo/test/queryPage
• 请求类型：POST
• 请求参数：

  {
    "pageNum":1,
    "pageSize":20,
    "name":""
  }
  

• 成功示例：

  {
    "errorNo": 0,
    "data": {
        "pageNum": 1,
        "pageSize": 20,
        "total": 4,
        "pages": 1,
        "list": [
            {
                "id": 4,
                "name": "测试3",
                "createdDate": 1662273536000
            },
            {
                "id": 3,
                "name": "test",
                "createdDate": 1662273532000
            },
            {
                "id": 2,
                "name": "测试1",
                "createdDate": 1662273529000
            },
            {
                "id": 1,
                "name": "它美或论"
            }
        ]
    }
  }
  

8.5 删除数据接口

• 接口地址：http://127.0.0.1:8088/demo/test/delete
• 请求类型：POST
• 请求参数：

  {
    "id":1
  }
  

• 成功示例：

  {
    "errorNo": 0
  }