Unit V – Interacting with Database

TEACHING HOURS – 12 TOTAL MARKS – 12

Course Outcome (CO-5) – Develop programs using database.

10. Explain PreparedStatement interface in Java-

Explanation:
 An object PreparedStatement represents a precompiled parameterized SQL statement.
 A SQL statement is precompiled and stored in a PreparedStatement object.
 This object can then be used to efficiently execute this statement multiple times.
 For example -
PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ?
WHERE ID = ?");
pstmt.setDouble(1, 35500.00)
pstmt.setInt(2, 1100)

 The question marks '?' as shown above represents placeholder for the input parameter.
 The input parameters are set using setInt(), setString() like methods.
Methods:
 public boolean execute() throws SQLException
o Executes the SQL statement in this PreparedStatement object, which may be any
kind of SQL statement. Some prepared statements return multiple results. If the
first result is ResultSet object then this method returns true;otherwise returns
false.
 public int executeUpdate() throws SQLException
o Executes the SQL statement in this PreparedStatement object, which must be an
SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or
DELETE; or an SQL statement that returns nothing, such as a DDL statement. It
returns the number of rows affected after execution.
 public ResultSet executeQuery() throws SQLException
o Executes the SQL query in this PreparedStatement object and returns the
ResultSet object generated by the query.
 public void setDouble(int parameterIndex, double x) throws SQLException

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 o Sets the designated parameter to the given Java double value. The first parameter
is 1, the second is 2, ... The driver converts this to an SQL DOUBLE value when it
sends it to the database.
 public void setInt(int parameterIndex, int x) throws SQLException
o Sets the designated parameter to the given Java int value. The first parameter is 1,
the second is 2, ... The driver converts this to an SQL INT value when it sends it to
the database.
 public void setString(int parameterIndex, String x) throws SQLException
o Sets the designated parameter to the given Java String value. The first parameter
is 1, the second is 2, ... The driver converts this to an SQL VARCHAR value when it
sends it to the database.
 public void setType(int parameterIndex, Type x) throws SQLException
o Sets the designated parameter to the given Java Type value. The first parameter is
1, the second is 2, ... The driver converts this to an SQL TYPE value when it sends it
to the database.
================================================================
Program to execute DML statements (INSERT INTO, DELETE FROM, UPDATE…SET) using
PreparedStatement using MySQL and Java
================================================================
Aim : To insert single record in student table of college database in MySQL.
import java.sql.*;
import java.io.*;
@SuppressWarnings("deprecation")

class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 // Create a precompiled parameterised statement
String query="insert into student values(?,?,?,?)";
PreparedStatement pstmt = con.prepareStatement(query);

// Read values for rollno, sname, sclass and sbranch

BufferedReader din = new BufferedReader(new
InputStreamReader(System.in));
System.out.println("Enter Roll No");
int rollno = Integer.parseInt(din.readLine());
System.out.println("Enter Name");
String sname = din.readLine();
System.out.println("Enter Class");
String sclass = din.readLine();
System.out.println("Enter Branch");
String sbranch = din.readLine();
String option="Y";

// set the values for input parameters shown by ? mark

pstmt.setInt(1,rollno);
pstmt.setString(2,sname);
pstmt.setString(3,sclass);
pstmt.setString(4,sbranch);

// execute the precompiled statement

int n = pstmt.executeUpdate();
System.out.println(n + " row inserted...");

// Close the connection

pstmt.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
}

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
Aim : To insert multiple records in student table of college database in MySQL.
import java.sql.*;
import java.io.*;
@SuppressWarnings("deprecation")

class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 Connection con = DriverManager.getConnection(url, username, password);

// Create a precompiled parameterised statement

String query="insert into student values(?,?,?,?)";
PreparedStatement pstmt = con.prepareStatement(query);

BufferedReader br = new BufferedReader(new

InputStreamReader(System.in));
String option="Y";
do
{
// Read values for rollno, sname, sclass and sbranch
System.out.println("Enter Roll No");
int rollno = Integer.parseInt(br.readLine());
System.out.println("Enter Name");
String sname = br.readLine();
System.out.println("Enter Class");
String sclass = br.readLine();
System.out.println("Enter Branch");
String sbranch = br.readLine();
// set the values for input parameters shown by ? mark
pstmt.setInt(1,rollno);
pstmt.setString(2,sname);
pstmt.setString(3,sclass);
pstmt.setString(4,sbranch);
// execute the precompiled statement
int n = pstmt.executeUpdate();
System.out.println(n + " row inserted...");
System.out.println("Do you want to continue?(Y-for YES and N-for NO)");
option=br.readLine();
}while(option.equalsIgnoreCase("Y"));

// Close the connection

pstmt.close();
br.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 }
}

Aim : To delete a record of student whose roll number is entered by user from student
table of college database in MySQL.
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
import java.sql.*;
import java.io.*;
@SuppressWarnings("deprecation")

class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

// Create a precompiled parameterised statement

String query="delete from student where rollno=?";
PreparedStatement pstmt = con.prepareStatement(query);

BufferedReader br = new BufferedReader(new

InputStreamReader(System.in));
String option="Y";
do
{
// Read values for rollno, sname, sclass and sbranch
System.out.println("Enter Roll No");
int rollno = Integer.parseInt(br.readLine());
// set the values for input parameters shown by ? mark
pstmt.setInt(1,rollno);
// execute the precompiled statement
int n = pstmt.executeUpdate();
System.out.println(n + " row deleted...");
System.out.println("Do you want to continue?(Y-for YES and N-for NO)");
option=br.readLine();
}while(option.equalsIgnoreCase("Y"));

