Tuesday, September 9, 2025

MCA 1 : Introduction to JDBC Program for IT Students

 Prepared By :Prof.  Uday Shah (HOD-IT)

 MCA 1 : Introduction to JDBC Program 

1. JDBC Architecture and Drivers

  • JDBC (Java Database Connectivity) is a Java API that allows Java programs to interact with relational databases.
  • It provides a set of interfaces and classes under java.sql and javax.sql packages.
  • JDBC follows a layered architecture:
  • Application → JDBC API → JDBC Driver Manager → JDBC Driver → Database
  • JDBC API: Defines interfaces such as Connection, Statement, PreparedStatement, ResultSet, etc.
  • Driver Manager: Manages the list of database drivers. It matches connection requests with the proper driver.
  • JDBC Driver: A software component that translates JDBC calls into database-specific calls.
  • Types of JDBC Drivers:
    1. Type 1: JDBC-ODBC Bridge Driver – uses ODBC, platform-dependent.
    2. Type 2: Native API Driver – uses native DB client libraries.
    3. Type 3: Network Protocol Driver – communicates with middleware servers.
    4. Type 4: Thin Driver – Pure Java, directly connects to DB (most widely used).
  • Example:
  • Class.forName("com.mysql.cj.jdbc.Driver"); // Load MySQL Driver
  • This architecture ensures platform independence and flexibility.

 

2. Establishing a Database Connection

  • After loading the driver, the next step is to establish a connection.
  • A connection represents a session between the Java app and the database.
  • We use DriverManager.getConnection() method.
  • Syntax:
  • Connection con = DriverManager.getConnection(
  •     "jdbc:mysql://localhost:3306/studentdb", "root", "password");
  • Here:
    • "jdbc:mysql://localhost:3306/studentdb" → Database URL
    • "root" → Username
    • "password" → Password
  • Once connected, we can execute SQL queries.
  • If the connection fails, JDBC throws a SQLException.
  • A connection must always be closed using con.close() to free resources.
  • Multiple connections can be created to interact with different databases.
  • Example check:
  • if (con != null) System.out.println("Connected Successfully!");

 

3. Creating a SQL Query

  • SQL queries are written as strings in Java.
  • Example:
  • String query = "INSERT INTO student(rollno, name) VALUES (1, 'Amit')";
  • SQL queries are of different types:
    • DDL (Data Definition Language): CREATE, DROP, ALTER
    • DML (Data Manipulation Language): INSERT, UPDATE, DELETE
    • DQL (Data Query Language): SELECT
  • Queries can be static or parameterized.
  • Static queries are used with Statement.
  • Parameterized queries (with ?) are used with PreparedStatement.
  • Example of UPDATE query:
  • String update = "UPDATE student SET name='Ravi' WHERE rollno=1";
  • Queries must follow the database’s SQL syntax.
  • Improper queries cause SQLException.

 

4. Executing SQL Queries using Statement

  • The Statement object is used to execute static SQL queries.
  • Created using:
  • Statement stmt = con.createStatement();
  • It provides 3 main methods:
    • executeQuery(sql) → Executes SELECT, returns ResultSet.
    • executeUpdate(sql) → Executes INSERT/UPDATE/DELETE, returns int (affected rows).
    • execute(sql) → Executes any query, returns boolean.
  • Example:
  • ResultSet rs = stmt.executeQuery("SELECT * FROM student");
  • while(rs.next()) {
  •     System.out.println(rs.getInt("rollno")+" "+rs.getString("name"));
  • }
  • Limitation: Cannot handle dynamic values efficiently.
  • Security risk: SQL Injection (malicious SQL through user input).

 

5. Executing SQL Queries using PreparedStatement

  • PreparedStatement is used for dynamic/parameterized queries.
  • Benefits:
    1. Prevents SQL injection.
    2. Reusable for multiple executions.
    3. Faster than Statement (precompiled).
  • Example:
  • PreparedStatement ps = con.prepareStatement(
  •     "INSERT INTO student VALUES(?, ?)");
  • ps.setInt(1, 2);
  • ps.setString(2, "Sita");
  • ps.executeUpdate();
  • Supports different data types: setInt(), setString(), setDouble(), etc.
  • Example (Update):
  • ps = con.prepareStatement("UPDATE student SET name=? WHERE rollno=?");
  • ps.setString(1, "Rahul");
  • ps.setInt(2, 2);
  • ps.executeUpdate();

 

