
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.
• | After installing Explains
the installation layout. | |
• | Upgrades Explains
how to upgrade a database created with a previous version of
Derby. | |
• | JDBC applications and Derby basics Basic
details for using Derby,
including loading the JDBC driver, specifying a database URL, starting Derby, and working with
Derby properties. | |
• | Deploying Derby applications An
overview of different deployment scenarios, and tips for getting the details
right when deploying applications. | |
• | Derby server-side programming Describes
how to program database-side JDBC routines, triggers, and table functions. | |
• | Controlling Derby application behavior JDBC, cursors, locking and isolation levels, and multiple connections. | |
• | Using Derby as a J2EE resource manager Information
for programmers developing back-end components in a J2EE system. | |
• | Derby and Security Describes
how to use the security features of
Derby. | |
• | Developing tools and using Derby with an IDE Tips
for tool designers. | |
• | SQL tips Insiders'
tricks of the trade for using SQL. | |
• | Localizing Derby An overview
of database localization. | |
• | Derby and standards Describes those parts of
Derby that are non-standard
or not typical for a database system. |
C:>echo %DERBY_HOME% C:\DERBY_HOME
• | index.html in the top-level directory is the top page for the on-line
documentation. | |||||||
• | RELEASE-NOTES.html, in the top-level Derby base
directory, contains important last-minute information. Read it first. | |||||||
• | /bin contains utilities and scripts for running Derby. | |||||||
• | /demo contains some sample applications, useful scripts, and prebuilt
databases.
| |||||||
• | /docs contains the on-line documentation (including this document). | |||||||
• | /javadoc contains the documented APIs for the public classes and
interfaces. Typically, you use the JDBC interface to interact with Derby;
however, you can use some of these additional classes in certain situations. | |||||||
• | /lib contains the Derby libraries. |
• | marks the database as upgraded to the current release (Version 10.5). | |
• | allows use of new features. |
1.
| Back up your database to a safe location using Derby online/offline
backup procedures. For more information on backup, see the Derby Server and Administration Guide. | |
2.
| Update your CLASSPATH with the latest jar files. | |
3.
| Make sure that there are no older versions of the Derby jar
files in your CLASSPATH. You can determine if you have multiple versions of Derby in your CLASSPATH by using the sysinfo tool. To use the sysinfo tool,
execute the following command: The sysinfo tool uses information found in the Derby jar files to determine the version of any Derby jar in your CLASSPATH. Be sure that you have only one version of the Derby jar files specified in your CLASSPATH. |
• | Back up your database before you upgrade. | |
• | Ensure that only the new Derby jar
files are in your CLASSPATH. |
• | A full upgrade is a complete upgrade of the Derby database.
When you perform a full upgrade, you cannot connect to the database with an
older version of Derby and
you cannot revert back to the previous version. | |
• | A soft upgrade allows you to run a newer version of Derby against
an existing database without having to fully upgrade the database. This means
that you can continue to run an older version of Derby against
the database. However, if you perform a soft upgrade, certain features will
not be available to you until you perform a full upgrade. |
1.
| To upgrade the database, select the type of upgrade that you want
to perform:
|
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
java -Djdbc.drivers=org.apache.derby.jdbc.EmbeddedDriver applicationClass
• | you can specify the name of the database you want to connect to | |
• | you can specify a number of attributes and values that allow you to accomplish
tasks. For more information about what you can specify with the Derby connection
URL, see Database connection examples. |
jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*
• | 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. See Accessing databases from the classpath for details. | |
• | jar: Databases are treated as read-only databases. DatabaseNames might
require a leading slash, because you specify them "relative" to the jar file.
See Accessing databases from a jar or zip file for details.
jar requires an additional element immediately before the
database name:
pathToArchive is the path to the jar or zip file that holds
the database. |
Connection conn = DriverManager.getConnection("jdbc:derby:sample");

2009-05-08 17:27:11.199 GMT: Booting Derby version The Apache Software Foundation - Apache Derby - 10.5.1.1 - (764942): instance a816c00e-0121-2140-ffd9-fffff0cfee85 on database directory C:\sampledb
DriverManager.getConnection("jdbc:derby:;shutdown=true");
2009-05-08 17:28:47.140 GMT: Shutting down instance a816c00e-0121-2140-ffd9-fffff0cfee85
Class.forName(org.apache.derby.jdbc.EmbeddedDriver).newInstance();
ERROR XJ040: Failed to start database 'sample', see the next exception for details. ERROR XSDB6: Another instance of Derby might have already booted the databaseC:\databases\sample.
WARNING: Derby (instance 80000000-00d2-3265-de92-000a0a0a0200) is attempting to boot the database /export/home/sky/wombat even though Derby (instance 80000000-00d2-3265-8abf-000a0a0a0200) might still be active. Only one instance of Derby should boot a database at a time. Severe and non-recoverable corruption can result and might have already occurred.
Sat Aug 14 09:42:51 PDT 2005: Booting Derby version Apache Derby - 10.0.0.1 - (29612): instance 80000000-00d2-1c87-7586-000a0a0b1300 on database at directory C:\tutorial_system\sample ------------------------------------------------------------ Sat Aug 14 09:42:59 PDT 2005: Booting Derby version Apache Derby - 10.0.0.1 - (29612): instance 80000000-00d2-1c87-9143-000a0a0b1300 on database at directory C:\tutorial_system\HelloWorldDB
• | All databases exist within a system. | |
• | System-wide properties affect the entire system, and persistent system-wide
properties live in the system directory. | |
• | You can boot all the databases in the system, and the boot-up times of
all databases affect the performance of the system. | |
• | You can preboot databases only if they are within the system. (Databases
do not necessarily have to live inside the system directory, but keeping
your databases there is the recommended practice.) | |
• | Once you connect to a database, it is part of the current system and thus
inherits all system-wide properties. | |
• | Only one instance of Derby can
run in a JVM at a single time, and only one instance of Derby should
boot a database at one time. Keeping databases in the system directory makes
it less likely that you would use more than one instance of Derby. | |
• | The error log is located inside the system directory. |
• | log directory Contains files that make up the database transaction
log, used internally for data recovery (not the same thing as the error log). | |
• | seg0 directory Contains one file for each user table, system
table, and index (known as conglomerates). | |
• | service.properties file A text file with internal configuration
information. | |
• | tmp directory (might not exist.) A temporary directory used
by Derby for large sorts
and deferred updates and deletes. Sorts are used by a variety of SQL statements.
For databases on read-only media, you might need to set a property to change
the location of this directory. See "Creating Derby Databases
for Read-Only Use". | |
• | jar directory (might not exist.) A directory in which jar files
are stored when you use database class loading. |

