LoadFortune Program
The LoadFortune application loads fortunes from a cookie file into the fortunes table. It demonstrates how to execute SELECT and INSERT statements. This section highlights some of the code.
Source Code Location
FORTUNE_HOME/java/LoadData/LoadFortune.java provides the source code for the LoadFortune application. You might want to open that file with an editor to see more context for the snippets of code described below.
Application Overview
The LoadFortune program loads a cookie file into the fortunes table, assigning each a unique integer id.
The application takes two arguments: the name of the database and the name of the cookie file. The syntax is shown below:
java -Dderby.system.home=$FORTUNE_HOME LoadFortune database file
It looks for the cookie file in ../../data.
The basic application flow is:
- Connect to the Derby database specified in the first argument.
- Fetch the max id from the fortunes table, then increment it by one. That produces the starting id number for the new insert.
- Open the cookie file specified by the second argument and read it until a % is found on a line by itself -- this identifies the end of the fortune.
- For each fortune it finds, it inserts it into the fortunes table along with a unique id and the name of the cookie file. It preserves the ASCII format of the fortunes it loads by retaining the new lines. The id is incremented by one for each fortune inserted.
Select max id
The JDBC code for fetching the maximum id from the fortunes table is quite simple. We create a statement, execute a query entered as a simple string, then get the result. The code is shown below:
// Get the max id int maxId=0; Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("select max(id) from fortunes"); if(rs.next()) maxId = rs.getInt(1); rs.close(); s.close();
Insert fortune
Since a cookie file could contain many fortunes, we would like the code to be as efficient. We do that by preparing a statement we can reuse for each fortune:
PreparedStatement ps = conn.prepareStatement ("insert into fortunes (id, src, fortune) values (?, ?, ?)");
By preparing a statement with parameter markers (those ? marks), we can avoid the overhead of compiling the query for each insert. The query gets compiled just once and just the parameter values change for each insert.
As the lines for a given fortune are read into a buffer, the newline is added back in to retain the ASCII format of the fortune, as the code snippet below shows:
String inString = fortuneFile.readLine(); while (inString != null) { String thisFortune = new String(""); // Read lines until we reach a % terminator. // Retain the original line feeds. while (inString.equals("%") == false) { thisFortune = thisFortune + inString + "\n"; inString = fortuneFile.readLine(); }
Once the fortune is read into the buffer, we set the value for each parameter marker, then execute the statement that inserts it:
maxId++; ps.setInt(1, maxId); ps.setString(2, cookieFile); ps.setString(3, insertString); rowsAdded = ps.executeUpdate(); // Get next record. inString = fortuneFile.readLine(); }
If performance were an issue, we could turn auto-commit off be executing conn.setAutoCommit(false) at the start of the program, then periodically commit by executing conn.commit().
Building
The LoadFortune application just requires derby.jar in the class path to build (for the embedded JDBC driver):
javac LoadFortune.java
Disclaimer
The LoadFortune application is demo quality code. It isn't robust when processing a malformed cookie file. In particular, it's persnickety about each fortune being terminated by a % on its own line. Some fortune files omit the final %.