//************************************************************************************
//
// Copyright (c) 2005-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20304JD.java
// Header: none
// Purpose: Demonstrate basic Teradata SQL using the JDBC API by
// performing a SELECT query on the table using
// parameter markers.
// The program will:
// - Connect as user guest/please
// - Prepare a statement which selects a row
// from table employee
// - Display the medatata associated with the
// column values of the prepared statement
// - Execute the select statement
// - Display the results
// - Disconnect
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.getMetaData,
// java.sql.PreparedStatement.executeQuery,
// java.sql.ResultSet,
// java.sql.ResultSetMetaData
//
// Version: Updated for Teradata V2R6
//
//************************************************************************************
import java.sql.*;
public class T20304JD
{
// 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 SELECT statement and its parameter value
String sSelect = "SELECT empID, empName, empJob FROM employee"
+ " WHERE empDept = ? ORDER BY empID";
String selectCond = "Product Development";
try
{
System.out.println(" Sample T20304JD: ");
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(" Preparing this SQL statement for execution: "
+ sSelect);
// Creating a prepared statement object from an active connection
PreparedStatement pstmt = con.prepareStatement(sSelect);
System.out.println(" Prepared statement object created. ");
try
{
System.out.println(" Display the PreparedStatement meta data"
+ " before executing the request");
displayRSMetaData(pstmt.getMetaData());
System.out.println();
// Set parameter values indicated by ? (dynamic update)
System.out.println(" Using setString() to bind value to " +
"the parameter marker:");
pstmt.setString(1, selectCond);
System.out.println(" FIRST ? set to: " + selectCond);
// The following code will perform a SELECT query
// on the table.
// Submit a query, creating a result set object
ResultSet rs = pstmt.executeQuery();
// Extract and display result set table data
System.out.println();
System.out.println(" DISPLAYING RESULT SET DATA:");
System.out.println(" ---------------------------");
int rowCount = 0; // result set row counter
int numData; // placeholder for integer data
String charData; // placeholder for character data
// iterate through all returned rows and display them
while (rs.next())
{
rowCount++; // increment retrieved row counter
System.out.println();
System.out.println(" ROW " + rowCount + ": ");
// retrieve and display first column value
numData = rs.getInt(1);
System.out.println(" Employee ID: " + numData);
// retrieve and display second column value
charData = rs.getString(2);
System.out.println(" Name: " + charData);
// retrieve and display third column value
charData = rs.getString(3);
System.out.println(" Job Description: " + charData);
}
System.out.println(" " + rowCount + " Row(s) returned.");
}
finally
{
// Close the statement
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
}
finally
{
// Close the connection
System.out.println(" Closing connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed. ");
}
System.out.println(" Sample T20304JD 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 displayRSMetaData(ResultSetMetaData rsmd) throws SQLException
{
// This code will demonstrate all available methods for
// retrieving table column meta data.
// Retrieve the number of columns returned
int colCount = rsmd.getColumnCount();
System.out.println(
" DISPLAYING RESULT SET COLUMN META DATA BEFORE EXECUTING STATEMENT:");
System.out.println(" -----------------------------------------");
System.out.println(" The resulting table has " + colCount + " columns:");
// For every column, display it's meta data.
int i = 1; // Initialize loop counter
while (i <= colCount)
{
// Demonstrating all methods for retrieving column meta data
System.out.println();
System.out.println(" Column " + i);
System.out.println(" ------------ ");
// Display the suggested column title for use in
// printouts and displays
System.out.println(" Column label: "
+ rsmd.getColumnLabel(i));
// Display the column name
System.out.println(" Column name: "
+ rsmd.getColumnName(i));
// Display the SQL type of a column.
System.out.println(" Column type: "
+ rsmd.getColumnType(i));
// Display the type name of a column
System.out.println(" Column type name: "
+ rsmd.getColumnTypeName(i));
// Display information on whether NULL values are allowed
System.out.println(" NULLs allowed: "
+ rsmd.isNullable(i));
// Display the normal maximum width of a column in characters.
System.out.println(" Maximum character width: "
+ rsmd.getColumnDisplaySize(i));
// Display precision: the number of decimal digits
// Note: default value is 0.
System.out.println(" Column precision"
+ " (number of decimal places): "
+ rsmd.getPrecision(i));
// Display the number of digits to the right of the
// decimal point. Note: default value is 0.
System.out.println(" Precision to the right of"
+ " the decimal point: "
+ rsmd.getScale(i));
// Increment column counter
i++;
}
} // end displayRSMetaData(...)
} // End class T20304JD
|
|
|
|