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.
As with Statement objects, to execute a PreparedStatement object, call an execute statement: executeQuery if the query returns only one ResultSet (such as a SELECT SQL statement), executeUpdate if the query does not return a ResultSet (such as an UPDATE SQL statement), or execute if the query might return more than one ...
You must always use prepared statements for any SQL query that would contain a PHP variable. To do so, always follow the below steps: Create a correct SQL SELECT statement.
The Statement. executeUpdate method works if you update data server tables with constant values. However, updates often need to involve passing values in variables to the tables. To do that, you use the PreparedStatement.
The Java source code To perform a SQL SELECT query from Java, you just need to follow these steps: Create a Java Connection to the MySQL database. Define the SELECT statement. Execute the SELECT query, getting a Java ResultSet from that query.
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.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With