How to connect to Derby database using a JDBC program?

Apache Derby is a Relational Database Management System which is fully based on (written/implemented in) Java programming language. It is an open source database developed by Apache Software Foundation.

Installing derby:

Follow the steps given below to install derby:

• Visit the home page of Apache Derby home page https://db.apache.org/derby/. Click the Download tab.

• Select and click on the link of the latest version of Apache Derby.

• On clicking the selected link, you will be redirected to the Distributions page of apache derby. If you observe here, derby provides distributions namely, db-derby-bin, db-derbylib.zip, db-derby-lib-debug.zip, and db-derby-src.zip.

• Download the db-derby-bin folder. Copy its contents to a separate folder where you wanted to install Apache Derby. (for example, say C:\Derby)

Now, to work with Derby,

• Make sure that you already have set the JAVA_HOME variable by passing the location of bin folder of Java Installation folder, and include the JAVA_HOME/bin in the PATH variable.
• Create a new environment variable, DERBY_HOME with value C:\Derby.
• The bin folder of db-derby-bin distributions (we changed it as C:\Derby\bin) contains all the required jar files.

Example

Following JDBC program establishes connection with Apache derby database, creates a table named employeedata, inserts records into it, retrieves and displays the contents of the table.

public class InsertData {
   public static void main(String args[]) throws Exception {
      //Registering the driver
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
      //Getting the Connection object
      String URL = "jdbc:derby:mydatabs;create=true";
      Connection conn = DriverManager.getConnection(URL);
      //Creating the Statement object
      Statement stmt = conn.createStatement();
      //Creating a table in Derby database
      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");
      //Inserting data
      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");
      //Retrieving data
      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();
      }
   }
}

Output

Table created
Values inserted
Contents of the table EmployeeData table:
ID: 1, Name: Amit, Salary: 30000, Location: Hyderabad
ID: 2, Name: Kalyan, Salary: 40000, Location: Vishakhapatnam
ID: 3, Name: Renuka, Salary: 50000, Location: Delhi
ID: 4, Name: Archana, Salary: 15000, Location: Mumbai
ID: 5, Name: Trupthi, Salary: 45000, Location: Kochin
ID: 6, Name: Suchatra, Salary: 33000, Location: Pune
ID: 7, Name: Rahul, Salary: 39000, Location: Lucknow
ID: 8, Name: Trupthi, Salary: 45000, Location: Kochin