package com.zd.db;
import lombok.extern.slf4j.Slf4j;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
@Slf4j
public class JdbcUtil {
/**
* 获取字段相关类:
* 使用完记得关闭连接
*/
public static ResultSetMetaData getResultSetMetaData(String tableName) {
PreparedStatement preparedStatement = null;
ResultSetMetaData resultSetMetaData = null;
Connection connection = getConnection();
try {
preparedStatement = connection.prepareStatement("select * from " + tableName + " where 1=2");
resultSetMetaData = preparedStatement.executeQuery().getMetaData();
} catch (Exception e) {
log.error("数据库连接异常", e);
} finally {
closePreparedStatement(preparedStatement);
closeConnection(connection);
}
return resultSetMetaData;
}
/**
* 创建表的新增记录触发器语句:
* 使用完记得关闭连接
*/
public static String getInsertTriggerSql(String tableName) {
PreparedStatement preparedStatement = null;
ResultSetMetaData resultSetMetaData = null;
Connection connection = getConnection();
String triggerColumns = "";
String triggerOldValues = "";
String triggerSql = "";
try {
preparedStatement = connection.prepareStatement("select * from " + tableName + " where 1=2");
resultSetMetaData = preparedStatement.executeQuery().getMetaData();
int columnCount = resultSetMetaData.getColumnCount();
//System.out.println(tableName + "表中字段个数为：" + columnCount);
for (int i = 1; i <= columnCount; i++) {
String columnName = resultSetMetaData.getColumnName(i);
triggerColumns += columnName + ",";
triggerOldValues += "NEW." + columnName + ",";
}
triggerColumns = triggerColumns.substring(0, triggerColumns.length() - 1);
triggerOldValues = triggerOldValues.substring(0, triggerOldValues.length() - 1);
triggerSql = "create trigger " + tableName + "_bak " + "after insert on " + tableName + " for each row"
+ " begin"
+ " insert into " + tableName + "_history (" + triggerColumns + ") values (" + triggerOldValues + ");"
+ " end";
} catch (Exception e) {
log.error("数据库连接异常", e);
} finally {
closePreparedStatement(preparedStatement);
closeConnection(connection);
}
return triggerSql;
}
/**
* 获取当前数据库下的所有表名称
*/
public static List<String> getAllTableName() {
List<String> tables = new ArrayList();
Connection connection = getConnection();
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery("SHOW TABLES ");
while (resultSet.next()) {
String tableName = resultSet.getString(1);
tables.add(tableName);
}
} catch (SQLException e) {
log.error("数据库连接异常", e);
} finally {
closeResultSet(resultSet);
closeStatement(statement);
closeConnection(connection);
}
return tables;
}
/**
* 获得某表的建表语句
*/
public static String getCreateTableDDL(String tableName) {
Connection connection = getConnection();
Statement statement = null;
ResultSet resultSet = null;
String createDDLSql = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery("SHOW CREATE TABLE " + tableName);
if (resultSet != null && resultSet.next()) {
createDDLSql = resultSet.getString(2);
}
} catch (SQLException e) {
log.error("数据库连接异常", e);
} finally {
closeResultSet(resultSet);
closeStatement(statement);
closeConnection(connection);
}
return createDDLSql;
}
public static String getCreateTableHistoryDDL(String sourceCreateTableDDL, String sourceTableName, String targetTableName) {
return sourceCreateTableDDL.replaceFirst(sourceTableName, targetTableName);
}
public static String getAlterColumnCreateTableDDl(String sourceCreateTableDDL, String addColumnDefine) {
return null;
}
public static String getAddColumnCreateTableDDl(String sourceCreateTableDDL, String addColumnDefine) {
StringBuilder sb = new StringBuilder();
sb.append(sourceCreateTableDDL).insert(sourceCreateTableDDL.indexOf("(")+1, addColumnDefine);
return sb.toString();
}
/**
* 获得某表的注释
*/
public static String getTableCommnet(String tableName) {
String creatDDLSql = getCreateTableDDL(tableName);
String comment = null;
int index = creatDDLSql.indexOf("COMMENT='");
if (index < 0) {
return "";
}
comment = creatDDLSql.substring(index + 9);
comment = comment.substring(0, comment.length() - 1);
return comment;
}
/**
* 获取表中字段的所有注释
*/
public static List<String> getColumnComments(String tableName) {
//与数据库的连接
Connection connection = getConnection();
PreparedStatement preparedStatement = null;
String tableSql = "select * from " + tableName;
//列名注释集合
List<String> columnComments = new ArrayList<>();
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(tableSql);
resultSet = preparedStatement.executeQuery("show full columns from " + tableName);
while (resultSet.next()) {
columnComments.add(resultSet.getString("Comment"));
}
} catch (SQLException e) {
log.error("数据库连接异常", e);
} finally {
closeResultSet(resultSet);
closeStatement(preparedStatement);
closeConnection(connection);
}
return columnComments;
}
/**
* 获取数据连接
*/
public static Connection getConnection() {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/testdb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true", "root", "root");
} catch (Exception e) {
log.error("get connection failure", e);
}
return connection;
}
/**
* 关闭数据库连接
*/
public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
log.error("close connection failure", e);
}
}
}
public static void closePreparedStatement(PreparedStatement preparedStatement) {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
log.error("close preparedStatement failure ", e);
}
}
}
public static void closeResultSet(ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
log.error("close resultSet failure ", e);
}
}
}
public static void closeStatement(Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
log.error("close statement failure ", e);
}
}
}
public static void main(String[] args) throws Exception{
//System.out.println("获取所有数据库的表名" + getAllTableName());
String tableName = "orders";
String createTableDDL = getCreateTableDDL(tableName);
System.out.println("获取建表语句：" + createTableDDL);
String createTableHistoryDDL = getCreateTableHistoryDDL(createTableDDL, tableName, tableName + "_history");
System.out.println("获取创建history表语句：" + createTableHistoryDDL);
String addColumnCreateTableDDl = getAddColumnCreateTableDDl(createTableHistoryDDL.replaceFirst("AUTO_INCREMENT", "").replace("PRIMARY KEY","KEY `idx_key`"), "`rowId` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,");
System.out.println("新增字段创建表ddl：" + addColumnCreateTableDDl);
String insertTriggerSql = getInsertTriggerSql(tableName);
System.out.println("获取表的insert触发器：" + insertTriggerSql);
//System.out.println("获取表的备注：" + getTableCommnet(tableName));
/*ResultSetMetaData resultSetMetaData = getResultSetMetaData(tableName);
int columnCount = resultSetMetaData.getColumnCount();
System.out.println(tableName + "表中字段个数为：" + columnCount);
System.out.println("打印字段信息：");
List<String> columnComments = getColumnComments(tableName);
for (int i = 1; i <= columnCount; i++) {
String columnClassName = resultSetMetaData.getColumnClassName(i);
System.out.print("java类型：" + columnClassName);
String columnTypeName = resultSetMetaData.getColumnTypeName(i).toLowerCase();
System.out.print(" 数据库类型:" + columnTypeName);
String columnName = resultSetMetaData.getColumnName(i);
System.out.print(" 字段名称:" + columnName);
int columnDisplaySize = resultSetMetaData.getColumnDisplaySize(i);
System.out.print(" 字段长度:" + columnDisplaySize);
System.out.print(" notNull：" + (resultSetMetaData.isNullable(i) == 1 ? false : true));
String columnComment = columnComments.get(i - 1);
System.out.println(" 注释为：" + columnComment);
}*/
}
}
获取建表语句：CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ordertime` varchar(255) DEFAULT NULL,
`total` double(5,2) DEFAULT NULL,
`uid` int(11) NOT NULL DEFAULT '0',
`des` varchar(80) DEFAULT '' COMMENT '描述',
`data` varchar(10) CHARACTER SET utf8mb4 DEFAULT 'aaaa',
`createdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
`updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COMMENT='ZZZZZZ'
获取创建history表语句：CREATE TABLE `orders_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ordertime` varchar(255) DEFAULT NULL,
`total` double(5,2) DEFAULT NULL,
`uid` int(11) NOT NULL DEFAULT '0',
`des` varchar(80) DEFAULT '' COMMENT '描述',
`data` varchar(10) CHARACTER SET utf8mb4 DEFAULT 'aaaa',
`createdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
`updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COMMENT='ZZZZZZ'
新增字段创建表ddl:CREATE TABLE `orders_history` (`rowId` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`id` int(11) NOT NULL ,
`ordertime` varchar(255) DEFAULT NULL,
`total` double(5,2) DEFAULT NULL,
`uid` int(11) NOT NULL DEFAULT '0',
`des` varchar(80) DEFAULT '' COMMENT '描述',
`data` varchar(10) CHARACTER SET utf8mb4 DEFAULT 'aaaa',
`createdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
`updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY `idx_key` (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COMMENT='ZZZZZZ'
获取表的insert触发器：
create trigger orders_bak after insert on orders for each row begin insert into orders_history (id,ordertime,total,uid,des,data,createdate,updatetime) values (NEW.id,NEW.ordertime,NEW.total,NEW.uid,NEW.des,NEW.data,NEW.createdate,NEW.updatetime); end
打印字段信息：
orders表中字段个数为：8
Process finished with exit code 0
