Connect to Database like ( DB2 , Sybase ,Oracle,SQL server ) using JDBC

Complete Code Example : Connect to Database like ( DB2 , Sybase ,Oracle,SQL server ) using JDBC

//
// DISCLAIMER OF WARRANTIES.
// The following [enclosed] code is sample code
// This sample code is not part of any standard
// product and is provided to you solely for the purpose of assisting
// you in the development of your applications. The code is provided
// "AS IS". Authour MAKES NO WARRANTIES, EXPRESS OR IMPLIED, INCLUDING BUT
// NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
// FOR A PARTICULAR PURPOSE, REGARDING THE FUNCTION OR PERFORMANCE OF
// THIS CODE. THIS CODE MAY CONTAIN ERRORS. Author shall not be liable
// for any damages arising out of your use of the sample code, even
// if it has been advised of the possibility of such damages.
//

import java.sql.*;

import javax.sql.*;

import java.io.*;
import java.awt.*;
import java.awt.event.*;

public class jdbctest {

public String driverclassname;
public String DBURL;
public String dbprefix = "jdbc:";
public Connection con;
public Statement stmt;
public BufferedReader instream;
public long operationTimer;

public String response = "0";

public String globaluid = null;
public String globalpwd = null;

public String dbhostname;
public String dbport;
public String dbname;

public ClassLoader loader=null;
public Class dsClass=null;
ConnectionPoolDataSource ds=null;

public static final int DATASOURCECONNECTIONS=10;


// Capture window events...
static private class FrameCloser extends WindowAdapter {

public void windowClosing(WindowEvent we) {
System.out.println("windowClosing..." + we);
we.getWindow().dispose();
System.exit(0);
}
}

// Capture key events...
static private class KeyInterceptor implements KeyListener {
public void keyTyped(java.awt.event.KeyEvent ke) {
}
public void keyPressed(java.awt.event.KeyEvent ke) {
}
public void keyReleased(java.awt.event.KeyEvent ke) {
// System.out.println("keyReleased..." + ke);
if (ke.getKeyCode() == KeyEvent.VK_ENTER) {
TextField tf = (TextField) ke.getComponent();
synchronized (tf) {
tf.notify();
}
} // if enter key is pressed
}
}

public static void main(String args[]) {
System.out.println("JDBC Test starting...");

jdbctest ct = new jdbctest();
ct.init(args); // Loads jdbc driver
ct.connect(); // sets connection
if (ct.con == null) {
System.out.println("Connection to " + ct.DBURL + " failed!");
System.exit(33);
}

System.out.println("Connection Successful: " + ct.con);
System.out.println(
"Connection took " + ct.operationTimer + " milliseconds to complete");
try {
System.out.println("AutoCommit is: " + ct.con.getAutoCommit());
} catch (SQLException s) {
s.printStackTrace();
System.out.println("Error code is: " + s.getErrorCode());
System.out.println("SQLState is: " + s.getSQLState());
System.exit(33);
}
do {
try {
if (!(ct.response.equals("11")) && !(ct.response.equals("10")))
{System.out.println("Create new statement...");
ct.stmt = ct.con.createStatement();}
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
System.exit(33);
}
} while (ct.process());
try {
if (ct.stmt != null) ct.stmt.close();
ct.con.close();
} catch (Exception e) {
e.printStackTrace();
}

// System.exit ???
System.exit(33);

}

public void init(String args[]) {
instream = new BufferedReader((new InputStreamReader(System.in)));

while (driverclassname == null) {
System.out.println(
"Please respond:\n\t1 - For DB2\n\t2 - For DB2/Pure Network Client\n\t3 - For Oracle (thin) \n\t4 -
For Oracle (oci8)\n\t5 - For Informix\n\t6 - For Sybase\n\t7 - For Cloudscape - (Local)\n\t8 - For Cloudscape - (RMI)\n\t9 -
For SQLServer (Microsoft Driver)\n\t10 - For SQLServer (SequeLink driver)\n\t11 - For SQLServer (WebSphere embedded Connect
JDBC driver)\n\t12 - For SQLServer(Data Direct Technologies Connect JDBC driver)");
response = readLine();
if (response.equals("1")) {
driverclassname = "COM.ibm.db2.jdbc.app.DB2Driver";
dbprefix += "db2:";
} else
if (response.equals("2")) {
driverclassname = "COM.ibm.db2.jdbc.net.DB2Driver";
dbprefix += "db2:";
} else
if (response.equals("3")) { //Thin Driver
driverclassname = "oracle.jdbc.driver.OracleDriver";
dbprefix += "oracle:thin:";
} else
if (response.equals("4")) { //Thick Driver
driverclassname = "oracle.jdbc.driver.OracleDriver";
dbprefix += "oracle:oci8:";
} else
if (response.equals("5")) {
driverclassname = "com.informix.jdbc.IfxDriver";
dbprefix += "informix-sqli:";
} else
if (response.equals("6")) {
driverclassname = "com.sybase.jdbc2.jdbc.SybDriver";
dbprefix += "sybase:";
} else
if (response.equals("7")) {
driverclassname = "COM.cloudscape.core.JDBCDriver";
dbprefix += "cloudscape:";
} else
if (response.equals("8")) {
driverclassname = "COM.cloudscape.core.RmiJdbcDriver";
dbprefix += "cloudscape:rmi:";
} else
if (response.equals("9")) {
driverclassname = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
dbprefix += "microsoft:sqlserver:";
} else
if (response.equals("10")) {
// driverclassname = "com.merant.sequelink.jdbcx.datasource.SequeLinkDataSource";
driverclassname = "com.ddtek.jdbcx.sequelink.SequeLinkDataSource";

dbprefix += "sequelink:";
} else
if (response.equals("11")) {
driverclassname = "com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource";
dbprefix += "sequelink:";
} else
if (response.equals("12")) {
driverclassname = "com.ddtek.jdbcx.sqlserver.SQLServerDataSource";
dbprefix += "sequelink:";
} else
System.out.println("Invalid selection: specify an option 1-12.");
}


System.out.println("Loading jdbc driver: " + driverclassname);
try {
if (Integer.parseInt(response) < DATASOURCECONNECTIONS)
{
Class.forName(driverclassname);
System.out.println(driverclassname + " was loaded successfully");
}
else
{loader = Thread.currentThread().getContextClassLoader();
if (loader == null)
loader = ClassLoader.getSystemClassLoader();

dsClass=loader.loadClass(driverclassname);
}

} catch (Exception e) {
e.printStackTrace();
System.out.println(
"Please modify you classpath to include the class: " + driverclassname);

System.out.println(
"To be sure that you have this class in your classpath, issue:\n javap "
+ driverclassname);
System.exit(33);
}

}

public void connect() {
while ((DBURL == null) && (Integer.parseInt(response) < DATASOURCECONNECTIONS)) {
System.out.println("Please enter connection URL info, e.g:");

if (driverclassname.endsWith(".app.DB2Driver")) {
System.out.println("jdbc:db2:dbname or dbname");
} else
if (driverclassname.endsWith(".net.DB2Driver")) {
System.out.println(
"jdbc:db2://:/ or //:/");
} else
if (driverclassname.endsWith(".OracleDriver")) {

if (response.equals("4")) {
System.out.println(
"jdbc:oracle:oci8:@:: or @::");
} else {
System.out.println(
"jdbc:oracle:thin:@:: or @::");
}

} else
if (driverclassname.endsWith(".IfxDriver"))
System.out.println(
"jdbc:informix-sqli://:/:INFORMIXSERVER=");
else
if (driverclassname.endsWith(".SybDriver"))
System.out.println("jdbc:sybase:::/");

else
if (driverclassname.endsWith(".SQLServerDriver"))
System.out.println("jdbc:microsoft:sqlserver://:;DatabaseName=");
else
if (driverclassname.endsWith(".JDBCDriver"))
System.out.println("jdbc:cloudscape:;create=");
else
if (driverclassname.endsWith(".RmiJdbcDriver"))
System.out.println("jdbc:cloudscape:rmi://:/ or //:
port>/
if (!(driverclassname.endsWith("SequeLinkDriver")))
DBURL = readLine();
else
DBURL = "";
}

if (DBURL==null) DBURL="";

if (!DBURL.startsWith(dbprefix))
DBURL = dbprefix + DBURL;


String userid = null;
{
if (Integer.parseInt(response) < 10)
System.out.println("Please enter userid for connection to " + DBURL);
else if (Integer.parseInt(response) < 13)
System.out.println("Please enter userid for connection to Microsoft SQL Server:");
userid = readLine();
System.out.println("userid is: '" + userid + "'");
}
String password = null;
if (userid != null && !userid.equals("")) {
System.out.println(
"Please enter password "
+ " =====> WARNING: PASSWORD NOT HIDDEN <====== ");
System.out.println(
"enter 'gui' instead of you password for a secure GUI prompt)");
password = readLine();
if (password.equalsIgnoreCase("gui"))
password = getPassword(userid);
}

globaluid = userid;
globalpwd = password;

try {

//per L3, DataSource approach is used with WS 4.x, so let's do it the same way
if (Integer.parseInt(response) >= DATASOURCECONNECTIONS) {
con = this.getDataSourceConnection(userid, password);
} else { //non-SQLServer

if (userid == null || userid.equals("")) {
long start = System.currentTimeMillis();
con = DriverManager.getConnection(DBURL);
operationTimer = System.currentTimeMillis() - start;
} else {
long start = System.currentTimeMillis();
con = DriverManager.getConnection(DBURL, userid, password);
operationTimer = System.currentTimeMillis() - start;
}

}
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
// See if we can help the user with diagnosing the problem:
boolean network_connect_problem = false;

if (driverclassname.equals("oracle.jdbc.driver.OracleDriver")) {
if ((se.getSQLState() == null) && (se.getErrorCode() == 17002)) {
System.out.println("Connection to the Oracle server failed.\n");
System.out.println(
"Unable to connect to @hostname:port specified in the DB URL.");
network_connect_problem = true;
}
}

if (driverclassname.equals("COM.ibm.db2.jdbc.net.DB2Driver")) {
if ((se.getSQLState() == "08S01") && (se.getErrorCode() == -99999)) {
System.out.println("Connection to the DB2 server failed.\n");
System.out.println(
"Unable to connect to //hostname:port specified in the DB URL.");
System.out.println(
"Verify that db2jstrt is running on the target host with the specified port as a
parameter...");
network_connect_problem = true;
}
}

if (network_connect_problem) {
System.out.println(
"Verify that the host and port that you are connecting to are correct.");
System.out.println(
"Also verify that the host you are connecting to is listening on the specified port.");
System.out.println("Hint: use telnet hostname port.");
System.out.println(
"\tIf you get connection refused then the host is not listening on the specified port.");
System.out.println(
"\tIf telnet simply hangs then the host is listening on the port.");
System.out.println();
}

} // End catch connect exception

}

private Connection getDataSourceConnection(String userid, String password) {
PooledConnection pooledConn = null;
Connection conn = null;


try {
/*loader = Thread.currentThread().getContextClassLoader();
if (loader == null)
loader = ClassLoader.getSystemClassLoader();

if (response.equals("10"))
dsClass =
loader.loadClass("com.merant.sequelink.jdbcx.datasource.SequeLinkDataSource");

else if (response.equals("11"))
dsClass =
loader.loadClass("com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource");

dsClass=loader.loadClass(driverclassname);

*/

ds = (ConnectionPoolDataSource) (dsClass.newInstance());

System.out.println("enter database name:");
dbname = readLine();

dsClass.getMethod("setDatabaseName", new Class[] { String.class }).invoke(
ds,
new Object[] { dbname });

System.out.println("enter port number:");
dbport = readLine();

dsClass.getMethod("setPortNumber", new Class[] { int.class }).invoke(
ds,
new Object[] { new Integer(dbport)});

System.out.println("enter server name:");
dbhostname = readLine();

dsClass.getMethod("setServerName", new Class[] { String.class }).invoke(
ds,
new Object[] { dbhostname });

dsClass.getMethod("setUser", new Class[] { String.class }).invoke(
ds,
new Object[] { userid });

dsClass.getMethod("setPassword", new Class[] { String.class }).invoke(
ds,
new Object[] { password });

long start = System.currentTimeMillis();
pooledConn = ds.getPooledConnection();
conn = pooledConn.getConnection();
operationTimer = System.currentTimeMillis() - start;

// Any use of the Connection must be done AFTER the unlock, or else it will fail
// with a license verification error.

} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
// See if we can help the user with diagnosing the problem:

System.out.println(
"Verify that the host and port that you are connecting to are correct.");
System.out.println(
"Also verify that the host you are connecting to is listening on the specified port.");
System.out.println("Hint: use telnet hostname port.");
System.out.println(
"\tIf you get connection refused then the host is not listening on the specified port.");
System.out.println(
"\tIf telnet simply hangs then the host is listening on the port.");
// System.out.println();

return (null);

} // End catch connect exception

catch (Throwable th) {
th.printStackTrace();
return (null);
} finally {
// if (conn != null) try { conn.close(); } catch (Throwable th) {}
// if (pooledConn != null) try { pooledConn.close(); } catch (Throwable th) {}

}

return (conn);

}


private Connection getDataSourceConnection() {
PooledConnection pooledConn = null;
Connection conn = null;

System.out.println("getting data source connection...");


try {

long start = System.currentTimeMillis();
pooledConn = ds.getPooledConnection();
conn = pooledConn.getConnection();
operationTimer = System.currentTimeMillis() - start;

// Any use of the Connection must be done AFTER the unlock, or else it will fail
// with a license verification error.

} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
// See if we can help the user with diagnosing the problem:

System.out.println(
"Verify that the host and port that you are connecting to are correct.");
System.out.println(
"Also verify that the host you are connecting to is listening on the specified port.");
System.out.println("Hint: use telnet hostname port.");
System.out.println(
"\tIf you get connection refused then the host is not listening on the specified port.");
System.out.println(
"\tIf telnet simply hangs then the host is listening on the port.");
// System.out.println();

return (null);

} // End catch connect exception

catch (Throwable th) {
th.printStackTrace();
return (null);
} finally {
// if (conn != null) try { conn.close(); } catch (Throwable th) {}
// if (pooledConn != null) try { pooledConn.close(); } catch (Throwable th) {}

}

return (conn);

}



public String readLine() {
String response = null;
try {
response = instream.readLine();
} catch (IOException e) {
e.printStackTrace();
}
return response;
}

public boolean process() {

String instring = null;
if (!(response.equals("11")) && !(response.equals("10")))
{
while (instring == null) {
System.out.println(
"Please enter sql statement to execute...(q to quit)\n(or maxconn to test maximum connections
possible to this database)");
instring = readLine();
if (instring.equals("q")) {
System.out.println("Ok, quitting!");
return false;
} else
if (instring.equals("maxconn")) {
System.out.println("testing for maximum connections...");
maxconn();
return true;
}

}

}

else //special case with DataDirect driver, test connect only

{
while ((instring == null) || (!instring.equals("q")) && (!instring.equals("maxconn"))) {
System.out.println(
"Please enter q to quit\n(or maxconn to test maximum connections possible to this database)");
instring = readLine();

}

if (instring.equals("maxconn")) {
System.out.println("testing for maximum connections...");
maxconn();
return true;
}

else
if (instring.equals("q")) {
System.out.println("Ok, quitting!");
return false; }

else System.out.println("Jdbctest only supports connection testing for this driver.");


}

boolean rc = false;
try {
long start = System.currentTimeMillis();
rc = stmt.execute(instring);
operationTimer = System.currentTimeMillis() - start;
} catch (Exception e) {
e.printStackTrace();
}

System.out.println(
"Operation took " + operationTimer + " milliseconds to complete");
ResultSet rs = null;

System.out.println(
"Just tried " + rc + " = stmt.execute(\"" + instring + "\");");

if (rc) {
try {
System.out.println("Getting result set...");
rs = stmt.getResultSet();
ResultSetMetaData rsm = rs.getMetaData();
// Display names of columns fetched
int colcount = rsm.getColumnCount();
System.out.println(colcount + " column(s) in result");
int[] coltype = new int[colcount + 1]; // Do not slot 0
for (int i = 1; i < colcount + 1; i++) {
System.out.print(rsm.getColumnName(i) + " ");
coltype[i] = rsm.getColumnType(i);
}

System.out.println();
System.out.println("-----------------------------------");

while (rs.next()) {
for (int j = 1; j < colcount + 1; j++) {
if (j != 1)
System.out.print(",");
switch (coltype[j]) {
case Types.TINYINT :
System.out.print("" + rs.getShort(j));
break;

case Types.SMALLINT :
System.out.print("" + rs.getShort(j));
break;

case Types.INTEGER :
System.out.print("" + rs.getInt(j));
break;

case Types.BIGINT :
System.out.print("" + rs.getLong(j));
break;

case Types.FLOAT :
System.out.print("" + rs.getFloat(j));
break;

case Types.REAL :
System.out.print("" + rs.getDouble(j));
break;

case Types.DOUBLE :
System.out.print("" + rs.getDouble(j));
break;

case Types.NUMERIC :
System.out.print("" + rs.getInt(j));
break;

case Types.DECIMAL :
System.out.print("" + rs.getInt(j));
break;

case Types.CHAR :
// System.out.print(""+rs.getByte(j));
System.out.print("" + rs.getString(j));
break;

case Types.VARCHAR :
System.out.print("" + rs.getString(j));
break;

case Types.LONGVARCHAR :
System.out.print("" + rs.getString(j));
break;

case Types.DATE :
System.out.print("" + rs.getDate(j));
break;

case Types.TIME :
System.out.print("" + rs.getTime(j));
break;

case Types.TIMESTAMP :
System.out.print("" + rs.getTimestamp(j));
break;

case Types.BINARY :
case Types.BIT :
case Types.VARBINARY :
case Types.LONGVARBINARY :
byte b[] = rs.getBytes(j);
for (int n = 0; n < b.length; n++)
System.out.print("" + b[n] + "|");
break;

case Types.NULL :
System.out.print("-");
break;

case Types.OTHER :
System.out.print("OTHER");
break;

default :
System.out.print("UNKNOWN-TYPE");
}
}
System.out.println();

}
System.out.println();

rs.close();
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
}
}

return true;

}

String getPassword(String userid) {
Frame f;
Panel p;
Label l;
TextField tf;

// Create the frame
f = new Frame("Password Prompt for JDBC tester");
f.setLocation(400, 400);
f.setSize(350, 100);
// Reister ourselves for 'Close' events
f.addWindowListener(new FrameCloser());

// Create the panel
p = new Panel();
f.add(p, "Center");
l = new Label("Please enter password for Data Base user: " + userid);
tf = new TextField(20);
tf.setEchoChar('*');
p.add(l, "Center");
p.add(tf, "Center");
try {
f.show();
} catch (Exception ex) {
ex.printStackTrace();
System.out.println(
"Your ===> DISPLAY <=== Environment Variable is not exported properly. Please correct and retry.");
}

try {
Thread.sleep(1000);
} catch (InterruptedException ie) {
}
f.show();
tf.requestFocus();
tf.addKeyListener(new KeyInterceptor());

// System.out.println("Waiting for password to be entered...");

synchronized (tf) {
try {
tf.wait();
} catch (InterruptedException e) {
}
}
String password = tf.getText();
f.transferFocus();
f.dispose();
return password;
}

public void maxconn() {

Connection c[] = new Connection[125];

System.out.println("1 connection assumed...looping...");

int i;

for (i = 0; i < 125; i++)
try {
if (globaluid == null || globaluid.equals("")) {

if (Integer.parseInt(response) < DATASOURCECONNECTIONS)
{
long start = System.currentTimeMillis();
c[i] = DriverManager.getConnection(DBURL);
operationTimer = System.currentTimeMillis() - start;
}
else c[i]= getDataSourceConnection();
} else {
if (Integer.parseInt(response) < DATASOURCECONNECTIONS)
{

long start = System.currentTimeMillis();
c[i] = DriverManager.getConnection(DBURL, globaluid, globalpwd);
operationTimer = System.currentTimeMillis() - start;
}

else c[i]= getDataSourceConnection();

}

System.out.println("Connection Successful: " + c[i]);
System.out.println(
"Connection "
+ (i + 2)
+ " took "
+ operationTimer
+ " milliseconds to complete");
} catch (SQLException se) {
se.printStackTrace();
System.out.println("Error code is: " + se.getErrorCode());
System.out.println("SQLState is: " + se.getSQLState());
i--;
break;
}

catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
i--;
break;
}

System.out.println(
"Maximum connections to DB:" + DBURL + " is " + (i + 2) + ".");
System.out.println("closing all connections except initial connection...");

for (i = 0; i < 125; i++)
if (c[i] != null)
try {
c[i].close();
System.out.print(".");
} catch (SQLException se2) {
se2.printStackTrace();
System.out.println("Error code is: " + se2.getErrorCode());
System.out.println("SQLState is: " + se2.getSQLState());
}
System.out.println("Done!");

}

}