springboot-demo

创建springboot工程

创建springboot

在src\main\java\目录下创建packageorg.mjtabu等目录，可使用Easy code插件自动生成

Maven配置pom.xml

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>org.mjtabu</groupId>
    <artifactId>springboot-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>springboot-demo</name>
    <description>Demo project for Spring Boot</description>

    <!--版本号统一控制-->
    <properties>
        <java.version>1.8</java.version>
        <springboot.version>2.6.2</springboot.version>
        <mybatis.version>2.2.0</mybatis.version>
        <slf4j.version>1.7.21</slf4j.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-commons</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--mybatis配置-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <!--mysql数据库配置-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--日志配置-->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <!--spring-boot-maven-plugin的版本号与springboot版本号一致-->
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${springboot.version}</version>
            </plugin>
        </plugins>
    </build>

</project>

基础配置application.properties

application.properties

# 访问根路径
server.servlet.context-path=/springboot

# 应用名称
spring.application.name=springboot-demo

# 访问端口号
server.port=1234

# 编码格式
server.tomcat.uri-encoding=utf-8

# 数据库相关配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/information_schema?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2b8&nullCatalogMeansCurrent=true
spring.datasource.username=root
spring.datasource.password=123456

# session生命周期
server.servlet.session.timeout=30m

# mybatis Mapper路径配置
mybatis.mapper-locations=classpath:mapper/*.xml

# mybatis 实体路径配置
mybatis.type-aliases-package=org.mjtabu.entity

# 开启Mapper打印sql
logging.level.org.mjtabu.mapper=debug

日志配置logback-spring.xml

logback-spring.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration scan="true" scanPeriod="60 seconds" debug="false">
    <property resource="application.properties"/>
    <contextName>logs</contextName>
    <!--  日志位置  -->
    <property name="log.path" value="log" />
    <!--  日志保留时长  -->
    <property name="log.maxHistory" value="15" />
    <!--  控制台格式化及颜色  -->
    <property name="log.colorPattern" value="%magenta(%d{yyyy-MM-dd HH:mm:ss}) %highlight(%-5level) %yellow(%thread) %green(%logger) %msg%n"/>
    <property name="log.pattern" value="%d{yyyy-MM-dd HH:mm:ss} %-5level %thread %logger %msg%n"/>

    <!--输出到控制台-->
    <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>${log.colorPattern}</pattern>
        </encoder>
    </appender>

    <!--info输出配置-->
    <appender name="file_info" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${log.path}/info/info.%d{yyyy-MM-dd}.log</fileNamePattern>
            <MaxHistory>${log.maxHistory}</MaxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>${log.pattern}</pattern>
        </encoder>
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>INFO</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
    </appender>

    <!--error输出配置-->
    <appender name="file_error" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${log.path}/error/error.%d{yyyy-MM-dd}.log</fileNamePattern>
        </rollingPolicy>
        <encoder>
            <pattern>${log.pattern}</pattern>
        </encoder>
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>ERROR</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
    </appender>

    <!--  日志类型为debug时，输出到控制台  -->
    <root level="debug">
        <appender-ref ref="console" />
    </root>

    <!--  日志类型为info时，输出到配置好的文件  -->
    <root level="info">
        <appender-ref ref="file_info" />
        <appender-ref ref="file_error" />
    </root>
</configuration>

启动配置Application

src\main\java\org.mjtabu.config.SpringbootApplicationl.java

package org.mjtabu.config;

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

/**
 * 启动配置类
 * ComponentScan：扫描路径，若不配置，则默认只扫描当前包
 * MapperScan：mapper扫描路径
 */
@SpringBootApplication()
@ComponentScan("org.mjtabu")
@MapperScan(value = "org.mjtabu.mapper")
public class SpringbootApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringbootApplication.class, args);
    }
}

CRUD之Entity.java

src\main\java\org.mjtabu.entity

package org.mjtabu.entity;

import java.util.Date;
import java.io.Serializable;

/**
 * (Tables)实体类
 *
 * @author makejava
 * @since 2021-12-30 14:32:29
 */
