Introduction
Sun Microsystems recently announced that it is distributing and supporting Java DB based on the 100 percent Java technology, open-source Apache Derby database. Derby was previously available under its earlier name, Cloudscape, from its former owners: Cloudscape, Informix, and IBM. IBM donated the Derby product source code to the Apache Foundation as an open-source project. Sun, IBM, other companies, and individuals have been actively involved in development of the relational database as part of the Apache Derby community. Sun distributes Java DB in many of its products, including the Sun Java Enterprise System and the Sun Java System Application Server.
Java DB is lightweight at 2 megabytes and embeddable within desktop Java technology applications. Desktop applications can now access powerful database storage with triggers, stored procedures, and support for SQL, Java DataBase Connectivity (JDBC) software, and Java Platform, Enterprise Edition (Java EE, formerly referred to as J2EE), all embedded within the same Java virtual machine (JVM).
This article describes how to setup and start using Java DB for desktop application as embedded mode. Also we will explain this using simple example with explanations.
Embedded & Client-Server Mode
Derby can be used in a server mode and in a so-called embedded mode. If Derby runs in the server mode you start the Derby network server which will be responsible for handling the database requests. In embedded mode Derby runs within the JVM (Java Virtual Machine) of the application. In this mode only the application can access the database, e.g. another user / application will not be able to access the database.
Java DB Setup
Download the latest Derby version from the Apache website http://db.apache.org/derby/. Choose the bin distribution and extract this zip to a directory of your choice.
To start working with Java DB, only Derby.jar file is important but it should be in the classpath of project.
Also make the Derby tools available in your path:
- Set the environment variable DERBY_HOME to the Derby installation directory
- Add DERBY_HOME/bin to the “path” environment variable
Developer can use any IDE, like eclipse or netbeans to set derby.jar file in project classpath.
Connect to the Java DB via Java
Once derby.jar file is configured in classpath then we can connect using java program. Here, we are just trying to connect Java DB in embedded mode. Please follow steps below –
- Loading the Database Driver
Loading the JDBC technology driver starts the database management system. Java DB’s drivers come with the derby.jar file, so you don’t need to download anything else. Load the JDBC driver by referencing it using the Class.forName method. The embedded driver name is org.apache.derby.jdbc.EmbeddedDriver, and you can load it as you would other JDBC drivers.
Class.forName(“org.apache.derby.jdbc.EmbeddedDriver”);
Using above string we load Java DB drivers.
- Connect to Java DB
Once Java DB Drivers are loaded, we then need to connect with Database to establish a connection using below code.
conn = DriverManager.getConnection(“jdbc:derby:.\\Java_DB\\sampleDB.db” );
- Once the connection is established then use all standard Java Database connectivity APIs to create, Update, Delete & Read data from Java DB. Java DB is internal and not required to start or stop server manually or using any ant script as long as it is used in embedded mode.
Above example contains basic functions to connect and utilize APIs for Java DB. Hope you understood the concept and liked this tutorial. Please add your valuable comments/feedback and contact us if you have any question.
Example
Please find Example to connect, create, insert and retrieve data from Java DB below –
package com.kw.sample;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* This class helps understand Java DB Connection Example, It will include below
* things - 1. Load Java DB Drivers 2. Establish Java DB Connection 3. Create
* Table 4. Insert Data into Table 5. Read Data from Table
*
* @author dsahu1
*
*/
public class KWConnectionDerby {
// Attribute to hold connection object
private static Connection conn = null;
// Attribute to hold statement Object
private static Statement sttm = null;
/**
* This main method helps to execute program.
*
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
System.out.println("****** Welcome to Java DB Example ******");
// Call method to Load the drivers and establish connection
loadAndEstablishConnection();
// establish statement object to execute Query
sttm = conn.createStatement();
// Call method to create Sample Table, if not created
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getTables(null, null, "SAMPLE", null);
if (!rs.next()) {
sttm.executeUpdate(createTableSQL());
}
// Call method to insert Dummy Data into Sample Table
PreparedStatement stmtSaveNewRecord = conn.prepareStatement(
insertIntoTableSQL(), Statement.RETURN_GENERATED_KEYS);
saveRecord(stmtSaveNewRecord);
// Call method to read Data from Sample Table
retrieveAndDisplayData();
}
/**
* This method helps to load and establish connection.
*
* @return Connection
*/
public static Connection loadAndEstablishConnection() {
try {
// Load Java DB Drivers
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Establish Connection
conn = DriverManager
.getConnection("jdbc:derby:.\\Sample_JAVA_DB\\sampleDB.db;create=false");
} catch (SQLException e) {
System.out.println(e.getMessage());
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
return conn;
}
/**
* This method helps to provide SQL to create Table.
*
* @return String
*/
public static String createTableSQL() {
StringBuilder strBuilder = new StringBuilder();
strBuilder.append("CREATE TABLE APP.Sample (");
strBuilder.append("ID INTEGER NOT NULL ");
strBuilder.append("PRIMARY KEY GENERATED ALWAYS AS IDENTITY "
+ "(START WITH 1, INCREMENT BY 1),");
strBuilder.append("LASTNAME VARCHAR(30),");
strBuilder.append("FIRSTNAME VARCHAR(30),");
strBuilder.append("PHONE VARCHAR(20),");
strBuilder.append("COUNTRY VARCHAR(30) )");
return strBuilder.toString();
}
/**
* This method helps to provider insert SQL Query.
*
* @return String
*/
public static String insertIntoTableSQL() {
StringBuilder strBuilder = new StringBuilder();
strBuilder.append("INSERT INTO APP.Sample "
+ " (LASTNAME, FIRSTNAME," + " PHONE,COUNTRY) "
+ "VALUES (?, ?, ?, ?)");
return strBuilder.toString();
}
/**
* This method helps to save records into table.
*
* @param PreparedStatement
* @return ID
*/
public static int saveRecord(PreparedStatement stmtSaveNewRecord) {
int id = -1;
try {
stmtSaveNewRecord.clearParameters();
stmtSaveNewRecord.setString(1, "SAHU");
stmtSaveNewRecord.setString(2, "Dharmendra");
stmtSaveNewRecord.setString(3, "6093584059");
stmtSaveNewRecord.setString(4, "INDIA");
int rowCount = stmtSaveNewRecord.executeUpdate();
System.out.println("Total Number of Records available:" + rowCount);
ResultSet results = stmtSaveNewRecord.getGeneratedKeys();
if (results.next()) {
id = results.getInt(1);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
}
return id;
}
/**
* This method helps to read records form the database and display.
*
* @throws SQLException
*/
public static void retrieveAndDisplayData() throws SQLException {
PreparedStatement statement = conn
.prepareStatement("SELECT * from APP.Sample");
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
String id = resultSet.getString("ID");
String lastName = resultSet.getString("LASTNAME");
String firstName = resultSet.getString("FIRSTNAME");
String phoneNumber = resultSet.getString("PHONE");
String country = resultSet.getString("COUNTRY");
System.out.println("ID: " + id);
System.out.println("LASTNAME: " + lastName);
System.out.println("FIRSTNAME: " + firstName);
System.out.println("PHONE: " + phoneNumber);
System.out.println("COUNTRY: " + country);
}
}
}
Outcome
****** Welcome to Java DB Example ******
ID: 1
LASTNAME: SAHU
FIRSTNAME: Dharmendra
PHONE: 6093584059
COUNTRY: INDIA
ID: 101
LASTNAME: SAHU
FIRSTNAME: Dharmendra
PHONE: 6093584059
COUNTRY: INDIA
Above example contains basic functionaries to connect and utilize APIs for Java DB. Hope you understand the concept and like the tutorial, Please add your valuable comments and contact us if any Query.