//********************************************************************* // // Copyright (c) 2009 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T22007JD.java // Header: none // Purpose: Demonstrate basic Teradata SQL using the JDBC API by // creating a table with PERIOD data types. // The program will: // - Connect as user guest/please // - Create a table with the PERIOD(DATE), PERIOD(TIME), // and PERIOD(TIMESTAMP) data types // - Create data using class SampStruct 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 T22007JD { // 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 T22007JD: \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 createTable = "CREATE TABLE PerTypes "+ " ( Col1 INTEGER, "+ " Col2 PERIOD(DATE), "+ " Col3 PERIOD(TIME), "+ " Col4 PERIOD(TIMESTAMP) )"; String dropTable = "DROP TABLE PerTypes " ; String insertStmt = " INSERT INTO PerTypes ( ?, ?, ?, ?)"; String selectStmt = " SELECT * FROM PerTypes ORDER BY 1"; long ms1 = new Long("1081331243974").longValue(); long ms2 = new Long("1082341243974").longValue(); long ms3 = new Long("1244742044360").longValue(); long ms4 = new Long("1244839244360").longValue(); // Define the data to be inserted into the table. Object[][] data = { {new Integer(1), new SampStruct("PERIOD(DATE)", new Object[] { new Date(ms1), new Date(ms2)}), new SampStruct("PERIOD(TIME)", new Object[] { new Time(ms1), new Time(ms2)}), new SampStruct("PERIOD(TIMESTAMP)", new Object[] { new Timestamp(ms1), new Timestamp(ms2)})}, {new Integer(2), new SampStruct("PERIOD(DATE)", new Object[] { new Date(ms3), new Date(ms4)}), new SampStruct("PERIOD(TIME)", new Object[] { new Time(ms3), new Time(ms4)}), new SampStruct("PERIOD(TIMESTAMP)", new Object[] { new Timestamp(ms3), new Timestamp(ms4)})}, }; // 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("\n Creating the table..."); stmt.executeUpdate(createTable); try { // Creating a prepared statement object System.out.println("\n 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( "\n Selecting from table PerTypes..."); // 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 PerTypes : "); System.out.println("\n ------------------------"); // iterate through all returned rows and display them while (rs.next()) { // retrieve the PERIOD ( DATE ) Struct Struct perDt = (Struct) rs.getObject(2); // retrieve the PERIOD ( TIME ) Struct Struct perTm = (Struct) rs.getObject(3); // retrieve the PERIOD ( TIMESTAMP ) Struct Struct perTs = (Struct) rs.getObject(4); // retrieve the structs attributes Object[] dateAtt = perDt.getAttributes(); Object[] timeAtt = perTm.getAttributes(); Object[] timestampAtt = perTs.getAttributes(); // retrieve and display first column value System.out.println("\n ColumnInt : " + rs.getObject(1)); // retrieve and display PERIOD(DATE) values System.out.println("\n Start Date : " + dateAtt[0]+"\n End Date : " + dateAtt[1]); // retrieve and display PERIOD(TIME) values System.out.println("\n Start Time : " + timeAtt[0]+"\n End Time : " + timeAtt[1]); // retrieve and display PERIOD(TIMESTAMP) values System.out.println("\n Start Timestamp : " + timestampAtt[0]+"\n End Timestamp : " + timestampAtt[1]); System.out.println("\n ------------------------"); } // 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 T22007JD finished. \n"); } } catch (SQLException ex) { // An 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("\n 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 T22007JD