//********************************************************************* // // Copyright (c) 2010 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T22010JD.java // Header: none // Purpose: Demonstrate basic Teradata SQL using the JDBC API by // creating a table with PERIOD data types and inserting // and selecting the data from the table using custom // type mapping. // 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 the MyPeriod... classes // - Insert data into the table // - Select and print the data from the table using // getObject(int, Map) // - 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 T22010JD { // 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 T22010JD: \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"; // 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 custom type mapping for Period Types to be // used with getObject System.out.println("\n Creating a new type map and putting" + " MyPeriod Class objects in it. "); // Create new type map HashMap typeMap = new HashMap(); // Map the type names to the associated Class objects typeMap.put("PERIOD(DATE)", MyPeriodDate.class); typeMap.put("PERIOD(TIME)", MyPeriodTime.class); typeMap.put("PERIOD(TIMESTAMP)", MyPeriodTimestamp.class); // Creating a prepared statement object System.out.println("\n Preparing: " + insertStmt); PreparedStatement pStmt = con.prepareStatement(insertStmt); try { System.out.println("\n Inserting Rows..."); // Add a row to the batch of insert statements pStmt.setInt(1, 1); pStmt.setObject(2, new MyPeriodDate("PERIOD(DATE)", Date.valueOf("2010-12-14"), Date.valueOf("2010-12-15"))); pStmt.setObject(3, new MyPeriodTime("PERIOD(TIME)", Time.valueOf("09:00:00"), Time.valueOf("17:00:00"))); pStmt.setObject(4, new MyPeriodTimestamp("PERIOD(TIMESTAMP)", Timestamp.valueOf("2010-11-01 10:00:00"), Timestamp.valueOf("2010-11-01 14:00:00"))); pStmt.addBatch(); // Add a row to the batch of insert statements pStmt.setInt(1, 2); pStmt.setObject(2, new MyPeriodDate("PERIOD(DATE)", Date.valueOf("2011-12-14"), Date.valueOf("2011-12-15"))); pStmt.setObject(3, new MyPeriodTime("PERIOD(TIME)", Time.valueOf("05:00:00"), Time.valueOf("20:00:00"))); pStmt.setObject(4, new MyPeriodTimestamp("PERIOD(TIMESTAMP)", Timestamp.valueOf("2012-11-01 10:00:00"), Timestamp.valueOf("2012-11-01 14:00:00"))); 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( "\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 using getObject(int, Map) : "); System.out.println("\n ------------------------"); // iterate through all returned rows and display them while (rs.next()) { // retrieve the PERIOD(DATE) Class object MyPeriodDate perDate = (MyPeriodDate) rs.getObject(2, typeMap); // retrieve the PERIOD(TIME) Class object MyPeriodTime perTime = (MyPeriodTime) rs.getObject(3, typeMap); // retrieve the PERIOD(TIMESTAMP) Class object MyPeriodTimestamp perTimestamp = (MyPeriodTimestamp) rs.getObject(4, typeMap); // retrieve and display first column value System.out.println("\n ColumnInt : " + rs.getObject(1)); // retrieve and display PERIOD(DATE) values System.out.println("\n Begin Date : " + perDate.m_beginDate+"\n End Date : " + perDate.m_endDate); // retrieve and display PERIOD(TIME) values System.out.println("\n Begin Time : " + perTime.m_beginTime+"\n End Time : " + perTime.m_endTime); // retrieve and display PERIOD(TIMESTAMP) values System.out.println("\n Begin Timestamp : " + perTimestamp.m_beginTimestamp + "\n End Timestamp : " + perTimestamp.m_beginTimestamp); 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 T22010JD 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 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 MyPeriodDate implements SQLData { public Date m_beginDate; public Date m_endDate; private String m_sSqlTypeName; public String getSQLTypeName() { return m_sSqlTypeName; } public MyPeriodDate() {}; public MyPeriodDate(String sSqlTypeName, Date beginDate, Date endDate) { m_sSqlTypeName = sSqlTypeName; m_beginDate = beginDate; m_endDate = endDate; } public void readSQL (SQLInput stream, String sSqlTypeName) throws SQLException { m_sSqlTypeName = sSqlTypeName; m_beginDate = stream.readDate(); m_endDate = stream.readDate(); } public void writeSQL (SQLOutput stream) throws SQLException { stream.writeDate(m_beginDate); stream.writeDate(m_endDate); } } // End of class MyPeriodDate public static class MyPeriodTime implements SQLData { public Time m_beginTime; public Time m_endTime; private String m_sSqlTypeName; public String getSQLTypeName() { return m_sSqlTypeName; } public MyPeriodTime() {}; public MyPeriodTime(String sSqlTypeName, Time beginTime, Time endTime) { m_sSqlTypeName = sSqlTypeName; m_beginTime = beginTime; m_endTime = endTime; } public void readSQL (SQLInput stream, String sSqlTypeName) throws SQLException { m_sSqlTypeName = sSqlTypeName; m_beginTime = stream.readTime(); m_endTime = stream.readTime(); } public void writeSQL (SQLOutput stream) throws SQLException { stream.writeTime(m_beginTime); stream.writeTime(m_endTime); } } // End of class MyPeriodTime public static class MyPeriodTimestamp implements SQLData { public Timestamp m_beginTimestamp; public Timestamp m_endTimestamp; private String m_sSqlTypeName; public String getSQLTypeName() { return m_sSqlTypeName; } public MyPeriodTimestamp() {}; public MyPeriodTimestamp(String sSqlTypeName, Timestamp beginTimestamp, Timestamp endTimestamp) { m_sSqlTypeName = sSqlTypeName; m_beginTimestamp = beginTimestamp; m_endTimestamp = endTimestamp; } public void readSQL (SQLInput stream, String sSqlTypeName) throws SQLException { m_sSqlTypeName = sSqlTypeName; m_beginTimestamp = stream.readTimestamp(); m_endTimestamp = stream.readTimestamp(); } public void writeSQL (SQLOutput stream) throws SQLException { stream.writeTimestamp(m_beginTimestamp); stream.writeTimestamp(m_endTimestamp); } } // End of class MyPeriodTimestamp } // End of class T22010JD