Apache Derby

Posted on April 9, 2018 by Jianming Li

Contents

  1. Download and Install
  2. Start/Stop Server
    1. Start Embedded Server
  3. Start Network Server
  4. Start/Stop SQL Client
    1. Connect to Embedded Server
    2. Connect to Network Server
  5. SQL Commands
  6. Sample Java App
  7. References
 

Download and Install

* Download from Derby site, e.g. db-derby-10.14.1.0-bin.zip
* Unzip into local directory, e.g. c:\prog
* Set env:

DERBY_HOME=C:\prog\db-derby-10.14.1.0-bin
PATH=%DERBY_HOME%\bin

Start/Stop Server

Start Embedded Server

* No server to start

Start Network Server

* Default port: 1527
* Start using Java commands:

# Start server
java -jar %DERBY_HOME%\lib\derbyrun.jar server start
 
# Stop server
java -jar %DERBY_HOME%\lib\derbyrun.jar server shutdown

* Start using scripts:

# Go to script directory
cd %DERBY_HOME%
 
# Set env
setNetworkServerCP.bat
 
# Start server
startNetworkServer.bat # Listening on default port 1527
startNetworkServer -p 1528 # Listening on port 1528
startNetworkServer -h 0.0.0.0 # Accept connection from localhost and any other server
# Stop server
stopNetworkServer.bat

Start/Stop SQL Client

* Setup env

cd %DERBY_HOME%
 
# For embedded server:
setEmbeddedCP.bat
 
# For network server:
setNetworkServerCP.bat
 
# Check sys info:
java org.apache.derby.tools.sysinfo
 
# Start client, i.e. ij
java org.apache.derby.tools.ij
 
# Stop client
exit or Control C

Connect to Embedded Server

 
cd %DERBY_HOME%
setEmbeddedCP.bat
java org.apache.derby.tools.ij
 
# Connect to C:/work/poc/tmp/derby/MyDbTest using absolute path, create if not existing:
connect 'jdbc:derby:C:/work/poc/tmp/derby/MyDbTest;create=true';
 
# Alternatively, connect to MyDbTest in local directory, create if not existing:
# Db files stored as a subdirectory named MyDbTest in where ij was started:
connect 'jdbc:derby:MyDbTest;create=true';
 
# Alternatively, connect using derby.system.home:
java -Dderby.system.home=C:\work\poc\tmp\derby org.apache.derby.tools.ij
connect 'jdbc:derby:MyDbTest';

Connect to Network Server

 
cd %DERBY_HOME%
setNetworkClientCP.bat
java org.apache.derby.tools.ij
connect 'jdbc:derby://localhost:1527/C:/work/poc/tmp/derby/MyDbTest;create=true';
 
# Create MyDbTest2 with username and password:
connect 'jdbc:derby://localhost:1527/C:/work/poc/tmp/derby/MyDbTest2;create=true;user=micky;password=mouse';
 
# Alternatively
C:/work/poc/tmp/derby
connect 'jdbc:derby://localhost:1527/MyDbTest;create=true';
 
# Alternatively
java -Dderby.system.home=C:\work\poc\tmp\derby org.apache.derby.tools.ij
connect 'jdbc:derby://localhost:1527/MyDbTest';

SQL Commands

* SQL queries

# CREATE a TABLE
CREATE TABLE SECONDTABLE
    (ID INT PRIMARY KEY,
    NAME VARCHAR(14));
 
# INSERT SOME DATA		
INSERT INTO SECONDTABLE VALUES 
    (100,'ONE HUNDRED'),(200,'TWO HUNDRED'),(300,'THREE HUNDRED');
 
# Query TABLE		
SELECT * FROM SECONDTABLE;

* Show tables

SHOW TABLES;

* Run sql file:

# FROM ij
run 'my_file.sql';
 
# OR USING Java
java org.apache.derby.tools.ij my_file.sql

Sample Java App

* Add derbyclient.jar to classpath

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class DerbyExample1 {
 
	public static void main(String[] args) {
		String driver = "org.apache.derby.jdbc.ClientDriver";
		String db = "jdbc:derby://localhost:1527/C:/work/poc/tmp/derby/MyDbTest2;user=micky;password=mouse";
 
		Connection conn = null;
		Statement stmt = null;
 
		try {
			System.out.println("Registering JDBC Driver:" + driver);
			Class.forName(driver);
			System.out.println("Registered JDBC Driver.");
 
			System.out.println("Connecting using: " + db);
			conn = DriverManager.getConnection(db);
			System.out.println("Connected to: " + db);
 
			stmt = conn.createStatement();
 
			String sql = "SELECT * FROM SECONDTABLE";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString("id");
				String age = rs.getString("name");
 
				// Display values
				System.out.print("ID: " + id);
				System.out.print(", Age: " + age);
				System.out.print("\n");
			}
			rs.close();
 
		} catch (ClassNotFoundException e) {
			System.out.println("Class not found: " + driver);
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("Failed connecting to: " + db);
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

References

* Working with the database connection URL attributes
* Working with user authentication
* http://db.apache.org/derby/papers/DerbyTut/ns_intro.html
* Tutorial

This entry was posted in derby and tagged . Bookmark the permalink.