JAVAWEB APP访问Derby数据库

1.开发环境

Tomcat7，Derby10.13， Idea

2.下载安装Derby

默认情况下jdk6以上自带Derby数据库，但是也可以直接下载Derby数据库。下载地址：http://db.apache.org/derby

下载后得到如下包：

db-derby-10.13.1.1-bin.zip

解压即可使用。

3.设置环境变量

设置DERBY_HOME变量为解压后的目录路径，然后在CLASSPATH中添加

$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbyclient.jar:$DERBY_HOME/lib/derbytools.jar:$DERBY_HOME/lib/derbynet.jar

4.运行如下命令启动Derby数据库

change to 将要存放数据库文件的目录

java -jar $DERBY_HOME/lib/derbyrun.jar server start

5. 使用ij工具连接数据库

首先创建文件ij.properties内容:

ij.driver=org.apache.derby.jdbc.ClientDriver

ij.protocol=jdbc:derby://localhost:1527/

ij.database=test;create=true

打开另一个命令行窗口执行:

java -jar $DERBY_HOME/lib/derbyrun.jar ij -p ij.properties

此时ij工具就连接到了数据库，可以使用SHOW命令显示数据库内容：

SHOW tables;显示所有表

使用SQL命令创建表，插入数据等

EXIT;命令退出ij工具

6.JAVAWEB APP访问derby，这里使用Client/Server模式

使用IDE创建好JAVAWEB APP，将$DERBY_HOME/lib/derbyclient.jar拷贝到APP的WEB-INF/lib目录。

然后编写数据库访问代码，并在servlet或者jsp中调用，代码如下：

import java.io.PrintWriter;
import java.sql.*;

public class DBUtils {
    //jdbc:derby://localhost:1527/test
    private static String driver = "org.apache.derby.jdbc.ClientDriver";
    private static String protocol = "jdbc:derby:";
    private static String address = "//localhost:1527/";
    private static String dbName = "test";

    private static Connection conn = null;

    public static void loadDriver() {
        try {
            Class.forName(driver).newInstance();
        } catch (Exception e) {
            ...
        }
    }

    public static void getConnection() {
        try {
            conn = DriverManager.getConnection(protocol + address + dbName);
        } catch (Exception ex) {
            ...
        }
    }


    public static void getDataFromDerby(PrintWriter out) {
        if (conn == null) {
            out.println("init failure");
            return;
        }
        try {
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery("select * from test");
            while(resultSet.next()) {
                out.println(resultSet.getString(1));
            }
            resultSet.close();
            statement.close();

        } catch (Exception e) {
            ...
        }
    }

    public static void releaseConn() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                ...
            }
        }
    }

    public static void unLoadDriver() {
        try {
            /* In embedded mode, an application should shut down Derby.
             Shutdown throws the XJ015 exception to confirm success. */
            if (driver.equals("org.apache.derby.jdbc.EmbeddedDriver")) {
                DriverManager.getConnection(protocol + address + dbName  + ";shutdown=true");
            }
        } catch (Exception ex) {
           
            ...
        }
    }
}

7.关闭Derby

java -jar $DERBY_HOME/lib/derbyrun.jar server shutdown