package com.etretatlogiciels.headfirstsql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This Java code is written because I can't figure out how to use SQL statements to
 * massage t_gregslist into these other tables, as illustrated on pages 338
 * and 381, using UPDATE or other SQL statements as if:
 *
 * UPDATE gregslist
 *   SET zipcode_id =
 *      SELECT zipcode.id
 *         FROM zipcode JOIN gregslist ON zipcode.zipcode = gregslist.zipcode;
 *
 * ...worked. Run this AFTER launching MySQL and running C:\Users\russ\dev\sql\gregslist.sql.
 *
 * @author Russell Bateman
 */
public class HeadFirstSqlChapter9
{
  private static final String CONNECTION_URL = "jdbc:mysql://localhost/headfirst_sql?user=root&password=test123";
  private static Connection   conn           = null;

  private static final void startUp()
  {
    try
    {
      Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
      conn = DriverManager.getConnection( CONNECTION_URL );
    }
    catch( IllegalAccessException e )
    {
      System.out.println( "IllegalAccessException" );
      System.out.println( e.getMessage() );
      e.printStackTrace();
    }
    catch( InstantiationException e )
    {
      System.out.println( "InstantiationException" );
      System.out.println( e.getMessage() );
      e.printStackTrace();
    }
    catch( ClassNotFoundException e )
    {
      System.out.println( "ClassNotFoundException" );
      System.out.println( e.getMessage() );
      e.printStackTrace();
    }
    catch( SQLException e )
    {
      System.out.println( "SQLException: getConnection()" );
      System.out.println( e.getMessage() );
      e.printStackTrace();
    }
  }

  /**
   * S Q L   s t a t e m e n t s
   *
   * The main table, t_gregslist, has a field, zipcode, out of which we've
   * already built a new, auxiliary table, t_zipcode. The latter contains a list
   * of all the discrete zipcodes originally in t_gregslist. This method walks
   * the main table to fill its (new) field, zipcode_id, with the corresponding
   * id from t_zipcode. Then, we drop the zipcode column from the main table
   * since we no longer need it.
   */
  public static final void fixUpZipcodes()
  {
    try
    {
      String    query     = null;
      String    update    = null;
      ResultSet gl_result = null;

      Statement stmt = conn.createStatement();

      /* Walk t_gregslist to fill its zipcode_id field with the corresponding
       * id from the t_zipcode table.
       */
      try
      {
        query = "SELECT id, zipcode FROM t_gregslist;";
        System.out.println( query );
        gl_result = stmt.executeQuery( query );

        /* Now result is full of ids and zipcodes. Use them, one by one, to
         * fill in the gregslist zipcode_id field with the id key out of the
         * zipcode table.
         */
        while( gl_result.next() )
        {
          int       gl_id   = gl_result.getInt   ( "id" );
          String    zipcode = gl_result.getString( "zipcode" );
          Statement stmt_a  = conn.createStatement();

          query = "SELECT id FROM t_zipcode WHERE zipcode = '" + zipcode + "';";
          System.out.println( query );
          ResultSet  zip_result = stmt_a.executeQuery( query );

          if( zip_result.next() )
          {
            /* Alter zipcode_id in t_gregslist, the column we're going to
             * keep, to hold the id in the new t_zipcode table.
             */
            int       zid    = zip_result.getInt( "id" );
            Statement stmt_b = conn.createStatement();

            update = "UPDATE t_gregslist SET zipcode_id = '" + zid + "' WHERE id = '" + gl_id + "';";
            System.out.println( update );
            stmt_b.executeUpdate( update );
          }
        }

        // now drop column zipcode from t_gregslist...
        Statement stmt_c = conn.createStatement();

        update = "ALTER TABLE t_gregslist DROP column zipcode;";
        System.out.println( update );
        stmt_c.executeUpdate( update );

      }
      catch( SQLException e )
      {
        System.out.println( "SQLException: " + e.getMessage() );
        System.out.println( "SQLState:     " + e.getSQLState() );
        System.out.println( "VendorError:  " + e.getErrorCode() );
        e.printStackTrace();
      }
    }
    catch( Exception e )
    {
      System.out.println( "Holy crap, Batman!" );
      System.out.println( e.getMessage() );
      e.printStackTrace();
    }
  }

