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

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>

Create a new class and initialize connection with connection string.

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

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))";

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;  
      }  
    }  
}