Using a Datasource

In this Lesson

You will

  • Learn how to configure a stand-alone environment to use datasources
  • Learn how to use a datasource
  • Understand how to handle any problems that may arise
  • Celebrate wildly because of your success

This is a long lesson because there is a lot of fun stuff to show you.

So, put on your dancing shoes and get ready to tango with a datasource!

Concepts

This lesson builds on the concepts from the Data Source Introduction lesson.

Database Connection Provider

Each datasource specifies the database connection definition that should be used, as shown here



Soiree provides a database connection provider that is able to take a connection definition and use it to establish the actual connection to the database. This means the database connection provider must be able to find the connection definitions at runtime or else it will be unable to establish the necessary connections.

There are two ways to make the connection definitions available to the connection provider

  1. As Resource Files
    This technique is most often used in the development environment.
  2. As XML Files
    This technique is more often used in a production server environment.

Both of these options were described in the Creating a Database Connection lesson.

In this lesson you will be using the Resource Files approach which consists of two steps

  1. Pass the USE_RESOURCES_FOR_DB system parameter to the JVM.
  2. Add the project’s sxres folder to the Java class path.

Stand-alone Environment for Datasources

Datasources are used in two different environments

  1. Inside SxServer
    SxServer provides the database connection provider and manages the connections.
  2. Stand-alone environment
    You must provide your own database connection provider and manage the connections.
    This involves the following responsibilities
    1. You must create the database connection provider.
    2. You must commit the database changes to the database if everything works ok or roll back the database changes if a processing error occurs.
    3. You must close the database connection provider when you are done with it.

In this lesson you will learn how to use datasources in a stand-alone environment.

Database Exception Handling

Whenever you access a database you must be prepared to handle exceptional conditions that may arise as you attempt to interact with the database server.

When you ask a database to do something it will respond in one of two ways

  • Pleasantly do what you asked
    This is called a normal response.
  • Grumble about your request
    The grumbling is gift-wrapped in an SQLException and then thrown at you.

It is important that any SQLException be handled well because it indicates something did not go as planned. Your response to the grumbling is dependent on the type of SQLException thrown at you and your ability to deal with the gift it contains.

Exceptions can be grouped into two general categories

  1. Exceptions that you can do something about
    These are exceptions that can be anticipated in your code. A good example would be a ‘record not found’ condition telling you the information you seek simply does not exist in the table.
  2. Exceptions that you simply cannot handle.
    These are exceptions that you do not expect to occur and when they are thrown at you the only reasonable course of action is to duck and let them hit the fan. A good example would be the database telling you the table you are interested in does not exist. [ it’s then time to have a chat with your friends on the data access team ]

Soiree datasources are designed to assist you with both types of exception.

  1. You can tell the datasource which exceptions you are able to handle.
  2. The datasource will throw an Exception for any condition that you have not explicitly stated that you will handle.

So, the datasource ensures that exceptions are handled. Either you tell the datasource you wish to handle them or the datasource will throw an exception. [ and your customer will be calling]

SQL State

Before we talk about how to tell a datasource what you want to handle you must first know a little bit about SQLSTATE codes.

Each time you execute an SQL statement the database responds by providing an SQLSTATE code. You can search the web for details about SQLSTATE codes. What we will provide here is an overview of why they are important and how they relate to SQLExceptions.

Each SQLSTATE code is exactly 5 characters in length. The first 2 characters describe the CLASS of the response ( most of which are consistent across vendors) and the last 3 characters provide a specific SUBCLASS (which can be vendor specific). We will discuss a just few of the more common classes here

  • Class 00
    Means successful completion and it has only one subclass code, 000. So a successful statement completion always returns 00000.
  • Class 02
    Means no data and has only one subclass code, 000. So, a select statement that returns an empty result set will return an SQLSTATE of 02000.
  • Class 21
    Means cardinality violation and has one sublass, 000. So, a single-row subquery that returns more than one row would return 21000. Also an attempt to SELECT INTO without a cursor would return this condition if the result set contained more than one row.
  • Class 22
    Means data exception and indicates that a data value is invalid in some way. There are a variety of subclass codes that describe conditions such as invalid date/time values, division by zero, or out of range conditions, to name a few.
  • Class 23
    Means integrity constraint violation and has one subclass code, 000. This condition would indicate that a database constraint has been violated – such as attempting to insert a duplicate key into a unique index or a referential integrity violation.
  • Class 40
    Means transaction rollback and has a few subclasses that indicate why a transaction rollback was performed.

