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 *
* ** 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. *
* ** 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 ); } }