Tag Archives: derby

Making sample JDBC code easier

Posted on by
Reply

I’m writing a lot of small JDBC examples for our upcoming practice tests book. I got tired of writing the same code over and over so I wrote this helper class. It can:

  • Drop the table if it already there (because I run the same examples many times)
  • Run a varargs of SQL statements (to create tables and insert data)
  • Spit out the contents of a table (to confirm what happened)


import java.sql.*;
public class DerbyUtil {

    public static void dropTablesIfExist(String url, String... tables) throws SQLException {
        try (var conn = DriverManager.getConnection(url)) {
            for (String table : tables) {
                try (PreparedStatement ps = conn.prepareStatement("DROP TABLE " + table)) {
                    ps.executeUpdate();
                } catch (SQLException e) {
                    // ignore exceptions on drop since table might not exist
                }
            }
        }
    }

    public static void run(String url, String... sqlStatements) throws SQLException {
        try (var conn = DriverManager.getConnection(url)) {
            for (String stmt : sqlStatements) {
                try (PreparedStatement ps = conn.prepareStatement(stmt)) {
                    ps.executeUpdate();
                }
            }
        }
    }

    public static void outputTable(String url, String tableName) throws SQLException {
        String sql = "SELECT * FROM " + tableName;
        try (var conn = DriverManager.getConnection(url);
             var ps = conn.prepareStatement(sql);
             var rs = ps.executeQuery()) {

            var metadata = rs.getMetaData();
            int numColumns = metadata.getColumnCount();

            while (rs.next()) {
                outputRow(rs, numColumns);
            }
        }
    }

    private static void outputRow(ResultSet rs, int numColumns) throws SQLException {
        for (int i = 1; i <= numColumns; i++) {
            System.out.print(rs.getString(i));
            System.out.print('\t');
        }
        System.out.println();
    }
}

OCP 11 Book Bonus: Creating a Derby Database in Java 11

Posted on by
4

Update (11/05/2020): Read The 1Z0-819 Exam page to learn how you can easily our Java 11 Study Guides to prepare for Oracle’s 1Z0-819 Exam, as well as the 1Z0-817 Upgrade Exam.

If you are taking the Java 8 exam, see the Java 8 version of this post instead.

While it is certainly possible to get all the JDBC questions on the exam correct without running any code or understanding any SQL, it is nice to be able to follow along. This blog post is meant to help anyone who has purchased our book, OCP: Oracle Certified Professional Java SE 11 Programmer II Study Guide: Exam 1Z0-816, download and run through the examples in the text. It also includes the database installation instructions and setup code so you can simply copy/paste it. The actual book covers what you need to know for the exam.

This blog post assumes you are reading chapter 10 of our OCP 11 book for 816 exam and have gotten up to the part that references this blog post.

Download Derby

Option 1: Download the derby.jar from Maven Central

Option 2: Apache Derby is an open source database. It is really easy to install and use. To install:

  1. Go to the Derby download page
  2. Click on the link for the latest version. (At the time of this blog post, that was 10.15.1.3)
  3. Download the zip file and unzip it. This will create a folder named something like db-derby-10.15.1.3-bin. The unzipped contents will look like this:

In the lib directory, copy the derby.jar to a convenient location on your machine.

Creating your initial database

To start out, copy this code into a file named SetupDerbyDatabase.java.

import java.sql.*;

public class SetupDerbyDatabase {

    public static void main(String[] args) throws Exception {
        String url = "jdbc:derby:zoo;create=true";
        try (Connection conn = DriverManager.getConnection(url)) {

            // run(conn,"DROP TABLE names");
            // run(conn,"DROP TABLE exhibits");

            run(conn,"CREATE TABLE exhibits ("
                    + "id INTEGER PRIMARY KEY, "
                    + "name VARCHAR(255), "
                    + "num_acres DECIMAL(4,1))");

            run(conn,"CREATE TABLE names ("
                    + "id INTEGER PRIMARY KEY, "
                    + "species_id integer REFERENCES exhibits (id), "
                    + "name VARCHAR(255))");

            run(conn,"INSERT INTO exhibits VALUES (1, 'African Elephant', 7.5)");
            run(conn,"INSERT INTO exhibits VALUES (2, 'Zebra', 1.2)");

            run(conn,"INSERT INTO names VALUES (1, 1, 'Elsa')");
            run(conn,"INSERT INTO names VALUES (2, 2, 'Zelda')");
            run(conn,"INSERT INTO names VALUES (3, 1, 'Ester')");
            run(conn,"INSERT INTO names VALUES (4, 1, 'Eddie')");
            run(conn,"INSERT INTO names VALUES (5, 2, 'Zoe')");

            printCount(conn,"SELECT count(*) FROM names");
        }
    }

    private static void run(Connection conn, String sql) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.executeUpdate();
        }
    }

    private static void printCount(Connection conn, String sql) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            rs.next();
            System.out.println(rs.getInt(1));
        }
    }
}

