import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * Database helper class, this is a sort of mini JDBCTemplate
 * from Spring that can be dropped into projects that don't
 * want the bloat of the Spring libraries.
 * 
 * Contains various execute* methods for running queries. If
 * no parameters are passed, a JDBC Statement is used to
 * prevent the PreparedStatement cache filling up with unreusable
 * queries.
 *
 * @author Robin Rawson-Tetley
 * Consider this class public domain.
 *
 */
public class JDBCHandler 
{
	private static final boolean DEBUG = true;
	
	/**
	 * Returns a connection to the database.  
	 * @param db A valid DataSource
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	private static Connection dbConnection(DataSource db) throws SQLException, NamingException
	{
		return db.getConnection();		
	}
	
	public static void log(String m, long timeStarted) {
		long dur = System.currentTimeMillis() - timeStarted;
		System.out.println(m + " (" + dur + "ms)");
	}
	
	/**
	 * Executes an action query against the specified database.
	 * @param db
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static int execute(DataSource db, String sql) throws Exception {
		return execute(db, sql, null);
	}
	
	/**
	 * Executes an action query with parameters against the
	 * specified database. 
	 * 
	 * @param db
	 * @param sql
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public static int execute(DataSource db, String sql, Object[] parameters) throws Exception {
		
		Connection con = null;		
		PreparedStatement pstmt = null;
		Statement stmt = null;
		
		try
		{
			
			long t = System.currentTimeMillis();
			
			// Get connection and parse/precompile statement
			con = dbConnection(db);
			
			// Use a regular statement if we have no parameters
			// so we don't fill up the PreparedStatement cache on
			// the appserver
			if (parameters != null && parameters.length > 0) {
				pstmt = con.prepareStatement(sql);
				for (int i = 0; i < parameters.length; i++) {
					pstmt.setObject(i + 1, parameters[i]);
				}
				if (DEBUG) log("EXECUTE(PS): " + sql, t);
				return pstmt.executeUpdate();
			}
			else {
				stmt = con.createStatement();
				if (DEBUG) log("EXECUTE(S): " + sql, t);
				return stmt.executeUpdate(sql);
			}
		}
		catch (Exception e) {
			if (DEBUG) e.printStackTrace();
			throw e;
		}
		finally {
			if (stmt != null) 
				try { stmt.close(); } catch (Exception e) {}
			if (pstmt != null)
				try { pstmt.close(); } catch (Exception e) {}
			if (con != null) 
				try { con.close(); } catch (Exception e) {}
			stmt = null;
			con = null;
		}
	}
	
	
	/**
	 * Executes a query against the db specified and
	 * calls the map() method of the RowMapper for each
	 * result
	 * 
	 * @param db
	 * @param sql
	 * @param mapper
	 * @throws Exception
	 */
	public static void executeQuery(DataSource db, String sql, 
			RowMapper mapper) throws Exception {
		executeQuery(db, sql, null, mapper);
	}
	
	/**
	 * Executes a parameterised query against the database
	 * specified. Calls back the map() method of the RowMapper
	 * argument for every result returned.
	 * 
	 * @param db
	 * @param sql
	 * @param parameters
	 * @param mapper
	 * @throws Exception
	 */
	public static void executeQuery(DataSource db, String sql, 
			Object[] parameters, RowMapper mapper) throws Exception {
		
		Connection con = null;		
		PreparedStatement stmt = null;
		ResultSet r = null;
		
		try
		{
			
			long t = System.currentTimeMillis();
			
			// Get connection and parse/precompile statement
			con = dbConnection(db);
			stmt = con.prepareStatement(sql);
			
			// Add parameters
			if (parameters != null && parameters.length > 0) {
				for (int i = 0; i < parameters.length; i++) {
					stmt.setObject(i + 1, parameters[i]);
				}
			}
			
			// Execute the query and iterate the results
			r = stmt.executeQuery();
			if (DEBUG) log("EXECUTEQUERY: " + sql, t);
			while (r.next()) {
				mapper.map(r);
			}
			
		}
		catch (Exception e) {
			if (DEBUG) e.printStackTrace();
			throw e;
		}
		finally {
			if (r != null) 
				try { r.close(); } catch (Exception e) {}
			if (stmt != null) 
				try { stmt.close(); } catch (Exception e) {}
			if (con != null) 
				try { con.close(); } catch (Exception e) {}
			r = null;
			stmt = null;
			con = null;
		}
	}
	
