//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20704JD.java
// Header: none
// Purpose: Demonstrate the retrievel of auto-generated keys
// after an PreparedStatement batch INSERT query.
// The program will:
// - Connect as user guest/please
// - Executes a PreparedStatement batch INSERT query,
// which inserts rows into table books indicating the
// generated key flag RETURN_GENERATED_KEYS
// - Retrieve the generated key
// - Display the generated keys result set
// - Executes a PreparedStatement batch INSERT query,
// which inserts rows into table books indicating the
// column names that will be made available
// - Retrieve the columns using getGeneratedKeys
// - Display the columns
// - Disconnect.
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.executeBatch,
// java.sql.PreparedStatement.addBatch,
// java.sql.PreparedStatement.getGeneratedKeys,
// java.sql.ResultSet, java.sql.ResultSet.getMetaData,
// java.sql.ResultSetMetaData
//
// Version: Updated for Teradata V2R6.2
//
//************************************************************************************
import java.sql.*;
public class T20704JD
{
// Name of the user able to create and drop tables
public static String sUser = "guest";
public static String sPassword = "please";
public static void main(String args[])
throws ClassNotFoundException
{
// Creation of URL to be passed to the JDBC driver
String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8";
// Creation of the SQL INSERT statement and its parameter values
String sInsert =
"INSERT into books (ISBN, bookTitle, bookAuthor) values(?, ?, ?)" ;
String sBooks[][] = { {"1-09786-701-X", "In the Dust", "Low, Anne"},
{"1-52392-872-X", "Outside", "Willis, Ted"},
{"1-77841-414-X", "Sunny Day", "Gile, Jeni"}};
try
{
System.out.println(" Sample T20704JD: ");
System.out.println(" Looking for the Teradata JDBC driver... ");
// Loading the Teradata JDBC driver
Class.forName("com.teradata.jdbc.TeraDriver");
System.out.println(" JDBC driver loaded. ");
// Attempting to connect to Teradata
System.out.println(" Attempting to connect to Teradata via" +
" the JDBC driver...");
// Creating a connection object
Connection con = DriverManager.getConnection(url, sUser, sPassword);
System.out.println(" User " + sUser + " connected.");
System.out.println(" Connection to Teradata established. ");
try
{
System.out.println(" Execute a PreparedStatement batch request "
+"with the flag GET_GENERATED_KEYS. ");
// Execute a PreparedStatement batch request and retrieve the
// identity column
executeBatchRequest(con, sInsert, sBooks, false);
System.out.println(" Execute a PreparedStatement batch request "
+"indicating which column names should be "
+"made available for retrieval. ");
// Execute a PreparedStatement batch request and retrieve the
// specified columns using getGeneratedKeys
executeBatchRequest(con, sInsert, sBooks, true);
}
finally
{
// Close the connection
System.out.println(" Closing connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed. ");
}
System.out.println(" Sample T20704JD finished. ");
}
catch (SQLException ex)
{
// A SQLException was generated. Catch it and display
// the error information.
// Note that there could be multiple error objects chained
// together.
System.out.println();
System.out.println("*** SQLException caught ***");
while (ex != null)
{
System.out.println(" Error code: " + ex.getErrorCode());
System.out.println(" SQL State: " + ex.getSQLState());
System.out.println(" Message: " + ex.getMessage());
ex.printStackTrace();
System.out.println();
ex = ex.getNextException();
}
throw new IllegalStateException ("Sample failed.") ;
}
} // End main
private static void executeBatchRequest(Connection con, String sInsert,
String[][] sBooks, boolean useColNames)
throws SQLException
{
PreparedStatement pstmt;
System.out.println(
" Preparing this SQL statement for execution: "
+ sInsert);
if (useColNames)
{
// Using an array of column names to indicate which columns should
// be made available for retrieval.
String columnNames[] = {"bookPublisher", "identityCol"};
// Creating a prepared statement object from an active connection
// and passing the columnNames
pstmt = con.prepareStatement(sInsert, columnNames);
}
else
{
// Creating a prepared statement object from an active connection
// and passing the columnIndexes
pstmt = con.prepareStatement(sInsert,
Statement.RETURN_GENERATED_KEYS);
}
System.out.println(" Prepared statement object created. ");
try
{
// Set parameter values indicated by ? (dynamic update)
System.out.println(" Using setString() to bind value to " +
"the parameter marker. "
for (int bookNum = 0; bookNum < sBooks.length; bookNum++)
{
for (int bookItem = 0; bookItem < sBooks[bookNum].length;
bookItem++)
pstmt.setString(bookItem + 1, sBooks[bookNum][bookItem]);
pstmt.addBatch();
}
// The following code will perform an INSERT query
// on the table.
System.out.println(" Submitting the batch request to be "+
"executed. ");
// Submit a batch request, returning update counts
int[] updateCount = pstmt.executeBatch();
int insertNum = 1;
System.out.println(" Retrieving generated keys. ");
// Retrieve generated keys
ResultSet rs = pstmt.getGeneratedKeys();
// iterate through all returned rows and display them
while (rs.next())
{
// Print the update count
System.out.println(
" =====================================================");
System.out.println(
" =====================================================");
System.out.println();
System.out.println(" INSERT " + insertNum +
" executed successfully. " +
updateCount[insertNum - 1] +
" row(s) inserted. ");
// Extract and display generated keys
System.out.println();
System.out.println(" DISPLAYING GENERATED KEY FOR STATEMENT " +
insertNum + ":");
System.out.println(
" -----------------------------------------");
// retrieve and display specified column values along
// with their column name
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++)
{
System.out.println(" " + rs.getMetaData().getColumnName(i)+
": " + rs.getString(i));
}
insertNum++;
}
}
finally
{
// Close the statement
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
}
} // End class T20704JD
|
|
|
|