Simple question about using JDBC to access DB2 - Java Help

AppletTalk.com
Java discussions newsgroups

Archives

FAQ

Memberlist

Usergroups

Profile

Simple question about using JDBC to access DB2

AppletTalk.com Forum Index -> Java Help

View previous topic :: View next topic

Author

Message

George3
Guest

Posted: Thu Feb 23, 2006 2:12 am Post subject: Simple question about using JDBC to access DB2

Hello everyone, I want to use JDBC to access DB2 database. I also want to display the content of a specific table (table name is configurable) into web page (just like what is displayed when we use command line "select * from <table name>" on the console). I am wondering whether there are any existing sample which is easy and quick to learn with source codes. thanks in advance, George

Hal Rosser
Guest

Posted: Thu Feb 23, 2006 3:12 am Post subject: Re: Simple question about using JDBC to access DB2

"George3" <u19006@uwe> wrote in message news:5c49895559d7a@uwe...

Quote:

Hello everyone,

I want to use JDBC to access DB2 database. I also want to display the
content
of a specific table (table name is configurable) into web page (just like
what is displayed when we use command line "select * from <table name>" on
the console). I am wondering whether there are any existing sample which
is
easy and quick to learn with source codes.

thanks in advance,
George

An excellent tuorial with examples are here:
http://java.sun.com/docs/books/tutorial/jdbc/index.html

HTH

Paul Hamaker
Guest

Posted: Thu Feb 23, 2006 9:12 am Post subject: Re: Simple question about using JDBC to access DB2

servlet: http://javalessons.com/cgi-bin/jsp/jsp-j2ee-servlets.cgi?1cd=sdq&sid=ao789 jsp: http://javalessons.com/cgi-bin/jsp/jsp-j2ee-servlets.cgi?1cd=jdq&sid=ao789 -------------------- Paul Hamaker, SEMM, teaching ICT since 1987 http://javalessons.com

Rhino
Guest

Posted: Thu Feb 23, 2006 7:12 pm Post subject: Re: Simple question about using JDBC to access DB2

"George3" <u19006@uwe> wrote in message news:5c49895559d7a@uwe...

Quote:

The example application below is pretty close to what you want: it uses
JDBC to access DB2 and writes the contents of a small sample table to a
report that is written in HTML.

I strongly suspect that what you'd _really_ like to see is a servlet that
displays the output on a live web page. I don't have a suitable servlet
handy and I don't have time to write one for you but I think the application
below will show you a lot of what you want to know; I'll leave it to you to
figure out how to do the same thing in a servlet.

Please note that the example is written and tested for DB2 Version 8 for
Unix, Linux and Windows and uses the new Universal Type 4 JDBC driver. You
would only need to make small changes to get this code to work in older
versions of DB2 for Unix, Linux and Windows or to use other JDBC drivers.
For convenience, the program uses the Sample database which should already
be present in your copy of DB2: if it is not there, simply run the db2sampl
command to create the Sample database. You will almost certainly have to
change the following constants to work with your system:
- the values of the schema name ("RHINO") in the DEMO_TABLE and SOURCE_TABLE
constants
- the values of the OUTPUT_FILE_PATH, LOGIN_NAME, and PASSWORD constants

Here's the code:

=================================================================
package com.foo.db2v8;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Locale;

