We will start by creating a JDBC Connection (API documentation) to our example database. Download the following database files and put them in your Eclipse project directory:

• film.backup
• film.script
• film.properties
• film.data

These files make up a HSQLDB format database. As described in the tutorial above, you are also going to need a HSQLDB JDBC driver. This is the hsqldb.jar library within the HSQLDB distribution, downloadable here. Put the library in the build path of your project.

Create a class FilmDatabase in package jdbc with a static public method getDatabaseConnection() which returns a JDBC Connection instance to the example database.

Add a static method to retrieve the names of all the PUBLIC schema tables from the database, with the following method signature:

public static String[] listAllTables(Connection connection)

Executing this method with the example database connection should give the following result:

{ "Actor", "Film", "Director", "PerformsIn" }

As a hint, you may wish to investigate the getMetaData() method of the Connection instance, and the tables you are looking for are all part of the schema PUBLIC.

Create a method listAllColumnsForTable with the following signature:

public static String[] listAllColumnsForTable(Connection connection, String tableName)

When given a JDBC Connection and a table name, the method returns the table columns. For example, using the example database and the table name Film, the method returns:

{ "filmId", "title", "year", "director Id" }

Create a method getTableData with the following signature:

public static String[][] getTableData(Connection connection, String tableName, String[] columnNames)

When given a JDBC Connection and a table name, the method returns a two dimensional array of the table data for the columns requested. For example, using the example database, the table name Film, and the columns [filmId, title, year], the method returns:

{ "BAT92", "Batman Returns", "1992",}, 
{ "DK008", "The Dark Knight", "2008", },
{ "DKR12", "The Dark Knight Rises", "2012", }, 
...

Finally, create a method viewTable with the following signature:

public static String viewTable(Connection connection, String tableName)

This method returns a dump of the named table, comma delimited between the fields of each row of the table. You may use the methods you have created above. For example, using the example database and the table name Film, the method returns:

filmId, title, year, directorId
BAT92, Batman Returns, 1992, BUR34
DK008, The Dark Knight, 2008, CN345
DKR12, The Dark Knight Rises, 2012, CN345
...

For the exercises below, please use PreparedStatements (API documentation) for the database access. Normally these are used when the same query is repreated lots of times, which we are not doing, but it is still better knowing how to use them.

Create a method getActorById with the following signature:

public static String[] getActorById(Connection connection, String actorId)

When given a JDBC Connection and an actor id, the method returns the fields of the Actor table for that record. If the actorId is not found, return null. For example, using the example database and the actorId EMG32, the method returns:

{"EMG32", "Ewan McGregor", "British", "43"}

Create a method addNewActor with the following signature:

public static boolean addNewActor(Connection connection, String actorId, String actorName, String nationality, int age)

When called, the method adds a new row to the example database table Actor with the supplied fields. If successful, return true. If there was a failure, including trying to add an already existing actorId, return false.