通过jdbc获得数据库中表、字段信息

一、首先创建数据库连接

public void create() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            this.connection = java.sql.DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ry?useUnicode=true&characterEncoding=utf8", "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

在spring boot 中也可以写成配置类的形式：

新建一个类：（我这里用了lombox ,没用的要自己加上get和set方法，lombox的注解@Data @NoArgsConstructor）

@Component
@ConfigurationProperties(prefix="dbconnect")
@PropertySource(value= {"classpath:application.yml"})
@Data
@NoArgsConstructor
public class DbConnect implements Serializable {

    private static final long serialVersionUID = 4349552945449301902L;

    /**
     * 数据库连接地址
     */
    private String url;

    /**
     * 用户名
     */
    private String userName;

    /**
     * 密码
     */
    private String password;

    /**
     * 连接驱动
     */
    private String driverClassName;

}

在application中配置：

dbconnect:
  #  配置数据库连接信息
  driverClassName: com.mysql.jdbc.Driver
  userName: root
  password: 123456
  url: jdbc:mysql://127.0.0.1:3306/ry?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true

然后创建连接：

@Component
public class ConnectUtil {


    @Autowired
    private DbConnection dbConnection;

    public void createConnection() {
        try {
            Class.forName(dbConnection.getDriverClassName()).newInstance();
            this.connection = java.sql.DriverManager.getConnection(customConnection.getUrl(), customConnection.getUserName(), customConnection.getPassword());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

创建连接后写调用方法：

 public void getTableData() {
        //创建数据库连接
//        create();
        createConnection();

        try {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet resultSet = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (resultSet.next()) {// ///TABLE_TYPE/REMARKS
//                System.out.println("表名：" + resultSet.getString("TABLE_NAME"));
//                System.out.println("表类型：" + resultSet.getString("TABLE_TYPE"));
//                System.out.println("表所属数据库：" + resultSet.getString("TABLE_CAT"));
//                System.out.println("表备注：" + getRemark(resultSet.getString("TABLE_NAME")));
                String tableName = resultSet.getString("TABLE_NAME");
                String tableType = resultSet.getString("TABLE_TYPE");
                String tableCat = resultSet.getString("TABLE_CAT");
                String tableRemark = getRemark(resultSet.getString("TABLE_NAME"));

                ResultSet rs = dbMetaData.getColumns(null, "%", resultSet.getString("TABLE_NAME"), "%");
                while (rs.next()) {
//                    System.out.println("字段名："+rs.getString("COLUMN_NAME")+"\t字段注释："+rs.getString("REMARKS")+"\t字段数据类型："+rs.getString("TYPE_NAME"));
                    String columnName = rs.getString("COLUMN_NAME");
                    String columnRemark = rs.getString("REMARKS");
                    String columnType = rs.getString("TYPE_NAME");

                }
         
            }
        } catch (SQLException e) {
            try {
                connection.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
        //关闭连接
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
      
}

/**
 * 传入表名，返回注释信息
 */
private String getRemark(String tableName) {
        String sql = "SHOW CREATE TABLE " + tableName;
        PreparedStatement ps = null;
        String comment = null;
        try {
            ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if (rs != null && rs.next()) {
                String createDDL = rs.getString(2);
                comment = parse(createDDL);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return comment;
}

/**
 * 传入建表语句，返回表注释信息
 */
public static String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
}