/**
* This class demonstrates various typical statements that could be used to
access a DB2
* database from a Java program via JDBC.
*
* <p>
* This program:
* </p>
* <ul>
* <li>loads the JDBC Driver used to access DB2. In order for the driver to
be
* loaded successfully, the following files, found in the SQLLIB\java
directory,
* need to be visible to the compiler during the compile of this program:
* db2java.zip; db2jcc.jar; and db2jcc_license_cu.jar.</li>
* <li>connects to the DB2 Sample database</li>
* <li>creates a new table in the DB2 Sample database based on an existing
* table in the same database</li>
* <li>grants all table privileges on the new table to everyone</li>
* <li>copies the contents of the original table to the new table</li>
* <li>creates one additional row in the new table</li>
* <li>queries the new table to see a subset of the rows</li>
* <li>updates several rows of the new table via a positioned update</li>
* <li>updates several rows of the new table via a direct update</li>
* <li>deletes several rows of the new table via a positioned delete</li>
* <li>deletes several rows of the new table via a direct delete</li>
* <li>drops the new table</li>
* <li>closes the database connection</li>
* </ul>
* </p>
*
* <p>
* All normal output from this program is written to an HTML page; all
errors are written to the
* console. A real program might write normal output to a GUI (Graphical
User Interface) or a
* different file format.
* </p>
*
* <p>
* This program uses a mix of java.sql.Statements and
java.sql.PreparedStatements. The latter are
* allowed to use parameter markers ('?' symbol) to represent variables but
JDBC places
* restrictions on where parameter markers can be within the statement. Both
Statements and
* PreparedStatements are allowed to use String concatenation: this allows
developers to put
* variables in places where parameter markers are not permitted; this
program demonstrates
* that technique.
* </p>
*
* <p>
* This program assumes that no nulls are sent or returned by any SQL
statement.
* Real programs are likely to need to receive or send nulls.
* </p>
*
* <p>
* Error handling is used throughout the program to ensure that the program
* terminates with a meaningful message if something didn't work correctly.
The
* following try/catch block is very useful in obtaining diagnostic
information
* from JDBC, which gets it from your database:
* <xmp>
* try {
* <your SQL statement>
* }
* catch (SQLException sql_excp) {
* System.err.println("SQLState: " + sql_excp.getSQLState());
* System.err.println("SQLCode: " + sql_excp.getErrorCode());
* System.err.println("Message: " + sql_excp.getMessage());
* }
* </xmp>
* However, a real program is likely to have more sophisticated error
handling that displays
* messages to the user via a GUI and/or logs the errors.
* </p>
*
* <p>
* A single connection and a single thread is used for the entire life of
this
* program. In a multi-user environment, a real program might use a
connection
* pool to ensure sufficient users could run this program simultaneously. If
the
* program had to do several things in parallel, additional threads could be
* used within each instance of the program.
* </p>
*
* <p>
* This program turns "autocommit" off and does all commits manually. Other
* approaches are possible and may be preferable, depending on your
* circumstances.
* </p>
*/
public class JDBC03 {

/*
*
* Class variables.
*
*/
final String CLASS_NAME = getClass().getName();

/**
* The path to the output directory.
*/
private static final String OUTPUT_FILE_PATH =
"e:/eclipse/workspace/DB2/output/"; //$NON-NLS-1$

/**
* The name of the file which will contain the HTML version of the
résumé.
*/
private static final String OUTPUT_FILE_NAME = "report.html";
//$NON-NLS-1$

/**
* The name of the JDBC driver that this program will use.
*/
private static final String JDBC_DRIVER_NAME =
"com.ibm.db2.jcc.DB2Driver"; //$NON-NLS-1$

/**
* The name of the database used by this program.
*/
private static final String DATABASE_NAME = "Sample"; //$NON-NLS-1$

/**
* The userid to use for logging on to the database server.
*/
private static final String LOGIN_NAME = "rhino"; //$NON-NLS-1$

/**
* The password to use for logging on to the database server.
*/
private static final String PASSWORD = "rhino"; //$NON-NLS-1$

/**
* The name of the new table which the the program will create and later
drop.
*/
private static final String DEMO_TABLE = "RHINO.PERSONNEL";
//$NON-NLS-1$

/**
* The name of the Sample database table which this program will clone.
*/
private static final String SOURCE_TABLE = "RHINO.EMPLOYEE";
//$NON-NLS-1$

/**
* The department number whose rows will be used in this program.
*/
private static final String DEMO_DEPARTMENT_NUMBER = "D21";
//$NON-NLS-1$

/**
* The new Salary amount given to employees whose bonus exceeds the
minimum bonus during
* the positioned update.
*/
private static final double NEW_SALARY = 50000.00; //$NON-NLS-1$

/**
* Employees who earn more than this amount will qualify for a new
salary during the
* positioned update and to be deleted in the positioned delete.
*/
private static final BigDecimal MINIMUM_BONUS = new BigDecimal(450.00);

/**
* The number of decimal places to show in currency amounts.
*/
private static final int MAX_DECIMAL_PLACES = 2;
/*
*
* Instance variables.
*
*/
/**
* The database connection used by this program.
*/
Connection conn01 = null;

/**
* The writer used by this program.
*/
PrintWriter printWriter = null;

/**
* The format to use when displaying currency amounts outside of HTML
tables; within tables,
* currency amounts will be shown in the normal DB2 format.
*/
NumberFormat currencyFormat = null;

/**
* main method for this application.
*/
public static void main(String[] args) {

/* Validate the command-line arguments, if any. */
if (args.length != 0) {
System.err.println("This program should not receive any
command-line arguments but it received these " //$NON-NLS-1$
+ args.length + " arguments: "); //$NON-NLS-1$
for (String oneArg : args) System.err.println(" " + oneArg);
//$NON-NLS-1$
System.err.println("These arguments were ignored.");
//$NON-NLS-1$
}

new JDBC03();
}

/**
* Constructor for this class.
*/
public JDBC03() {

/* Delete the old version of the output file. */
deleteFile(OUTPUT_FILE_PATH + OUTPUT_FILE_NAME);

/* Create the file that will contain the output from this program.
*/
this.printWriter = openOutputFile(OUTPUT_FILE_PATH +
OUTPUT_FILE_NAME);

/* Write the initial lines of the report. */
this.printWriter.println("<html>"); //$NON-NLS-1$
this.printWriter.println("<body>"); //$NON-NLS-1$

this.printWriter.println("<h1>Welcome to " + this.CLASS_NAME +
"!</h1>"); //$NON-NLS-1$ //$NON-NLS-2$

/* Load the JDBC driver and connect to the database. */
loadDriver();
connectToDatabase();

/* Create a formatter for currency amounts. */
this.currencyFormat =
NumberFormat.getCurrencyInstance(Locale.getDefault());
this.currencyFormat.setMaximumFractionDigits(MAX_DECIMAL_PLACES);

/*
* Create the demonstration table, grant privileges to it, populate
the table, and
* display the contents of the new table.
*/
createTable();
grantPrivileges();
copyMultipleRows();
queryTable();

/* Add a single row to the table and verify the contents of the
table. */
insertOneRow();
queryTable();

/* Do a positioned update and verify the contents of the table. */
positionedUpdate();
queryTable();

/* Do a direct update and verify the contents of the table. */
directUpdate();
queryTable();

/* Do a positioned delete and verify the contents of the table. */
positionedDelete();
queryTable();

/* Do a direct delete and verify the contents of the table. */
directDelete();
queryTable();

/* Drop the table and close the database connection. */
dropTable();
closeConnection();

/* Write the conclusing lines of the report. */
this.printWriter.println("</body>"); //$NON-NLS-1$
this.printWriter.println("</html>"); //$NON-NLS-1$

/* Close the report. */
closeOutputFile(this.printWriter);

System.out.println("The report was written to " + OUTPUT_FILE_PATH +
OUTPUT_FILE_NAME + "."); //$NON-NLS-1$ //$NON-NLS-2$
}

/**
* Load the JDBC driver.
*/
private void loadDriver() {

String METHOD_NAME = "loadDriver()"; //$NON-NLS-1$

this.printWriter.println("<h4>Load the JDBC driver....</h4>");
//$NON-NLS-1$

/* Load the JDBC driver. */
try {
Class.forName(JDBC_DRIVER_NAME);
} catch (ClassNotFoundException cnf_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered ClassNotFoundException while
attempting to load JDBC driver " //$NON-NLS-1$
+ JDBC_DRIVER_NAME + ". Error: " + cnf_excp);
//$NON-NLS-1$
cnf_excp.printStackTrace();
System.exit(16);
}
}

