Derby Database

Table of Contents

1 Derby简介

Apache Derby (previously distributed as IBM Cloudscape) is a relational database management system (RDBMS) developed by the Apache Software Foundation that can be embedded in Java programs and used for online transaction processing. It is implemented entirely in Java.

Derby使用的SQL语法来自IBM DB2 SQL,而不是Oracle Database。(如:Derby中的sequence和trigger的语法和DB2类似,和Oracle Database有很多不同)。

Derby最大的优点是容易安装、部署和使用。

参考:
Official website: https://db.apache.org/derby/index.html
Apache Derby: Quick Start: https://db.apache.org/derby/quick_start.html
Derby Reference Manual(v10.8): https://db.apache.org/derby/docs/10.8/ref/index.html

2 安装和部署

下面以Derby 10.8.2.2为例介绍其安装和部署的过程。

第一步,下载Derby程序。https://db.apache.org/derby/derby_downloads.html

$ wget http://archive.apache.org/dist/db/derby/db-derby-10.8.2.2/db-derby-10.8.2.2-bin.zip
$ unzip db-derby-10.8.2.2-bin.zip
$ ls -l db-derby-10.8.2.2-bin
total 200
-rw-r--r--   1 cig01  staff  39891 Oct 10  2011 KEYS
-rw-r--r--   1 cig01  staff  11560 Oct 10  2011 LICENSE
-rw-r--r--   1 cig01  staff   7323 Oct 10  2011 NOTICE
-rw-r--r--   1 cig01  staff  29693 Oct 10  2011 RELEASE-NOTES.html
drwxr-xr-x  21 cig01  staff    714 Oct 10  2011 bin
drwxr-xr-x   6 cig01  staff    204 Oct 10  2011 demo
drwxr-xr-x   4 cig01  staff    136 Oct 10  2011 docs
-rw-r--r--   1 cig01  staff   5629 May 11  2011 index.html
drwxr-xr-x   4 cig01  staff    136 Oct 10  2011 javadoc
drwxr-xr-x  21 cig01  staff    714 Oct 10  2011 lib
drwxr-xr-x   5 cig01  staff    170 Oct 10  2011 test

第二步,测试工具ij是否可以启动。
The Derby ij tool is a JDBC tool that you can use to run scripts or interactive queries against a Derby database.

有很多方法可以启动工具ij,如下面任意方法都可以:

$ ./db-derby-10.8.2.2-bin/bin/ij                        # 方法一
ij version 10.8
ij> exit;
$ java -jar db-derby-10.8.2.2-bin/lib/derbyrun.jar ij   # 方法二
ij version 10.8
ij> exit;
$ java org.apache.derby.tools.ij                        # 方法三,(要手动设置CLASSPATH)

说明1:方法一中运行的ij是个shell脚本。上面三种方法,最后运行的都是Java类org.apache.derby.tools.ij。
说明2:使用方法三时,要手动设置CLASSPATH。Derby自带了几个脚本(位于DERBY_HOME/bin目录下)帮助我们配置CLASSPATH环境变量。

setEmbeddedCP
Use the setEmbeddedCP script to set the classpath when the database engine is used in embedded mode. This script adds the derby.jar, derbytools.jar and derbyoptionaltools.jar files to the classpath.
setNetworkServerCP
Use the setNetworkServerCP script to set the classpath when you want to start the network server. This script adds the derbynet.jar file to the classpath.
setNetworkClientCP
Use the setNetworkClientCP script to set the classpath when you want to access databases using the network client. This script adds the derbyclient.jar, derbytools.jar and derbyoptionaltools.jar files to the classpath.
$ export JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk1.8.0_66.jdk/Contents/Home
$ export DERBY_HOME=/Users/cig01/test/db-derby-10.8.2.2-bin
$ echo $CLASSPATH                  # 设置setEmbeddedCP之前,CLASSPATH为空

$ . $DERBY_HOME/bin/setEmbeddedCP
$ echo $CLASSPATH                  # 设置setEmbeddedCP后,CLASSPATH已有值
/Users/cig01/test/db-derby-10.8.2.2-bin/lib/derby.jar:/Users/cig01/test/db-derby-10.8.2.2-bin/lib/derbytools.jar:
$ java org.apache.derby.tools.ij
ij version 10.8
ij> exit;

3 工具ij的基本使用

下面演示创建数据库,创建表,插入和查询记录等基本使用。

$ export PATH=$DERBY_HOME/bin:$PATH
$ ij
ij version 10.8
ij> CONNECT 'jdbc:derby:firstdb;create=true';                                # 指定create=true会在当前目录创建数据库
ij> CREATE TABLE FIRSTTABLE (ID INT PRIMARY KEY, NAME VARCHAR(12));
0 rows inserted/updated/deleted
ij> INSERT INTO FIRSTTABLE VALUES (10,'TEN'),(20,'TWENTY'),(30,'THIRTY');
3 rows inserted/updated/deleted
ij> SELECT * FROM FIRSTTABLE;
ID         |NAME
------------------------
10         |TEN
20         |TWENTY
30         |THIRTY

3 rows selected
ij> exit;
$ ls -F                                                                      # 当前目录中会生成log文件以及数据库相关文件
derby.log  firstdb/

上面例子中生成的数据库相关文件保存在当前目录中,下次在Derby中可直接使用。

$ ij
ij version 10.8
ij> CONNECT 'jdbc:derby:firstdb';                   # 连接已存在数据库
ij> select * from firsttable;
ID         |NAME
------------------------
10         |TEN
20         |TWENTY
30         |THIRTY

3 rows selected
ij> exit;

上面的实例摘自:Creating a Derby database and running SQL statements

4 访问其它机器上的Derby服务器

