Setup Apache Derby in Windows

Step 1: Create User Variables

Right click My Computer -> Advance Tab-> Click Environment variables-> Add new User variable called DERBY_INSTALL and the values is your DERBY ROOT DIR.

Make sure JAVA_HOME and JRE_HOME is present in the user variables list.

Step 2: Run ij tool

Open windows command prompt and execute the below command to set class path:

set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbytools.jar;.

Run below command to connect with ij tool:

java org.apache.derby.tools.ij

ij is an interactive SQL scripting tool that comes with Derby.

You should see the output shown below:

ij version #

ij>

Run connect 'jdbc:derby:MyDbTest;create=true'; command to create new database called MyDbTest.

Run SQL queries to play with the DB.

EX:

ij> create table derbyDB(num int, addr varchar(40));

ij> insert into derbyDB values (1956,'Webster St.');

ij> insert into derbyDB values (1910,'Union St.');

ij> update derbyDB set num=180, addr='Grand Ave.' where num=1956;

ij> select * from derbyDb;

disconnect; command used to disconnect from the connected DB.

exit; command used to come out from ij tool;

Run SQL file:

You can execute SQL scripts in ij as shown below:

ij> run 'my_file.sql';

You can also run SQL scripts from the command line:

java org.apache.derby.tools.ij my_file.sql

Step 3: Start network server

To start derby DB network server go to %DERBY_INSTALL%/bin and run startNetworkServer.bat.

 

To stop derby DB network server go to %DERBY_INSTALL%/bin and run stopNetworkServer.bat.

Connect Derby network server:

Driver name is org.apache.derby.jdbc.ClientDriver.

URL is jdbc:derby://IP_ADRESS:1527/, 1527 is a default derby port.

The complete url is jdbc:derby://localhost:1527/derbyDB;create=true

Create a database To create a database, specify the create=true attribute in the connection URL. For example, the command below creates a new database called MyDbTest: 

ij> connect 'jdbc:derby:MyDbTest;create=true';

Now exit ij:

ij> exit;

 The MyDbTest directory contains the files that make up the database you just created. The derby.log file is an error log, which is helpful whenever things don't work as expected.

Connect to a database

Start up ij again and connect to the database you just created:

java org.apache.derby.tools.ij

ij> connect 'jdbc:derby:MyDbTest';

We'll take a quick look at the protocol ("jdbc:derby:") and database ("MyDbTest") in this connection URL. 

Execute SQL statements

Once you connect to a database, you can execute SQL statements. ij expects each statement to be terminated with a semicolon (;); for example:

ij> create table derbyDB(num int, addr varchar(40));

ij> insert into derbyDB values (1956,'Webster St.');

ij> insert into derbyDB values (1910,'Union St.');

ij> update derbyDB set num=180, addr='Grand Ave.' where num=1956;

 ij> select * from derbyDb;

Disconnect from a database

The disconnect command disconnects from the current database:

ij> disconnect;

Exit

The exit command quits out of ij and, in embedded mode, shuts down the Derby database:

ij> exit;

Example Java program:

Copy derbyclient.jar and derby.jar to the java class path.

 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.ResultSetMetaData; public class Restaurants {     private static String dbURL = "jdbc:derby://localhost:1527/ MyDbTest;create=true";     private static String tableName = "restaurants";     // jdbc Connection     private static Connection conn = null;     private static Statement stmt = null;     public static void main(String[] args) {         createConnection();         insertRestaurants(5, "LaVals", "Berkeley");         selectRestaurants();         shutdown();     }         private static void createConnection() {         try {             Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();             //Get a connection             conn = DriverManager.getConnection(dbURL);         }         catch (Exception except) {             except.printStackTrace();         }     }         private static void insertRestaurants(int id, String restName, String cityName)     {         try {             stmt = conn.createStatement();             stmt.execute("insert into " + tableName + " values (" +                     id + ",'" + restName + "','" + cityName +"')");             stmt.close();         }         catch (SQLException sqlExcept) {             sqlExcept.printStackTrace();         }     }         private static void selectRestaurants() {         try {             stmt = conn.createStatement();             ResultSet results = stmt.executeQuery("select * from " + tableName);             ResultSetMetaData rsmd = results.getMetaData();             int numberCols = rsmd.getColumnCount();             for (int i=1; i<=numberCols; i++) {                 //print Column Names                 System.out.print(rsmd.getColumnLabel(i)+"\t\t");              }             System.out.println("\n-------------------------------------------------");             while(results.next()) {                 int id = results.getInt(1);                 String restName = results.getString(2);                 String cityName = results.getString(3);                 System.out.println(id + "\t\t" + restName + "\t\t" + cityName);             }             results.close();             stmt.close();         }         catch (SQLException sqlExcept) {             sqlExcept.printStackTrace();         }     }         private static void shutdown() {         try {             if (stmt != null) {                 stmt.close();             }             if (conn != null)  {                 DriverManager.getConnection(dbURL + ";shutdown=true");                 conn.close();             }                   }         catch (SQLException sqlExcept)   { }     } }