Java，通过实体类生成sql
方式一：
package com.xz;
import com.xz.entity.IndexProduct;//本地实体类
import com.xz.entity.User;//本地实体类
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import javax.xml.bind.annotation.XmlElement;
public class CreateTable {
/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
createTable(User.class, null);
// createTable(Book.class, null);
}
public static void createTable(Class obj,String tableName) throws IOException{
Field[] fields = null;
fields = obj.getDeclaredFields();
Class annotationType = null;
Object param = null;
String column = null;
XmlElement xmlElement = null;
StringBuilder sb = null;
sb = new StringBuilder(50);
if(tableName==null||tableName.equals("")){
//未传表明默认用类名
tableName = obj.getName();
tableName = tableName.substring(tableName.lastIndexOf(".")+1);
}
sb.append("\r\ndrop table if exists ").append(tableName).append(";\r\n");
sb.append("create table ").append(tableName).append(" ( \r\n");
System.out.println(tableName);
boolean firstId = true;
File file = null;
for(Field f:fields){
column = f.getName();
sb.append(column).append(" ");
System.out.println(column+","+f.getType());
param = f.getType();
sb.append(column);//一般第一个是主键
if(param instanceof Integer){
sb.append(" INTEGER ");
}else{
sb.append(" VARCHAR(30) ");//根据需要自行修改
}
if(firstId){//类型转换
sb.append(" PRIMARY KEY ");
firstId = false;
}
//获取字段中包含fieldMeta的注解
//2、获取属性上的所有注释
Annotation[] allAnnotations = f.getAnnotations();
for(Annotation an : allAnnotations){
sb.append(" COMMIT '");
xmlElement = (XmlElement)an;
annotationType = an.annotationType();
param = ((XmlElement) an).name();
System.out.println("属性【"+f.getName()+"-----的注释类型有: " + param);
sb.append(param).append("'");
}
sb.append(",\n ");
}
String sql = null;
sql = sb.toString();
sql = sb.substring(0, sql.length()-1)+" )ENGINE =INNODB DEFAULT CHARSET= utf8;\r\n";
file = new File("WebContent/createTable/User.sql"); //文件保存路径与命名
if (!file.getParentFile().exists()) {
if (!file.getParentFile().mkdirs()) {
}
}
if (!file.exists()) {
file.createNewFile();
}
System.out.println("文件路径:"+file.getAbsolutePath());
BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file,true)));
out.write(sql) ;
out.flush();
out.close() ;
}
}实体类:
package com.xz.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
//使用这个注解，就不用再去手写Getter,Setter,equals,canEqual,hasCode,toString等方法了，注解后在编译时会自动加进去。
@AllArgsConstructor
//使用后添加一个构造函数，该构造函数含有所有已声明字段属性参数
@NoArgsConstructor
//使用后创建一个无参构造函数
public class User {
private int uid;
private String uname;
private String upwd;
private String email;
private String phone;
private String avatar;
private String user_name;
private int gender;
}成功演示:
方式二：
package com.xz;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SqlGenerator {
private static final Logger logger = LoggerFactory.getLogger(SqlGenerator.class);
/**
* 用来存储Java等属性类型与sql中属性类型的对照
* </br>
* 例如：java.lang.Integer 对应 integer
*/
public static Map<String, String> map = new HashMap<>();
public static void main(String[] args) {
map.put("class java.lang.String", "varchar(255)");
map.put("class java.lang.Integer", "int");
map.put("class java.lang.Long", "integer unsigned");
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");
map.put("class java.lang.Double", "double");
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.time.LocalDateTime", "datetime");
map.put("class java.time.LocalDate", "date");
map.put("class java.time.LocalTime", "time");
map.put("class java.lang.Byte", "tinyint");
map.put("class java.math.BigDecimal", "decimal");
//实体类所在的package在磁盘上的绝对路径
String packageName = "D:/pro/m_system/src/main/java/com/test/model/entity";
//生成的sql文件名
String sqlName = "entityToSql";
//生成sql的文件夹
String filePath = "D:/pscp/" + sqlName;
//表命名前缀
String tablePrefix = "is_";
//项目中实体类的路径,（可以不用修改）
// String prefix = "com.test.model.entity.";
String prefix = packageName.substring(packageName.indexOf("java") + 5).replace("/", ".") + ".";
//开始构造sql
sqlConstruction(packageName, filePath, prefix, tablePrefix);
}
/**
* 生成sql建库语句
* @param tablePrefix 表命名前缀
* @param packageName 实体类所在的package在磁盘上的绝对路径
* @param filePath 生成sql的文件夹
* @param prefix 项目中实体类的路径
*/
private static void sqlConstruction(String packageName, String filePath, String prefix, String tablePrefix) {
String className = "";
StringBuffer sqls = new StringBuffer();
//获取包下的所有类名称
List<String> list = getAllClasses(packageName);
for (String str : list) {
className = prefix + str.substring(0, str.lastIndexOf("."));
String sql = generateSql(className, tablePrefix);
sqls.append(sql);
}
StringToSql(sqls.toString(), filePath + ".sql");
}
/**
* 根据实体类生成建表语句
*
* @param className 全类名
* @param tablePrefix 表命名前缀
* @return
*/
public static String generateSql(String className, String tablePrefix) {
try {
Class<?> clz = Class.forName(className);
className = clz.getSimpleName();
// 表表名adminUser → tb_admin_user
className = tablePrefix + getStandardFields(className);
Field[] fields = clz.getDeclaredFields();
StringBuffer column = new StringBuffer();
String varchar = " CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,";
for (Field f : fields) {
if ("class java.lang.String".equals(f.getType().toString())){
column.append(" \n `" + getStandardFields(f.getName()) + "`" + " " + map.get(f.getType().toString())).append(varchar);
}else {
column.append(" \n `" + getStandardFields(f.getName()) + "`" + " " + map.get(f.getType().toString())).append(",");
}
}
//已单独指定id列的生成语句，去掉多余id的拼接
String column1 = column.substring(column.indexOf(",") + 1);
StringBuffer sql = new StringBuffer();
sql.append("\n -- 表 " + className + "\n")
.append("\n DROP TABLE IF EXISTS `" + className + "`; ")
.append(" \n CREATE TABLE `" + className + "` (")
.append(" \n `id` int(11) NOT NULL AUTO_INCREMENT,")
.append(" " + column1)
.append(" \n PRIMARY KEY (`id`) USING BTREE,")
.append("\n INDEX `id`(`id`) USING BTREE")
.append(" \n ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci; \n");
return sql.toString();
} catch (ClassNotFoundException e) {
logger.debug("该类未找到！");
return null;
}
}
/**
* 转换为标准等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);
//非首字母，在大写字母A到Z之间
if (i != 0 && (c >= 'A' && c <= 'Z')) {
sb.append("_");
}
sb.append(c);
}
return sb.toString();
}
/**
* 获取包下面等所有实体类名称，类似于获取 XXX.java
*
* @param packageName 全类名
* @return
*/
public static List<String> getAllClasses(String packageName) {
List<String> classList = new ArrayList();
String className = "";
File f = new File(packageName);
if (f.exists() && f.isDirectory()) {
File[] files = f.listFiles();
// 遍历实体类下面等所有.java文件 获取其类名
for (File file : files) {
className = file.getName();
classList.add(className);
}
return classList;
} else {
logger.debug("包路径未找到！");
return null;
}
}
/**
* 将生成等String字符串 写进sql文件
*
* @param str String字符串
* @param path sql文件路径路径
*/
public static void StringToSql(String str, String path) {
byte[] sourceByte = str.getBytes();
FileOutputStream os = null;
if (null != sourceByte) {
try {
//文件路径（路径+文件名）
File file = new File(path);
//文件不存在则创建文件，先创建目录
if (!file.exists()) {
File dir = new File(file.getParent());
dir.mkdirs();
file.createNewFile();
}
//文件输出流用于将数据写入文件
os = new FileOutputStream(file);
os.write(sourceByte);
os.flush();
System.out.println("生成成功!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭文件输出流
try {
if (os != null) {
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
