import java.util.*;
import java.io.*;
import java.sql.*;
import java.math.*;


public class AccessMethods {

  private String DRIVER;
  private String URL;
  private String USER;
  private String PASSWD;
  private String TABLENAME;


  public AccessMethods(String driver, String url, String username, 
                       String passwd, String tablename) {
    DRIVER = driver;
    URL = url;
    USER = username;
    PASSWD = passwd;
    TABLENAME = tablename;

  }

  public static void main(String args[]) throws Exception {

    AccessMethods acc = new AccessMethods( "org.gjt.mm.mysql.Driver", 
                                           "jdbc:mysql://localhost/JWTest", 
                                           "username", 
                                           "password", 
                                           "Article" );

    String [] fieldnames = { "ArticleID", "Title", "Text",
                             "WordCount", "SubmitDate", "Rating" };

    // fieldvalues_in contains the data to put in the database
    Object [] fieldvalues_in = new Object[fieldnames.length];

    // fieldvalues_out contains the data retrieved from the database
    Object [] fieldvalues_out;
   
    
    // Make up some data
    fieldvalues_in[0] = new Long(0);
    fieldvalues_in[1] = new String("Dummy Article");
    fieldvalues_in[2] = new String("This is a dummy article");
    fieldvalues_in[3] = new Integer(5);
    fieldvalues_in[4] = new Timestamp ( (new java.util.Date()).getTime() );
    fieldvalues_in[5] = new BigDecimal(2.5);

    // Store data in database
    acc.insert( fieldnames, fieldvalues_in );

    // Retrieve data 
    fieldvalues_out = acc.select( fieldnames, "ArticleID", (new Long(0)) );

    // display
    System.out.println( ((Long) fieldvalues_out[0]).toString() );
    System.out.println( ((String) fieldvalues_out[1]) );
    System.out.println( ((String) fieldvalues_out[2]) );
    System.out.println( ((Integer) fieldvalues_out[3]).toString() );
    System.out.println( ((Timestamp) fieldvalues_out[4]).toString() );
    System.out.println( ((BigDecimal) fieldvalues_out[5]).toString() );

    return;
  }

  // Establish the JDBC connection object to the database

  private Connection getConn() throws Exception {

    Class.forName(DRIVER);
    Connection conn = DriverManager.getConnection(URL, USER, PASSWD);
    return conn;

  }

  // Assembly "arg1,arg2,...argn" type of string  to use in 
  // SQL statements from the input array of strings.

  public String getSQLList(String [] fields) throws Exception {

    StringBuffer result = new StringBuffer();
    for (int i = 0; i < fields.length -1; i++) {
      result.append( fields[i] );
      result.append( " , " );
    }
    result.append( fields[fields.length-1] );
    return result.toString();

  }

  public boolean insert (String [] fieldnames,
                         Object [] fieldvalues) throws Exception {

    // Generate an array of question marks for the SQL template parameters
    String [] paras = new String[fieldnames.length];
    for (int i = 0; i < fieldnames.length; i++) {
      paras[i] = "?";
    }

    // Prepare the template
    String SQLstr = "INSERT INTO " + TABLENAME + " ( " + 
                    getSQLList(fieldnames) + " ) VALUES ( " +
                    getSQLList(paras) + " ) ";
    Connection conn = getConn();
    PreparedStatement pstmt = conn.prepareStatement(SQLstr);
    // set parameter values
    for (int i = 0; i < fieldnames.length; i++) {
      pstmt.setObject(i + 1, fieldvalues[i]);
    }
    // execute SQL statement
    boolean succ = pstmt.execute();
    pstmt.close();
    conn.close();
    
    return succ;
  }

  public Object [] select (String [] fieldnames, String idFieldname,
                           Object idValue) throws Exception {

    Object [] result = new Object [fieldnames.length];

    // Prepared the template
    String SQLstr = "SELECT " + getSQLList(fieldnames) + " FROM " + 
                    TABLENAME + " WHERE " + idFieldname + " = ?";

    Connection conn = getConn();
    PreparedStatement pstmt = conn.prepareStatement(SQLstr);
    // set parameter value
    pstmt.setObject(1, idValue);

    // execute SQL statement
    ResultSet rs = pstmt.executeQuery();
    // go to the first record
    rs.next();
    for ( int i = 0; i < fieldnames.length; i++) {
      result[i] = rs.getObject(i+1);
    }
    rs.close();
    pstmt.close();
    conn.close();

    return result;
  }

}