/**
* Get a connection to the database.
*
*/
private void connectToDatabase() {

String METHOD_NAME = "connectToDatabase()"; //$NON-NLS-1$

this.printWriter.println("<h4>Connect to the database....</h4>");
//$NON-NLS-1$

/* Initialize the variables used to get the connection. */
String url = "jdbc:db2:" + DATABASE_NAME; //$NON-NLS-1$

/* Connect to the database. */
try {
this.conn01 = DriverManager.getConnection(url, LOGIN_NAME,
PASSWORD);
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException on connect to URL " + url
+ ". Error: " //$NON-NLS-1$ //$NON-NLS-2$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}

/* Set autocommit off. */
try {
this.conn01.setAutoCommit(false);
this.printWriter.println("<p>Value of autocommit: " +
this.conn01.getAutoCommit() + "</p>"); //$NON-NLS-1$ //$NON-NLS-2$
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException on attempt to turn
autocommit off. Error: " //$NON-NLS-1$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}

/* Obtain JDBC version. */
try {
DatabaseMetaData dbMeta = this.conn01.getMetaData();
int jdbcMajorVersion = dbMeta.getJDBCMajorVersion();
int jdbcMinorVersion = dbMeta.getJDBCMinorVersion();
this.printWriter.println("<p>JDBC Version: " + jdbcMajorVersion +
"." + jdbcMinorVersion + "</p>"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME +
//$NON-NLS-1$
" - Encountered SQLException on attempt to turn
autocommit off. Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Create the demonstration table.
*/
private void createTable() {

String METHOD_NAME = "createTable()"; //$NON-NLS-1$

this.printWriter.println("<h4>Create the " + DEMO_TABLE + "
table....</h4>"); //$NON-NLS-1$ //$NON-NLS-2$

/*
* The table that we want to create may already exist from a
previous
* execution of this program. Drop the table, if it exists, so that
we
* can start fresh.
*/
dropTable();

/*
* Create a new, empty demonstration table that will be a clone of
the
* source table.
*/
String createTableSQL = "create table " + DEMO_TABLE + " like " +
SOURCE_TABLE; //$NON-NLS-1$ //$NON-NLS-2$
Statement createTableStmt = null;
try {
createTableStmt = this.conn01.createStatement();
int numRows = createTableStmt.executeUpdate(createTableSQL);
this.printWriter.println("<p>" + this.CLASS_NAME + "." +
METHOD_NAME + " - return code = " + numRows + "</p>"); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException attempting to create " +
DEMO_TABLE + " from " //$NON-NLS-1$ //$NON-NLS-2$
+ SOURCE_TABLE + ". Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Dispose of the statement and commit. */
try {
createTableStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing
statement or committing. Error: " //$NON-NLS-1$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Grant privileges on the demonstration table.
*
*/
private void grantPrivileges() {

String METHOD_NAME = "grantPrivileges()"; //$NON-NLS-1$

this.printWriter.println("<h4>Grant privileges on the " + DEMO_TABLE
+ " table....</h4>"); //$NON-NLS-1$ //$NON-NLS-2$

/* Grant all table privileges on the demonstration table to PUBLIC.
*/
String grantPrivilegesSQL = "grant all on " + DEMO_TABLE + " to
PUBLIC"; //$NON-NLS-1$ //$NON-NLS-2$
Statement grantPrivilegesStmt = null;
try {
grantPrivilegesStmt = this.conn01.createStatement();
int numRows =
grantPrivilegesStmt.executeUpdate(grantPrivilegesSQL);
this.printWriter.println("<p>" + this.CLASS_NAME + "." +
METHOD_NAME + " - return code = " + numRows + "</p>"); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException attempting to grant
privileges on " //$NON-NLS-1$
+ DEMO_TABLE + " table. Error: " + sql_excp);
//$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Dispose of the statement and commit. */
try {
grantPrivilegesStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing
statement or committing. Error: " //$NON-NLS-1$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Populate the demonstration table.
*/
private void copyMultipleRows() {

String METHOD_NAME = "copyMultipleRows()"; //$NON-NLS-1$

this.printWriter.println("<h4>Copy the rows from the " +
SOURCE_TABLE + " table to the " //$NON-NLS-1$ //$NON-NLS-2$
+ DEMO_TABLE + " table...</h4>"); //$NON-NLS-1$

/* Populate the demonstration table from the source table. */
String copyRowsSQL = "insert into " + DEMO_TABLE + " select * from "
+ SOURCE_TABLE; //$NON-NLS-1$ //$NON-NLS-2$
Statement copyRowsStmt = null;
try {
copyRowsStmt = this.conn01.createStatement();
int numRows = copyRowsStmt.executeUpdate(copyRowsSQL);
this.printWriter.println("<p>" + this.CLASS_NAME + "." +
METHOD_NAME + " - numRows = " + numRows + "</p>"); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException attempting to copy " +
SOURCE_TABLE //$NON-NLS-1$
+ " rows into " + DEMO_TABLE + " table. Error: " +
sql_excp); //$NON-NLS-1$ //$NON-NLS-2$
sql_excp.printStackTrace();
System.exit(16);
}

/* Dispose of the statement and commit. */
try {
copyRowsStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing
statement or committing. Error: " //$NON-NLS-1$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Insert an additional row in the demonstration table.
*/
private void insertOneRow() {

String METHOD_NAME = "insertOneRow()"; //$NON-NLS-1$

this.printWriter.println("<h4>Add another row to the " + DEMO_TABLE
+ " table....</h4>"); //$NON-NLS-1$ //$NON-NLS-2$

/* Create a single new row in the demonstration table. */
String insertRowSql =
"insert into " + DEMO_TABLE + " " //$NON-NLS-1$ //$NON-NLS-2$
+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
//$NON-NLS-1$
PreparedStatement insertRowStmt = null;
try {
insertRowStmt = this.conn01.prepareStatement(insertRowSql);
insertRowStmt.setString(1, "999999"); //$NON-NLS-1$
insertRowStmt.setString(2, "John"); //$NON-NLS-1$
insertRowStmt.setString(3, "A"); //$NON-NLS-1$
insertRowStmt.setString(4, "Doe"); //$NON-NLS-1$
insertRowStmt.setString(5, "D21"); //$NON-NLS-1$
insertRowStmt.setString(6, "1234"); //$NON-NLS-1$
insertRowStmt.setDate(7, toSqlDate("1974-08-23")); //$NON-NLS-1$
insertRowStmt.setString(8, "Fieldrep"); //$NON-NLS-1$
insertRowStmt.setInt(9, 16);
insertRowStmt.setString(10, "M"); //$NON-NLS-1$
insertRowStmt.setDate(11, toSqlDate("1944-07-20"));
//$NON-NLS-1$
insertRowStmt.setBigDecimal(12, new BigDecimal(30000.00));
insertRowStmt.setBigDecimal(13, new BigDecimal(500.00));
insertRowStmt.setBigDecimal(14, new BigDecimal(2000.00));
int numRowsAdded = insertRowStmt.executeUpdate();
this.printWriter.println("<p>" + this.CLASS_NAME + "." +
METHOD_NAME + " - numRowsAdded = " + numRowsAdded + "</p>"); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException attempting to insert
single row into " //$NON-NLS-1$
+ DEMO_TABLE + " table. Error: " + sql_excp);
//$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Dispose of the statement and commit. */
try {
insertRowStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing
statement or committing. Error: " //$NON-NLS-1$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Display the rows for the designated department number in the
demonstration table.
*/
private void queryTable() {

String METHOD_NAME = "queryTable()"; //$NON-NLS-1$

this.printWriter.println("<p>Current Department " +
DEMO_DEPARTMENT_NUMBER + " rows in the table: </p>"); //$NON-NLS-1$
//$NON-NLS-2$
String queryTableSql = "select lastname, workdept, salary, bonus,
hiredate " //$NON-NLS-1$
+ "from " + DEMO_TABLE + " " //$NON-NLS-1$ //$NON-NLS-2$
+ "where workdept = ?"; //$NON-NLS-1$

/*
* Query the demonstration table to get information about employees
in the designated
* department.
*/
PreparedStatement queryTableStmt = null;
ResultSet rs01 = null;
try {
queryTableStmt = this.conn01.prepareStatement(queryTableSql);
queryTableStmt.setString(1, DEMO_DEPARTMENT_NUMBER);
rs01 = queryTableStmt.executeQuery();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while trying to get
information from " //$NON-NLS-1$
+ DEMO_TABLE + " table. Error: " + sql_excp);
//$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Print the header for the table. */
this.printWriter.println("<table border=\"1\" width=\"580\">");
//$NON-NLS-1$
this.printWriter.println("<tr>"); //$NON-NLS-1$
this.printWriter.println("<th>Lastname</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Workdept</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Salary</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Bonus</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Hiredate</th>"); //$NON-NLS-1$
this.printWriter.println("</tr>"); //$NON-NLS-1$

/* Initialize the host variables used for handling the result set.
*/
String lastname = null;
String workdept = null;
BigDecimal salary = null;
BigDecimal bonus = null;
Date hiredate = null;

/* Print each line of the result set. */
try {
while (rs01.next()) {
lastname = rs01.getString(1);
workdept = rs01.getString(2);
salary = rs01.getBigDecimal(3);
bonus = rs01.getBigDecimal(4);
hiredate = rs01.getDate(5);
this.printWriter.println("<tr>"); //$NON-NLS-1$
this.printWriter.println("<td>" + lastname + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + workdept + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + salary + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + bonus + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + hiredate + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("</tr>"); //$NON-NLS-1$
}
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while reading " +
DEMO_TABLE //$NON-NLS-1$
+ " table. Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Close the HTML table. */
this.printWriter.println("</table>"); //$NON-NLS-1$

/* Close the result set, dispose of the statement, and commit. */
try {
rs01.close();
queryTableStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing " +
DEMO_TABLE //$NON-NLS-1$
+ " result set, closing statement, or committing. Error:
" + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Do a positioned update in the demonstration table.
*/
private void positionedUpdate() {

String METHOD_NAME = "positionedUpdate()"; //$NON-NLS-1$

this.printWriter.println("<h4>Positioned update of salaries in the "
+ DEMO_TABLE //$NON-NLS-1$
+ " table that refer to department " +
DEMO_DEPARTMENT_NUMBER + "....</h4>"); //$NON-NLS-1$ //$NON-NLS-2$

this.printWriter.println("<p>Any employees of department " +
DEMO_DEPARTMENT_NUMBER //$NON-NLS-1$
+ " whose bonus is more than " +
this.currencyFormat.format(MINIMUM_BONUS) //$NON-NLS-1$
+ " will have their salary changed to " +
this.currencyFormat.format(NEW_SALARY) + "."); //$NON-NLS-1$ //$NON-NLS-2$

String queryTableSql = "select lastname, workdept, salary, bonus,
hiredate " //$NON-NLS-1$
+ "from " + DEMO_TABLE + " " //$NON-NLS-1$ //$NON-NLS-2$
+ "where workdept = ? " //$NON-NLS-1$
+ "for update"; //$NON-NLS-1$
String positionedUpdateSql = null;

/*
* The query will display all of the rows that belong to the
designated department.
* The update will set a new salary for only those rows where the
bonus is above an
* arbitrary amount.
*/
PreparedStatement queryTableStmt = null;
PreparedStatement positionedUpdateStmt = null;
ResultSet rs01 = null;
try {
queryTableStmt = this.conn01.prepareStatement(queryTableSql);
queryTableStmt.setString(1, DEMO_DEPARTMENT_NUMBER);
rs01 = queryTableStmt.executeQuery();
String cursorName = rs01.getCursorName();
positionedUpdateSql =
"update " + DEMO_TABLE + " " //$NON-NLS-1$ //$NON-NLS-2$
+ "set salary = ? " //$NON-NLS-1$
+ "where current of " + cursorName; //$NON-NLS-1$
positionedUpdateStmt =
this.conn01.prepareStatement(positionedUpdateSql);
positionedUpdateStmt.setBigDecimal(1, new
BigDecimal(NEW_SALARY));

} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while trying to get
information from " //$NON-NLS-1$
+ DEMO_TABLE + " table. Error: " + sql_excp);
//$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Print the header for the table. */
this.printWriter.println("<p>All rows in designated department
before positioned update:</p>"); //$NON-NLS-1$
this.printWriter.println("<table border=\"1\" width=\"580\">");
//$NON-NLS-1$
this.printWriter.println("<tr>"); //$NON-NLS-1$
this.printWriter.println("<th>Lastname</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Workdept</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Salary</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Bonus</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Hiredate</th>"); //$NON-NLS-1$
this.printWriter.println("</tr>"); //$NON-NLS-1$

/* Initialize the host variables used for handling the result set.
*/
String lastname = null;
String workdept = null;
BigDecimal salary = null;
BigDecimal bonus = null;
Date hiredate = null;

/* Print each line of the result set. */
try {
while (rs01.next()) {
lastname = rs01.getString(1);
workdept = rs01.getString(2);
salary = rs01.getBigDecimal(3);
bonus = rs01.getBigDecimal(4);
hiredate = rs01.getDate(5);
this.printWriter.println("<tr>"); //$NON-NLS-1$
this.printWriter.println("<td>" + lastname + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + workdept + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + salary + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + bonus + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + hiredate + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("</tr>"); //$NON-NLS-1$
/*
* If the individual's bonus is greater than the minimum
amount, do a positioned
* update of that one row.
*/
if (bonus.compareTo(MINIMUM_BONUS) > 0) {
positionedUpdateStmt.executeUpdate();
}
}
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while reading " +
DEMO_TABLE //$NON-NLS-1$
+ " table. Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Close the HTML table. */
this.printWriter.println("</table>"); //$NON-NLS-1$

/* Close the result set, dispose of the statement, and commit. */
try {
rs01.close();
queryTableStmt.close();
positionedUpdateStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing " +
DEMO_TABLE //$NON-NLS-1$
+ " result set, closing statements, or committing.
Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Update the demonstration table directly, i.e. don't use a cursor and
a
* positioned update.
*/
private void directUpdate() {

String METHOD_NAME = "directUpdate()"; //$NON-NLS-1$

this.printWriter.println("<h4>Update directly the salaries in the
rows in the " + DEMO_TABLE //$NON-NLS-1$
+ " table that refer to department " +
DEMO_DEPARTMENT_NUMBER + "....</h4>"); //$NON-NLS-1$ //$NON-NLS-2$

this.printWriter.println("<p>All employees in the designated
department will have their salaries adjusted.</p>"); //$NON-NLS-1$

/* Update rows in the demonstration table. */
String updateRowsSql =
"update " + DEMO_TABLE + " " //$NON-NLS-1$ //$NON-NLS-2$
+ "set salary = salary * 1.5 " //$NON-NLS-1$
+ "where workdept = ?"; //$NON-NLS-1$
PreparedStatement updateRowsStmt = null;
try {
updateRowsStmt = this.conn01.prepareStatement(updateRowsSql);
updateRowsStmt.setString(1, DEMO_DEPARTMENT_NUMBER);
int numRows = updateRowsStmt.executeUpdate();
this.printWriter.println("<p>" + this.CLASS_NAME + "." +
METHOD_NAME + " - numRows = " + numRows + "</p>"); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException attempting to update rows
in " + DEMO_TABLE //$NON-NLS-1$
+ " table. Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Dispose of the statement and commit. */
try {
updateRowsStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing
statement or committing. Error: " //$NON-NLS-1$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Do a positioned delete in the demonstration table.
*/
private void positionedDelete() {

String METHOD_NAME = "positionedDelete()"; //$NON-NLS-1$

this.printWriter.println("<h4>Positioned delete of rows in the " +
DEMO_TABLE //$NON-NLS-1$
+ " table that refer to department " +
DEMO_DEPARTMENT_NUMBER + "....</h4>"); //$NON-NLS-1$ //$NON-NLS-2$"

this.printWriter.println("<p>Any employees of department " +
DEMO_DEPARTMENT_NUMBER //$NON-NLS-1$
+ " whose bonus is more than " +
this.currencyFormat.format(MINIMUM_BONUS) //$NON-NLS-1$
+ " will be deleted."); //$NON-NLS-1$

String queryTableSql =
"select lastname, workdept, salary, bonus, hiredate "
//$NON-NLS-1$
+ "from " + DEMO_TABLE + " " //$NON-NLS-1$ //$NON-NLS-2$
+ "where workdept = ? " //$NON-NLS-1$
+ "for update"; //$NON-NLS-1$
String positionedDeleteSQL = null;

/*
* The query will display the rows that will be considered by the
delete.
* The delete will examine all the rows shown in the SELECT but only
* remove the rows where the bonus is above the minimum amount.
*/
PreparedStatement queryTableStmt = null;
Statement positionedDeleteStmt = null;
ResultSet rs01 = null;
try {
queryTableStmt = this.conn01.prepareStatement(queryTableSql);
queryTableStmt.setString(1, DEMO_DEPARTMENT_NUMBER);
positionedDeleteStmt = this.conn01.createStatement();
rs01 = queryTableStmt.executeQuery();
String cursorName = rs01.getCursorName();
positionedDeleteSQL =
"delete from " + DEMO_TABLE + " " //$NON-NLS-1$
//$NON-NLS-2$
+ "where current of " + cursorName; //$NON-NLS-1$
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while trying to get
information from " //$NON-NLS-1$
+ DEMO_TABLE + " table. Error: " + sql_excp);
//$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Print the header for the table. */
this.printWriter.println("<p>All rows in designated department
before positioned delete:</p>"); //$NON-NLS-1$
this.printWriter.println("<table border=\"1\" width=\"580\">");
//$NON-NLS-1$
this.printWriter.println("<tr>"); //$NON-NLS-1$
this.printWriter.println("<th>Lastname</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Workdept</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Salary</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Bonus</th>"); //$NON-NLS-1$
this.printWriter.println("<th>Hiredate</th>"); //$NON-NLS-1$
this.printWriter.println("</tr>"); //$NON-NLS-1$

/* Initialize the host variables used for handling the result set.
*/
String lastname = null;
String workdept = null;
BigDecimal salary = null;
BigDecimal bonus = null;
Date hiredate = null;

/* Print each line of the result set. */
try {
while (rs01.next()) {
lastname = rs01.getString(1);
workdept = rs01.getString(2);
salary = rs01.getBigDecimal(3);
bonus = rs01.getBigDecimal(4);
hiredate = rs01.getDate(5);
this.printWriter.println("<tr>"); //$NON-NLS-1$
this.printWriter.println("<td>" + lastname + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + workdept + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + salary + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + bonus + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("<td>" + hiredate + "</td>");
//$NON-NLS-1$ //$NON-NLS-2$
this.printWriter.println("</tr>"); //$NON-NLS-1$
/*
* If the individual's bonus is greater than the minimum
amount, do a positioned
* delete of that one row.
*/
if (bonus.compareTo(MINIMUM_BONUS) > 0) {
positionedDeleteStmt.executeUpdate(positionedDeleteSQL);
}
}
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while reading " +
DEMO_TABLE //$NON-NLS-1$
+ " table. Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Close the HTML table. */
this.printWriter.println("</table>"); //$NON-NLS-1$

/* Close the result set, dispose of the statement, and commit. */
try {
rs01.close();
queryTableStmt.close();
positionedDeleteStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing " +
DEMO_TABLE //$NON-NLS-1$
+ " result set, closing statements, or committing.
Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Delete rows from the demonstration table directly, i.e. don't use a
* cursor and a positioned delete.
*/
private void directDelete() {

String METHOD_NAME = "directDelete()"; //$NON-NLS-1$

this.printWriter.println("<h4>Delete directly the rows in the " +
DEMO_TABLE //$NON-NLS-1$
+ " table that refer to department " +
DEMO_DEPARTMENT_NUMBER + "....</h4>"); //$NON-NLS-1$ //$NON-NLS-2$

/* Delete rows in the demonstration table. */
String deleteRowsSql =
"delete from " + DEMO_TABLE + " " //$NON-NLS-1$ //$NON-NLS-2$
+ "where workdept = ?"; //$NON-NLS-1$
PreparedStatement deleteRowsStmt = null;
try {
deleteRowsStmt = this.conn01.prepareStatement(deleteRowsSql);
deleteRowsStmt.setString(1, DEMO_DEPARTMENT_NUMBER);
int numRows = deleteRowsStmt.executeUpdate();
this.printWriter.println("<p>" + this.CLASS_NAME + "." +
METHOD_NAME + " - numRows = " + numRows + "</p>"); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException attempting to delete rows
from " + DEMO_TABLE //$NON-NLS-1$
+ " table. Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}

/* Dispose of the statement and commit. */
try {
deleteRowsStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing
statement or committing. Error: " //$NON-NLS-1$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Drop the demonstration table.
*/
private void dropTable() {

String METHOD_NAME = "dropTable()"; //$NON-NLS-1$

/*
* Drop the table. If the table can't be dropped because it doesn't
* exist, report that and continue with the rest of the program. If
any
* other error occurs, report it and stop the program.
*/
String dropTableSQL = "drop table " + DEMO_TABLE; //$NON-NLS-1$
Statement dropTableStmt = null;
try {
dropTableStmt = this.conn01.createStatement();
int numRows = dropTableStmt.executeUpdate(dropTableSQL);
this.printWriter.println("<p>" + this.CLASS_NAME + "." +
METHOD_NAME + " - return code = " + numRows + "</p>"); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
} catch (SQLException sql_excp) {
if (sql_excp.getSQLState().equals("42704")) { //$NON-NLS-1$
this.printWriter.println("<p>" + this.CLASS_NAME + "." +
METHOD_NAME + " - Cannot drop " + DEMO_TABLE //$NON-NLS-1$ //$NON-NLS-2$
//$NON-NLS-3$
+ " table because it does not exist.</p>");
//$NON-NLS-1$
} else {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException attempting to drop "
+ DEMO_TABLE //$NON-NLS-1$
+ " table. Error: " + sql_excp); //$NON-NLS-1$
sql_excp.printStackTrace();
System.exit(16);
}
}

/* Dispose of the statement and commit. */
try {
dropTableStmt.close();
this.conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing
statement or committing. Error: " //$NON-NLS-1$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Close the database connection.
*/
private void closeConnection() {

String METHOD_NAME = "closeConnection()"; //$NON-NLS-1$

this.printWriter.println("<h4>Close the database
connection....</h4>"); //$NON-NLS-1$

/* Close the database connection. */
try {
this.conn01.close();
} catch (SQLException sql_excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME
//$NON-NLS-1$
+ " - Encountered SQLException while closing database
connection. Error: " //$NON-NLS-1$
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}

/**
* Deletes a specified file.
*
* @param fileName the name of the file to be deleted.
*/
private void deleteFile(String fileName) {

/* Delete the old version of the output file. */
File outfile = new File(fileName);
outfile.delete();
}

/**
* Opens a PrintWriter that will write a specific file.
*
* @param fileName the name of the file to be written.
* @return reference to PrintWriter that will write the file.
*/
private PrintWriter openOutputFile(String fileName) {

String METHOD_NAME = "openOutputFile()"; //$NON-NLS-1$

FileOutputStream fileOutputStream = null;
OutputStreamWriter outputStreamWriter = null;
PrintWriter localPrintWriter = null;

try {
fileOutputStream = new FileOutputStream(fileName, true);
} catch (FileNotFoundException excp) {
System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Tried to open file " + fileName + ". Error: " + excp); //$NON-NLS-1$
//$NON-NLS-2$ //$NON-NLS-3$
excp.printStackTrace();
return localPrintWriter;
}

outputStreamWriter = new OutputStreamWriter(fileOutputStream);
localPrintWriter = new PrintWriter(new
BufferedWriter(outputStreamWriter), true);

return localPrintWriter;
}

/**
* Flushes and closes a specific file.
*
* @param localPrintWriter the PrintWriter used to write the file.
*/
private void closeOutputFile(PrintWriter localPrintWriter) {

localPrintWriter.flush();
localPrintWriter.close();
}

/**
* Converts a date expressed as a String into a java.sql.Date.
*
* <p><i>Example:</i><br>
* <xmp>
* java.sql.Date mySqlDate = toSqlDate("2005-02-17"); //should return an
SQL date equivalent to the input ISO date.
* </xmp></p>
*
* <p><b>NOTE: </b>Although the input parameter expresses a date with
only a year, month and day,
* the date returned by this method includes a year, month, day, hour,
minute, second, and milliseconds.
* The date returned by this method will have the same year, month, and
day as the input parameters
* while the hours, minutes, seconds, and milliseconds will all be zero,
which effectively means
* midnight on the date (year, month, and day) in question.</p>
*
* @param date a date given in ISO (YYYY-MM-DD) format.
* @return java.sql.Date expressed with a default time of midnight.
*/
private java.sql.Date toSqlDate(String date) {

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//$NON-NLS-1$

java.util.Date utilDate = null;
try {
utilDate = sdf.parse(date);
} catch (ParseException p_excp) {
throw new IllegalArgumentException("Encountered ParseException
at offset " + p_excp.getErrorOffset() + " while attempting to convert the
String " + date + " to a java.sql.Date. Details: " + p_excp.getMessage());
//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}

java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());

return (sqlDate);
}

}
=================================================================

--
Rhino

trippy
Guest

Posted: Thu Feb 23, 2006 10:12 pm Post subject: Re: Simple question about using JDBC to access DB2

In article <NynLf.6691$XZ3.265478 (AT) news20 (DOT) bellglobal.com>, Rhino took the
hamburger, threw it on the grill, and I said "Oh wow"...

Quote:

"George3" <u19006@uwe> wrote in message news:5c49895559d7a@uwe...
Hello everyone,

I want to use JDBC to access DB2 database. I also want to display the
content
of a specific table (table name is configurable) into web page (just like
what is displayed when we use command line "select * from <table name>" on
the console). I am wondering whether there are any existing sample which
is
easy and quick to learn with source codes.

The example application below is pretty close to what you want: it uses
JDBC to access DB2 and writes the contents of a small sample table to a
report that is written in HTML.

I strongly suspect that what you'd _really_ like to see is a servlet that
displays the output on a live web page. I don't have a suitable servlet
handy and I don't have time to write one for you but I think the application
below will show you a lot of what you want to know; I'll leave it to you to
figure out how to do the same thing in a servlet.

Please note that the example is written and tested for DB2 Version 8 for
Unix, Linux and Windows and uses the new Universal Type 4 JDBC driver. You
would only need to make small changes to get this code to work in older
versions of DB2 for Unix, Linux and Windows or to use other JDBC drivers.
For convenience, the program uses the Sample database which should already
be present in your copy of DB2: if it is not there, simply run the db2sampl
command to create the Sample database. You will almost certainly have to
change the following constants to work with your system:
- the values of the schema name ("RHINO") in the DEMO_TABLE and SOURCE_TABLE
constants
- the values of the OUTPUT_FILE_PATH, LOGIN_NAME, and PASSWORD constants

Here's the code:

=================================================================
package com.foo.db2v8;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;

//import java.io.*;

Quote:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//import java.sql.*;

Quote:

import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

//import java.text.*;

Quote:

import java.util.Locale;

I'd probably import java.util.* but for one class, who cares, really.

And also, it's not like Rhino is wrong or anything, you absolutely can
import this way. Just makes it a little easier to read/less cluttered.

--
trippy
mhm31x9 Smeeter#29 WSD#30
sTaRShInE_mOOnBeAm aT HoTmAil dOt CoM

NP: "He-Man Woman Hater" -- Extreme

"Now, technology's getting better all the time and that's fine,
but most of the time all you need is a stick of gum, a pocketknife,
and a smile."

-- Robert Redford "Spy Game"

Rhino
Guest

Posted: Fri Feb 24, 2006 1:12 am Post subject: Re: Simple question about using JDBC to access DB2

"trippy" <trippy (AT) spam (DOT) sucks> wrote in message
news:MPG.1e67ed06715c387598a046 (AT) news (DOT) alt.net...

Quote:

In article <NynLf.6691$XZ3.265478 (AT) news20 (DOT) bellglobal.com>, Rhino took the
hamburger, threw it on the grill, and I said "Oh wow"...

"George3" <u19006@uwe> wrote in message news:5c49895559d7a@uwe...
Hello everyone,

I want to use JDBC to access DB2 database. I also want to display the
content
of a specific table (table name is configurable) into web page (just
like
what is displayed when we use command line "select * from <table name>"
on
the console). I am wondering whether there are any existing sample
which
is
easy and quick to learn with source codes.

The example application below is pretty close to what you want: it uses
JDBC to access DB2 and writes the contents of a small sample table to a
report that is written in HTML.

I strongly suspect that what you'd _really_ like to see is a servlet that
displays the output on a live web page. I don't have a suitable servlet
handy and I don't have time to write one for you but I think the
application
below will show you a lot of what you want to know; I'll leave it to you
to
figure out how to do the same thing in a servlet.

Please note that the example is written and tested for DB2 Version 8 for
Unix, Linux and Windows and uses the new Universal Type 4 JDBC driver.
You
would only need to make small changes to get this code to work in older
versions of DB2 for Unix, Linux and Windows or to use other JDBC drivers.
For convenience, the program uses the Sample database which should
already
be present in your copy of DB2: if it is not there, simply run the
db2sampl
command to create the Sample database. You will almost certainly have to
change the following constants to work with your system:
- the values of the schema name ("RHINO") in the DEMO_TABLE and
SOURCE_TABLE
constants
- the values of the OUTPUT_FILE_PATH, LOGIN_NAME, and PASSWORD constants

Here's the code:

=================================================================
package com.foo.db2v8;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;

//import java.io.*;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//import java.sql.*;

import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

//import java.text.*;

import java.util.Locale;

I'd probably import java.util.* but for one class, who cares, really.

And also, it's not like Rhino is wrong or anything, you absolutely can
import this way. Just makes it a little easier to read/less cluttered.

I agree that it's less cluttered to simply use the wild cards in the imports

but my IDE, Eclipse, automatically generates those specific imports. Also,
I'm pretty sure I've seen posts in the Java newsgroups that insist that you
get better runtime performance if you import only the specific classes you
want and stay away from wild cards.

Your mileage may vary but that's why I don't used the wild cards.

--
Rhino