学习使用jdk1.7中内置数据库Derby(一)

Java内嵌数据库Derby环境配置和使用
    Derby数据库是一个纯用Java实现的内存数据库，属于Apache的一个开源项目。由于是用Java实现的，所以可以在任何平台上运行；另外一个特点是体积小，免安装，只需要几个小jar包就可以运行了。jdk1.6和jdk1.7内嵌了Derby数据库,安装在jdk的安装目录下的子
目录db中,以下以jdk1.7为例说明,db目录中有:

1） bin目录，包含了一些工具脚本和设备环境的脚本；
2） lib目录，包含了Derby数据库的jar文件；

一、Derby数据库有两种运行模式：
1） 内嵌模式。Derby数据库与应用程序共享同一个JVM，通常由应用程序负责启动和停止，对除启动它的应用程序外的其它应用程序不可见，即其它应用程序不可访问它；


2） 网络模式。Derby数据库独占一个JVM，做为服务器上的一个独立进程运行。在这种模式下，允许有多个应用程序来访问同一个Derby数据库。

二、 使用Derby脚本
   Derby提供了几个脚本来操作Derby数据库，在使用这些脚本前，你必须先设置好Derby的运行环境。


   下面的例子都是在命令行下设置环境变量，这些设置都是临时的，如果你新开一个命令行窗口，则需要重新设置一遍，如果想要使环境变量永久生效，可以在我的电脑中进行设置或写一个.bat文件每次运行。

   首先设置好DERBY_HOME这个环境变量，为DERBY_HOME指定你的derby目录，这里用jdk1.7内带的derby,所以,则可以如下设置：
set DERBY_HOME=c:\jdk1.7\db
将DERBY_HOME\bin目录添加到PATH环境变量中：
set path=%DERBY_HOME%\bin;%PATH%

你可以将上面的设置写成一个bat文件: derby.bat
set DERBY_HOME=c:\jdk1.7\db
set path=%DERBY_HOME%\bin;%PATH%
如果你以内嵌模式使用Derby数据库,那么在运行和编译java程序前先运行:
derby.bat和setEmbeddedCP.bat

   这样可以简化你稍后在命令行中的输入，否则你每次都必须使用脚本的全路径或者你必须到DERBY_HOME\bin目录中才能执行脚本。最后需要将Derby的jar包添加到classpath环境变量中，在DERBY_HOME%\bin目录中提供了几个脚本用于设置classpath，
以简化你手工在classpath中添加jar包的麻烦：


1） setEmbeddedCP。当使用内嵌模式来运行Derby时，可以使用该脚本来设置。
    该脚本将derby.jar和derbytools.jar添加到环境变量中；

2） setNetworkServerCP。当使用网络模式来运行Derby时，用该脚本来设置Derby服务端的classpath变量。
    该脚本将derbynet.jar添加到环境变量中；

3） setNetworkClientCP。当使用网络模式来运行Derby时，用该脚本来设置Derby客户端的classpath变量。
    该脚本将derbyclient.jar和derbytools.jar添加到环境变量中。

例:使用嵌入式数据库Derby一个基本的例子(建数据库、建表、插入数据、查询)

import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Statement;
  import java.sql.SQLException;
  import java.util.Properties;

  
  public class TestDerbyBaisc {
   public static void main(String[] args) throws SQLException{
     try {          
           
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();//加载驱动
Connection conn = DriverManager.getConnection("jdbc:derby:TESTDB;create=true");//连接数据库
      Statement st = conn.createStatement();
 st.execute("create table USER_INFO (ID INT NOT NULL,NAME VARCHAR(10) NOT NULL)");//建表
      st.executeUpdate("insert into USER_INFO(ID,NAME) values (1,'hermit')");//插入数据
      st.executeUpdate("insert into USER_INFO(ID,NAME) values (2,'test')");//插入数据
      ResultSet rs = st.executeQuery("select * from USER_INFO");//读取刚插入的数据
             while(rs.next()){
                 int id = rs.getInt(1);
                 String name = rs.getString(2);
                 System.out.println("ID="+id);
                 System.out.println("NAME="+name);
             }
         } catch(Exception e){
             DriverManager.getConnection("jdbc:derby:;shutdown=true");//关闭数据库
             e.printStackTrace();
         }
     }
 }

运行以后输出
ID=1
NAME=hermit
ID=2
NAME=test

