import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.ijwt.element.Constants;
/**
* @author InJavaWeTrust
*/
public class UtilDB {
private static Connection conn = null;
private static Statement st = null;
private static ResultSet rs = null;
private static DatabaseMetaData dmd = null;
private static Logger log = LoggerFactory.getLogger(UtilDB.class);
private UtilDB() {}
/**
* 连接数据库
* @return
*/
public static Connection connection() {
try {
Class.forName(Constants.DRIVER);
conn = DriverManager.getConnection(Constants.URL, Constants.USER, Constants.PASSWORD);
} catch (ClassNotFoundException | SQLException e) {
log.error(e.getMessage());
}
return conn;
}
/**
* 关闭链接
* @param rs
* @param st
* @param conn
*/
public static void release(ResultSet rs, Statement st, Connection conn) {
try {
if (null != rs) {
rs.close();
}
} catch (Exception e) {
log.error(e.getMessage());
}
try {
if (null != st) {
st.close();
}
} catch (Exception e) {
log.error(e.getMessage());
}
try {
if (null != conn) {
conn.close();
}
} catch (Exception e) {
log.error(e.getMessage());
}
}
/**
* 根据表名查询表中列注释
* @param tableName 表名
* @return List<String>
*/
public static List<String> getColumnCommentByTableName(String tableName) {
List<String> columnComments = new ArrayList<>();
connection();
try {
dmd = conn.getMetaData();
rs = dmd.getColumns(null, dmd.getUserName(), tableName, null);
while (rs.next()) {
columnComments.add(rs.getString("REMARKS"));
}
} catch (SQLException e) {
log.error(e.getMessage());
}
release(rs, st, conn);
return columnComments;
}
/**
* 根据表名查询表中列名
* @param tableName 表名
* @return List<String>
*/
public static List<String> getColumnNameByTableName(String tableName) {
List<String> columnComments = new ArrayList<>();
connection();
try {
dmd = conn.getMetaData();
rs = dmd.getColumns(null, dmd.getUserName(), tableName, null);
while (rs.next()) {
columnComments.add(rs.getString("COLUMN_NAME"));
}
} catch (SQLException e) {
log.error(e.getMessage());
}
release(rs, st, conn);
return columnComments;
}
/**
* 根据表名查询表中列类型
* @param tableName 表名
* @return List<String>
*/
public static List<String> getColumnTypeByTableName(String tableName) {
List<String> tableNames = new ArrayList<>();
connection();
try {
dmd = conn.getMetaData();
rs = dmd.getColumns(null, dmd.getUserName(), tableName, null);
while (rs.next()) {
tableNames.add(rs.getString("TYPE_NAME"));
}
} catch (SQLException e) {
log.error(e.getMessage());
}
release(rs, st, conn);
return tableNames;
}
/**
* 得到数据库中所有表名
* @return List<String>
*/
public static List<String> getAllTableName() {
List<String> tableNames = new ArrayList<>();
connection();
try {
dmd = conn.getMetaData();
rs = dmd.getTables(null, dmd.getUserName(), null, null);
while (rs.next()) {
tableNames.add(rs.getString("TABLE_NAME"));
}
} catch (SQLException e) {
log.error(e.getMessage());
}
release(rs, st, conn);
return tableNames;
}
/**
* 得到数据库表注释
* @param tableName 表名
* @return 表注释
*/
public static String getTableCommentByTableName(String tableName) {
String comment = "";
connection();
PreparedStatement pstmt = null;
try {
String sql = "SHOW TABLE STATUS WHERE NAME = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, tableName);
rs = pstmt.executeQuery();
boolean has = rs.next();
if(has) {
comment = rs.getString("Comment");
}
} catch (SQLException e) {
log.error(e.getMessage());
} finally {
try {
if (null != pstmt) {
pstmt.close();
}
} catch (SQLException e) {
log.error(e.getMessage());
}
}
release(rs, st, conn);
return comment;
}
public static void main(String[] args) {
String list = getTableCommentByTableName("dept");
System.out.println(list);
}
}