JDBC工具类

封装了一个工具类，用于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:[**,**,**,**]