To run the program, you need to include the Derby jar file in your classpath. If you don’t know how to find it or encounter problems see the below frequently asked questions in this blog post.

For example:

java -cp "<pathToDerby>/derby.jar" SetupDerbyDatabase.java

If all goes well, the program will output the number 5.

Note that we used single file source code execution here. This simplified the classpath since we only needed to specify the derby jar. Had we compiled our class we would have needed to include it as well (along with an operating system delimiter)

Alternatively, you could have added Derby to your CLASSPATH environment variable and just run the program as

java SetupDerbyDatabase.java

What does this program actually do?

The main method starts out by obtaining a connection to the Derby database. It then calls a run method to actually run the SQL. The run method uses a PreparedStatement with all the data hardcoded.

The code runs two SQL statements to create tables in the zoo database. The commands each include:

  • the table name – exhibits and names
  • the fields in each table along with their type. Integer is like a Java int. Decimal is like a Java double. Varchar stands for variable character and is like a String. The variable length part means that the database doesn’t need to allocate space for all 255 characters and should only use the space for the actual length of the string. (This matters when you frequently update the field with values of different lengths)
  • the primary key for each table – this tells the database how to you uniquely identify each row

Then the code runs seven SQL statements to insert rows into these tables. The order of the data columns matches the order the fields were defined in the create statements.

Finally, the code runs a query to check the rows were added to the database. The count(*) function in SQL always returns a number. For an empty table, this number is zero. Therefore, we can call rs.next() outside of a conditional or loop. We know there is always a number being returned.

Derby will create a “zoo” directory and a derby.log file in whatever directory you ran the program in. The zoo directory is your database.

Frequently Encountered Problems

If you have an error that isn’t here or have trouble with these instructions, feel free to ask a question in the CodeRanch forums

ERROR #1 – DERBY IS NOT IN YOUR CLASSPATH OR POINTS TO AN INVALID LOCATION

Exact error message:

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:derby:zoo;create=true
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)
	at SetupDerbyDatabase.main(SetupDerbyDatabase.java:7)

Solution:

Check you are actually pointing to Derby in your classpath. Also check your classpath has the three required components

ERROR #2 – THE TABLES ALREADY EXIST

Exact error message:

Exception in thread "main" java.sql.SQLException: Table/View 'EXHIBITS' already exists in Schema 'APP'.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:115)
...

Solution:

The program can only be run once as is. If you want to run it again, uncomment the two “drop table” lines.

Error #3 – NoClassDefFoundError: SystemPermission

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/derby/shared/common/security/SystemPermission
	at org.apache.derby.iapi.jdbc.AutoloadedDriver.connect(AutoloadedDriver.java:134)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)
	at injection.AttackStatement.main(AttackStatement.java:12)
Caused by: java.lang.ClassNotFoundException: org.apache.derby.shared.common.security.SystemPermission
	at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:583)
	at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
	at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:521)

If you are using an IDE that uses the module path instead of the classpath, add derbyshared.jar to your module path in addition to derby.jar. (This jar is needed when running with modules.)

Note: While the exam does have topics on modules, they are distinction questions. We recommend using the classpath (and command line) when studying all topics other than the Java Platform Module System.

OCP 8 Book Bonus: Creating a Derby Database in Java 8

Posted on by
13

While it is certainly possible to get all the JDBC questions on the exam without running any code or understanding any SQL, it is nice to be able to follow along. This blog post is meant to help anyone who has purchased our book, OCP: Oracle Certified Professional Java SE 8 Programmer II Study Guide: Exam 1Z0-809, download and run through the examples in the text. It also includes the database setup code so you can simply copy/paste it. The actual book covers what you need to know for the exam.

This blog post assumes you are reading chapter 10 of our OCP 8 book and have gotten up to the part that references this blog post.

Creating your initial database

Apache Derby is an open source database. It is really easy to use and comes with JDK 8. This means you don’t have to install anything special. You can even create and setup the database completely in Java. To start out, copy this code into a file named SetupDerbyDatabase.java.

import java.sql.*;

public class SetupDerbyDatabase {

