oceanbase整合springboot springboot整合ibatis

文章目录

• 前言
• 一、pom.xml添加Maven依赖
• 二、application.yaml修改数据库配置
• 三、resources目录下添加generator.properties、generatorConfig.xml
• 四、添加运行生成的java代码
• 五、数据库多表操作
• 六、添加Mapper对应路径
• 七、数据库

---

前言

开发过程中的笔记

一、pom.xml添加Maven依赖

<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <!-- MyBatis 生成器 -->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.3</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

二、application.yaml修改数据库配置

其中“trade”为数据库名，username为数据库账号，password为数据库密码

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/trade?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: 123456

三、resources目录下添加generator.properties、generatorConfig.xml

generator.properties内容如下

jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.connectionURL=jdbc:mysql://localhost:3306/trade?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
jdbc.userId=root
jdbc.password=123456

 generatorConfig.xml内容如下，其中包的路径名称不一样自行修改和根据不同Table表生成，需自行修改<table>标签里的内容，例

commentGenerator、javaModelGenerator、sqlMapGenerator、javaClientGenerator、table根据实际修改

<?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>
    <properties resource="generator.properties"/>
    <context id="MySqlContext" targetRuntime="MyBatis3" defaultModelType="flat">
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>
        <property name="javaFileEncoding" value="UTF-8"/>
        <!-- 为模型生成序列化方法-->
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin"/>
        <!-- 为生成的Java模型创建一个toString方法 -->
        <plugin type="org.mybatis.generator.plugins.ToStringPlugin"/>
        <!--可以自定义生成model的代码注释-->
        <commentGenerator type="com.afc.trade.mbg.CommentGenerator">
            <!-- 是否去除自动生成的注释 true：是 ： false:否 -->
            <property name="suppressAllComments" value="true"/>
            <property name="suppressDate" value="true"/>
            <property name="addRemarkComments" value="true"/>
        </commentGenerator>
        <!--配置数据库连接-->
        <jdbcConnection driverClass="${jdbc.driverClass}"
                        connectionURL="${jdbc.connectionURL}"
                        userId="${jdbc.userId}"
                        password="${jdbc.password}">
            <!--解决mysql驱动升级到8.0后不生成指定数据库代码的问题-->
            <property name="nullCatalogMeansCurrent" value="true" />
        </jdbcConnection>
        <!--指定生成model的路径-->
        <javaModelGenerator targetPackage="com.afc.trade.mbg.model" targetProject="Trade\src\main\java"/>
        <!--指定生成mapper.xml的路径-->
        <sqlMapGenerator targetPackage="com.afc.trade.mbg.mapper" targetProject="Trade\src\main\resources"/>
        <!--指定生成mapper接口的的路径-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.afc.trade.mbg.mapper"
                             targetProject="Trade\src\main\java"/>
        <!--生成全部表tableName设为%-->
        <table tableName="trade_order">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
        <table tableName="trade_notes">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
        <table tableName="trade_admin">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
        <table tableName="trade_permission">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
        <table tableName="trade_admin_permission_relation">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
    </context>
</generatorConfiguration>

四、添加运行生成的java代码

内容如下

package com.afc.trade.mbg;

import org.mybatis.generator.api.IntrospectedColumn;
import org.mybatis.generator.api.IntrospectedTable;
import org.mybatis.generator.api.dom.java.CompilationUnit;
import org.mybatis.generator.api.dom.java.Field;
import org.mybatis.generator.api.dom.java.FullyQualifiedJavaType;
import org.mybatis.generator.internal.DefaultCommentGenerator;
import org.mybatis.generator.internal.util.StringUtility;

import java.util.Properties;

/**
 * 自定义注释生成器
 * Created by macro on 2018/4/26.
 */
public class CommentGenerator extends DefaultCommentGenerator {
    private boolean addRemarkComments = false;
    private static final String EXAMPLE_SUFFIX="Example";
    private static final String MAPPER_SUFFIX="Mapper";
    private static final String API_MODEL_PROPERTY_FULL_CLASS_NAME="io.swagger.annotations.ApiModelProperty";

    /**
     * 设置用户配置的参数
     */
    @Override
    public void addConfigurationProperties(Properties properties) {
        super.addConfigurationProperties(properties);
        this.addRemarkComments = StringUtility.isTrue(properties.getProperty("addRemarkComments"));
    }