	/**
	 * Executes a query that is expected to return one row/column
	 * containing a string. 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static String executeForString(DataSource db, String sql) throws Exception {
		return executeForString(db, sql, null);
	}
	
	/**
	 * Executes a query that is expected to return one row/column
	 * containing a string. 
	 * @param db
	 * @param sql
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public static String executeForString(DataSource db, String sql, Object[] parameters) throws Exception {
		Connection con = null;		
		PreparedStatement stmt = null;
		ResultSet r = null;
		
		try
		{
			long t = System.currentTimeMillis();
			
			// Get connection and parse/precompile statement
			con = dbConnection(db);
			stmt = con.prepareStatement(sql);
			
			// Add parameters
			if (parameters != null && parameters.length > 0) {
				for (int i = 0; i < parameters.length; i++) {
					stmt.setObject(i + 1, parameters[i]);
				}
			}
			
			// Execute the query and iterate the results
			r = stmt.executeQuery();
			if (DEBUG) log("EXECUTEFORSTRING: " + sql, t);
			if (r.next()) {
				String v = r.getString(1);
				if (r.wasNull())
					return "";
				else 
					return v;
			}
			return "";
		}
		catch (Exception e) {
			if (DEBUG) e.printStackTrace();
			throw e;
		}
		finally {
			if (r != null) 
				try { r.close(); } catch (Exception e) {}
			if (stmt != null) 
				try { stmt.close(); } catch (Exception e) {}
			if (con != null) 
				try { con.close(); } catch (Exception e) {}
			r = null;
			stmt = null;
			con = null;
		}
	}
	
	/**
	 * Executes a query that is expected to return one row/column
	 * containing an integer. 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static int executeForInt(DataSource db, String sql) throws Exception {
		return executeForInt(db, sql, null);
	}
	
	/**
	 * Executes a query that is expected to return one row/column
	 * containing an integer 
	 * @param db
	 * @param sql
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public static int executeForInt(DataSource db, String sql, Object[] parameters) throws Exception {
		Connection con = null;		
		PreparedStatement stmt = null;
		ResultSet r = null;
		
		try
		{
			long t = System.currentTimeMillis();
			
			// Get connection and parse/precompile statement
			con = dbConnection(db);
			stmt = con.prepareStatement(sql);
			
			// Add parameters
			if (parameters != null && parameters.length > 0) {
				for (int i = 0; i < parameters.length; i++) {
					stmt.setObject(i + 1, parameters[i]);
				}
			}
			
			// Execute the query and iterate the results
			r = stmt.executeQuery();
			if (DEBUG) log("EXECUTEFORINT: " + sql, t);
			if (r.next()) {
				return r.getInt(1);
			}
			return 0;
		}
		catch (Exception e) {
			if (DEBUG) e.printStackTrace();
			throw e;
		}
		finally {
			if (r != null) 
				try { r.close(); } catch (Exception e) {}
			if (stmt != null) 
				try { stmt.close(); } catch (Exception e) {}
			if (con != null) 
				try { con.close(); } catch (Exception e) {}
			r = null;
			stmt = null;
			con = null;
		}
	}
	
	/**
	 * RowMapper class to be used with SELECT queries.
	 * The map method will be fired for each row in
	 * the results.
	 * 
	 * @author Robin Rawson-Tetley
	 */
	public static abstract class RowMapper {
		public abstract void map(ResultSet r) throws Exception;
	}
}