Playing with Apache Derby.

Apache Derby has two modes, Embedded Derby and Derby Network Server.’ Lets look at some code using both these modes.

Let’s work on creating a class DerbyEmbeddedClient to demo the Embedded Derby mode.

  • Application uses JDBC to converse with the Derby Engine directly. Once you load the derby engine in one JVM, any process in another JVM cannot access it.
  • Loading this driver will start up the DerbyEngine, please specify any static Derby system properties before hand.
    Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
  • Username and Password are optional in Embedded Derby and Derby Network Server. I think by default passwords are ignored but please do your own research!
    Properties props = new Properties();
    props.put("user", "user1");
    props.put("password", "user1");
  • This will set the derby system home to “c:/derbydb”. All databases will be created within this folder.
    System.setProperty("derby.system.home", "c:/derbydb");
  • Protocol is “jdbc:derby:”. dbName is the name of the database. A folder with this name will be created. where? in the derby.system.home or user.dir. It also seems to recognize absolute and relative paths. So if i had dbName=”c:/db/derby”, the db would end up at c:/db/derby. create = true means if this database doesn’t exist create it. When installing your app this parameter will be true. However when you connect to the derby DB from your app, this parameter would usually be false.
    DriverManager.getConnection("jdbc:derby:" + dbName + ";create=true", props);
  • Using the EmbeddedClientDriver, Create db with name “db/derbyDB” if not created. Then within that schema create, insert, display and drop ‘usertable’.
    DerbyEmbeddedClient dec=new DerbyEmbeddedClient("db/derbyDB", true, true, true, true, true);
    dec.execute();
  • You cannot change derby.system.home after loading the EmbeddedDriver. After setting the derby.system.home the 2nd time DerbyEmbeddedClient will yet be pointing to the old derby.system.home which is “c:/derbydb”
    System.setProperty("derby.system.home", "c:/derbydb2");
    dec=new DerbyEmbeddedClient("db/derbyDB", true, true, true, true, true);
    dec.execute();

Let’s work on creating a class DerbyNetworkServer and DerbyNetworkClient to demo the Embedded Derby mode.

  • Application uses a Derby Network Client that passes your JDBC calls to the Derby Network Server which in turn uses JDBC to converse with the Derby Engine. This is probably how we speak to other standard databases, it includes that extra network latency component. However, using the DerbyNetworkServer doesn’t put restrictions that don’t allow process from other JVM to access the DB. If you’ve started the DerbyNetworkServer in some JVM you could access the DB using the embedded client from the same JVM. It’s just that other processes have the flexibility to connect to that DB if they want to.
  • Following code starts the Derby Network Server on the localhost. This should also startup the Derby network engine.
    server=new NetworkServerControl(InetAddress.getByName("localhost"), port, user, pass);
    server.start(new PrintWriter(System.out));
  • Load derby Client Driver to connect to Network DB server.
    Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
  • Protocol is “jdbc:derby://HOST:PORT/”. dbName is the name of the database. A folder with this name will be created. where? in the derby.system.home (of server) or user.dir where server is started. It also seems to recognize absolute and relative paths. So if i had dbName=”c:/db/derby”, the db would end up at c:/db/derby. create = true means if this database doesn’t exist create it. When installing your app this parameter will be true. However when you connect to the derby DB from your app, this parameter would usually be false.
    DriverManager.getConnection("jdbc:derby:" + dbName + ";create=true", props);
  • a.) Using the ClientDriver, connect to DB at localhost at port 1527. Create db with name “db/derbyDB” if not created (on the server!). Then within that schema create and display ‘usertable’. b.) Then Using the EmbeddedClientDriver, within “db/derbyDB” insert into ‘usertable’. c.) Then using the ClientDriver, connect to DB at localhost at port 1527. Within “db/derbyDB” display and drop ‘usertable’.
    DerbyNetworkClient den = null;
    den=new DerbyNetworkClient("localhost", 1527, "db/derbyDB", true, true, false, true, false);
    den.execute();
    DerbyEmbeddedClient dec=new DerbyEmbeddedClient("db/derbyDB", false, false, true, false, false);
    dec.execute();
    den=new DerbyNetworkClient("localhost", 1527, "db/derbyDB", false, false, false, true, true);
    den.execute();
    dServer.shutdown();
     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

