CODEWITHSUNDEEP
javamysqljdbcarraylist
damianm
damianm

Reputation: 131

ArrayList shows all results from database

I want to get one single row from mysql db. But my program prints all rows from db. I'm not sure what is wrong. Could you help me?

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;
}

Below is my second class which I was hoping that selects one row. But it doesn't work properly. I'm typing clientID + client password, so why my method 'showOnScreenBalance' doesn't see it?

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();
    }

}

}

Upvotes: 1

Views: 234

Answers (1)

Supun Amarasinghe
Supun Amarasinghe

Reputation: 1443

You have to define which row of the database table to be printed. To do that, you can define a WHERE clause in your sql statement.

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

eg:

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

For the number of results to be 1, the WHERE condition must also designed such a way.(Only one row with such column value can be there).Otherwise there is no way for the program to know what is your expected result.

Hope it helps :)

Upvotes: 3

Related Questions