Java JDBC Connect to Apache Derby (JavaDB)

📘 Premium Read: Access my best content on Medium member-only articles — deep dives into Java, Spring Boot, Microservices, backend architecture, interview preparation, career advice, and industry-standard best practices.

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (176K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

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.