GWT Database Setup

December 5, 2013 Maithilish

Chapter 8. Setup Database

It’s time to say goodbye to mock datastore SymbolDatabase.
This section explains the GWT Database setup. Any RDBMS accessible through JDBC should do, and we choose HSQLDB for its configuration simplicity. HSQLDB, a full fledged RDBMS, is easy to setup and requires almost zero configuration. Further, HSQLDB plugin for Eclipse makes the process even more simpler.
Once database is up, we require to load it with data. For that, we are going to use a small java application, RStore. Instead of tinkering with Fins, a separate application makes it easier to understand database handling and Object Relational Mapping (ORM). In the next part, when we move on to Google App Engine(GAE), we merge RStore with Fins to push the data to App Engine’s High Replication DataStore (HRD).
RStore is a standalone Java application which parse an XML input file and constructs fully loaded Symbol objects. It uses Java Data Objects (JDO) to create database schema and persists the objects to database. Once objects are persisted, it retrieves Symbol object from the database via JDO, Hibernate and MyBatis. Even though JDO is preferred ORM for Fins, we use Hibernate and MyBatis just to show the flexibility offered by ORM. For newbies, RStore serves as a gentle introduction to the world of ORM.

8.1. Install DB

There is a couple of ways to install HSQLDB; as Eclipse plugin or as standalone database. Plugin makes it easy to install and setup. It also provides menu option to start/stop database and invoke Database Manager GUI. These features make ones life easy during development.
HSQLDB Database Server Plugin

Select HelpEclipse MarketPlace… and in Eclipse Marketplace dialog, enter HSQLDB in Search tab to search for the plugin. Select HSQLDB Database Server Plugin from search result.

After installation, choose WindowShow ViewOthers and in Show View dialog expand HSQL Database server and select HSQLDB Server to open the HSQLDB view. This view has menu options to set database preferences, start/stop database and invoke Database client to interact with the database.
First, set preferences which is used later to configure JDBC driver. Click on View Menu and select Preferences from the drop down menu. Enter values shown in the Figure.
HSQLDB Preferences
Figure 8.1. HSQLDB Preferences

With these preferences, HSQLDB creates database file named db and database alias finsdb and user sa. Ensure that Persist database between session check box is checked.
After preferences is set, start the database with Start icon in the HSQLDB view. Once database is up and running, open database manager by selecting Show Database Client from drop down menu.
Database Client
Figure 8.2. Database Client

The left pane shows database schema, right hand top pane to enter the SQL and right hand bottom pane shows the query result. Use Refresh option in View menu to refresh schema. Refer HSQLDB documentation to know more about Database Manager.
Manual Installation

For whatever reasons, if you prefer to run HSQLDB outside the Eclipse then you may do so by downloading the HSQLDB package from SourceForge .

Extract the zip file to some location and use following commands to start the database.
$ cd <hsqldb install directory>

$ mkdir database     ## dir to hold data files

$ java -cp lib/hsqldb.jar org.hsqldb.server.Server      /
       --database.0 file:database/db                    /
       --dbname.0 finsdb
This command runs HSQLDB in server mode and options --database.0 file:database/db indicates that for database instance 0, use database/db as datafile and --dbname.0 finsdb indicates that for database instance 0, use alias finsdb to access database.
Use following command to invoke Database Manger GUI.
$ cd <hsqldb install directory>
$ java -cp lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing
With HSQLDB up and running, we are ready to load it with the data.