com.warework.service.datastore.view
Interface RDBMSView

All Superinterfaces:

DataStoreView, DBMSView

All Known Implementing Classes:

JDBCViewImpl

public interface RDBMSView
extends DBMSView

View for Relational Database Management Systems.

This View is an interface that represents a collection of data items organized as a set of formal-ly-described tables. You can take advantage of this View to query and update with SQL statements relational databases like Oracle, MySQL, DB2, etc. Two basic operations are allowed here:

• Update operations: Executes a given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement (for example: CREATE TABLE).
• Query operations: Executes a given SQL statement, typically a static SQL SELECT statement, to retrieve data from the relational database.

To work with this View, first you have to connect to the database management system:

// Get an instance of a RDBMS View interface.
RDBMSView view = (RDBMSView) datastoreService.getView(...);

// Connect the Data Store.
view.connect();

Perfect, now a connection with the database is ready to accept SQL commands. In the following examples we are going to save some information in a database but before that, it is recommended to begin a transaction with the database.

A transaction comprises a unit of work performed within a database management system, and treated in a coherent and reliable way independent of other transactions. Transactions in a da-tabase environment have two main purposes:

• To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.
  
• To provide isolation between programs accessing a database concurrently. If this isola-tion is not provided the programs outcome are possibly erroneous.

Transactions provide an "all-or-nothing" proposition, stating that each work-unit performed in a database must either complete in its entirety or have no effect whatsoever. Further, the system must isolate each transaction from other transactions, results must conform to existing con-straints in the database, and transactions that complete successfully must get written to durable storage.

To begin a transaction in a database management system you have to perform the following actions:

// Get an instance of a RDBMS View interface.
RDBMSView view = (RDBMSView) datastoreService.getView(...);

// Connect the Data Store.
view.connect();

// Begin a transaction in the database management system.
view.beginTransaction();

Now it is the right time to perform some update operations. First, we are going to add one row in a table of the database:

// Get an instance of a RDBMS View interface.
RDBMSView view = (RDBMSView) datastoreService.getView(...);

// Connect the Data Store.
view.connect();

// Begin a transaction in the database management system.
view.beginTransaction();

// Create the SQL statement to execute.
String sql = "INSERT INTO HOME_USERS (ID, NAME) VALUES (1, 'John Wood')";

// Run the SQL update statement.
view.executeUpdate(sql, null);

Another option is to execute multiple statements at once. You can do it by specifying a separa-tor character which delimits each statement:

// SQL statement to create first user.
String sql1 = "INSERT INTO HOME_USERS (ID, NAME) VALUES (1, 'John Wood')";

// SQL statement to create second user.
String sql2 = "INSERT INTO HOME_USERS (ID, NAME) VALUES (2, 'James Sharpe')";

// SQL statement to create third user.
String sql3 = "INSERT INTO HOME_USERS (ID, NAME) VALUES (3, 'Sofia Green')";

// Execute three SQL update statements at once.
view.executeUpdate(sql1 + ";" + sql2 + ";" + sql3, new Character(';'));

Perform update operations like this when you have to dynamically construct SQL statements in Java. If your statements are not too complex to create, like the ones we saw in the previous example, you should consider storing them on separate files as they are easier to maintain. A very convenient way to keep SQL statements in separate files consist of keeping each statement (or a set of related statements) in an independent text file, for example: create-user.sql, delete-user.sql, update-user.sql, etc. Later on we can read these text files with a Provider (Warework recommends you to use the FileText Provider for this task) and use this Provider to read the statements for a View. Remember that you can define a default Provider for a View when you associate a View to a Data Store:

// Add a View and link a Provider to it.
datastoreService.addView("sample-datastore", SampleViewImpl.class, "sql-provider", null);

The sql-provider Provider now can be used in the View as the default Provider to read text files from a specific directory. Let us say we have the following content for create-user.sql:

INSERT INTO HOME_USERS (ID, NAME) VALUES (1, 'John Wood')

If sql-provider is the default Provider in a RDBMS View, we can read the content of this file with the following code:

// Read the content of 'create-user.sql' and execute it.
view.executeUpdateByName("create-user", null, null);

When executeUpdateByName is invoked, these actions are performed:

