Derby介绍

Introduction to <city w:st="on"><place w:st="on">Derby</place></city>

Welcome to <city w:st="on"><place w:st="on">Derby</place></city>! <city w:st="on"><place w:st="on">Derby</place></city> is a full-featured, open source relational database management system (RDBMS) that is based on Java™ and SQL.

<city w:st="on"><place w:st="on"><span lang="EN-US">Derby</span></place></city> is written and implemented completely in the Java programming language. <city w:st="on"><place w:st="on">Derby</place></city> provides users with a small-footprint standards-based database engine that can be tightly embedded into any Java based solution. <city w:st="on"><place w:st="on">Derby</place></city> ensures data integrity and provides sophisticated transaction support. In the default configuration there is no separate database server to be installed or maintained by the end user. For more information on <place w:st="on"><city w:st="on">Derby</city></place>, visit the Derby Web site at http://db.apache.org/derby.

Derby是一个全功能的，基于java和sql标准的开源的关系型数据库，它是由著名的开源组织apache发起的一个项目。Derby是一个纯java语句编写的数据库管理系统。Derby是一个非常小型的数据库，可以非常紧密的嵌入到基于java的项目中。本文将介绍如何使用derby进行数据库操作以及如何嵌入的java程序中。在JEE sdk中derby作为了默认的数据库，可见sun对其的支持。

Creating a <place w:st="on"><city w:st="on">Derby</city></place> database and running SQL statements

Now, you will use the <city w:st="on">Derby</city>ij tool to load the <city w:st="on"><place w:st="on">Derby</place></city> database engine. You will use the <city w:st="on"><place w:st="on">Derby</place></city> embedded driver to create and connect to the firstdb database. You will also use a few basic SQL statements to create and populate a table.

1. Run the <city w:st="on"><place w:st="on">Derby</place></city>ij tool. If you included the DERBY_HOME/bin directory in your PATH environment variable, type:

ij

Otherwise, you can use the java command to start the ij tool.

Operating System	Command
UNIX (Korn Shell)	java -jar $DERBY_HOME/lib/derbyrun.jar ij ij version 10.4
Windows	java -jar %DERBY_HOME%/lib/derbyrun.jar ij ij version 10.4

2. Create the database and open a connection to the database using the embedded driver.

CONNECT 'jdbc:derby:firstdb;create=true';

Description of connection command:

connect

The ij command to establish a connection to a database. The <city w:st="on"><place w:st="on">Derby</place></city> connection URL is enclosed in single quotation marks. An ij command can be in either uppercase or lowercase.

jdbc:derby:

The JDBC protocol specification for the <city w:st="on"><place w:st="on">Derby</place></city> driver.

firstdb

The name of the database. The name can be any string. Because no filepath is specified, the database is created in the default working directory (DERBYTUTOR).

;create=true

The <city w:st="on"><place w:st="on">Derby</place></city>URL attribute that is used to create a database. <city w:st="on"><place w:st="on">Derby</place></city> does not have an SQL create database command.

;

The semicolon is the ij command terminator.

3. Create a table with two columns using standard SQL.

4. CREATE TABLE FIRSTTABLE

5. (ID INT PRIMARY KEY,

6. NAME VARCHAR(12));

0 rows inserted/updated/deleted

7. Insert three records.

8. INSERT INTO FIRSTTABLE VALUES

9. (10,'TEN'),(20,'TWENTY'),(30,'THIRTY');

3 rows inserted/updated/deleted

10. Perform a simple select of all records in the table.

11. SELECT * FROM FIRSTTABLE;

12. ID |NAME

13. ------------------------

14. 10 |TEN

15. 20 |TWENTY

16. 30 |THIRTY

17.

3 rows selected

18. Perform a qualified select of the record with column ID=20.

19. SELECT * FROM FIRSTTABLE

20. WHERE ID=20;

21. ID |NAME

22. ------------------------

23. 20 |TWENTY

24.

1 row selected

25. Optional: Create and populate additional tables and other schema objects.
    1. Load the SQL script ToursDB_schema.sql.

b. run 'ToursDB_schema.sql';

c.

d. ij> ...

e. CREATE TABLE AIRLINES

