{"id":12159,"date":"2018-04-09T15:03:19","date_gmt":"2018-04-09T20:03:19","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=12159"},"modified":"2020-06-23T14:07:10","modified_gmt":"2020-06-23T19:07:10","slug":"apache-derby","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=12159","title":{"rendered":"Apache Derby"},"content":{"rendered":"
\n

Contents<\/h2>\n
    \n\t
  1. \n\t\tDownload and Install<\/a>\n\t<\/li>\n\t
  2. \n\t\tStart\/Stop Server<\/a>\n\t\t
      \n\t\t\t
    1. \n\t\t\t\tStart Embedded Server<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
    2. \n\t\tStart Network Server<\/a>\n\t<\/li>\n\t
    3. \n\t\tStart\/Stop SQL Client<\/a>\n\t\t
        \n\t\t\t
      1. \n\t\t\t\tConnect to Embedded Server<\/a>\n\t\t\t<\/li>\n\t\t\t
      2. \n\t\t\t\tConnect to Network Server<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
      3. \n\t\tSQL Commands<\/a>\n\t<\/li>\n\t
      4. \n\t\tSample Java App<\/a>\n\t<\/li>\n\t
      5. \n\t\tReferences<\/a>\n\t<\/li>\n<\/ol>\n<\/ol>\n<\/div>\n
         <\/div>\n

        Download and Install<\/h2><\/span>\n

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

        DERBY_HOME=C:\\prog\\db-derby-10.14.1.0-bin\nPATH=%DERBY_HOME%\\bin\n<\/pre>\n
        Start\/Stop Server<\/h2><\/span>\n
        Start Embedded Server<\/h3><\/span>\n
        * No server to start<\/p>\n
        Start Network Server<\/h2><\/span>\n
        * Default port: 1527
        \n* Start using Java commands:<\/p>\n
        # Start server\njava -jar %DERBY_HOME%\\lib\\derbyrun.jar server start\n\n# Stop server\njava -jar %DERBY_HOME%\\lib\\derbyrun.jar server shutdown\n<\/pre>\n
        * Start using scripts:<\/p>\n
        # Go to script directory\ncd %DERBY_HOME%\n\n# Set env\nsetNetworkServerCP.bat\n\n# Start server\nstartNetworkServer.bat # Listening on default port 1527\nstartNetworkServer -p 1528 # Listening on port 1528\nstartNetworkServer -h 0.0.0.0 # Accept connection from localhost and any other server\n# Stop server\nstopNetworkServer.bat\n<\/pre>\n
        Start\/Stop SQL Client<\/h2><\/span>\n
        * Setup env<\/p>\n
        cd %DERBY_HOME%\n\n# For embedded server:\nsetEmbeddedCP.bat\n\n# For network server:\nsetNetworkServerCP.bat\n\n# Check sys info:\njava org.apache.derby.tools.sysinfo\n\n# Start client, i.e. ij\njava org.apache.derby.tools.ij\n\n# Stop client\nexit or Control C\n<\/pre>\n
        Connect to Embedded Server<\/h3><\/span>\n
        cd %DERBY_HOME%\nsetEmbeddedCP.bat\njava org.apache.derby.tools.ij\n\n# Connect to C:\/work\/poc\/tmp\/derby\/MyDbTest using absolute path, create if not existing:\nconnect 'jdbc:derby:C:\/work\/poc\/tmp\/derby\/MyDbTest;create=true';\n\n# Alternatively, connect to MyDbTest in local directory, create if not existing:\n# Db files stored as a subdirectory named MyDbTest in where ij was started:\nconnect 'jdbc:derby:MyDbTest;create=true';\n\n# Alternatively, connect using derby.system.home:\njava -Dderby.system.home=C:\\work\\poc\\tmp\\derby org.apache.derby.tools.ij\nconnect 'jdbc:derby:MyDbTest';\n<\/pre>\n
        Connect to Network Server<\/h3><\/span>\n
        cd %DERBY_HOME%\nsetNetworkClientCP.bat\njava org.apache.derby.tools.ij\nconnect 'jdbc:derby:\/\/localhost:1527\/C:\/work\/poc\/tmp\/derby\/MyDbTest;create=true';\n\n# Create MyDbTest2 with username and password:\nconnect 'jdbc:derby:\/\/localhost:1527\/C:\/work\/poc\/tmp\/derby\/MyDbTest2;create=true;user=micky;password=mouse';\n\n# Alternatively\nC:\/work\/poc\/tmp\/derby\nconnect 'jdbc:derby:\/\/localhost:1527\/MyDbTest;create=true';\n\n# Alternatively\njava -Dderby.system.home=C:\\work\\poc\\tmp\\derby org.apache.derby.tools.ij\nconnect 'jdbc:derby:\/\/localhost:1527\/MyDbTest';\n<\/pre>\n
        SQL Commands<\/h2><\/span>\n
        * SQL queries<\/p>\n
        # Create a table\nCREATE TABLE SECONDTABLE\n    (ID INT PRIMARY KEY,\n    NAME VARCHAR(14));\n\n# Insert some data\t\t\nINSERT INTO SECONDTABLE VALUES \n    (100,'ONE HUNDRED'),(200,'TWO HUNDRED'),(300,'THREE HUNDRED');\n\n# Query table\t\t\nSELECT * FROM SECONDTABLE;\n<\/pre>\n
        * Show tables<\/p>\n
        show tables;\n<\/pre>\n
        * Run sql file:<\/p>\n
        # From ij\nrun 'my_file.sql';\n\n# OR using Java\njava org.apache.derby.tools.ij my_file.sql\n<\/pre>\n
        Sample Java App<\/h2><\/span>\n
        * Add derbyclient.jar to classpath<\/p>\n
        import java.sql.Connection;\nimport java.sql.DriverManager;\nimport java.sql.ResultSet;\nimport java.sql.SQLException;\nimport java.sql.Statement;\n\npublic class DerbyExample1 {\n\n\tpublic static void main(String[] args) {\n\t\tString driver = \"org.apache.derby.jdbc.ClientDriver\";\n\t\tString db = \"jdbc:derby:\/\/localhost:1527\/C:\/work\/poc\/tmp\/derby\/MyDbTest2;user=micky;password=mouse\";\n\n\t\tConnection conn = null;\n\t\tStatement stmt = null;\n\n\t\ttry {\n\t\t\tSystem.out.println(\"Registering JDBC Driver:\" + driver);\n\t\t\tClass.forName(driver);\n\t\t\tSystem.out.println(\"Registered JDBC Driver.\");\n\n\t\t\tSystem.out.println(\"Connecting using: \" + db);\n\t\t\tconn = DriverManager.getConnection(db);\n\t\t\tSystem.out.println(\"Connected to: \" + db);\n\n\t\t\tstmt = conn.createStatement();\n\n\t\t\tString sql = \"SELECT * FROM SECONDTABLE\";\n\t\t\tResultSet rs = stmt.executeQuery(sql);\n\t\t\twhile (rs.next()) {\n\t\t\t\tString id = rs.getString(\"id\");\n\t\t\t\tString age = rs.getString(\"name\");\n\n\t\t\t\t\/\/ Display values\n\t\t\t\tSystem.out.print(\"ID: \" + id);\n\t\t\t\tSystem.out.print(\", Age: \" + age);\n\t\t\t\tSystem.out.print(\"\\n\");\n\t\t\t}\n\t\t\trs.close();\n\n\t\t} catch (ClassNotFoundException e) {\n\t\t\tSystem.out.println(\"Class not found: \" + driver);\n\t\t\te.printStackTrace();\n\t\t} catch (SQLException e) {\n\t\t\tSystem.out.println(\"Failed connecting to: \" + db);\n\t\t\te.printStackTrace();\n\t\t} finally {\n\t\t\tif (conn != null) {\n\t\t\t\ttry {\n\t\t\t\t\tconn.close();\n\t\t\t\t} catch (SQLException e) {\n\t\t\t\t\te.printStackTrace();\n\t\t\t\t}\n\t\t\t}\n\t\t}\n\t}\n}\n\n<\/pre>\n
        References<\/h2><\/span>\n
        * Working with the database connection URL attributes<\/a>
        \n*         Working with user authentication<\/a>
        \n*         http:\/\/db.apache.org\/derby\/papers\/DerbyTut\/ns_intro.html<\/a>
        \n*         Tutorial<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
        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 * … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[782],"tags":[783],"class_list":["post-12159","post","type-post","status-publish","format-standard","hentry","category-derby","tag-derby"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-3a7","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/12159","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12159"}],"version-history":[{"count":3,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/12159\/revisions"}],"predecessor-version":[{"id":12473,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/12159\/revisions\/12473"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12159"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12159"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12159"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}