Calling insert/update/delete statement

Any statement that updates the database or inserts and deletes a value in a 

database can be executed using the 

executeUpdate

 or

 execute 

method of the 

statement. Example 7 41 shows the update of the record using the prepared 

statement for MySQL and conversion for DB2 UDB. The change is due to the 

multiple MySQL database that have been merged into one DB2 database and 

grouped by the table schema. If you use 

set schema=

schema 

for a connection, 

then nothing needs to be changed.

Example 7 41   MySQL Prepared statement and executeUpdate

// MySQL prepared statement

PreparedStatement s = conn.prepareStatement("update warenkorb set anzahl=? 

,status=? where wk_id=65;");

//DB2 UDB changes for prepared statement

PreparedStatement s = conn.prepareStatement("update itsodb.warenkorb set 

anzahl=? ,status=? where wk_id=65;");

s.setInt (1, 10);

s.setString (2, "working");

int count = s.executeUpdate ();

System.out.println (count + " rows were inserted");

Using connection pooling and data source

The JDBC 2.0 Standard Extension API provides the data source interface as an 

alternative to the DriverManager for establishing a connection. When a 

DataSource class has been implemented appropriately, a DataSource object can 

be used to produce Connection objects that participate in connection pooling. 

You can either create a data source in your program or you can get an existing 

data source from the context.

MySQL provides 

MysqlDataSource

, which is a data source implementation of 

MySQL data source. The MySQL data source can be created by calling a default 

constructor 

com.mysql.jdbc.jdbc2.optional.MysqlDataSource

 as shown in 

Example 7 42.

Example 7 42   MySQL datasource creation and binding

com.mysql.jdbc.jdbc2.optional.MysqlDataSource mysqlds = new 

com.mysql.jdbc.jdbc2.optional.MysqlDataSource();

mysqlds.setServerName("localhost");

mysqlds.setDatabaseName( itsodb );

mysqlds.setUser("itso");

mysqlds.setPassword("itso");

mysqlds.setPortNumber(3306);

Context ctx = new InitialContext(env);

 Chapter 7. Application porting 

195