Examples of Accessing Data through JDBC
The TDV JDBC driver supports the following statement types:
To use these examples in your TDV environment
1. Supply values for the following:
— Login credentials for accessing TDV Server
— Login credentials for accessing the data source
— SELECT statement
2. Set the CLASSPATH to csjdbc.jar, which contains the TDV JDBC driver.
3. Compile and run your code.
Examples of Accessing Data through JDBC Using Statements
This section contains examples to illustrate the use of statements and specifications for pass-through login credentials.
Example 1: Submit a Select Statement Using the JDBC Driver
This example demonstrates how to submit a SELECT statement to the TDV JDBC driver.
In this example, the data source does not require pass-through login credentials. Instead, the login credentials are compUser and compPassword.
import java.util.*;
import java.sql.*;
public class SelectExample {
public static void main(String[] arg) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
String url = "jdbc:compositesw:dbapi@localhost:9401?" +"domain=composite&dataSource=cds";
String user = "compUser";
String pass = "compPassword";
// Load driver
Class.forName("cs.jdbc.driver.CompositeDriver");
// Create connection
conn = DriverManager.getConnection(url, user, pass);
// Create statement
stmt = conn.createStatement();
// Execute statement
rs = stmt.executeQuery("SELECT * FROM catalog.schema.table");
// Get column count
ResultSetMetaData rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();
// Get results
while(rs.next()) {
for (int i=0; i<columns; i++) {
Object o = rs.getObject(i+1);
if (o == null) {
System.out.print("[NULL]");
} else {
System.out.print(o.toString());
}
System.out.print(" ");
}
System.out.println();
}
} finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
}
Example 2: Set Data Source Credentials to Use Pass-through Data Sources
This example illustrates how to submit a SELECT statement to a data source that requires pass-through credentials (dsUser, dsPassword). These data source login credentials are different from the ones used for accessing TDV Server (compUser, compPassword).
import java.util.*;
import java.sql.*;
public class multiPassThruWithTDVLogInCred {
public static void main(String[] arg) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
String url = "jdbc:compositesw:dbapi@localhost:9401?"
+"domain=composite&dataSource=cdspt";
String user = "compUser";
String pass = "compPassword";
// Load driver
Class.forName("cs.jdbc.driver.CompositeDriver");
// Create connection
conn = DriverManager.getConnection(url, user, pass);
((cs.jdbc.driver.CompositeConnection)conn)
.setDataSourceCredentials("/shared/sources/dsPassThru", "dsUser", "dsPassword");
// Create statement
stmt = conn.createStatement();
// Execute statement
rs = stmt.executeQuery("SELECT * FROM catalog.schema.table");
// Get column count
ResultSetMetaData rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();
// Get results
while(rs.next()) {
for (int i=0; i<columns; i++) {
Object o = rs.getObject(i+1);
if (o == null) {
System.out.print("[NULL]");
} else {
System.out.print(o.toString());
}
System.out.print(" ");
}
System.out.println();
}
} finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
}
Example 3: Setting Credentials for Use with Any Pass-through Data Source
In this example, the path to the data source is specified as NULL. When NULL is specified as the resource path, the credential is added to the session’s list of generic credentials for the user.
The program tries to connect with the data source using different credentials for the user, but connects only with a data source that has the specified user name and password. By not having to specify a resource path, the client can be ignorant of data source namespace, at the cost of having to try various login credentials to achieve a successful connection.
import java.util.*;
import java.sql.*;
public class multiPassThruWithNull {
public static void main(String[] arg) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
String url = "jdbc:compositesw:dbapi@localhost:9401?"
+"domain=composite&dataSource=cdspt";
String user = "compUser";
String pass = "compPassword";
// Load driver
Class.forName("cs.jdbc.driver.CompositeDriver");
// Create connection
conn = DriverManager.getConnection(url, user, pass);
((cs.jdbc.driver.CompositeConnection)conn)
.setDataSourceCredentials(NULL, "dsUser", "dsPassword");
// Create statement
stmt = conn.createStatement();
// Execute statement
rs = stmt.executeQuery("SELECT * FROM catalog.schema.table");
// Get column count
ResultSetMetaData rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();
// Get results
while(rs.next()) {
for (int i=0; i<columns; i++) {
Object o = rs.getObject(i+1);
if (o == null) {
System.out.print("[NULL]");
} else {
System.out.print(o.toString());
}
System.out.print(" ");
}
System.out.println();
}
} finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
}
Example 4: Set TDV Server Credentials to Use Pass-through Data Sources
This example is similar to
Example 2: Set Data Source Credentials to Use Pass-through Data Sources. The login credentials for accessing TDV Server are the same as those for accessing the data source.
import java.util.*;
import java.sql.*;
public class multiPassThruWithTDVLogInCred {
public static void main(String[] arg) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
String url = "jdbc:compositesw:dbapi@localhost:9401?"
+"domain=composite&dataSource=cdspt";
String user = "dsUser";
String pass = "dsPassword";
// Load driver
Class.forName("cs.jdbc.driver.CompositeDriver");
// Create connection
conn = DriverManager.getConnection(url, user, pass);
((cs.jdbc.driver.CompositeConnection)conn)
.setDataSourceCredentials("/shared/sources/dsPassThru" "dsUser", "dsPassword");
// Create statement
stmt = conn.createStatement();
// Execute statement
rs = stmt.executeQuery("SELECT * FROM catalog.schema.table");
// Get column count
ResultSetMetaData rsmd = rs.getMetaData();
int columns = rsmd.getColumnCount();
// Get results
while(rs.next()) {
for (int i=0; i<columns; i++) {
Object o = rs.getObject(i+1);
if (o == null) {
System.out.print("[NULL]");
} else {
System.out.print(o.toString());
}
System.out.print(" ");
}
System.out.println();
}
} finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
}
Examples of Accessing Data through JDBC Using Prepared Statements
A prepared statement is an object that contains an SQL statement, possibly with varying input parameters, that can be executed multiple times. Use a question mark (?) as a placeholder for a parameter within the SQL statement. After all placeholder parameters are set, the query is executed.
For further details on prepared statements, see references on JDBC API, and the information provided on JDBC at http://www.oracle.com/technetwork/java/javase/jdbc/index.html.
Restrictions When Using Prepared Statement
The following rules apply when you submit a prepared statement to the TDV JDBC driver to access the server:
• You can create and use multiple prepared statements on one connection to the server.
• The server maintains a cache of prepared statements, some of which exist across multiple connections, so that when you create a prepared statement that is already in the cache, the server does not need to recreate the query plan. The cache size is configurable, and access is through Manager. For details, see the TDV Administration Guide.
• The placeholder for a query parameter can be used anywhere a literal can be used.
• Prepared statements with placeholder parameters (?) cannot used with Netezza data ship because all variables must be resolved before submitting SQL to the data source. Federated queries with database-specific native functions must be able to push SQL directly to the data source, or the query fails.
• The DatabaseMetaData.getMetaData( ) method is not supported. However, you can get ResultSetMetaData by using ResultSet.getMetaData( ).
Prepared Statements Examples
The following examples show how to use prepared statements in TDV Server.
Example 1: SELECT Statement
The following sample code demonstrates how to use a prepared statement that contains a SELECT statement. In this example, the SELECT statement queries the customers table and retrieves the required data under a certain condition, which initially uses the placeholder parameter ?. This example uses a for loop to set values for parameters in the prepared statement.
import java.sql.*;
public class PreparedStatementSample
{
private static final String COMPOSITE_URL =
"jdbc:compositesw:dbapi@localhost:9401?domain=composite&dataSource=cdbs";
private static final String COMPOSITE_DRIVER =
"cs.jdbc.driver.CompositeDriver";
private static final String COMPOSITE_USER = "admin";
private static final String COMPOSITE_PASSWORD = "admin";
public static void main(String[] args) {
try {
Class.forName(COMPOSITE_DRIVER);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
return;
}
try {
execute();
} catch (SQLException ex) {
ex.printStackTrace();
return;
}
}
private static void execute()
throws SQLException
{
Connection conn = DriverManager.getConnection(
COMPOSITE_URL, COMPOSITE_USER, COMPOSITE_PASSWORD);
PreparedStatement stmt = conn.prepareStatement(
"SELECT * FROM products WHERE ProductID = ?”);
for (int i = 1; i <= 5 ; i++) {
stmt.setInt(1, i);
ResultSet rs = stmt.executeQuery();
System.out.println("Row " + i);
printResultSet(rs);
rs.close();
}
stmt.close();
conn.close();
}
private static void printResultSet(ResultSet rs)
throws SQLException
{
ResultSetMetaData metaData = rs.getMetaData();
while (rs.next()) {
for (int i=1; i<=metaData.getColumnCount(); i++) {
System.out.println(" Column " + i + " " + metaData.getColumnName(i) +
" " + rs.getString(i));
}
}
}
}
Example 2: INSERT Statement
The following sample code demonstrates the usage of a prepared statement that contains an INSERT statement. This example works like
Example 1: SELECT Statement, except that here executeUpdate() is used instead of executeQuery() to execute the SQL, and the result set is the number of rows affected by the insert operation.
import java.sql.*;
import java.math.BigDecimal;
public class PreparedStatementInsert
{
private static final String COMPOSITE_URL =
"jdbc:compositesw:dbapi@localhost:9401?domain=composite&dataSource=tutorial";
private static final String COMPOSITE_DRIVER =
"cs.jdbc.driver.CompositeDriver";
private static final String COMPOSITE_USER = "admin";
private static final String COMPOSITE_PASSWORD = "admin";
public static void main(String[] args) {
try {
Class.forName(COMPOSITE_DRIVER);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
return;
}
try {
execute();
} catch (SQLException ex) {
ex.printStackTrace();
return;
}
}
private static void execute()
throws SQLException
{
Connection conn = DriverManager.getConnection(
COMPOSITE_URL, COMPOSITE_USER, COMPOSITE_PASSWORD);
PreparedStatement stmt = conn.prepareStatement(
"INSERT INTO products (ProductID, ProductName, UnitPrice)" +
"VALUES (?, ?, ?)");
stmt.setInt(1, 50);
stmt.setString(2, "new");
stmt.setBigDecimal(3, new BigDecimal(50.00));
int rowsInserted = stmt.executeUpdate();
System.out.println("Rows inserted " + rowsInserted);
stmt.close();
conn.close();
}
private static void printResultSet(ResultSet rs)
throws SQLException
{
ResultSetMetaData metaData = rs.getMetaData();
int rowIndex = 0;
while (rs.next()) {
System.out.println("Row " + rowIndex++);
for (int i=1; i<=metaData.getColumnCount(); i++) {
System.out.println(" Column " + i + " " + metaData.getColumnName(i) +
" " + rs.getString(i));
}
}
}
}
Example 3: UPDATE Statement
The following sample code demonstrates the usage of a prepared statement that contains an UPDATE statement. This example works similar to
Example 2: INSERT Statement. Here, the result set is the number of rows affected by the update operation.
import java.sql.*;
import java.math.BigDecimal;
public class PreparedStatementUpdate
{
private static final String COMPOSITE_URL =
"jdbc:compositesw:dbapi@localhost:9401?domain=composite&dataSource=tutorial";
private static final String COMPOSITE_DRIVER =
"cs.jdbc.driver.CompositeDriver";
private static final String COMPOSITE_USER = "admin";
private static final String COMPOSITE_PASSWORD = "admin";
public static void main(String[] args) {
try {
Class.forName(COMPOSITE_DRIVER);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
return;
}
try {
execute();
} catch (SQLException ex) {
ex.printStackTrace();
return;
}
}
private static void execute()
throws SQLException
{
Connection conn = DriverManager.getConnection(
COMPOSITE_URL, COMPOSITE_USER, COMPOSITE_PASSWORD);
PreparedStatement stmt = conn.prepareStatement(
"UPDATE products SET ProductName = ? WHERE ProductID = ?");
stmt.setString(1, "newProduct");
stmt.setBigDecimal(2, new BigDecimal(50.00));
int rowsUpdated = stmt.executeUpdate();
System.out.println("Rows updated " + rowsUpdated);
stmt.close();
conn.close();
}
private static void printResultSet(ResultSet rs)
throws SQLException
{
ResultSetMetaData metaData = rs.getMetaData();
int rowIndex = 0;
while (rs.next()) {
System.out.println("Row " + rowIndex++);
for (int i=1; i<=metaData.getColumnCount(); i++) {
System.out.println(" Column " + i + " " + metaData.getColumnName(i) +
" " + rs.getString(i));
}
}
}
}
Example 4: DELETE Statement
The following sample code demonstrates the usage of a prepared statement that contains a DELETE statement. This example works similar to
Example 2: INSERT Statement. Here, the result set is the number of rows affected by the delete operation.
import java.sql.*;
import java.math.BigDecimal;
public class PreparedStatementDelete
{
private static final String COMPOSITE_URL =
"jdbc:compositesw:dbapi@localhost:9401?domain=composite&dataSource=tutorial";
private static final String COMPOSITE_DRIVER =
"cs.jdbc.driver.CompositeDriver";
private static final String COMPOSITE_USER = "admin";
private static final String COMPOSITE_PASSWORD = "admin";
public static void main(String[] args) {
try {
Class.forName(COMPOSITE_DRIVER);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
return;
}
try {
execute();
} catch (SQLException ex) {
ex.printStackTrace();
return;
}
}
private static void execute()
throws SQLException
{
Connection conn = DriverManager.getConnection(
COMPOSITE_URL, COMPOSITE_USER, COMPOSITE_PASSWORD);
PreparedStatement stmt = conn.prepareStatement(
"DELETE FROM products WHERE ProductID = ?");
stmt.setInt(1, 50);
int rowsDeleted = stmt.executeUpdate();
System.out.println("Rows deleted " + rowsDeleted);
stmt.close();
conn.close();
}