public class Tables implements Serializable {
    private static final long serialVersionUID = -49556619006096444L;
    
    private String tableCatalog;
    
    private String tableSchema;
    
    private String tableName;
    
    private String tableType;
    
    private String engine;
    
    private Long version;
    
    private String rowFormat;
    
    private Long tableRows;
    
    private Long avgRowLength;
    
    private Long dataLength;
    
    private Long maxDataLength;
    
    private Long indexLength;
    
    private Long dataFree;
    
    private Long autoIncrement;
    
    private Date createTime;
    
    private Date updateTime;
    
    private Date checkTime;
    
    private String tableCollation;
    
    private Long checksum;
    
    private String createOptions;
    
    private String tableComment;


    public String getTableCatalog() {
        return tableCatalog;
    }

    public void setTableCatalog(String tableCatalog) {
        this.tableCatalog = tableCatalog;
    }

    public String getTableSchema() {
        return tableSchema;
    }

    public void setTableSchema(String tableSchema) {
        this.tableSchema = tableSchema;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public String getTableType() {
        return tableType;
    }

    public void setTableType(String tableType) {
        this.tableType = tableType;
    }

    public String getEngine() {
        return engine;
    }

    public void setEngine(String engine) {
        this.engine = engine;
    }

    public Long getVersion() {
        return version;
    }

    public void setVersion(Long version) {
        this.version = version;
    }

    public String getRowFormat() {
        return rowFormat;
    }

    public void setRowFormat(String rowFormat) {
        this.rowFormat = rowFormat;
    }

    public Long getTableRows() {
        return tableRows;
    }

    public void setTableRows(Long tableRows) {
        this.tableRows = tableRows;
    }

    public Long getAvgRowLength() {
        return avgRowLength;
    }

    public void setAvgRowLength(Long avgRowLength) {
        this.avgRowLength = avgRowLength;
    }

    public Long getDataLength() {
        return dataLength;
    }

    public void setDataLength(Long dataLength) {
        this.dataLength = dataLength;
    }

    public Long getMaxDataLength() {
        return maxDataLength;
    }

    public void setMaxDataLength(Long maxDataLength) {
        this.maxDataLength = maxDataLength;
    }

    public Long getIndexLength() {
        return indexLength;
    }

    public void setIndexLength(Long indexLength) {
        this.indexLength = indexLength;
    }

    public Long getDataFree() {
        return dataFree;
    }

    public void setDataFree(Long dataFree) {
        this.dataFree = dataFree;
    }

    public Long getAutoIncrement() {
        return autoIncrement;
    }

    public void setAutoIncrement(Long autoIncrement) {
        this.autoIncrement = autoIncrement;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public Date getCheckTime() {
        return checkTime;
    }

    public void setCheckTime(Date checkTime) {
        this.checkTime = checkTime;
    }

    public String getTableCollation() {
        return tableCollation;
    }

    public void setTableCollation(String tableCollation) {
        this.tableCollation = tableCollation;
    }

    public Long getChecksum() {
        return checksum;
    }

    public void setChecksum(Long checksum) {
        this.checksum = checksum;
    }

    public String getCreateOptions() {
        return createOptions;
    }

    public void setCreateOptions(String createOptions) {
        this.createOptions = createOptions;
    }

    public String getTableComment() {
        return tableComment;
    }

    public void setTableComment(String tableComment) {
        this.tableComment = tableComment;
    }

}

CRUD之Mapper.java

src\main\java\org.mjtabu.mapper

package org.mjtabu.mapper;

import org.apache.ibatis.annotations.Param;
import org.mjtabu.entity.Tables;
import org.springframework.data.domain.Pageable;

import java.util.List;

/**
 * (Tables)表数据库访问层
 *
 * @author makejava
 * @since 2021-12-30 14:32:32
 */
public interface TablesMapper {

    /**
     * 通过数据库和数据库表查询单条数据
     *
     * @param tableSchema 数据库
     * @param tableName   数据库表
     * @return
     */
    Tables queryBySchemaAndTable(@Param("tableSchema") String tableSchema, @Param("tableName") String tableName);

    /**
     * 查询指定行数据
     *
     * @param tables   查询条件
     * @param pageable 分页对象
     * @return 对象列表
     */
    List<Tables> queryAllByLimit(Tables tables, @Param("pageable") Pageable pageable);

    /**
     * 统计总行数
     *
     * @param tables 查询条件
     * @return 总行数
     */
    long count(Tables tables);

    /**
     * 新增数据
     *
     * @param tables 实例对象
     * @return 影响行数
     */
    int insert(Tables tables);

    /**
     * 批量新增数据（MyBatis原生foreach方法）
     *
     * @param entities List<Tables> 实例对象列表
     * @return 影响行数
     */
    int insertBatch(@Param("entities") List<Tables> entities);

    /**
     * 批量新增或按主键更新数据（MyBatis原生foreach方法）
     *
     * @param entities List<Tables> 实例对象列表
     * @return 影响行数
     * @throws org.springframework.jdbc.BadSqlGrammarException 入参是空List的时候会抛SQL语句错误的异常，请自行校验入参
     */
    int insertOrUpdateBatch(@Param("entities") List<Tables> entities);

    /**
     * 修改数据
     *
     * @param tables 实例对象
     * @return 影响行数
     */
    int update(Tables tables);

    /**
     * 通过数据库和数据库表删除数据
     *
     * @param tableSchema 数据库
     * @param tableName   数据库表
     * @return 影响行数
     */
    int deleteBySchemaAndTable(@Param("tableSchema") String tableSchema, @Param("tableName") String tableName);

}

CRUD之Mapper.xml

src\main\resources\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="org.mjtabu.mapper.TablesMapper">

    <resultMap type="org.mjtabu.entity.Tables" id="TablesMap">
        <result property="tableCatalog" column="TABLE_CATALOG" jdbcType="VARCHAR"/>
        <result property="tableSchema" column="TABLE_SCHEMA" jdbcType="VARCHAR"/>
        <result property="tableName" column="TABLE_NAME" jdbcType="VARCHAR"/>
        <result property="tableType" column="TABLE_TYPE" jdbcType="VARCHAR"/>
        <result property="engine" column="ENGINE" jdbcType="VARCHAR"/>
        <result property="version" column="VERSION" jdbcType="INTEGER"/>
        <result property="rowFormat" column="ROW_FORMAT" jdbcType="VARCHAR"/>
        <result property="tableRows" column="TABLE_ROWS" jdbcType="INTEGER"/>
        <result property="avgRowLength" column="AVG_ROW_LENGTH" jdbcType="INTEGER"/>
        <result property="dataLength" column="DATA_LENGTH" jdbcType="INTEGER"/>
        <result property="maxDataLength" column="MAX_DATA_LENGTH" jdbcType="INTEGER"/>
        <result property="indexLength" column="INDEX_LENGTH" jdbcType="INTEGER"/>
        <result property="dataFree" column="DATA_FREE" jdbcType="INTEGER"/>
        <result property="autoIncrement" column="AUTO_INCREMENT" jdbcType="INTEGER"/>
        <result property="createTime" column="CREATE_TIME" jdbcType="TIMESTAMP"/>
        <result property="updateTime" column="UPDATE_TIME" jdbcType="TIMESTAMP"/>
        <result property="checkTime" column="CHECK_TIME" jdbcType="TIMESTAMP"/>
        <result property="tableCollation" column="TABLE_COLLATION" jdbcType="VARCHAR"/>
        <result property="checksum" column="CHECKSUM" jdbcType="INTEGER"/>
        <result property="createOptions" column="CREATE_OPTIONS" jdbcType="VARCHAR"/>
        <result property="tableComment" column="TABLE_COMMENT" jdbcType="VARCHAR"/>
    </resultMap>

    <!--查询单个-->
    <select id="queryBySchemaAndTable" parameterType="java.lang.String" resultMap="TablesMap">
        select TABLE_CATALOG,
               TABLE_SCHEMA,
               TABLE_NAME,
               TABLE_TYPE,
               ENGINE,
               VERSION,
               ROW_FORMAT,
               TABLE_ROWS,
               AVG_ROW_LENGTH,
               DATA_LENGTH,
               MAX_DATA_LENGTH,
               INDEX_LENGTH,
               DATA_FREE,
               AUTO_INCREMENT,
               CREATE_TIME,
               UPDATE_TIME,
               CHECK_TIME,
               TABLE_COLLATION,
               CHECKSUM,
               CREATE_OPTIONS,
               TABLE_COMMENT
        from TABLES
        where TABLE_SCHEMA = #{tableSchema}
          and TABLE_NAME = #{tableName}
    </select>

    <!--查询指定行数据-->
    <select id="queryAllByLimit" resultMap="TablesMap">
        select
        TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
        DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, CHECK_TIME,
        TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT
        from TABLES
        <where>
            <if test="tableCatalog != null and tableCatalog != ''">
                and TABLE_CATALOG = #{tableCatalog}
            </if>
            <if test="tableSchema != null and tableSchema != ''">
                and TABLE_SCHEMA = #{tableSchema}
            </if>
            <if test="tableName != null and tableName != ''">
                and TABLE_NAME = #{tableName}
            </if>
            <if test="tableType != null and tableType != ''">
                and TABLE_TYPE = #{tableType}
            </if>
            <if test="engine != null and engine != ''">
                and ENGINE = #{engine}
            </if>
            <if test="version != null">
                and VERSION = #{version}
            </if>
            <if test="rowFormat != null and rowFormat != ''">
                and ROW_FORMAT = #{rowFormat}
            </if>
            <if test="tableRows != null">
                and TABLE_ROWS = #{tableRows}
            </if>
            <if test="avgRowLength != null">
                and AVG_ROW_LENGTH = #{avgRowLength}
            </if>
            <if test="dataLength != null">
                and DATA_LENGTH = #{dataLength}
            </if>
            <if test="maxDataLength != null">
                and MAX_DATA_LENGTH = #{maxDataLength}
            </if>
            <if test="indexLength != null">
                and INDEX_LENGTH = #{indexLength}
            </if>
            <if test="dataFree != null">
                and DATA_FREE = #{dataFree}
            </if>
            <if test="autoIncrement != null">
                and AUTO_INCREMENT = #{autoIncrement}
            </if>
            <if test="createTime != null">
                and CREATE_TIME = #{createTime}
            </if>
            <if test="updateTime != null">
                and UPDATE_TIME = #{updateTime}
            </if>
            <if test="checkTime != null">
                and CHECK_TIME = #{checkTime}
            </if>
            <if test="tableCollation != null and tableCollation != ''">
                and TABLE_COLLATION = #{tableCollation}
            </if>
            <if test="checksum != null">
                and CHECKSUM = #{checksum}
            </if>
            <if test="createOptions != null and createOptions != ''">
                and CREATE_OPTIONS = #{createOptions}
            </if>
            <if test="tableComment != null and tableComment != ''">
                and TABLE_COMMENT = #{tableComment}
            </if>
        </where>
        limit #{pageable.offset}, #{pageable.pageSize}
    </select>

    <!--统计总行数-->
    <select id="count" resultType="java.lang.Long">
        select count(1)
        from TABLES
        <where>
            <if test="tableCatalog != null and tableCatalog != ''">
                and TABLE_CATALOG = #{tableCatalog}
            </if>
            <if test="tableSchema != null and tableSchema != ''">
                and TABLE_SCHEMA = #{tableSchema}
            </if>
            <if test="tableName != null and tableName != ''">
                and TABLE_NAME = #{tableName}
            </if>
            <if test="tableType != null and tableType != ''">
                and TABLE_TYPE = #{tableType}
            </if>
            <if test="engine != null and engine != ''">
                and ENGINE = #{engine}
            </if>
            <if test="version != null">
                and VERSION = #{version}
            </if>
            <if test="rowFormat != null and rowFormat != ''">
                and ROW_FORMAT = #{rowFormat}
            </if>
            <if test="tableRows != null">
                and TABLE_ROWS = #{tableRows}
            </if>
            <if test="avgRowLength != null">
                and AVG_ROW_LENGTH = #{avgRowLength}
            </if>
            <if test="dataLength != null">
                and DATA_LENGTH = #{dataLength}
            </if>
            <if test="maxDataLength != null">
                and MAX_DATA_LENGTH = #{maxDataLength}
            </if>
            <if test="indexLength != null">
                and INDEX_LENGTH = #{indexLength}
            </if>
            <if test="dataFree != null">
                and DATA_FREE = #{dataFree}
            </if>
            <if test="autoIncrement != null">
                and AUTO_INCREMENT = #{autoIncrement}
            </if>
            <if test="createTime != null">
                and CREATE_TIME = #{createTime}
            </if>
            <if test="updateTime != null">
                and UPDATE_TIME = #{updateTime}
            </if>
            <if test="checkTime != null">
                and CHECK_TIME = #{checkTime}
            </if>
            <if test="tableCollation != null and tableCollation != ''">
                and TABLE_COLLATION = #{tableCollation}
            </if>
            <if test="checksum != null">
                and CHECKSUM = #{checksum}
            </if>
            <if test="createOptions != null and createOptions != ''">
                and CREATE_OPTIONS = #{createOptions}
            </if>
            <if test="tableComment != null and tableComment != ''">
                and TABLE_COMMENT = #{tableComment}
            </if>
        </where>
    </select>

    <!--新增所有列-->
    <insert id="insert" keyProperty="" useGeneratedKeys="true">
        insert into TABLES(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS,
                           AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
                           CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS,
                           TABLE_COMMENT)
        values (#{tableCatalog}, #{tableSchema}, #{tableName}, #{tableType}, #{engine}, #{version}, #{rowFormat},
                #{tableRows}, #{avgRowLength}, #{dataLength}, #{maxDataLength}, #{indexLength}, #{dataFree},
                #{autoIncrement}, #{createTime}, #{updateTime}, #{checkTime}, #{tableCollation}, #{checksum},
                #{createOptions}, #{tableComment})
    </insert>

    <insert id="insertBatch" keyProperty="" useGeneratedKeys="true">
        insert into TABLES(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS,
        AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME,
        CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.tableCatalog}, #{entity.tableSchema}, #{entity.tableName}, #{entity.tableType}, #{entity.engine},
            #{entity.version}, #{entity.rowFormat}, #{entity.tableRows}, #{entity.avgRowLength}, #{entity.dataLength},
            #{entity.maxDataLength}, #{entity.indexLength}, #{entity.dataFree}, #{entity.autoIncrement},
            #{entity.createTime}, #{entity.updateTime}, #{entity.checkTime}, #{entity.tableCollation},
            #{entity.checksum}, #{entity.createOptions}, #{entity.tableComment})
        </foreach>
    </insert>

    <insert id="insertOrUpdateBatch" keyProperty="" useGeneratedKeys="true">
        insert into TABLES(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS,
        AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME,
        CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.tableCatalog}, #{entity.tableSchema}, #{entity.tableName}, #{entity.tableType}, #{entity.engine},
            #{entity.version}, #{entity.rowFormat}, #{entity.tableRows}, #{entity.avgRowLength}, #{entity.dataLength},
            #{entity.maxDataLength}, #{entity.indexLength}, #{entity.dataFree}, #{entity.autoIncrement},
            #{entity.createTime}, #{entity.updateTime}, #{entity.checkTime}, #{entity.tableCollation},
            #{entity.checksum}, #{entity.createOptions}, #{entity.tableComment})
        </foreach>
        on duplicate key update
        TABLE_CATALOG = values(TABLE_CATALOG),
        TABLE_SCHEMA = values(TABLE_SCHEMA),
        TABLE_NAME = values(TABLE_NAME),
        TABLE_TYPE = values(TABLE_TYPE),
        ENGINE = values(ENGINE),
        VERSION = values(VERSION),
        ROW_FORMAT = values(ROW_FORMAT),
        TABLE_ROWS = values(TABLE_ROWS),
        AVG_ROW_LENGTH = values(AVG_ROW_LENGTH),
        DATA_LENGTH = values(DATA_LENGTH),
        MAX_DATA_LENGTH = values(MAX_DATA_LENGTH),
        INDEX_LENGTH = values(INDEX_LENGTH),
        DATA_FREE = values(DATA_FREE),
        AUTO_INCREMENT = values(AUTO_INCREMENT),
        CREATE_TIME = values(CREATE_TIME),
        UPDATE_TIME = values(UPDATE_TIME),
        CHECK_TIME = values(CHECK_TIME),
        TABLE_COLLATION = values(TABLE_COLLATION),
        CHECKSUM = values(CHECKSUM),
        CREATE_OPTIONS = values(CREATE_OPTIONS),
        TABLE_COMMENT = values(TABLE_COMMENT)
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update TABLES
        <set>
            <if test="tableCatalog != null and tableCatalog != ''">
                TABLE_CATALOG = #{tableCatalog},
            </if>
            <if test="tableSchema != null and tableSchema != ''">
                TABLE_SCHEMA = #{tableSchema},
            </if>
            <if test="tableName != null and tableName != ''">
                TABLE_NAME = #{tableName},
            </if>
            <if test="tableType != null and tableType != ''">
                TABLE_TYPE = #{tableType},
            </if>
            <if test="engine != null and engine != ''">
                ENGINE = #{engine},
            </if>
            <if test="version != null">
                VERSION = #{version},
            </if>
            <if test="rowFormat != null and rowFormat != ''">
                ROW_FORMAT = #{rowFormat},
            </if>
            <if test="tableRows != null">
                TABLE_ROWS = #{tableRows},
            </if>
            <if test="avgRowLength != null">
                AVG_ROW_LENGTH = #{avgRowLength},
            </if>
            <if test="dataLength != null">
                DATA_LENGTH = #{dataLength},
            </if>
            <if test="maxDataLength != null">
                MAX_DATA_LENGTH = #{maxDataLength},
            </if>
            <if test="indexLength != null">
                INDEX_LENGTH = #{indexLength},
            </if>
            <if test="dataFree != null">
                DATA_FREE = #{dataFree},
            </if>
            <if test="autoIncrement != null">
                AUTO_INCREMENT = #{autoIncrement},
            </if>
            <if test="createTime != null">
                CREATE_TIME = #{createTime},
            </if>
            <if test="updateTime != null">
                UPDATE_TIME = #{updateTime},
            </if>
            <if test="checkTime != null">
                CHECK_TIME = #{checkTime},
            </if>
            <if test="tableCollation != null and tableCollation != ''">
                TABLE_COLLATION = #{tableCollation},
            </if>
            <if test="checksum != null">
                CHECKSUM = #{checksum},
            </if>
            <if test="createOptions != null and createOptions != ''">
                CREATE_OPTIONS = #{createOptions},
            </if>
            <if test="tableComment != null and tableComment != ''">
                TABLE_COMMENT = #{tableComment},
            </if>
        </set>
        where = #{}
    </update>

    <!--通过主键删除-->
    <delete id="deleteBySchemaAndTable" parameterType="java.lang.String">
        delete
        from TABLES
        where TABLE_SCHEMA = #{tableSchema}
          and TABLE_NAME = #{tableName}
    </delete>

</mapper>

CRUD之Service.java

src\main\java\org.mjtabu.service

package org.mjtabu.service;

import org.mjtabu.entity.Tables;
import org.mjtabu.mapper.TablesMapper;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

/**
 * (Tables)表服务实现类
 *
 * @author makejava
 * @since 2021-12-30 14:32:33
 */
@Service("tablesService")
public class TablesService {
    @Resource
    private TablesMapper tablesMapper;

    /**
     * 通过数据库和数据库表查询单条数据
     *
     * @param tableSchema 数据库
     * @param tableName   数据库表
     * @return
     */
    public Tables queryBySchemaAndTable(String tableSchema, String tableName) {
        return tablesMapper.queryBySchemaAndTable(tableSchema, tableName);
    }

    /**
     * 分页查询
     *
     * @param tables      筛选条件
     * @param pageRequest 分页对象
     * @return 查询结果
     */
    public Page<Tables> queryByPage(Tables tables, PageRequest pageRequest) {
        long total = tablesMapper.count(tables);
        return new PageImpl<>(tablesMapper.queryAllByLimit(tables, pageRequest), pageRequest, total);
    }

    /**
     * 新增数据
     *
     * @param tables 实例对象
     * @return 实例对象
     */
    public Tables insert(Tables tables) {
        tablesMapper.insert(tables);
        return tables;
    }

    /**
     * 修改数据
     *
     * @param tables 实例对象
     * @return 实例对象
     */
    public Tables update(Tables tables) {
        tablesMapper.update(tables);
        return tablesMapper.queryBySchemaAndTable(tables.getTableSchema(), tables.getTableName());
    }

    /**
     * 通过数据库和数据库表删除数据
     *
     * @param tableSchema 数据库
     * @param tableName   数据库表
     * @return 是否成功
     */
    public boolean deleteBySchemaAndTable(String tableSchema, String tableName) {
        return tablesMapper.deleteBySchemaAndTable(tableSchema, tableName) > 0;
    }
}

CRUD之Controller.java

src\main\java\org.mjtabu.controller

package org.mjtabu.controller;

import lombok.extern.slf4j.Slf4j;
import org.mjtabu.entity.Tables;
import org.mjtabu.service.TablesService;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;

/**
 * (Tables)表控制层
 *
 * @author makejava
 * @since 2021-12-30 14:32:34
 */
@Slf4j
@RestController
@RequestMapping("tables")
public class TablesController {
    /**
     * 服务对象
     */
    @Resource
    private TablesService tablesService;

    /**
     * 分页查询
     *
     * @param tables      筛选条件
     * @param pageRequest 分页对象
     * @return 查询结果
     */
    @GetMapping
    public ResponseEntity<Page<Tables>> queryByPage(Tables tables, PageRequest pageRequest) {
        return ResponseEntity.ok(tablesService.queryByPage(tables, pageRequest));
    }

    /**
     * 通过数据库和数据库表查询单条数据
     *
     * @param tableSchema 数据库
     * @param tableName   数据库表
     * @return 单条数据
     */
    @GetMapping("/queryBySchemaAndTable/{tableSchema}/{tableName}")
    public ResponseEntity<Tables> queryBySchemaAndTable(@PathVariable("tableSchema") String tableSchema, @PathVariable("tableName") String tableName) {
        log.info("tableSchema:"+tableSchema+"tableName:"+tableName);
        return ResponseEntity.ok(tablesService.queryBySchemaAndTable(tableSchema, tableName));
    }

    /**
     * 新增数据
     *
     * @param tables 实体
     * @return 新增结果
     */
    @PostMapping
    public ResponseEntity<Tables> add(Tables tables) {
        return ResponseEntity.ok(tablesService.insert(tables));
    }

    /**
     * 编辑数据
     *
     * @param tables 实体
     * @return 编辑结果
     */
    @PutMapping
    public ResponseEntity<Tables> edit(Tables tables) {
        return ResponseEntity.ok(tablesService.update(tables));
    }

    /**
     * 通过数据库和数据库表删除数据
     *
     * @param tableSchema 数据库
     * @param tableName   数据库表
     * @return 删除是否成功
     */
    @GetMapping("/deleteBySchemaAndTable/{tableSchema}/{tableName}")
    public ResponseEntity<Boolean> deleteBySchemaAndTable(@PathVariable("tableSchema") String tableSchema, @PathVariable("tableName") String tableName) {
        return ResponseEntity.ok(tablesService.deleteBySchemaAndTable(tableSchema, tableName));
    }

}

Postman测试

Postman测试

通过数据库和数据库表查询单条数据

http://{{ip_port}}/springboot/tables/queryBySchemaAndTable/information_schema/TABLES