Type of Object | Limit |
tables in each database | java.lang.Long.MAX_VALUE Some operating systems
impose a limit to the number of files allowed in a single directory. |
indexes in each table | 32,767 or storage |
columns in each table | 1,012 |
number of columns on an index key | 16 |
rows in each table | No limit. |
size of table | No limit. Some operating systems impose a limit on the
size of a single file. |
size of row | No limit. Rows can span pages. Rows cannot span tables
so some operating systems impose a limit on the size of a single file, which
results in limiting the size of a table and size of a row in that table. |
jdbc:derby:myDB
Connection conn =DriverManager.getConnection("jdbc:derby:myDB");
jdbc:derby:../otherDirectory/myDB jdbc:derby:c:/otherDirectory/myDB
• | refer to a previously created Derby database | |
• | specify the create=true attribute |
jdbc:derby:directory:myDB
jdbc:derby:/sample
jdbc:derby:/demo/databases/sample
jdbc:derby:/jarDB1
jdbc:derby:jar:(c:/derby/lib/jar2.jar)jarDB2
• | jdbc:derby:db1 Open a connection to the database db1. db1 is
a directory located in the system directory. | |
• | jdbc:derby:london/sales Open a connection to the database london/sales. london is
a subdirectory of the system directory, and sales is a subdirectory
of the directory london. | |
• | jdbc:derby:/reference/phrases/french Open a connection to
the database /reference/phrases/french. On a UNIX system, this
would be the path of the directory. On a Windows system, the path would be C:\reference\phrases\french if
the current drive were C. If a jar file storing databases were in the
user's classpath, this could also be a path within the jar file. | |
• | jdbc:derby:a:/demo/sample Open a connection to the database
stored in the directory \demo\sample on drive A (usually the
floppy drive) on a Windows system. | |
• | jdbc:derby:c:/databases/salesdb
jdbc:derby:salesdb These two connection URLs connect to the same
database, salesdb, on a Windows platform if the system directory of
the Derby system is C:\databases. | |
• | jdbc:derby:support/bugsdb;create=true Create the database support/bugsdb in
the system directory, automatically creating the intermediate directory support if
it does not exist. | |
• | jdbc:derby:sample;shutdown=true Shut down the sample database.
(Authentication is not enabled, so no user credentials are required.)
| |
• | jdbc:derby:/myDB Access myDB (which is directly in
a directory in the classpath) as a read-only database. | |
• | jdbc:derby:classpath:/myDB Access myDB (which is directly
in a directory in the classpath) as a read-only database. The reason for using
the subsubprotocol is that it might have the same path as a database in the
directory structure. | |
• | jdbc:derby:jar:(C:/dbs.jar)products/boiledfood Access the
read-only database boiledfood in the products directory from
the jar file C:/dbs.jar. | |
• | jdbc:derby:directory:myDB Access myDB, which is in
the system directory. The reason for using the directory: subsubprotocol
is that it might happen to have the same path as a database in the classpath. |
jdbc:derby:;shutdown=true
// shutting down a database from your application DriverManager.getConnection( "jdbc:derby:sample;shutdown=true");
// shutting down an authenticated database as database owner DriverManager.getConnection( "jdbc:derby:securesample;user=joeowner;password=secret;shutdown=true");
jdbc:derby:databaseName;create=true
1.
| Specify the language and country codes for the territory attribute,
and the TERRITORY_BASED value for the collation attribute
when you create the database. For example:
|
1.
| Use the encryptionKey attribute in the connection URL. For example to create the database and encrypt the database encDB using
an external key, specify this URL:
Attention: If you lose the encryption key you
will not be able to boot the database. |
1.
| The attribute that you specify depends on how the database was
originally encrypted:
|
Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); Properties p = new Properties(); p.setProperty("user", "sa"); p.setProperty("password", "manager"); p.setProperty("create", "true"); Connection conn = DriverManager.getConnection( "jdbc:derby:mynewDB", p);
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
• | Whether to authorize users | |
• | Page size of tables and indexes | |
• | Where and whether to create an error log | |
• | Which databases in the system to boot |
• | system-wide Most properties can be set on a system-wide basis; that is, you set a
property for the entire system and all its databases and conglomerates, if this
is applicable. Some properties, such as error handling and automatic booting,
can be configured only in this way, since they apply to the entire system. (For
information about the Derby
system, see Derby system.) | |
• | database-wide Some properties can also be set on a database-wide basis. That is, the
property is true for the selected database only and not for the other databases
in the system unless it is set individually within each of them. |
• | That value is changed and the system is rebooted | |
• | The file is removed from the system and the system is rebooted | |
• | The database is booted outside of that system |
1.
| System-wide properties set programmatically (as a command-line option
to the JVM when starting the application or within application code) | |
2.
| Database-wide properties | |
3.
| System-wide properties set in the derby.properties file |
• | ||
• |
java -Dderby.system.home=C:\home\Derby\ -Dderby.storage.pageSize=8192 JDBCTest
Properties p = System.getProperties(); p.setProperty("derby.system.home", "C:\databases\sample");
• | Provide this file | |
• | Automatically create this file for you | |
• | Automatically write any properties or values to this file |
derby.infolog.append=true derby.storage.pageSize=8192 derby.storage.pageReservedSpace=60
Properties sprops = System.getProperties(); System.out.println("derby.storage.pageSize value: " + sprops.getProperty("derby.storage.pageSize"));
Type of property | How you set it | ||||||
System-wide |
| ||||||
Database-wide | Using system procedures and functions in an SQL statement |
• | As database-wide properties | |
• | As system-wide properties via a Properties object
in the application in which the Derby engine is embedded |
derby.storage.pageSize=8192
java -Dderby.system.home=c:\system_directory MyApp
CREATE TABLE table1 (a INT, b VARCHAR(10))
java -Dderby.system.home=c:\system_directory -Dderby.storage.pageSize=4096 MyApp CREATE TABLE anothertable (a INT, b VARCHAR(10))
CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)"); cs.setString(1, "derby.storage.pageSize"); cs.setString(2, "32768"); cs.execute(); cs.close();
CREATE TABLE table2 (a INT, b VARCHAR(10))
java -Dderby.system.home=c:\system_directory MyApp
CREATE TABLE table4 (a INT, b VARCHAR(10))


• | The Derby library (derby.jar). | |
• | The libraries for the application. You have the option of storing these
libraries in the database. | |
• | The database or databases used by the application, in the context of their
system directory. |

