How to use prepared statement for select query in Java?
Solution 1
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.
Solution 2
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);
jasim
I am a computer Engineer, a technology enthusiast. I like experimenting new things, learn & share.... fb: https://www.facebook.com/mohdjasim.m
Updated on August 07, 2022Comments
-
jasim almost 2 years
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.