    /**
     * 给字段添加注释
     */
    @Override
    public void addFieldComment(Field field, IntrospectedTable introspectedTable,
                                IntrospectedColumn introspectedColumn) {
        String remarks = introspectedColumn.getRemarks();
        //根据参数和备注信息判断是否添加备注信息
        if(addRemarkComments&&StringUtility.stringHasValue(remarks)){
//            addFieldJavaDoc(field, remarks);
            //数据库中特殊字符需要转义
            if(remarks.contains("\"")){
                remarks = remarks.replace("\"","'");
            }
            //给model的字段添加swagger注解
            field.addJavaDocLine("@ApiModelProperty(value = \""+remarks+"\")");
        }
    }

    /**
     * 给model的字段添加注释
     */
    private void addFieldJavaDoc(Field field, String remarks) {
        //文档注释开始
        field.addJavaDocLine("/**");
        //获取数据库字段的备注信息
        String[] remarkLines = remarks.split(System.getProperty("line.separator"));
        for(String remarkLine:remarkLines){
            field.addJavaDocLine(" * "+remarkLine);
        }
        addJavadocTag(field, false);
        field.addJavaDocLine(" */");
    }

    @Override
    public void addJavaFileComment(CompilationUnit compilationUnit) {
        super.addJavaFileComment(compilationUnit);
        //只在model中添加swagger注解类的导入
        if(!compilationUnit.getType().getFullyQualifiedName().contains(MAPPER_SUFFIX)&&!compilationUnit.getType().getFullyQualifiedName().contains(EXAMPLE_SUFFIX)){
            compilationUnit.addImportedType(new FullyQualifiedJavaType(API_MODEL_PROPERTY_FULL_CLASS_NAME));
        }
    }
}

package com.afc.trade.mbg;

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 用于生产MBG的代码
 * Created by macro on 2018/4/26.
 */
public class Generator {
    public static void main(String[] args) throws Exception {
        //MBG 执行过程中的警告信息
        List<String> warnings = new ArrayList<String>();
        //当生成的代码重复时，覆盖原代码
        boolean overwrite = true;
        //读取我们的 MBG 配置文件
        InputStream is = Generator.class.getResourceAsStream("/generatorConfig.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(is);
        is.close();

        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        //创建 MBG
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        //执行生成代码
        myBatisGenerator.generate(null);
        //输出警告信息
        for (String warning : warnings) {
            System.out.println(warning);
        }
    }
}

执行Generator

运行成功后即生成如下文件

 

 通过生成的Mapper即可实现简单的单表操作，另外多表操作则需要自定义Mapper并且书写SQL语句

五、数据库多表操作

一对多的例子

自定义DoTradeOrder，相当于model

package com.afc.trade.domain;

import com.afc.trade.mbg.model.TradeNotes;
import com.afc.trade.mbg.model.TradeOrder;
import lombok.Data;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.util.List;

@Data
@Repository
public class DoTradeOrder extends TradeOrder {
    private List<TradeNotes> tradeNotesList;
}

继承于TradeOrder

package com.afc.trade.mbg.model;

import io.swagger.annotations.ApiModelProperty;
import java.io.Serializable;
import java.util.Date;

public class TradeOrder implements Serializable {
    private Integer id;

    private Date time;

    private Integer type;

    private Integer total;

    private Integer pages;

    private static final long serialVersionUID = 1L;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Date getTime() {
        return time;
    }

