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

Main menu:

Topics

Recent Posts

Feeds

RSS Feed RSS - Posts

September 2015
M T W T F S S
« Aug   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  

Past Posts

Java/Java EE

JDBC

Other

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

September 12th, 2015 by Jeanne Boyarsky

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.

Comments

Comment from Jeanne Boyarsky
Posted: June 12, 2016 at 11:55 am

John points out you might prefer adding derby to your bash_profile on Mac rather than using the command line classpath flag: http://www.coderanch.com/t/666680/JDBC/databases/Setting-Classpath-Derby-OS

Comment from dj
Posted: October 19, 2016 at 11:10 pm

If I got this correct, Derby won’t come with the JDK in the future (6/2015), but be a separate install from Apache.
https://blogs.oracle.com/java-platform-group/entry/deferring_to_derby_in_jdk

Comment from Jeanne Boyarsky
Posted: October 22, 2016 at 8:57 am

DJ: True. That’s for Java 9 though, not future versions of Java 8. And since this page is designed to go with Java 8….

Comment from Tahir Abdullayev
Posted: May 11, 2017 at 6:48 am

The code in the book doesn’t work. The seconds are not included in timestamp and because of this Java gives exception while running the code. I think this should be included in errata list. I’ve spent 1 hour trying to figure out what’s wrong with this code. I’ve found this page after already solving it myself.

Comment from Jeanne Boyarsky
Posted: May 12, 2017 at 9:59 pm

Note: this post is linked from the errata page.

Comment from Tahir Abdullayev
Posted: May 13, 2017 at 4:36 am

It’s in the ‘bonus’ link, not errata.
How often would people look for ‘bonus’ if they found an error in the book?
Not me, anyway. I was always checking list of errors in the book because I found many, but I didn’t find anything for this error and moved to google to find what the problem is.
Also, why do you need a numbered list of errors in the book if you describe some errors somewhere else and call it not errors, but ‘bonus’?

Comment from Jeanne Boyarsky
Posted: May 13, 2017 at 8:17 pm

I just added it to the errata page. The reason I saw a distinction was that the book directs to the “bonus” blog post for updates. (The bonus page existed before this errata was known. We anticipated changes in db setup). So I thought people might look there already. But you are correct. That’s a philosophical point.

Comment from Antonio
Posted: May 26, 2017 at 5:52 am

if someone is interested to know how it works with Eclipse-Mars on MAC: right-click in Eclipse on the src folder containing SetupDerbyDatabase file, then click on “Build path”>”Configure Build Path…”, select “Libraries” and click on “Add External JARs…”, search for derby.jar in your jdk library and apply.

Comment from Jeanne Boyarsky
Posted: July 2, 2017 at 8:10 am

Changed decimal to decimal(1,1) per https://coderanch.com/t/681668/certification/Mistake-OCP-Java-SE-Programmer

Comment from Joel Fuhrmann
Posted: August 1, 2017 at 6:11 pm

I got an exception related to the DECIMAL(1,1), and I looked it up on Stack Overflow and the post there said the first digit has to contain the total number of digits in the number, and the second digit contains the number after the decimal poiint. So to store a number like 7.5, the DECIMAL statement should be changed to DECIMAL(2,1). So I ran it like that and it ran with no exceptions.

Comment from Jeanne Boyarsky
Posted: August 19, 2017 at 12:35 pm

Note: these instructions assume you are using JDK 8 since this is for the Java 8 exam. We do not recommend studying for the Java 8 exam using Java 9. Among other things, Derby is not packaged with the JDK in Java 9)

Comment from Filip
Posted: August 21, 2018 at 6:00 am

I typed the code for setting up Derby database. As I tried to compile the code I got the following error:

An exception has occurred in the compiler (1.8.0_161). Please file a bug against the Java compiler via the Java bug reporting page (http://bugreport.java.com) after checking the Bug Database (http://bugs.java.com) for duplicates. Include your program and the following diagnostic in your report. Thank you.
java.lang.NoClassDefFoundError: com/sun/tools/javac/comp/CompileStates$CompileState
at com.sun.tools.javac.main.JavaCompiler.(JavaCompiler.java:428)
at com.sun.tools.javac.main.JavaCompiler.instance(JavaCompiler.java:88)
at com.sun.tools.javac.main.Main.compile(Main.java:485)
at com.sun.tools.javac.main.Main.compile(Main.java:381)
at com.sun.tools.javac.main.Main.compile(Main.java:370)
at com.sun.tools.javac.main.Main.compile(Main.java:361)
at com.sun.tools.javac.Main.compile(Main.java:56)
at com.sun.tools.javac.Main.main(Main.java:42)
Caused by: java.lang.ClassNotFoundException: com.sun.tools.javac.comp.CompileStates$CompileState
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:338)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
… 8 more

…….So my java file did not get compiled. But not only that also my other .java files (simple practice programs) which used to compile fine , are now not compiling and are causing the same error.
Sorry for this verbose comment but I am trying to solve this. Everything I found on the net so far was not very helpful.
Please for some helpful advices!!! Thanks

Comment from Jeanne Boyarsky
Posted: August 25, 2018 at 1:45 pm

Can post this at https://coderanch.com/f/33/java? CodeRanch is a big Java forum so you’d get people looking at it quickly. (It’s hard to answer long questions in the comments of a blog)

Write a comment