How to get specific data from MySQL database?

Question

I successfully created an online MySQL database and connected my java program to it.

Now I wanted to get data out of it: In my database table "playerdata" are three rows:

ID, Username, Password

I created a JavaFX (changed from swing) window with one Textfield(for the username), a Passwordfield(for the password) and a button to login in.

This is my event code for the button:

//makes a query in table "Login" searching for "username"s and "password"s
String query = "select * from playerdata where username=? and password=?";                  
//passing query to preparedStt (statement)
PreparedStatement preparedStt = cnct.prepareStatement(query); 

//setting Strings for username and password 
preparedStt.setString(1, userName.getText()); //index 1 for username (upper ?)
preparedStt.setString(2, userPassword.getText()); //index 2 for username (upper ?) (index starts at 1)

ResultSet resSet = preparedStt.executeQuery(); //executes query result to resSet

int count = 0;

while(resSet.next()) {
    count++; //increments counter
}

if(count == 1) { //goes one time if query matches user and pass
    System.out.println("Access granted!");
} else { //otherwise login is incorrect
    System.out.println("Access denied!");
    System.exit(0);
}

//closes resources
resSet.close();
preparedStt.close();

So this query goes through all usernames and password and if they match with the Textfield(userName) and Passwordfield(userPassword) it grants access.

I encountered a problem! As I mentioned above I had three rows and this query searches for two rows. I'd like to know the ID of the player which logged in into my program. Do you have any idea how I could get this working?

I already tried making a third index (and id=?) and then use the resSet.getString(3), but it gives me an error which says that the index 3 isn't specified.

Thanks.

Youcef LAIDANI · Accepted Answer · 2016-06-26 11:01:50Z

1

You can simply get your id like that:

String query = "select * from playerdata where username=? and password=?"; //makes a query in table "Login" searching for "username"s and "password"s

            PreparedStatement preparedStt = cnct.prepareStatement(query); //passing query to preparedStt (statement)

            //setting Strings for username and password 
            preparedStt.setString(1, userName.getText()); //index 1 for username (upper ?)
            preparedStt.setString(2, userPassword.getText()); //index 2 for username (upper ?) (index starts at 1)

            ResultSet resSet = preparedStt.executeQuery(); //executes query result to resSet

            int count = 0;
            int id = 0;
            while(resSet.next()) {
                count++; //increments counter
                id = resSet.getInt("id");
            }

            if(count == 1) { //goes one time if query matches user and pass
                System.out.println("Access granted!");
            } else { //otherwise login is incorrect
                System.out.println("Access denied!");
                System.exit(0);
            }

            //closes connection
            resSet.close();
            preparedStt.close();

answered Jun 26, 2016 at 11:01

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

creativecreatorormaybenot Over a year ago

Thank you! Worked perfectly. Now it's really obvious.. I don't know why I didn't see that. The error was because I tried to take a string but it is an Integer.

Youcef LAIDANI Over a year ago

you can validate my answer if you if that helping you!

Collectives™ on Stack Overflow

How to get specific data from MySQL database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related