[java] Passing parameters to a JDBC PreparedStatement

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.

This question is related to java mysql jdbc

The answer is


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.


Examples related to java

Under what circumstances can I call findViewById with an Options Menu / Action Bar item? How much should a function trust another function How to implement a simple scenario the OO way Two constructors How do I get some variable from another class in Java? this in equals method How to split a string in two and store it in a field How to do perspective fixing? String index out of range: 4 My eclipse won't open, i download the bundle pack it keeps saying error log

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to jdbc

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' Hibernate Error executing DDL via JDBC Statement Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] MySQL JDBC Driver 5.1.33 - Time Zone Issue Spring-Boot: How do I set JDBC pool properties like maximum number of connections? Where can I download mysql jdbc jar from? Print the data in ResultSet along with column names How to set up datasource with Spring for HikariCP? java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver Exception occurring. Why? java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname