PreparedStatement in Java

Java preparedstatement example – PreparedStatement in Java | Using Prepared Statements | Steps to Use Java PreparedStatement & Example

by

Java preparedstatement example: PreparedStatement is an interface, which is available in java.MySQL package It extends the Statement interface. Learn How to use PreparedStatement in Java along with creating and closing a prepared statement interface in java easily from the direct links provided in this tutorial.

This Java PreparedStatement Tutorial Includes:

What is PreparadStatement?

Prepared statement java examples: A PreparedStatement is a pre-compiled SQL statement. The PreparedStatement interface extends the Statement interface. Also, It is a subinterface of the Statement. PreparedStatement objects have some helpful new features than Statement objects. The PreparedStatement object gives a feature to execute a parameterized query, instead of hard coding queries.

However, the PreparedStatement interface provides you added functionality with a few advantages over a generic Statement object. Also, this statement furnishes the flexibility of supplying arguments dynamically.

Also Check:

Why we use PreparedStatement?

  • It can be used to execute dynamic and parameterized SQL Query.
  • A Prepared Statement is faster than a Statement interface. Because in Statement Query will be compiled and execute every time, while in case of Prepared Statement Query won’t be compiled every time just executed.
  • It can be used for both static and dynamic queries.
  • In the case of Prepared Statement no chance of SQL Injection attack. It is some kind of problem in database programming.

Suppose, I have an SQL Query. In this SQL Query, we have to use a username and password. This query is checking the username and password are valid or not. Because the end user-provided input the query behavior is changing, it is not checking username and password is valid or not. If you change the behavior of the SQL query by adding a special character in end user-provided input this problem is known as SQL Injection attack.

Creating PreparedStatement Object

Preparedstatement java example: The following creates a PreparedStatement object:

PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}

Closing PreparedStatement Object

Preparedstatement example java: Just make use of the call to the close() method to do your job. Also, remember that always explicitly closing the PreparedStatement object ensures proper cleanup.

PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
pstmt.close();
}

Steps to use PreparedStatement

1. Create Connection to Database

Connection myCon = DriverManager.getConnection(path,username,password)

2. Prepare Statement

In place of hardcoding queries like,

select * from students where age>10 and name ='Chhavi'
Set parameter placeholders( use question mark for placeholders) like,

select * from students where age> ? and name = ?
PreparedStatement myStmt;
myStmt = myCon.prepareStatement(select * from students where age> ? and name = ?);

3. Set parameter values for type and position

myStmt.setInt(1,10);
myStmt.setString(2,"Chhavi");

4. Execute the Query

ResultSet myRs= myStmt.executeQuery();

Methods of PreparedStatement Interface

Java prepared statement example: The important methods of PreparedStatement are given below:

1. public void setInt( int parameter Index, int value): This method is used to set the integer value to the given parameter index.

2. public void setString(int parameter Index, int value): This method is used to set the String value to the given parameter index.

3. public void setFloat(int parameter Index, int value): This method is used to set the float value to the given parameter index.

4. public void setDouble(int parameter Index, int value): This method is used to set the double type value to the given parameter index.

5. public int executeUpdate(String url): If you want to modify in your database you should go for executeUpdate() method. This method returns an integer value which indicates the number of rows affected.

6. public ResultSet executeQuery(String url): This method is used for the select statement which retrieves some data from the database. This method returns a ResultSet.

Java Prepared Statement Example

import java.sql.*;
import java.util.*;
class Example{
public static void main(String args[])throws Exception{
class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/emp_record","root"," ");
String sqlquery = "insert into employee values(? ? ? ?)";
preparedStatement pst = con.preparedStatement(sqlquery);
Scanner sc = new Scanner(System.in);

while(true){
System.out.println("Enter employee number: ")
int eno = sc.nextInt();
System.out.println("Enter employee name: ")
String ename = sc.next();
System.out.println("Enter employee salary: ")
double esal = sc.nextDouble();
System.out.println("Enter employee address: ")
String eaadr = sc.next();

pst.setInt(1,eno);
pst.setString(2,ename);
pst.setDouble(3, esal);
pst.setString(4, eddr);
pst.excecuteUpdate();

System.out.println("Record inserted succesfully ");
System.out.println("Do you want to insert more records[yes/no]");
String option = sc.next();
if(option.equalsIgnoreCase("no")){
break;
}
}
}
}

Output:

PreparedStatement in Java 1

PreparedStatement in Java 2