java - 如何获取mysql数据库中所有列名的数据

Question

我创建这段代码是为了在 sql 数据库中获取列名。但是现在我要修改上面的代码以获取所有具有列名的表数据。然后获取所有数据并转换为jsonarray并通过。我如何修改此代码以获取具有列名称的所有表数据。

@Override
    public JSONArray getarray(String sheetName) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");
            con.setAutoCommit(false);
            PreparedStatement pstm = null;
            Statement stmt = null;

            //-----------------------Drop earliye table -------------------------------------------------------------
            try {
                String sqldrop = "select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME='" + sheetName.replaceAll(" ", "_") + "'";
                System.out.println(sqldrop);
                PreparedStatement mypstmt = con.prepareStatement(sqldrop);
                ResultSet resultSet = mypstmt.executeQuery();
                JSONArray jsonArray = new JSONArray();
                while (resultSet.next()) {
                    int total_rows = resultSet.getMetaData().getColumnCount();
                    JSONObject obj = new JSONObject();
                    for (int i = 0; i < total_rows; i++) {
                        String columnName = resultSet.getMetaData().getColumnLabel(i + 1).toLowerCase();

                        Object columnValue = resultSet.getObject(i + 1).toString().replaceAll("_", " ");
                        // if value in DB is null, then we set it to default value
                        if (columnValue == null) {
                            columnValue = "null";
                        }
                        /*
                         Next if block is a hack. In case when in db we have values like price and price1 there's a bug in jdbc - 
                         both this names are getting stored as price in ResulSet. Therefore when we store second column value,
                         we overwrite original value of price. To avoid that, i simply add 1 to be consistent with DB.
                         */
                        if (obj.has(columnName)) {
                            columnName += "1";
                        }
                        obj.put(columnName, columnValue);
                    }
                    jsonArray.put(obj);
                }
                mypstmt.close();
                con.commit();
                return jsonArray;
            } catch (Exception e) {
                System.out.println("There is no exist earlyer databases table!..... :( :( :( **************** " + sheetName.replaceAll(" ", "_"));
            }
//----------------------------------------------------------------------------

        } catch (ClassNotFoundException e) {
            System.out.println(e);
        } catch (SQLException ex) {
            Logger.getLogger(PassArrayDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("%%%%%%%%%%");
        return null;
    }

我的目标是获取所有具有列名和以上数据的数据作为 json 传递 html 页面。因此，如果您有任何方法来获取具有列名的所有数据都适合我。

Answer 1

//代码从这里开始

//此方法从 mysql 表中检索所有数据...

public void retrieveAllData(String host, String user, String pass, String query) {

JTextArea textArea = new JTextArea();
try(
    Connection connection = DriverManager.getConnection( host, user, pass )
    Statement statement = connection.createStatement()
    ResultSet resultSet = statement.executeQuery(query)) {

        ResultSetMetaData metaData = resultSet.getMetaData();
        int totalColumns = metaData.getColumnCount();

        for( int i = 1; i <= totalColumns; i++ ) {
            textArea.append( String.format( "%-8s\t", metaData.getColumnName(i) ) );
        }
        textArea.append( "\n" );
        while( resultSet.next() ) {
            for( int i = 1; i <= totalColumns; i++ ) {
                Object object = resultSet.getObject(i).toString();
                textArea.append( String.format("%-8s\t", object) );


    }
            textArea.append( "\n" );
        }

    }