Java DB Developer's Guide - Oracle

[Pages:172]Java DB Developer's Guide

Version 10.8

Derby Document build: April 7, 2014, 3:56:05 PM (EDT)

Version 10.8 Java DB Developer's Guide

Contents

Copyright................................................................................................................................5

License................................................................................................................................... 6

Relationship between Java DB and Apache Derby.........................................................10

About this guide..................................................................................................................11 Purpose of this guide..............................................................................................11 Audience................................................................................................................... 11 How this guide is organized...................................................................................11

After installing..................................................................................................................... 13 The installation directory........................................................................................ 13 Batch files and shell scripts................................................................................ 13 Derby and JVMs.......................................................................................................13 Derby libraries and classpath................................................................................ 14 UNIX-specific issues................................................................................................14 Configuring file descriptors..................................................................................14 Scripts.................................................................................................................. 14

Upgrades.............................................................................................................................. 15 Preparing to upgrade.............................................................................................. 15 Upgrading a database............................................................................................. 15 Soft upgrade limitations..........................................................................................16

JDBC applications and Derby basics............................................................................... 17 Application development overview........................................................................17 Derby embedded basics......................................................................................... 17 Derby JDBC driver.............................................................................................. 18 Derby JDBC database connection URL..............................................................18 Derby system.......................................................................................................19 A Derby database............................................................................................... 23 Connecting to databases.....................................................................................26 Working with the database connection URL attributes....................................... 29 Using in-memory databases................................................................................... 32 Working with Derby properties.............................................................................. 34 Properties overview............................................................................................. 34 Setting Derby properties......................................................................................35 Properties case study..........................................................................................38

Deploying Derby applications............................................................................................40 Deployment issues.................................................................................................. 40 Embedded deployment application overview...................................................... 40 Deploying Derby in an embedded environment.................................................. 41 Creating Derby databases for read-only use........................................................ 42 Creating and preparing the database for read-only use..................................... 42 Deploying the database on the read-only media................................................ 42 Transferring read-only databases to archive (jar or zip) files.............................. 42 Accessing a read-only database in a zip/jar file..................................................43 Accessing databases within a jar file using the classpath.................................. 44 Databases on read-only media and DatabaseMetaData.................................... 44 Loading classes from a database..........................................................................45 Class loading overview........................................................................................45 Dynamic changes to jar files or to the database jar classpath............................ 47

Derby server-side programming........................................................................................48

i

Version 10.8 Java DB Developer's Guide

Programming database-side JDBC routines.........................................................48 Database-side JDBC routines and nested connections...................................... 48 Database-side JDBC routines using non-nested connections............................ 49 Database-side JDBC routines and SQLExceptions............................................ 49 User-defined SQLExceptions.............................................................................. 50

Programming trigger actions..................................................................................50 Trigger action overview....................................................................................... 50 Performing referential actions............................................................................. 51 Accessing before and after rows.........................................................................51 Examples of trigger actions.................................................................................51 Triggers and exceptions...................................................................................... 51

Programming Derby-style table functions............................................................ 52 Overview of Derby-style table functions..............................................................52 Example Derby-style table function.....................................................................54 Writing restricted table functions......................................................................... 55 Optimizer support for Derby-style table functions............................................... 57

Programming user-defined types...........................................................................60

Controlling Derby application behavior............................................................................64 The JDBC connection and transaction model......................................................64 Connections......................................................................................................... 64 Transactions........................................................................................................ 65 Result set and cursor mechanisms....................................................................... 68 Simple non-updatable result sets........................................................................69 Updatable result sets...........................................................................................69 Result sets and auto-commit...............................................................................74 Scrollable result sets........................................................................................... 74 Holdable result sets.............................................................................................75 Locking, concurrency, and isolation..................................................................... 76 Isolation levels and concurrency......................................................................... 76 Configuring isolation levels..................................................................................79 Lock granularity................................................................................................... 79 Types and scope of locks in Derby systems...................................................... 80 Deadlocks............................................................................................................ 83 Working with multiple connections to a single database....................................88 Deployment options and threading and connection modes................................ 88 Multi-user database access.................................................................................89 Multiple connections from a single application....................................................89 Working with multiple threads sharing a single connection...............................89 Pitfalls of sharing a connection among threads.................................................. 89 Multi-thread programming tips.............................................................................90 Example of threads sharing a statement............................................................ 90 Working with database threads in an embedded environment...........................91 Working with Derby SQLExceptions in an application........................................ 92 Information provided in SQL Exceptions.............................................................92

Using Derby as a Java EE resource manager................................................................. 93 Classes that pertain to resource managers..........................................................93 Getting a DataSource.............................................................................................. 94 Shutting down or creating a database.................................................................. 94

Derby and security..............................................................................................................96 Configuring security for your environment.......................................................... 97 Configuring security in a client/server environment............................................ 97 Configuring security in an embedded environment.............................................98 Working with user authentication.......................................................................... 98 Enabling user authentication............................................................................... 99

ii

Version 10.8 Java DB Developer's Guide

Defining users....................................................................................................100 External directory service.................................................................................. 100 Built-in Derby users........................................................................................... 105 List of user authentication properties................................................................ 106 Programming applications for Derby user authentication..................................106 Users and authorization identifiers......................................................................107 Authorization identifiers, user authentication, and user authorization............... 107 Database owner ............................................................................................... 108 User names and schemas................................................................................ 109 Exceptions when using authorization identifiers................................................109 User authorizations................................................................................................109 Setting the default connection access mode.................................................... 110 Setting access for individual users....................................................................111 Setting the SQL standard authorization mode.................................................. 112 Encrypting databases on disk..............................................................................119 Requirements for Derby encryption.................................................................. 119 Working with encryption.................................................................................... 119 Signed jar files....................................................................................................... 125 Notes on the Derby security features..................................................................125 User authentication and authorization examples............................................... 126 User authentication example in a client/server environment.............................126 User authentication example in a single-user, embedded environment............ 132 User authentication examples using SQL authorization....................................138 Running Derby under a security manager.......................................................... 154 Granting permissions to Derby..........................................................................154 Examples of Java 2 security policy files for embedded Derby.......................... 156

Developing tools and using Derby with an IDE.............................................................158 Offering connection choices to the user............................................................ 158 The DriverPropertyInfo Array............................................................................ 158 Using Derby with IDEs.......................................................................................... 159 IDEs and multiple JVMs....................................................................................159

SQL tips............................................................................................................................. 161 Retrieving the database connection URL........................................................... 161 Supplying a parameter only once........................................................................ 161 Defining an identity column................................................................................. 161 Using third-party tools.......................................................................................... 161 Tricks of the VALUES clause............................................................................... 162 Multiple rows......................................................................................................162 Mapping column values to return values.......................................................... 162 Creating empty queries..................................................................................... 162

Localizing Derby................................................................................................................163 SQL parser support for Unicode..........................................................................163 Character-based collation in Derby..................................................................... 163 Other components with locale support...............................................................165 Messages libraries................................................................................................. 166

Derby and standards........................................................................................................ 167 XML data types and operators............................................................................. 168

Trademarks........................................................................................................................ 170

iii

Java DB Developer's Guide

Apache Software FoundationJava DB Developer's GuideApache Derby

4

Java DB Developer's Guide

Copyright

Copyright 2004-2012 The Apache Software Foundation Copyright 2005, 2012, Oracle and/or its affiliates. All rights reserved. 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 . Related information License

5

Java DB Developer's Guide

License

The Apache License, Version 2.0

Apache License Version 2.0, January 2004

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

6

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download