Derby: Run a JDBC program using the embedded driver

In my previous posts, I explained how to create database and tables using both embedded mode and client-server mode.

Let’s try to connect to the embedded Derby driver using jdbc. JDBC stands for Java Database Connectivity, and is a platform independent interface between databases and Java. Just like packages awt, swing, and lang, jdbc is also a package, that allows Java programs to access database management systems like MySql, oracle, db2 etc.,

If you are new to jdbc, I refer you go through my below tutorial.

https://self-learning-java-tutorial.blogspot.com/2014/10/jdbc.html

Below step by step procedure explains simple jdbc program that creates employee table, insert 3 records into it and display the employee table.

Step 1: 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 2: 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/derby -->
    <dependency>
      <groupId>org.apache.derby</groupId>
      <artifactId>derby</artifactId>
      <version>10.13.1.1</version>
    </dependency>

  </dependencies>
</project>

Note

If you got any error like 'java.lang.ClassNotFoundException: org.apache.derby.jdbc.EmbeddedDriver' it may be because the scope of the dependency is set to test.

                 <!-- https://mvnrepository.com/artifact/org.apache.derby/derby -->

                 <dependency>

                          <groupId>org.apache.derby</groupId>

                          <artifactId>derby</artifactId>

                          <version>10.13.1.1</version>

                          <scope>test</scope>

                 </dependency>

                

You need to remove the <scope> element from the dependency and update the dependency like below.

                 <!-- https://mvnrepository.com/artifact/org.apache.derby/derby -->

                 <dependency>

                          <groupId>org.apache.derby</groupId>

                          <artifactId>derby</artifactId>

                          <version>10.13.1.1</version>

                 </dependency>

Step 3: 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.EmbeddedDriver";
  private static String dbName = "organization";
  private static String connectionURL = "jdbc:derby:" + 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.EmbeddedDriver";

Class.forName(driver);

Connect to the embedded server

Below statements are used to connect to the embedded server.

private static String dbName = "organization";

private static String connectionURL = "jdbc:derby:" + 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