package com.wei.test.dao;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
public class Sql {
// 数据库连接
private static final String URL = "jdbc:mysql://127.0.0.1:3306/tb_books";
private static final String NAME = "root";
private static final String PASS = "root";
private static final String DRIVER = "com.mysql.jdbc.Driver";
private String packageOutPath = "com.wei.test.entity";// 指定实体生成所在包的路径
private static String databaseName=URL.substring(URL.lastIndexOf("/")+1,URL.length()); //获得数据库名
private Connection con = null;
private Statement pStemt = null;
private ResultSet rs = null;
private ResultSetMetaData rsmd = null;
private String[] tableNames;
private String[] tablePre = { "tb_" };
private String[] colsPre = { "F_NB_", "F_VC_", "F_CR_", "F_DT_"};
private String[] colnames; // 列名数组
private String[] colTypes; // 列名类型数组
private int[] colSizes; // 列名大小数组
public Sql() {
connect();
getAllTables();
}
/**
* 1、创建数据库连接
*/
private void connect() {
try {
Class.forName(DRIVER);
con = DriverManager.getConnection(URL, NAME, PASS);
pStemt = (Statement) con.createStatement();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
private void colseConnect() {
try {
if (con != null) {
con.close();
con = null;
}
if (pStemt != null) {
pStemt.close();
pStemt = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 获取所有表名
*/
private void getAllTables() {
String sql = "select table_name from information_schema.tables where table_schema='"+databaseName+"' and table_type='base table'";
try {
rs = pStemt.executeQuery(sql);
String s = "";
while (rs.next()) {
s = s + rs.getString("TABLE_NAME") + ",";
}
tableNames = s.substring(0,s.length()-1).split(",");
for (String tableName : tableNames) {
getTableInfo(tableName);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
colseConnect();
}
}
/**
* 获取单个数据库表信息
*/
private void getTableInfo(String tableName) {
boolean f_sql = false; //是否需要导入包import java.sql.Timestamp;
int size = 0;
String sql = "SELECT * FROM " + tableName;
try {
rs = pStemt.executeQuery(sql);
rsmd = rs.getMetaData();
size = rsmd.getColumnCount();
colnames = new String[size];
colTypes = new String[size];
colSizes = new int[size];
for (int i = 0; i < size; i++) {
colnames[i] = rsmd.getColumnName(i + 1); //数据库所有的表名
colTypes[i] = rsmd.getColumnTypeName(i + 1); //数据库所有的数据类型
if (colTypes[i].equalsIgnoreCase("date")
|| colTypes[i].equalsIgnoreCase("timestamp")) {
f_sql= true;
}
colSizes[i] = rsmd.getColumnDisplaySize(i + 1);
}
//规范化表名
String normTableName = normTableName(tableName);//获取数据库的列名 去除tb_ 将首字母转换为大写
//获取单张数据库表注释
String tableComment = getTableComment(tableName);
//获取单张数据库表的所有列信息
StringBuffer tempSb = getColsInfo(tableName,normTableName);
//生成JavaBean文件
genFile(normTableName, getSb(normTableName, tableComment, tempSb,f_sql));
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 生成JavaBean文件
*
* @param content
*/
private void genFile(String tableName, StringBuffer content) {
try {
File directory = new File("");
String outputPath = directory.getAbsolutePath() + "/src/"
+ this.packageOutPath.replace(".", "/") + "/" + tableName
+ ".java";
FileWriter fw = new FileWriter(outputPath);
PrintWriter pw = new PrintWriter(fw);
pw.println(content);
pw.flush();
pw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取单张数据库表的所有列信息
* 1.获取所有的列名
* 2.获取列类型
* 3.获取列注释
* @param tableName tb_表名
* @param normTableName 规范后的表名
*/
private StringBuffer getColsInfo(String tableName,String normTableName) {
StringBuffer temp = new StringBuffer();
for (int i = 0; i < colnames.length; i++) {
temp.append("\tprivate " + getColsType(colTypes[i]) + " "
+ getColsName(colnames[i]) + "; //"
+ getColComment(tableName, colnames[i]) + "\r\n");
}
for (int i = 0; i < colnames.length; i++) { //实体类中的set/get方法
String colname = getColsName(colnames[i]);
String colnameUp = captureName(colname);
temp.append("\n\tpublic void set" + colnameUp + "("
+ getColsType(colTypes[i]) + " " + colname + "){\r\n");
temp.append("\t\tthis." + colname + "=" + colname + ";\r\n");
temp.append("\t}\r\n");
temp.append("\n\tpublic " + getColsType(colTypes[i]) + " get"
+ colnameUp + "(){\r\n");
temp.append("\t\treturn " + colname + ";\r\n");
temp.append("\t}\r\n");
}
//有参构造方法
temp.append("\n\tpublic " + normTableName + "(");
String str="";
for (int i = 0; i < colnames.length; i++) {
str += (getColsType(colTypes[i])+" "+getColsName(colnames[i])+",");
}
temp.append(str.substring(0, str.length()-1)+") {\n");
temp.append("\t\tsuper();\n");
for (int i = 0; i < colnames.length; i++) {
temp.append("\t\tthis." + getColsName(colnames[i]) + "=" + getColsName(colnames[i]) + ";\r\n");
}
temp.append("\t}\r\n");
//无参构造方法
temp.append("\n\tpublic " + normTableName + "() {");
temp.append("\t\r\n\t\tsuper();\n \t}\r\n");
//toString方法
temp.append("\r\t@Override\r\n");
temp.append("\tpublic String toString() {\r\n");
String tostring = "";
for (int i = 0; i < colnames.length; i++) {
tostring+=(getColsName(colnames[i]) + "=\"+"+ getColsName(colnames[i]) +"+\",");
}
temp.append("\t\treturn \""+tableName+" ["+tostring.substring(0, tostring.length()-1)+"]\";");
temp.append("\r\t}\r\n");
return temp;
}
/**
* 构建StringBuffer缓存
*
* @param tableName
* @param tableComment
* @param colSb
* @return
*/
private StringBuffer getSb(String tableName, String tableComment,StringBuffer colSb,Boolean f_sql) {
StringBuffer sb = new StringBuffer();
sb.append("package " + this.packageOutPath + ";\r\n");
// 判断是否导入工具包
if (f_sql) {
sb.append("\nimport java.sql.Timestamp;\r\n");
}
// 注释部分
sb.append(" /**\r\n");
sb.append(" * " + new Date() + "\r\n");
sb.append(" */ ");
// 实体部分
sb.append("\npublic class " + tableName + "{\r\n");
sb.append(colSb);
sb.append("\n}\r\n");
return sb;
}
/**
* 获取单张数据库表注释
*
* @param tableName
* @return
*/
private String getTableComment(String tableName) {
String str = "";
String sql = "select column_comment from INFORMATION_SCHEMA.Columns where"
+ " table_name='"+tableName+"' and table_schema='"+databaseName+"'";
try {
rs = pStemt.executeQuery(sql);
while (rs.next()) {
str = rs.getString("column_comment");
if (null != str && str.indexOf("\r\n") != -1) {
str = str.replace("\r\n", "");
}
if (null != str && str.indexOf("\n") != -1) {
str = str.replace("\n", "");
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return str;
}
/**
* 获取列名
*
* @param str
* @return
*/
private String getColsName(String str) {
for (String temp : colsPre) {
int preIndex = str.indexOf(temp);
if (preIndex >= 0) {
str = str.substring(preIndex + temp.length());
str = str.replace("_", "").toLowerCase();
}
}
return str;
}
/**
* 获取列类型
*
* @param sqlType
* @return
*/
private String getColsType(String sqlType) {
if (sqlType.equalsIgnoreCase("double")) {
return "double";
} else if (sqlType.equalsIgnoreCase("float")) {
return "double";
} else if (sqlType.equalsIgnoreCase("blob")) {
return "byte[]";
} else if (sqlType.equalsIgnoreCase("blob")) {
return "byte[]";
} else if (sqlType.equalsIgnoreCase("char")
|| sqlType.equalsIgnoreCase("varchar2")
|| sqlType.equalsIgnoreCase("varchar")) {
return "String";
} else if (sqlType.equalsIgnoreCase("date")
|| sqlType.equalsIgnoreCase("timestamp")
|| sqlType.equalsIgnoreCase("timestamp with local time zone")
|| sqlType.equalsIgnoreCase("timestamp with time zone")) {
return "Timestamp";
} else if (sqlType.equalsIgnoreCase("int")) {
return "int";
}
return "String";
}
/**
* 获取列注释
*
* @param tableName
* @param columnName
* @return
*/
private String getColComment(String tableName, String columnName) {
String str = "";
String sql = "select column_comment from INFORMATION_SCHEMA.Columns where"
+ " table_name='"+tableName+"' and table_schema='"+databaseName+"' and COLUMN_NAME = '"+columnName+"'";
try {
rs = pStemt.executeQuery(sql);
while (rs.next()) {
str = rs.getString("column_comment");
if (null != str && str.indexOf("\r\n") != -1) {
str = str.replace("\r\n", "");
}
if (null != str && str.indexOf("\n") != -1) {
str = str.replace("\n", "");
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return str;
}
/**
* 规范类名
*
* @param tableName
* @return
*/
public String normTableName(String tableName) {
String result = "";
for (String temp : tablePre) {
int index = tableName.indexOf(temp.toLowerCase());//tb_在字符串中首次出现的位置
if (index >= 0) {
tableName = tableName.substring(index + temp.length()); //截取tb_
String[] names = tableName.split("_"); //去除_
if (null != names && names.length > 0) {
for (String name : names) {
result += captureName(name.toLowerCase());
}
}
}
}
return result;
}
/**
* 首字母大写
* @param name
* @return
*/
public static String captureName(String name) {
char[] cs = name.toCharArray();
cs[0] -= 32;
return String.valueOf(cs);
}
/**
* 出口 TODO
*
* @param args
*/
public static void main(String[] args) {
new Sql();
System.out.println(“ok”);
}
}