My simple library

..of useful code



Chapters

OOP Design Principles OOP Concepts Exceptions Multithreading Final Keyword Strings Regex I/O Files Lambda JDBC Networking JSON Java Memory Java Data Structures MySQL C/C++ Unit Testing Linux Git Maven/Gradle REST-API Python Matlab Assembly & uC Java bit manipulation

JDBC Programming Guide

Basic JDBC Example

// Java program to implement a simple JDBC application
import java.sql.*;

public class Geeks {
    public static void main(String[] args)
    {
        // Database URL, username, and password
        String url = "jdbc:mysql://localhost:3306/your_database";
        String username = "your_username";
        String password = "your_password";

        String query = "INSERT INTO students (id, name) VALUES (109, 'bhatt')";

        // Establish JDBC Connection
        try {
            // Load Type-4 Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish connection
            Connection c = DriverManager.getConnection(url, username, password);

            // Create a statement
            Statement st = c.createStatement();

            // Execute the query
            int count = st.executeUpdate(query);
            System.out.println("Number of rows affected by this query: " + count);

            // Close the connection
            st.close();
            c.close();
            System.out.println("Connection closed.");
        }
        catch (ClassNotFoundException e) {
            System.err.println("JDBC Driver not found: " + e.getMessage());
        }
        catch (SQLException e) {
            System.err.println("SQL Error: " + e.getMessage());
        }
    }
}

1. Statement

A Statement object is used for general-purpose access to databases and is useful for executing static SQL statements at runtime.

Syntax
Statement statement = connection.createStatement();

Implementation

Once the Statement object is created, there are three ways to execute it:

  • execute(String SQL): Executes any SQL statements (like SELECT, INSERT, UPDATE or DELETE). Returns true if a ResultSet object is retrieved, false otherwise.
  • executeUpdate(String SQL): Executes SQL statements (like INSERT, UPDATE or DELETE). Returns the number of rows affected by the SQL statement.
  • ResultSet executeQuery(String SQL): Executes the SELECT query. Returns a ResultSet object that contains the data retrieved by the query.

2. Prepared Statement

A PreparedStatement represents a precompiled SQL statement that can be executed multiple times. It accepts parameterized SQL queries, with ? as placeholders for parameters, which can be set dynamically.

Example
String query = "INSERT INTO people(name, age) VALUES(?, ?)";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1,"Ayan");
ptstmt.setInt(2,25);

Implementation

Once the PreparedStatement object is created, there are three ways to execute it:

  • execute(): Returns a boolean value and executes a static SQL statement that is present in the prepared statement object.
  • executeQuery(): Returns a ResultSet from the current prepared statement.
  • executeUpdate(): Returns the number of rows affected by the DML statements such as INSERT, DELETE, and more that is present in the current Prepared Statement.

Complete Example
// Java Program illustrating Prepared Statement in JDBC
import java.sql.*;
import java.util.Scanner;

class Geeks {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Scanner sc = new Scanner(System.in);

            System.out.println("What age do you want to search?? ");
            int age = sc.nextInt();

            Connection con = DriverManager.getConnection(
                "jdbc:mysql:///world", "root", "12345");

            PreparedStatement ps = con.prepareStatement(
                "select name from world.people where age = ?");

            ps.setInt(1, age);
            ResultSet res = ps.executeQuery();

            while (res.next()) {
                System.out.println("Name : " + res.getString(1));
            }
        }
        catch (SQLException e) {
            System.out.println(e);
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

3. Callable Statement

A CallableStatement is used to execute stored procedures in the database. Stored procedures are precompiled SQL statements that can be called with parameters. They are useful for executing complex operations that involve multiple SQL statements.

Syntax
CallableStatement cstmt = con.prepareCall("{call ProcedureName(?, ?)}");

{call ProcedureName(?, ?)}: Calls a stored procedure named ProcedureName with placeholders ? for input parameters.

Methods to Execute

  • execute(): Executes the stored procedure and returns a boolean indicating whether the result is a ResultSet (true) or an update count (false).
  • executeQuery(): Executes a stored procedure that returns a ResultSet.
  • executeUpdate(): Executes a stored procedure that performs an update and returns the number of rows affected.

Example
// Java Program illustrating Callable Statement in JDBC
import java.sql.*;

public class Geeks {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager
              .getConnection("jdbc:mysql:///world", "root", "12345");

            CallableStatement cs = con.prepareCall("{call GetPeopleInfo()}");
            ResultSet res = cs.executeQuery();

            while (res.next()) {
                System.out.println("Name : " + res.getString("name"));
                System.out.println("Age : " + res.getInt("age"));
            }

            res.close();
            cs.close();
            con.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

JDBC Classes and Interfaces

Class/Interfaces Description
DriverManager Manages JDBC drivers and establishes database connections.
Connection Represents a session with a specific database.
Statement Used to execute static SQL queries.
PreparedStatement Precompiled SQL statement, used for dynamic queries with parameters.
CallableStatement Used to execute stored procedures in the database.
ResultSet Represents the result set of a query, allowing navigation through the rows.
SQLException Handles SQL-related exceptions during database operations.