如何编写Java方法从MySQL的Menu表读取指定数据并控制台表格化输出

Let's walk through fixing your code step by step—there are a couple of key issues causing it to fail, plus we'll add the clean table formatting you need for the console.

Key Issues in Your Original Code #

1. Invalid SQL Query: Your SELECT statement is missing the specific columns you want to fetch (item name, price, type, category). The syntax select from tableName isn't valid MySQL.
2. No ResultSet Processing: You didn't include code to extract data from the ResultSet and format it into a readable table.
3. Resource Leaks: You're not closing database resources (Connection, Statement, ResultSet) properly, which can cause performance issues over time.

Corrected Full Method #

Here's the fixed code with proper querying, resource management, and table formatting:

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

public static void getMenuItems(String tableName) {
    // Use try-with-resources to auto-close connections/statements/resultsets
    try (Connection con = getConnection(); 
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT `item name`, price, type, category FROM " + tableName)) {

        // Print formatted table header
        System.out.printf("%-20s %-10s %-15s %-20s%n", "Item Name", "Price", "Type", "Category");
        System.out.println("----------------------------------------------------------------------------------------");

        // Iterate through results and print each row
        while (rs.next()) {
            String itemName = rs.getString("item name");
            double price = rs.getDouble("price");
            String type = rs.getString("type");
            String category = rs.getString("category");

            // Align columns for clean output
            System.out.printf("%-20s %-10.2f %-15s %-20s%n", itemName, price, type, category);
        }

    } catch (SQLException e) {
        // Handle exceptions with meaningful feedback
        System.err.println("Error retrieving menu items: " + e.getMessage());
        e.printStackTrace();
    }
}

// Replace with your actual database connection details
private static Connection getConnection() throws SQLException {
    String url = "jdbc:mysql://localhost:3306/your_database_name";
    String user = "your_username";
    String password = "your_password";
    return DriverManager.getConnection(url, user, password);
}

Explanation of Changes #

• Valid SQL Query: We explicitly select the four columns you need. Backticks around item name handle the space in the column name (MySQL requires this for identifiers with spaces).
• Try-With-Resources: This auto-closes all database resources when done, preventing leaks without manual cleanup code.
• Table Formatting: String.printf uses format specifiers (like %-20s for left-aligned 20-character strings) to create a neatly aligned table.
• Robust Error Handling: Catches and prints SQL exceptions to help debug issues like connection failures or invalid column names.

Example Console Output #

If your Menu table has 4 rows, the output will look like this:

Item Name            Price      Type            Category            
----------------------------------------------------------------------------------------
Cheeseburger         9.99       Main Course     American           
Caesar Salad         7.49       Appetizer       Salad               
Chocolate Cake       5.99       Dessert         Bakery              
Iced Tea             2.49       Beverage        Non-Alcoholic       

内容的提问来源于stack exchange，提问作者Mahmoud Al-Haroon