  /**
   * Shortened version of fixUpTable() when the table, field and id field names
   * are purely orthogonal.
   *
   * @param whichTable - the auxiliary table (not t_gregslist).
   * @see fixUpTable( String whichTable, String whichField )
   */
  public static final void fixUpTable( String whichTable )
  {
    String whichField = whichTable.substring( 2, whichTable.length() );
    fixUpTable( whichTable, whichField, whichField + "_id" );
  }

  /**
   * P r e p a r e d    s t a t e m e n t s
   *
   * We use a prepared statement here to do any table/field combination and not
   * just t_zipcode/zipcode as above.
   *
   * Nota bene: Prepared statements enclose anything you pass to the pronoun or
   * place-holder with single quotes. For this reason, you cannot pass
   *
   *   - a table name. 
   *   - a column name. 
   *  - the column name to drop in an ALTER statement. 
   *
   * Therefore, prepared statements cannot be used to wildcard just everything.
   *
   * @param whichTable - the auxiliary table (not t_gregslist) whose id
   *         column will be copied to t_gregslist's column.
   * @param whichField - the column in t_gregslist and in the auxiliary
   *         table that contains the value that interests us on each row.
   */
  public static final void fixUpTable( String whichTable, String whichField, String idField )
  {
    String  query = null, update = null;

    try
    {
      ResultSet gl_result = null;

      /* Walk t_gregslist to fill its whichField with the corresponding
       * id from the whichTable.
       */
      try
      {
        Statement stmt = conn.createStatement();

        query = "SELECT id, " + whichField + " FROM t_gregslist;";
        System.out.println( query );
        gl_result = stmt.executeQuery( query );

        /* Now result is full of ids and whichFields. Use them, one by one, to
         * fill in the t_gregslist whichField_id with the id key out of the
         * whichTable.
         */
        while( gl_result.next() )
        {
          int    gl_id      = gl_result.getInt   ( "id" );
          String  fieldValue = gl_result.getString( whichField );

          query = "SELECT id FROM " + whichTable + " WHERE " + whichField + " = ?";
          PreparedStatement  stmt_a = conn.prepareStatement( query );

          stmt_a.setString( 1, fieldValue );
          System.out.println( "Query statement: " + stmt_a.toString() );
          ResultSet  sub_result = stmt_a.executeQuery();

          if( sub_result.next() )
          {
            int zid = sub_result.getInt( "id" );

            update = "UPDATE t_gregslist SET " + idField + " = ? WHERE id = ?;";

            PreparedStatement stmt_b = conn.prepareStatement( update );

            stmt_b.setInt( 1, zid );
            stmt_b.setInt( 2, gl_id );
            System.out.println( "Update statement: " + stmt_b.toString() );
            stmt_b.executeUpdate();
          }
        }

        // now drop column profession from t_gregslist...
        Statement stmt_c = conn.createStatement();

        update = "ALTER TABLE t_gregslist DROP COLUMN " + whichField + ";";

        System.out.println( update );
        stmt_c.executeUpdate( update );

      }
      catch( SQLException e )
      {
        System.out.println( "SQLException: " + e.getMessage() );
        System.out.println( "SQLState:     " + e.getSQLState() );
        System.out.println( "VendorError:  " + e.getErrorCode() );
        e.printStackTrace();
      }
    }
    catch( Exception e )
    {
      System.out.println( "Holy crap, Batman!" );
      System.out.println( e.getMessage() );
      e.printStackTrace();
    }
  }