6. Executing SQL Queries using CallableStatement

  • CallableStatement is used to call stored procedures in DB.
  • A stored procedure is a precompiled group of SQL statements stored in the database.
  • Syntax:
  • CallableStatement cs = con.prepareCall("{call getStudent(?)}");
  • cs.setInt(1, 1);
  • ResultSet rs = cs.executeQuery();
  • Example Stored Procedure in MySQL:
  • CREATE PROCEDURE getStudent(IN roll INT)
  • BEGIN
  •   SELECT * FROM student WHERE rollno = roll;
  • END;
  • Advantages:
    1. Faster (precompiled on DB side).
    2. Useful for business logic inside DB.
    3. Supports IN, OUT, INOUT parameters.

 

7. ResultSet

  • The ResultSet object holds query results (like a virtual table).
  • Obtained from executeQuery().
  • Cursor starts before the first row.
  • Common methods:
    • next() → Moves to next row.
    • getInt(colName) → Returns integer value.
    • getString(colName) → Returns string value.
    • first(), last(), previous(), absolute(n).
  • Example:
  • ResultSet rs = stmt.executeQuery("SELECT * FROM student");
  • while(rs.next()) {
  •     System.out.println(rs.getInt(1)+" "+rs.getString(2));
  • }
  • Types of ResultSet:

1.                  TYPE_FORWARD_ONLY (default).

2.                  TYPE_SCROLL_INSENSITIVE (can move both ways, no changes reflected).

3.                  TYPE_SCROLL_SENSITIVE (changes in DB are reflected).

 

8. ResultSetMetaData

  • Provides information about ResultSet columns.
  • Useful when we don’t know table structure.
  • Example:
  • ResultSetMetaData rsmd = rs.getMetaData();
  • int cols = rsmd.getColumnCount();
  • for(int i=1; i<=cols; i++) {
  •     System.out.println(rsmd.getColumnName(i)+" - "+rsmd.getColumnTypeName(i));
  • }
  • Returns details like:
    • Column count
    • Column names
    • Data type of each column
    • Column size
  • Example output:
  • rollno - INT 
  • name - VARCHAR
  • Helps in dynamic applications (like displaying data without hardcoding column names).

 

9. Handling Exceptions in JDBC

  • JDBC operations may throw SQLException.
  • Always handle using try-catch-finally.
  • Example:
  • try {
  •     Connection con = DriverManager.getConnection(...);
  • } catch(SQLException e) {
  •     System.out.println("Error: " + e.getMessage());
  • } finally {
  •     con.close();
  • }
  • SQLException provides details:
    • getMessage() → Error message
    • getSQLState() → SQL error code
    • getErrorCode() → Vendor-specific error code
  • Use finally block to close resources (Connection, Statement, ResultSet).
  • Java 7 introduced try-with-resources for auto-closing.

 

10. Transactions in JDBC

  • A transaction is a group of SQL statements executed as one unit.
  • By default, JDBC uses auto-commit mode (each query is committed instantly).
  • To use transactions:
  • con.setAutoCommit(false);
  • stmt.executeUpdate("INSERT INTO student VALUES(5, 'Rohan')");
  • stmt.executeUpdate("UPDATE student SET name='Mohan' WHERE rollno=5");
  • con.commit(); // both succeed together
  • If any query fails:
  • con.rollback(); // undo changes
  • Ensures ACID properties (Atomicity, Consistency, Isolation, Durability).
  • Used in banking systems, booking systems, e-commerce.

 

11. Savepoints in JDBC

  • Savepoints are checkpoints inside a transaction.
  • Allows rolling back to a specific point without undoing the entire transaction.
  • Example:
  • con.setAutoCommit(false);
  • Savepoint sp1 = con.setSavepoint("step1");
  • stmt.executeUpdate("INSERT INTO student VALUES(6, 'Anita')");
  • Savepoint sp2 = con.setSavepoint("step2");
  • stmt.executeUpdate("INSERT INTO student VALUES(7, 'Karan')");
  • con.rollback(sp2); // rollback only Karan
  • con.commit();
  • Benefits:
    • Greater control in complex transactions.
    • Allows partial rollbacks.
  • Used in systems where multiple operations happen in one transaction.

 

 


Posted by at