Example Java JDBC Client Application Code
This section provides a sample template for using the JDBC driver in Java code. You must provide appropriate values for ip, datasource, userName, password, and the SQL statement.
import java.sql.*;
class JdbcSample
{
public static void main(String args[])
{
if (args.length != 7) {
System.err.println("usage : prog <datasource name> <host name> <port> <user> <password> <domain name> \"<sql statement>\"");
System.exit(1);
}
String datasource = args[0]; // datasource_name
String ip = args[1]; // IP or host name of TDV Server
// port of TDV Server dbapi service
int port = 0;
try {
port = Integer.parseInt(args[2]);
} catch (Exception e) {
port = 9401;
}
String userName = args[3];
String password = args[4];
String domain = args[5];
String url = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ResultSetMetaData rsmd = null;
try {
Class.forName("cs.jdbc.driver.CompositeDriver");
url = "jdbc:compositesw:dbapi@" + ip + ":" + port + "?domain=" +
domain + "&dataSource=" + datasource;
conn = DriverManager.getConnection(url, userName, password);
((cs.jdbc.driver.CompositeConnection)conn).clearAllDataSourceCredentials();
((cs.jdbc.driver.CompositeConnection)conn)
.setDataSourceCredentials(<datasourcename>,user,password);
stmt = conn.createStatement();
boolean isNotUpdate = stmt.execute(args[6]);
int rows = 0;
// return type is a result set
if (isNotUpdate == true) {
rs = stmt.getResultSet();
if (rs == null) {
throw new SQLException("sql=`"+args[6]+"` did not generate a result set");
}
rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();
System.out.println("column count = " + columns);
rows = 1;
int type = 0;
while (rs.next()) {
System.out.print("row = `" + rows + "` ");
for (int i=1; i <= columns; i++) {
type = rsmd.getColumnType(i);
switch (type) {
case Types.INTEGER:
System.out.print(" col[" + i + "]=`" + rs.getInt(i) + "` ");
break;
case Types.SMALLINT:
System.out.print(" col[" + i + "]=`" + rs.getShort(i) + "` ");
break;
case Types.TINYINT:
System.out.print(" col[" + i + "]=`" + rs.getByte(i) + "` ");
break;
case Types.BIGINT:
System.out.print(" col[" + i + "]=`" + rs.getLong(i) + "` ");
break;
case Types.FLOAT:
System.out.print(" col[" + i + "]=`" + rs.getFloat(i) + "` ");
break;
case Types.REAL:
System.out.print(" col[" + i + "]=`" + rs.getFloat(i) + "` ");
break;
case Types.DECIMAL:
System.out.print(" col[" + i + "]=`" + rs.getFloat(i) + "` ");
break;
case Types.DOUBLE:
System.out.print(" col[" + i + "]=`" + rs.getDouble(i) + "` ");
break;
case Types.NUMERIC:
System.out.print(" col[" + i + "]=`" + rs.getFloat(i) + "` ");
break;
case Types.CHAR:
System.out.print(" col[" + i + "]=`" + rs.getString(i) + "` ");
break;
case Types.VARCHAR:
System.out.print(" col[" + i + "]=`" + rs.getString(i) + "` ");
break;
case Types.LONGVARCHAR:
System.out.print(" col[" + i + "]=`" + rs.getString(i) + "` ");
break;
case Types.DATE:
System.out.print(" col[" + i + "]=`" + rs.getDate(i) + "` ");
break;
case Types.TIME:
System.out.print(" col[" + i + "]=`" + rs.getTime(i) + "` ");
break;
case Types.TIMESTAMP:
System.out.print(" col[" + i + "]=`" + rs.getTimestamp(i) + "` ");
break;
case Types.BOOLEAN:
System.out.print(" col[" + i + "]=`" + rs.getBoolean(i) + "` ");
break;
default:
System.out.print(" col[" + i + "]=`" + rs.getString(i) + "` ");
break;
}
}
System.out.println("\n");
rows++;
}
rs.close();
} else {
// return type is not a result set
rows = stmt.getUpdateCount();
System.out.println("sql=`"+args[4]+"` affected " + rows + " row(s)");
}
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
if (rs != null) {
try {
rs.close();
} catch (SQLException ignore) { }
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ignore) { }
}
if (conn != null) {
try {
conn.close();
} catch (SQLException ignore) { }
}
} finally {
rs = null;
stmt = null;
conn = null;
}
}
}
Example of Calling Procedures
JDBC supports the getMoreResults method and the getResultSet method. The following pseudocode illustrates how to use those methods when registerOutputCursor is set to true.
The registerOutputCursors property might not be best for use in JDBC, ODBC, and ADO.NET client connections. TDV provides a standard way to call procedures with or without registerOutputCursors in JDBC, ODBC, and ADO.NET connections.
String query = "{call LookupProduct(?,?,?,?)}";
cst = conn.prepareCall(query);
cst.setInt(1, 3);
cst.registerOutParameter(2, Types.OTHER);
cst.setInt(3, 3);
cst.registerOutParameter(4, Types.OTHER);
cst.execute();
/**
* Method 1:
*/
rs = (ResultSet) cst.getObject(2);
rs = (ResultSet) cst.getObject(4);
/**
* Method 2:
*
* rs = cst.getResultSet();
* if(cst.getMoreResults())
* rs = cst.getResultSet();
*/
while (rs.next())
;
conn.close();
The following pseudocode shows how to use getResultSet() and getMoreResults() whether or not registerOutputCursors is set to true.
String query = "{call LookupProduct(?,?)}";
cst = conn.prepareCall(query);
cst.setInt(1, 3);
cst.setInt(2, 4);
cst.execute();
rs = cst.getResultSet();
if (cst.getMoreResults())
rs = cst.getResultSet();
while (rs.next())
;
conn.close();