In the book most examples use one or more XML files as data input. For the examples in this Wiki, I chose to work with real databases and database tables. Of course, I want you to be able to install and run these databases; regardless of the level of your technical skills, regardless of the Operating System you’re working on. To make this possible, I chose for a light-weight database system, that can easily be installed: hsqldb.

Because the samples on this site are all small standalone examples, you’ll be using hsqldb in standalone mode. This mode runs the database engine as part of your application program in the same Java Virtual Machine.
In practice: all you need to install and run this database is the hsqldb.jar in your external libraries directory. The databases are just some files in the databases directory.
For those who want to try the examples with their own database, I have also provided the sql scripts that can be fed to (almost) any database (I tried to use simple SQL syntax; the scripts should work on most database servers).

The database connection is made through JDBC (with org.hsqldb.jdbcDriver as database driver). You don’t need to know much about JDBC to be able to work with the sample databases: I made a simple abstract wrapper class com.lowagie.database.HSQLDataBase. The class is designed to work in small standalone examples.

DO NOT USE THIS CLASS IN A PRODUCTION ENVIRONMENT! It’s meant for examples only!

Let’s give it a try.

Class com.lowagie.database.HSQLDataBase is abstract; you can’t create an instance of the class. We need a specific implementation, for instance com.lowagie.filmfestival.database.FilmfestivalDatabase. This class extends HSQLDatabase and it has some interesting extra functionality.

Starting and stopping the database, opening and closing a database, it’s not always trivial, but I made it very easy:

FilmfestivalDatabase database = new FilmfestivalDatabase();
try {
  database.open();
  // do stuff
} catch (SQLException e) {
  e.printStackTrace();
}
database.close();

First you create a specific database instance; this sets the connection string, username and password of the HSQLDataBase class.
Then you open the database. I have thought about the best way to name this method. The open() method does several things at the same time: it starts the database server and it opens a database connection. In some cases it also prepares and even executes some queries to initialize static values; values we’re going to need in our examples.
The close() method actually shuts down the database server, before closing the database connection and setting it to null.
I could have named these methods start() and stop(), init() and destroy(),... but I opted for open() and close(). You needn’t worry about all this. Just make sure that you always open() the database before performing any query, and close() it before you exit your standalone program.

Performing a query is very simple because of the method executeQuery.

FilmfestivalDatabase database = new FilmfestivalDatabase();
try {
  database.open();
  ResultSet rs = database.executeQuery("SELECT title FROM film_titles ORDER BY title");
  while (rs.next()) {
    System.out.println(rs.getString("title"));
  }
} catch (SQLException e) {
  e.printStackTrace();
}
database.close();

The result of this query looks like this:

2001: A Space Odyssey
4:30
ACE-Competitie voor Vlaamse Studentenkortfilms
Agua
Ahlaam
All the Invisible Children
...

You can test this code by running com.lowagie.filmfestival.test.FilmfestivalDatabaseTest1.

If you don’t like to be confronted with SQL, you’re in luck. I already prepared the queries that you’ll need. When you called database.open(), the SQL was loaded into static java.sql.PreparedStatement objects. You can use these prepared statements without bothering about the SQL that is behind. You only need to know which parameters to pass to the statement.
For example: if you need the names of the directors of the movie All the Invisible Children (a movie with id=1200 in the database), you can get the ResultSet like this:

FilmfestivalDatabase database = new FilmfestivalDatabase();
try {
  database.open();
  PreparedStatement statement = FilmfestivalDatabase.directors;
  statement.setInt(1, 1200);
  ResultSet rs = statement.executeQuery();
  while (rs.next()) {
    System.out.println(rs.getString("name"));
  }
} catch (SQLException e) {
  e.printStackTrace();
}
database.close();

The result looks like this:

Charef, Mehdi
Kusturica, Emir
Lee, Spike
Lund, Kátia
Scott, Jordan
Scott, Ridley
Veneruso, Stefano
Woo, John

(This movie is a selection of shortfilms for UNICEF about children all over the world.) You can test this code by compiling and executing com.lowagie.filmfestival.test.FilmfestivalDatabaseTest2.

Whenever it gets to difficult, you get some helper classes. If you want to perform some specific queries on the filmfestival database, you’ll find a class com.lowagie.filmfestival.database.FilmfestivalQuery that implements all the business logic to construct objects of type com.lowagie.filmfestival.database.FilmfestivalMovie and -FilmfestivalScreening. These classes contain all the information on the movies and the date/time/place when they are screened.
Printing a complete overview of all the screening dates and the movies that are screened on each of these days is as simple as this:

