--------------------------------------

//JDBC connect to DB2, JDBC driver  is provided from WebSphere by default
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); //need db2java.zip in the path 
conn = DriverManager.getConnection("jdbc:db2:sample");	
//DB2, no username and password needed
--------------------------------------

--------------------------------------

//*** JDBC connection with Oracle 9, classes12.zip is needed in the path

Class.forName("oracle.jdbc.driver.OracleDriver");
//or use DriverManager.registerDriver(new oracle.jdbc.OracleDriver());	
conn= DriverManager.getConnection("jdbc:oracle:thin:@machine-or-url:1521:dbname", 
			"username", "password");
//1521 is port number
--------------------------------------

--------------------------------------

//Need mysqlJDBC driver in the path
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/cis234j", "root", "");
//username=root, no password
--------------------------------------

--------------------------------------

1. Use SQL to get data

String firstname="";
String sql = "select firstnme, lastname from employee";
Statement stm = conn.createStatement();
ResultSet rst = stm.executeQuery(sql);
for (int i=0; (i<10 && rst.next()); i++)		//only print 10 names
{
	firstname =  rst.getString("firstnme");
	System.out.println("Hello "+ firstname+"," );
}
if(conn != null)
{
	conn.close();
	System.out.println("Successfully closed");  
}
--------------------------------------

2. Calling a Stored Procedure in a Database
This example demonstrates how to call stored procedures with IN, OUT, and IN/OUT parameters.

//CallableStatement cstmt = con.prepareCall("{call getTestData(?, ?)}");
CallableStatement cs;
try {
// Call a procedure with no parameters
cs = connection.prepareCall("{call myproc}");
cs.execute();

// Call a procedure with one IN parameter
cs = connection.prepareCall("{call myprocin(?)}");

// Set the value for the IN parameter
cs.setString(1, "a string");

// Execute the stored procedure
cs.execute();

// Call a procedure with one OUT parameter
cs = connection.prepareCall("{call myprocout(?)}");

// Register the type of the OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);

// Execute the stored procedure and retrieve the OUT value
cs.execute();
String outParam = cs.getString(1); // OUT parameter

// Call a procedure with one IN/OUT parameter
cs = connection.prepareCall("{call myprocinout(?)}");

// Register the type of the IN/OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);

// Set the value for the IN/OUT parameter
cs.setString(1, "a string");

// Execute the stored procedure and retrieve the IN/OUT value
cs.execute();
outParam = cs.getString(1); // OUT parameter
} catch (SQLException e) {
}

-------------------------------------------------
Calling a Function in a Database
A function is essentially a stored procedure that returns a result. This example demonstrates how to call functions with IN, OUT, and IN/OUT parameters. 

CallableStatement cs;
try {
// Call a function with no parameters; the function returns a VARCHAR
// Prepare the callable statement
cs = connection.prepareCall("{? = call myfunc}");

// Register the type of the return value
cs.registerOutParameter(1, Types.VARCHAR);

// Execute and retrieve the returned value
cs.execute();
String retValue = cs.getString(1);

// Call a function with one IN parameter; the function returns a VARCHAR
cs = connection.prepareCall("{? = call myfuncin(?)}");

// Register the type of the return value
cs.registerOutParameter(1, Types.VARCHAR);

// Set the value for the IN parameter
cs.setString(2, "a string");

// Execute and retrieve the returned value
cs.execute();
retValue = cs.getString(1);

------------------------------------------------

// Call a function with one OUT parameter; the function returns a VARCHAR
cs = connection.prepareCall("{? = call myfuncout(?)}");

// Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);

// Execute and retrieve the returned values
cs.execute();
retValue = cs.getString(1); // return value
String outParam = cs.getString(2); // OUT parameter

// Call a function with one IN/OUT parameter; the function returns a VARCHAR
cs = connection.prepareCall("{? = call myfuncinout(?)}");

// Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);

// Set the value for the IN/OUT parameter
cs.setString(2, "a string");

// Execute and retrieve the returned values
cs.execute();
retValue = cs.getString(1); // return value
outParam = cs.getString(2); // IN/OUT parameter
} catch (SQLException e) {
}

//>>Reference: from http://javaalmanac.com/egs/java.sql/pkg.html
------------------------------------------------

Returning a JDBC result set from an Oracle stored procedure (function)


CallableStatement stmt = conn.prepareCall(query);

// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);

// set the in param
stmt.setFloat(2, price);

// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);

// print the results
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" +
rs.getFloat(2) + "\t" +
rs.getDate(3).toString());
}
//>>>>Reference: http://www.enterprisedt.com/publications/oracle/result_set.html 
--------------------------------------