Java Database Connectivity (JDBC) Tutorial with Example

What is Java Database Connectivity (JDBC)?

JDBC is a Java-based API that allows developers to connect and execute SQL queries with any relational database management system (RDBMS). It is database-independent, meaning you can use JDBC to interact with MySQL, Oracle, PostgreSQL, and more without changing your Java code significantly.

Why Use JDBC?

• Database Independence: JDBC works with any RDBMS, making it versatile.
• Ease of Use: Provides a simple and consistent interface for database operations.
• Performance: Enables efficient data retrieval and manipulation.
• Security: Supports secure database connections and transactions.

Prerequisites for JDBC

Before diving into JDBC, ensure you have the following:

1. Install an RDBMS Application: MySQL, Oracle, or any other relational database.
2. Create a Database (Schema): Set up a database with a name of your choice.
3. Create a Table: Define a table structure within the database.
4. Insert Data: Populate the table with sample data for testing.

Steps to Work with JDBC

To interact with a database using JDBC, follow these steps:

1. Load the JDBC Driver: Register the database driver.
2. Establish a Connection: Connect to the database using the DriverManager class.
3. Execute SQL Queries: Use Statement or PreparedStatement to run queries.
4. Process Results: Retrieve and process data using the ResultSet object.
5. Close JDBC Objects: Release resources by closing connections, statements, and result sets.

Implementing a Basic JDBC Example

Below is a simple Java program demonstrating JDBC connectivity with an Oracle database:

package jdbc;
import java.sql.*;

class MyFirstCode {
    public static void main(String args[]) {
        try {
            // Step 1: Load the JDBC Driver
            Class.forName("oracle.jdbc.OracleDriver");
            System.out.println("Driver loaded successfully");

            // Step 2: Establish the Database Connection
            Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@//shubham-pc:1521/orcl", "scott", "tiger");

            // Step 3: Create a Statement Object
            Statement st = conn.createStatement();

            // Step 4: Execute SQL Query
            ResultSet rs = st.executeQuery("SELECT ename, sal FROM emp");

            // Step 5: Process the ResultSet
            int total = 0;
            int count = 0;
            while (rs.next()) {
                String name = rs.getString(1);
                int salary = rs.getInt(2);
                System.out.println(name + "\t" + salary);
                total += salary;
                ++count;
            }
            System.out.println("Average salary is " + (float) total / count);

            // Step 6: Close JDBC Objects
            rs.close();
            st.close();
            conn.close();
        } catch (SQLException ex2) {
            System.out.println("Failed to connect to the database: " + ex2.getMessage());
        } catch (ClassNotFoundException ex1) {
            System.out.println("Failed to load the driver: " + ex1.getMessage());
        }
    }
}
        

Key Points to Remember

• Add JDBC Driver JAR File: To use JDBC, include the database-specific JAR file in your project. Right-click on your Java project, select Build Path > Add External Archives, and choose the JAR file.
• Exception Handling: Always handle SQLException and ClassNotFoundException to ensure robust code.
• Resource Management: Close all JDBC objects (Connection, Statement, ResultSet) to avoid memory leaks.

Conclusion

Java Database Connectivity (JDBC) is an essential tool for Java developers to interact with relational databases. By following the steps outlined in this guide, you can easily connect to a database, execute queries, and process results. Whether you're building a small application or a large-scale system, JDBC provides the flexibility and performance you need.

For more tutorials and updates, follow us on Instagram and Facebook. Happy coding!