FilmfestivalDatabase database = new FilmfestivalDatabase();
try {
  database.open();
  FilmfestivalQuery q = new FilmfestivalQuery(
    FilmfestivalDatabase.getDays(),
    false,  // onlyPress
    false,  // noPress
    false,  // comp
    false,  // eXploreZone
    false); // alter
    FilmfestivalMovie movie;
  for (int i = 0; i < q.getNumberOfDays(); i++) {
    System.out.println(q.getDay(i));
    for (Iterator iterator = q.getMovies(i).iterator(); iterator.hasNext(); ) {
      movie = (FilmfestivalMovie)iterator.next();
      System.out.println(movie.getTitle());
    }
  }
} catch (SQLException e) {
  e.printStackTrace();
}
database.close();

The static method getDays() gets all the dates with movie screenings during the filmfestival. This is an example of a query that was already executed when invoking the open() method.
The onlyPress/noPress booleans indicate if the list should only contain press screening, or completely omit them. The comp/eXploreZone booleans indicate if the list should only contains films selected for the official filmfestival competition or the eXploreZone Award. By setting alter to true, you indicate that you want every movie object to know its alternative screenings (screenings on other days/times/places than the current day/time/place).
Depending on the parameters passed to the constructor of FilmfestivalQuery a different SQL statement is used, but you don’t need to worry about that.
The result of our small test program looks like this:

2006-10-06
Windkracht 10
2006-10-10
Metal: A Headbanger´s Journey
Percy, Buffalo Bill och jag
Laberinto del Fauno, El
Perfume: The Story of a Murderer
Queen, The
Children of Men
...

You can test this code by compiling and executing com.lowagie.filmfestival.test.FilmfestivalDatabaseTest3.

The data that comes with the examples is test data. If for some reason you want to replace this data with data of your own, you can do so, but this could lead to odd results. The following examples will explain how to remove the database, create the tables, and fill them with data.

Deleting one of the sample databases from your system is easy: just remove the database files from the databases folder. For instance: if you want to remove the filmfestival database, you need to remove these files (if present): * filmfestival.lck (this file will only be there if you didn’t close the database properly) * filmfestival.log (this file will only be there if you didn’t close the database properly) * filmfestival.properties * filmfestival.script (this file has the actual data) If you remove all these files, the filmfestival database is gone!

Suppose you have changed the database files mentioned about, and you want to start anew with the original database that shipped with the examples. Or suppose you want to create a new examples, with your own database.
In this case have to use or create a file sample_tables.sql and put it in the sqlscripts directory. You can create the database like this:

String path = Examples.properties.getProperty("sqlscripts", "sqlscripts/");
FilmfestivalDatabase database = new FilmfestivalDatabase();
try {
  database.create(path + "filmfestival_tables.sql", null);
} catch (Exception e) {
  e.printStackTrace();
} catch (IOException e) {
  e.printStackTrace();
}
database.close();

Now we have created the database, but it doesn’t contain any data. We could feed the database tables like this:

String path = Examples.properties.getProperty("sqlscripts", "sqlscripts/");
FilmfestivalDatabase database = new FilmfestivalDatabase();
try {
  BufferedReader reader;
  reader = new BufferedReader(new InputStreamReader(
    new FileInputStream(path + "filmfestival_data.sql")));
  HSQLDataBase.feed(database, reader);
} catch (SQLException e) {
  e.printStackTrace();
} catch (IOException e) {
  e.printStackTrace();
}
database.close();

This is how it’s done in example FilmfestivalDatabaseTest4, but it more elegant to to it in one go by passing the sql for the tables and the sql for the data like this code snippet taken from FilmfestivalDatabaseTest5:

String path = Examples.properties.getProperty("sqlscripts", "sqlscripts/");
FilmfestivalDatabase database = new FilmfestivalDatabase();
try {
  database.create(path + "filmfestival_tables.sql", path + "filmfestival_data.sql");
} catch (SQLException e) {
  e.printStackTrace();
} catch (IOException e) {
  e.printStackTrace();
}
database.close()

Note that you don’t open the database connection when using create(). This doesn’t mean you don’t have to close() the database! Also take into account that the create() method doesn’t prepare the statements, nor does it execute any of the queries discussed in the previous section.

The sample databases are created with hsqldb, and you are provided with different classes that will help you perform the queries needed to publish these databases to a report in PDF. When we discussed the filmfestival database, you were introduced to the most common functionality. For the sake of completeness, we also discussed some extra functionality to create and fill a database. Now it’s time to actually use a sample database to create a PDF document.