DEV Community

Cover image for Connect to Apache Derby database
Akos Kovacs
Akos Kovacs

Posted on

1

Connect to Apache Derby database

I would like to show a simple example, how you can set up Apache Derby on your local Windows machine and connect to it with Java code to write and read data. It is a lightweight relational database management system.

Windows side configuration and steps
You can download it here. Choose one of the compressed binary packages. After that it is needed to be unzipped. A new environment variable, named DERBY_HOME needs to be set to the extracted folder, where Derby bin distribution is located.

set  DERBY_HOME=c:\Derby
Enter fullscreen mode Exit fullscreen mode

Once DERBY_HOME environment variable is set and it is included also in the PATH environment variable, shortened commands are available to use the Derby tools.
To start the server you need to execute /bin/startNetworkServer.bat file, which will open a command prompt window. When you close this, server will be stopped. By default it is running on port number 1527.

Java side steps
Create a new Maven project and add the following dependencies to pom.xml file. You can check for newer versions on MVNrepository.

<dependency>  
 <groupId>org.apache.derby</groupId>  
 <artifactId>derby</artifactId>  
 <version>10.15.2.0</version>  
</dependency>  
<dependency>  
 <groupId>org.apache.derby</groupId>  
 <artifactId>derbyclient</artifactId>  
 <version>10.15.2.0</version>  
</dependency>  
<dependency>  
 <groupId>org.apache.derby</groupId>  
 <artifactId>derbytools</artifactId>  
 <version>10.15.2.0</version>  
</dependency>  
<dependency>  
 <groupId>org.apache.derby</groupId>  
 <artifactId>derbynet</artifactId>  
 <version>10.15.2.0</version>  
</dependency>
Enter fullscreen mode Exit fullscreen mode

Create a new class and initialize connection with connection string.

Connection connect = DriverManager.getConnection("jdbc:derby://localhost:1527/testdb" + System.currentTimeMillis() + ";create=true");
Enter fullscreen mode Exit fullscreen mode

I use System.currentTimeMillis() method to create a differently named database in each execution.
After that you need to write necessary SQL queries and store those in String objects.

String query = "CREATE TABLE EmployeeData( "  
  + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "  
  + "Name VARCHAR(255), "  
  + "Salary INT NOT NULL, "  
  + "Location VARCHAR(255), "  
  + "PRIMARY KEY (Id))";
Enter fullscreen mode Exit fullscreen mode

These will be executed as Statements.
You can check also the whole code, which is needed to establish a database connection, create a database, a table and get data from it.

import java.sql.*;  

public class HandleDBExample {  
    public static void main(String args[]) throws Exception {  
        try {  
            Class.forName("org.apache.derby.jdbc.ClientDriver");  
            Connection connect = DriverManager.getConnection("jdbc:derby://localhost:1527/testdb" + System.currentTimeMillis() + ";create=true");  
            Statement stmt = connect.createStatement();  

            System.out.println("Database info: " + connect.getMetaData().getURL() + " " + connect.getMetaData().getDatabaseProductName());  

            String query = "CREATE TABLE EmployeeData( "  
            + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "  
            + "Name VARCHAR(255), "  
            + "Salary INT NOT NULL, "  
            + "Location VARCHAR(255), "  
            + "PRIMARY KEY (Id))";  

            stmt.execute(query);  
            System.out.println("Table created");  

            query = "INSERT INTO EmployeeData("  
            + "Name, Salary, Location) VALUES "  
            + "('Amit', 30000, 'Hyderabad'), "  
            + "('Kalyan', 40000, 'Vishakhapatnam'), "  
            + "('Renuka', 50000, 'Delhi'), "  
            + "('Archana', 15000, 'Mumbai'), "  
            + "('Trupthi', 45000, 'Kochin'), "  
            + "('Suchatra', 33000, 'Pune'), "  
            + "('Rahul', 39000, 'Lucknow'), "  
            + "('Trupthi', 45000, 'Kochin')";  

            stmt.execute(query);  
            System.out.println("Values inserted");  

            ResultSet rs = stmt.executeQuery("Select * from EmployeeData");  
            System.out.println("Contents of the table EmployeeData table:");  
            while(rs.next()) {  
                  System.out.print("ID: "+rs.getInt("ID")+", ");  
                  System.out.print("Name: "+rs.getString("Name")+", ");  
                  System.out.print("Salary: "+rs.getInt("Salary")+", ");  
                  System.out.print("Location: "+rs.getString("Location"));  
                  System.out.println();  
           }  
           connect.close();  
      } catch (Exception e) {  
            throw e;  
      }  
    }  
}
Enter fullscreen mode Exit fullscreen mode

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

The Most Contextual AI Development Assistant

Pieces.app image

Our centralized storage agent works on-device, unifying various developer tools to proactively capture and enrich useful materials, streamline collaboration, and solve complex problems through a contextual understanding of your unique workflow.

👥 Ideal for solo developers, teams, and cross-company projects

Learn more