* the org.apache.derby.jdbc.EmbeddedDataSource, rather than the
* org.apache.derby.jdbc.EmbeddedSimpleDataSource that we need to
* use for Java ME.
*/
EmbeddedSimpleDataSource ds = new EmbeddedSimpleDataSource();
/*
* The connection specifies "create" in the DataSource settings for
* the database to be created when connecting for the first time.
*
* To remove the database, remove the directory simpleMobileDB and its
* contents.
*
* The directory simpleMobileDB will be created in the directory that
* the system property <code>derby.system.home</code> points to, or the
* current directory (<code>user.dir</code>) if derby.system.home is not
* set.
*/
String dbName = "simpleMobileDB"; // the name of the database
ds.setDatabaseName(dbName);
// tell Derby to create the database if it does not already exist
ds.setCreateDatabase("create");
/* We will be using Statement and PreparedStatement objects for
* executing SQL. These objects are resources that should be released
* explicitly after use, hence the try-catch-finally pattern below.
*/
Connection conn = null;
Statement s = null;
PreparedStatement ps = null;
ResultSet rs = null; // used for retreiving the results of a query
try {
/* By default, the schema APP will be used when no username is
* provided.
* Otherwise, the schema name is the same as the user name.
* If you want to use a different schema, or provide a username and
* password for other reasons, you can connect using:
*
* Connection conn = ds.getConnection(username, password);
* or use the
* setUser(String) and setPassword(String) methods of
* EmbeddedSimpleDataSource.
*
* Note that user authentication is off by default, meaning that any
* user can connect to your database using any password. To enable
* authentication, see the Derby Developer's Guide.
*/
conn = ds.getConnection();
System.out.println("Connected to and created database " + dbName);
/* Creating a statement object that we can use for running various
* SQL statements commands against the database.*/
s = conn.createStatement();
// autoCommit is on by default
/* Create a table... */
s.execute("create table streetaddr(num int, addr varchar(40))");
System.out.println("Created table streetaddr");
// Insert some rows...
s.execute("insert into streetaddr values (1956,'Webster St.')");
System.out.println("Inserted 1956 Webster");
s.execute("insert into streetaddr values (1910,'Union St.')");
System.out.println("Inserted 1910 Union");
// Update some rows...
/* It is recommended to use PreparedStatements whenever you are
* repeating execution of an SQL statement. PreparedStatements also
* allows you to parameterize variables. By using PreparedStatements
* you may increase performance (because the Derby engine does not
* have to recompile the SQL statement each time it is executed) and
* improve security (because of Java type checking).
*/
// use this PreparedStatement for updating a row identified by num
ps = conn.prepareStatement(
"update streetaddr set num=?, addr=? where num=?");
// update one row...
ps.setInt(1, 180);
ps.setString(2, "Grand Ave.");
ps.setInt(3, 1956);
ps.executeUpdate();
System.out.println("Updated 1956 Webster to 180 Grand");
// update another row...
ps.setInt(1, 300);
ps.setString(2, "Lakeshore Ave.");
ps.setInt(3, 180);
ps.execute();
System.out.println("Updated 180 Grand to 300 Lakeshore");
// Select the rows and verify some of the results...
rs = s.executeQuery("SELECT num, addr FROM streetaddr ORDER BY num");
// Verification: Number of rows and sorted contents of the num column
boolean correctResults = true;
if (!rs.next())
{
System.err.println("No rows in table! (ResultSet was empty)");
correctResults = false;
} else {
int num;
int rows = 0;
do {
rows++;
num = rs.getInt(1);
if ((rows == 1) && (num != 300)) {
System.err.println("Wrong first row returned! "
+ "Expected num = 300, but got " + num);
correctResults = false;
} else if ((rows == 2) && (num != 1910)) {
System.err.println("Wrong second row returned! "
+ "Expected num = 1910, but got " + num);
correctResults = false;
}
} while (rs.next());
if (rows !=2) {
System.err.println("Wrong number of rows in ResultSet "
+ "(streetaddr table): " + rows);
correctResults = false;
}
}
if (correctResults) {
System.out.println("Verified the rows");
} else {
System.out.println("Verification failed: Wrong results!");
}
/* This demo automatically drops the table. This way the demo can
* be run the same way multiple times. If you want the data to
* stay in the database, comment out the following Statement
* execution and recompile the class.
*/
s.execute("drop table streetaddr");
System.out.println("Dropped table streetaddr");
// shut down the database
/* In embedded mode, an application should shut down the database.
* If the application fails to shut down the database explicitly,
* the Derby does not perform a checkpoint when the JVM shuts down,
* which means that the next connection will be slower because
* Derby has to perform recovery operations.
* Explicitly shutting down the database using the appropriate
* data source property is recommended.
* This style of shutdown will always throw an SQLException, but in
* this case the exception is (usually) not an indication that
* something went wrong.
*/
try {
ds.setShutdownDatabase("shutdown");
ds.getConnection();
} catch (SQLException se) {
if (!( (se.getErrorCode() == 45000)
&& ("08006".equals(se.getSQLState()) ))) {
// if the error code or SQLState is different, we have an
// unexpected exception (shutdown failed)