Dr R Anurekha: 2026

JDBC CallableStatement

Database : College

Table name : Department

Fields : DeptCode INT & DeptName VARCHAR(20)

Stored Procedure

DELIMITER //

CREATE PROCEDURE getAll()

BEGIN

SELECT *

FROM Department;

END //

DELIMITER ;

// JDBC

import java.sql.*;

class JDBC_Demo

{

public static void main(String as[])

{

try

{

Connection conc = DriverManager.getConnection("jdbc:mysql://localhost:3306/College","root", "JP@2026");

System.out.println("Database Connected");

CallableStatement cs = conc.prepareCall("{call getAll()}");

ResultSet rs = cs.executeQuery();

while(rs.next())

{

System.out.println("Department Code = "+rs.getInt("DeptCode"));

System.out.println("Department Name = "+rs.getString(2));

}

conc.close();

}

catch(SQLException e)

{

System.out.println(e);

}

catch(Exception e)

{

System.out.println(e);

}

finally

{

System.out.println("End of Program.");

}

C>java JDBC_Demo

Database Connected

Department Code = 205

Department Name = IT

Department Code = 104

Department Name = CSE

Department Code = 105

Department Name = EEE

Department Code = 106

Department Name = ECE

Department Code = 111

Department Name = MECH

Department Code = 159

Department Name = CDS

End of Program.

JDBC CallableStatements

Database : College

Table name : Department

Fields : DeptCode INT & DeptName VARCHAR(20)

Stored Procedure

DELIMITER //

CREATE PROCEDURE getDepartment(IN val INT)

BEGIN

SELECT *

FROM Department

WHERE DeptCode = val;

END //

DELIMITER ;

// JDBC

import java.sql.*;

class JDBC_Demo

{

public static void main(String as[])

{

try

{

Connection conc = DriverManager.getConnection("jdbc:mysql://localhost:3306/College","root", "JP@2026");

System.out.println("Database Connected");

CallableStatement cs = conc.prepareCall("{call getDepartment(159)}");

ResultSet rs = cs.executeQuery();

if(rs.next())

{

System.out.println("Department Code = "+rs.getInt("DeptCode"));

System.out.println("Department Name = "+rs.getString(2));

}

else

System.out.println("No record found for department code.");

conc.close();

}

catch(SQLException e)

{

System.out.println(e);

}

catch(Exception e)

{

System.out.println(e);

}

finally

{

System.out.println("End of Program.");

}

Sample Output

>javac JDBC_Demo.java

>java JDBC_Demo

Database Connected

Department Code = 159

Department Name = CDS

End of Program.

Java Statements

(Statement, PreparedStatement, CallableStatement)

Database : College

Table name : Department

Fields : DeptCode INT & DeptName VARCHAR(20)

// JDBC

import java.sql.*;

class JDBC_Demo

{

public static void main(String as[])

{

Connection conc;

try

{

String url = "jdbc:mysql://localhost:3306/College";

String usrname = "root";

String pwd = "JP@2026";

conc = DriverManager.getConnection(

url, usrname, pwd);

System.out.println("Database Connected");

Statement stat = conc.createStatement();

ResultSet rs1 = stat.executeQuery("Select * from Department where DeptCode=205");

if(rs1.next()) {

System.out.println("Statement");

System.out.println("Department Code = "+rs1.getInt("DeptCode"));

System.out.println("Department Name = "+rs1.getString(2));

}

PreparedStatement ps = conc.prepareStatement("SELECT * FROM Department WHERE DeptCode = ?");

ps.setInt(1, 205);

ResultSet rs2 = ps.executeQuery();

if(rs2.next()) {

System.out.println("Prepared statement");

System.out.println("Department Code = "+rs2.getInt("DeptCode"));

System.out.println("Department Name = "+rs2.getString(2));

}

CallableStatement cs =

conc.prepareCall("{call getDepartment(205)}");

ResultSet rs3 = cs.executeQuery();

if(rs3.next()) {

System.out.println("Callable statement");

System.out.println("Department Code = "+rs3.getInt("DeptCode"));

System.out.println("Department Name = "+rs3.getString(2));

}

conc.close();

}

catch(SQLException e)

{

System.out.println(e);

}

catch(Exception e)

{

System.out.println(e);

}

finally

{

System.out.println("End Of Program");

}

Procedure in Database

Procedure:

DELIMITER //

CREATE PROCEDURE getDepartment(IN val INT)

BEGIN

SELECT *

FROM Department

WHERE DeptCode = val;

END //

DELIMITER ;

Sample Output

>javac JDBC_Demo.java

>java JDBC_Demo

Database Connected

Statement

Department Code = 205

Department Name = IT

Prepared statement

Department Code = 205

Department Name = IT

Callable statement

Department Code = 205

Department Name = IT

End Of Program

Java Statement

Database : College

Table name : Department

Fields : DeptCode INT & DeptName VARCHAR(20)

// JDBC

import java.sql.*;

class JDBC_Demo

{

public static void main(String as[])

{

Connection conc;

try

{

String url = "jdbc:mysql://localhost:3306/College";

String usrname = "root";

String pwd = "JP@2026";

conc = DriverManager.getConnection(

url, usrname, pwd);

System.out.println("Database Connected");

Statement stat = conc.createStatement();

stat.executeUpdate("create table dept(dc INT, dn varchar(20), ds INT);");

stat.executeUpdate("insert into dept values (205, 'IT',57), (104,'eee',45), (105,'ece',65), (111,'mech',60), (107,'civil',59);");

ResultSet rs = stat.executeQuery("Select * from dept;");

while(rs.next())

{

int code = rs.getInt("dc");

String name = rs.getString("dn");

int count = rs.getInt("ds");

System.out.println("Code = "+code);

System.out.println("Name = "+name);

System.out.println("Count = "+count);

}

conc.close();

}

catch(SQLException e)

{

System.out.println(e);

}

catch(Exception e)

{

System.out.println(e);

}

finally

{

System.out.println("EOP");

}

Prepared Statement

Database : College

Table name : Department

Fields : DeptCode INT & DeptName VARCHAR(20)

// JDBC

import java.sql.*;

class JDBC_Demo

{

public static void main(String as[])

{

try

{

Connection conc = DriverManager.getConnection("jdbc:mysql://localhost:3306/College","root", "JP@2026");

System.out.println("Database Connected");

PreparedStatement ps = conc.prepareStatement("Select * from Department where DeptCode=?");

ps.setInt(1,205);

ResultSet rs = ps.executeQuery();

if(rs.next())

{

System.out.println("Department Code = "+rs.getInt("DeptCode"));

System.out.println("Department Name = "+rs.getString("DeptName"));

}

else

System.out.println("No Deptartment found.");

conc.close();

}

catch(SQLException e)

{

System.out.println(e);

}

catch(Exception e)

{

System.out.println(e);

}

finally

{

System.out.println("End of Program.");

}

Sample Output

>javac JDBC_Demo.java

>java JDBC_Demo

Database Connected

Department Code = 205

Department Name = IT

End of Program.

Java Statement

Database : College

Table name : Department

Fields : DeptCode INT & DeptName VARCHAR(20)

// JDBC

import java.sql.*;

class JDBC_Demo

{

public static void main(String as[])

{

Connection conc;

try

{

conc = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/College",

"root",

"JP@2026"

);

System.out.println("Database Connected");

String sq = "Select * from Department;";

Statement stat = conc.createStatement();

ResultSet rs = stat.executeQuery(sq);

while(rs.next())

{

int dc = rs.getInt("DeptCode");

String dn = rs.getString("DeptName");

System.out.println("Code = "+dc);

System.out.println("Name = "+dn);

}

conc.close();

}

catch(SQLException e)

{

System.out.println(e);

}

catch(Exception e)

{

System.out.println(e);

}

finally

{

System.out.println("End Of Program");

}

Sample Output

>javac JDBC_Demo.java

>java JDBC_Demo

Database Connected

Code = 205

Name = IT

Code = 104

Name = CSE

Code = 105

Name = EEE

Code = 106

Name = ECE

Code = 111

Name = MECH

Code = 159

Name = CDS

End Of Program

Prepared statements

Database : College

Table name : Department

Fields : DeptCode INT & DeptName VARCHAR(20)

// JDBC

import java.sql.*;

import java.util.*;

class JDBC_Demo

{

public static void main(String as[])

{

Scanner sc = new Scanner(System.in);

try

{

Connection conc = DriverManager.getConnection("jdbc:mysql://localhost:3306/College","root", "JP@2026");

System.out.println("Database Connected");

PreparedStatement ps = conc.prepareStatement("Select * from Department where DeptCode=?");

for(int i=0;i<3;i++)

{

System.out.print("Enter Department Code to search : ");

int dc = sc.nextInt();

ps.setInt(1,dc);

ResultSet rs = ps.executeQuery();

if(rs.next())

System.out.println("Deptartment = "+rs.getString(2));

else

System.out.println("No Deptartment found.");

}

conc.close();

}

catch(SQLException e)

{

System.out.println(e);

}

catch(Exception e)

{

System.out.println(e);

}

finally

{

System.out.println("EOP");

}

Sample Output:

>javac JDBC_Demo.java

>java JDBC_Demo

Database Connected

Enter Department Code to search : 205

Deptartment = IT

Enter Department Code to search : 105

Deptartment = EEE

Enter Department Code to search : 1234

No Deptartment found.

EOP

JDBC Statements

JDBC Statements

Types of Statements in JDBC

JDBC provides three types of statements for executing SQL commands:

Statement
PreparedStatement
CallableStatement

1. Statement

Statement is used to execute simple and static SQL statements that do not require input parameters. The SQL query is written directly as a string and sent to the database for execution. It is commonly used for basic SELECT, INSERT, UPDATE, and DELETE operations.

Example:

Statement stat = conc.createStatement();

Stat.executeUpdate(“INSERT into Department values(205, ‘IT’)”);

ResultSet rs = stat.executeQuery("SELECT * FROM Department where deptCode=205");

2. PreparedStatement

PreparedStatement is used to execute parameterized SQL statements. It uses ? as placeholders for values, which are supplied using methods such as setInt() and setString(). It is useful when the same SQL statement needs to be executed with different values.

Example:

PreparedStatement ps = conc.prepareStatement("SELECT * FROM Department WHERE DeptCode = ?");

ps.setInt(1, 205);

ResultSet rs = ps.executeQuery();

3. CallableStatement

CallableStatement is used to execute stored procedures in a database. A stored procedure is a set of SQL statements stored and executed on the database server. It can accept input parameters and can also return output parameters.

Step 1: Create a stored procedure in MySQL

Step 2: Call the procedure from Java

Procedure:

DELIMITER //

CREATE PROCEDURE getDepartment(IN val INT)

BEGIN

SELECT *

FROM Department

WHERE DeptCode = val;

END //

DELIMITER ;

CallableStatement cs = conc.prepareCall("{call getDepartment(205)}");

ResultSet rs = cs.executeQuery();