在当前目录下会生成一个文件数据库文件夹TESTDB

建立好连接后，其它的数据操作，如查询、更新数据都和其它数据库一样，这里不详述。有一点需要注意，通过Java应用程序访问内嵌模式Derby数据库时，应用程序有责任需要在程序结束时关闭Derby数据库，如上面代码中的
DriverManager.getConnection("jdbc:derby:;shutdown=true");
shutdown参数用于关闭Derby数据库，如果url中指定了数据库名，则只会关闭指定的数据库，而不会关闭整个Derby数据库。

最后看一下jdk1.7中带的演示程序:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Properties;

public class SimpleApp
{
    /* the default framework is embedded*/
    private String framework = "embedded";
    private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    private String protocol = "jdbc:derby:";

    
    public static void main(String[] args)
    {
        new SimpleApp().go(args);
        System.out.println("SimpleApp finished");
    }

   
    void go(String[] args)
    {
       
        parseArguments(args);

   System.out.println("SimpleApp starting in " + framework + " mode");

        /* load the desired JDBC driver */
        loadDriver();

       
        Connection conn = null;
	
   ArrayList statements = new ArrayList(); //list of Statements, PreparedStatements
        PreparedStatement psInsert = null;
        PreparedStatement psUpdate = null;
        Statement s = null;
        ResultSet rs = null;
        try
        {
         Properties props = new Properties(); // connection properties
     // providing a user name and password is optional in the embedded
            // and derbyclient frameworks
            props.put("user", "user1");
            props.put("password", "user1");

            

            String dbName = "derbyDB"; // the name of the database

            conn = DriverManager.getConnection(protocol + dbName
                    + ";create=true", props);

    System.out.println("Connected to and created database " + dbName);

     // We want to control transactions manually. Autocommit is on by
            // default in JDBC.
            conn.setAutoCommit(false);

    /* Creating a statement object that we can use for running various
             * SQL statements commands against the database.*/
            s = conn.createStatement();
            statements.add(s);

            // We create a table...
       s.execute("create table location(num int, addr varchar(40))");
            System.out.println("Created table location");

            // and add a few rows...

            
            // parameter 1 is num (int), parameter 2 is addr (varchar)
            psInsert = conn.prepareStatement(
                        "insert into location values (?, ?)");
            statements.add(psInsert);

            psInsert.setInt(1, 1956);
            psInsert.setString(2, "Webster St.");
            psInsert.executeUpdate();
            System.out.println("Inserted 1956 Webster");

            psInsert.setInt(1, 1910);
            psInsert.setString(2, "Union St.");
            psInsert.executeUpdate();
            System.out.println("Inserted 1910 Union");

            // Let's update some rows as well...

     // parameter 1 and 3 are num (int), parameter 2 is addr (varchar)
            psUpdate = conn.prepareStatement(
                     "update location set num=?, addr=? where num=?");
            statements.add(psUpdate);

            psUpdate.setInt(1, 180);
            psUpdate.setString(2, "Grand Ave.");
            psUpdate.setInt(3, 1956);
            psUpdate.executeUpdate();
            System.out.println("Updated 1956 Webster to 180 Grand");

            psUpdate.setInt(1, 300);
            psUpdate.setString(2, "Lakeshore Ave.");
            psUpdate.setInt(3, 180);
            psUpdate.executeUpdate();
            System.out.println("Updated 180 Grand to 300 Lakeshore");


            /*
               We select the rows and verify the results.
             */
            rs = s.executeQuery(
                    "SELECT num, addr FROM location ORDER BY num");

            
            int number; // street number retrieved from the database
            boolean failure = false;
            if (!rs.next())
            {
                failure = true;
                reportFailure("No rows in ResultSet");
            }

            if ((number = rs.getInt(1)) != 300)
            {
                failure = true;
 reportFailure("Wrong row returned, expected num=300, got " + number);
            }

            if (!rs.next())
            {
                failure = true;
                reportFailure("Too few rows");
            }

            if ((number = rs.getInt(1)) != 1910)
            {
                failure = true;
   reportFailure("Wrong row returned, expected num=1910, got " + number);
            }

            if (rs.next())
            {
                failure = true;
                reportFailure("Too many rows");
            }

            if (!failure) {
                System.out.println("Verified the rows");
            }

            // delete the table
            s.execute("drop table location");
            System.out.println("Dropped table location");

            /*
               We commit the transaction. Any changes will be persisted to
               the database now.
             */
            conn.commit();
            System.out.println("Committed the transaction");

          

            if (framework.equals("embedded"))
            {
                try
                {
                    // the shutdown=true attribute shuts down Derby
                    DriverManager.getConnection("jdbc:derby:;shutdown=true");

                    // To shut down a specific database only, but keep the
                    // engine running (for example for connecting to other
                    // databases), specify a database in the connection URL:
                    //DriverManager.getConnection("jdbc:derby:" + dbName + ";shutdown=true");
                }
                catch (SQLException se)//关闭数据库时会产生异常
                {
                    if (( (se.getErrorCode() == 50000)
                && ("XJ015".equals(se.getSQLState()) ))) {//这是正常关闭
                        // we got the expected exception
                System.out.println("Derby shut down normally");
            // Note that for single database shutdown, the expected
            // SQL state is "08006", and the error code is 45000.
                    } else {
               // if the error code or SQLState is different, we have
                        // an unexpected exception (shutdown failed)
               System.err.println("Derby did not shut down normally");
                        printSQLException(se);
                    }
                }
            }
        }
        catch (SQLException sqle)
        {
            printSQLException(sqle);
        } finally {
       // release all open resources to avoid unnecessary memory usage

            // ResultSet
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
            } catch (SQLException sqle) {
                printSQLException(sqle);
            }

            // Statements and PreparedStatements
            int i = 0;
            while (!statements.isEmpty()) {
                // PreparedStatement extend Statement
                Statement st = (Statement)statements.remove(i);
                try {
                    if (st != null) {
                        st.close();
                        st = null;
                    }
                } catch (SQLException sqle) {
                    printSQLException(sqle);
                }
            }

            //Connection
            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException sqle) {
                printSQLException(sqle);
            }
        }
    }

    private void loadDriver() {
        
        try {
            Class.forName(driver).newInstance();
            System.out.println("Loaded the appropriate driver");
        } catch (ClassNotFoundException cnfe) {
     System.err.println("\nUnable to load the JDBC driver " + driver);
            System.err.println("Please check your CLASSPATH.");
            cnfe.printStackTrace(System.err);
        } catch (InstantiationException ie) {
            System.err.println(
                "\nUnable to instantiate the JDBC driver " + driver);
            ie.printStackTrace(System.err);
        } catch (IllegalAccessException iae) {
            System.err.println(
                "\nNot allowed to access the JDBC driver " + driver);
            iae.printStackTrace(System.err);
        }
    }

    /**
     * Reports a data verification failure to System.err with the given message.
     *
     * @param message A message describing what failed.
     */
    private void reportFailure(String message) {
        System.err.println("\nData verification failed:");
        System.err.println('\t' + message);
    }

    /**
     * Prints details of an SQLException chain to <code>System.err</code>.
     * Details included are SQL State, Error code, Exception message.
     *
     * @param e the SQLException from which to print details.
     */
    public static void printSQLException(SQLException e)
    {
        // Unwraps the entire exception chain to unveil the real cause of the
        // Exception.
        while (e != null)
        {
            System.err.println("\n----- SQLException -----");
            System.err.println("  SQL State:  " + e.getSQLState());
            System.err.println("  Error Code: " + e.getErrorCode());
            System.err.println("  Message:    " + e.getMessage());
            // for stack traces, refer to derby.log or uncomment this:
            //e.printStackTrace(System.err);
            e = e.getNextException();
        }
    }

   
    private void parseArguments(String[] args)
    {
        if (args.length > 0) {
            if (args[0].equalsIgnoreCase("derbyclient"))
            {
                framework = "derbyclient";
                driver = "org.apache.derby.jdbc.ClientDriver";
                protocol = "jdbc:derby://localhost:1527/";
            }
        }
    }
}

程序运行:
D:\db>java  SimpleApp
SimpleApp starting in embedded mode
Loaded the appropriate driver
Connected to and created database derbyDB
Created table location
Inserted 1956 Webster
Inserted 1910 Union
Updated 1956 Webster to 180 Grand
Updated 180 Grand to 300 Lakeshore
Verified the rows
Dropped table location
Committed the transaction
Derby shut down normally
SimpleApp finished

源码下载: