Java JDBC Connect to Apache Derby (JavaDB)

Apache Derby, also known as JavaDB, is an open-source relational database implemented entirely in Java. It can be embedded within Java applications or run in a client-server mode. In this tutorial, we will learn how to connect a Java application to an Apache Derby database using JDBC and perform basic CRUD operations.

Prerequisites

• Apache Derby installed.
• Apache Derby JDBC driver added to your project.

Step-by-Step Guide

1. Set Up Dependencies

Ensure you have the Apache Derby JDBC driver in your classpath. If you are using Maven, add the following dependency to your pom.xml:

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
    <version>10.15.2.0</version>
</dependency>

2. Establish a Database Connection

First, we need to establish a connection to the Apache Derby database using the DriverManager class.

3. Perform Basic CRUD Operations

We will perform Create, Read, Update, and Delete operations on a table named books.

Example Code

Below is the complete example code demonstrating how to connect to an Apache Derby database and perform CRUD operations.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DerbyExample {

    private static final String JDBC_URL = "jdbc:derby:myDB;create=true";
    private static final String USER = "";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD)) {
            System.out.println("Connected to the database");

            // Create table
            createTable(connection);

            // Insert records
            insertRecord(connection, 1, "Effective Java", "Joshua Bloch", 2008);
            insertRecord(connection, 2, "Java Concurrency in Practice", "Brian Goetz", 2006);

            // Select records
            selectRecords(connection);

            // Update record
            updateRecord(connection, 1, "Effective Java (2nd Edition)");

            // Delete record
            deleteRecord(connection, 2);

            // Select records again to see the changes
            selectRecords(connection);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createTable(Connection connection) throws SQLException {
        String createTableSQL = "CREATE TABLE books (" +
                "id INT PRIMARY KEY, " +
                "title VARCHAR(255), " +
                "author VARCHAR(255), " +
                "year INT)";
        try (Statement stmt = connection.createStatement()) {
            stmt.execute(createTableSQL);
            System.out.println("Table 'books' created successfully");
        }
    }

    private static void insertRecord(Connection connection, int id, String title, String author, int year) throws SQLException {
        String insertSQL = "INSERT INTO books (id, title, author, year) VALUES (?, ?, ?, ?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertSQL)) {
            pstmt.setInt(1, id);
            pstmt.setString(2, title);
            pstmt.setString(3, author);
            pstmt.setInt(4, year);
            pstmt.executeUpdate();
            System.out.println("Inserted record: " + title);
        }
    }

    private static void selectRecords(Connection connection) throws SQLException {
        String selectSQL = "SELECT * FROM books";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(selectSQL)) {
            System.out.println("Selecting records from 'books':");
            while (rs.next()) {
                int id = rs.getInt("id");
                String title = rs.getString("title");
                String author = rs.getString("author");
                int year = rs.getInt("year");
                System.out.println(id + ", " + title + ", " + author + ", " + year);
            }
        }
    }

    private static void updateRecord(Connection connection, int id, String newTitle) throws SQLException {
        String updateSQL = "UPDATE books SET title = ? WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(updateSQL)) {
            pstmt.setString(1, newTitle);
            pstmt.setInt(2, id);
            pstmt.executeUpdate();
            System.out.println("Updated record with id " + id + " to title " + newTitle);
        }
    }

    private static void deleteRecord(Connection connection, int id) throws SQLException {
        String deleteSQL = "DELETE FROM books WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(deleteSQL)) {
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            System.out.println("Deleted record with id " + id);
        }
    }
}

Explanation

1. Apache Derby Connection:

   • DriverManager.getConnection(JDBC_URL, USER, PASSWORD) is used to establish a connection to the Derby database.
   • The URL jdbc:derby:myDB;create=true creates a new database named myDB if it doesn't exist.

2. Create Table:

   • The createTable() method creates a new table named books with columns id, title, author, and year.

3. Insert Records:

   • The insertRecord() method inserts a new record into the books table.

4. Select Records:

   • The selectRecords() method retrieves all records from the books table and prints them.

5. Update Record:

   • The updateRecord() method updates the title of a record in the books table.

6. Delete Record:

   • The deleteRecord() method deletes a record from the books table.

Output

Running the code will produce output similar to the following:

Connected to the database
Table 'books' created successfully
Inserted record: Effective Java
Inserted record: Java Concurrency in Practice
Selecting records from 'books':
1, Effective Java, Joshua Bloch, 2008
2, Java Concurrency in Practice, Brian Goetz, 2006
Updated record with id 1 to title Effective Java (2nd Edition)
Deleted record with id 2
Selecting records from 'books':
1, Effective Java (2nd Edition), Joshua Bloch, 2008

Conclusion

Connecting a Java application to an Apache Derby (JavaDB) database using JDBC is straightforward. This tutorial demonstrated how to establish a connection and perform basic CRUD operations on a Derby database. The approach can be adapted for more complex operations and use cases.