How can I retrieve two fields of a table in Mysql with Java?

Question

I have a ClientsTable like that :

firstName lastName address idNumber userName password
         |        |       |        |        |
david      bowie    here    123     mick      jagger 
chuck      norris   there   456     steven    seagal

I want to get both the userName and the password of each row and verify it with the given parameters of my method :

public Person verifyClientExists(String username,String password) throws SQLException
{

    ResultSet results = this.m_statement.executeQuery("SELECT `userName` FROM `ClientsTable`");  
    while (results.next() == true)
    {
        // here I want to compare the retrieved data from 
                    // mysql with the "username" and "password" given in the prototype
    }



    return null;

}

But with my current query I can only get one field .

How can I retrieve two fields?

It's a bad practice to store the password in plaintext in the database. — Mark Byers, Aug 06 '12 at 08:45
Use something like PBKDF2 or bcrypt. http://stackoverflow.com/questions/2860943/suggestions-for-library-to-hash-passwords-in-java — Mark Byers, Aug 06 '12 at 08:50

Brian Agnew · Accepted Answer · 2012-08-06T08:37:45.897

3

Why not use

select username, password from clientstable

? And then your ResultSet interrogation is:

String username = results.getString("username");
String password = results.getString("password");

See the JDBC tutorial page on ResultSets. Note that I ask for the results by column name. It's fractionally more robust than by asking for them by index (number). If I change the SQL statement (reorder query parameters) then the correct columns are still returned.

I hope you're not storing cleartext passwords in those tables, btw! See here for more info on hashing and salts.

edited Aug 06 '12 at 08:37

answered Aug 06 '12 at 08:26

Brian Agnew

268,207
37
334
440

Good answer +1 & chosen , for the mentioning the importance of password encryption . – Jack cole Aug 06 '12 at 08:50

score 2 · Answer 2 · answered Aug 06 '12 at 08:26

2

Change your query to:

SELECT `userName`, `password` FROM `ClientsTable`

answered Aug 06 '12 at 08:26

Mark Byers

811,555
193
1,581
1,452

score 1 · Answer 3 · answered Aug 06 '12 at 08:27

First, I would suggest using the APIs to build queries, and not execute raw queries (take a look here).

But to answer your question - if you want to get also the password use:

ResultSet results = this.m_statement.executeQuery("SELECT `userName`, `password` FROM `ClientsTable`");

score 1 · Answer 4 · answered Aug 06 '12 at 08:28

alternatively, you can check the number of records from the result:

SELECT COUNT(*) 
FROM   `ClientsTable`
WHERE  userName = 'userHere' AND
       password = 'passHere'

the result here would be 0 and 1 only :) because I assume that the username is unique.

but if you want to retrieve the username and the password, just select the two columns in your query.

SELECT `userName`, `password` 
FROM `ClientsTable`

score 1 · Answer 5 · answered Aug 06 '12 at 08:28

1

You can ask for more fields like this: SELECT userName, password FROM ClientsTable

Then use resultSet.getString(1) for userName and resultSet.getString(2) for password

answered Aug 06 '12 at 08:28

Paaske

4,345
1
21
33

score 1 · Answer 6 · answered Aug 06 '12 at 09:03

Change your sql to "SELECT userName ,password FROM ClientsTable

Now you can use the resultset object and fetch the values
results.getString(1)
results.getString(2)

or you can retrieve using the fieldName results.getString("userName"); results.getString("password");

score 1 · Answer 7 · answered Aug 06 '12 at 09:17

1

Your query is wrong. Your query should be like :-

SELECT EMP_NAME,EMP_PASSWORD FROM EMP_DETAILS TABLE;

Its quite simple to retrieve not only 2 field but also any number of field.

answered Aug 06 '12 at 09:17

JDGuide

6,239
12
46
64

How can I retrieve two fields of a table in Mysql with Java?

7 Answers7