  /**
   * Create an intermediate table that links t_gregslist by contact_id with
   * t_interests by interest_id. This intermediate table is called a junction
   * table (see Head First SQL, page 315). The new table's name will be
   * t_contact_interest.
   *
   * Do this also for t_contact_seeking, etc.
   *
   * This is pretty ugly code and I'm ashamed of it, but at this point, I just
   * wanted to get on with the exercises in chapter 9.
   *
   * See pages 316, 338 and 381.
   */
  public static final void linkUpInterests( String junctionTable, String otherTable, String fieldName )
  {
    String create  = null, query = null, update = null, oldTableField = null;

    if( fieldName == null )
      fieldName = otherTable.substring( 2, otherTable.length() );
    oldTableField = fieldName;
    if( fieldName.equals( "interest" ) )
      oldTableField += "s";

    String otherId = fieldName + "_id";

    try
    {
      ResultSet otab_result = null;
      ResultSet sub_result  = null;

      try
      {
        Statement stmt = conn.createStatement();

        // create the junction table...
        create = "CREATE TABLE " + junctionTable
             + "("
             + " contact_id INTEGER, "
             +  otherId + " INTEGER "
             + ");";
        System.out.println( create );
        stmt.executeUpdate( create );

        /* Get a list of both the id and important fields in the other table.
         */
        query = "SELECT id, " + fieldName + " FROM " + otherTable + ";";
        System.out.println( query );
        otab_result = stmt.executeQuery( query );

        /* Now result is full of ids and principal fields (like 'interest').
         * Use them, one by one, to fill in the junction table's if fields
         * with the id key out of the other table.
         */
        while( otab_result.next() )
        {
          int    other_id   = otab_result.getInt   ( "id" );
          String fieldValue = otab_result.getString( fieldName );

          Statement  stmt_a = conn.createStatement();

          /* Get a list of rows in t_gregslist that share the interest named
           * in 'fieldValue'.
           */
          query = "SELECT id FROM t_gregslist WHERE " + oldTableField + " LIKE '%" + fieldValue + "%';";
          System.out.println( query );
          sub_result = stmt_a.executeQuery( query );

          if( sub_result.next() )
          {
            /* Alter the id field in the junction table to hold the id of
             * the field in question. Also, alter the contact_id field to
             * refer to t_gregslist.
             */
            int gl_id = sub_result.getInt( "id" );

            update = "INSERT INTO " + junctionTable + "( contact_id, " + otherId + " ) VALUES( ?, ? );";

            PreparedStatement stmt_b = conn.prepareStatement( update );

            stmt_b.setInt( 1, gl_id );
            stmt_b.setInt( 2, other_id );
            System.out.println( "Update statement: " + stmt_b.toString() );
            stmt_b.executeUpdate();
          }
        }

        // now drop column profession from t_gregslist...
        Statement stmt_c = conn.createStatement();

        update = "ALTER TABLE t_gregslist DROP COLUMN " + oldTableField + ";";

        System.out.println( update );
        stmt_c.executeUpdate( update );

      }
      catch( SQLException e )
      {
        System.out.println( "SQLException: " + e.getMessage() );
        System.out.println( "SQLState:     " + e.getSQLState() );
        System.out.println( "VendorError:  " + e.getErrorCode() );
        e.printStackTrace();
      }
    }
    catch( Exception e )
    {
      System.out.println( "Holy crap, Batman!" );
      System.out.println( e.getMessage() );
      e.printStackTrace();
    }
  }

  public static void main( String[] args )
  {
    startUp();
    fixUpZipcodes();
    System.out.println( "---------------------------------------------------------------" );
    fixUpTable( "t_profession", "profession", "prof_id" );
    System.out.println( "---------------------------------------------------------------" );
    fixUpTable( "t_status" );
    System.out.println( "---------------------------------------------------------------" );
    linkUpInterests( "t_contact_interest", "t_interests", "interest" );
    System.out.println( "---------------------------------------------------------------" );
    linkUpInterests( "t_contact_seeking", "t_seeking", null );
  }
}

Copyright ©2010 by Russell Bateman and Etretat Logiciels, LLC.

Permission is granted for any derivational use. You are forbidden only from reposting the texts and any sources of this article without express permission of the author.