1. The RDBMS View requests the create-user object to sql-provider.
2. sql-provider reads the content of create-user.sql and returns it (as a String object).
3. The RDBMS View executes the statement included at create-user.sql in the Data Store.

The RDBMS View and the FileText Provider are perfect mates. Both, in combination, will simpl-ify a lot the process of executing scripts in your database. Just write simple text files with SQL statements and let Warework execute them for you. It is recommended that you check out the documentation associated to the FileText Provider to fully take advantage of this feature.

If we need a generic statement to create new users in the database, we can define the script create-user.sql with some variables, like this:

INSERT INTO HOME_USERS (ID, NAME) VALUES (${USER_ID}, ${USER_NAME})

Then replace these variables with the values that you need:

// Values for variables.
Hashtable values = new Hashtable();

// Set variables for the update statement.
values.put("USER_ID", new Integer(3));
values.put("USER_NAME", "Ian Sharpe");

// Create a new user in the database.
view.executeUpdateByName("create-user", values, null);

When your script contains multiple statements, you also have to indicate the character that delimits each statement. Suppose we have the following create-user.sql script:

INSERT INTO HOME_USERS (ID, NAME) VALUES (${USER_ID}, ${USER_NAME});
INSERT INTO ACTIVE_USERS (ID, NAME) VALUES (${USER_ID});

Now we can replace variables in multiple statements with this code:

// Values for variables.
Hashtable values = new Hashtable();

// Set variables for the update statement.
values.put("USER_ID", new Integer(3));
values.put("USER_NAME", "Ian Sharpe");

// Create a new user in the database.
view.executeUpdateByName("create-user", values, new Character(';'));

Every update operation that we performed in the previous examples is related to the transaction that we created earlier in this section. Once the work is done, you should either commit or rollback the transaction. If the operations were executed without problems, then you should perform commit to register the changes in the database:

// Commits changes in the Database Management System.
view.commit();

In the other hand, if you find a failure, something unexpected happened or you just do not want to register the changes in the database, then you should perform rollback to undo every update operation executed since the transaction was started:

// Cancel latest update operations.
view.rollback();

* We have reviewed with this interface how to connect to a database and perform update opera-tions in it with a transaction. Now we are going to focus on query operations to know how to retrieve data from a relational database. The following code is an example to perform this action:

// Execute the statement to retrieve some data.
Object result = view.executeQuery("SELECT * FROM HOME_USERS", -1, -1);

This code executes the SQL statement into the relational database and returns an object that represents the result provided by the database. For those using a JDBC Data Store, this result is in the form of a ResultSet object. Anyway, check out the specific type returned by the implementation class of this View because each Data Store may provide a different object as result.

Sometimes you may need to limit the number of rows returned by a database when a query operation is performed. Let us say that there are 26 registries or rows in the HOME_USERS table and that we just expect to retrieve the first 10 rows. We can write something like this:

// Get the first 10 rows.
Object result = view.executeQuery("SELECT * FROM HOME_USERS", 1, 10);

What is going on right now? When you specify the number of rows that you want in the result of a database, Warework automatically calculates the number of pages that hold this number of rows. In the previous example we specified 10 rows per result and with this information Warework estimates that the size of each page is 10 rows and that there are three pages: page 1 with 10 rows, page 2 with 10 rows and page 3 with 6 rows. If now we need to retrieve the next ten rows, we have to indicate that we want the second page:

// Get rows from 11 to 20.
Object result = view.executeQuery("SELECT * FROM HOME_USERS", 2, 10);

If we request page number three, we get the last 6 registries from the database. The important fact to keep in mind here is that the number of rows remains as 10:

// Get rows from 21 to 26.
Object result = view.executeQuery("SELECT * FROM HOME_USERS", 3, 10);

With queries, you can also write SELECT statements in separate text files (this time, just one statement per file). Suppose that the following code is the content of the list-users.sqlfile:

SELECT * FROM HOME_USERS
If sql-provider still is our default Provider, we can read the script like this:

// Read the content of 'list-users.sql' and execute it in the database.
Object result = view.executeQueryByName("list-users", null, -1, -1);

There is also the possibility to define some variables in the query:

SELECT * FROM HOME_USERS A WHERE A.ID = ${USER_ID}