package com.napp;
import java.sql.* ;
import java.util.Properties;
/**
 * 
 * DerbyEmbeddedClient
 *	dbName - Database name (its a path!)
 *	createDB - Create DB if doesnt exist
 *	createTables - Create table
 *	insertTables - Insert Table
 *	displayTables - Display table
 *	dropTables - Drop table
 *
 */
public class DerbyEmbeddedClient
{
	String dbName;
	boolean createDB;
 
	boolean createTables;
	boolean insertTables;
	boolean displayTables;
	boolean dropTables;
 
	public DerbyEmbeddedClient(String dbName, boolean createDB, boolean createTables, boolean insertTables, boolean displayTables, boolean dropTables) {
		this.dbName = dbName;
		this.createDB = createDB;
 
		this.createTables = createTables;
		this.insertTables = insertTables;
		this.displayTables = displayTables;
		this.dropTables = dropTables;
	}
 
	public void execute() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
 
            // Loading this driver will start up the DerbyEngine!
            // Please specify any static Derby system properties before hand!
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            System.out.println("EmbeddedDriver loaded, derby engine! has begun!!!");
 
            // Username and Password are optional in embedded and derbyclient
            // If we're creating DB, following is the username and password used to connect to it.
            // I think by default passwords are ignored but please do your own research!
            Properties props = new Properties();
            props.put("user", "user1");
            props.put("password", "user1");
 
            // Get a connection to the database
            String protocol = "jdbc:derby:";
 
            // The name of the database. A folder with this name will be created! where? in the derby.system.home or user.dir.
            // It also seems to recognize absolute and relative paths. So if i had dbName=c:/db/derby, it would end up at c:/db/derby
            // String dbName = "db/derbyDB";
 
            // create = true means if this database doesn't exist create it!
            // So in a scenario where you would probably use an installer to create your database this parameter will be true
            // However when you connect to the derby DB from your app, this parameter would usually be false.
            Connection conn = null;
            if ( createDB ) {
            	conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
            } else {
            	conn = DriverManager.getConnection(protocol + dbName + ";", props);
            }
 
            JdbcCode jdbccode=new JdbcCode();
            jdbccode.execute(conn, "usertable", createTables, insertTables, displayTables, dropTables);
 
