 |
|
| 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:
- JDBC coding – shown here
- Using IBM data access beans (dbbeans.jar) to manage data access
- Using IBM JSP data access tags in a JSP page
- Generating a data access application using the Web database application
wizard
In ...
To continue reading for free, register below or login
To read more you must become a member of Search400.com
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, stored procedures (to call
host programs easily) and UDFs (user defined functions).
The sample test class (TestUpdateMethods) will do the following:
- Create a table (WEBUSR) and populate it in the selected database (DB2
UDB or AS/400)
- List a short report of table rows after create completes
- Set a selection value to select rows in the database
- Run a where update (an SQL update using a where clause) to set Title
to Programmer for selected rows
- List a short report of table rows after the where update completes
- Run an SQL select before the positioned update to select rows
- Run a positioned SQL update using a cursor to iterate on the result
set by row and set Title to Engineer for each row
- List a short report of table rows after the where update completes
- Close the database statements and connections
Basic concepts
The sample application uses SQL statements to do the following:
- Create a table (WEBUSR) – CREATE TABLE statement
- Insert records into the table – INSERT INTO statement
- Read selected records from the table – SELECT statement
- Update records in the table using a where clause – UPDATE WHERE
- Read selected records for update from the table – SELECT … FOR UPDATE
OF statement
- Update records in the table using a where clause with a cursor - UPDATE
WHERE CURRENT OF {cursor}
With JDBC, the concepts are similar to using files in a traditional program:
- You connect to a database - here using a JDBC Connection or opening
a data source
- You position to data in a table – here using a SELECT statement with
a WHERE clause
- You can create a new table – here using CREATE TABLE (instead of CRTPF)
- You can write records to a table – here using INSERT INTO
- You can update records in a table – here using 2 routines: UPDATE WHERE,
UPDATE by cursor
- You can delete records in a table – not shown here
- You can scroll backward or forward in a table – using result set scrolling
(see UPDATE by cursor)
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:
- Read sequential, Read by Key, Write records, Update records, Delete
records
- Manage concurrent access to the file with record "lock for update"
control and more
- Scrolling through the database in the application using Read Next,
Read Previous
- Commitment control and rollback under program control or automated
- Access specific file members in data or source physical files
- Efficient pooling of database connections using data sources (not shown
here)
- Management of errors by the application (similar to MONMSG in a CL
program)
Other benefits JDBC offers:
- Less coding needed in many cases than traditional programs – you can
write "generic" JDBC routines one time to talk to any table!
- Can operate on iSeries data from ANYWHERE in a TCP network using the
Java toolbox JDBC drivers
- Good performance for network applications including caching options
- Opportunity to replace many expensive host programs that do database
reporting or updates with simple SQL and stored procedures
- Run CL commands easily
- Execute server Java applications with RUNJVA
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
DB Connection type
Versions
DB Jar file
Driver class name
url prefix
DB2 UDB database local connection
6, 7, 8
Db2java.zip
COM.ibm.db2.java.app.DB2Driver
Jdbc:db2
iSeries Java toolbox remote connection
JDK 1.1x to JDK 1.4
Jt400.jar
com.ibm.as400.access.AS400JDBCDriver
Jdbc:as400
iSeries Java native local connection
JDK 1.1x to JDK 1.4
Rt.jar
com.ibm.db2.jdbc.app.DB2Driver
Jdbc:db2
Sun JdbcOdbc connection
JDK 1.1x to JDK 1.4
Classes.zip or rt.jar
sun.jdbc.odbc.JdbcOdbcDriver
Jdbc:odbc
MySQL connection
3, 4
Mysql-connector-java-2.0.14-bin.jar
com.mysql.jdbc.Driver
Jdbc:mysql
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:
- Create a new project in WDSC
- Add a class variable for your JDBC database jar file to the project
build path
- Create a new class: TestUpdateMethods.java
- Copy selected methods from TestUpdateMethods.java sample here to your
own class definition
- Create a scrapbook page in the project
- Copy the text to run the Database update tests to the scrapbook
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:
- In the scrabook text, set the user, password, system and library names
to the correct values for your environment.
- Highlight the selected lines
- Right button menu > Stop evaluation (IF it is an available option)
- Right button menu > Run Snippet
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:
- Defines an SQL query
- Creates a query statement using the query definition
- Executes the query returning a result set
- Gets the column headings for each column in the result set using the
result set metadata
- Lists the column data for each row in the result set
- Displays the results on the system console device
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:
- Defines a select SQL string
- Creates a select statement with the SQL string
- Executes the select statement returning a result set
- Gets the name of the SQL cursor returned from the result set.
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:
- Defines an SQL Update string using the WHERE CURRENT OF {cursor} clause
to update a result set cursor position. (We'll use the cursor name we retrieved
in the selectDb() method above.)
- Prepare and update statement using the update SQL string
- Iterate on the result set with the while clause for each row in the
result set retrieved in selectDb
- Set a value for the Title column in the update statement by position
using setString
- Log the new value for the row to the console with System.out.println
- Execute the update statement for that row and value
- When we're finished with all rows, we update the entire set using a
single commit statement.
- We end by closing all our statements used
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:
- Defines an SQL update string with a where clause to select records
and specifies SET conditions for columns
- Prepares an update statement
- Sets any positional parameter values for the SQL statement (here we
have a selectValue = "Mason" and an updateValue = "Programmer")
- Executes the update statement
- Commits the changes to the database
- Closes the update statement
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 |
|
|
|
|
|
