Creating a Database Connection

In this Lesson

You will learn what a Soiree Database Connection is and how it is used.

Concepts

The Soiree Database Connection item (or simply Connection) describes a connection between Soiree and a relational database and it contains all the usual suspects that you would expect to find in that type of animal – and a little more.

Before you create a connection let’s discuss their silhouette

  1. A connection, like all Soiree items, is known by its item ID (package + name).
  2. Each connection is created for a specific purpose.
    You will generally need only one connection for your solution. However, there are times when you may want more than one connection. You may need to connect to more than one database server simultaneously or you may want to provide unit of work separation. In our Party project there are two Derby databases (demo and acme). In later lessons you will be creating a connection to both databases and use them together in a single transaction. It’s like inviting multiple people to your party and having them all get along beautifully.

    Here are the connections that Soiree uses for its tables




    com.seronix.soiree.connection.System is used by all of the SQL statements that access the Soiree system tables. By using a separate connection ID we have the option to place these tables in the same database as the solution or in a separate one. We can make that decision at deployment time and may also select different rules for unit of work control than what the solutions are using.

    com.seronix.soiree.connection.ConsoleJournal is used by the performance monitor instrumentation in SxServer. The performance monitor uses SQL statements to log performance information to database tables and those statements use this connection. Using a separate connection ID allows us to write the performance metrics to a separate database server if we want physical separation. Additionally, it is configured to use autocommit which ensures all metrics are logged regardless of what commit/rollback strategies are being used by the solution.
  3. A connection may contain multiple sets of connection parameters.
    This is a very convenient capability for team development. Imagine you are working on a team that is building an event promotion solution and there is a database server that you are all sharing. You may want to use a private database server to test some things out before you add them to the team database. So, let’s say you have a connection named com.hotstuff.promotion.PrimaryConnection which contains connection parameters for the team database server. You could add a second set of parameters to the connection item that would point to your private database. Because the connection item allows you to specify which set of parameters is ‘active’ you can easily switch between the team and local database by simply changing the ‘active’ settings and restarting the server. [ If this saves you too much time you could always slow yourself down by retyping all the connection parameters each time you want to switch databases. ]
  4. The connection definitions can be deployed as Soiree resource files or as XML files. We will talk more about that after you have created a database connection item.

Create a Database Connection

    • Right click on the src folder and create a package for database connections named com.example.party.connection
      Tip
      Save yourself some typing. Copy the package name out of the lesson and paste it into the New Java Package window.






  1. Open the Database Connection wizard
    • Left click on the com.example.party.connection package to select it and then press Command + N (OS X) or CTRL + N (Win) and select Database Connection from the Soiree section.



    • Enter the following information
      Package Name: should already contain com.example.party.connection
      Name: Demo
      Description: Demo database



      Check out the default
      The package name in the Soiree item wizard defaults to the package that is selected before the wizard opens.
    • Click Finish

      You will be presented with the Connection editor as shown here.



      Notice the error marker by some of the fields






  2. Provide the database connection in the Connection Settings section as shown here.



  3. Save the connection definition by pressing Command + S (OS X) or CTRL + S (Win) or by selecting File … Save from the menu.
  4. Test the connection (and watch it fail if the database is not running)



  5. Start the Derby database server



    You should see some output in the Java Console



  6. Test the connection (and watch it succeed)



  7. Save the item. This connection is ready to go!

Multiple Connection Settings

This exercise will illustrate how you can have more than one set of connection settings contained in a single Database Connection item.

  1. Click on the + tab to create a new settings window



    Give the connections settings a name. These settings could be used to attach to a database that is shared by your team. The database could be the same type as your Private database or it could be a database from another vendor (as long as you did not write your SQL statements in a vendor specific way).



  2. Selecting the active connection settings
    When a connection item contains more than one connection setting you have the option of selecting which of those settings is active.





  3. If there is more than one settings tab in the item you may remove one of them by clicking the X on the tab you want to remove.



Database Connection Deployment

Database connections are used at runtime by Java classes running in a JVM.

There are two ways to provide database connections to the JVM.

  1. As Soiree resource files

    This method is provided for the convenience of the development server that runs in Eclipse, however, this method could also be used for any SxServer running outside of Eclipse if that is your preference.

    You may recall that most Soiree item files are translated into Soiree resource files by the Soiree Item Builder and stored in the project’s sxres folder. The database connection resource files stored in this folder can be passed directly to the JVM as follows:

    When the server JVM is started…

    1. Pass this system parameter to the JVM: -DUSE_RESOURCES_FOR_DB
      The USE_RESOURCES_FOR_DB parameter instructs Soiree to look for database connection resource files on the java classpath.
    2. Add the sxres folder to the Java classpath used by the server JVM.
    Hear ye, Hear Ye
    The ACTIVE connection settings in the resource file determines which database will be used by the JVM.
  2. As XML files
    This method is recommended for any SxServer running outside of Eclipse.

    Database connection items have a capability that most other items do not possess. This capability is not quite at the level of being a superpower but it has been known to help server administrators leap medium height obstacles with less effort.

    Here’s the scoop…

    Soiree item and resource files are handy while you are in Eclipse, however, they are not your best friend if you are working outside of Eclipse. Server administrators are better served with text files they can easily modify. This is where the almost-superpower enters the stage.

    The active database connection setting can be exported to an XML file by pressing this button



    The exported XML files are provided to the JVM in the following manner

    1. Pass this system parameter to the JVM: -DSOIREE_HOME=”/some/folder”.
      SOIREE_HOME specifies the location of the server configuration folder. The server configuration contains many things – and one of those things is XML configuration files for database connections.
    2. Copy the XML files to the config-db folder
      The database connection XML files must be located in a sub-folder of SOIREE_HOME named config-db.
      For Example: If you pass -DSOIREE_HOME=”/some/folder” to the server JVM then Soiree looks for the XML connection files in /some/folder/config-db.
    3. Add a file named dbconnection.map to the config-db folder
      When using XML files you must specify which XML file is to be used for each connection ID that your solution needs. You do this by adding a text file named dbconnection.map to the config-db folder. The dbconnection.map file may contain multiple lines and each line describes a single mapping in the form
      connectionId=XMLfile

      For example:

      
      com.seronix.soiree.connection.ConsoleJournal=mysql-prod-monitor-log.xml
      com.seronix.soiree.connection.System=derby-prod.xml
      com.example.party.connection.Demo=derby-prod.xml
      
    4. Notice
      You may use the same XML file for more than one connection ID.

    Prepare the Soiree connection IDs

    In preparation for the lessons that follow you will need to configure the two Soiree database connections.

    1. Prepare com.seronix.soiree.connection.System
      Update the Derby settings and make them active as shown here



    2. Prepare com.seronix.soiree.connection.ConsoleJournal
      Update the Derby settings and make them active as shown here



    3. Test both connections to ensure they can connect to Derby.
    4. Tip
      You need to ensure the settings are active before you can test them.

    That concludes this lesson.