Getting Started with Java DB
Version 10.3
Derby Document build:
July 24, 2007, 1:18:40 PM (EDT)
Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below). "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives. Copyright [yyyy] [name of copyright owner] Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
• | Access the Self-study
tutorial to get up and running with Derby. This tutorial demonstrates
how to use Derby in the
embedded and client/server configurations. | |
• | If you are an experienced JDBC programmer, you should also see the Quick start guide for experienced JDBC users. |
• | The demo subdirectory contains the demonstration programs. | |
• | The bin subdirectory contains the scripts for executing utilities
and setting up the environment. | |
• | The javadoc subdirectory contains the api documentation
that was generated from source code comments. | |
• | The docs subdirectory contains the Java DB documentation. | |
• | The lib subdirectory contains the Java DB .jar
files. | |
• | The frameworks subdirectory contains older scripts for executing
utilities and setting up the environment. These are provided in this release
for backward compatibility. These scripts are deprecated in favor of the scripts
in the bin directory, and will be removed in a future release. |
1.
| Choose the method that you want to use:
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||
2.
| Based on the requirements of the method that you chose to run the
tools, follow the instructions to set
the environment variables. |
1.
| Set the DERBY_HOME environment
variable to the location where you extracted the Derby bin distribution. For example, if you installed Derby in
the /opt/Derby_10 directory
on UNIX or in the c:\Derby_10 directory
on Windows, use the following command to set the DERBY_HOME environment
variable:
| |||||||
2.
| Be certain that the java.exe file, version 1.4.2
or, higher is in your command execution PATH. Open a command window and run
the java -version command. | |||||||
3.
| Add the DERBY_HOME/bin directory
to the PATH environment variable so that you can run the Derby scripts
from any directory.
Tip: When the DERBY_HOME environment
variable is set and the underlying /bin directory is included
in the PATH environment variable, you can use shortened commands to start
the Derby tools. Otherwise,
either you must be in the directory where the script that starts the Derby tool
is located, or you must specify the full path to the location of the script
when you want to start the tool. |
Operating System | Command |
UNIX (Korn Shell) |
|
Windows |
|
• | On UNIX, use echo $CLASSPATH | |
• | On Windows, use echo %CLASSPATH% |
• | For the dblook tool, call the script and specify the -d option
and the full URL to the Network Server database. For example:
| |
• | For the ij tool, issue the command and then start ij by issuing the command ij. | |
• | For the sysinfo tool, issue the command
|
Method | When to Use | Command |
Run sysinfo as a standalone command. | Use this method if you are relatively new to the Java programming
language and new to Derby. | You must set
your environment variables before you can run the sysinfo utility
using this method. To run the sysinfo script from the
command line us:
The sysinfo script
sets the appropriate environment variables, including the CLASSPATH, and runs
the sysinfo utility. |
Run sysinfo using the jar file that is located in
the directory where sysinfo resides. | Use this method if you are new to Derby,
but are familiar with the Java programming language. | You must set the DERBY_HOME environment
variable before you can run the sysinfo utility using this
method. On UNIX, the command is:
On
Windows, the command is:
|
Run sysinfo using the java command. | Use this method if you are familiar with both the Java programming
language and Derby, and
you have already set the java.exe file in your command execution
PATH. | You must set your CLASSPATH. Use the steps specified in Manually setting the CLASSPATH environment variable.
Then specify the class name in the java command. For example:
|
• | Choose the method that you can use to run the ij script:
| |||||||||||||
• | When you are ready to leave the ij tool, type:
|
Method | When to Use | Command |
Run dblook as a standalone command. | Use this method if you are relatively new to the Java programming
language and new to Derby. | You must set
your environment variables before you can run the dblook utility
using this method. To run the dblook script from the command
line use:
The dblook script
sets the appropriate environment variables, including the CLASSPATH, and runs
the dblook utility. |
Run dblook using the jar file that is located in
the directory where dblook resides. | Use this method if you are new to Derby,
but are familiar with the Java programming language. | You must set the DERBY_HOME environment
variable before you can run the dblook utility using this
method. On UNIX, the command is:
On
Windows, the command is:
|
Run dblook using the java command. | Use this method if you are familiar with both the Java programming
language and Derby, and
you have already set the java.exe file in your command execution
PATH. | You must set your CLASSPATH. Use the steps specified in Manually setting the CLASSPATH environment variable.
Then specify the class name in the java command. For example:
|
1.
| Verify that the java.exe file, version 1.4.2 or,
higher is in your command execution PATH by opening a command window and running
the java -version command. The output from
the command looks something like this: The output you see might be different from what is shown here because the java -version command outputs vendor-specific information. If the command produces an error or the version listed is not 1.4 or higher, install a JDK before continuing. | ||||||||||
2.
| Verify that the DERBY_HOME
environment variable is set and points to the root directory of the Derby 10.3 installation. Open
a command window and run the appropriate command for your system. If
you installed Derby in
the /opt/Derby_10 directory
on UNIX or the c:\Derby_10 directory
on Windows, the command that you use and the expected output are shown in
the following table:
If you receive an error or do not see the expected
output, ensure that you have set the DERBY_HOME environment
variable, as described in Setting the environment variables. |
• | A Java Development Kit (JDK) version 1.4 or higher installed on your computer | |||||||
• | The binary (bin) installation of Apache Derby version 10.3 installed on your computer | |||||||
• | A basic knowledge of the computer command line interface:
|
1.
| Open a command window and change to a directory where you want
to store the files that you create during the self-study tutorial activities. | |||||||
2.
| Create the DERBYTUTOR directory. DERBYTUTOR will
be your working directory for this activity.
| |||||||
3.
| Change to the DERBYTUTOR directory.
| |||||||
4.
| Copy the SQL scripts from the Derby demo\programs\toursdb subdirectory
into the DERBYTUTOR directory. You will use these scripts
to create tables and add data to a new database, toursdb.
| |||||||
5.
| Verify that the files were copied to the DERBYTUTOR directory.
>
Important: Include the dot (.)
at the end of each command so that your current working directory is included
in the classpath and the files are copied to the correct location. |
1.
| Run the Derby ij
tool. If you included the DERBY_HOME/bin directory
in your PATH environment variable, type: Otherwise, you can use the java command to start the ij tool.
| |||||||
2.
| Create the database and open a connection to the database using
the embedded driver.
Description of connection command: connect The ij command to establish a connection to a database.
The Derby 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 Derby 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 Derby URL
attribute that is used to create a database. Derby 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.
| Insert three records.
| |||||||
5.
| Perform a simple select of all records in the table.
| |||||||
6.
| Perform a qualified select of the record with column ID=20.
| |||||||
7.
| Optional: Create and populate additional tables and other schema
objects.
| |||||||
8.
| Exit the ij tool.
You should be returned to the DERBYTUTOR directory. | |||||||
9.
| Browse the most important files created by this activity:
|
1.
| Open a command window (Shell-1) and change to the DERBYTUTOR directory. | |||||||
2.
| Start the Network Server.
A Network Server startup message appears
in the Shell-1 command window. | |||||||
3.
| Open another command window (Shell-2). Change to the DERBYTUTOR directory. | |||||||
4.
| Start ij. If you included the DERBY_HOME/bin directory
in your PATH environment variable, type: Otherwise, you can use the java command to start the ij tool.
You will enter all subsequent commands
from the network client, so you will type the commands in the Shell-2 command
window. | |||||||
5.
| Create and open a connection to the database using the client driver.
Remember: A client connection URL
contains a hostname and a port number. For example: //localhost:1527/ | |||||||
6.
| Create a table with two columns (ID and NAME) using the following
SQL statement:
| |||||||
7.
| Insert three records into the table.
| |||||||
8.
| Select all of the records in the table.
| |||||||
9.
| Select a subset of records from the table by specifying a WHERE clause.
| |||||||
10.
| Exit ij.
| |||||||
11.
| Shut down the Derby Network
Server.
The server shutdown confirmation appears
in both command windows. |
Apache Derby Network Server - 10.2.2.0 - (485682) started and ready to accept connections on port 1527 at 2007-05-04 20:04:33.511 GMT ... Database engine messages not shown ... 2007-05-04 20:08:43.883 GMT: Shutting down instance c013800d-010f-82e9-fa03-00000011f280
1.
| Copy the program files into the DERBYTUTOR directory
and set the CLASSPATH environment variable.
>
Important: Include the dot (.)
at the end of each command so that your current working directory is included
in the classpath and the files are copied to the correct location. | |||||||
2.
| Compile the WwdEmbedded.java program.
>
Important: A command prompt appears if the compilation
is successful. The binary file WwdEmbedded.class is created.
If an error message appears, verify that the JDK is properly installed. | |||||||
3.
| Run the program. The WwdEmbedded.java program populates
a table with wish-list items. The program prompts you for text input (up to
32 characters), stores the text input in a database table, and then lists
the items stored in the table. The program continues to ask for wish-list
items until the you type the command exit or a problem is
encountered. Some basic information on program progress is displayed at the
beginning and the end of the program.
|
import java.sql.*; public class WwdEmbedded { public static void main(String[] args) {
String driver = "org.apache.derby.jdbc.EmbeddedDriver"; String dbName="jdbcDemoDB"; String connectionURL = "jdbc:derby:" + dbName + ";create=true"; ... String createString = "CREATE TABLE WISH_LIST " + "(WISH_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY " ... + " WISH_ITEM VARCHAR(32) NOT NULL) " ;
String driver = "org.apache.derby.jdbc.EmbeddedDriver"; ... try { Class.forName(driver); } catch(java.lang.ClassNotFoundException e) { ... }
String connectionURL = "jdbc:derby:" + dbName + ";create=true"; ... try { conn = DriverManager.getConnection(connectionURL); ... <most of the program code is contained here> } catch (Throwable e) { ... }
s = conn.createStatement(); if (! WwdUtils.wwdChk4Table(conn)) { System.out.println (" . . . . creating table WISH_LIST"); s.execute(createString); }
psInsert = conn.prepareStatement ("insert into WISH_LIST(WISH_ITEM) values (?)");
• | The setString method sets the substitution parameter
of the psInsert object to the value typed by the user. Then
the executeUpdate method performs the database insert.
| |
• | The statement object s is used to select all the records
in the WISH_LIST table and store them in the ResultSet named myWishes. The while loop reads each record in turn by calling the next method. The getTimestamp and getString methods return specific fields in the record in the proper format. The fields are displayed using rudimentary formatting. Close the ResultSet to release the memory being used.
|
if (driver.equals("org.apache.derby.jdbc.EmbeddedDriver")) { boolean gotSQLExc = false; try { DriverManager.getConnection("jdbc:derby:;shutdown=true"); } catch (SQLException se) { if ( se.getSQLState().equals("XJ015") ) { gotSQLExc = true; } } if (!gotSQLExc) { System.out.println("Database did not shut down normally"); } else { System.out.println("Database shut down normally"); } }
// Beginning of the primary catch block: uses errorPrint method } catch (Throwable e) { /* Catch all exceptions and pass them to ** the exception reporting method */ System.out.println(" . . . exception thrown:"); errorPrint(e); }
static void errorPrint(Throwable e) { if (e instanceof SQLException) SQLExceptionPrint((SQLException)e); else { System.out.println("A non SQL error occured."); e.printStackTrace(); } } // END errorPrint
// Iterates through a stack of SQLExceptions static void SQLExceptionPrint(SQLException sqle) { while (sqle != null) { System.out.println("\n---SQLException Caught---\n"); System.out.println("SQLState: " + (sqle).getSQLState()); System.out.println("Severity: " + (sqle).getErrorCode()); System.out.println("Message: " + (sqle).getMessage()); sqle.printStackTrace(); sqle = sqle.getNextException(); } } // END SQLExceptionPrint
1.
| Create the WwdClient program using the following
steps:
| |||||||||||||||||||||||||
2.
| Set up the client/server environment using the following steps:
| |||||||||||||||||||||||||
3.
| Run the client program using the following steps:
| |||||||||||||||||||||||||
4.
| Shut down the Network Server:
The server shutdown confirmation appears
in both command windows. |
Use this link: WorkingWithDerby Resources page Browser URL: http://wiki.apache.org/db-derby/WorkingWithDerby
• | The Derby Quick
Start page is a good reference page organized by area of interest. | |
• | You will find that many content-rich areas, such as the Derby Wiki
and the Derby Users mailing
list, are available to you. | |
• | If you are interested in how others are using Derby,
see the Uses of Derby page
on the Wiki. This page contains informational links to development projects
and products that use Derby.
When you implement a system using Derby,
please add it to this list. |
where:jdbc:derby:databaseName;URLAttributes
• | databaseName The name of the
database that you want to connect to | |
• | URLAttributes One or more of
the supported attributes of the database connection URL, such as ;locale=ll_CC or ;create=true. For more information, see the Java DB Developer's Guide. |
where the server and port specify the host name (or IP address) and port number where the server is listening for requests and databaseName is the name of the database you want to connect to. The URLAttributes can be either Derby embedded or network client attributes. See the Java DB Server and Administration Guide for more information on accessing the Network Server by using the network client.jdbc:derby://server[:port]/databaseName[;URLAttributes=value[;...]]
Symbol | Meaning |
| | or. Choose one of the items |
[ ] | Enclose optional items. |
* | Flags items that you can repeat 0 or more times. Has a
special meaning in some SQL statements. |
{ } | Groups items so that they can be marked with one of the
other symbols, i.e. [ ], |, or *. |
( ) . , | Other punctuation that is part of the syntax. |
CREATE [ UNIQUE ] INDEX IndexName ON TableName ( SimpleColumnName [ , SimpleColumnName ] * )
Usage | Typeface | Examples |
New terms | Italic | defined by keys |
File and directory names | Italic | C:\derby |
Dictionary objects | Italic | The Employees table |
In syntax, items that you do not type exactly as they appear,
but replace with the appropriate name | Italic | CREATE TABLE tableName |
SQL examples | Bold and/or fixed-width | SELECT city_name FROM Cities |
Java application examples | Bold and/or fixed-width | Connection conn = DriverManager.getConnection ("jdbc:derby:Sample") |
Things you type in a command prompt | Bold and/or fixed-width | java org.apache.derby.tools.ij |
Comments within examples | Bold and/or fixed-width | --This line ignored |
SQL keywords (commands) | All caps | You can use a CREATE TABLE statement |
Library Name | Use |
derby.jar | For embedded databases. You always need this library for embedded
environments. For client/server environments, you only need this library on
the server. |
Library Names | Use |
derbytools.jar | Required for running all the Derby tools
(such as ij, dblook, and import/export). |
derbyrun.jar | Executable jar file that can be used to start the Derby tools. |
Library Name | Use |
derbyclient.jar | Required to start the Derby Network
Server. |
Library Name | Use |
derbyclient.jar | Required to use the Derby network
client driver. |
Library Names | Use | |||||||||||||||||||||||||||||||||||||||
| Required to provide translated messages for the indicated locales. |