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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2019-12-30 Http请求代码模板类
2019-12-30 Android Service的onStartCommand方法的返回值和参数详解