在春季启动中将所有mysql表数据转换为JSON [英] Convert all the mysql table data into JSON in spring boot

问题描述

我只想指定表名，它将自动将其所有数据转换为JSON格式

I want to just specify the table name and it will automatically convert all of its data to JSON Format

我尝试过使用JDBC将表中的数据存储到JSON中的代码.

I have tried a code that is storing the data from table into JSON using JDBC .

public class DataBaseToJson {
    public static ResultSet RetrieveData() throws Exception {
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        String mysqlUrl = "jdbc:mysql://localhost/studentsDB?autoReconnect=true&useSSL=false";
        Connection con = DriverManager.getConnection(mysqlUrl, "root", "root");
        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery("Select * from students");
        return resultSet;
    }

    public static void main(String args[]) throws Exception {
        JSONObject jsonObject = new JSONObject();
        JSONArray array = new JSONArray();
        ResultSet resultSet = RetrieveData();
        while (resultSet.next()) {
            JSONObject record = new JSONObject();
            record.put("students ID", resultSet.getInt("students_id"));
            record.put("students Name", resultSet.getString("students_name"));
            array.add(record);
        }
        jsonObject.put("students Information", array);
        try {
            FileWriter file = new FileWriter("output.json");
            file.write(jsonObject.toJSONString());
            file.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

我不想在文件中指定文件.我想使其通用，以便我们只能放入表名，它将自动转换该表中的所有数据并保存到JSON文件中.

I don't want to specify the fileds in a file. I want to make it generic so that we could only put the table name and it will automatically convert all the data from that table and save into a JSON file.

推荐答案

    public static void main(String args[]) throws Exception {
        String tableName = "books";
        Connection connection = createConnection();
        JSONArray array = new JSONArray();
        JSONObject jsonObject = new JSONObject();
        List<String> columns = loadColumns(connection, tableName);
        ResultSet dataSet = loadData(connection, tableName);
        while (dataSet.next()) {
            JSONObject record = new JSONObject();
            for (String column : columns) {
                record.put(column, dataSet.getObject(column));
            }
            array.add(record);
        }
        jsonObject.put(tableName, array);
        try {
            FileWriter file = new FileWriter("output.json");
            file.write(jsonObject.toJSONString());
            file.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static Connection createConnection() throws SQLException {
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        String mysqlUrl = "jdbc:mysql://localhost/library?autoReconnect=true&useSSL=false";
        Connection connection = DriverManager.getConnection(mysqlUrl, "root", "root");
        return connection;
    }


    public static List<String> loadColumns(Connection connection, String tableName) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT COLUMN_NAME FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_NAME LIKE '"+tableName+"'");
        List<String> columnsName = new ArrayList<String>();
        while(resultSet.next()) {
            columnsName.add(resultSet.getString("COLUMN_NAME"));
        }
        return columnsName;
    }

    public static ResultSet loadData(Connection connection, String tableName) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("Select * from "+tableName+"");
        return resultSet;
    }

这篇关于在春季启动中将所有mysql表数据转换为JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！