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.
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);
If you are using prepared statement, you should use it like this:
"SELECT * from employee WHERE userID = ?"
Then use:
statement.setString(1, userID);
?
will be replaced in your query with the user ID passed into setString
method.
Take a look here how to use PreparedStatement.
The problem was that you needed to add " ' ;" at the end.
Do something like this, which also prevents SQL injection attacks
statement = con.prepareStatement("SELECT * from employee WHERE userID = ?");
statement.setString(1, userID);
ResultSet rs = statement.executeQuery();
You can use '?' to set custom parameters in string using PreparedStatments.
statement =con.prepareStatement("SELECT * from employee WHERE userID = ?");
statement.setString(1, userID);
ResultSet rs = statement.executeQuery();
If you directly pass userID in query as you are doing then it may get attacked by SQL INJECTION Attack.
Source: Stackoverflow.com