[jdbc]JDBC原生写法获取表字段信息以及

[jdbc]JDBC原生写法获取表字段信息以及表数据

package com.xzm.utils;

import lombok.SneakyThrows;
import org.springframework.lang.NonNull;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static java.sql.DriverManager.getConnection;

public class DbUtilsNew {

    public static final String COLUMN_NAME = "COLUMN_NAME";
    public static final String TYPE_NAME = "TYPE_NAME";
    public static final String COLUMN_SIZE = "COLUMN_SIZE";
    public static final String REMARKS = "REMARKS";

    public static final String TABLE = "TABLE";

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/lyg_test?useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "root";
        String tableName = "region";
        String queryDataSql = "SELECT * FROM " + tableName + " LIMIT 1,3";

        Map<Integer, List<String>> tableColumnAndData = getTableColumnAndData(url, user, password, queryDataSql, tableName);
        System.out.println("tableColumnAndData = " + tableColumnAndData);

    }

    /**
     * 获取表字段信息以及表数据
     */
    public static Map<Integer, List<String>> getTableColumnAndData(String dbUrl,
                                                                   String dbUserName,
                                                                   String dbPassword,
                                                                   String queryDataSql,
                                                                   String tableName) {
        
        // Class.forName("com.mysql.jdbc.Driver");
        // Class.forName("com.mysql.cj.jdbc.Driver");
        
        try (Connection conn = getConnection(dbUrl, dbUserName, dbPassword);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(queryDataSql)) {
            List<ColumnEntry> tableColumnInfoList = getTableColumnInfoList(conn, tableName);
            return getTableDataLinkedHashMap(rs, tableColumnInfoList);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取表数据
     */
    @SneakyThrows
    private static LinkedHashMap<Integer, List<String>> getTableDataLinkedHashMap(@NonNull ResultSet rs,
                                                                                  @NonNull List<ColumnEntry> tableColumnInfoList)  {
        List<String> tableColumnNameList = tableColumnInfoList.stream().map(ColumnEntry::getColumnName).collect(Collectors.toList());

        // <索引, 值>
        LinkedHashMap<Integer, List<String>> dataMap = new LinkedHashMap<>();
        int index = 0;
        // 5. 遍历结果集并输出
        while (rs.next()) {
            index++;
            List<String> tempList = new ArrayList<>();
            for (String columnName : tableColumnNameList) {
                String columnValue = rs.getString(columnName);
                if (columnValue == null) {
                    columnValue = "";
                }
                tempList.add(columnValue);
            }
            dataMap.put(index, tempList);
        }
        return dataMap;
    }

    /**
     * 获取某张表的字段信息
     */
    public static List<ColumnEntry> getTableColumnInfoList(Connection conn, String tableName) {
        List<ColumnEntry> columnList = new ArrayList<>();
        try {
            // 查询表的元数据信息
            DatabaseMetaData dbMetaData = conn.getMetaData();
            ResultSet resultSet = dbMetaData.getColumns(null, null, tableName, null);

            // 遍历结果集，将字段名添加到集合中
            while (resultSet.next()) {

                String columnName = resultSet.getString(COLUMN_NAME);
                String typeName = resultSet.getString(TYPE_NAME);
                String columnSize = resultSet.getString(COLUMN_SIZE);
                String remarks = resultSet.getString(REMARKS);
                ResultSet pkRs = dbMetaData.getPrimaryKeys(null, null, tableName);
                boolean isPrimaryKey = false;
                while (pkRs.next()) {
                    if (columnName.equals(pkRs.getString(COLUMN_NAME))) {
                        isPrimaryKey = true;
                        break;
                    }
                }
                columnList.add(new ColumnEntry(columnName, typeName, columnSize, remarks, isPrimaryKey));
            }
            columnList.forEach(System.out::println);
            return columnList;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

}