Using Prepared statement correctly with WHERE condition in case of any value in MySQL Java

MySQLMySQLi Database

For this, you can use PrepareStatement in Java. Following is the syntax −

String anyVariableName="select yourColumnName from yourTableName where name = ?";
PreparedStatement ps = (PreparedStatement) con.prepareStatement(yourVariableName);
ps.setString(yourColumnIndex, yourValue);

Let us create a table −

mysql> create table demo37
−> (
−> id int not null auto_increment primary key,
−> name varchar(200)
−> );
Query OK, 0 rows affected (2.46 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo37(name) values('John');
Query OK, 1 row affected (0.09 sec)
mysql> insert into demo37(name) values('Bob');
Query OK, 1 row affected (0.08 sec)
mysql> insert into demo37(name) values('John');
Query OK, 1 row affected (0.09 sec)
mysql> insert into demo37(name) values('Chris');
Query OK, 1 row affected (0.08 sec)
mysql> insert into demo37(name) values('David');
Query OK, 1 row affected (0.12 sec)
mysql> insert into demo37(name) values('John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into demo37(name) values('Mike');
Query OK, 1 row affected (0.09 sec)

Display records from the table using select statement −/p>

mysql> select *from demo37;

This will produce the following output −

+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Bob   |
|  3 | John  |
|  4 | Chris |
|  5 | David |
|  6 | John  |
|  7 | Mike  |
+----+-------+
7 rows in set (0.00 sec)

Example

Following is the Java code for PrepareStatement −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class PrepareStatementDemo {
   public static void main(String[] args) {
      Connection con = null;
      try {
         Class.forName("com.mysql.jdbc.Driver");
         con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sampledatabase", "root", "123456");
         String query = "select name from demo37 where name = ?";
         PreparedStatement ps = (PreparedStatement) con.prepareStatement(query);
         ps.setString(1, "John");
         ResultSet rs = ps.executeQuery();
         while (rs.next()) {
            System.out.println(rs.getString(1));
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

Output

This will produce the following output −

John
John
John

Following is the snapshot of the output −

raja
Published on 19-Nov-2020 12:43:47
Advertisements