机器A运行Derby服务器,机器B作为客户端访问机器A中的Derby数据库。

第一步,在机器A中启动Derby服务器。


$ ./db-derby-10.8.2.2-bin/bin/startNetworkServer -h 0.0.0.0 -p 1527
Fri Dec 11 22:55:28 CST 2015 : Apache Derby Network Server - 10.8.2.2 - (1181258) started and ready to accept connections on port 1527

说明1:要使数据库能从其它机器访问,一定要指定 -h 0.0.0.0 ,否则Derby服务器监听在localhost上,只能从机器A本机访问。
说明2:1527是默认端口,可以省略 -p 1527

第二步,在机器B中用ij工具连接到机器B(假设机器B的IP为192.168.1.4)。
连接时使用的URL格式为:

jdbc:derby://<server>[:<port>]/<databaseName>[;<URL attribute>=<value> [;...]]

参考:https://db.apache.org/derby/docs/10.8/adminguide/cadminappsclient.html

演示实例如下:

$ ij
ij version 10.8
ij> CONNECT 'jdbc:derby://192.168.1.4:1527/firstdb';
ERROR 08004: The connection was refused because the database firstdb was not found.
ij> CONNECT 'jdbc:derby://192.168.1.4:1527/firstdb;create=true';
ij> exit;

参考:Derby - Starting the Network Server

5 JDBC访问Derby

下面演示一个在Java中用JDBC访问Derby数据库的例子。

完整的程序源码如下:

// file JDBCExample.java
// 运行这个程序前,Derby数据库必须启动。可以这样启动:
// $ ./db-derby-10.8.2.2-bin/bin/startNetworkServer -noSecurityManager
// 数据库 firstdb 必须存在,表 FIRSTTABLE 必须存在。否则会报异常。可以这样创建:
// ij> CONNECT 'jdbc:derby:firstdb;create=true';
// ij> CREATE TABLE FIRSTTABLE (ID INT PRIMARY KEY, NAME VARCHAR(12));

public class JDBCExample {
  static final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
  // 如果要使用嵌入方式的Derby,其驱动名为:"org.apache.derby.jdbc.EmbeddedDriver"
  static final String CONNECTION_URL = "jdbc:derby://localhost:1527/firstdb";

  public static void main(String[] args) {

    Connection conn = null;
    Statement stmt = null;
    try{
      //STEP 1: Register JDBC driver
      Class.forName(JDBC_DRIVER);

      System.out.println("Connecting to a selected database.");

      //STEP 2: Open a connection
      conn = DriverManager.getConnection(CONNECTION_URL);

      System.out.println("Connected database successfully.");

      //STEP 3: Execute a query
      stmt = conn.createStatement();

      String sql = "INSERT INTO FIRSTTABLE VALUES (50, 'HAHA')";
      stmt.executeUpdate(sql);

      sql = "INSERT INTO FIRSTTABLE VALUES (60, 'HOHO')";
      stmt.executeUpdate(sql);
      System.out.println("Inserted two records into the table.");

    }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
    }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
    }finally{
      //finally block used to close resources
      try{
        if(stmt!=null)
          stmt.close();
      }catch(SQLException se){
      }
      try{
        if(conn!=null)
          conn.close();
      }catch(SQLException se){
        se.printStackTrace();
      }//end finally try
    }
    System.out.println("Goodbye!");
  }//end main
}

编译运行如下:

$ javac JDBCExample.java
$ java -cp /Users/cig01/test/db-derby-10.8.2.2-bin/lib/derbyclient.jar:. JDBCExample
Connecting to a selected database.
Connected database successfully.
Inserted two records into the table.
Goodbye!

程序成功执行后,检测数据库,确实插入了两条记录。

ij> select * from firsttable;
ID         |NAME
------------------------
50         |HAHA
60         |HOHO

参考:http://www.tutorialspoint.com/jdbc/index.htm

6 Tips

6.1 startNetworkServer时提示access denied错误

如果你使用的JRE版本大于1.7.0_51,运行 startNetworkServer 时可能出现下面错误:

java.security.AccessControlException: access denied ("java.net.SocketPermission" "localhost:1527" "listen,resolve")

原因是"The default socket permissions assigned to all code including untrusted code have been changed in this release."

解决办法一:用 -noSecurityManager 参数启动startNetworkServer

$ ./db-derby-10.8.2.2-bin/bin/startNetworkServer -noSecurityManager

解决办法二:这其定制policy。
修改文件$JAVA_HOME/jre/lib/security/java.policy,或者文件~/.java.policy。
如:

$ cat ~/.java.policy
grant {
    permission java.net.SocketPermission "localhost:1527", "listen";
};

参考:http://stackoverflow.com/questions/21154400/unable-to-start-derby-database-from-netbeans-7-4

6.2 修改监听的端口号

指定startNetworkServer的-p参数,可修改监听的端口号。如:

$ startNetworkServer -h 0.0.0.0 -p 1234

6.3 显示数据库中所有的表

在ij中输入 show tables 可显示数据库中所有的表。

6.4 显示表的定义

在ij中输入 describe tab1 可显示表tab1的定义。

6.5 查看Derby数据库的当前活动连接(可通过JMX)

使用jconsole连接到Derby server所在JVM进程,再通过浏览MBeans可得到Derby数据库的当前活动连接。如图 1 中“ActiveConnectionCount”所表示的就是Derby数据库的当前活动连接。

derby_nserver_attribs.png

Figure 1: Accessing Derby's MBeans

参考:Derby JMX Quick Start


Author: cig01

Created: <2015-11-12 Thu 00:00>

Last updated: <2017-03-16 Thu 17:16>

Creator: Emacs 25.1.1 (Org mode 9.0.7)