PreparedStatement in java is a pre-compiled statement . So it is faster than normal JDBC Statement. Here we are discussing about preparedstatement in java . Also we are discussing an example Java program which contains a PreparedStatement.
PreparedStatement in java
Let us start from scratch.For doing any query we should have a database first. Then we need to create a database table .In our case we are using MySQL server 5.6 community version as database . We can download it from here. For executing queries to create database and table , either we can use the default command line client or any object explorer like SQLyog.We can download the community version of SQLyog from here.
Now execute the following queries either from the command line client or from the object browser.
1)Create database testdb
CREATE DATABASE testdb;
2)create table testable
USE testdb;
CREATE TABLE testtable ( number INT, DATA VARCHAR(100) );
3)Insert few records into the table
INSERT INTO testtable VALUES (‘1′,’cow’);
INSERT INTO testtable VALUES (‘2′,’sheep’);
Now let us see the code. This code needs the MySQL connector jar in the class path.We can download it from here.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class PreparedStatementSample {
private Connection connection = null;
private Statement statement = null;
private PreparedStatement prepStatement = null;
private ResultSet resultset = null;
public PreparedStatementSample() {
}
public void update() {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "username", "password");
String readQuery = "select * from testtable";
statement = connection.createStatement();
resultset = statement.executeQuery(readQuery);
System.out.println("Before update");
while (resultset.next()) {
System.out.println("Number = " + resultset.getInt(1)
+ " Data = " + resultset.getString(2));
}
prepStatement = connection
.prepareStatement("update testtable SET data=? where number=?");
prepStatement.setString(1, "cat");
prepStatement.setInt(2, 1);
int status = prepStatement.executeUpdate();
resultset = statement.executeQuery(readQuery);
System.out.println("After update");
while (resultset.next()) {
System.out.println("Number = " + resultset.getInt(1)
+ " Data = " + resultset.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
PreparedStatementSample sample = new PreparedStatementSample();
sample.update();
}
}
We can understand the use of PreparedStatement from the above code.The ‘ ? ‘ indicates the value to be replaced at run time. SetX() method is using to set the value at run time. It can be either String , or Integer or any other supported type. Now let us see the output of the above program
Output
Before update
Number = 1 Data = cow
Number = 2 Data = sheep
After update
Number = 1 Data = cat
Number = 2 Data = sheep