package test;

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

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: Postal Communications of Tatarstan</p>
 *
 * @author Eugene Andrewhin
 * @version 1.0
 */
public class PreparedStatementBugFix {
  public static final String qryCreateTable =
      "CREATE TABLE IF NOT EXISTS `test_table` (`int_field` int(6) unsigned NOT NULL default '0') " +
      "ENGINE=InnoDB DEFAULT CHARSET=koi8r";
  public static final String qryDropTable = "DROP TABLE IF EXISTS `test_table`";
  public static final String qryInsert = "INSERT `test_table` VALUES (123456)";
  public static final String qrySimpleSelect =
      "SELECT `int_field` FROM `test_table` WHERE `int_field` = 123456";
  public static final String qryPreparedSelect =
      "SELECT `int_field` FROM `test_table` WHERE `int_field` = ?"; // 123456 not eq 4295090752
  private Connection conn;

  public PreparedStatementBugFix () {
    initConnection();
  }

  protected void finalize() {
    finitConnection();
  }

  private void initConnection() {
    Statement stmt = null;
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance(); //org.gjt.mm.mysql.Driver
      try {
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test" , "root" , "root");
        stmt = conn.createStatement();
        stmt.execute(qryCreateTable);
        stmt.execute(qryInsert);
      } catch(SQLException ex) { ex.printStackTrace(); }
    } catch(ClassNotFoundException ex) { ex.printStackTrace();
    } catch(IllegalAccessException ex) { ex.printStackTrace();
    } catch(InstantiationException ex) { ex.printStackTrace();
    }
  }

  private void finitConnection() {
    try {
      Statement stmt = conn.createStatement();
      try {
        stmt.execute(qryDropTable);
      } finally {
        conn.close();
      }
    } catch(SQLException ex) { /* do nothing*/ }
  }

  public void testSimpleSelect() {
    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(qrySimpleSelect);
      while(rs.next()) System.out.println(rs.getObject(1));
    } catch(SQLException ex) { ex.printStackTrace();
    } finally {
      if(rs != null) try { rs.close(); } catch(SQLException ex) { /* do nothing*/ }
      if(stmt != null) try { stmt.close(); } catch(SQLException ex) { /* do nothing*/ }
    }
  }

  public void testPreparedSelect() {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = conn.prepareStatement(qryPreparedSelect);
      pstmt.setInt(1,123456);
      rs = pstmt.executeQuery();
      while(rs.next()) System.out.println(rs.getObject(1));
    } catch(SQLException ex) { ex.printStackTrace();
    } finally {
      if(rs != null) try { rs.close(); } catch(SQLException ex) { /* do nothing*/ }
      if(pstmt != null) try { pstmt.close(); } catch(SQLException ex) { /* do nothing*/ }
    }
  }

  public static void main (String[] args) {
    PreparedStatementBugFix bugfix = new PreparedStatementBugFix();
    bugfix.testSimpleSelect();
    /* result - 123456 */
    bugfix.testPreparedSelect();
    /* result - 4295090752 */
  }
}

/*
123456		                11110001001000000
4295090752	100000000000000011110001001000000
*/