{"id":315,"date":"2012-12-17T06:40:40","date_gmt":"2012-12-17T06:40:40","guid":{"rendered":"http:\/\/www.softwareeverydayblog.com\/?p=315"},"modified":"2012-12-17T10:20:50","modified_gmt":"2012-12-17T10:20:50","slug":"playing-with-apache-derby","status":"publish","type":"post","link":"https:\/\/www.softwareeverydayblog.com\/?p=315","title":{"rendered":"Playing with Apache Derby."},"content":{"rendered":"<p>Apache Derby has two modes, <a href=\"http:\/\/db.apache.org\/derby\/papers\/DerbyTut\/embedded_intro.html\" title=\"Embedded Derby\">Embedded Derby<\/a> and <a href=\"http:\/\/db.apache.org\/derby\/papers\/DerbyTut\/ns_intro.html\" title=\"Derby Network Server\">Derby Network Server<\/a>.&#8217; Lets look at some code using both these modes.<\/p>\n<p>Let&#8217;s work on creating a class DerbyEmbeddedClient to demo the Embedded Derby mode.<\/p>\n<ul>\n<li>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.<\/li>\n<li>Loading this driver will start up the DerbyEngine, please specify any static Derby system properties before hand.<br \/>\n   <code>Class.forName(\"org.apache.derby.jdbc.EmbeddedDriver\").newInstance();<\/code><\/li>\n<li>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!<br \/>\n   <code>Properties props = new Properties();<br \/>\n   props.put(\"user\", \"user1\");<br \/>\n   props.put(\"password\", \"user1\");<\/code><\/li>\n<li>This will set the derby system home to &#8220;c:\/derbydb&#8221;. All databases will be created within this folder.<br \/>\n   <code>System.setProperty(\"derby.system.home\", \"c:\/derbydb\");<\/code><\/li>\n<li>Protocol is &#8220;jdbc:derby:&#8221;. 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=&#8221;c:\/db\/derby&#8221;, the db would end up at c:\/db\/derby. create = true means if this database doesn&#8217;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.<br \/>\n   <code>DriverManager.getConnection(\"jdbc:derby:\" + dbName + \";create=true\", props);<\/code><\/li>\n<li>Using the EmbeddedClientDriver, Create db with name &#8220;db\/derbyDB&#8221; if not created. Then within that schema create, insert, display and drop &#8216;usertable&#8217;.<br \/>\n   <code>DerbyEmbeddedClient dec=new DerbyEmbeddedClient(\"db\/derbyDB\", true, true, true, true, true);<br \/>\n   dec.execute();<\/code><\/li>\n<li>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 &#8220;c:\/derbydb&#8221;<br \/>\n   <code>System.setProperty(\"derby.system.home\", \"c:\/derbydb2\");<br \/>\n   dec=new DerbyEmbeddedClient(\"db\/derbyDB\", true, true, true, true, true);<br \/>\n   dec.execute();<\/code><\/li>\n<\/ul>\n<p>Let&#8217;s work on creating a class DerbyNetworkServer and DerbyNetworkClient to demo the Embedded Derby mode.<\/p>\n<ul>\n<li>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&#8217;t put restrictions that don&#8217;t allow process from other JVM to access the DB. If you&#8217;ve started the DerbyNetworkServer in some JVM you could access the DB using the embedded client from the same JVM. It&#8217;s just that other processes have the flexibility to connect to that DB if they want to.<\/li>\n<li>Following code starts the Derby Network Server on the localhost. This should also startup the Derby network engine.<br \/>\n<code>server=new NetworkServerControl(InetAddress.getByName(\"localhost\"), port, user, pass);<br \/>\nserver.start(new PrintWriter(System.out));<\/code><\/li>\n<li>Load derby Client Driver to connect to Network DB server.<br \/>\n<code>Class.forName(\"org.apache.derby.jdbc.ClientDriver\").newInstance();<\/code><\/li>\n<li>Protocol is &#8220;jdbc:derby:\/\/HOST:PORT\/&#8221;. 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=&#8221;c:\/db\/derby&#8221;, the db would end up at c:\/db\/derby. create = true means if this database doesn&#8217;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.<br \/>\n   <code>DriverManager.getConnection(\"jdbc:derby:\" + dbName + \";create=true\", props);<\/code><\/li>\n<li>a.) Using the ClientDriver, connect to DB at localhost at port 1527. Create db with name &#8220;db\/derbyDB&#8221; if not created (on the server!). Then within that schema create and display &#8216;usertable&#8217;. b.) Then Using the EmbeddedClientDriver, within &#8220;db\/derbyDB&#8221; insert into &#8216;usertable&#8217;. c.) Then using the ClientDriver, connect to DB at localhost at port 1527. Within &#8220;db\/derbyDB&#8221; display and drop &#8216;usertable&#8217;.<br \/>\n\t<code>DerbyNetworkClient den = null;<br \/>\n\tden=new DerbyNetworkClient(\"localhost\", 1527, \"db\/derbyDB\", true, true, false, true, false);<br \/>\n\tden.execute();<br \/>\n\tDerbyEmbeddedClient dec=new DerbyEmbeddedClient(\"db\/derbyDB\", false, false, true, false, false);<br \/>\n\tdec.execute();<br \/>\n\tden=new DerbyNetworkClient(\"localhost\", 1527, \"db\/derbyDB\", false, false, false, true, true);<br \/>\n\tden.execute();<br \/>\n\tdServer.shutdown();<br \/>\n\t<\/code>\n\t<\/li>\n<\/ul>\n<pre lang=\"java\" line=\"1\">\r\npackage com.napp;\r\nimport java.sql.* ;\r\nimport java.util.Properties;\r\n\/**\r\n * \r\n * DerbyEmbeddedClient\r\n *\tdbName - Database name (its a path!)\r\n *\tcreateDB - Create DB if doesnt exist\r\n *\tcreateTables - Create table\r\n *\tinsertTables - Insert Table\r\n *\tdisplayTables - Display table\r\n *\tdropTables - Drop table\r\n *\r\n *\/\r\npublic class DerbyEmbeddedClient\r\n{\r\n\tString dbName;\r\n\tboolean createDB;\r\n\t\r\n\tboolean createTables;\r\n\tboolean insertTables;\r\n\tboolean displayTables;\r\n\tboolean dropTables;\r\n\t\r\n\tpublic DerbyEmbeddedClient(String dbName, boolean createDB, boolean createTables, boolean insertTables, boolean displayTables, boolean dropTables) {\r\n\t\tthis.dbName = dbName;\r\n\t\tthis.createDB = createDB;\r\n\t\t\r\n\t\tthis.createTables = createTables;\r\n\t\tthis.insertTables = insertTables;\r\n\t\tthis.displayTables = displayTables;\r\n\t\tthis.dropTables = dropTables;\r\n\t}\r\n\t\r\n\tpublic void execute() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {\r\n\t\t\t\r\n            \/\/ Loading this driver will start up the DerbyEngine!\r\n            \/\/ Please specify any static Derby system properties before hand!\r\n            Class.forName(\"org.apache.derby.jdbc.EmbeddedDriver\").newInstance();\r\n            System.out.println(\"EmbeddedDriver loaded, derby engine! has begun!!!\");\r\n\t\t\t\r\n            \/\/ Username and Password are optional in embedded and derbyclient\r\n            \/\/ If we're creating DB, following is the username and password used to connect to it.\r\n            \/\/ I think by default passwords are ignored but please do your own research!\r\n            Properties props = new Properties();\r\n            props.put(\"user\", \"user1\");\r\n            props.put(\"password\", \"user1\");\r\n\t\t\t\r\n            \/\/ Get a connection to the database\r\n            String protocol = \"jdbc:derby:\";\r\n   \r\n            \/\/ The name of the database. A folder with this name will be created! where? in the derby.system.home or user.dir.\r\n            \/\/ 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\r\n            \/\/ String dbName = \"db\/derbyDB\";\r\n            \r\n            \/\/ create = true means if this database doesn't exist create it!\r\n            \/\/ So in a scenario where you would probably use an installer to create your database this parameter will be true\r\n            \/\/ However when you connect to the derby DB from your app, this parameter would usually be false.\r\n            Connection conn = null;\r\n            if ( createDB ) {\r\n            \tconn = DriverManager.getConnection(protocol + dbName + \";create=true\", props);\r\n            } else {\r\n            \tconn = DriverManager.getConnection(protocol + dbName + \";\", props);\r\n            }\r\n            \r\n            JdbcCode jdbccode=new JdbcCode();\r\n            jdbccode.execute(conn, \"usertable\", createTables, insertTables, displayTables, dropTables);\r\n            \r\n            conn.close() ;\r\n\t\t\t\r\n\t}\r\n\t\r\n\tpublic static void main( String args[] ) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException\r\n\t{\r\n\t\tSystem.setProperty(\"derby.system.home\", \"c:\/derbydb\");\r\n\t\t\r\n\t\t\/\/ Using the EmbeddedClientDriver\r\n\t\t\/\/ Create DB db\/derbyDB if not created, and create, insert, display and drop 'usertable' in the DB.\r\n\t\tDerbyEmbeddedClient dec=new DerbyEmbeddedClient(\"db\/derbyDB\", true, true, true, true, true);\r\n\t\tdec.execute();\r\n\t\t\r\n\t\t\/\/ You cannot change derby.system.home after loading the EmbeddedDriver. Even after doing this DerbyEmbeddedClient\r\n\t\t\/\/ will yet be pointing to c:\/derbydb\r\n\t\tSystem.setProperty(\"derby.system.home\", \"c:\/derbydb2\");\r\n\t\tdec=new DerbyEmbeddedClient(\"db\/derbyDB\", true, true, true, true, true);\r\n\t\tdec.execute();\r\n\t}\r\n}\r\n<\/pre>\n<pre lang=\"java\" line=\"1\">\r\npackage com.napp;\r\n\r\nimport java.io.PrintWriter;\r\nimport java.net.InetAddress;\r\n\r\nimport org.apache.derby.drda.NetworkServerControl;\r\n\r\n\/**\r\n * Class to start DerbyNetworkServer at localhost with port, username and password!\r\n *\r\n *\/\r\npublic class DerbyNetworkServer implements Runnable\r\n{\r\n\tint port;\r\n\tString user;\r\n\tString pass;\r\n\tNetworkServerControl server;\r\n\t\r\n\tpublic DerbyNetworkServer(int port, String user, String pass) {\r\n\t\tthis.port = port;\r\n\t\tthis.user = user;\r\n\t\tthis.pass = pass;\r\n\t\tthis.server = null;\r\n\t}\r\n\t\r\n\tpublic void run() {\r\n\t\ttry {\r\n\t\t\t\/\/start network server!\r\n\t\t\tSystem.out.println(\"Starting Derby Network Server\");\t\t\t\r\n\t\t\tserver=new NetworkServerControl(InetAddress.getByName(\"localhost\"), port, user, pass);\r\n\t\t\tserver.start(new PrintWriter(System.out));\r\n\t\t} catch (Exception e) {\r\n\t\t\te.printStackTrace();\r\n\t\t}\r\n\t}\r\n\t\r\n\tpublic void shutdown() throws Exception {\r\n\t\tserver.shutdown();\r\n\t\tSystem.out.println(\"DerbyNetworkServer shutdown\");\r\n\t}\r\n\t\r\n}\r\n<\/pre>\n<pre lang=\"java\" line=\"1\">\r\npackage com.napp;\r\n\r\nimport java.sql.Connection;\r\nimport java.sql.DriverManager;\r\nimport java.sql.SQLException;\r\nimport java.util.Properties;\r\n\r\n\/**\r\n *\r\n * DerbyNetworkClient\r\n *\tdbName - Database name (its a path!)\r\n *\tcreateDB - Create DB if doesnt exist\r\n *\tcreateTables - Create table\r\n *\tinsertTables - Insert Table\r\n *\tdisplayTables - Display table\r\n *\tdropTables - Drop table\r\n *\/\r\npublic class DerbyNetworkClient {\r\n\t\r\n\tString dbName;\r\n\tboolean createDB;\r\n\tString ip;\r\n\tint port;\r\n\t\r\n\tboolean createTables;\r\n\tboolean insertTables;\r\n\tboolean displayTables;\r\n\tboolean dropTables;\r\n\t\r\n\tpublic DerbyNetworkClient(String ip, int port, String dbName, boolean createDB, boolean createTables, boolean insertTables, boolean displayTables, boolean dropTables) {\r\n\t\tthis.dbName = dbName;\r\n\t\tthis.createDB = createDB;\r\n\t\tthis.ip = ip;\r\n\t\tthis.port = port;\r\n\t\t\r\n\t\tthis.createTables = createTables;\r\n\t\tthis.insertTables = insertTables;\r\n\t\tthis.displayTables = displayTables;\r\n\t\tthis.dropTables = dropTables;\r\n\t}\r\n\t\r\n\tpublic void execute() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {\r\n\t\t\t\r\n            \/\/ Load driver to connect to Network DB server!\r\n            \/\/ Please specify any static Derby system properties before hand!\r\n            Class.forName(\"org.apache.derby.jdbc.ClientDriver\").newInstance();\r\n            System.out.println(\"ClientDriver loaded, ready to connect!\");\r\n\t\t\t\r\n            \/\/ Username and Password are optional in embedded and derbyclient\r\n            \/\/ If we're creating DB, following is the username and password used to connect to it.\r\n            \/\/ I think by default passwords are ignored but please do your own research!\r\n            Properties props = new Properties();\r\n            props.put(\"user\", \"user1\");\r\n            props.put(\"password\", \"pass1\");\r\n\t\t\t\r\n            \/\/ Get a connection to the database\r\n            \/\/ jdbc:derby:\/\/HOST:PORT\/\r\n            String protocol = \"jdbc:derby:\/\/\" + ip + \":\" + port + \"\/\";\r\n   \r\n            \/\/ 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)!.\r\n            \/\/ 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!)\r\n            \/\/ String dbName = \"db\/derbyDB\";\r\n            \r\n            \/\/ create = true means if this database doesn't exist create it!\r\n            \/\/ So in a scenario where you would probably use an installer to create your database this parameter will be true\r\n            \/\/ However when you connect to the derby DB from your app, this parameter would usually be false.\r\n            Connection conn = null;\r\n            \r\n            if ( createDB ) {\r\n            \tconn = DriverManager.getConnection(protocol + dbName + \";create=true\", props);            \r\n            } else {\r\n            \tconn = DriverManager.getConnection(protocol + dbName + \";\", props);  \r\n            }\r\n            \r\n            JdbcCode jdbccode=new JdbcCode();\r\n            jdbccode.execute(conn, \"usertable\", createTables, insertTables, displayTables, dropTables);\r\n            \r\n            conn.close() ;\r\n\t\t\t\r\n\t}\t\r\n\t\r\n\tpublic static void main(String[] args) throws Exception {\r\n\t\t\r\n\t\t\/\/Start DerbyNetworkServer\r\n\t\tDerbyNetworkServer dServer=new DerbyNetworkServer(1527, \"user\", \"pass\");\r\n\t\tThread server=new Thread(dServer);\r\n\t\tserver.start();\r\n\t\t\r\n\t\t\/\/You can use the DerbyNetworkClient to connect to DerbyNetworkServer\r\n\t\t\/\/If the process is in the same JVM, you could even use the DerbyEmbeddedClient!\r\n\t\t\/\/Lets use both!\r\n\r\n\t\t\/\/ Using the ClientDriver, connect to DB at localhost at port 1527. Create db with name \"db\/derbyDB\" if not created (on the server!).\r\n\t\t\/\/ Then within that schema create and display 'usertable'\r\n\t\tDerbyNetworkClient den = null;\t\t\r\n\t\tden=new DerbyNetworkClient(\"localhost\", 1527, \"db\/derbyDB\", true, true, false, true, false);\r\n\t\tden.execute();\r\n\t\t\r\n\t\t\/\/ Using the EmbeddedClientDriver\r\n\t\t\/\/ Within db db\/derbyDB insert into 'usertable'\r\n\t\tDerbyEmbeddedClient dec=new DerbyEmbeddedClient(\"db\/derbyDB\", false, false, true, false, false);\r\n\t\tdec.execute();\r\n\r\n\t\t\/\/ Using the ClientDriver, connect to DB at localhost at port 1527. Create db with name \"db\/derbyDB\" if not created (on the server!).\r\n\t\t\/\/ Within db db\/derbyDB display and drop 'usertable'\r\n\t\tden=new DerbyNetworkClient(\"localhost\", 1527, \"db\/derbyDB\", false, false, false, true, true);\r\n\t\tden.execute();\r\n\t\t\r\n\t\tdServer.shutdown();\r\n\t\t\r\n\t}\r\n\t\r\n}\r\n<\/pre>\n<p>Here is the code for <a href='https:\/\/www.softwareeverydayblog.com\/wp-content\/uploads\/2012\/12\/apachederbytest.zip'>Apache Derby Test<\/a>. You need maven to run it. Here are the commands to run DerbyEmbeddedClient and DerbyNetworkClient.<br \/>\nmvn clean test -P DerbyEmbeddedClient<br \/>\nmvn clean test -P DerbyNetworkClient<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Apache Derby has two modes, Embedded Derby and Derby Network Server.&#8217; Lets look at some code using both these modes. Let&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-315","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=\/wp\/v2\/posts\/315","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=315"}],"version-history":[{"count":28,"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=\/wp\/v2\/posts\/315\/revisions"}],"predecessor-version":[{"id":317,"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=\/wp\/v2\/posts\/315\/revisions\/317"}],"wp:attachment":[{"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=315"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=315"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.softwareeverydayblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=315"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}