Passing parameters to a JDBC PreparedStatement

Question

I'm trying to make my validation class for my program. I already establish the connection to the MySQL database and I already inserted rows into the table. The table consists of firstName, lastName and userID fields. Now I want to select a specific row on the database through my parameter of my constructor.

import java.sql.*; import java.sql.PreparedStatement; import java.sql.Connection;  public class Validation {      private PreparedStatement statement;     private Connection con;     private String x, y;      public Validation(String userID) {         try {             Class.forName("com.mysql.jdbc.Driver");             con = DriverManager.getConnection(                     "jdbc:mysql://localhost:3306/test", "root", "");             statement = con.prepareStatement(                     "SELECT * from employee WHERE  userID = " + "''" + userID);             ResultSet rs = statement.executeQuery();             while (rs.next()) {                 x = rs.getString(1);                 System.out.print(x);                 System.out.print(" ");                 y = rs.getString(2);                 System.out.println(y);             }         } catch (Exception ex) {             System.out.println(ex);         }     } }      

But it doesn't seem work.

Answer 1

You should use the setString() method to set the userID. This both ensures that the statement is formatted properly, and prevents SQL injection:

statement =con.prepareStatement("SELECT * from employee WHERE  userID = ?"); statement.setString(1, userID); 

There is a nice tutorial on how to use PreparedStatements properly in the Java Tutorials.

Answer 2

There is a problem in your query..

   statement =con.prepareStatement("SELECT * from employee WHERE  userID = "+"''"+userID);    ResultSet rs = statement.executeQuery(); 

You are using Prepare Statement.. So you need to set your parameter using statement.setInt() or statement.setString() depending upon what is the type of your userId

Replace it with: -

   statement =con.prepareStatement("SELECT * from employee WHERE  userID = :userId");    statement.setString(userId, userID);    ResultSet rs = statement.executeQuery(); 

Or, you can use ? in place of named value - :userId..

   statement =con.prepareStatement("SELECT * from employee WHERE  userID = ?");    statement.setString(1, userID);