封装了一个工具类,用于JDBC操作MySql数据库,获取某个数据库下的所有表名,某个表中的所有字段名称、字段类型、字段注解、动态建表、动态插入、动态删除等功能
使用坏境
- JDK: 1.8 or 1.7
- Maven: 3.x.x
使用步骤
1. Maven项目或是Spring Boot项目在pom.xml文件中引入mysql-connector-java.jar
这个依赖包,不是的需要去下载这个包加入项目中
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
2.复制代码到新建好的类中
DataBaseUtil类
import java.sql.*;
import java.util.*;
/**
* 自制操作连接数据库工具类
*
* @Author: ChenBin
* @Date: 2018/4/20/0020 15:46
*/
public class DataBaseUtil {
private static final Logger logger = LoggerFactory.getLogger(DataBaseUtil.class);
/**
* 获取数据库下的所有表名
*
* @param dbConfig 数据库配置对象
* @return List集合
*/
public static List<String> getTableNames(DbConfig dbConfig) {
List<String> data = null;
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection(dbConfig);
statement = connection.createStatement();
resultSet = statement.executeQuery("SHOW TABLES");
data = resultSetToList(resultSet);
logger.info("查询成功" + data);
} catch (SQLException e) {
throw new RuntimeException("获取数据库表名失败:" + e.getMessage());
} finally {
closeConnection(connection, resultSet, statement);
}
return data;
}
/**
* 获取数据库下的所有表字段名称
*
* @param dbConfig 数据库配置对象
* @return List集合
*/
public static List<String> getColumnNames(DbConfig dbConfig) {
List<String> columnNames = new ArrayList<>();
Connection connection = getConnection(dbConfig);
PreparedStatement statement = null;
String sql = "SELECT * FROM " + dbConfig.getTableName();
try {
statement = connection.prepareStatement(sql);
//结果集元数据
ResultSetMetaData metaData = statement.getMetaData();
//表列数
int size = metaData.getColumnCount();
for (int i = 0; i < size; i++) {
columnNames.add(metaData.getColumnName(i + 1));
}
logger.info("查询成功" + columnNames);
} catch (SQLException e) {
throw new RuntimeException("获取数据库表字段失败:" + e.getMessage());
} finally {
closeConnection(connection, null, statement);
}
return columnNames;
}
/**
* 获取数据库下的所有表字段类型名称
*
* @param dbConfig 数据库配置对象
* @return List集合
*/
public static List<String> getColumnTypes(DbConfig dbConfig) {
List<String> columnTypes = new ArrayList<>();
Connection connection = getConnection(dbConfig);
PreparedStatement statement = null;
String sql = "SELECT * FROM " + dbConfig.getTableName();
try {
statement = connection.prepareStatement(sql);
//结果集元数据
ResultSetMetaData metaData = statement.getMetaData();
//表列数
int size = metaData.getColumnCount();
for (int i = 0; i < size; i++) {
columnTypes.add(metaData.getColumnTypeName(i + 1));
}
logger.info("查询成功", columnTypes);
} catch (SQLException e) {
throw new RuntimeException("获取数据库表字段类型失败:" + e.getMessage());
} finally {
closeConnection(connection, null, statement);
}
return columnTypes;
}
/**
* 获取数据库下的所有表字段注释
*
* @param dbConfig 数据库配置对象
* @return List集合
*/
public static List<String> getColumnComments(DbConfig dbConfig) {
List<String> columnComments = new ArrayList<>();
Connection connection = getConnection(dbConfig);
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery("SHOW FULL COLUMNS FROM " + dbConfig.getTableName());
while (resultSet.next()) {
columnComments.add(resultSet.getString("Comment"));
}
logger.info("查询成功" + columnComments);
} catch (SQLException e) {
throw new RuntimeException("获取数据库表字段注释失败[" + e.getMessage());
} finally {
closeConnection(connection, resultSet, statement);
}
return columnComments;
}
/**
* 新建数据表
*
* @param dbConfig
* @param list 存放要添加的字段名称
*/
public static void createTable(DbConfig dbConfig, List<String> list) {
Connection connection = getConnection(dbConfig);
Statement statement = null;
ResultSet resultSet = null;
StringBuilder sql = new StringBuilder();
sql.append("CREATE TABLE IF NOT EXISTS ").append(dbConfig.getTableName())
.append(" (").append("id VARCHAR(32) NOT NULL,");
if (list != null) {
for (String s : list) {
sql.append(s).append(" VARCHAR(255),");
}
}
sql.append(" PRIMARY KEY (id)").append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
try {
statement = connection.createStatement();
statement.executeUpdate(sql.toString());
logger.info("创建成功");
} catch (SQLException e) {
throw new RuntimeException("创建表失败:" + e.getMessage());
} finally {
closeConnection(connection, resultSet, statement);
}
}
/**
* 数据库插入
*
* @param dbConfig 数据库配置对象
* @param hashMap key存放着要插入的字段,value存放着插入字段的值
* @param idType 如果是UUID就输入uuid
* @return 返回主键
*/
public static Object insert(DbConfig dbConfig, HashMap<String, String> hashMap, String idType) {
Connection connection = getConnection(dbConfig);
PreparedStatement statement = null;
ResultSet resultSet = null;
StringBuilder sql = new StringBuilder();
Iterator iterator = hashMap.keySet().iterator();
String key;
Object object = null;
sql.append("INSERT INTO ").append(dbConfig.getTableName())
.append(" (");
String type = "uuid";
if (type.equalsIgnoreCase(idType)) {
sql.append("id");
while (iterator.hasNext()) {
key = (String) iterator.next();
sql.append(",").append(key);
}
sql.append(" )").append("VALUES(").append("REPLACE(UUID(),\"-\",\"\") ");
iterator = hashMap.keySet().iterator();
while (iterator.hasNext()) {
key = (String) iterator.next();
sql.append(",").append("'").append(hashMap.get(key)).append("'");
}
sql.append(")");
} else {
while (iterator.hasNext()) {
key = (String) iterator.next();
sql.append(key).append(",");
}
sql.deleteCharAt(sql.lastIndexOf(","));
sql.append(" )").append("VALUES(");
iterator = hashMap.keySet().iterator();
while (iterator.hasNext()) {
key = (String) iterator.next();
sql.append("'").append(hashMap.get(key)).append("'").append(",");
}
sql.deleteCharAt(sql.lastIndexOf(","));
sql.append(")");
}
try {
statement = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate();
resultSet = statement.getGeneratedKeys();
if (resultSet.next()) {
object = resultSet.getObject(1);
}
System.out.println("成功插入数据");
} catch (SQLException e) {
throw new RuntimeException("插入数据失败:[" + e.getMessage() + "]");
} finally {
closeConnection(connection, resultSet, statement);
}
return object;
}
/**
* 预处理方式往数据库中插入数据
*
* @return
*/
public static Object insertPrepare(Connection connection, String sql, Map<String, String> keyAndValue) throws SQLException {
Object object = null;
PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
int i = 0;
for (String key : keyAndValue.keySet()) {
i++;
statement.setObject(i, keyAndValue.get(key));
}
statement.executeUpdate();
ResultSet resultSet = statement.getGeneratedKeys();
if (resultSet.next()) {
object = resultSet.getObject(1);
}
return object;
}
/**
* 往数据库中插入数据
*
* @return
*/
public static void update(Connection connection, String sql) throws SQLException {
Object object = null;
PreparedStatement statement = connection.prepareStatement(sql);
statement.executeUpdate();
}
/**
* 预处理方式查询数据库
*
* @return
*/
public static Object selectPrepare(Connection connection, String sql, Map<String, String> keyAndValue, String keyName) throws SQLException {
if (keyAndValue.isEmpty()) {
return null;
}
PreparedStatement statement = connection.prepareStatement(sql);
int i = 0;
for (String key : keyAndValue.keySet()) {
i++;
statement.setObject(i, keyAndValue.get(key));
}
ResultSet resultSet = statement.executeQuery();
return resultSet.next() ? resultSet.getString(keyName) : null;
}
/**
* 删除指定表
*
* @param dbConfig 数据库配置对象
*/
public static void deleteTable(DbConfig dbConfig) {
Connection connection = getConnection(dbConfig);
String sql = "DROP TABLE " + dbConfig.getTableName();
Statement statement = null;
try {
statement = connection.createStatement();
statement.executeUpdate(sql);
} catch (Exception e) {
throw new RuntimeException("删除表失败:" + e.getMessage());
} finally {
closeConnection(connection, null, statement);
}
}
/**
* 获取所有不能为null的字段
*
* @param dbConfig
* @return
*/
public static List<String> getNotNUllField(DbConfig dbConfig) {
Connection connection = getConnection(dbConfig);
List<String> fields = new ArrayList<>();
ResultSet rs = null;
try {
DatabaseMetaData dbmd = connection.getMetaData();
rs = dbmd.getColumns(null, "%", dbConfig.getTableName(), "%");
String flag;
while (rs.next()) {
flag = rs.getString("IS_NULLABLE");
if ("NO".equals(flag)) {
fields.add(rs.getString("COLUMN_NAME"));
}
}
} catch (SQLException e) {
throw new RuntimeException("获取非空字段失败:" + e.getMessage());
} finally {
closeConnection(connection, rs, null);
}
return fields;
}
/**
* 获取表名称、表主键类型以及表注释
* 可指定表名查询,不指定返回所有表
* @param dbConfig
* @return
*/
public static List<Map<String, Object>> getTableComment(DbConfig dbConfig) {
Connection connection = getConnection(dbConfig);
Statement statement = null;
ResultSet resultSet = null;
List<Map<String, Object>> tableComment = new ArrayList<>();
String tableName = dbConfig.getTableName();
StringBuilder sql = new StringBuilder();
sql.append("SELECT b.TABLE_NAME AS tableName,b.TABLE_COMMENT AS tableComment,")
.append("a.COLUMN_TYPE AS dataType ")
.append("FROM ").append("( ")
.append("SELECT TABLE_NAME,COLUMN_NAME ").append("FROM ")
.append("INFORMATION_SCHEMA.KEY_COLUMN_USAGE ")
.append("WHERE ").append("constraint_name = 'PRIMARY' ")
.append("AND TABLE_SCHEMA = ").append("'" + dbConfig.getDataBase() + "'");
if (!"".equals(tableName) && tableName != null){
sql.append(" AND TABLE_NAME LIKE ").append("'%").append(tableName).append("%'");
}
sql.append(" GROUP BY TABLE_NAME")
.append(" ) p").append(" INNER JOIN information_schema.COLUMNS a ")
.append("ON (").append(" a.TABLE_SCHEMA = ").append("'" + dbConfig.getDataBase() + "'")
.append(" AND a.TABLE_NAME = p.TABLE_NAME ")
.append("AND p.COLUMN_NAME = a.COLUMN_NAME ").append(")")
.append(" INNER JOIN INFORMATION_SCHEMA.TABLES b ON ( ")
.append("b.TABLE_SCHEMA = ").append("'" + dbConfig.getDataBase() + "'")
.append(" AND b.TABLE_NAME = a.TABLE_NAME ").append(")");
try {
statement = connection.createStatement();
statement.setQueryTimeout(60000);
resultSet = statement.executeQuery(sql.toString());
while (resultSet.next()) {
String jdbcType = resultSet.getString("dataType").toUpperCase();
if (jdbcType.contains("VARCHAR")){
jdbcType = "String";
}else if (jdbcType.contains("BIGINT")){
jdbcType = "Long";
}else {
jdbcType = "Integer";
}
Map<String, Object> map = new HashMap<>(16);
map.put("tableName", resultSet.getString("tableName"));
map.put("comment", resultSet.getString("tableComment"));
map.put("jdbcType", jdbcType);
tableComment.add(map);
}
logger.info("查询成功" + tableComment);
} catch (SQLException e) {
throw new RuntimeException("获取表主键信息失败:" + e.getMessage());
} finally {
closeConnection(connection, resultSet, statement);
}
return tableComment;
}
/**
* 获取表主键信息
*
* @param edTemplateDbconfig
* @return
*/
public static List<Map<String, Object>> getTablePk(EdTemplateDbconfigDTO edTemplateDbconfig) {
Connection connection = getConnection(edTemplateDbconfig);
Statement statement = null;
ResultSet rs = null;
List<Map<String, Object>> tableConfig = new ArrayList<>();
StringBuilder sql = new StringBuilder();
sql.append("SELECT p.TABLE_NAME AS tableName,p.COLUMN_NAME AS columnName,")
.append("a.COLUMN_TYPE AS dataType,")
.append("CASE WHEN p.column_Name IS NULL THEN 'false' ELSE 'true' END AS pkColumn,")
.append("CASE WHEN a.extra = 'auto_increment' THEN 'true' ELSE 'false' END AS autoAdd ")
.append("FROM ").append("( ")
.append("SELECT TABLE_NAME,COLUMN_NAME ").append("FROM ")
.append("INFORMATION_SCHEMA.KEY_COLUMN_USAGE ")
.append("WHERE ").append("constraint_name = 'PRIMARY' ")
.append("AND TABLE_SCHEMA = ").append("'" + edTemplateDbconfig.getDbname() + "'")
.append(" GROUP BY TABLE_NAME")
.append(" ) p").append(" INNER JOIN information_schema.COLUMNS a ")
.append("ON (").append(" a.TABLE_SCHEMA = ").append("'" + edTemplateDbconfig.getDbname() + "'")
.append(" AND a.TABLE_NAME = p.TABLE_NAME ")
.append("AND p.COLUMN_NAME = a.COLUMN_NAME ").append(")");
try {
statement = connection.createStatement();
statement.setQueryTimeout(60000);
rs = statement.executeQuery(sql.toString());
while (rs.next()) {
Map<String, Object> map = new HashMap<>(16);
map.put("tableName", rs.getString("tableName"));
map.put("columnName", rs.getString("columnName"));
map.put("jdbcType", rs.getString("dataType"));
tableConfig.add(map);
}
} catch (SQLException e) {
throw new SqlErrorException("获取表主键信息失败[" + e.getMessage() + "]");
} finally {
closeConnection(connection, rs, statement);
}
return tableConfig;
}
/**
* 数据库连接
*
* @param dbConfig 连接配置对象
* @return Connection对象
*/
public static Connection getConnection(DbConfig dbConfig) {
Connection connection;
String usingPassword = "using password";
String unknownDatabase = "Unknown database";
String linkFailure = "link failure";
try {
Class.forName(dbConfig.getDriverClass());
String url = "jdbc:mysql://" + dbConfig.getUrl() + ":" + dbConfig.getPort() + "/" + dbConfig.getDataBase() + "?connectTimeout=60000&socketTimeout=60000&useUnicode=true&characterEncoding=UTF-8&useSSL=false";
logger.info("正在连接[" + url + "]...");
connection = DriverManager.getConnection(url, dbConfig.getUser(), dbConfig.getPassWord());
} catch (ClassNotFoundException e) {
throw new RuntimeException("连接失败,请检查数据库驱动类型是否正确!");
} catch (SQLException e) {
if (e.getMessage().contains(usingPassword)) {
throw new RuntimeException("数据库帐号或密码错误");
}
else if (e.getMessage().contains(unknownDatabase)) {
throw new RuntimeException("找不到数据库名" + e.getMessage().substring((e.getMessage().indexOf("'")) + 1, e.getMessage().lastIndexOf("'")));
}
else if (e.getMessage().contains(linkFailure)) {
throw new RuntimeException("数据库连接失败,请检查配置是否正确!");
}
else {
throw new RuntimeException("数据库连接失败,请检查配置是否正确!");
}
}
return connection;
}
/**
* 转换元数据
*
* @param rs ResultSet对象
* @return List数据
* @throws SQLException
*/
private static List<String> resultSetToList(ResultSet rs) throws SQLException {
if (rs == null) {
logger.info("调试" + "转换失败元数据为空");
return null;
}
//获取字段数
int columnCount = rs.getMetaData().getColumnCount();
List<String> result = new ArrayList<>();
while (rs.next()) {
for (int j = 1; j <= columnCount; j++) {
result.add(rs.getString(j));
}
}
return result;
}
/**
* 关闭所有连接
*/
public static void closeConnection(Connection connection, ResultSet resultSet, Statement statement) {
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException("resultSet关闭出错:" + e.getMessage());
}
resultSet = null;
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException("statement关闭出错:" + e.getMessage());
}
statement = null;
}
if (connection != null){
try {
connection.close();
logger.info("数据库关闭连接");
} catch (SQLException e) {
throw new RuntimeException("connection关闭出错:" + e.getMessage());
}
connection = null;
}
}
}
说明
动态创建表
dbConfig
是一个数据库配置对象,里面包括了数据库地址,用户名密码等这些信息,list
是传进来得一个集合对象,通过这个集合对象的大小循环,取出对应的字段,因为对象里的这个字段就是要给新建表里的字段,写上SQL,加上了IF NOT EXISTS
,有了就不重复创建以免报错了,就这样循环添加进去,自然就动态产生了。
动态插入
创建迭代器,方便后续将hashMap里的key以及value取出,创建StringBuilder
对象进行拼接insert语句,根据状态值“0或者1”来判断是要进行UUID插入还是主键自增插入,语句中做了两次while
,因为insert语句中的字段要取出来,先取key
的值做插入的字段,再while
一次,把value
值取出,是导入的值,有一点注意一下,设置字段的时候,拼接的时候需要加逗号,然后加到最后,用StringBuilder
的deleteCharAt
方法,把最后一个逗号去掉,类似于String的subString
DbConfig类用于存放配置信息
public class DbConfig {
private String url;
private String port;
private String user;
private String passWord;
private String dataBase;
private String dbType;
private String driverClass;
private String tableName;
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getPort() {
return port;
}
public void setPort(String port) {
this.port = port;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
public String getDataBase() {
return dataBase;
}
public void setDataBase(String dataBase) {
this.dataBase = dataBase;
}
public String getDbType() {
return dbType;
}
public void setDbType(String dbType) {
this.dbType = dbType;
}
public String getDriverClass() {
return driverClass;
}
public void setDriverClass(String driverClass) {
this.driverClass = driverClass;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
@Override
public String toString() {
return "DbConfig{" +
"url='" + url + '\'' +
", port='" + port + '\'' +
", user='" + user + '\'' +
", passWord='" + passWord + '\'' +
", dataBase='" + dataBase + '\'' +
", dbType='" + dbType + '\'' +
", driverClass='" + driverClass + '\'' +
", tableName='" + tableName + '\'' +
'}';
}
}
3. 然后在需要的时候调用
List<String> list = DataBaseUtil.getColumnNames(DatabaseConfig config)
其他
当调用工具类的getColumnNames
方法以及getColumnTypes
方法的时候,想在一个接口里返回给前端这两个返回内容,即字段名称和字段类型一起给前端,可以创建一个HashMap
public HashMap<String, List<String>> columnAndType(DatabaseConfig dbConfig) {
List<String> columnNames = DataBaseUtil.getColumnNames(dbConfig);
List<String> columnTypes = DataBaseUtil.getColumnTypes(dbConfig);
HashMap<String, List<String>> map = new HashMap<>(16);
map.put("columnNames",columnNames);
map.put("columnTypes",columnTypes);
return map;
}
返回的JSON数据格式
columnNames:[**,**,**,**]
columnTypes:[**,**,**,**]