Handling SQLExceptions

The JDBC driver, which translates information between Java and the database server, will throw an SQLException whenever the database server returns an SQLSTATE that is not normal (00000). This is how SQLSTATE information makes its way into the Java virtual machine.

So, when we talk about handling SQLExceptions, we are really talking about handling SQLSTATE return codes that are wrapped in an SQLException.

Each datasource provides methods that can be used to indicate which SQLSTATEs you are willing to handle. It provides one method for each of the SQLSTATE classes mentioned above. It also provides one method that allows you to be very specific about the SQLSTATE if the general methods do not meet your needs.

  • setHandleNotFound() is used to handle any class 02 SQLSTATE
  • setHandleMultRow() is used to handle any class 21 SQLSTATE
  • setHandleDataException() is used to handle any class 22 SQLSTATE
  • setHandleConstraintViolation() is used to handle any class 23 SQLSTATE
  • setHandleRollback() is used to handle any class 40 SQLSTATE
  • setHandleSqlstate(String sqlstate)
    This method can be used to specify the exact 5 character SQLSTATE that you wish to handle.

If you wish to handle an exceptional condition you do the following

  1. Tell the datasource the exceptional condition(s) you want to handle by using one of the setHandle…() methods.
  2. Execute the SQL statement
    Each method in the datasource that executes a statement such as insert, delete, update, or select will return true if the statement succeeds (SQLSTATE 00000) or it will return false if the the SQLSTATE was something you asked to handle. The method will throw an exception if the SQLSTATE is not 00000 and you did not ask to handle it.
  3. If the statement returns false (indicating an SQLSTATE other than 00000 that you wanted to handle) you must check to see if one of the exceptional condition has occurred using one of the following methods
    • isNotFound()
    • isMultRow()
    • isDataException()
    • isConstraintViolation()
    • isRollback()
    • isSqlstate(String sqlState)
  4. If the statement returns true (indicating an SQLSTATE of 00000) you know the statement suceeded. You can also ask the datasource if the statement succeeded by using this method
    • isNormal()

You will be using this exception handling in this lesson’s exercises.

Install the Sequence

Your Party datasource will need to use the sequence that you created so we need to install the sequence.

Soiree item definitions are installed when the server starts.

  1. Start Derby if it is not already running.



  2. Start the server.



    The Java Console will display the resources that are installed [ these detailed messages only show up you your Eclipse preferences has logging enabled for SxServer ]


  3. Stop the server



  4. You can also verify the sequence has been installed by looking at the SoireeSequence table.
    Open the DBeaver perspective by clicking on the DBeaver perspective tab.



  5. Open an SQL Editor



  6. Enter this SQL statement: select * from SoireeSequence and then right click the editor and select Execute SQL Script



  7. You should see two rows, one of which is your PartyId sequence.



Prepare a Stand-alone environment for Datasources

You will now install a test harness that can be used to test your datasource. This test harness provides a example of the database connection management that is required for using datasources in a stand-alone environment.

  1. Switch back to the Java perspective



  2. Create a new Java package named test






  3. Download the following files by clicking on the links

    DatasourceTestHarness.java
    DatasourceTestInterface.java
  4. Copy the files into the test package. [ you should be able to drag them into the package ]
    Your project should now look like this



  5. Create an Eclipse launch configuration for the test harness
    • Open the Eclipse Run Configurations dialog



    • Select Java Application and then the New Configuration button.



    • Enter test.DatasourceTestHarness as the main class [ it may already be defaulted for you ]


    • Soiree’s database connection provider needs to know where to find the database connection definitions. One way to do that is to pass USE_RESOURCES_FOR_DB as a System parameter to the JVM and add your resource files to the Java classpath. You will do this in the following steps.
    • Select the Arguments Tab and then enter ${java_type_name} in the Program arguments section and -DUSE_RESOURCES_FOR_DB in the VM arguments section.



    • Select the Classpath Tab, select the User Entries line and press the Advanced button.



    • Select Add Folders and press OK



    • Select Party project’s sxres folder and press OK



    • Select Apply and Close