Now we can assign a value to this variable to complete the query. This is done as follows:

// Values for variables.
Hashtable values = new Hashtable();

// Set variables to filter the query.
values.put("USER_ID", new Integer(8375));

// Read 'list-users.sql', replace variables and execute the final statement.
Object result = view.executeQueryByName("list-users", values, -1, -1);

The two last arguments allow you to define the page and maximum number of rows to retrieve. The following example shows how to get the second page with a fixed size of 10 rows per page:

// Get the second page with no more than 10 registries in it.
Object result = view.executeQueryByName("list-users", null, 2, 10);

As always, when the work is done, you have to disconnect the Data Store:

// Close the connection with the Data Store.
view.disconnect();



Version:

1.0.0

Author:

Warework

Method Summary

Object	executeQuery(String statement, int page, int pageRows) Executes an SQL query statement in the Relational Database Management System.
Object	executeQueryByName(String name, Hashtable values, int page, int pageRows) Loads a String that represents an SQL query (typically a static SQL SELECT statement) from the default Provider defined for this View (or the next View in the stack of Views of the Data Store) and executes it in the Relational Database Management System.
void	executeUpdate(String statement, Character delimiter) Executes a set of SQL update statements in the Relational Database Management System.
void	executeUpdateByName(String name, Hashtable values, Character delimiter) Reads a set of SQL update statements in the default Provider defined for this View (or the next View in the stack of Views of the Data Store) and executes them in the Relational Database Management System.

Methods inherited from interface com.warework.service.datastore.view.DBMSView

beginTransaction, commit, rollback

Methods inherited from interface com.warework.service.datastore.view.DataStoreView

connect, disconnect, isConnected

Method Detail

executeQuery

Object executeQuery(String statement,
                    int page,
                    int pageRows)
                    throws ClientException

Executes an SQL query statement in the Relational Database Management System.

Parameters:

statement - SQL statement, typically a static SQL SELECT statement, to retrieve data from the relational database.

page - Number of pages which hold the number of rows that you want in the result of the database (check out pageRows argument). Set this argument to -1 to avoid pages.

pageRows - Number of rows that you want in the result of the database. Set this argument to -1 to retrieve every row.

Returns:

Object that holds the result of the query.

Throws:

ClientException

executeQueryByName

Object executeQueryByName(String name,
                          Hashtable values,
                          int page,
                          int pageRows)
                          throws ClientException

Loads a String that represents an SQL query (typically a static SQL SELECT statement) from the default Provider defined for this View (or the next View in the stack of Views of the Data Store) and executes it in the Relational Database Management System.

Parameters:

name - Name of the query statement to load from a Provider.

values - Map where the keys represent variable names in the query-string loaded from the Provider and the values those that will replace the variables. Every variable must be inside '${' and '}' so the variable CAR must be in this query-string as '${CAR}'. Pass null to this parameter to make no changes in the query loaded.

page - Number of pages which hold the number of rows that you want in the result of the database (check out pageRows argument). Set this argument to -1 to avoid pages.

pageRows - Number of rows that you want in the result of the database. Set this argument to -1 to retrieve every row.

Returns:

Object that holds the result of the query.

Throws:

ClientException

executeUpdate

void executeUpdate(String statement,
                   Character delimiter)
                   throws ClientException

Executes a set of SQL update statements in the Relational Database Management System.

Parameters:

statement - SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement (for example: CREATE TABLE).

delimiter - Character that separates each statement (for example: ';'). Use null to indicate that just one statement is passed to the database.

Throws:

ClientException

executeUpdateByName

void executeUpdateByName(String name,
                         Hashtable values,
                         Character delimiter)
                         throws ClientException

Reads a set of SQL update statements in the default Provider defined for this View (or the next View in the stack of Views of the Data Store) and executes them in the Relational Database Management System.

Parameters:

name - Name of the statement to load in the Provider.

values - Map where the keys represent variable names in the update-string loaded from the Provider and the values those that will replace the variables. Every variable must be inside '${' and '}' so the variable CAR must be in this statement-string as '${CAR}'. Pass null to this parameter to make no changes in the statement loaded.

delimiter - Character that separates each statement (i.e.: ';'). Use null to indicate that just one statement is passed to the database.

Throws:

ClientException