Java DB: Embedded Database in JDK6

JRE6 introduces a new feature: JavaDB. You can directly use this DB in both within desktop application or network application like we normally did using MySQL.

I am particulaly interested in the embedded DB. Here below is a simple demo showing how it works:

1. Create project in Netbeans;

2. Add two JAR files to project library (JavaDB SDK can be downloaded from http://db.apache.org/derby/derby_downloads.html)

• derby.jar: contains the Derby engine and the Derby Embedded JDBC driver
• derbytools.jar: optional, provides the ij tool that is used by a couple of sections in this tutorial
  

 3. Compose your source code:

package hellojavadb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class helloJavaDB {

    public static void main(String[] args) {
        try { // load the driver

            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            System.out.println("Load the embedded driver");
            Connection conn = null;
            Properties props = new Properties();
            props.put("user", "user1");
            props.put("password", "user1");
            //create and connect the database named helloDB 
            conn = DriverManager.getConnection("jdbc:derby:helloDB;create=true", props);
            System.out.println("create and connect to helloDB");
            conn.setAutoCommit(false);

            // create a table and insert two records
            Statement s = conn.createStatement();
            s.execute("create table hellotable(name varchar(40), score int)");
            System.out.println("Created table hellotable");
            s.execute("insert into hellotable values('Ruth Cao', 86)");
            s.execute("insert into hellotable values ('Flora Shi', 92)");
            // list the two records
            ResultSet rs = s.executeQuery(
                    "SELECT name, score FROM hellotable ORDER BY score");
            System.out.println("name\t\tscore");
            while (rs.next()) {
                StringBuilder builder = new StringBuilder(rs.getString(1));
                builder.append("\t");
                builder.append(rs.getInt(2));
                System.out.println(builder.toString());
            }
            // delete the table
            s.execute("drop table hellotable");
            System.out.println("Dropped table hellotable");

            rs.close();
            s.close();
            System.out.println("Closed result set and statement");
            conn.commit();
            conn.close();
            System.out.println("Committed transaction and closed connection");

            try { // perform a clean shutdown 

                DriverManager.getConnection("jdbc:derby:;shutdown=true");
            } catch (SQLException se) {
                System.out.println("Database shut down normally");
            }
        } catch (Throwable e) {
            // handle the exception
            e.printStackTrace();
        }
        System.out.println("SimpleApp finished");
    }
}

4. Execute the application. You should see the print out:

Load the embedded driver
create and connect to helloDB
Created table hellotable
name		score
Ruth Cao	86
Flora Shi	92
Dropped table hellotable
Closed result set and statement
Committed transaction and closed connection
Database shut down normally
SimpleApp finished

This tutorial is modified from http://www.ibm.com/developerworks/cn/java/j-lo-jse65/