            conn.close() ;
 
	}
 
	public static void main( String args[] ) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
	{
		System.setProperty("derby.system.home", "c:/derbydb");
 
		// Using the EmbeddedClientDriver
		// Create DB db/derbyDB if not created, and create, insert, display and drop 'usertable' in the DB.
		DerbyEmbeddedClient dec=new DerbyEmbeddedClient("db/derbyDB", true, true, true, true, true);
		dec.execute();
 
		// You cannot change derby.system.home after loading the EmbeddedDriver. Even after doing this DerbyEmbeddedClient
		// will yet be pointing to c:/derbydb
		System.setProperty("derby.system.home", "c:/derbydb2");
		dec=new DerbyEmbeddedClient("db/derbyDB", true, true, true, true, true);
		dec.execute();
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

package com.napp;
 
import java.io.PrintWriter;
import java.net.InetAddress;
 
import org.apache.derby.drda.NetworkServerControl;
 
/**
 * Class to start DerbyNetworkServer at localhost with port, username and password!
 *
 */
public class DerbyNetworkServer implements Runnable
{
	int port;
	String user;
	String pass;
	NetworkServerControl server;
 
	public DerbyNetworkServer(int port, String user, String pass) {
		this.port = port;
		this.user = user;
		this.pass = pass;
		this.server = null;
	}
 
	public void run() {
		try {
			//start network server!
			System.out.println("Starting Derby Network Server");			
			server=new NetworkServerControl(InetAddress.getByName("localhost"), port, user, pass);
			server.start(new PrintWriter(System.out));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
 
	public void shutdown() throws Exception {
		server.shutdown();
		System.out.println("DerbyNetworkServer shutdown");
	}
 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113

package com.napp;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
 
/**
 *
 * DerbyNetworkClient
 *	dbName - Database name (its a path!)
 *	createDB - Create DB if doesnt exist
 *	createTables - Create table
 *	insertTables - Insert Table
 *	displayTables - Display table
 *	dropTables - Drop table
 */
public class DerbyNetworkClient {
 
	String dbName;
	boolean createDB;
	String ip;
	int port;
 
	boolean createTables;
	boolean insertTables;
	boolean displayTables;
	boolean dropTables;
 
	public DerbyNetworkClient(String ip, int port, String dbName, boolean createDB, boolean createTables, boolean insertTables, boolean displayTables, boolean dropTables) {
		this.dbName = dbName;
		this.createDB = createDB;
		this.ip = ip;
		this.port = port;
 
		this.createTables = createTables;
		this.insertTables = insertTables;
		this.displayTables = displayTables;
		this.dropTables = dropTables;
	}
 
	public void execute() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
 
            // Load driver to connect to Network DB server!
            // Please specify any static Derby system properties before hand!
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            System.out.println("ClientDriver loaded, ready to connect!");
 
            // Username and Password are optional in embedded and derbyclient
            // If we're creating DB, following is the username and password used to connect to it.
            // I think by default passwords are ignored but please do your own research!
            Properties props = new Properties();
            props.put("user", "user1");
            props.put("password", "pass1");
 
            // Get a connection to the database
            // jdbc:derby://HOST:PORT/
            String protocol = "jdbc:derby://" + ip + ":" + port + "/";
 
            // The name of the database. A folder with this name will be created! where? in the derby.system.home (of server) or user.dir (where server is started)!.
            // It also seems to recognize absolute and relative paths. So if i had dbName=c:/db/derby, it would end up at c:/db/derby (on the server!)
            // String dbName = "db/derbyDB";
 
            // create = true means if this database doesn't exist create it!
            // So in a scenario where you would probably use an installer to create your database this parameter will be true
            // However when you connect to the derby DB from your app, this parameter would usually be false.
            Connection conn = null;
 
            if ( createDB ) {
            	conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);            
            } else {
            	conn = DriverManager.getConnection(protocol + dbName + ";", props);  
            }
 
            JdbcCode jdbccode=new JdbcCode();
            jdbccode.execute(conn, "usertable", createTables, insertTables, displayTables, dropTables);
 
            conn.close() ;
 
	}	
 
	public static void main(String[] args) throws Exception {
 
		//Start DerbyNetworkServer
		DerbyNetworkServer dServer=new DerbyNetworkServer(1527, "user", "pass");
		Thread server=new Thread(dServer);
		server.start();
 
		//You can use the DerbyNetworkClient to connect to DerbyNetworkServer
		//If the process is in the same JVM, you could even use the DerbyEmbeddedClient!
		//Lets use both!
 
		// Using the ClientDriver, connect to DB at localhost at port 1527. Create db with name "db/derbyDB" if not created (on the server!).
		// Then within that schema create and display 'usertable'
		DerbyNetworkClient den = null;		
		den=new DerbyNetworkClient("localhost", 1527, "db/derbyDB", true, true, false, true, false);
		den.execute();
 
		// Using the EmbeddedClientDriver
		// Within db db/derbyDB insert into 'usertable'
		DerbyEmbeddedClient dec=new DerbyEmbeddedClient("db/derbyDB", false, false, true, false, false);
		dec.execute();
 
		// Using the ClientDriver, connect to DB at localhost at port 1527. Create db with name "db/derbyDB" if not created (on the server!).
		// Within db db/derbyDB display and drop 'usertable'
		den=new DerbyNetworkClient("localhost", 1527, "db/derbyDB", false, false, false, true, true);
		den.execute();
 
		dServer.shutdown();
 
	}
 
}

Here is the code for Apache Derby Test. You need maven to run it. Here are the commands to run DerbyEmbeddedClient and DerbyNetworkClient.
mvn clean test -P DerbyEmbeddedClient
mvn clean test -P DerbyNetworkClient

softwareeveryday

[Programming Problem] Subsets II
Read More
[Programming Problem] Flatten Binary Tree to Linked List
Read More
[Programming Problem] Largest Number
Read More