[JPA Setup] Deploying a Database for JEE Environment

The following post shows you:

  • Installing and starting a local database (HSQLDB)
  • Creating a datasource in the Wildfly application server

It is a pre-requisite to running the example JPA application that you will be asked to modify, and that will demonstrate in practice JPA theory in the JPA introduction I and II.

Installing and starting database

For my example, I chose the Hypersonic SQL Database (HSQLDB). It is very easy to install, start, and like most production relational databases, it is exposed on a particular URL, so that multiple applications or users can connect to it. To prepare a database instance:

  1. Download the latest HSQLDB zip file. At the time of writing, it is hsqldb-2.4.0.zip.
  2. Unzip the file into a directory of your choosing. This will be referred to as ${HSQLDB_HOME}.
  3. In a command line, change into the ${HSQLDB_HOME}/hsql/data directory, for example: cd c:/hsqldb-2.4.0/hsql/data
  4. Execute the following command:
    java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 file:bookDbFile -dbname.0 bookDb
    The command starts a database with the following parameters:

    1. Database is exposed on localhost:9001
    2. The database is named bookDb. The example application requires bookDb to be present.
    3. The user sa is given admin privileges to the bookDb database

You should see something similar to the following:

You can stop the database by issuing Ctrl+c. However, when deploying our example project, the database must be running at all times. Leave the database running for the rest of this article.

Creating Wildfly datasource

Now that we have a database server up and running, we want to create a managed datasource such that the application server manages the connection for us. To do so:

  1. In the command line, change into the Wildfly bin directory, for example: cd c:/wildfly-11.0.0.Final/bin
  2. Add an administrative user:
    On Windows: add-user.bat -u halfastack -p secr3t -g admin
    On Unix: ./add-user.sh -u halfastack -p secr3t -g admin
    You should see something similar:
  3. Deploy the HSQLDB driver jar file:
    1. Locate the ${HSQLDB_HOME}/lib/hsqldb.jar driver.
    2. Copy the jar driver  into the Wildfly standalone deployment directory: ${WILDFLY_HOME}/standalone/deployments/.
  4. Start the server. To start the server, in a command line, issue the following command:
    On Unix: ${WILDFLY_HOME}/bin/standalone.sh
    On Windows: ${WILDFLY_HOME}\bin\standalone.bat
    If you deployed the driver correctly, you should see:
  5. In a browser, access the management console: localhost:9990 and enter credentials you created earlier:
    Username: halfastack
    Password: secr3t
  6. Click configuration -> subsystems -> datasources -> non-XA -> Add. The Create Datasource dialogue opens.
  7. Select Custom datasource and click Next.
  8. Enter the name and JDNI:
    Name: BookDB
    JNDI: java:/jboss/BookDB
    Note that it is important you make the JNDI ID the same as in this tutorial. Otherwise, the JPA application will not work.
  9. In the Step 2/3: JDBC Driver window, click Detected Driver, select hsqldb.jar and click Next. In case you do not see the jar here, you did not deploy the hsqldb.jar driver properly. Return to step 3. Alternatively, you can also consult the JBoss EAP documentation, which shows different ways to deploy JDBC drivers.
  10. In the Connection Settings step, enter the following:
    Connection URL: jdbc:hsqldb:hsql://localhost/bookDb
    Username: sa
    Leave the rest empty and click Next.
  11. Test the connection to see whether all is configured correctly. You should see the following:
    Successful JDBC ping when creating Wildfly Datasource
  12.  Click OK, Next, and Finish. If asked, click Reload server now.

You have successfully started your own database server, deployed a driver to your Wildfly application server, and connected the two.

Summary

In this article, you have learned how to:

  • Create a database server
  • Deploy a JDBC driver on your Java application server (Wildfly in our case)
  • Create a managed datasource

In the next article, you will learn how to use this datasource in your application.

2 thoughts on “[JPA Setup] Deploying a Database for JEE Environment

Leave a Reply