• | If you are setting any system-wide properties, see if they can be set
as database-wide properties instead. | |
• | Are any properties being set in the derby.properties file? Some
properties can only be set on a system-wide basis. If so, deploy the entire
system directory along with the properties file. Deploy only those databases
that you wish to include. Setting properties programmatically can simplify
this step- you will not have to worry about deploying the system directory/properties
file. |
1.
| Create and populate the database on read-write media. | |
2.
| Commit all transactions and shut down Derby in
the prescribed manner. If you do not shut down Derby in
the prescribed manner, Derby will
need to perform recovery the next time the system boots. Derby cannot
perform recovery on read-only media. | |
3.
| Delete the tmp directory if one was created within your
database directory. If you include this directory, Derby will
attempt to delete it and will return errors when attempting to boot a database
on read-only media. | |
4.
| For the read-only database, set the property derby.storage.tempDirectory to
a writable location. Derby needs
to write to temporary files for large sorts required by such SQL statements
as ORDER BY, UNION, DISTINCT, and GROUP BY. For more information about this
property, see the Derby Reference Manual.
| |
5.
| Configure the database to send error messages to a writable file
or to an output stream. For information on the
derby.stream.error.file property, see the Derby Reference Manual.
|
1.
| Move the database directory to the read-only media, including the
necessary subdirectory directories (log and seg0) and the file service.properties. | |
2.
| Use the database as usual, except that you will not be able to
insert or update any data in the database or create or drop dictionary objects. |
1.
| Create a database for use on read-only media. | |
2.
| From the directory that contains the database folder, archive the database
directory and its contents. For example, for the database sales that
lives in the system directory C:\london, issue the command from london.
Do not issue the command from inside the database directory itself. |
cd C:\london jar cMf C:\dbs.jar sales
cd C:\london jar cMf C:\dbs.jar sales products\boiledfood
jdbc:derby:jar:(pathToArchive)databasePathWithinArchive
jdbc:derby:jar:(C:/dbs.jar)products/boiledfood jdbc:derby:jar:(C:/dbs.jar)sales
jdbc:derby:jar:(C:/dbs.jar)/products/boiledfood
1.
| Set the classpath to include the jar or zip file before starting
up Derby:
| |
2.
| Connect to a database within the jar or zip file with one of the
following connection URLs:
|
jdbc:derby:classpath:/products/boiledfood
jdbc:derby:directory:databasePathInFileSystem
jdbc:derby:directory:/products/boiledfood
• | The standard Java packages (java.*, javax.*)
Derby does not prevent
you from storing such a jar file in the database, but these classes are
never loaded from the jar file. | |
• | The classes that are supplied with your Java environment (for example, sun.*) |
jar cf travelagent.jar travelagent/*.class.
• | Extract the required third-party classes from their jar file and include
only those classes in your jar file. Use this option when you need
only a small subset of the classes in the third-party jar file. | |
• | Store the third-party jar file in the database. Use this option
when you need most or all of the classes in the third-party jar file, since
your application and third-party logic can be upgraded separately. | |
• | Deploy the third-party jar file in the user's class path. Use
this option when the classes are already installed on a user's machine (for
example, Objectspace's JGL classes). |
• | Separate jar files with a colon (:). | |
• | Use two-part names for the jar files (schema name and jar name). Set the
property as a database-level property for the database. The first time you
set the property, you must reboot to load the classes. |
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.database.classpath', 'APP.ToursLogic:APP.ACCOUNTINGLOGIC')
• | You originally configured database-level class loading for the
database correctly. Turning on the database-level class loading property requires
setting the derby.database.classpath property with
valid two-part names, then rebooting. | |
• | If changes to the derby.database.classpath property are needed
to reflect new jar files, you change the property to a valid value. |
• | to avoid blocking and deadlocks | |
• | to ensure that any updates done from within the routine are atomic with
the outer transaction |
Connection conn = DriverManager.getConnection( "jdbc:default:connection");
• | Can issue a commit or rollback only within a procedure (not a function).
| |
• | Cannot change the auto-commit connection attribute. | |
• | Cannot modify the data in a table used by the parent statement that called
the routine, using INSERT, UPDATE, or DELETE. For example, if a SELECT statement
using the T table calls the changeTables procedure, changeTables cannot
modify data in the T table. | |
• | Cannot drop a table used by the statement that called the routine. | |
• | Cannot be in a class whose static initializer executes DDL statements. |
CALL MYPROC()
try { preparedStatement.execute(); } catch (SQLException se ) { String SQLState = se.getSQLState(); if ( SQLState.equals( "23505" ) ) { correctDuplicateKey(); } else if ( SQLState.equals( "22003" ) ) { correctArithmeticOverflow(); } else { throw se; } }
CREATE TRIGGER . . . DELETE FROM flightavailability WHERE flight_id IN (SELECT flight_id FROM flightavailability WHERE YEAR(flight_date) < 2005);)
• | the "before" values of the rows being changed (their values before the
database event that caused the trigger to fire) | |
• | the "after" values of the rows being changed (the values to which the
database event is setting them) |
CREATE TRIGGER trig1 AFTER UPDATE ON flights REFERENCING OLD AS UPDATEDROW FOR EACH ROW MODE DB2SQL INSERT INTO flights_history VALUES (UPDATEDROW.FLIGHT_ID, UPDATEDROW.SEGMENT_NUMBER, UPDATEDROW.ORIG_AIRPORT, UPDATEDROW.DEPART_TIME, UPDATED ROW.DEST_AIRPORT, UPDATEDROW.ARRIVE_TIME, UPDATEDROW.MEAL, UPDATEDROW.FLYING_TIME, UPDATEDROW.MILES, UPDATEDROW.AIRCRAFT,'INSERTED FROM trig1');
• | XML-formatted reports and logs | |
• | Queries that run in foreign databases | |
• | Streaming data from sensors | |
• | RSS feeds |
• | next() | |
• | close() | |
• | wasNull() | |
• | getXXX() - When invoking a Derby-style table function at runtime, Derby calls a getXXX()
method on each referenced column. The particular getXXX()
method is based on the column's data type
as declared in the CREATE FUNCTION statement.
Preferred getXXX() methods for Derby-style table functions
explains how Derby selects an appropriate getXXX() method.
However, nothing prevents application code from calling other getXXX()
methods on the ResultSet. The returned ResultSet
needs to implement the getXXX() methods which Derby will call as well
as all getXXX() methods which the application will call.
|
public static ResultSet read() {...}
CREATE FUNCTION externalEmployees () RETURNS TABLE ( employeeId INT, lastName VARCHAR( 50 ), firstName VARCHAR( 50 ), birthday DATE ) LANGUAGE JAVA PARAMETER STYLE DERBY_JDBC_RESULT_SET READS SQL DATA EXTERNAL NAME 'com.acme.hrSchema.EmployeeTable.read'
INSERT INTO employees SELECT s.* FROM TABLE (externalEmployees() ) s;
Column Type Declared by CREATE FUNCTION | getXXX() Method Called by Derby for JDBC 3.0 and 4.0 | getXXX() Method Called by Derby for JSR 169 |
BIGINT | getLong() | Same |
BLOB | getBlob() | Same |
CHAR | getString() | Same |
CHAR FOR BIT DATA | getBytes() | Same |
CLOB | getClob() | Same |
DATE | getDate() | Same |
DECIMAL | getBigDecimal() | getString() |
DOUBLE | getDouble() | Same |
DOUBLE PRECISION | getDouble() | Same |
FLOAT | getDouble() | Same |
INTEGER | getInt() | Same |
LONG VARCHAR | getString() | Same |
LONG VARCHAR FOR BIT DATA | getBytes() | Same |
NUMERIC | getBigDecimal() | getString() |
REAL | getFloat() | Same |
SMALLINT | getShort() | Same |
TIME | getTime() | Same |
TIMESTAMP | getTimestamp() | Same |
VARCHAR | getString() | Same |
VARCHAR FOR BIT DATA | getBytes() | Same |
XML | Not supported | Not supported |
package com.acme.hrSchema; import java.sql.*; /** * Sample Table Function for reading the employee table in an * external database. */ public class EmployeeTable { public static ResultSet read() throws SQLException { Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement( "select * from hrSchema.EmployeeTable" ); return ps.executeQuery(); } protected static Connection getConnection() throws SQLException { String EXTERNAL_DRIVER = "com.mysql.jdbc.Driver"; try { Class.forName( EXTERNAL_DRIVER ); } catch (ClassNotFoundException e) { throw new SQLException( "Could not find class " + EXTERNAL_DRIVER ); } Connection conn = DriverManager.getConnection ( "jdbc:mysql://localhost/hr?user=root&password=mysql-passwd" ); return conn; } }
• | Expensive - It is expensive to create and loop through the
rows of the table function. This makes it likely that the optimizer will
place the table function in an outer slot of the join
order so that it will not be looped through often. | |
• | Repeatable - The table function can be instantiated
multiple times with the same results. This is probably true
for most table functions. However, some
table functions may open read-once streams. If the optimizer knows that a
table function is repeatable, then the optimizer can place
the table function in an inner slot where the function can be
invoked multiple times. If a table function is not
repeatable, then the optimizer must either place it in the
outermost slot or invoke the function once and store its contents in
a temporary table. |
• | No-arg constructor - The table function's class
must have a public constructor whose signature has no arguments. | ||||||||||||||||
• | VTICosting - The class must also implement
org.apache.derby.vti.VTICosting. This involves
implementing the following methods as described in
Measuring the cost of Derby-style table functions
and
Example VTICosting implementation:
|
• | C = The estimated Cost for creating and running the
table function. That is, the value returned by
VTICosting.getEstimatedCostPerInstantiation().
In general, Cost is a measure of time in milliseconds. | |
• | I = The optimizer's Imprecision. A measure of how skewed the optimizer's estimates tend
to be in your particular environment. See below for instructions on how to estimate this Imprecision. | |
• | A = The Actual time in milliseconds which it takes
to create and run this table function. |
• | O = The Optimizer's estimated cost for a plan. | |
• | T = The Total runtime in milliseconds for the plan. |
• | Select = Select all of the rows from a big table. | |
• | Record = In the statistics output, look for the ResultSet
which represents the table scan. That scan has a field
labelled "optimizer estimated cost". That's O. Now
look for the fields in that ResultSet's statistics labelled
"constructor time", "open time", "next time", and "close time". Add up
all of those fields. That total is T.
|
MAXIMUMDISPLAYWIDTH 7000; CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1); select * from T; values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
• | P = The runtime spent Per row (in milliseconds). | |
• | N = The Number of rows in the table function. | |
• | E = The time spent creating an Empty instance of the
table function which has no rows in it. Usually, P * N dwarfs
E. That is, the table function instantiation cost is very
small compared to the actual cost of looping through the
rows. However, for some table functions, E may be significant
and may dominate the table function's cost when N is small. |
• | Short-circuit = Short-circuit the next() method of the
ResultSet
returned by your Derby-style Table Function so that it returns
false the first time it is called. This makes it
appear that the
ResultSet
has no rows.
| |
• | Select = Select all of the rows from the table function. | |
• | Record = In the statistics output, look for the VTIResultSet
which represents the table function scan. Add up
the values of the fields in that VTIResultSet's statistics labelled
"constructor time", "open time", "next time", and "close time".
That total is E.
|
• | Select = Select all of the rows from the table function. | |
• | Record = In the statistics output, look for the VTIResultSet
which represents the table function scan. Add up
the values of the fields in that VTIResultSet's statistics labelled
"constructor time", "open time", "next time", and "close
time". Subtract E from the result. Now divide by the
value of the field "Rows seen".
The result is P.
|
package com.acme.hrSchema; import java.io.Serializable; import java.sql.*; import org.apache.derby.vti.VTICosting; import org.apache.derby.vti.VTIEnvironment; /** * Tuned table function. */ public class TunedEmployeeTable extends EmployeeTable implements VTICosting { public TunedEmployeeTable() {} public double getEstimatedRowCount( VTIEnvironment optimizerState ) throws SQLException { return getRowCount( optimizerState ); } public double getEstimatedCostPerInstantiation( VTIEnvironment optimizerState ) throws SQLException { double I = 100.0; // optimizer imprecision double P = 10.0; // cost per row in milliseconds double E = 0.0; // cost of instantiating the external ResultSet double N = getRowCount( optimizerState ); return I * ( ( P * N ) + E ); } public boolean supportsMultipleInstantiations( VTIEnvironment optimizerState ) throws SQLException { return true; } ////////////////////////////////////////////////////////////////////////////// private double getRowCount( VTIEnvironment optimizerState ) throws SQLException { String ROW_COUNT_KEY = "rowCountKey"; Double estimatedRowCount = (Double) getSharedState( optimizerState, ROW_COUNT_KEY ); if ( estimatedRowCount == null ) { Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement( "select count(*) from hrSchema.EmployeeTable" ); ResultSet rs = ps.executeQuery(); rs.next(); estimatedRowCount = new Double( rs.getDouble( 1 ) ); setSharedState( optimizerState, ROW_COUNT_KEY, estimatedRowCount ); rs.close(); ps.close(); conn.close(); } return estimatedRowCount.doubleValue(); } private Serializable getSharedState( VTIEnvironment optimizerState, String key ) { return (Serializable) optimizerState.getSharedState( key ); } private void setSharedState( VTIEnvironment optimizerState, String key, Serializable value ) { optimizerState.setSharedState( key, value ); } }
Connection conn = DriverManager.getConnection( "jdbc:derby:sample"); System.out.println("Connected to database sample"); conn.setAutoCommit(false); Connection conn2 = DriverManager.getConnection( "jdbc:derby:newDB;create=true"); System.out.println("Created AND connected to newDB"); conn2.setAutoCommit(false); Connection conn3 = DriverManager.getConnection( "jdbc:derby:newDB"); System.out.println("Got second connection to newDB"); conn3.setAutoCommit(false);
• | Cursors You cannot use auto-commit if you do any positioned
updates or deletes (that is, an update or delete statement with a WHERE CURRENT
OF clause) on cursors which have the
ResultSet.CLOSE_CURSORS_AT_COMMIT holdability value
set. Auto-commit automatically closes cursors that are
explicitly opened with the
ResultSet.CLOSE_CURSORS_AT_COMMIT value, when you do any
in-place updates or deletes. An updatable cursor declared to be held
across commit (this is the default value) can execute updates and issue multiple
commits before closing the cursor. After an explicit or implicit commit, a
holdable forward-only cursor must be repositioned with a call to the
next method before it can accessed again. In this state,
the only other valid operation besides calling next is
calling close. | ||||||||||||||||||||||
• | Database-side JDBC routines (routines using nested connections) You cannot execute functions within SQL statements if those functions
perform a commit or rollback on the current connection. Since in auto-commit
mode all SQL statements are implicitly committed, Derby turns
off auto-commit during execution of database-side routines and turns it
back on when the statement completes. Routines that use nested connections
are not permitted to turn auto-commit on or off. | ||||||||||||||||||||||
• | Table-level locking and the SERIALIZABLE isolation level When
an application uses table-level locking and the SERIALIZABLE isolation level,
all statements that access tables hold at least shared table locks. Shared
locks prevent other transactions that update data from accessing the table.
A transaction holds a lock on a table until the transaction commits. So
even a SELECT statement holds a shared lock on a table until its connection
commits and a new transaction begins. Table 4. Summary
of Application Behavior with Auto-Commit On or Off
|
conn.setAutoCommit(false); // Autocommit must be off to use savepoints. Statement stmt = conn.createStatement(); int rows = stmt.executeUpdate("INSERT INTO TABLE1 (COL1) VALUES(1)"); // set savepoint Savepoint svpt1 = conn.setSavepoint("S1"); rows = stmt.executeUpdate("INSERT INTO TABLE1 (COL1) VALUES (2)"); ... conn.rollback(svpt1); ... conn.commit();
Connection conn = DriverManager.getConnection( "jdbc:derby:sample"); Statement s = conn.createStatement(); s.execute("set schema 'SAMP'"); //note that autocommit is on--it is on by default in JDBC ResultSet rs = s.executeQuery( "SELECT empno, firstnme, lastname, salary, bonus, comm " + "FROM samp.employee"); /** a standard JDBC ResultSet. It maintains a * cursor that points to the current row of data. The cursor * moves down one row each time the method next() is called. * You can scroll one way only--forward--with the next() * method. When auto-commit is on, after you reach the * last row the statement is considered completed * and the transaction is committed. */ System.out.println( "last name" + "," + "first name" + ": earnings"); /* here we are scrolling through the result set with the next() method.*/ while (rs.next()) { // processing the rows String firstnme = rs.getString("FIRSTNME"); String lastName = rs.getString("LASTNAME"); BigDecimal salary = rs.getBigDecimal("SALARY"); BigDecimal bonus = rs.getBigDecimal("BONUS"); BigDecimal comm = rs.getBigDecimal("COMM"); System.out.println( lastName + ", " + firstnme + ": " + (salary.add(bonus.add(comm)))); } rs.close(); // once we've iterated through the last row, // the transaction commits automatically and releases //shared locks s.close();
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery( "SELECT FIRSTNAME, LASTNAME, WORKDEPT, BONUS " + "FROM EMPLOYEE"); while (uprs.next()) { int newBonus = uprs.getInt("BONUS") + 100; uprs.updateInt("BONUS", newBonus); uprs.updateRow(); }
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery( "SELECT FIRSTNAME, LASTNAME, WORKDEPT, BONUS " + "FROM EMPLOYEE"); while (uprs.next()) { if (uprs.getInt("WORKDEPT")==300) { uprs.deleteRow(); } }
• | After an update or delete is made on a forward only result set,
the result set's cursor is no longer on the row just updated or
deleted, but immediately before the next row in the result set (it is
necessary to move to the next row before any further row operations
are allowed). This means that changes made by
ResultSet.updateRow() and
ResultSet.deleteRow() are never visible.
| |
• | If a row has been inserted, i.e using
ResultSet.insertRow() it may be visible in a forward
only result set. |
• | If the current row is deleted by a statement in the same transaction, calls to
ResultSet.updateRow() will cause an exception, since
the cursor is no longer positioned on a valid row. |
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery( "SELECT FIRSTNAME, LASTNAME, WORKDEPT, BONUS " + "FROM EMPLOYEE"); uprs.absolute(5); // update the fifth row int newBonus = uprs.getInt("BONUS") + 100; uprs.updateInt("BONUS", newBonus); uprs.updateRow();
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery( "SELECT FIRSTNAME, LASTNAME, WORKDEPT, BONUS " + "FROM EMPLOYEE"); uprs.last(); uprs.relative(-5); // moves to the 5th from the last row uprs.deleteRow();
• | Changes caused by other statements, triggers and other
transactions (others) are considered as other changes, and are not visible in
scrollable insensitive result sets. | |
• | Own updates and deletes are visible in Derby's scrollable
insensitive result sets. Note: Derby handles changes
made using positioned updates and deletes as own changes, so when made
via a result set's cursor such changes are also visible in that result
set.
| |
• | Rows inserted to the table may become visible in the result set. | |
• | ResultSet.rowDeleted() returns true if the row
has been deleted using the cursor or result set. It does not detect
deletes made by other statements or transactions. Note that the
method will also work for result sets with concurrency
CONCUR_READ_ONLY if the underlying result set is FOR UPDATE and a
cursor was used to delete the row.
| |
• | ResultSet.rowUpdated() returns true if the row
has been updated using the cursor or result set. It does not detect
updates made by other statements or transactions. Note that the
method will also work for result sets with concurrency
CONCUR_READ_ONLY if the underlying result set is FOR UPDATE and a
cursor was used to update the row.
| |
• | Note: Both ResultSet.rowUpdated() and
ResultSet.rowDeleted() return true if the row
first is updated and later deleted. |
• | The row has been deleted after it was read into the result set:
Scrollable insensitive result sets will give a warning with SQLState 01001 . | |
• | The table has been compressed: Scrollable insensitive
result sets will give a warning with SQLState
01001. A compress conflict may happen if the cursor is held
over a commit. This is because the table intent lock is released on
commit, and not reclaimed until the cursor moves to another row.
|
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery( "SELECT firstname, lastname, workdept, bonus " + "FROM employee"); uprs.moveToInsertRow(); uprs.updateString("FIRSTNAME", "Andreas"); uprs.updateString("LASTNAME", "Korneliussen"); uprs.updateInt("WORKDEPT", 123); uprs.insertRow(); uprs.moveToCurrentRow();
Statement s3 = conn.createStatement(); // name the statement so we can reference the result set // it generates s3.setCursorName("UPDATABLESTATEMENT"); // we will be able to use the following statement later // to access the current row of the cursor // a result set needs to be obtained prior to using the // WHERE CURRENT syntax ResultSet rs = s3.executeQuery("select * from FlightBookings FOR UPDATE of number_seats"); PreparedStatement ps2 = conn.prepareStatement( "UPDATE FlightBookings SET number_seats = ? " + "WHERE CURRENT OF UPDATABLESTATEMENT");
PreparedStatement ps2 = conn.prepareStatement( "UPDATE employee SET bonus = ? WHERE CURRENT OF "+ Updatable.getCursorName());
Connection conn = DriverManager.getConnection("jdbc:derby:sample"); conn.setAutoCommit(false); // Create the statement with concurrency mode CONCUR_UPDATABLE // to allow result sets to be updatable Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT); // Updatable statements have some requirements // for example, select must be on a single table ResultSet uprs = stmt.executeQuery( "SELECT FIRSTNME, LASTNAME, WORKDEPT, BONUS " + "FROM EMPLOYEE FOR UPDATE of BONUS"); // Only bonus can be updated String theDept="E21"; while (uprs.next()) { String firstnme = uprs.getString("FIRSTNME"); String lastName = uprs.getString("LASTNAME"); String workDept = uprs.getString("WORKDEPT"); BigDecimal bonus = uprs.getBigDecimal("BONUS"); if (workDept.equals(theDept)) { // if the current row meets our criteria, // update the updatable column in the row uprs.updateBigDecimal("BONUS", bonus.add(BigDecimal.valueOf(250L))); uprs.updateRow(); System.out.println("Updating bonus for employee:" + firstnme + lastName); } } conn.commit(); // commit the transaction // close object uprs.close(); stmt.close(); // Close connection if the application does not need it any more conn.close();
//autocommit does not have to be off because even if //we accidentally scroll past the last row, the implicit commit //on the the statement will not close the result set because result sets //are held over commit by default conn.setAutoCommit(false); Statement s4 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); s4.execute("set schema 'SAMP'"); ResultSet scroller=s4.executeQuery( "SELECT sales_person, region, sales FROM sales " + "WHERE sales > 8 ORDER BY sales DESC"); if (scroller.first()) { // One row is now materialized System.out.println("The sales rep who sold the highest number" + " of sales is " + scroller.getString("SALES_PERSON")); } else { System.out.println("There are no rows."); } scroller.beforeFirst(); scroller.afterLast(); // By calling afterlast(), all rows will be materialized scroller.absolute(3); if (!scroller.isAfterLast()) { System.out.println("The employee with the third highest number " + "of sales is " + scroller.getString("SALES_PERSON") + ", with " + scroller.getInt("SALES") + " sales"); } if (scroller.isLast()) { System.out.println("There are only three rows."); } if (scroller.last()) { System.out.println("The least highest number " + "of sales of the top three sales is: " + scroller.getInt("SALES")); } scroller.close(); s4.close(); conn.commit() conn.close(); System.out.println("Closed connection");
• | CLOSE_CURSORS_AT_COMMIT Result sets are closed when an
implicit or explicit commit is performed. | |
• | HOLD_CURSORS_OVER_COMMIT Result sets are held open when
a commit is performed, implicitly or explicitly. This is the default behavior. |
• | Open result sets remain open. Non-scrollable result sets becomes
positioned before the next logical row of the result set. Scrollable
insensitive result sets keep their current position. | |||||||
• | When the session is terminated, the result set is closed and destroyed. | |||||||
• | All locks are released, including locks protecting the current
cursor position. | |||||||
• | For non-scrollable result sets, immediately following a commit, the
only valid operations that can be performed on the ResultSet
object are:
|
Isolation levels for JDBC | Isolation levels for SQL |
Connection.TRANSACTION_READ_UNCOMMITTED (ANSI level
0) | UR, DIRTY READ, READ UNCOMMITTED |
Connection.TRANSACTION_READ_COMMITTED (ANSI level
1) | CS, CURSOR STABILITY, READ COMMITTED |
Connection.TRANSACTION_REPEATABLE_READ (ANSI level
2) | RS |
Connection.TRANSACTION_SERIALIZABLE (ANSI level
3) | RR, REPEATABLE READ, SERIALIZABLE |
Anomaly | Example |
Dirty Reads A dirty read happens when a transaction
reads data that is being modified by another transaction that has not yet
committed. | Transaction A begins. Transaction B begins. (Transaction B sees data updated by transaction A. Those updates have not yet been committed.) |
Non-Repeatable Reads Non-repeatable reads happen when
a query returns data that would be different if the query were repeated within
the same transaction. Non-repeatable reads can occur when other transactions
are modifying data that a transaction is reading. | Transaction A begins. Transaction B begins. (Transaction B updates rows viewed by transaction A before transaction A commits.) If Transaction A issues the same SELECT statement, the results will be different. |
Phantom Reads Records that appear in a set being read
by another transaction. Phantom reads can occur when other transactions insert
rows that would satisfy the WHERE clause of another transaction's statement. | Transaction A begins. Transaction B begins. Transaction B inserts a row that would satisfy the query in Transaction A if it were issued again. |
Isolation Level | Table-Level Locking | Row-Level Locking |
TRANSACTION_READ_UNCOMMITTED | Dirty reads, nonrepeatable reads, and phantom reads possible | Dirty reads, nonrepeatable reads, and phantom reads possible |
TRANSACTION_READ_COMMITTED | Nonrepeatable reads and phantom reads possible | Nonrepeatable reads and phantom reads possible |
TRANSACTION_REPEATABLE_READ | Phantom reads not possible because entire table is locked | Phantom reads possible |
TRANSACTION_SERIALIZABLE | None | None |
• | TRANSACTION_SERIALIZABLE RR, SERIALIZABLE,
or REPEATABLE READ from SQL. TRANSACTION_SERIALIZABLE means
that Derby treats the transactions
as if they occurred serially (one after the other) instead of concurrently. Derby issues locks to prevent
all the transaction anomalies listed in Transaction Anomalies from
occurring. The type of lock it issues is sometimes called a range lock. | |||||||
• | TRANSACTION_REPEATABLE_READ RS from
SQL. TRANSACTION_REPEATABLE_READ means that Derby issues
locks to prevent only dirty reads and non-repeatable reads, but not phantoms.
It does not issue range locks for selects. | |||||||
• | TRANSACTION_READ_COMMITTED CS or CURSOR
STABILITY from SQL. TRANSACTION_READ_COMMITTED means
that Derby issues locks
to prevent only dirty reads, not all the transaction anomalies listed in Transaction Anomalies. TRANSACTION_READ_COMMITTED is
the default isolation level for transactions. | |||||||
• | TRANSACTION_READ_UNCOMMITTED UR, DIRTY
READ, or READ UNCOMMITTED from SQL. For a
SELECT INTO, FETCH with a read-only cursor, full select used in an INSERT,
full select/subquery in an UPDATE/DELETE, or scalar full select (wherever
used), READ UNCOMMITTED allows:
For other operations, the rules that apply to READ COMMITTED also
apply to READ UNCOMMITTED. |
' | Shared | Update | Exclusive |
Shared | + | + | - |
Update | + | - | - |
Exclusive | - | - | - |
• | For TRANSACTION_REPEATABLE_READ isolation, the locks are released at the
end of the transaction. | |
• | For TRANSACTION_READ_COMMITTED isolation, Derby locks
rows only as the application steps through the rows in the result. The current
row is locked. The row lock is released when the application goes to the next
row. | |
• | For TRANSACTION_SERIALIZABLE isolation, however, Derby locks
the whole set before the application begins stepping through. | |
• | For TRANSACTION_READ_UNCOMMITTED, no row locks are requested. |
• | For any isolation level, Derby locks all
the rows in the result plus an entire range of rows for updates or deletes. | |
• | For the TRANSACTION_SERIALIZABLE isolation level, Derby locks
all the rows in the result plus an entire range of rows in the table for SELECTs
to prevent nonrepeatable reads and phantoms. |
Transaction Isolation Level | Table-Level Locking | Row-Level Locking |
Connection.TRANSACTION_READ_UNCOMMITED (SQL: UR) | For SELECT statements, table-level locking is never requested
using this isolation level. For other statements, same as for TRANSACTION_READ_COMMITTED. | SELECT statements get no locks. For other statements, same
as for TRANSACTION_ READ_COMMITTED. |
Connection.TRANSACTION_READ_COMMITTED (SQL: CS) | SELECT statements get a shared lock on the entire table.
The locks are released when the user closes the ResultSet. Other statements
get exclusive locks on the entire table, which are released when the transaction
commits. | SELECTs lock and release single rows as the user steps
through the ResultSet. UPDATEs and DELETEs get exclusive locks on a
range of rows. INSERT statements get exclusive locks on single rows (and sometimes
on the preceding rows). |
Connection.TRANSACTION_REPEATABLE_READ (SQL: RS) | Same as for TRANSACTION_SERIALIZABLE | SELECT statements get shared locks on the rows that satisfy
the WHERE clause (but do not prevent inserts into this range). UPDATEs and
DELETEs get exclusive locks on a range of rows. INSERT statements get exclusive
locks on single rows (and sometimes on the preceding rows). |
Connection.TRANSACTION_SERIALIZABLE (SQL: RR) | SELECT statements get a shared lock on the entire table.
Other statements get exclusive locks on the entire table, which are released
when the transaction commits. | SELECT statements get shared locks on a range of rows.
UPDATE and DELETE statements get exclusive locks on a range of rows. INSERT
statements get exclusive locks on single rows (and sometimes on the preceding
rows). |

ERROR 40001: A lock could not be obtained due to a deadlock, cycle of locks & waiters is: Lock : ROW, DEPARTMENT, (1,14) Waiting XID : {752, X} , APP, update department set location='Boise' where deptno='E21' Granted XID : {758, X} Lock : ROW, EMPLOYEE, (2,8) Waiting XID : {758, U} , APP, update employee set bonus=150 where salary=23840 Granted XID : {752, X} The selected victim is XID : 752



/// if this code might encounter a deadlock, // put the whole thing in a try/catch block // then try again if the deadlock victim exception // was thrown try { s6.executeUpdate( "UPDATE employee " + "SET bonus = 625 " "WHERE empno='000150'"); s6.executeUpdate("UPDATE project " + "SET respemp = '000150' " + "WHERE projno='IF1000'"); } // note: do not catch such exceptions in database-side methods; // catch such exceptions only at the outermost level of // application code. // See Database-side JDBC routines and SQLExceptions. catch (SQLException se) { if (se.getSQLState().equals("40001")) { // it was chosen as a victim of a deadlock. // try again at least once at this point. System.out.println( "Will try the transaction again."); s6.executeUpdate("UPDATE employee " + "SET bonus = 625 " + "WHERE empno='000150'"); s6.executeUpdate("UPDATE project " + "SET respemp = 000150 " + "WHERE projno='IF1000'"); } else throw se; }
• | Multiple applications access a single database (possible only when Derby is running inside a server
framework). | |||||||||||||
• | A single application has more than one Connection to the same database.
The way you deploy Derby affects
the ways applications can use multi-threading and connections, as shown in Threading and Connection Modes. Table 10. Threading and Connection Modes
|
• | Use the TRANSACTION_READ_COMMITTED isolation level and turn on
row-level locking (the defaults). | |
• | Beware of deadlocks caused by using more than one Connection in
a single thread (the most obvious case). For example, if the thread tries
to update the same table from two different Connections,
a deadlock can occur. | |
• | Assign Connections to threads that handle discrete tasks. For example,
do not have two threads update the Hotels table. Have one thread update
the Hotels table and a different one update the Groups table. | |
• | If threads access the same tables, commit transactions often. | |
• | Multi-threaded Java applications have the ability to self-deadlock without
even accessing a database, so beware of that too. | |
• | Use nested connections to share the same lock space. |
• | Committing or rolling back a transaction closes all open ResultSet objects
and currently executing Statements, unless you are using held cursors. If
one thread commits, it closes the Statements and ResultSets of
all other threads using the same connection. | |
• | Executing a Statement automatically closes any existing open ResultSet generated
by an earlier execution of that Statement. If threads share Statements,
one thread could close another's ResultSet. |
• | Avoid sharing Statements (and their ResultSets)
among threads. | |
• | Each time a thread executes a Statement, it should process the
results before relinquishing the Connection. | |
• | Each time a thread accesses the Connection, it should consistently
commit or not, depending on application protocol. | |
• | Have one thread be the "managing" database Connection thread that
should handle the higher-level tasks, such as establishing the Connection,
committing, rolling back, changing Connection properties
such as auto-commit, closing the Connection, shutting
down the database (in an embedded environment), and so on. | |
• | Close ResultSets and Statements that are
no longer needed in order to release resources. |
• | Use row-level locking. | |
• | Use the TRANSACTION_READ_COMMITTED isolation level. | |
• | Avoid queries that cannot use indexes; they require locking of all the
rows in the table (if only very briefly) and might block an update. |
PreparedStatement ps = conn.prepareStatement( "UPDATE account SET balance = balance + ? WHERE id = ?"); /* now assume two threads T1,T2 are given this java.sql.PreparedStatement object and that the following events happen in the order shown (pseudojava code)*/ T1 - ps.setBigDecimal(1, 100.00); T1 - ps.setLong(2, 1234); T2 - ps.setBigDecimal(1, -500.00); // *** At this point the prepared statement has the parameters // -500.00 and 1234 // T1 thinks it is adding 100.00 to account 1234 but actually // it is subtracting 500.00 T1 - ps.executeUpdate(); T2 - ps.setLong(2, 5678); // T2 executes the correct update T2 - ps.executeUpdate(); /* Also, the auto-commit mode of the connection can lead to some strange behavior.*/
catch (Throwable e) { 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: " + e.toString()); } 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(); } }
• | org.apache.derby.jdbc.EmbeddedDataSource
and org.apache.derby.jdbc.EmbeddedDataSource40 Implements the javax.sql.DataSource interface,
which a JNDI server can reference. Typically this is the object that you work
with as a DataSource. | |
• | org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource
and org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource40 Implements the javax.sql.ConnectionPoolDataSource interface.
A factory for PooledConnection objects. | |
• | org.apache.derby.jdbc.EmbeddedXADataSource
and org.apache.derby.jdbc.EmbeddedXADataSource40 Derby's
implementation of the javax.sql.XADataSource interface. |
// If your application is running on the Java SE 6 platform, // and if you would like to call DataSource methods specific // to the JDBC 4 API (for example, isWrapperFor), use the // JDBC 4 variants of these classes: // // org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource40 // org.apache.derby.jdbc.EmbeddedDataSource40 // org.apache.derby.jdbc.EmbeddedXADataSource40 // import org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource; import org.apache.derby.jdbc.EmbeddedDataSource; import org.apache.derby.jdbc.EmbeddedXADataSource; javax.sql.ConnectionPoolDataSource cpds = new EmbeddedConnectionPoolDataSource(); javax.sql.DataSource ds = new EmbeddedDataSource(); javax.sql.XADataSource xads = new EmbeddedXADataSource();
• | DatabaseName This mandatory property must be set. It identifies
which database to access. To access a database named wombat located at
/local1/db/wombat, call setDatabaseName("/local1/db/wombat")
on the data source object. | |
• | CreateDatabase Optional. Sets a property to create a database
the next time the getConnection method of a data source object is called.
The string createString is always "create" (or possibly null). (Use
the method setDatabaseName() to define the name of
the database.) | |
• | ShutdownDatabase Optional. Sets a property to shut down a
database. The string shutDownString is always "shutdown" (or possibly
null). Shuts down the database the next time the getConnection method
of a data source object is called. | |
• | DataSourceName Optional. Name for ConnectionPoolDataSource
or XADataSource. Not used by the data source object. Used for informational
purposes only. | |
• | Description Optional. Description of the data source. Not
used by the data source object. Used for informational purposes only. | |
• | connectionAttributes Optional. Connection attributes specific
to Derby. See the Derby Reference Manual for a more information about
the attributes. |
javax.sql.XADataSource xads = makeXADataSource(mydb, true); // example of setting property directory using // Derby 's XADataSource object import org.apache.derby.jdbc.EmbeddedXADataSource; import javax.sql.XADataSource; // dbname is the database name // if create is true, create the database if not already created XADataSource makeXADataSource (String dbname, boolean create) { // // If your application runs on JDK 1.6 or higher, then // you will use the JDBC4 variant of this class: // EmbeddedXADataSource40. // EmbeddedXADataSource xads = new EmbeddedXADataSource(); // use Derby 's setDatabaseName call xads.setDatabaseName(dbname); if (create) xads.setCreateDatabase("create"); return xads; }
• | User authentication Derby verifies
user names and passwords before permitting them access to the Derby system. | |
• | User authorization A means of granting specific users permission
to read a database or to write to a database. | |
• | Disk encryption A means of encrypting Derby data
stored on disk. | |
• | Validation of Certificate for Signed Jar Files In a Java 2
environment, Derby validates
certificates for classes loaded from signed jar files. | |
• | Network encryption and authentication Derby
network traffic may be encrypted with SSL/TLS. SSL/TLS certificate
authentication is also supported. See "Network encryption and authentication with SSL/TLS" in the
Derby Server and Administration Guide for
details. |


1.
| When first working with security, work with system-level properties only
so that you can easily override them if you make a mistake. | |
2.
| Be sure to create at least one valid user, and grant that user full (read-write)
access. For example, you might always want to create a user called sa with
the password derby while
you are developing. | |
3.
| Test the authentication system while it is still configured at the system
level. Be absolutely certain that you have configured the system correctly
before setting the properties as database-level properties. | |
4.
| Before disabling system-level properties (by setting derby.database.propertiesOnly to
true), test that at least one database-level read-write user (such as sa)
is valid. If you do not have at least one valid user that the system can authenticate,
you will not be able to access your database. |