//********************************************************************* // // Copyright (c) 2010 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T22009JD.java // Header: none // Purpose: Demonstrate basic Teradata SQL using the JDBC API by // creating a table with a nested User Defined Type (UDT), // and inserting and selecting data from the table using // custom type mapping. // The program will: // - Connect as user guest/please // - Create a table with the Student UDT which contains // a nested Address UDT. (Student is created using // sample T22001JD and Address is created using // sample T22000JD.) // - Create data using the Student class // - 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.*; import java.util.HashMap; public class T22009JD { // 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 T22009JD: \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 = "Student"; String sTableName = "StudentTbl"; String createTable = "CREATE TABLE "+sTableName+ " ( studentId INTEGER, "+ " student "+sUDTName+" )"; String dropTable = "DROP TABLE " + sTableName ; String insertStmt = " INSERT INTO "+sTableName+"( ?, ?)"; String selectStmt = " SELECT * FROM "+sTableName+" ORDER BY 2"; // Define the data to be inserted into the table. We need to // create two new Class objects for both Student and Address. Object[][] data = { {"120", new Student("Student", "Will", "Jones", new Address( "Address", "123 Main Street", "My City", "NY", "00245"))}, {"130", new Student("Student", "Joe", "Black", new Address( "Address", "487 Elm Avenue", "Your City", "AZ", "30246"))}, {"140", new Student("Student", "Joe", "Black", new Address( "Address", "287 Elm Avenue", "Your City", "AZ", "30246"))}, {"150", new Student("Student", "Alice", "Frong", new Address( "Address", "1050 Oak Street", "Big City", "CA", "90124"))}, {"160", new Student("Student", "Fred", "Join", new Address( "Address", "8132 First Street", "Small Town", "CO", "80124"))} }; // Creating a custom type mapping for Student & Address System.out.println(" Putting Student and Address Class objects" + " in the connection type map. \n"); // Retrieves the Map object associated with this connection HashMap typeMap = new HashMap(); // Map the UDT names to the associated Class objects typeMap.put("SYSUDTLIB.Address", Address.class); typeMap.put("SYSUDTLIB.Student", Student.class); // Sets the updated Map object for this connection con.setTypeMap(typeMap); // 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 { System.out.println(" Inserting Rows"); for (int i = 0; i < data.length; i++) { // Bind parameter values for (int x = 0; x < data[0].length; x++) 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(); // 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 and display the Student ID System.out.println("\n Student ID : " + rs.getObject(1)); // retrieve the student class object Student student = (Student) rs.getObject(2); // display first and last name from student System.out.println("\n Student Name : " + student.m_sFirstName + ", " + student.m_sLastName); // display student's address System.out.println("\n Student Address: " + student.m_address.m_sStreet +", "+student.m_address.m_sCity +", "+student.m_address.m_sState +" "+student.m_address.m_sZip); 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 T22009JD 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 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 public static class Address implements SQLData { public String m_sStreet; public String m_sCity; public String m_sState; public String m_sZip; private String m_sSqlTypeName; public String getSQLTypeName() { return m_sSqlTypeName; } public Address() {} public Address(String sSqlTypeName, String sStreet, String sCity, String sState, String sZip) { m_sSqlTypeName = sSqlTypeName; m_sStreet = sStreet; m_sCity = sCity; m_sState = sState; m_sZip = sZip; } public void readSQL (SQLInput stream, String sTypeName) throws SQLException { m_sSqlTypeName = sTypeName; m_sStreet = stream.readString(); m_sCity = stream.readString(); m_sState = stream.readString(); m_sZip = stream.readString(); } public void writeSQL (SQLOutput stream) throws SQLException { stream.writeString(m_sStreet); stream.writeString(m_sCity); stream.writeString(m_sState); stream.writeString(m_sZip); } } // End of class Address public static class Student implements SQLData { public String m_sFirstName; public String m_sLastName; public Address m_address; private String m_sSqlTypeName; public String getSQLTypeName() { return m_sSqlTypeName; } public Student() {} public Student(String sSqlTypeName, String sFirstName, String sLastName, Address address) { m_sSqlTypeName = sSqlTypeName; m_sFirstName = sFirstName; m_sLastName = sLastName; m_address = address; } public void readSQL (SQLInput stream, String sTypeName) throws SQLException { m_sSqlTypeName = sTypeName; m_sFirstName = stream.readString(); m_sLastName = stream.readString(); m_address = (Address) stream.readObject(); } public void writeSQL (SQLOutput stream) throws SQLException { stream.writeString(m_sFirstName); stream.writeString(m_sLastName); stream.writeObject(m_address); } } // End of class Student } //End T22009JD