   public static void main(String[] args) throws Exception {
      String url = "jdbc:derby:zoo;create=true";
      try (Connection conn = DriverManager.getConnection(url); 
           Statement stmt = conn.createStatement()) {
			
	   // stmt.executeUpdate("DROP TABLE animal");
	   // stmt.executeUpdate("DROP TABLE species");
			
	   stmt.executeUpdate("CREATE TABLE species ("
	        + "id INTEGER PRIMARY KEY, "
	 	+ "name VARCHAR(255), "
		+ "num_acres DECIMAL(4,1))");
		
	   stmt.executeUpdate("CREATE TABLE animal ("
		+ "id INTEGER PRIMARY KEY, "
		+ "species_id integer REFERENCES species (id), "
		+ "name VARCHAR(255), "
		+ "date_born TIMESTAMP)");

	   stmt.executeUpdate("INSERT INTO species VALUES (1, 'African Elephant', 7.5)");
   	   stmt.executeUpdate("INSERT INTO species VALUES (2, 'Zebra', 1.2)");

 	   stmt.executeUpdate("INSERT INTO animal VALUES (1, 1, 'Elsa', '2001-05-06 02:15:00')");
	   stmt.executeUpdate("INSERT INTO animal VALUES (2, 2, 'Zelda', '2002-08-15 09:12:00')");
	   stmt.executeUpdate("INSERT INTO animal VALUES (3, 1, 'Ester', '2002-09-09 10:36:00')");
	   stmt.executeUpdate("INSERT INTO animal VALUES (4, 1, 'Eddie', '2010-06-08 01:24:00')");
	   stmt.executeUpdate("INSERT INTO animal VALUES (5, 2, 'Zoe', '2005-11-12 03:44:00')");
			
	   ResultSet rs = stmt.executeQuery("select count(*) from animal");
	   rs.next();
	   System.out.println(rs.getInt(1));
      }
   }
}

Then compile as usual:

javac SetupDerbyDatabase.java

Running it is a bit different as you need to include the Derby jar file in your classpath. If you don’t know how to find it or encounter problems see the next sections of this blog post. Notice the classpath contains the following three things:

  1. The relative or absolute path of the Derby jar file
  2. A separator (semicolon on Windows, colon on Mac/Linux)
  3. A dot (which means the current directory)

For example, on my Mac either of these works:

java -cp "$JAVA_HOME/db/lib/derby.jar:." SetupDerbyDatabase
java -cp "/Library/Java/JavaVirtualMachines/jdk1.8.0_45.jdk/Contents/Home/db/lib/derby.jar:." SetupDerbyDatabase

If all goes well, the program will output the number 5.

Alternatively, you could have added Derby to your CLASSPATH environment variable and just run the program as

java SetupDerbyDatabase

How do I set up the classpath to run the Java program?

If you know where the JDK ($JAVA_HOME) is on your computer, you can start there and then look in the db/lib directory to find the derby.jar file. The most likely location for the JDK install is:

Operating System Most likely location
Windows c:\program files or c:\program files (x86)
Mac /Library/Java/JavaVirtualMachinges
Linux /usr

Or you can search for derby.jar to get the exact path. On Mac and Linux, the search command is:

find / -name derby.jar -print 2> /dev/null

What does this program actually do?

The main method starts out by obtaining a connection to the Derby database. It then creates a statement object so it can run updates. it would have been more efficient to use a PreparedStatement, but those aren’t on the exam. We aren’t taking user input here so there is no security risk with SQL Injection.

Then the code runs two SQL statements to create tables in the zoo database. The commands each include:

  • the table name – species and animal
  • the fields in each table along with their type. Integer is like a Java int. Decimal is like a Java double. Timestamp is like a Java LocalDateTime or old java Date. Varchar stands for variable character and is like a String. The variable length part means that the database doesn’t need to allocate space for all 255 characters and should only use the space for the actual length of the string. (This matters when you frequently update the field with values of different lengths)
  • the primary key for each table – this tells the database how to you uniquely identify each row

Then the code runs seven SQL statements to insert rows into these tables. The order of the data matches the order the fields were defined in the create statements.

Finally, the code runs a query to check the rows were added to the database. The count(*) function in SQL always returns a number. For an empty table, this number is zero. Therefore, we can call rs.next() outside of a conditional or loop. We know there is always a number being returned.

Derby will create a “zoo” directory and a derby.log file in whatever directory you ran the program in. The zoo directory is your database.

Frequently Encountered Problems

If you have an error that isn’t here or have trouble with these instructions, feel free to ask a question in the CodeRanch forums

Error #1 – Derby is not in your classpath or points to an invalid location

Exact error message:

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:derby:zoo;create=true

at java.sql.DriverManager.getConnection(DriverManager.java:689)

at java.sql.DriverManager.getConnection(DriverManager.java:270)

at derby.SetupDerbyDatabase.main(SetupDerbyDatabase.java:9)

Solution:

Check you are actually pointing to Derby in your classpath. Also check your classpath has the three required components

Error #2 – The current directory is not in your classpath

Exact error message:

Could not find or load main class derby.SetupDerbyDatabase

Solution:

Check you have the current directory (dot) in your classpath. Also, check you have the correct separator for your operating system (semicolon for Windows, colon for Mac/Linux).

Error #3 – The tables already exist

Exact error message:


Exception in thread "main" java.sql.SQLException: Table/View 'SPECIES' already exists in Schema 'APP'.

at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)

at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)

Solution:

The program can only be run once as is. If you want to run it again, uncomment the two “drop table” lines.

I can’t find derby.jar in my Java install directory?

Make sure you are looking at your JDK and not a JRE. The JRE has less things included. For example, it doesn’t have the javac command. And it doesn’t have Derby.