Data Source Introduction

In this Lesson

This lesson will be introduced to the Soiree Data Source [ also written as datasource ].

Concepts

A datasource provides access to database tables. Soiree is Java based and like all Java based solutions it uses Java Database Connectivity (JDBC) to access the database – but JDBC is only a part of the datasource story. A datasource provides the following

  • A convenient alternative to using the JDBC application programming interface (API)
  • Simplified access to database tables
  • Simplified error handling
  • Table objectification
  • Multi-statement integration
  • Multi-tenancy support
  • SQL statement reuse
  • Data validation
  • Embedded processing
  • Change logs
  • Enhanced support for concurrent updates
  • Centralized management
  • Cross-database support
  • Deep solution integration
  • Improved security
Just For You
These capabilities are wrapped in enough automation to make it qualify as a really big hammer for smashing your workload down to a highly digestible size.
Datasources are super easy to create. You will get to create one in the lesson that follows this one.

This lesson is only an introduction to some of the data source concepts. We will get into the details of all the features as you progress through the lessons.

Item Definition and Java Class

The Soiree items that you have been creating up to this point are simply definitions: they provide information to some other process. For example, the Database Connection item defines the connection parameters for a database.

A datasource starts out as a definition: you define what you want the datasource to be. You then run the definition through a build process that creates Java classes. The datasource classes are used to read and write to a database.

Table Objectification

Datasources take their inspiration from object oriented programming. So, let’s divert for just a moment and consider the concept so that we can see how it applies to datasources.

Object oriented programming attempts to combine information and process into a single ‘thing’ [ an object ]. These objects are able to

  1. Take responsibility for the information they contain
  2. Respond in a more natural way because they have the goods [ data ] and know how to use it

Consider a rather silly example:
Imagine you own a dog [ a flesh and bone dog ] that has all the parts you would expect in a dog but lacks the capacity to actually do anything. We could say the dog has data but no process. Now imagine you want to make the dog bark. Since the dog has no process capable of making the parts emit sound you would need to

  • Reach over and open the dog’s mouth
  • Wrap your hands around the dog’s neck as if strangling the poor thing
  • Squeeze until the dog emits a sound

The poor dog may die … or it may sound more like a mouse than a canine. Wouldn’t it be better to own a dog that can bark on it’s own and lick your face when you feed it? [ no face licking please ]

In our example, the dog represents the database. [ I have seen databases that work like a dog ]

We will now illustrate how a datasource can help us stop strangling the dog.

  • We will first look at life without datasources
  • We will then see what datasources bring to the party

Data Access using only JDBC

When accessing a database using only the JDBC API the process appears very procedural [ a bit like squeezing your dog’s neck, perhaps ]. The actions are taking place outside, or away from, the information as illustrated here

The data is “over there” and we poke at it.



Data Access using a Data Source

A datasource combines the following items into a single ‘object’ provided to you as a Java class

  • Information [ the stuff in the table ]
  • Access to the information [ SQL statements and JDBC ]
  • Rules that protect the information [ code that validates the information ]
  • Actions related to the information [ intelligence about the information ]

It looks something like this



The Datasource Silhouette

Each datasource is a Java class that contains

  • SQL Statements used to access the database
    These are contained in methods that can be called to execute the statement.
  • Values
    These values correspond to columns in the table.
  • Validation Code
    This is Java code used to guard the integrity of the table’s contents.
  • Enhanced Processing Code
    You can configure the datasource item to add additional code for specific purposes or you may add custom Java to address your design needs.

A datasource may only contain values from one row. Stated another way, at any moment in time a datasource can contain column values from only one row.

Datasource Select

Selecting rows from a table looks like this



Think of the datasource as a magnifying glass that focuses on one row at a time. As you slide the magnifying glass down the rows in the table you can see each row as you pass over it.

If the select statement utilizes a cursor you may then call a fetch() method to return the next row in the query’s result set.



Focus
When a datasource contains values that are associated with a row in the table the datasource is said to be focused on the row.

Datasource Update

Updating a row in a table works like this



Multi-statement integration
Notice how you do not have to tell the update statement which row in the database to update. With JDBC you need to provide the update statement with the keys of the row that should be updated. The datasource does this for you. It passes the keys of the focused row to the update query for you. This provides a more natural interaction with the table.

This is one example of how you can use statements in combination with each in a very natural fashion.

Datasource Insert

You have probably already figured out how an insert works. Here is an example:



Datasource Delete

A SQL delete statement is different type of animal than other statements. It does not operate on columns – it only deals with rows.

So, deleting a row simply involves calling a delete() method and specifying the key of the row or rows to be removed from the table.



Parting shots about datasources

  1. Datasources are designed to be shared. There is no need to write the same query more than once.
    [ have your data access people create the datasources and have every dine on them ]
  2. A datasource may contain a subset of the columns in the table.
  3. The values declared in a datasource is the contract of what is available to the consumer.
    This is a contract that must be honored.
    • All of the SQL statements in the datasource must operate on all the values declared in the datasource.
      • Select statements may not not choose to select only some of the values from the table.
      • Update statements may not choose to update some of the declared column values and not others.
      • Update statements may not update values that are not declared in the datasource.
      • Delete statements are exempted from this rule, by definition, because they do not operate on column values. A datasource may contain any delete statement that is chooses.
  4. The number of datasource values determines which queries can co-exist in a datasource.
    • If a query operates on different quantity or type of columns than what is declared in a datasource you must then create a new datasource to contain the query.
  5. Datasources may return values from multiple tables.
    • This may be the result of a join or union statement.
    • You may also have two separate select statement fetching rows from two different tables. This is allowed as long as the number and type of the columns match. [ the need must also be legitimate – you do not want to confuse the consumer ]
  6. Datasources should be clearly named so that consumers can locate them and have some idea about what they provide.
    Here are some naming recommendations for your consideration
    • Each table should have one datasource whose name is the table name. This datasource should provide full access to the table and contain the statements that operate on all rows from the table.
    • Datasources for a single table that do not operate on all the table’s columns should have a name that begins with the table name and is then qualified by the purpose of the datasource. For example: PartyNameList or Party_NameList could be a datasource that provides a list of parties and only returns the party name and id columns.
    • Datasource that operate on multiple tables, such as a join, should be given a name that reflects the purpose or source of the values.
  7. A datasource keeps track of the focused row by storing the unique key to the row. You must declare the unique key when you create the datasource in order for the focusing mechanism to work for you.

That’s it for this lesson! [ pure lecture, no labs ]