Introducing Java DB 10.5.1.1

Authentifizierung
Anmelden als:

Downloads
Hardware
Software
Support
Documentation
Training

Sun Log In Sun Contacts How to Buy
Oracle and Sun Sun Worldwide Java Developers Solaris Admins and Developers Java.com

( Sign In/Register for Account | Help ) United States

SELECT A COUNTRY/REGION

Brazil
China
Japan
Korea
Latin America
Russia
United States

Communities

Social Applications
Oracle Mix
Oracle Blogs
Oracle Wiki
Oracle on Facebook
Oracle on Twitter
Networks
Oracle ACEs
Oracle PartnerNetwork
Oracle Technology Network
Knowledge Base
Oracle User Groups
Oracle Discussion Forums
Support
My Oracle Support Community

I am a...

Java Developer
Database Admins and Developers
System Admins and Developers
Architect
C-Level Executives
Chief Financial Officer (CFO)
Chief Information Officer (CIO)
Other Roles
Analyst
Investor
Job Seeker
Partner
PeopleSoft Customer
Siebel Customer
Sun Customer
Student
Midsize Company

I want to...

Support
Create or Update Service Request
Search Support Knowledge Database
Download Patches
Education
Find Oracle University Training
Choose an Oracle Certification Path
Partner
Find a Partner Solution
Access Software and Technical Support
Attend a Partner-Only Event
Attend Training
Download Logos
Manage Solutions Catalog
Other Tasks
Purchase Oracle Products Online
Browse Oracle Documentation
Search Oracle Documentation
Check Oracle Earnings
Check Oracle Price Lists
Download Oracle Software
Find an Oracle ACE
View Oracle Benchmarks

Filters:

- All
- Blogs
- Corporate Site
- CRM On Demand
- Discussion Forums
- Documentation
- Education
- Java.com
- JavaOne and Oracle Develop
- MySQL
- Oracle OpenWorld
- PartnerNetwork
- Sun Defects
- Technology Network
- Video and Multimedia

- Application Development Framework
- Application Express
- Business Intelligence
- Cloud Computing
- Communications
- Database Performance & Availability
- Data Warehousing
- .NET
- Dynamic Scripting Languages
- Embedded
- Enterprise 2.0
- Enterprise Architecture
- Enterprise Management
- Grid Computing
- Identity & Security
- Java
- Linux
- Service-Oriented Architecture
- SQL & PL/SQL
- Server and Storage Administration
- Server and Storage Development
- Systems Hardware and Architecture
- Virtualization

# Introducing Java DB 10.5.1.1

By Rick Hillegas, June 2009

Articles Index

Contents

- In-Memory Database
- Intermission
- Generated Columns
- The `OFFSET/FETCH` Syntax
- SQL Roles
- For More Information

The next major release of Java DB, version 10.5.1.1, is now available for download. Java DB is Sun's supported distribution of the Apache Derby open-source database. Java DB is 100 percent Java technology and easy to use. Don't be fooled by its small JAR file (2.5M): This free database is fast, reliable, and packed with features. You can learn more about it on the Java DB web site.

The new 10.5.1.1 release of Java DB introduces performance boosts and some exciting features:

- Scalability. Throughput has gone up significantly for applications that concurrently read and update indexed tables.
- Large Objects (LOBs). Large binary and text objects stream faster.
- In-Memory Database. Fast, transient databases can live entirely in memory and vanish when the Java Virtual Machine (JVM)* exits.
- Generated Columns. Queries run faster because query fragments can be indexed.
- `OFFSET/FETCH`. Results can be chunked efficiently.
- SQL Roles. Fine-grained access control is simpler.

The rest of this article describes the new features: in-memory database, generated columns, `OFFSET/FETCH` syntax, and SQL roles.

In-Memory Database

Version 10.5.1.1 introduces in-memory databases. Now you can run fast queries against complicated data that disappears when your JVM exits. In-memory databases are great for the following:

- Caches. Use in-memory Java DB in the middle tier to cache data from enterprise back ends.
- Monitors. Use in-memory Java DB to aggregate and analyze transient streams of sensor and environmental data.
- Test rigs. Use in-memory Java DB to plug into test rigs so that laptops can test enterprise applications fast.

