jdk 1.8
springboot 2.3.10
maven 用到的依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.3.10.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.3.10.RELEASE</version>
</dependency>
<!-- mysql驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-boot-starter</artifactId>
<version>3.0.0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>compile</scope>
</dependency>
首先用到获取数据表的类
import lombok.Data;
/**
* 表格字段数据
*/
@Data
public class DatabaseTableField {
private String tableCatalog;
private String tableSchema;
private String tableName;
private String columnName;
private Long ordinalPosition;
private String columnDefault;
private String isNullable;
private String dataType;
private Long characterMaximumLength;
private Long characterOctetLength;
private Long numericPrecision;
private Long numericScale;
private String datetimePrecisopm;
private String characterSetName;
private String collationName;
private String columnType;
private String columnKey;
private String extra;
private String privileges;
private String columnComment;
}
关键的数据库工具类：DataBaseUtil
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.lang.NonNull;
import org.springframework.stereotype.Component;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;
/**
* 数据库工具，用于对数据库相关数据进行处理
*
* @author zhangxuetu
* @datetime 2023-03-20
*/
@Component
public class DataBaseUtil {
private static JdbcTemplate jdbcTemplate;
@Autowired
public DataBaseUtil(JdbcTemplate jdbcTemplate) {
DataBaseUtil.jdbcTemplate = jdbcTemplate;
}
public static JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
/**
* 如果没有使用 Spring 启动的话需要手动进行JDBC初始化配置
*
* @param configPath 配置文件路径
*/
public static void initJdbcConfig(String configPath) {
try {
DataBaseUtil.jdbcTemplate = createJdbcTemplateByConfigFile(configPath);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 根据配置文件获取 JDBCTemplate 实例
*
* @param configPath 配置文件路径。例如 resources 文件夹下的 application.properties，直接传入 application.properties
* @return 返回这个配置的 jdbc 代码
*/
public static JdbcTemplate createJdbcTemplateByConfigFile(String configPath) throws IOException {
Properties properties = new Properties();
// 使用ClassLoader加载properties配置文件生成对应的输入流
InputStream in = DataBaseUtil.class.getClassLoader().getResourceAsStream(configPath);
if (in == null) {
throw new IllegalArgumentException(configPath + "文件不存在，请检查文件路径是否正确！");
}
// 使用properties对象加载输入流
properties.load(in);
//获取key对应的value值
String url = properties.getProperty("spring.data.url");
String username = properties.getProperty("spring.data.username");
String password = properties.getProperty("spring.data.password");
String driverClassName = properties.getProperty("spring.data.driverClassName");
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(driverClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
return new JdbcTemplate(dataSource);
}
/**
* Sql 类型转换为 Java 类型
*
* @param sqlType sql数据类型
* @return 返回对应的 Java 中的类型
*/
public static String toJavaFormatType(String sqlType) {
String type;
switch (sqlType.toLowerCase(Locale.ROOT)) {
case "int":
case "tinyint":
case "smallint":
return "Integer";
case "varchar":
case "text":
case "xmltype":
case "longtext":
case "char":
case "varchar2":
return "String";
case "double":
return "Double";
case "float":
return "Float";
case "timestamp":
case "datetime":
case "date":
case "time":
return "Date";
case "bigint":
return "BigInteger";
case "decimal":
return "BigDecimal";
default:
return "Object";
}
}
/**
* 列表转为 Map。用于创建 key 和 value 值都相同的 Map
*
* @param list 字段列表。
* <p>字符串中如果有 “<code>:</code>” 英文冒号则会进行切分，冒号左边的为 key，冒号右边的为 value。</p>
* <p>没有则 key 和 value 都是这个值</p>
*/
@SafeVarargs
public static Map<String, String> listToMap(String... list) {
return listToMap(Arrays.asList(list));
}
/**
* 列表转为 Map。用于创建 key 和 value 值都相同的 Map
*
* @param list 字段列表。
* <p>字符串中如果有 “<code>:</code>” 英文冒号则会进行切分，冒号左边的为 key，冒号右边的为 value。</p>
* <p>没有则 key 和 value 都是这个值</p>
*/
public static Map<String, String> listToMap(Collection<String> list) {
Map<String, String> hashMap = new LinkedHashMap<>();
for (String item : list) {
String[] split = item.split(":");
if (split.length == 1) {
hashMap.put(item, item);
} else {
hashMap.put(split[0].trim(), split[1].trim());
}
}
return hashMap;
}
/**
* 对每行进行缩进
*
* @param code 代码
* @param spaceChara 缩进空格字符串
* @return 返回缩进后的字符串
*/
public static String indent(String code, String spaceChara) {
String[] split = code.split("\n");
for (int i = 0; i < split.length; i++) {
split[i] = spaceChara + split[i];
}
return String.join("\n", split);
}
//=======================================================================
// 处理 JSON
//=======================================================================
/**
* 对象转为 JSON 字符串
*/
public static String objectToJson(Object object) {
ObjectMapper objectMapper = new ObjectMapper();
try {
return objectMapper.writeValueAsString(object);
} catch (JsonProcessingException e) {
e.printStackTrace();
return null;
}
}
/**
* 字符串转为 Map 对象
*/
public static <K, V> Map<K, V> jsonToMap(String json) {
ObjectMapper objectMapper = new ObjectMapper();
try {
return objectMapper.readValue(json, Map.class);
} catch (JsonProcessingException e) {
e.printStackTrace();
return null;
}
}
//=======================================================================
// 数据库相关
//=======================================================================
/**
* 查询表结构
*
* @param databaseName 数据库名
* @param tableName 表名
* @return 返回这个数据库中这个表的字段信息列表
*/
public static List<DatabaseTableField> queryTablesStructure(String databaseName, String tableName) {
String sql = "SELECT * " +
"FROM INFORMATION_SCHEMA.COLUMNS " +
"WHERE TABLE_NAME='" + tableName + "' AND TABLE_SCHEMA='" + databaseName + "';";
RowMapper<DatabaseTableField> rowMapper = new BeanPropertyRowMapper<>(DatabaseTableField.class);
return jdbcTemplate.query(sql, rowMapper);
}
/**
* 创建 Table SQL 代码
*
* @param tableName 表名
* @param map 根据这个Map创建
* @return 返回创建表SQL代码
*/
public static String generateCreateTable(String tableName, Map<?, ?> map) {
String code = "CREATE TABLE " + tableName + "(\n";
code += map.keySet().stream()
.map(key -> String.format("\t%s VARCHAR(100)", key.toString()))
.collect(Collectors.joining(",\n"));
code += "\n);\n";
return code;
}
/**
* 创建 Table SQL 代码
*
* @param tableName 表名
* @param json 根据这个 Map 格式的 JSON 创建
* @return 返回创建表SQL代码
*/
public static String generateCreateTable(String tableName, String json) {
Map<Object, Object> map = jsonToMap(json);
if (map != null) {
return generateCreateTable(tableName, map);
}
return "";
}
//=======================================================================
// Java 相关
//=======================================================================
/**
* 生成实体类
*
* @param entityName 生成的实体类的名称
* @param databaseTableFields 数据表字段信息列表
* @return 返回生成的 Java 实体类代码
*/
public static String generateEntityClass(String entityName, List<DatabaseTableField> databaseTableFields) {
// 注解
String entityCode = String.join("\n", Arrays.asList(
"@Data", "@ApiModel(\"\")"
)) + "\n";
entityCode += String.format("public class %s {\n", entityName);
// 字段列表
entityCode += databaseTableFields.stream()
.map(databaseTableField -> {
// 每个字段的格式
String code = "";
code += String.format("\t@ApiModelProperty(\"%s\")\n", databaseTableField.getColumnComment());
code += String.format("\tprivate %s %s;\n", toJavaFormatType(databaseTableField.getDataType()), databaseTableField.getColumnName());
return code;
})
.collect(Collectors.joining(""));
entityCode += "}\n";
// 导入包
String importCode = String.join("\n", Arrays.asList(
"import io.swagger.annotations.ApiModel;",
"import io.swagger.annotations.ApiModelProperty;"
)) + "\n";
if (entityCode.contains("BigInteger") || entityCode.contains("BigDecimal")) {
importCode += "import java.math.*;\n";
}
if (entityCode.contains("Date")) {
importCode += "import java.util.*;\n";
}
return importCode + "\n" + entityCode;
}
//=======================================================================
// MyBatis相关
//=======================================================================
/**
* 创建标签
*
* @param tagName 标签名
* @param body 标签内容
* @return 返回生成的标签
*/
public static String createTag(String tagName, String body) {
return createTag(tagName, null, body);
}
/**
* 创建标签
*
* @param tagName 标签名
* @param params 标签参数
* @param body 标签内容
* @return 返回生成的标签
*/
public static String createTag(String tagName, Map<String, String> params, String body) {
String paramsString = "";
if (params != null && !params.isEmpty()) {
for (Map.Entry<String, String> entry : params.entrySet()) {
paramsString += String.format("%s=\"%s\" ", entry.getKey(), entry.getValue());
}
}
return String.format("<%s %s>\n%s\n</%s>", tagName, paramsString, body, tagName);
}
/**
* 生成 where 标签
*
* @param map 数据库字段对应的 Java 字段
* @return 返回生成的 where 类型的 trim 标签
*/
private static String generateWhereTag(Map<?, ?> map) {
Map<String, String> tagParams = new LinkedHashMap<>();
tagParams.put("prefix", "WHERE");
tagParams.put("prefixOverrides", "AND");
String body = String.join("", map.entrySet()
.stream()
.map(v -> "\t<if test=\"" + v.getValue() + "!=null\">AND " + v.getKey() + "=#{" + v.getValue() + "}</if>\n")
.collect(Collectors.toList())
);
// 创建 where 版 trim 标签
return createTag("trim", tagParams, "\t" + body.trim());
}
/**
* 生成 set 标签
*
* @param map 表字段对应的Java类字段
* @return 返回生成的 set 类型的 trim 标签
*/
private static String generateSetTag(Map<String, String> map) {
Map<String, String> tagParams = new LinkedHashMap<>();
tagParams.put("prevfix", "SET");
tagParams.put("suffixOverrides", ",");
String body = String.join("", map.entrySet()
.stream()
.map(v -> "\t<if test=\"" + v.getValue() + "!=null\">" + v.getKey() + "=#{" + v.getValue() + "},</if>\n")
.collect(Collectors.toList())
);
// 创建 set 版 trim 标签
return createTag("trim", tagParams, "\t" + body.trim());
}
/**
* 生成 Select Mybatis 代码
*
* @param tableName 表名
* @return 返回生成的 select 标签代码
*/
public static String generateMapperSelectTag(String tableName) {
return createTag("select", "SELECT * FROM " + tableName);
}
/**
* 生成 Select Mybatis 代码
*
* @param id 标签ID，即Mapper的方法名
* @param resultType 返回结果 Java 类的类型
* @param tableName 表名
* @param where WHERE条件中表字段对应Java类字段的名字，例如：{"role_name": "roleName"}
* @return 返回生成的 select 标签代码
*/
public static String generateMapperSelectTag(String id, String resultType, @NonNull String tableName, Map<String, String> where) {
String sql = "SELECT * FROM " + tableName + " \n";
if (!where.isEmpty()) {
sql += generateWhereTag(where);
}
sql = indent(sql, " ");
Map<String, String> tagParams = new HashMap<>();
tagParams.put("id", id);
tagParams.put("resultType", resultType);
return createTag("select", tagParams, sql);
}
/**
* 生成 insert 标签代码
*
* @param id 标签ID，即Mapper的方法名
* @param tableName 表名
* @param sqlFieldNames 字段名列表
* @param javaFieldNames 对应插入的Java字段列表
* @return 返回生成的 insert 标签代码
*/
public static String generateInsertTag(String id, String tableName, List<String> sqlFieldNames, List<String> javaFieldNames) {
String sql = "INSERT INTO " + tableName + "( " + String.join(", ", sqlFieldNames) + " ) \n";
sql += "VALUES ( ";
sql += javaFieldNames.stream()
.map(v -> "#{" + v + "}")
.collect(Collectors.joining(", "));
sql += " )";
sql = indent(sql, " ");
Map<String, String> tagParams = new HashMap<>();
tagParams.put("id", id);
return createTag("insert", tagParams, sql);
}
/**
* 生成 update 标签代码
*
* @param id 标签ID，即Mapper的方法名
* @param tableName 表名
* @param setMap SET 字段列表
* @param whereMap WHERE 条件列表
* @return 返回生成的 update 标签代码
*/
public static String generateUpdateTag(String id, String tableName, Map<String, String> setMap, Map<String, String> whereMap) {
String sql = " UPDATE " + tableName + "\n";
sql += indent(generateSetTag(setMap), " ");
sql += "\n" + indent(generateWhereTag(whereMap), " ");
Map<String, String> tagParams = new HashMap<>();
tagParams.put("id", id);
return createTag("update", tagParams, sql);
}
/**
* 生成 delete 标签代码
*
* @param id 标签ID，即Mapper的方法名
* @param tableName 表名
* @param where WHERE 条件列表
* @return 返回生成的 delete 标签代码
*/
public static String generateDeleteTag(String id, String tableName, Map<String, String> where) {
String sql = " DELETE FROM " + tableName + "\n";
sql += indent(generateWhereTag(where), " ");
Map<String, String> tagParams = new HashMap<>();
tagParams.put("id", id);
return createTag("delete", tagParams, sql);
}
/**
* 执行查询
*
* @param sql 执行SQL
* @param params SQL里对应的 ? 字符串的值
* @return 返回执行结果
*/
public static List<Map<String, Object>> query(String sql, Object... params) {
if (params.length == 0) {
return jdbcTemplate.queryForList(sql);
}
return jdbcTemplate.queryForList(sql, params);
}
/**
* 查询一个值
*
* @param sql 执行SQL
* @param params SQL里对应的 ? 字符串的值
* @return 返回执行结果
*/
public static Map<String, Object> queryOne(String sql, Object... params) {
return jdbcTemplate.queryForMap(sql, params);
}
/**
* 执行更新
*
* @param sql 执行SQL
* @param params SQL里对应的 ? 字符串的值
* @return 返回执行结果
*/
public static int update(String sql, Object... params) {
if (params.length == 0) {
return jdbcTemplate.update(sql);
}
return jdbcTemplate.update(sql, params);
}
}
application.properties 配置文件
spring.data.driverClassName=com.mysql.cj.jdbc.Driver
spring.data.url=jdbc:mysql://127.0.0.1:3306/test?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.data.username=root
spring.data.password=123456
简单使用一下，测试用例：
import org.junit.Test;
import java.util.*;
/**
* 数据库工具测试用例
*
* @author zhangxuetu
* @date 2023-03-20
*/
public class DBTestCase {
static {
DataBaseUtil.initJdbcConfig("application.properties");
}
String databaseName = "test";
String tableName = "location_info_863659043180750";
String entityName = "location_info_863659043180750";
@Test
public void testGenerateEntity() {
// 生成实体类
List<DatabaseTableField> databaseTableFields = DataBaseUtil.queryTablesStructure(databaseName, databaseName);
String code = DataBaseUtil.generateEntityClass(entityName, databaseTableFields);
System.out.println(code);
}
@Test
public void testCreateTable() {
// 生成创建表SQL
String sql = DataBaseUtil.generateCreateTable(databaseName + "." + tableName, "{\"a\": 1}");
int count = DataBaseUtil.update(sql);
System.out.println(count);
}
@Test
public void testSelectTag() {
// 生成 mybatis 的 select 标签
Map<String, String> where = new HashMap<>();
where.put("name", "userName");
where.put("age", "age");
String sql = DataBaseUtil.generateMapperSelectTag("queryTestData", entityName, tableName, where);
System.out.println(sql);
}
@Test
public void testInsertTag() {
// 生成 mybatis 的 insert 标签
String code = DataBaseUtil.generateInsertTag("insertTest", tableName,
Arrays.asList("name", "aa", "fas"),
Arrays.asList("name", "aa", "ccc")
);
System.out.println(code);
}
@Test
public void testUpdateTag() {
// 生成 mybatis 的 update 标签
Map<String, String> setMap = DataBaseUtil.listToMap("username:userName", "create_time:createTime", "desc");
Map<String, String> where = DataBaseUtil.listToMap("id");
String code = DataBaseUtil.generateUpdateTag("updateTest", tableName, setMap, where);
System.out.println(code);
}
@Test
public void testDeleteTag() {
// 生成 mybatis 的 delete 标签
Map<String, String> where = new HashMap<>();
where.put("id", "id");
String code = DataBaseUtil.generateDeleteTag("deleteTag", tableName, where);
System.out.println(code);
}
@Test
public void testExecuteSql() {
// 执行SQL
List<Map<String, Object>> list = DataBaseUtil.query(
"SELECT tag_code " +
"FROM test.location_info_863659043180750 " +
"WHERE tag_code=?",
"863659043180750"
);
System.out.println(list);
}
@Test
public void testExecuteSqlQueryOne() {
// 执行查询单个对象 SQL
String sql = "SELECT COUNT(0) AS 'count' " +
"FROM test.location_info_863659043180750 " +
"WHERE tag_code = ? " +
"GROUP BY tag_code ";
Map<String, Object> objectMap = DataBaseUtil.queryOne(sql, "863659043180750");
// 转为 JSON 打印输出
System.out.println(DataBaseUtil.objectToJson(objectMap));
}
}