    public void setTime(Date time) {
        this.time = time;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    public Integer getTotal() {
        return total;
    }

    public void setTotal(Integer total) {
        this.total = total;
    }

    public Integer getPages() {
        return pages;
    }

    public void setPages(Integer pages) {
        this.pages = pages;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", time=").append(time);
        sb.append(", type=").append(type);
        sb.append(", total=").append(total);
        sb.append(", pages=").append(pages);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

 创建自定义的Mapper

package com.afc.trade.dao;

import com.afc.trade.domain.DoTradeOrder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface TradeOrderDao {
    DoTradeOrder getDoTradeOrderById(@Param("id") Long id);
}

 创建对应的映射xml

 注意根据实际修改namespace、extends等等

<?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.afc.trade.dao.TradeOrderDao">
    <resultMap id="doTradeOrderMap" type="com.afc.trade.domain.DoTradeOrder"
               extends="com.afc.trade.mbg.mapper.TradeOrderMapper.BaseResultMap">
        <collection property="tradeNotesList" columnPrefix="ot_"
                    resultMap="com.afc.trade.mbg.mapper.TradeNotesMapper.BaseResultMap">
        </collection>
    </resultMap>
    <select id="getDoTradeOrderById" resultMap="doTradeOrderMap">
        SELECT
            o.id,
            o.Time,
            o.Type,
            o.Total,
            o.Pages,
            ot.id ot_id,
            ot.Currency ot_Currency,
            ot.Denomination ot_Denomination
        FROM
            trade_order o
                LEFT JOIN trade_notes ot ON o.id = ot.Trade_id
        WHERE
            o.id = #{id}
    </select>
</mapper>

至此，完成自定义关联类的生成，即TradeOrder对应列表tradeNoteList 关联起来了

六、添加Mapper对应路径

@MapperScan({"com.afc.trade.mbg.mapper","com.afc.trade.dao"})

package com.afc.trade.config;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;

/**
 * MyBatis配置类
 * Created by macro on 2019/4/8.
 */
@Configuration
@MapperScan({"com.afc.trade.mbg.mapper","com.afc.trade.dao"})
public class MyBatisConfig {
}

 

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

七、数据库

/*
 Navicat MySQL Data Transfer

 Source Server         : rzh
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : trade

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 08/11/2021 23:06:23
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for trade_order
-- ----------------------------
DROP TABLE IF EXISTS `trade_order`;
CREATE TABLE `trade_order`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `Time` datetime NULL DEFAULT NULL,
  `Type` int NULL DEFAULT NULL,
  `Total` int NULL DEFAULT NULL,
  `Pages` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '交易订单' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of trade_order
-- ----------------------------
INSERT INTO `trade_order` VALUES (1, '2021-11-01 19:16:52', 0, 5700, 9);
INSERT INTO `trade_order` VALUES (2, '2021-11-01 21:20:12', 0, 600, 2);
INSERT INTO `trade_order` VALUES (3, '3921-12-02 00:00:00', 0, 2000, 4);
INSERT INTO `trade_order` VALUES (9, '2021-11-01 13:20:12', 0, 300, 3);

SET FOREIGN_KEY_CHECKS = 1;

/*
 Navicat MySQL Data Transfer

 Source Server         : rzh
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : trade

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 08/11/2021 23:06:35
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for trade_notes
-- ----------------------------
DROP TABLE IF EXISTS `trade_notes`;
CREATE TABLE `trade_notes`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `Trade_id` int NULL DEFAULT NULL,
  `Currency` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Denomination` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 33 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '钞票信息' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of trade_notes
-- ----------------------------
INSERT INTO `trade_notes` VALUES (1, 1, 'HKD', 1000);
INSERT INTO `trade_notes` VALUES (2, 1, 'HKD', 1000);
INSERT INTO `trade_notes` VALUES (3, 1, 'HKD', 1000);
INSERT INTO `trade_notes` VALUES (4, 1, 'HKD', 1000);
INSERT INTO `trade_notes` VALUES (5, 1, 'HKD', 500);
INSERT INTO `trade_notes` VALUES (6, 1, 'HKD', 500);
INSERT INTO `trade_notes` VALUES (7, 1, 'HKD', 500);
INSERT INTO `trade_notes` VALUES (8, 1, 'HKD', 100);
INSERT INTO `trade_notes` VALUES (9, 1, 'HKD', 100);
INSERT INTO `trade_notes` VALUES (10, 2, 'HKD', 100);
INSERT INTO `trade_notes` VALUES (11, 2, 'HKD', 500);
INSERT INTO `trade_notes` VALUES (12, 3, 'HKD', 500);
INSERT INTO `trade_notes` VALUES (13, 3, 'HKD', 500);
INSERT INTO `trade_notes` VALUES (14, 3, 'HKD', 500);
INSERT INTO `trade_notes` VALUES (15, 3, 'HKD', 500);
INSERT INTO `trade_notes` VALUES (31, 9, 'HKD', 100);
INSERT INTO `trade_notes` VALUES (32, 9, 'HKD', 100);
INSERT INTO `trade_notes` VALUES (33, 9, 'HKD', 100);

SET FOREIGN_KEY_CHECKS = 1;