Creating an in-memory database is easy. Just add the `memory` subprotocol to the JDBC connection URL. For instance, if your classpath contains the Derby JAR file ( `derby.jar`), here's how you create a JDBC connection to an in-memory database called `patient`:

    java.sql.Connection conn = java.sql.DriverManager.getConnection( "jdbc:derby:

    memory:patient;create=true" );

After that, you use the in-memory `patient` database just like you would a disk-based database: Create tables, views, triggers, functions, and procedures. Insert, update, browse, and delete data. When your application exits, the database disappears, leaving no trace of itself on disk.

The in-memory database is an experimental feature in version 10.5.1.1. This means that the API may change when it is fully productized in the next major release. Try the new release and let the Java DB development team know how to improve it. The best way to reach the team is through the Derby community mailing list, specifically derby-user [at] db.apache.org.

Intermission

Before turning to the other major features in version 10.5.1.1, let's set up Java DB for the examples that this article will show. These examples use a medical research application. In this application, data is collected on patients participating in a study. Because these are just examples, you take two shortcuts. First, you use a transient in-memory database so that you don't have to clean up when you are done.

Second, you use Java DB's `BUILTIN` authentication scheme. The `BUILTIN` authentication scheme is useful for writing tests like this, but you should not use this scheme in production. To run an application like this in production, use an on-disk database and plug in an LDAP-based authenticator as described in the white paper on Java DB security.

To run these examples, download version 10.5.1.1 of the Java DB engine and tools. Make sure that you wire the `derby.jar` and `derbytools.jar` files into your classpath.

Start by creating a database that recognizes three users: Alice, Ruth, and an administrator:

    java \
      -Dderby.connection.requireAuthentication=true  \
      -Dderby.authentication.provider=BUILTIN \
      -Dderby.user.admin=adminpassword \
      -Dderby.user.alice=alicepassword \
      -Dderby.user.ruth=ruthpassword \
      -Dderby.database.sqlAuthorization=true \
      -Dderby.stream.error.method=java.sql.DriverManager.getLogStream \
      org.apache.derby.tools.ij

At this point, you are talking to the `ij` program, Java DB's command-line tool for executing SQL statements. Now the administrator creates a database:

    connect 'jdbc:derby:memory:patient;create=true;user=admin;password=adminpassword' as admin_conn;

Now that you have set up Java DB for this article's examples, let's go back to discussing the new 10.5.1.1 features.

Generated Columns

Version 10.5.1.1 introduces generated columns. A generated column is a column whose contents are computed from the values of other columns in the same row. This allows you to take expressions out of the `WHERE` clause of a query, pre-compute those expressions, and index the pre-computed results so that your query runs faster. An example follows.

Remember that the administrator has just booted a database. Next, the administrator creates some tables to hold the patient data. One of the tables has a generated column, flagged here as the first line of bold type. Also note the index, flagged here as the second bold line:

    create table privatePatientData
    (
       patientID int primary key generated always as identity,
       name varchar( 32672 ) not null,
       address varchar( 32672 ) not null,
       telephoneNumber varchar( 100 ) not null
    );
    create table publicPatientData
    (
       patientID int primary key references privatePatientData( patientID ),
       postalCode varchar( 100 ),
       dateBorn date not null,
       heightInches double not null,
       weightPounds double not null,
       gender char(1),
       diseaseMetric int,
       heightWeightRatio

    generated always as ( (heightInches*heightInches) / weightPounds )
    );

    create index ppd_hwr_idx on publicPatientData( heightWeightRatio, diseaseMetric );
    create index ppd_d_idx on publicPatientData( diseaseMetric, patientID );

Let's take a look at the generated column and the bold text that follows it, the generation clause. The generation clause consists of some keywords followed by an expression in parentheses. The expression itself is built up out of other columns in the row and can be complicated. For instance, the expression in parentheses can involve system and user-defined functions.

Whenever you insert a new row into the `publicPatientData` table, Java DB automatically computes the contents of the generated `heightWeightRatio` column based on the values that you have inserted into the `heightInches` and `weightPounds` columns. Whenever you update the `heightInches` or `weightPounds` columns, Java DB automatically recalculates the value of the generated column. The only way to change the contents of the generated column is to alter the value of a column that it depends on.

Remember the index `ppd_hwr_idx`, flagged on the second bold line? That index, combined with the generated column, makes it easy for Java DB to calculate how patient health correlates with the patient's height-weight ratio. The following query can take advantage of the index and therefore run fast:

    select diseaseMetric, count(*) outcome
    from publicPatientData where heightWeightRatio < 0.4
    group by diseaseMetric;

The `OFFSET/FETCH` Syntax

Version 10.5.1.1 also introduces the `OFFSET/FETCH` syntax, which was recently added to the ANSI/ISO SQL standard. This syntax lets you focus attention on a small page of results without having to read a table all the way to the end. Some other databases support this functionality through the nonstandard `LIMIT` clause.

Suppose that, during the course of the medical study, researchers want to notify patients who are at high risk as measured by their `diseaseMetric`. Several researchers can share this notification job by using queries like the following statements. These queries divide the high-risk patients into groups, and each researcher takes responsibility for a group. Note the new syntax flagged in bold:

    -- Page 1
    select diseaseMetric, pub.patientID, name, telephoneNumber
    from publicPatientData pub, privatePatientData priv
    where pub.patientID = priv.patientID
    and diseaseMetric > 10
    order by diseaseMetric, pub.patientID

    offset 0 rows
    fetch next 20 rows only;

    -- Page 2
    select diseaseMetric, pub.patientID, name, telephoneNumber
    from publicPatientData pub, privatePatientData priv
    where pub.patientID = priv.patientID
    and diseaseMetric > 10
    order by diseaseMetric, pub.patientID

    offset 20 rows
    fetch next 20 rows only;

In the preceding queries, the `OFFSET` clause tells Java DB to skip over some number of rows before it returns results to the user. The `FETCH NEXT` clause tells Java DB to stop reading data after giving the user the desired number of rows.

SQL Roles

Finally, version 10.5.1.1 introduces roles to help simplify the management of fine-grained SQL permissions. Roles let you define packets of privileges and then grant a whole packet to a user. This ability gives application designers and database administrators several benefits:

- Clarity. Roles make it easy to prove, at a glance, that permissions are restricted to the people who should perform certain jobs.
- Simplicity. Roles make it easy to add and delete users from specific job categories.

In the following example, the administrator creates two roles: `researcher` and `tech_writer`. Users with the `researcher` role can view and update all information. Users with the `tech_writer` role cannot change the data and cannot view sensitive, personal information:

    create role researcher;
    create role tech_writer;

    grant all privileges on privatePatientData to researcher;
    grant all privileges on publicPatientData to researcher;

    grant select on publicPatientData to tech_writer;

Next, the administrator identifies Alice as a researcher and Ruth as a tech writer:

    grant researcher to alice;
    grant tech_writer to ruth;

Now Alice verifies that she can create and view data. Note the statement in bold type in the following code: Alice starts out explicitly stating that she is going to perform researcher tasks. This can be surprising at first. If an application relies on SQL roles, then its users have to explicitly state what job they are performing at any given time:

    connect 'jdbc:derby:memory:patient;user=alice;password=alicepassword' as alice_conn;

    set role researcher;

    -- Alice can insert data into these tables
    insert into admin.privatePatientData
    ( name, address, telephoneNumber )
    values
    ( 'Martha Murphy', '29 Primrose Lane/St. Paul, Minnesota', '123-456-7890' )
    ;
    insert into admin.publicPatientData
    values
    (
      ( select patientID from admin.privatePatientData where name = 'Martha Murphy' ),
      '55109',
      date('1955-02-23'),
      66,
      150,
      'F',
      7,
      default
    );

    -- Alice can view all of the data
    select * from admin.privatePatientData;
    select * from admin.publicPatientData;

Similarly, Ruth verifies that she can read only public data and that she cannot change any data. In the following example, the comments flag several spots where you will see errors when you run this script through the `ij` tool:

    connect 'jdbc:derby:memory:patient;user=ruth;password=ruthpassword' as ruth_conn;

    -- Ruth is not allowed to set herself to a role not already granted to her.

    set role researcher;

    -- but she can set herself to this role

    set role tech_writer;

    -- she does not have permission to insert into the tables
    insert into admin.privatePatientData
    ( name, address, telephoneNumber )
    values
    ( 'XXX', 'XXXX', 'XXX-XXX-XXXX' )
    ;

    -- she can't read the private table
    select * from admin.privatePatientData;

    -- but she can read the public table
    select * from admin.publicPatientData;

That concludes the examples. Now shut down the `ij` tool:

    exit;

This concludes the overview of the main features of Java DB 10.5.1.1: in-memory database, generated columns, `OFFSET/FETCH`, and SQL roles.

_______
* As used on this web site, the terms "Java Virtual Machine" and "JVM" mean a virtual machine for the Java platform.

For More Information

Download Java DB 10.5.1.1
Java DB Home Page
François Orsini's Blog: Where Innovations Matter
Knut Anders Hatlen's Weblog
Masood Mortazavi's Blog

Rate This Article

Comments

Terms of Use

Kein Avatar

Mein Computer

Google-Profil

Twitter-Account

FriendFeed-Account

Yahoo!-Account

Gravatar E-Mail

Anmelden

Google-Profil

Twitter-Account

FriendFeed-Account

Yahoo!-Account

Blogger-Account

JS-Kit-Account

Haloscan-Account

OpenID

Meine Website (Zum Bearbeiten hier klicken)

Teilen

Diese Seite

meinen Google-Freunden

meinen Twitter-Followern

meinen FriendFeed-Followern

meinen Yahoo!-Freunden

Diese Seite
Ihr Kommentar...
Bild hinzufügen
Folgen
Kommentieren
@ Steuerung
Echo 0 Einträge

Administrator |

Anmelden

Mein Profil bearbeiten

Folgen

Als Moderator fungieren

allgemeine Eistellungen

Anmerkungen des Administrators

Zu Ihrer Website hinzufügen

Echo Blog

Echo auf Twitter

Hilfe

–
Social Networking by Echo

1 vote

About Oracle | Oracle and Sun | | Subscribe | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights