Apache Derby With Java Example

INTRODUCTION

• Apache Derby is an open source database and it is completely written in java.
• Supports for embedding with your java application using it’s embedded JDBC driver as well as client-server mode.
• Easy installation and use.
• Oracle’s Java DB is a distribution of Apache Derby.

SOFTWARES & TOOLS

1. Apache Derby DB
2. Eclipse IDE (Mars 2)

INSTALLATION & SETUP

1. Download latest Derby (**-bin.zip) from Apache website : https://db.apache.org/derby/derby_downloads.html
2. Unzip and copy to your local drive: C:\ranjiths\CommonSofties\db-derby-10.12.1.1
3. Set Environment Variables (for Windows)
1. DERBY_HOME= C:\ranjiths\CommonSofties\db-derby-10.12.1.1
2. PATH = C:\ranjiths\CommonSofties\db-derby-10.12.1.1\bin
3. CLASSPATH = C:\ranjiths\CommonSofties\db-derby-10.12.1.1\lib
4. Run & Check IJ Command Line Interface (CLI)
1. Open cmd prompt and type : ij and you will able to access ij>

CREATE DATABASE & TABLES

1. Open the cmd and type ij to enter into the CLI.
2. Create Database

ji> connect 'jdbc:derby:/jbr/library;create=true';

Upon successful execution of the above command, under C drive new folder will be created in the name of: jbr/library

3. Create Tables

CREATE TABLE users (  user_id INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY,  first_name VARCHAR(30) NOT NULL,  last_name VARCHAR(30) NOT NULL,  email VARCHAR(40),  phone INTEGER ); CREATE TABLE books(  book_id VARCHAR(30),  book_name VARCHAR(30),  authors VARCHAR(30),  publisher VARCHAR(30),  price INTEGER );

4. Insert data and execute a SELECT command to check the data.

INSERT INTO users VALUES(1,'Ranjith','Sekar','ranjith@gmail.com',96000); INSERT INTO users VALUES(2,'Manoj','Kumar','manoj@gmail.com',88000); INSERT INTO users VALUES(3,'Sachin','Tendulkar','@gmail.com',77000); INSERT INTO books VALUES('b100','Computer Science','John','TATA',400); INSERT INTO books VALUES('b200','Mathematics','David','Oreilly',300); INSERT INTO books VALUES('b300','Java Complete Reference','Jonathan','PSB',600);

EXAMPLE JAVA PROGRAM

1. Create a Java Project in Eclipse
2. Add all jars from C:\ranjiths\CommonSofties\db-derby-10.12.1.1\lib into the project’s classpath.

package jbr.derbyex; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DerbyExample { public static void main(String[] args) throws SQLException {   String driver = "org.apache.derby.jdbc.EmbeddedDriver";   String dbName = "/jbr/library";   String connectionURL = "jdbc:derby:" + dbName;   Connection connection = null;   try {          Class.forName(driver);          connection = DriverManager.getConnection(connectionURL);          Statement statement = connection.createStatement();          // INSERT          int result = statement.executeUpdate("INSERT INTO users VALUES(4,'Bose','Subash','bose@gmail.com',96000)");          System.out.println("Updated " + result + " rows");          // SELECT          ResultSet resultSet = statement.executeQuery("SELECT * FROM USERS");          while (resultSet.next()) {                 String first = resultSet.getString("FIRST_NAME");                 String last = resultSet.getString("LAST_NAME");                 System.out.println("Name: " + first + " " + last);          }          resultSet.close();   } catch (Throwable e) {          e.printStackTrace();   } finally {          connection.close();   } } }