Apache Derby (Cloudscape 10)
In 1997, the first release of the Cloudscape Java database engine, then called JBMS. In 1999 Informix Software acquired Cloudscape. In 2001 IBM acquired the database assets of Informix Software. In August 2004 IBM contributed the code to the Apache DB project and named it Apache Derby.
The core of the technology, Derby's database engine is a full functioned relational embedded database engine. JDBC and SQL are the programming APIs. The Derby network server increases the reach of the Derby database engine by providing traditional client server functionality. The network server allows clients to connect over TCP / IP using the standard DRDA protocol. Apache Derby can operate either as SQL Embedded or Network Server modes.
Apache Utilities:
- ij -- a tool that allows SQL scripts to be executed against any JDBC database.
- dblook -- Schema extraction tool for a Derby database.
- sysinfo -- Utility to display version numbers and class path.
Setting up Java Environment and quick test
set path=C:\Cloudscape\jre\bin
java -version
set CLASSPATH=c:\cloudscape\lib\derby.jar;c:\cloudscape\lib\derbytools.jar;
java org.apache.derby.tools.sysinfo
[ Display System Information ]
java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
Creating an Apache Derby database
[ Display the ij> prompt ]
connect 'jdbc:derby:MyAdb;create=true';
create table testab (num int, addr varchar(40));
insert into testab values (1956,'Webster St.');
insert into testab values (2004,'ZZZ St.');
select * from testab;
exit;
[ Exit ij prompt and back to Command Prompt ]
java org.apache.derby.tools.dblook -d 'jdbc:derby:MyAdb'
[ This will display what is inside the database - example MyAdb ]
Creating a secure (userid & password protected) database
set path=C:\Cloudscape\jdk\bin
java -version
set CLASSPATH=c:\cloudscape\lib\derby.jar;c:\cloudscape\lib\derbytools.jar;
java org.apache.derby.tools.sysinfo
javac SimpleApp.java
java SimpleApp
Listing of SimpleApp.java
import java.sql.Connection;java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class SimpleApp
{
public String framework = "embedded";
public String driver = "org.apache.derby.jdbc.EmbeddedDriver";
public String protocol = "jdbc:derby:";
public static void main(String[] args)
{
new SimpleApp().go(args);
}
void go(String[] args)
{
parseArguments(args);
System.out.println("SimpleApp starting in " + framework + " mode.");
try
{
Class.forName(driver).newInstance();
System.out.println("Loaded the appropriate driver.");
Connection conn = null;
Properties props = new Properties();
/*
props.put("user", "user1");
props.put("password", "pass2");
*/
conn = DriverManager.getConnection(protocol +"SafeAdb;create=true", props);
System.out.println("Connected to and created database SafeAdb");
conn.setAutoCommit(false);
Statement s = conn.createStatement();
s.execute("create table TesTab (num int, addr varchar(40))");
System.out.println("Created table derbyDB");
s.execute("insert into TesTab values (1956,'Webster St.')");
System.out.println("Inserted 1956 Webster");
s.execute("insert into TesTab values (1910,'Union St.')");
System.out.println("Inserted 1910 Union");
s.execute("update TesTab set num=180, addr='Grand Ave.' where num=1956");
System.out.println("Updated 1956 Webster to 180 Grand");
s.execute("update TesTab set num=300, addr='Lakeshore Ave.' where num=180");
System.out.println("Updated 180 Grand to 300 Lakeshore");
ResultSet rs = s.executeQuery("SELECT num, addr FROM TesTab ORDER BY num");
if (!rs.next())
{
throw new Exception("Wrong number of rows");
}
if (rs.getInt(1) != 300)
{
throw new Exception("Wrong row returned");
}
if (!rs.next())
{
throw new Exception("Wrong number of rows");
}
if (rs.getInt(1) != 1910)
{
throw new Exception("Wrong row returned");
}
if (rs.next())
{
throw new Exception("Wrong number of rows");
}
System.out.println("Verified the rows");
/*
s.execute("drop table TesTab");
System.out.println("Dropped table TesTab");
*/
rs.close();
s.close();
System.out.println("Closed result set and statement");
conn.commit();
conn.close();
System.out.println("Committed transaction and closed connection");
/*
In embedded mode, an application should shut down Derby.
If the application fails to shut down Derby explicitly,
the Derby does not perform a checkpoint when the JVM shuts down, which means
that the next connection will be slower.
Explicitly shutting down Derby with the URL is preferred.
This style of shutdown will always throw an "exception".
*/
boolean gotSQLExc = false;
if (framework.equals("embedded"))
{
try
{
DriverManager.getConnection("jdbc:derby:;shutdown=true");
}
catch (SQLException se)
{
gotSQLExc = true;
}
if (!gotSQLExc)
{
System.out.println("Database did not shut down normally");
}
else
{
System.out.println("Database shut down normally");
}
}
}
catch (Throwable e)
{
System.out.println("exception thrown:");
if (e instanceof SQLException)
{
printSQLError((SQLException) e);
}
else
{
e.printStackTrace();
}
}
System.out.println("SimpleApp finished");
}
static void printSQLError(SQLException e)
{
while (e != null)
{
System.out.println(e.toString());
e = e.getNextException();
}
}
private void parseArguments(String[] args)
{
int length = args.length;
for (int index = 0; index < length; index++)
{
if (args[index].equalsIgnoreCase("jccjdbcclient"))
{
framework = "jccjdbc";
driver = "com.ibm.db2.jcc.DB2Driver";
protocol = "jdbc:derby:net://localhost:1527/";
}
}
}
}
CONNECT 'jdbc:derby:SafeAdb;user=user1;password=pass2;'
insert into TesTab values (5956,'Webster St.');
insert into TesTab values (5004,'ZZZ St.');
select * from TesTab;
Updated On: 15.02.13