Prepared statements

 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:

1. Statement
2. PreparedStatement
3. 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();

 

JDBC Drivers

 

JDBC Drivers

 

 

JDBC stands for Java Database Connectivity. It is a Java API that lets a Java application connect to a database, run SQL queries, and read or modify data in a database.

 

Interfaces and Classes of JDBC API

• DriverManager class − used to load a SQL driver to connect to database.
• Connection interface − used to make a connection to the database using database connection string and credentials.
• Statement interface − used to make a query to the database.
• PreparedStatement interface − used for a query with placeholder values.
• CallableStatement interface − used to called stored procedure or functions in database.
• ResultSet interface − represents the query results obtained from the database.
• ResultSetMetaData interface − represents the metadata of the result set.
• BLOB class − represents binary data stored in BLOB format in database table.
• CLOB class − represents text data like XML stored in database table

 

JDBC Drivers

The JDBC driver acts as a translator between Java/JDBC commands and the database's own communication protocol. JDBC drivers implement the defined interfaces in the JDBC API, for interacting with the database server.

 

Types of JDBC Drivers

There are 4 types of JDBC drivers:

Type	Name	Working
Type 1	JDBC-ODBC Bridge Driver	JDBC → ODBC → Database
Type 2	Native-API Driver	JDBC → Native Database API → Database
Type 3	Network Protocol Driver	JDBC → Middleware Server → Database
Type 4	Thin / Pure Java Driver	JDBC → Database directly

 

 

Type 1 – JDBC-ODBC Bridge Driver

The Type 1 JDBC driver acts as a bridge between JDBC and ODBC. It converts JDBC calls made by a Java program into ODBC calls, which are then sent to the database. Thus, the communication takes place through JDBC → ODBC → Database. It is an older type of driver and is no longer used in modern Java applications.

 

 

Type 2 – Native-API Driver

The Type 2 JDBC driver converts JDBC calls into the native API calls of a particular database. It uses database-specific native libraries installed on the system to communicate with the database. Thus, the communication takes place through JDBC → Native API → Database. The driver contains both Java code and native code.

 

 

Type 3 – Network Protocol Driver

The Type 3 JDBC driver uses a middleware server to communicate with the database. The Java application sends JDBC calls to the middleware server, which converts them into the appropriate database-specific requests. Thus, the communication takes place through JDBC → Middleware Server → Database. The middleware acts as an intermediate layer between the Java application and the database.

 

 

Type 4 – Thin / Pure Java Driver

The Type 4 JDBC driver is a pure Java driver that communicates directly with the database using its network protocol. It converts JDBC calls into the database-specific protocol without requiring an intermediate middleware server. Thus, the communication takes place through JDBC → Type 4 Driver → Database. MySQL Connector/J is an example of a Type 4 JDBC driver.

 

JDBC API

JDBC API

 

JDBC API (Java Database Connectivity API) is a collection of classes and interfaces provided by Java that allows a Java program to communicate with databases.

The JDBC API provides standard methods for connecting to a database, executing SQL statements, retrieving results, and handling database errors. It allows Java programs to work with different databases without changing the basic JDBC programming approach.

The main JDBC API components are:

Component	Purpose
DriverManager	Establishes a connection with the database
Connection	Represents the connection between Java and the database
Statement	Executes SQL statements
PreparedStatement	Executes parameterized SQL statements
ResultSet	Represents data returned by a SELECT query
SQLException	Represents database-related errors

 

1.   DriverManager

DriverManager is a class in the java.sql package that manages JDBC drivers. It is mainly used to establish a connection between a Java application and a database. The getConnection() method is used with the database URL, username, and password. It identifies a suitable JDBC driver and returns a Connection object.

 

2.   Connection

Connection is an interface that represents an active connection between a Java application and a database. It is obtained using the DriverManager.getConnection() method. The Connection object is used to create Statement and PreparedStatement objects for executing SQL commands. After completing the database operations, the connection should be closed.

 

3.   Statement

Statement is an interface used to execute SQL statements from a Java program. A Statement object is created using the Connection object. Methods such as executeQuery() and executeUpdate() are used to execute SQL commands. It is commonly used when the SQL statement does not require dynamic input values.

 

4.   PreparedStatement

PreparedStatement is an interface used to execute parameterized SQL statements. It allows placeholders (?) to be used in an SQL statement and values to be supplied separately. Methods such as setInt() and setString() are used to provide the values. It is commonly used when SQL statements need to be executed with different input values.

 

5.   ResultSet

ResultSet is an interface that represents the data returned by a SELECT query. It contains the rows and columns retrieved from the database. The next() method is used to move through the rows one at a time. Methods such as getInt() and getString() are used to retrieve column values.

 

6.   SQLException

SQLException is an exception class used to represent errors that occur during database operations. It can occur while connecting to the database, executing SQL statements, or retrieving results. JDBC programs generally handle it using try-catch or throws. The exception provides information about the database error.