代码测试类
package org.jeecg;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* @Author Andy
* @Date 2021/3/3
*/
public class SqlCommentTest {
//---------------------------------------读取数据-------------------------------------
public static void main(String[] args) throws Exception {
Connection con = null;// 创建一个数据库连接
PreparedStatement pre = null;// 创建预编译语句对象,一般都是用这个而不用Statement
PreparedStatement pre1 = null;// 创建预编译语句对象,一般都是用这个而不用Statement
ResultSet result = null;// 创建一个结果集对象
ResultSet result1 = null;// 创建一个表名结果集对象
try {
Class.forName("com.mysql.jdbc.Driver");// 加载mysql驱动程序
System.out.println("开始尝试连接mydatabase数据库!");
String url =
"jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true";
String user = "root";// 用户名,系统默认的账户名
String password = "123456";// 你安装时选设置的密码
con = DriverManager.getConnection(url, user, password);
//con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root","123456");// 获取连接
System.out.println("输入数据库--mydatabase--连接成功!");
//--------------------查看表名-----------------------------------------
// ****1改库名*****---------
String sql1 =
"select table_name,table_comment from information_schema.tables where table_schema='" + "数据库名"
//***********************1改输入库名***************************
+ "'";
pre1 = con.prepareStatement(sql1);
result1 = pre1.executeQuery();// 执行查询,注意括号中不需要再加参数
//--------------------查看列名/类型/注释--------------------------------------------------
while (result1.next()) {
System.out.println("------------进入库中表:" + result1.getString("table_name"));
System.out.println("------------表注释:" + result1.getString("table_comment"));
//---------------------------mysql输入所需字段------------------------------
/*
String sql = "select column_name,column_comment,column_type from information_schema.columns where table_schema='mydatabase' and table_name='"
+ result1.getString("table_name")
+ "'";// 预编译语句
*/
//---------------------------postgresql输入所需字段------------------------------
String sql = "select column_name,column_comment from information_schema.columns where table_schema='"
+ "数据库名"
//**********************2改输入库名************************
+ "' and table_name='" + result1.getString("table_name") + "'";// 预编译语句
pre = con.prepareStatement(sql);// 实例化预编译语句
result = pre.executeQuery();// 执行查询,注意括号中不需要再加参数
System.out.println("结果集result=" + result + "结果集result=" + result1);
// -----------------------处理同一个result1,请分开执行-----------------------------------
//更新表注释
// updateTableComment(result1);
//更新表字段注释
updateTableColumnComment(result1, result);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// 逐一将上面的几个对象关闭,因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序,最后使用的最先关闭
if (result1 != null)
result1.close();
if (result != null)
result.close();
if (pre1 != null)
pre1.close();
if (pre != null)
pre.close();
if (con != null)
con.close();
System.out.println("输入数据库--mydatabase--数据库连接已关闭!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static Connection connectPostGreGreSql() throws Exception {
Class.forName("org.postgresql.Driver");// 加载postgresql驱动程序
System.out.println("开始尝试连接test数据库!");
String url =
"jdbc:postgresql://localhost:5432/mydatabase?useUnicode=true&characterEncoding=utf8¤tSchema=public";
String user = "postgres";// 用户名,系统默认的账户名
String password = "postgres";// 你安装时选设置的密码
Connection con = DriverManager.getConnection(url, user, password);// 获取连接
System.out.println("dw连接成功!");
return con;
}
//-------------------------------------------更新表注释数据-------------------------------------
public static String updateTableComment(ResultSet result1) {
Connection con = null;// 创建一个数据库连接
PreparedStatement preComment = null;// 创建预编译语句对象,一般都是用这个而不用Statement
try {
//--------------------mysql-------------------
/*Class.forName("com.mysql.jdbc.Driver");// 加载mysql驱动程序
System.out.println("开始尝试连接testdb数据库!");
String url = "jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=utf8";
String user = "root";// 用户名,系统默认的账户名
String password = "123456";// 你安装时选设置的密码
con = DriverManager.getConnection(url, user, password);// 获取连接
System.out.println("testdb连接成功!");*/
//-------------------postgresql---------------
con = connectPostGreGreSql();
int num = 0;//统计表注释个数
// 更新表注释
while (result1.next()) {
String tableName = result1.getString("table_name");
String tableComment = result1.getString("table_comment");
System.out.println("表名:" + tableName + "表注释:" + tableComment);
ResultSet rs = con.getMetaData().getTables(null, null, tableName, null);
//判断库中是否存在某表
if (rs.next()) {
// -------------------------更新表mysql注释sql语句--------------------------
// alter table '表名' comment '备注信息';;
// String sqlComment = " alter table "+tableName + " comment " +tableComment;
// -------------------------更新表postgresql注释sql语句--------------------------
// comment on table '表名' is '备注信息';
String sqlComment = " comment on table " + tableName + " is '" + tableComment + "'";
preComment = con.prepareStatement(sqlComment);// 实例化预编译语句
preComment.execute();
num = num + 1;
System.out.println("更新【" + tableName + "】表名,注释【" + tableComment + "】成功第" + (num) + "条数据");
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// 逐一将上面的几个对象关闭,因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序,最后使用的最先关闭
if (preComment != null)
preComment.close();
if (con != null)
con.close();
System.out.println("dw数据库连接已关闭!");
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//-------------------------------------------更新表字段注释数据-------------------------------------
public static String updateTableColumnComment(ResultSet result1, ResultSet result) {
Connection con = null;// 创建一个数据库连接
PreparedStatement pre = null;// 创建预编译语句对象,一般都是用这个而不用Statement
try {
//--------------------mysql-------------------
/*Class.forName("com.mysql.jdbc.Driver");// 加载mysql驱动程序
System.out.println("开始尝试连接testdb数据库!");
String url = "jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf8";
String user = "root";// 用户名,系统默认的账户名
String password = "123456";// 你安装时选设置的密码
con = DriverManager.getConnection(url, user, password);// 获取连接
System.out.println("testdb连接成功!");*/
//-------------------postgresql---------------
con = connectPostGreGreSql();
int num = 0;//统计数据个数
// 更新表字段注释
while (result.next()) {
String tableName = result1.getString("table_name");
String columnName = result.getString("column_name");
String columnComment = result.getString("column_comment");
System.out.println("表名:【" + tableName + "】字段名:【" + columnName + "】,字段注释:【" + columnComment + "】");
//-------------------------更新mysql库中表注释-------------------------
/* String sql = "ALTER TABLE "
+ result1.getString("table_name")
+ " MODIFY COLUMN "
+result.getString("column_name")
+" "
+result.getString("column_type")
+" COMMENT "
+"'" +result.getString("column_comment") +"'"; // 预编译语句
*/
//--------------------更新postgresql库中表注释------------------
//comment on column test.test.name is 'hahaname';
ResultSet rs = con.getMetaData().getTables(null, null, tableName, null);
//判断库中是否存在某表
if (rs.next()) {
//修改对应表的字段注释
String sql = "comment on column " + tableName + "." + columnName + " is '" + columnComment + "'";
pre = con.prepareStatement(sql);// 实例化预编译语句
pre.execute();
pre.close();
num = num + 1;
System.out.println("更新【" + tableName + "】表字段,成功第【" + (num) + "】条数据");
//==========================日志管理==============================
/* Timestamp currentTime = new Timestamp(System.currentTimeMillis());
System.out.println("==========================" + currentTime);
String sql1 = "insert into test.message values('" + result1.getString("table_name") + "','" + result
.getString("column_name") + "','" + result.getString("column_comment") + "','" + currentTime
+ "')";
pre1 = con.prepareStatement(sql1);// 实例化预编译语句
pre1.execute();
pre1.close();*/
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// 逐一将上面的几个对象关闭,因为不关闭的话会影响性能、并且占用资源
// 注意关闭的顺序,最后使用的最先关闭
if (result != null)
result.close();
if (pre != null)
pre.close();
if (con != null)
con.close();
System.out.println("dw数据库连接已关闭!");
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
}