Sakila Sample Database for H2

Introduction

This project is a recreation for the H2 database engine of the Sakila sample database that was originally created for MySQL.

Project contents

sakila.sql

Currently, the sakila.sql file contains the code to recreate the Sakila database schema and populate the database with sample data. The file was created using the files sakila-schema.sql and sakila-data.sql. These files are available in the sakila-db archive downloadable from the MySQL website and were licensed under the new BSD license.

sakila.mv.db

The sakila.mv.db file is the result of executing the sakila.sql script from above. Thus contains all the tables and data for the Sakila sample database.

runh2.sh

runh2.sh is a convenience shell script that can be used to run the H2 Sakila Sample database from a command shell. The H2 engine will be started in server mode. The script uses the sakila.mv.db above and the H2 jar file below.

h2-<version>.jar

h2-<version>.jar is the executable jar file to run the H2 database engine. It also contains the JDBC drivers needed to connect to the database from your Java applications. It is used by the runh2.sh script above. The H2 database engine is dual licensed under the Mozilla Public License Version 2.0 and the Eclipse Public License Version 1.0.

README.md

This file

How to use

Server mode

You can use the runh2.sh shell script to start the database in server mode. Just issue ./runh2.sh from a command line window.

me@machine sakila-h2 % ./runh2.sh 
Starting h2 server - jdbc url: jdbc:h2:tcp://localhost/./sakila
TCP server running at tcp://localhost:9092 (only local connections)
Web Console server running at http://localhost:8082 (only local connections)

Now you can connect to it from Java code using JDBC, e.g. as follows:

Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/./sakila", "sa", "");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from ACTOR");
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
int columnCount = resultSetMetaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
    System.out.println("Column " + i + " -> " + resultSetMetaData.getColumnName(i));
}

Embedded Mode

In this case you just use the path to the folder containing thesakila.mv.db file in the JDBC and append sakila as this is the name of the database to your connection string. Assuming this file is for instance located in the folder called Databases under your home directory, the code above would look as follows:

Connection connection = DriverManager.getConnection("jdbc:h2:~/Databases/sakila", "sa", "");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from ACTOR");
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
int columnCount = resultSetMetaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
    System.out.println("Column " + i + " -> " + resultSetMetaData.getColumnName(i));
}

More Options

Since the database is a H2 database, all the documentation available from the H2 website is valid.

Database Structure and Contents

Full documentation about the structure and the contents of the Sakila Sample Database can be found on the MySQL Sakila Database website.