java - ArrayList 显示数据库中的所有结果

Question

我想从 mysql 数据库中获取一行。但是我的程序打印了 db 中的所有行。我不确定哪里出了问题。你能帮帮我吗？

public class ScreenBalance {

// show all records
public static ArrayList<String> showOnScreenBalance() throws Exception {
    LoginVerification.login();

    try {
        Connection con = ConnectionDB.getConnection();
        PreparedStatement statement = con.prepareStatement("SELECT ClientID, Money FROM BankDB.Info");

        ResultSet result = statement.executeQuery();

        ArrayList<String> array = new ArrayList<String>();
        System.out.print("\nID\t\tAmount\t\n");
        while (result.next()) {
            System.out.print(result.getString("ClientID"));
            System.out.print("\t");
            System.out.print(result.getString("Money"));
            System.out.print("\t");
            array.add(result.getString("Money"));
        }
        System.out.println();
        return array;
    } catch (Exception e) {
        System.out.println(e);
    }
    return null;
}

下面是我的第二堂课，我希望它能选择一行。但它不能正常工作。我输入的是 clientID + 客户端密码，为什么我的方法“showOnScreenBalance”看不到它？

public class LoginVerification {

private static boolean loggedIn = false;

    public static void login() throws Exception {

        System.out.println("ATM Machine v 1.0, Welcome user.");
        if(loggedIn){
            //do nothing, user already logged in
            return;
        }

    Scanner input = new Scanner(System.in);
    System.out.println("Please enter USER ID:");
    String userId = input.nextLine();
    System.out.println("Please enter PIN CODE:");
    String pass = input.nextLine();

    try {
        Connection con = ConnectionDB.getConnection();
        String sql = "SELECT COUNT(*) FROM Info WHERE ClientID = ? AND ClientPass = ?";

        PreparedStatement posted = con.prepareStatement(sql);
        posted.setString(1, userId);
        posted.setString(2, pass);
        ResultSet resultSet = posted.executeQuery();
        resultSet.next();
        int rowCount = resultSet.getInt(1);

        if (rowCount == 1) {
            loggedIn = true;
            System.out.println("LOGIN SUCCESSFUL");
            Helper.showMainMenu();

        } else {
            loggedIn = false;
            System.out.println("#LOGIN UNSUCCESSFUL, WRONG LOGIN OR PASSWORD");

            System.out.println("\nTry again? \n1) yes \n2) no [quit]");
            int choice = input.nextInt();
            if (choice == 1) {
                login();
            } else {
                System.exit(0);
            }
        }
        con.close();

    } catch (Exception e) {
        e.printStackTrace();
    }

}

Answer 1

您必须定义要打印数据库表的哪一行。为此，您可以在 sql 语句中定义一个 WHERE 子句。

PreparedStatement statement = con.prepareStatement("SELECT ClientID, Money FROM BankDB.Info WHERE some_condition" );

例如:

PreparedStatement statement = con.prepareStatement("SELECT ClientID, Money FROM BankDB.Info WHERE ClientID='some_value'" );

如果结果数为1，那么WHERE条件也必须这样设计(只能有这样列值的一行)，否则程序无法知道你期望的结果是什么.

希望对您有所帮助:)