Derby: JDBC program using the client driver and Network Server

In my previous post, I explained jdbc application using embedded derby server. In this post, I am going to show you how to connect to derby server using jdbc and perform CRUD operations.

Below step-by-step procedure explains how to start derby server and how to connect it by using jdbc.

Step 1: Create a directory ‘derby_example_jdbc’.

Step 2: Go to the directory ‘derby_example_jdbc’ and start the derby server.

On Windows

java -jar %DERBY_HOME%\lib\derbyrun.jar server start

On Linux platforms

java -jar $DERBY_HOME/lib/derbyrun.jar server start

You can able to see below messages, once the server started successfully.

C:\Users\Krishna\Documents\Study\Apache Derby\examples\derby_example_jdbc>java -jar "%DERBY_HOME%"\lib\derbyrun.jar server start
Thu Jun 29 14:13:38 IST 2017 : Security manager installed using the Basic server security policy.
Thu Jun 29 14:13:38 IST 2017 : Apache Derby Network Server - 10.13.1.1 - (1765088) started and ready to accept connections on port 1527

From above messages, it is clear that the server started listening on port 1527.

Step 3: Open Eclipse, create new maven project.

Right click on Eclipse ‘Project Explorer’ -> New -> Other.

Select ‘Maven Project’ and press next.

Select the check box ‘Create a simple project (skip archetype selection)’.

Press Next.

Give the group id and project id as 'jdbc_helloworld'.

Click on the button Finish.

It creates project structure like below.

Step 4: Add derby jdbc dependency to pom.xml.

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>jdbc_helloworld</groupId>
  <artifactId>jdbc_helloworld</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <dependencies>
    <!-- https://mvnrepository.com/artifact/org.apache.derby/derbyclient -->
    <dependency>
      <groupId>org.apache.derby</groupId>
      <artifactId>derbyclient</artifactId>
      <version>10.13.1.1</version>
    </dependency>


  </dependencies>
</project>

Step 5: Create a package com.sample.

Define class ‘Test’ under the package ‘com.sample’.

Test.java

package com.sample;

/* Step 1: Import sql package */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {

  private static String driver = "org.apache.derby.jdbc.ClientDriver";
  private static String dbName = "organization";
  private static String connectionURL = "jdbc:derby://localhost:1527/" + dbName + ";create=true";

  public static void main(String args[]) throws SQLException, ClassNotFoundException {
    /* Step 2: Load Driver */
    System.out.println("Loading/Registering driver");
    Class.forName(driver);

    /* Step 3: Open connection to database */
    System.out.println("Connecting to database");
    Connection conn = DriverManager.getConnection(connectionURL);

    /* Create table employee */
    String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
    Statement stmt = conn.createStatement();
    stmt.execute(query);

    /* Insert data to employee table */
    query = "INSERT INTO employee values(1, 'Krishna')";
    stmt.execute(query);
    query = "INSERT INTO employee values(2, 'Arjun')";
    stmt.execute(query);

    query = "SELECT * FROM employee";
    ResultSet rs = stmt.executeQuery(query);

    while (rs.next()) {
      int id = rs.getInt("id");
      String name = rs.getString("name");
      System.out.println(id + " " + name);
    }
    rs.close();
    conn.close();
  }
}

Output

Loading/Registering driver
Connecting to database
1 Krishna
2 Arjun

Load Derby Driver

Below statements are used to load derby embedded driver.

private static String driver = " org.apache.derby.jdbc.ClientDriver";

Class.forName(driver);

Connect to the server

Below statements are used to connect to the server.

private static String dbName = "organization";

private static String connectionURL = "jdbc:derby://localhost:1527/" + dbName + ";create=true";

Connection conn = DriverManager.getConnection(connectionURL);

Create table employee

String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";

Statement stmt = conn.createStatement();

stmt.execute(query);

Insert data into employee table

String query = "INSERT INTO employee values(1, 'Krishna')";

stmt.execute(query);

query = "INSERT INTO employee values(2, 'Arjun')";

stmt.execute(query);

Display the records in employee table

query = "SELECT * FROM employee";

ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {

         int id = rs.getInt("id");

         String name = rs.getString("name");

         System.out.println(id + " " + name);

}

Close result set and connection

rs.close();

conn.close();

Previous                                                 Next                                                 Home