Connect to Apache Derby database
Tags: database, java, tutorial
Published: 2020-09-03 14:54:45

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](http://xenia.sote.hu/ftp/mirrors/www.apache.org/db/derby/db-derby-10.15.2.0/). 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](https://mvnrepository.com/artifact/org.apache.derby/derby).

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