java - 在Java中，如何让我的数据库表在带有标题和行的列中输出？

Question

我需要代码的输出包含 ID、NAME 和 CALORIES 列，但我不确定“如何”。我希望 id# 自动填充自身，但是当我尝试在 Oracle 上找到的内容时，它不起作用。我该如何让线条出现来分隔一切？

这是我的代码:

package edu.umsl.java3816.foodItem;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class FoodItemTable {

    Connection fit = null;

    public static void main(String[] args) {

        String createTableSQL = "create table FOOD_ITEM(ID INTEGER,NAME VARCHAR(256),CALORIES INTEGER)";
        String insertTableSQL = "INSERT INTO FOOD_ITEM(ID,NAME,CALORIES) VALUES('1','hamburger','550')";
        String selectSQLStatement = "SELECT * FROM FOOD_ITEM";

        FoodItemTable fit = new FoodItemTable();

        try {
            fit.getConnection();
            fit.createTable(createTableSQL);
            fit.insertSQL(insertTableSQL);
            fit.selectSQL(selectSQLStatement);
            fit.shutdownDB();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void getConnection() throws Exception {

        Class.forName("org.hsqldb.jdbcDriver");

        fit = DriverManager.getConnection("jdbc:hsqldb:mem", "sa", // username
                "");

    }

    public void createTable(String sqlStatement) {

        Statement statement = null;
        try {
            statement = fit.createStatement();
            int i = statement.executeUpdate(sqlStatement);

            if (i == -1) {
                System.out.println("Error: " + sqlStatement);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void insertSQL(String insertSQLStatement) {

        Statement statement = null;
        try {
            statement = fit.createStatement();
            int i = statement.executeUpdate(insertSQLStatement);

            if (i == -1) {
                System.out.println("Error: " + insertSQLStatement);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void selectSQL(String selectSQLStatement) {

        Statement statement = null;

        try {
            statement = fit.createStatement();
            ResultSet rs = statement.executeQuery(selectSQLStatement);
            while (rs.next()) {
                System.out.println(rs.getInt("ID"));
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getInt("CALORIES"));
            }
        } catch (SQLException e) {
            e.printStackTrace();

        }
    }

    public void shutdownDB() {

        try {
            fit.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

Answer 1

它看起来就像您想要在控制台窗口中显示查询结果，但以表格样式有序的方式(至少在某种程度上)。

您可以使用 java 方法来完成此操作，但首先您需要将返回的查询结果集放入二维 (2D) 数组中。以下是实现此目的的方法(利用当前的 selectSQL() 方法):

public void selectSQL(String selectSQLStatement) {
    PreparedStatement statement;
    int resultSetCount = 0;
    String rowCountSQL = "SELECT COUNT(*) AS rCount FROM FOOD_ITEM;";

    try {
        //Get the number of records within that will be
        //retrieved from your query...  
        statement = fit.prepareStatement(rowCountSQL);
        ResultSet rs = statement.executeQuery();
        while (rs.next()) { resultSetCount = rs.getInt("rCount"); } 
        // Are there records to display? 
        if (resultSetCount == 0) {
            // No there isn't
            System.out.println("There are NO Records to display!");
            return; 
        }

        // Yes there is so let's declare our 2D Object Array...
        Object[][] queriedRecords = new Object[resultSetCount][3];

        // And now fill the array...
        statement = fit.prepareStatement(selectSQLStatement);
        rs = statement.executeQuery();
        int counter = 0;
        while (rs.next()) {
            queriedRecords[counter][0] = rs.getInt("ID");
            queriedRecords[counter][1] = rs.getString("NAME");
            queriedRecords[counter][2] = rs.getInt("CALORIES");
            counter++;
        }

        // Display the retrieved records in Console window...
        // The table header names to be used when printed
        String[] tableHeader = { "ID", "NAME", "CALORIES" };
        consolePrintTable(tableHeader, queriedRecords, 2, false, true); 
    } catch (SQLException e) { e.printStackTrace(); }
}

您会注意到该方法的底部调用了另一个名为 consolePrintTable() 的方法。该方法会将检索到的数据显示到控制台。请阅读我随此方法提供的 JavaDoc。这是方法代码:

/**
 * This method will take the supplied data and print a table to console in a particular 
 * spaced format.<br><br>
 * 
 * <b>Example Usage:</b><pre>
 * 
 *      final Object[][] table = new Object[4][];
 *      table[0] = new Object[] { "foo", "bar", "baz", "bar2", "foo2", "baz2" };
 *      table[1] = new Object[] { "bar2", "foo2", "baz2", "bar2", "foo2", "baz2" };
 *      table[2] = new Object[] { "baz3", "bar3", "foo3", "bar2", "foo2", "baz2" };
 *      table[3] = new Object[] { "foo4", "bar4", "baz4", "bar2", "foo2", "baz2" };
 *   
 *      String[] h = {"Header 1", "Header 2", "Header 3", "Header 4", "Header 5", "Header 6"};
 *      consolePrintTable(h, table, 4, false, true);
 * 
 *      // Output will be:
 *      
 *      --------------------------------------------------------------------
 *      Header 1    Header 2    Header 3    Header 4    Header 5    Header 6    
 *      --------------------------------------------------------------------
 *      foo         bar         baz         bar2        foo2        baz2        
 *      bar2        foo2        baz2        bar2        foo2        baz2        
 *      baz3        bar3        foo3        bar2        foo2        baz2        
 *      foo4        bar4        baz4        bar2        foo2        baz2</pre>
 * 
 * @param headerData (1D String Array) Column (header) titles for the table. 
 * If no Header is desired then supply <b>null</b><br>
 * 
 * @param tableData (2D Object Array) The table data to display.<br>
 * 
 * @param spacesBetweenCells (Integer) The table that will be printed is always
 * spaced apart from one another based on the widest cell detected within both 
 * supplied header data or the 2D Object Array data. This parameter allows you
 * add additional spacing between each column.<br>
 * 
 * @param options (optional - Boolean) ...<pre>
 * 
 *      rightAlignment      - (Default is false) If boolean true is supplied 
 *                            theTable is displayed as right justified. Boolean
 *                            false make the table display as left justified.
 * 
 *      applyHeaderLines    - (Default is true) By default lines are applied to 
 *                            the table so as to separate the header from table
 *                            data. If false is supplied then header lines are
 *                            not displayed. This option only applies if a Header
 *                            1D String Array is supplied (not null).
 * 
 * </pre><br>
 */
public static void consolePrintTable(String[] headerData, Object[][] tableData, int spacesBetweenCells, boolean... options) {
    if (tableData.length == 0) { return; }
    boolean alignRight = false; // Default is Left Alignment
    boolean addHeaderLines = true;
    if(options.length > 0) { 
        if (options.length >= 1) { alignRight = options[0]; }
        if (options.length == 2) { addHeaderLines = options[1]; }
    }

    // Get the widest Cell needed so that all the
    // table cells will be the same when printed.
    int widestCell = 0;
    for (Object[] tableData1 : tableData) {
        for (int j = 0; j < tableData[0].length; j++) {
            int l = tableData1[j].toString().length();
            if (l > widestCell) { widestCell = l; }
        }
    }
    //Now check for the widest in header (if any)
    if (headerData != null && headerData.length > 0) {
        for(int i = 0; i < headerData.length; i++) {
            if (headerData[i].length() > widestCell) {
                widestCell = headerData[i].length(); 
            }
        }
    }

    widestCell = (widestCell + spacesBetweenCells);
    // -------------------------------------------

    int columns = tableData[0].length;
    String format = "", alignStrg = "-";
    if (alignRight) { alignStrg = ""; }
    for (int i = 1; i <= columns; i++) { 
        format+= "%" + alignStrg + String.valueOf(widestCell) + "s"; 
    }
    format+= "\n";

    //Print The Header (if any)...
    if (headerData != null && headerData.length > 0) {
        int charCount = columns*widestCell;
        if (!alignRight) { charCount = ((columns*widestCell) - spacesBetweenCells);}
        String gridline = "\n" + String.join("", Collections.nCopies(charCount, "-"));
        if (addHeaderLines) { System.out.println(gridline); }
        for(int i = 0; i < headerData.length; i++) {
            System.out.printf("%" + alignStrg + String.valueOf(widestCell) + "s", headerData[i]);
        }
        if (addHeaderLines) { System.out.println(gridline); }
        else { System.out.println(""); }
    }

    // Display the Table data...
    for (final Object[] row : tableData) {
        System.out.format(format, row);
    }
}