The test harness for datasources is now ready to use.

Inspect the DatasourceTestHarness class

The DatasourceTestHarness class demonstrates the steps you need to take in order to create and manage the database connections in a stand-alone environment.

  1. Open the DatasourceTestHarness class by double clicking on it.
  2. Notice how it creates the connection provider and manages the connections. class.



  3. Take some time to read the green Java comments in this class.
  4. Any stand-alone environment that you create needs to be structured in a similar manner.

Insert A Row

This exercise will demonstrate how to insert rows into a table.

  1. Download this file by clicking on the link

    PartyInsert.java
  2. Copy the file into the test package



  3. Open the PartyInsert class by double clicking on it
  4. Notice the 3 steps used for inserting a row



  5. Run this insert process
    The PartyInsert class must be open in the Java editor when you run the test harness.
    Important
    The test harness always attempts to run the class in the active Java editor.







    You will see messages about the Party that was inserted in the Java Console



  6. Use DBeaver to look at the row that was added to the Party table as shown here



Validation Exception Handling – without MessageOwner

PartyInsert is written to use the Party datasource without MessageOwners. This means that any validation exception will cause a ValidationException to be thrown. PartyInsert will catch the thrown exception and then display all the validation errors that may have occurred.

  1. Change the value assignments to look like this.



  2. Save the class
  3. Run the test harness
    [ you could also simply press the Run button because the test harness was the last class to be run ]


    The datasource will not perform the insert SQL statement because the validation pack found problems with the data. The validation errors are displayed in the Java console



Validation Exception Handling – with MessageOwner

  1. Download this file and add it to the test package.

    PartyInsertWithMessageOwner.java
  2. A message owner is assigned to all the values in the datasource and the error detection logic has been changed.



  3. Run the test harness for PartyInsertWithMessageOwner
  4. You can see the MessageOwners received the validation error messages and the process threw an Exception because errors were detected.



Validating without doing an insert or update

The rules in the validation pack can be used directly instead of validating only as part of an insert or update operation.

The validation pack can be used in two ways

  1. Store values in the datasource and then validate all of them.
  2. Pass individual values to the validation pack without actually storing them in the datasource.

Select a Single Row with condition handling

This exercise will demonstrate the following

  • How to select a single row from a table
  • How to handle conditions ( SQLState return values )

SQL queries can return a single row if it qualifies rows using the unique key for the table. A full access datasource contains one of these.

selectBy
All datasource queries that select a single row are contained in a method that begins with selectBy…(). So, you can easily find the single row select queries in a datasource by using Eclipse code assist – simply look for any method starting with selectBy.
  1. Download this file and add it to the test package.

    PartySelect.java



  2. Open the PartySelect class by double clicking on it
  3. Notice the 3 steps of selecting a single row



  4. This class performs three different select options. Read the comments in the class to understand what each example is doing.
  5. Run the test harness and review the results in the Java Console



Show me the query
Hover the mouse over any datasource query method and it’s Javadoc comments will appear. Expand the pop-up window or scroll the comments down until you see the query.



Delete a Single Row

This exercise will demonstrate how to delete a single row.

An SQL delete statement can delete a single row if it qualifies rows using the unique key for the table. A full access datasource contains one of these.

deleteBy
All datasource queries that delete rows are contained in a method that begins with deleteBy…(). So, you can easily find the delete queries in a datasource by using Eclipse code assist – simply look for the any method starting with deleteBy.
  1. Download this file and add it to the test package.

    PartyDelete.java



  2. Open the PartyDelete class by double clicking on it
  3. Notice the 3 steps of deleting a single row



  4. This class attempts to delete 2 rows. Read the comments in the class to understand what each example is doing.
  5. Run the test harness and review the results in the Java Console



  6. Use DBeaver to select all the rows from the Party table



    Party 1 still exists in the table because a rollback was performed when the second delete attempt threw an Exception. Look in the Java Console for the Rolling back database changes log entry.