f. (

g. AIRLINE CHAR(2) NOT NULL ,

h. AIRLINE_FULL VARCHAR(24),

i. BASIC_RATE DOUBLE PRECISION,

j. ...

k. 0 rows inserted/updated/deleted

l. ... Other output messages not shown ...

25. 13. Populate the tables with data by running the script loadTables.sql.

n. run 'loadTables.sql';

o.

p. ij> run 'loadCOUNTRIES.sql';

q. ij> insert into COUNTRIES values ( '<country-region w:st="on">Afghanistan</country-region>','AF','<place w:st="on">Asia</place>');

r. 1 row inserted/updated/deleted

s. ij> insert into COUNTRIES values ( '<country-region w:st="on">Albania</country-region>','<state w:st="on">AL</state>','<place w:st="on">Europe</place>');

t. 1 row inserted/updated/deleted

u. ... Other output messages not shown ...

26. Exit the ij tool.

exit;

You should be returned to the DERBYTUTOR directory.

27. Browse the most important files created by this activity:

• The derby.log file. This file is a message and error log that, under normal circumstances, contains a set of startup messages and a shutdown message.

o ----------------------------------------------------------------

o 2008-09-16 20:59:31.661 GMT:

o Booting <city w:st="on"><place w:st="on">Derby</place></city> version The Apache Software Foundation - Apache

o <city w:st="on"><place w:st="on">Derby</place></city> - <chsdate year="1899" month="12" day="30" islunardate="False" isrocdate="False" w:st="on">10.4.2</chsdate>.0 - (689064):

o instance a<chmetcnv unitname="C" sourcevalue="816" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">816c</chmetcnv>00e<chmetcnv unitname="C" sourcevalue="11" hasspace="False" negative="True" numbertype="1" tcsc="0" w:st="on">-011c</chmetcnv>-6cf5-f556<chmetcnv unitname="C" sourcevalue="4" hasspace="False" negative="True" numbertype="1" tcsc="0" w:st="on">-0000004c</chmetcnv><chmetcnv unitname="a" sourcevalue="14" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">14a</chmetcnv>8

o on database directory C:/DERBYTUTOR/firstdb

o

o Database Class Loader started - derby.database.classpath=''

o

o 2008-09-16 21:01:44.314 GMT:

o Shutting down instance a<chmetcnv unitname="C" sourcevalue="816" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">816c</chmetcnv>00e<chmetcnv unitname="C" sourcevalue="11" hasspace="False" negative="True" numbertype="1" tcsc="0" w:st="on">-011c</chmetcnv>-6cf5-f556<chmetcnv unitname="C" sourcevalue="4" hasspace="False" negative="True" numbertype="1" tcsc="0" w:st="on">-0000004c</chmetcnv><chmetcnv unitname="a" sourcevalue="14" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">14a</chmetcnv>8

----------------------------------------------------------------

• The firstdb database directory. Within the directory are the subdirectories seg0 (containing the data files) and log (containing the transaction log files).

下面介绍derby嵌入式编程。

<place w:st="on"><city w:st="on"><b><span lang="EN-US" style="font-size:16.0pt; font-family:宋体;mso-bidi-font-family:宋体;mso-font-kerning:0pt">Derby</span></b></city></place> embedded basics

This section explains how to use and configure <city w:st="on"><place w:st="on">Derby</place></city> in an embedded environment.

Included in the installation is a sample application program, /demo/programs/simple, which illustrates how to run <city w:st="on"><place w:st="on">Derby</place></city> embedded in the calling program.

Embedded <city w:st="on"><place w:st="on">Derby</place></city> JDBC driver

The <city w:st="on"><place w:st="on">Derby</place></city> driver class name for the embedded environment is org.apache.derby.jdbc.EmbeddedDriver.

In a Java application running on JDK 1.5 or lower, you typically load the driver with the static Class.forName method or with the jdbc.drivers system property. If your application runs on JDK 1.6 or higher, then you do not need to explicitly load the EmbeddedDriver. In that environment, the driver loads automatically.

For detailed information about loading the Derby JDBC driver, see "java.sql.Driver" in the Derby Reference Manual.

Embedded Derby JDBC database connection URL

This is the standard Derby JDBC connection URL, which you can use for tasks besides connecting to a database.

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*

Subsubprotocol, which is not typically specified, determines how <city w:st="on"><place w:st="on">Derby</place></city> looks for a database: in a directory, in a class path, or in a jar file. Subsubprotocol is one of the following:

•		directory The default. Specify this explicitly only to distinguish a database that might be ambiguous with one on the class path.
•		classpath Databases are treated as read-only databases, and all databaseNames must begin with at least a slash, because you specify them "relative" to the classpath directory.
•		jar Databases are treated as read-only databases. DatabaseNames might require a leading slash, because you specify them "relative" to the jar file.

jar requires an additional element immediately before the database name:

(pathToArchive)

pathToArchive is the path to the jar or zip file that holds the database.

You typically pass the connection URL as an argument to the JDBC DriverManager.getConnection method call. For example:

DriverManager.getConnection("jdbc:derby:sample");

You can specify attributes and attribute values to a connection URL. For detailed reference about attributes and values, see the Derby Reference Manual.

Getting a nested connection

When you are executing a method within SQL, that method might need to reuse the current connection to the database in order to execute more SQL statements. Such a connection is called a nested connection. The way for a method to get a nested connection is to issue a connection request using the connection URL.

jdbc:default:connection

URL attributes are not supported as part of this connection URL. Any URL attributes specified in a Properties object, user name, or password that are passed to a java.sql.DriverManager.getConnection() call will be ignored.

Starting <city w:st="on"><place w:st="on">Derby</place></city> as an embedded database

To start <city w:st="on"><place w:st="on">Derby</place></city>, you start the Derby JDBC driver. Starting the <city w:st="on">Derby</city> driver starts up the complete <city w:st="on"><place w:st="on">Derby</place></city> system within the current JVM.

For example, when using the JBDC driver manager directly within Java code, you typically start a JDBC driver in one of these ways:

•		Specify the jdbc.drivers system property, which allows users to customize the JDBC drivers used by their applications. For example: java -Djdbc.drivers=org.apache.derby.jdbc.EmbeddedDriver applicationClass
•		Load the class directly from Java code using the static method Class.forName. For example: Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
•		If your application runs on JDK 1.6 or higher, then you do not need to explicitlty load the EmbeddedDriver. In that environment, the driver loads automatically and the engine starts when your application requests its first Connection.

For more details, see "java.sql.Driver" in the <city w:st="on"><place w:st="on"><i>Derby</i></place></city> Reference Manual.

Once the Derby JDBC driver class has been loaded, you can connect to any <city w:st="on"><place w:st="on">Derby</place></city> database by passing the embedded connection URL with the appropriate attributes to the DriverManager.getConnection method.

For example:

Connection conn = DriverManager.getConnection("jdbc:derby:sample");

This program is intended to run in Virtual Machines for the Java Platform supporting J2SE <chsdate year="1899" month="12" day="30" islunardate="False" isrocdate="False" w:st="on">1.4.2</chsdate> or newer.

In embedded mode, the application starts up an instance of <city w:st="on"><place w:st="on">Derby</place></city> within the current Java Virtual Machine (JVM) and shuts down the instance before it completes. No network access is involved. Only one JVM can access a database at a time.

In a client/server environment, the application demonstrates the use of the <city w:st="on"><place w:st="on">Derby</place></city> network client driver or the IBM DB2 JDBC Universal Driver by connecting to the Network Server and running the demo. Note that the client drivers allow multiple instances of the application to run at the same time. However, the SQL operations performed by this demo will cause failures when multiple simultaneous instances of the application are run. Use of a client driver to connect to the Network Server in this application is intended only to demonstrate this type of connection. The SimpleApp demo is not suited for simultaneous executions because it creates and drops the table on which it operates.

Note that in this document, file paths and environment variables are referenced using UNIX notation unless noted otherwise. This means that if, for example, you are using a Windows platform, you must substitute file separators, path separators and environment variable references to the Windows-specific notation, for example:

Element	Example, UNIX	Example, Windows
File separator	/home/path/file	c:/path/file
(CLASS)PATH separator	derby.jar:derbytools.jar	derby.jar;derbytools.jar
Environment Variable reference	$DERBY_HOME	%DERBY_HOME%

Also note that this document refers to the JVM launch command as java, and that it is being assumed that the JVM installation's launcher is available via the system's PATH. Refer to the documentation of your JVM / runtime environment for details on how to run a Java program using that JVM.