// Close the connection

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 pstmt.close();
br.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
}

Aim : To change the branch of student from co to computer whose rollno is entered by
user.
import java.sql.*;
import java.io.*;
@SuppressWarnings("deprecation")
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

// Create a precompiled parameterised statement

String query="update student set sbranch=? where rollno=?";
PreparedStatement pstmt = con.prepareStatement(query);

BufferedReader br = new BufferedReader(new

InputStreamReader(System.in));
String option="Y";
do
{
// Read values for rollno and sclass
System.out.println("Enter Roll No");
int rollno = Integer.parseInt(br.readLine());
System.out.println("Enter new Branch Name");
String sbranch = br.readLine();
// set the values for input parameters shown by ? mark in sequence
pstmt.setString(1,sbranch);
pstmt.setInt(2,rollno);
// execute the precompiled statement
int n = pstmt.executeUpdate();
System.out.println(n + " row updated...");
System.out.println("Do you want to continue?(Y-for YES and N-for NO)");
option=br.readLine();
}while(option.equalsIgnoreCase("Y"));

// Close the connection

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 pstmt.close();
br.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
}

================================================================
Program to execute DQL statements (SELECT * FROM) using PreparedStatement using
MySQL and Java
================================================================
Aim : To display all records of student table of college database in MySQL.

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
import java.sql.*;
import java.io.*;
@SuppressWarnings("deprecation")

class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

// Create a precompiled parameterised statement

String query="select * from student";
PreparedStatement pstmt =
con.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDA
TABLE);

BufferedReader br = new BufferedReader(new

InputStreamReader(System.in));
String option="Y";

// execute the precompiled statement and obtain scrollable and updatable

resultset
ResultSet rs = pstmt.executeQuery();

while(rs.next())
{
System.out.print(rs.getInt(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.print(rs.getString(3) + " ");
System.out.print(rs.getString(4) + " ");
System.out.println();
}
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 // Close the connection
pstmt.close();
rs.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
}

Aim : To display first and last records of student table of college database in MySQL.
import java.sql.*;
import java.io.*;
@SuppressWarnings("deprecation")

class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

// Create a precompiled parameterised statement

String query="select * from student";

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 PreparedStatement pstmt =
con.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDA
TABLE);

BufferedReader br = new BufferedReader(new

InputStreamReader(System.in));
String option="Y";

// execute the precompiled statement and obtain scrollable and updatable

resultset
ResultSet rs = pstmt.executeQuery();

System.out.println("First record of student");

if(rs.first())
{
System.out.print(rs.getInt(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.print(rs.getString(3) + " ");
System.out.print(rs.getString(4) + " ");
System.out.println();
}
System.out.println("Last record of student");
if(rs.last())
{
System.out.print(rs.getInt(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.print(rs.getString(3) + " ");
System.out.print(rs.getString(4) + " ");
System.out.println();
}

// Close the connection

pstmt.close();
rs.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
}

Aim : To insert record in student table using ResultSet object and display all information
from student table after insertion.
import java.sql.*;
import java.io.*;
@SuppressWarnings("deprecation")

class Mydatabase {
public static void main(String[] args) {
try {

// create driver and register it

Class.forName("com.mysql.cj.jdbc.Driver");

// Establish the connection with college database

String url = "jdbc:mysql://localhost:3306/college";
String username = "root";
String password = "Admin@123";
Connection con = DriverManager.getConnection(url, username, password);

// Create a precompiled parameterised statement

String query="select * from student";
PreparedStatement pstmt =
con.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDA
TABLE);

// execute the precompiled statement and obtain scrollable and updatable

resultset
ResultSet rs = pstmt.executeQuery();

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 BufferedReader br = new BufferedReader(new
InputStreamReader(System.in));
String option="Y";
do
{

// Read values for rollno, sname, sclass and sbranch

System.out.println("Enter Roll No");
int rollno = Integer.parseInt(br.readLine());
System.out.println("Enter Name");
String sname = br.readLine();
System.out.println("Enter Class");
String sclass = br.readLine();
System.out.println("Enter Branch");
String sbranch = br.readLine();

// Create empty row and move current cursor to it

rs.moveToInsertRow();

// insert values for each column

rs.updateInt(1,rollno);
rs.updateString(2,sname);
rs.updateString(3,sclass);
rs.updateString(4,sbranch);
// insert this empty row in ResultSet
rs.insertRow();

// move the cursor to this newly created row in ResultSet

rs.moveToCurrentRow();
System.out.println(" 1 row inserted...");
System.out.println("Do you want to continue?(Y-for YES and N-for NO)");
option=br.readLine();
}while(option.equalsIgnoreCase("Y"));

// display all the records on the computer screen

// move the cursor to the row just before first row
System.out.println("=====Student Table=======");
rs.beforeFirst();
while(rs.next())
Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate
 {
System.out.print(rs.getInt(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.print(rs.getString(3) + " ");
System.out.print(rs.getString(4) + " ");
System.out.println();
}

// Close the connection

pstmt.close();
br.close();
rs.close();
con.close();
} catch (Exception e) {
System.out.println("Error:"+e);
}
}
}

Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate