公司用的jpa，没有用mybatis。所以也没有用mybatis自动生成。但有些数据库表字段太多，就想着一劳永逸了，连数据库注释都搞上去
第一种
这里使用的是jdbcTemplate+Junit测试生成，方式可变。
SpringBoot版本是2.4.4,只需要加上@SpringBootTest就可以了。不用@RunWith
pom:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>代码
package com.shinedata.bims.web;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @ClassName TestController
* @Author yupanpan
* @Date 2021/4/12 14:24
*/
@SpringBootTest
public class TestController {
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
public void database(){
createEntity("1ceping", "t_evaluation_template_info_copy","com.shinedata.bims.entity",false);
}
/**
*
* @param dataBaseName 数据库名
* @param tableName 表名
* @param packageName 包名
* @param isAddEntitySuffix 类名是否添加Entity后缀 true-添加 false-不添加
*/
public void createEntity(String dataBaseName,String tableName,String packageName,boolean isAddEntitySuffix){
String className=tableName;
if(tableName.substring(0,2).equals("t_")){
StringBuilder stringBuilder = new StringBuilder(tableName);
stringBuilder.replace(0, 2, "");
String initialsUpperCase = stringBuilder.substring(0, 1).toUpperCase();
className=initialsUpperCase+stringBuilder.substring(1);
}
className=removeUnderline(className)+(isAddEntitySuffix?"Entity":"");
StringBuffer classBuffer=new StringBuffer();
classBuffer.append("import java.util.Date;\r\n");
classBuffer.append("import java.time.LocalDateTime;\r\n");
classBuffer.append("import com.alibaba.fastjson.JSONObject;\r\n");
classBuffer.append("import java.lang.*;\r\n");
classBuffer.append("import java.math.*;\r\n");
classBuffer.append("import java.sql.*;\r\n");
classBuffer.append("import lombok.Data;\r\n\r\n\r\n");
classBuffer.append("@Data\r\n");
classBuffer.append("public class " + className + " {\r\n\r\n");
List<Map> filedMaps = getFiledMaps(dataBaseName, tableName);
processAllAttrs(classBuffer,filedMaps);
classBuffer.append("}\r\n");
markerBean(className,classBuffer.toString(),packageName);
}
/**
* 创建实体类文件
* @param className 类名(不包含.java文件名后缀) 根据表名首字母大写并去掉开头t_和所有下划线-驼峰命名
* @param content 添加的内容(字段注释等)
* @param packageName 包名(com.xxx.xxx.xxx)
*/
public void markerBean(String className, String content, String packageName) {
String folder = System.getProperty("user.dir") + "/src/main/java/" + packageName.replace(".","/") + "/";
File file = new File(folder);
if (!file.exists()) {
file.mkdirs();
}
String fileName = folder + className + ".java";
try {
File newjava = new File(fileName);
FileWriter fw = new FileWriter(newjava);
fw.write("package\t" + packageName + ";\r\n");
fw.write(content);
fw.flush();
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 解析输出属性
*
* @return
*/
private void processAllAttrs(StringBuffer sb,List<Map> filedMaps) {
for (int i = 0; i < filedMaps.size(); i++) {
Map map = filedMaps.get(i);
String fieldType = MapUtils.getString(map, "fieldType");
String fieldName = MapUtils.getString(map, "fieldName");
String fieldComment = MapUtils.getString(map, "fieldComment");
if(StringUtils.isNotBlank(fieldComment)){
sb.append("\t/**\r\n").append("\t* ").append(fieldComment).append("\n").append("\t*/\r\n");
}
sb.append("\tprivate " + fieldType + " " + fieldName + ";\r\n\r\n");
}
}
/**
* 获取表字段信息(列名、类型、注释等)
* @param dataBaseName
* @param tableName
* @return
*/
private List<Map> getFiledMaps(String dataBaseName,String tableName) {
String sql="SELECT * FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA ='"+dataBaseName+"' AND TABLE_NAME = '"+tableName+"'";
List<Map> tableFieldList = jdbcTemplate.query(sql, new RowMapper<Map>() {
@Override
public Map<String,String> mapRow(ResultSet resultSet, int rowNum) throws SQLException {
Map<String,String> fieldMap = new HashMap();
String column_name = resultSet.getString("COLUMN_NAME");
setFieldName(column_name.toLowerCase(),fieldMap);
String data_type = resultSet.getString("DATA_TYPE");
setFieldType(data_type.toUpperCase(),fieldMap);
fieldMap.put("fieldComment",resultSet.getString("COLUMN_COMMENT"));
return fieldMap;
}
});
return tableFieldList;
}
private void setFieldName(String columnName, Map fieldMap) {
fieldMap.put("fieldName",removeUnderline(columnName));
}
/**
* 去下划线
* @param string
* @return
*/
public String removeUnderline(String string){
StringBuilder columnNameBuilder=new StringBuilder(string);
if(!string.contains("_")){
return string;
}else {
int i = columnNameBuilder.indexOf("_");
columnNameBuilder.replace(i,i+1, "").replace(i,i+1,columnNameBuilder.substring(i,i+1).toUpperCase());
return removeUnderline(columnNameBuilder.toString());
}
}
private void setFieldType(String columnType,Map fieldMap){
String fieldType="String";
if(columnType.equals("INT")||columnType.equals("INTEGER")){
fieldType="Integer";
}else if(columnType.equals("BIGINT")){
fieldType="Long";
}else if(columnType.equals("DATETIME")){
fieldType="Date";
}else if(columnType.equals("TEXT")||columnType.equals("VARCHAR")||columnType.equals("TINYTEXT")||columnType.equals("LONGTEXT")){
fieldType="String";
}else if(columnType.equals("DOUBLE")){
fieldType="Double";
}else if(columnType.equals("BIT")){
fieldType="Boolean";
}else if(columnType.equals("FLOAT")){
fieldType="Float";
}else if(columnType.equals("DECIMAL")){
fieldType="BigDecimal";
}else if(columnType.equals("DATE")){
fieldType="Date";
}else if(columnType.equals("TIMESTAMP")){
fieldType="LocalDateTime";
}else if(columnType.equals("CHAR")){
fieldType="Char";
}else if(columnType.equals("JSON")){//mysql5.7版本才开始有的
fieldType="JSONObject";
}
fieldMap.put("fieldType",fieldType);
}
}
生成的类
用的lombok，就懒得去搞getset了
第二种
搞的是直接main方法运行下就生成，大同小异。不用Junit，方便一些，就是个工具。和第一种结果一样的
package com.shinedata.bims.web;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;
/**
* @ClassName TableToEntityUtils
* @Author yupanpan
* @Date 2021/6/8 17:25
*/
public class TableToEntityUtils {
static final String USER = "root";
static final String PASS = "xxxxxxxxxxx";
// static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
// MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://xxxxxxxxxxxxxxxxxxxxxxx:3306/1ceping?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&allowMultiQueries=true&rewriteBatchedStatements=true";
public static void main(String[] args) throws Exception {
createEntity("1ceping", "t_evaluation_template_info_copy","com.shinedata.bims.entity",false);
}
/**
*
* @param dataBaseName 数据库名
* @param tableName 表名
* @param packageName 包名
* @param isAddEntitySuffix 类名是否添加Entity后缀 true-添加 false-不添加
*/
public static void createEntity(String dataBaseName, String tableName, String packageName, boolean isAddEntitySuffix) throws Exception{
String className=tableName;
if(tableName.substring(0,2).equals("t_")){
StringBuilder stringBuilder = new StringBuilder(tableName);
stringBuilder.replace(0, 2, "");
String initialsUpperCase = stringBuilder.substring(0, 1).toUpperCase();
className=initialsUpperCase+stringBuilder.substring(1);
}
className=removeUnderline(className)+(isAddEntitySuffix?"Entity":"");
StringBuffer classBuffer=new StringBuffer();
classBuffer.append("import java.util.Date;\r\n");
classBuffer.append("import java.time.LocalDateTime;\r\n");
classBuffer.append("import com.alibaba.fastjson.JSONObject;\r\n");
classBuffer.append("import java.lang.*;\r\n");
classBuffer.append("import java.math.*;\r\n");
classBuffer.append("import java.sql.*;\r\n");
classBuffer.append("import lombok.Data;\r\n\r\n\r\n");
classBuffer.append("@Data\r\n");
classBuffer.append("public class " + className + " {\r\n\r\n");
List<Map> filedMaps = getFiledMaps(dataBaseName, tableName);
processAllAttrs(classBuffer,filedMaps);
classBuffer.append("}\r\n");
markerBean(className,classBuffer.toString(),packageName);
}
/**
* 创建实体类文件
* @param className 类名(不包含.java文件名后缀) 根据表名首字母大写并去掉开头t_和所有下划线-驼峰命名
* @param content 添加的内容(字段注释等)
* @param packageName 包名(com.xxx.xxx.xxx)
*/
public static void markerBean(String className, String content, String packageName) throws Exception {
// 这里不使用System.getProperty("user.dir")了。user.dir是根据运行时环境来的
File f2 = new File(TableToEntityUtils.class.getResource("/").getPath());
String homePath=f2.getCanonicalPath().replace("\\target\\classes", "");
String folder = homePath + "/src/main/java/" + packageName.replace(".","/") + "/";
File file = new File(folder);
if (!file.exists()) {
file.mkdirs();
}
String fileName = folder + className + ".java";
try {
File newjava = new File(fileName);
FileWriter fw = new FileWriter(newjava);
fw.write("package\t" + packageName + ";\r\n");
fw.write(content);
fw.flush();
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 去下划线
* @param string
* @return
*/
public static String removeUnderline(String string){
StringBuilder columnNameBuilder=new StringBuilder(string);
if(!string.contains("_")){
return string;
}else {
int i = columnNameBuilder.indexOf("_");
columnNameBuilder.replace(i,i+1, "").replace(i,i+1,columnNameBuilder.substring(i,i+1).toUpperCase());
return removeUnderline(columnNameBuilder.toString());
}
}
/**
* 获取表字段信息(列名、类型、注释等)
* @param dataBaseName
* @param tableName
* @return
*/
private static List<Map> getFiledMaps(String dataBaseName, String tableName) {
Connection conn = null;
Statement stmt = null;
List<Map> tableFieldList=new ArrayList<>();
try{
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
// 执行查询
stmt = conn.createStatement();
String sql= "SELECT * FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA ='"+dataBaseName+"' AND TABLE_NAME= '"+tableName+"'";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
Map<String,String> fieldMap = new HashMap();
String column_name = rs.getString("COLUMN_NAME");
setFieldName(column_name.toLowerCase(),fieldMap);
String data_type = rs.getString("DATA_TYPE");
setFieldType(data_type.toUpperCase(),fieldMap);
fieldMap.put("fieldComment",rs.getString("COLUMN_COMMENT"));
tableFieldList.add(fieldMap);
}
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
// 处理 JDBC 错误
se.printStackTrace();
}catch(Exception e){
// 处理 Class.forName 错误
e.printStackTrace();
}finally{
// 关闭资源
try{
if(stmt!=null){
stmt.close();
}
}catch(SQLException se2){
}// 什么都不做
try{
if(conn!=null) conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
return tableFieldList;
}
/**
* 解析输出属性
*
* @return
*/
private static void processAllAttrs(StringBuffer sb, List<Map> filedMaps) {
for (int i = 0; i < filedMaps.size(); i++) {
Map map = filedMaps.get(i);
String fieldType = MapUtils.getString(map, "fieldType");
String fieldName = MapUtils.getString(map, "fieldName");
String fieldComment = MapUtils.getString(map, "fieldComment");
if(StringUtils.isNotBlank(fieldComment)){
sb.append("\t/**\r\n").append("\t* ").append(fieldComment).append("\n").append("\t*/\r\n");
}
sb.append("\tprivate " + fieldType + " " + fieldName + ";\r\n\r\n");
}
}
private static void setFieldName(String columnName, Map fieldMap) {
fieldMap.put("fieldName",removeUnderline(columnName));
}
private static void setFieldType(String columnType, Map fieldMap){
String fieldType="String";
if(columnType.equals("INT")||columnType.equals("INTEGER")){
fieldType="Integer";
}else if(columnType.equals("BIGINT")){
fieldType="Long";
}else if(columnType.equals("DATETIME")){
fieldType="Date";
}else if(columnType.equals("TEXT")||columnType.equals("VARCHAR")||columnType.equals("TINYTEXT")||columnType.equals("LONGTEXT")){
fieldType="String";
}else if(columnType.equals("DOUBLE")){
fieldType="Double";
}else if(columnType.equals("BIT")){
fieldType="Boolean";
}else if(columnType.equals("FLOAT")){
fieldType="Float";
}else if(columnType.equals("DECIMAL")){
fieldType="BigDecimal";
}else if(columnType.equals("DATE")){
fieldType="Date";
}else if(columnType.equals("TIMESTAMP")){
fieldType="LocalDateTime";
}else if(columnType.equals("CHAR")){
fieldType="Char";
}else if(columnType.equals("JSON")){//mysql5.7版本才开始有的
fieldType="JSONObject";
}
fieldMap.put("fieldType",fieldType);
}
}
根据实体类生成创建表语句
package com.fruit.system.utils;
import org.apache.commons.lang3.StringUtils;
import javax.persistence.Table;
import java.io.File;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URL;
import java.util.*;
/**
* @ClassName EntityToTableUtils
* @Author yupanpan
* @Date 2021/11/16 15:23
*/
public class EntityToTableUtils {
/**
* 用来存储Java等属性类型与sql中属性类型的对照
* </br>
* 例如：java.lang.Integer 对应 integer
*/
public static Map<String, String> map = new HashMap<>();
static {
map.put("class java.lang.String", "varchar(255)");
map.put("class java.lang.Integer", "int(11)");
map.put("class java.lang.Long", "bigint(20)");
map.put("class java.lang.byte[]", "blob");
map.put("class java.lang.Boolean", "bit");
map.put("class java.math.BigInteger", "bigint unsigned");
map.put("class java.lang.Float", "float(10,1)");
map.put("class java.lang.Double", "double(10,2)");
map.put("class java.sql.Date", "datetime");
map.put("class java.sql.Time", "time");
map.put("class java.sql.Timestamp", "datetime");
map.put("class java.util.Date", "datetime");
map.put("class java.lang.Byte", "tinyint");
map.put("class java.math.BigDecimal", "decimal(10,2)");
}
public static void main(String[] args) throws Exception{
createTableSql("com.fruit.system.entity");
}
public static void createTableSql(String packageName) throws Exception {
List<String> classNames = getClassName(packageName);
for (String reference : classNames) {
Class<?> aClass = Class.forName(reference);
Table table = aClass.getAnnotation(Table.class);
if(table!=null){
StringBuilder sb=new StringBuilder();
String primaryKey="id";
String name = table.name();
if(StringUtils.isNotBlank(name)){
sb.append("CREATE TABLE `"+name+"` (");
sb.append("\n");
}
List<Field> allFields=getAllFields(aClass);
for (Field field : allFields) {
String fieldName = field.getName();
String columnName = getStandardFields(fieldName);
Class<?> type = field.getType();
String typeName = type.toString();
String columnType = map.get(typeName);
if(StringUtils.isNotBlank(columnType)){
StringBuilder columnDDL=new StringBuilder();
columnDDL.append("\t").append("`").append(columnName).append("` ").append(columnType);
if(primaryKey.equals(columnName)){
columnDDL.append(" NOT NULL,");
}else {
columnDDL.append(" DEFAULT NULL,");
}
columnDDL.append("\n");
//将id主键放在最前面一列
if(primaryKey.equals(columnName)){
int i = sb.indexOf("(");
//i+2，有个换行符
sb=new StringBuilder(sb.substring(0,i+2)+columnDDL.toString()+sb.substring(i+2));
}else {
sb.append(columnDDL);
}
}
}
sb.append("\t");
sb.append("PRIMARY KEY (`"+primaryKey+"`)");
sb.append("\n");
sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
sb.append("\n");
System.out.println(sb.toString());
}
}
}
/**
* 获取所有字段(包括父类的，排除Object)
* @return
*/
public static List<Field> getAllFields(Class<?> aClass) {
List<Field> fields=new ArrayList<>();
Field[] declaredFields = aClass.getDeclaredFields();
fields.addAll(Arrays.asList(declaredFields));
Class<?> superclass = aClass.getSuperclass();
while(superclass!=null){
// System.out.println(superclass.getName());
if("java.lang.Object".equals(superclass.getName())){
break;
}
fields.addAll(Arrays.asList(superclass.getDeclaredFields()));
superclass=superclass.getSuperclass();
}
return fields;
}
/**
* 转换为标准等sql字段 例如 adminUser → admin_user
*
* @param str 转换为字符串的字段名
* @return
*/
public static String getStandardFields(String str) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < str.length(); i++) {
char c = str.charAt(i);
if (i != 0 && (c >= 'A' && c <= 'Z')) {
sb.append("_");
}
sb.append(c);
}
return sb.toString().toLowerCase();
}
/**
* 获得对应包下的类名称(自动遍历子包)
* <功能详细描述>
* @param packageName
* @return
*/
public static List<String> getClassName(String packageName)
{
ClassLoader loader = Thread.currentThread().getContextClassLoader();
String packagePath = packageName.replace(".", "/");
//加载包下的所有资源
URL url = loader.getResource(packagePath);
List<String> fileNames = null;
if (url != null)
{
String type = url.getProtocol();
if (type.equals("file"))
{
fileNames = getClassName(url.getPath(), null);
}
}
return fileNames;
}
/**
* 根据资源路径返回 class 路径
* <功能详细描述>
* @param filePath
* @param className
* @return
*/
private static List<String> getClassName(String filePath, List<String> className)
{
List<String> myClassName = new ArrayList<String>();
File file = new File(filePath);
File[] childFiles = file.listFiles();
for (File childFile : childFiles)
{
if (childFile.isDirectory())
{
myClassName.addAll(getClassName(childFile.getPath(), myClassName));
}
else
{
String childFilePath = childFile.getPath();
childFilePath =
childFilePath.substring(childFilePath.indexOf("\\classes") + 9, childFilePath.lastIndexOf("."));
childFilePath = childFilePath.replace("\\", ".");
myClassName.add(childFilePath);
}
}
return myClassName;
}
}
实体类用了@Table(name="t_member")注解定义表名
结果:
CREATE TABLE `t_member` (
`id` bigint(20) NOT NULL,
`bind_member_id` bigint(20) DEFAULT NULL,
`user_name` varchar(255) DEFAULT NULL,
`tel` varchar(255) DEFAULT NULL,
`employee_id` bigint(20) DEFAULT NULL,
`open_id` varchar(255) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`delete_status` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `t_member_coupon` (
`id` bigint(20) NOT NULL,
`member_id` bigint(20) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`source_id` bigint(20) DEFAULT NULL,
`use_channel` int(11) DEFAULT NULL,
`use_product_id` bigint(20) DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`satisfy_amount` decimal(10,2) DEFAULT NULL,
`expire_time` datetime DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`delete_status` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `t_member_coupon_setting` (
`id` bigint(20) NOT NULL,
`type` int(11) DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`satisfy_amount` decimal(10,2) DEFAULT NULL,
`expire_type` int(11) DEFAULT NULL,
`expire_value` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
`delete_status` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3773
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