Update a Single Row

This exercise will demonstrate how to update a single row.

An SQL update statement can modify a single row if it qualifies rows using the unique key for the table. A full access datasource contains one of these.

  1. Download this file and add it to the test package.

    PartyUpdate.java



  2. Open the PartyUpdate class by double clicking on it
  3. Notice the 3 steps of updating a single row



    Query Synergy
    This example shows the power of cross-query integration. You are using a select query to read a row and simply calling update. The update() method actually runs a second query to perform the update. You did not have to pass any keys to the update query because the datasource knows which row it contains [ it is focused on the selected row ]. The datasource passes the row’s unique key to the update query for you!
  4. Run the test harness and review the results in the Java Console



  5. Use DBeaver to confirm the changed value is stored in the table Party table



  6. Modify the PartyUpdate class to provide some invalid values



  7. Run the test harness and review the results in the Java Console



Validate if Changed
When updating a row the validation pack will only validate a value if it has been changed.

Browse Multiple Rows

This exercise will demonstrate how to obtain multiple rows from a query that has the ability to return more than one row.

The full access datasource contains a browse query (also called a browse cursor) that is able to return all rows from the table. Because the party table contain a tenant the browse query qualifies the rows by tenant_id. So, ‘all rows’ means ‘all rows for specific tenant’ as shown here:



The datasource provides two methods for this query, one that accepts a tenant_id and one that accepts no input parameters.



Tenant Default
Any query that accepts a tenant will have two methods. One that accepts the tenant and one that does not. You should generally use the method that does not accept tenant and allow the tenant to be obtained from the user. This gives you better protection against showing the wrong rows to the right customer.
Check out the selectBy and deleteBy methods in the Party datasource superclass.
  1. Download this file and add it to the test package.

    PartyBrowse.java



  2. Open the PartyBrowse class by double clicking on it
  3. Notice the 3 steps of reading multiple rows from a table



    Danger
    The SourceIterator must be closed when you are done with it [call it’s close() method]. This closes the SQL statement and releases it resources.
    Tip
    Put the SourceIterator in a try with resources block. This will automatically close the iterator for you.
  4. Run the test harness and review the results in the Java Console



  5. Want to see more rows?
    • Use PartyInsert to add more rows to the table
    • Re-run PartyBrowse

Browse Cursor with Update

A datasource allows you to easily update rows that are returned from a browse cursor. This works very much like the select and update example. As you fetch each row from the browse cursor the datasource is focused on the fetched row. If you call the update() method it knows which row to update.

  1. Download this file and add it to the test package.

    PartyBrowseWithUpdate.java



  2. Open the PartyBrowseWithUpdate class by double clicking on it
  3. Notice the 5 steps of reading multiple rows from a table and updating them



  4. Run the test harness and review the results in the Java Console



  5. Use DBeaver to verify the values in the table.



Change Cursors

There is one more generated query that can be added to a datasource: a change cursor. This is very much like a browse cursor but it adds the ability to update rows through the cursor instead of using two different queries.

A full access datasource does not contain a change cursor, by default, so you will add one to the datasource and then test it out.

  1. Add a generated change cursor to the Party datasource
    1. Open the Party datasource.



    2. Request a new generated query



    3. Provide a query name of PartyUpdate and press OK



    4. Notice the default options that were selected for you



    5. Build the datasource.



      The build should succeed



  2. Download this file and add it to the test package.

    PartyChangeCursor.java



  3. Open the PartyChangeCursor class by double clicking on it
  4. Notice the 5 steps of reading multiple rows from a table and updating them



  5. Browse vs Change
    The change cursor processing looks very much like using a browse cursor in combination with the update() method. The difference between these two is locking. The browse cursor would fetch rows with a read lock – which means other people are also allowed to read the same row. The change cursor would fetch the rows with an update lock – which means when you read the row no one else can read it until you are done with it.
  6. Run the test harness and review the results in the Java Console



  7. Use DBeaver to verify the values in the table.



You have been dancing with the Party datasource for a long time now. Thanks for hanging in there!

We will leave the wild celebration to you. It’s your party.