SQL Notes

package com.dzone.batchsql;

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

/**
 * Demonstrate batch update.
 *
 * id  name  code  department
 *
 * 1   Allen abc   Sales
 * 2   Max   102   Marketing
 * 3   Ward  55    Sales
 * 4   Sam   xyz   Marketing
 *
 * TODO: Integrate use of PreparedStatement into this example.
 */
public class BatchSql1
{
  private static final String CONNECTION_URL = "jdbc:mysql://localhost/personnel?user=root&;password=test123";

  Connection connection = null;

  public BatchSql1()
  {
    try
    {
      connection = DriverManager.getConnection( CONNECTION_URL );
    }
    catch( SQLException e )
    {
      System.out.println( "SQLException: " + e.getMessage() );
      System.out.println( "SQLState:     " + e.getSQLState() );
      System.out.println( "VendorError:  " + e.getErrorCode() );
    }
  }

  private final String[] newEmployeesString =
  {
    "INSERT INTO employee { id, name, code, department } values ( '1', 'Allen', 'abc', 'Sales' )",
    "INSERT INTO employee { id, name, code, department } values ( '2', 'Max',   '102', 'Marketing' )",
    "INSERT INTO employee { id, name, code, department } values ( '3', 'Ward',  'xyz', 'Sales' )",
    "INSERT INTO employee { id, name, code, department } values ( '4', 'Sam',   '55',  'Marketing' )",
  };

  /**
   * Hits the database for each insert statement.
   */
  public void doItTheSlowWay() throws SQLException
  {
    Statement statement  = connection.createStatement();

    for( String insert : newEmployeesString )
      statement.execute( insert );

    statement.close();
    connection.close();
  }

  private final Employee[] newEmployees =
  {
    new Employee( 1, "Allen", "abc", "Sales" ),
    new Employee( 2, "Max",   "102", "Marketing" ),
    new Employee( 3, "Ward",  "xyz", "Sales" ),
    new Employee( 4, "Sam",   "55",  "Marketing" )
  };

  /**
   * Create a batch of inserts, then insert all together at one time. Note
   * that batch updates can include or consist of updates or deletes too.
   */
  public void doItTheFasterWay() throws SQLException
  {
    PreparedStatement statement = connection.prepareStatement( INSERT );

    for( Employee employee : newEmployees )
    {
      StringBuilder query = new StringBuilder();

      query.append( "INSERT INTO employee( id, name, code, department ) " );
      query.append( "VALUES (' " );
      query.append( employee.getId()         ).append( "', '" );
      query.append( employee.getName()       ).append( "', '" );
      query.append( employee.getCode()       ).append( "', '" );
      query.append( employee.getDepartment() );
      query.append( "')" );
      statement.addBatch( query.toString() );
    }

    statement.executeBatch();

    statement.close();
    connection.close();
  }

  private final String INSERT = "INSERT INTO employee( id, name, code, department ) VALUES( ?, ?, ?, ? )";

  public void doItFastAndThePreparedWay() throws SQLException
  {
    PreparedStatement statement = connection.prepareStatement( INSERT );

    for( Employee employee : newEmployees )
    {
      statement.setInt   ( 1, employee.getId() );
      statement.setString( 2, employee.getName() );
      statement.setString( 3, employee.getCode() );
      statement.setString( 4, employee.getDepartment() );
      statement.addBatch();
    }

    statement.executeBatch();

    statement.close();
    connection.close();
  }
}