JDBC-PreparedStatement in java

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

See related topics also:

JDBC API overview

JDBC Driver types

Reading database  in JDBC

Inserting record into database in JDBC

Updating database record in JDBC