//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20204JD.java
// Header: none
// Purpose: Demonstrate the retrievel of auto-generated keys
// after an INSERT query.
// The program will:
// - Connect as user guest/please
// - Insert a row into table books indicating the
// generated key flag RETURN_GENERATED_KEYS
// - Retrieve the generated key
// - Display the generated keys result set
// - Insert a row into table books indicating the
// column names that will be made available
// - Retrieve the generated keys
// - Display the generated keys result set
// - Disconnect.
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.executeUpdate,
// java.sql.PreparedStatement.getGeneratedKeys,
// java.sql.ResultSet, java.sql.ResultSet.getMetaData,
//
// Version: Updated for Teradata V2R6
//
//************************************************************************************
import java.sql.*;
public class T20204JD
{
// 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 value
String sInsertQuery = "INSERT into books "
+ " (ISBN, bookTitle, bookAuthor, bookPublisher)"
+ " values(?, ?, ?, ?)";
try
{
System.out.println(" Sample T20204JD: ");
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 request "+
"with the flag GET_GENERATED_KEYS. ");
// Execute a PreparedStatement request and retrieve the
// identity column
requestSingleColumn(con, sInsertQuery);
System.out.println(" Execute a PreparedStatement request "+
"indicating which column names should be "+
"made available for retrieval. ");
// Execute a PreparedStatement batch request and retrieve the
// specified columns using getGeneratedKeys
requestMultipleColumns(con, sInsertQuery);
}
finally
{
// Close the connection
System.out.println(" Closing connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed. ");
}
System.out.println(" Sample T20204JD 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
static void requestSingleColumn(Connection con, String sInsertQuery)
throws SQLException
{
System.out.println(" Preparing this SQL statement for execution: "
+ sInsertQuery);
// Creating a prepared statement object from an active connection
// and indicating that the generated key will be returned.
PreparedStatement pstmt = con.prepareStatement(sInsertQuery,
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:");
pstmt.setString(1, "1-57345-785-X");
pstmt.setString(2, "SQL for Beginners");
pstmt.setString(3, "Smith, John");
pstmt.setString(4, "Able Books");
// The following code will perform an INSERT query
// on the table.
System.out.println(" Executing query. ");
// Submit a query, returning an update count
int updateCount = pstmt.executeUpdate();
System.out.println(" Obtained update count: " + updateCount);
System.out.println(" Retrieving generated keys.");
// Retrieve generated key
ResultSet rs = pstmt.getGeneratedKeys();
// set result set to the first row
rs.next();
// Extract and display generated keys
System.out.println();
System.out.println(" DISPLAYING GENERATED KEY:");
System.out.println(" ---------------------------");
// retrieve and display identity column value
int identityCol = rs.getInt(1);
System.out.println(" Identity Column Value: " + identityCol);
}
finally
{
// Close the statement
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
}
static void requestMultipleColumns(Connection con, String sInsertQuery)
throws SQLException
{
// Specifying the column names that should be returned in a String array
String columnNames[] = {"bookTitle", "bookAuthor", "identityCol"};
System.out.println(" Preparing this SQL statement for execution: "
+ sInsertQuery);
// Creating a prepared statement object from an active connection
// and passing the columnNames, which is a list of columns to be
// returned from getGeneratedKeys
PreparedStatement pstmt = con.prepareStatement(sInsertQuery,
columnNames);
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. ");
pstmt.setString(1, "1-68345-785-X");
pstmt.setString(2, "Advanced SQL");
pstmt.setString(3, "White, Joseph");
pstmt.setString(4, "Able Books");
// The following code will perform an INSERT query
// on the table.
System.out.println(" Executing query. ");
// Submit a query, returning an update count
int updateCount = pstmt.executeUpdate();
System.out.println(" Obtained update count: " + updateCount);
System.out.println(" Retrieving generated keys.");
// Retrieve generated key
ResultSet rs = pstmt.getGeneratedKeys();
// set the result set to the first row
rs.next();
// Extract and display generated keys
System.out.println();
System.out.println(" DISPLAYING GENERATED KEY:");
System.out.println(" ---------------------------");
// retrieve and display specified column value
String bookTitle = rs.getString(1);
String bookAuthor = rs.getString(2);
int identityCol = rs.getInt(3);
System.out.println(" Book Title : " + bookTitle);
System.out.println(" Book Author: " + bookAuthor);
System.out.println(" Identity Column Value: " + identityCol);
}
finally
{
// Close the statement
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
}
} // End class T20204JD
|
|
|
|