//********************************************************************* // // Copyright (c) 2009 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T22003JD.java // Header: none // Purpose: Demonstrate basic Teradata SQL using the JDBC API by // creating a table with a User Defined Type (UDT), and // inserting & selecting data from the table. // The program will: // - Connect as user guest/please // - Create a table with the Address UDT // (Address is created using sample T22000JD.java) // - Create data using SampStruct class defined at the // end of this file // - Insert data into the table // - Select and print the data from the table // - Disconnect // // JDBC API: java.sql.Connection, java.sql.Statement, // java.sql.Statement.executeUpdate, // // Version: Created for Teradata Database 13.10 // //********************************************************************* import java.sql.*; import java.io.*; public class T22003JD { // Name of the user able to create, drop, and manipulate 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"; try { System.out.println("\n Sample T22003JD: \n"); 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. \n"); // 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); try { System.out.println(" User " + sUser + " connected."); System.out.println(" Connection to Teradata established. \n"); // Statements used in table creation String sUDTName = "Address"; String sTableName = "EmployeeTbl"; String createTable = "CREATE TABLE "+sTableName+ " ( empId INTEGER, "+ " empFirstName VARCHAR(50), "+ " empLastName VARCHAR(50), "+ " empAddress " +sUDTName+" )"; String dropTable = "DROP TABLE " + sTableName ; String insertStmt = " INSERT INTO "+sTableName+"( ?, ?, ?, ?)"; String selectStmt = " SELECT * FROM "+sTableName+" ORDER BY 4"; // Define the data to be inserted into the table. Uses the // SampStruct Class defined at the end of this file. Object[][] data = { {"120", "Will", "Jones", new SampStruct("Address", new Object[] { "123 Main Street", "My City", "NY", "00245"})}, {"130", "Joe", "Black", new SampStruct("Address", new Object[] { "487 Elm Avenue", "Your City", "AZ", "30246"})}, {"140", "Sam", "Rish", new SampStruct("Address", new Object[] { "287 Elm Avenue", "Your City", "AZ", "30246"})}, {"150", "Alice", "Frong", new SampStruct("Address", new Object[] { "1050 Oak Street", "Big City", "CA", "90124"})}, {"160", "Fred", "Join", new SampStruct("Address", new Object[] { "8132 First Street", "Small Town", "CO", "80124"})} }; // Create a statement object from an active connection. Statement stmt = con.createStatement(); try { // If the sample table already exists, drop it. dropObject(con, dropTable); // Create the sample table System.out.println(" Creating the table..."); stmt.executeUpdate(createTable); try { // Creating a prepared statement object System.out.println(" Preparing: " + insertStmt); PreparedStatement pStmt = con.prepareStatement(insertStmt); try { // Call the following method to insert rows into the // sample table insertRows(con, pStmt, data); // The following code will perform a SELECT query // on the table. stmt = con.createStatement(); System.out.println(" Selecting from table " +sTableName); // Submit a query, creating a result set object ResultSet rs = stmt.executeQuery(selectStmt); // Extract and display result set table data System.out.println( "\n Displaying the result set returned by the"+ " select to "+sTableName+" : "); System.out.println("\n -------------------------"); // iterate through all returned rows and display them while (rs.next()) { // retrieve the struct from the fourth column Struct address = (Struct) rs.getObject(4); // retrieve the structs attributes Object[] attributes = address.getAttributes(); // retrieve and display first column value System.out.println("\n Employee Id : " + rs.getObject(1)); // retrieve and display second & third column // value System.out.println("\n Employee Name : " + rs.getObject(2)+", "+rs.getObject(3)); // display attributes of fourth column System.out.println("\n Employee Address: " + attributes[0]+", "+attributes[1]+", " + attributes[2]+" "+attributes[3]); System.out.println(" -------------------------"); } // End while } finally { // close the PreparedStatement pStmt.close(); System.out.println( "\n PreparedStatement object closed.\n"); } } catch (SQLException ex) { // if an exception is thrown, drop the sample table dropObject(con, dropTable); throw ex; } } finally { // close the statement stmt.close(); System.out.println("\n Statement object closed.\n"); } } finally { //Close the connection System.out.println(" Closing Connection"); con.close(); System.out.println(" Sample T22003JD finished. \n"); } } 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 public static void insertRows(Connection con, PreparedStatement pStmt, Object[][] data) throws SQLException { // The following code will perform an INSERT query // on the sample table. System.out.println(" Inserting Rows"); for (int i = 0; i < data.length; i++) { for (int x = 0; x < data[0].length; x++) { // Set parameter values indicated by ? pStmt.setObject(x + 1, data[i][x]); } // Add a row to the batch of insert statements pStmt.addBatch(); } // Insert all the rows into the sample table pStmt.executeBatch(); } // End insertRows public static void dropObject(Connection con, String dropQuery) { try { // The following code will be used to perform a DROP query Statement stmt = con.createStatement(); System.out.println(" Executing command "+dropQuery); stmt.executeUpdate(dropQuery); System.out.println(" Command executed successfully."); } catch (SQLException ex) { // If the table did not exist, no drop is required. // Ignore the raised "no table present" exception by // printing out the error message and swallowing the // exception. System.out.println(" Ignoring exception: " + ex); } } // End dropObject // This class is used instead of calling Connection.createStruct, which is // only supported in JDK 6.0 or later public static class SampStruct implements Struct { private Object[] m_attributes; private String m_sqlTypeName; public SampStruct(String sqlTypeName, Object[] attributes) { m_sqlTypeName = sqlTypeName; m_attributes = attributes; } // Returns attributes public Object[] getAttributes() throws SQLException { return m_attributes; } // Returns SQLTypeName public String getSQLTypeName() throws SQLException { return m_sqlTypeName; } // This method is not supported, but needs to be included public Object[] getAttributes(java.util.Map map) throws SQLException { //Unsupported Exception throw new SQLException("getAttributes(Map) NOT SUPPORTED"); } } // End of class SampStruct } // End of class T22003JD