 |
|
| iSeries 400 Tips: |
|
|
|
WEBSPHERE STRATEGIES FOR ISERIES PROFESSIONALS
How to update iSeries databases using JDBC, SQL
Jim Mason, Search400.com expert
04.03.2003
Rating: -4.00- (out of 5)
JDBC is the most commonly used method to access data in relational databases
on any platform from Java applications of any type (web, client, EJB, host).
This tip shows common JDBC examples to create, write, read and update data
in normal iSeries data files using JDBC and SQL and how to efficiently update
iSeries databases using JDBC (Java Database Connectivity). The sample application
(TestUpdateMethods.java) demonstrates two techniques for database updates
using SQL: using an UPDATE statement with a where clause, using a positioned
update with SQL cursor control.
Where you can use this
These techniques will help you develop more efficient Java database applications.
The data access methods shown can be added to Java beans and invoked by JSPs.
The examples use both DB2 UDB for Common Servers (version 7.2) and the iSeries
Java Toolbox JDBC (version 3.0) drivers for testing. The example also was
tested successfully with MySQL, version 3.5.3. The Sun JDBC specification
is now at 3.0. The update where statement works with JDBC 1.0 drivers. The
positioned update statements work with JDBC, 2.0 drivers.
Remember, if you are developing a Web application in WebSphere Development
Studio Client (WDSC), you have other choices for building data access routines.
You could also use the record IO classes in the Java Toolbox. They are similar
to working with iSeries physical and logical files. Generally, record IO
is more expensive to program for potentially small performance improvements
over JDBC. JDBC is the lowest level of SQL data access development in WDSC.
It offers the most control but also requires more work than other SQL data
access development options. The "data access development" layers possible
in WDSC include the following:
In addition, WDSC's Data perspective offers an excellent set of database
management tools to visually manage your iSeries database, import the database
definitions into WDSC, create SQL statements, sto
To continue reading for free, register below or login
To read more you must become a member of Search400.com
red procedures (to call
host programs easily) and UDFs (user defined functions).
The sample test class (TestUpdateMethods) will do the following:
Basic concepts
The sample application uses SQL statements to do the following:
With JDBC, the concepts are similar to using files in a traditional program:
For more on JDBC, the "10-minute Tutor" covers the basics of JDBC data access
as well (www.quickwebsupport.com
) if needed. The sample class TestUpdateMethods.java is available for download
there.
With JDBC applications, you have ALL the control over data access you have
used in traditional RPG or Cobol programs with traditional physical and logical
file IO:
Other benefits JDBC offers:
Beyond iSeries, the SAME JDBC routines can read data in almost any other
resource (MySQL on Linux, SQL Server in Windows, Excel spreadsheets, text
files and so on). There's almost no limit. In fact, I have one Java bean
we've written that can read or write to ALL those data sources and more anywhere
in the network! That's the power of Java.
Setup for examples
You need WDSC version 5 installed correctly on a Windows system. For the
DB2 UDB Common servers local access to DB2, you should install DB2 Personal
Edition or the equivalent at version 6 or higher. For the iSeries database
access, you'll need a TCP connection to an iSeries at V4R4 or later that
has the Java toolbox installed (57xx-JC1).
JDBC drivers are provided by database vendors to connect Java applications
using JDBC to a specific database. The drivers are normally packaged as jar
files that need to be added to your application classpath at runtime.
Below is a table showing the different connection information you need for
different JDBC drivers.
Table of JDBC connection information for common database drivers
[TABLE]
Unless it's already in your classpath, you'll need to find the jar file and
add it to your classpath BEFORE trying to use a JDBC driver. For the iSeries
Java toolbox remote connection, JT400.jar is in WDSC in a folder {WDSC home}iserieseclipsepluginscom.ibm.etools.iseries.toolbox_5.0.0runtime)
where {WDSC home} represents the base directory WDSC is installed in. For
DB2 UDB Common servers, the jar file Db2java.zip is usually stored in the
folder: x:sqllibjava (where x is your drive identifier)
To setup a project in WDSC to create the example, you need to do the following:
Copy the text to run the Database update tests to the scrapbook
Here's the scrapbook text to run the database samples:
Run the example
To run the example using the script above in the scrapbook page:
The output below is shown in the WDSC console view
Console output for the example:
Build the example
You can edit the example in WDSC using the LPEX editor and make any changes
you like.
Logically, building database access for an application WOULD NOT be done
in a single class as done here. This was only for demonstration purposes.
You would define a valid object model for a data access framework or use
an existing framework if it fits such as IBM's Dbbeans.jar file used in many
of the IBM data access wizards.
Let's review some sections of the sample application.
Connecting to the database (connectDb method)
Our connectDb method handles two types of connections: iSeries database using
Java toolbox for iSeries and a local DB2 database using DB2 Personal Edition
for Windows.
Setting the doAS400Connection property to true causes this application example
to connect to an iSeries database. If false, the application attempts to
connect to a local DB2 database.
Since my test library is not journalled or an SQL collection, I set the transaction
isolation level to none for the connection so the database update statements
would be allowed WITHOUT commitment control in place (good for testing but
not production).
Our finalize method ensures the database statements and the connection are
closed even if the application aborts in an error condition.
Create database table
The create database table method (createDb) attempts to delete the WEBUSR
table if it already exists with an SQL drop statement. It then does a CREATE
TABLE statement defining the ID field as the primary key. After each statement
is prepared (defined), an execute causes the statement to run and the commit
causes the change to be applied permanently to the database.
Note that all the sample code shown here is enclosed in try – catch blocks
to handle reporting any errors that occur (much like MONMSG commands in a
CL program or using the error indicators in RPG and COBOL to handle database
errors).
After the table is created, we define an insert statement with positional
parameters for substitution. Substitution parameters are marked with a "?".
The prepared statement allows us to efficiently create the statement one
time and then reuse it over and over by just substituting new parameters
and executing it again. When we're finished, we close the statement releasing
its resources.
The array rows hold three arrays of Strings (rowdata1 to 3). The for loop
(for each row of data values in rows) below does three things: sets each
variable to insert positionally with a parameter value, executes the insert
statement and commits the change. The substitutions occur in order by field
index. Note the rows array index begins at 0, but the JDBC field index for
a row begins at 1.
List records in a table
The listDb() method lists the all the records in the table for a selected
column list (fieldList). The routine:
One of JDBC's big advantages over normal host applications is the dynamic
access to table metadata (your DDS definitions and more). You can adapt your
program to information using the metadata.
Select records for update
The selectDb() method:
The cursor name will be needed later when we do a positional update.
Update records using a result set cursor
The positionalUpdateDb() method does several things:
Update records using a where clause
The whereUpdateDb() method is simpler to code than the positional update
which required two methods: selectDb() and positionalUpdateDb(). It is useful
if you are doing a single update in a program. If multiple update statements
are needed for different values, the positional update is the best choice
because it allows flexibility in the updates for a single row and it does
the selection once.
The whereUpdateDb() method accomplishes several things:
Key tips
----------------------------------------
About the author: Jim Mason, president of ebt-now, is an iSeries WebSphere
engineer. ebt-now provides iSeries WebSphere, WebFacing engineering, development
and training services. For more on JDBC, visit www.quickwebsupport.com.
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
