How to use prepared statement for select query in Java?

Question

I had tried several times using prepared statements but it returns SQL exception. here is my code:

public ArrayList<String> name(String mobile, String password) {
    ArrayList<String> getdata = new ArrayList<String>();
    PreparedStatement stmt = null;
    try {
        String login = "select mobile, password from tbl_1 join tbl_2 on tbl_1.fk_id=2.Pk_ID where mobile=? and password=?";

        String data = "select * from tbl_2  where password='" + password + "'";

        PreparedStatement preparedStatement = conn.prepareStatement(login);

        preparedStatement.setString(1, mobile);
        preparedStatement.setString(1, password);

        ResultSet rs = preparedStatement.executeQuery(login);

        Statement stmts = (Statement) conn.createStatement();

        if (rs.next()) {
            System.out.println("Db inside RS");
            ResultSet data = stmts.executeQuery(data);

            while (data.next()) { /* looping through the resultset */

                getdata.add(data.getString("name"));
                getdata.add(data.getString("place"));
                getdata.add(data.getString("age"));
                getdata.add(data.getString("job"));
            }

        }

    } catch (Exception e) {
        System.out.println(e);
    }

    return getdata;
}

While running this, I got the following SQL exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? and password=?' at line 1.

Any suggestion to make this work? any piece of code is appreciated.

First problem - you're setting parameter 1 twice, and never setting parameter 2. Second problem - you're only using a prepared statement for *one* of the queries. Third problem: it sounds like you might be storing the password in plain text... Fourth problem: you haven't told us *where* you're getting the exception. — Jon Skeet, Jul 11 '14 at 07:31
The Jon Skeet analytical machine has just taken your question to the cleaners. Consider yourself Skeeted. — christopher, Jul 11 '14 at 07:33
@Jon Skeet,i cleared first problem.i need prepared statment only for first query the exception is for the first query which i am using prepared statement. Thanks it worked — jasim, Jul 11 '14 at 08:22
possible duplicate of [PreparedStatement does not work with mysql/JDBC](http://stackoverflow.com/questions/18680503/preparedstatement-does-not-work-with-mysql-jdbc) — Mark Rotteveel, Jul 11 '14 at 08:22

score 32 · Accepted Answer · 2014-07-11T08:46:54.117

You need to use:

preparedStatement.executeQuery();

instead of

preparedStatement.executeQuery(login);

when you pass in a string to executeQuery() that query is executed literally and thus the ? is send to the database which then creates the error. By passing query string you are not execution the "cached" prepared statement for which you passed the values.

score 4 · Answer 2 · answered Jul 11 '14 at 07:39

For both parameter you use preparedStatement.setString(1, ..); so the first parameter is set two times. but you never set the value for second parameter.

so change

preparedStatement.setString(1, mobile);
            preparedStatement.setString(1, password);

to

    preparedStatement.setString(1, mobile);
    preparedStatement.setString(2, password);

How to use prepared statement for select query in Java?

2 Answers2

Linked

Related