10.1 JDBC Basics Explained

Java Database Connectivity (JDBC) is a Java API that allows Java applications to interact with databases. JDBC provides a set of interfaces and classes to connect to databases, execute SQL queries, and manage database transactions. Understanding JDBC is crucial for developing database-driven applications in Java SE 11.

Key Concepts

1. JDBC Driver

A JDBC driver is a software component that enables Java applications to interact with a database. There are four types of JDBC drivers: Type 1 (JDBC-ODBC Bridge), Type 2 (Native-API Driver), Type 3 (Network-Protocol Driver), and Type 4 (Pure Java Driver). The Type 4 driver is the most commonly used as it is written entirely in Java and provides the best performance.

Example

        // Loading the MySQL JDBC driver
        Class.forName("com.mysql.cj.jdbc.Driver");
    

2. Connection

The Connection interface represents a connection to a database. It is used to create statements, manage transactions, and perform other database operations. A connection is established using the DriverManager.getConnection() method, which takes a database URL, username, and password as parameters.

Example

        // Establishing a connection to a MySQL database
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";
        Connection connection = DriverManager.getConnection(url, username, password);
    

3. Statement

The Statement interface is used to execute static SQL queries. It provides methods to execute SQL statements such as executeQuery() for SELECT queries and executeUpdate() for INSERT, UPDATE, and DELETE queries.

Example

        // Executing a SELECT query
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");
    

4. PreparedStatement

The PreparedStatement interface is a subinterface of Statement that allows executing precompiled SQL queries with parameters. It is more efficient and secure than Statement as it prevents SQL injection attacks.

Example

        // Executing a parameterized INSERT query
        String sql = "INSERT INTO employees (name, age) VALUES (?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "John Doe");
        preparedStatement.setInt(2, 30);
        preparedStatement.executeUpdate();
    

5. ResultSet

The ResultSet interface represents the result set of a database query. It provides methods to navigate through the result set and retrieve data. The next() method is used to move the cursor to the next row, and various getXXX() methods are used to retrieve column values.

Example

        // Retrieving data from a ResultSet
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            System.out.println("Name: " + name + ", Age: " + age);
        }
    

6. Transaction Management

JDBC supports transaction management using the Connection interface. Transactions ensure that a set of database operations are executed as a single unit of work. The setAutoCommit() method is used to enable or disable auto-commit mode, and the commit() and rollback() methods are used to commit or rollback transactions.

Example

        // Managing a transaction
        connection.setAutoCommit(false);
        try {
            Statement statement = connection.createStatement();
            statement.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
            statement.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
        }
    

Examples and Analogies

Think of JDBC as a bridge that connects your Java application to a database. The JDBC driver is like the foundation of the bridge, ensuring a stable connection. The Connection object is like the entrance to the bridge, allowing you to access the database. The Statement and PreparedStatement objects are like vehicles on the bridge, carrying your SQL queries to the database and bringing back results. The ResultSet is like a container that holds the data returned by the database. Transaction management is like a traffic controller, ensuring that all operations on the bridge are completed successfully or not at all.

By mastering JDBC, you can create powerful and efficient database-driven applications in Java SE 11, ensuring seamless interaction between your Java code and the database.