//===========================================================================
// package
//===========================================================================
package jp.gr.java_conf.bugslife.db;

//===========================================================================
// import
//===========================================================================
// Java core API
import java.io.*;
import java.sql.*;
import java.util.*;
import java.text.SimpleDateFormat;
import org.apache.commons.logging.*;

import org.apache.commons.beanutils.*;

// Util API
import jp.gr.java_conf.bugslife.util.*;
import jp.gr.java_conf.bugslife.system.*;


/**
 *<PRE>
 * 
 * DAOクラス
 *
 *</PRE>
 * @version	0.0, 2001/09/10
 * @author	鰐川　正寛
 */
public class DAO
{
//===========================================================================
// attributes
//===========================================================================
	private static SimpleDateFormat DB_DATE = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private static SimpleDateFormat DB_DATE2 = new SimpleDateFormat("yyyy-MM-dd");
	
	public static int EXECUTE_COUNT_ANY = 0;
	public static int EXECUTE_COUNT_ONE = 1;
	
	public static boolean PK_TYPE_AUTO_INCREMENT = true;
	public static boolean PK_TYPE_SET = false;
	
	/**
	 * Log
	 */
	private static Log log = LogFactory.getLog(DAO.class.getName());
//===========================================================================
// methods
//===========================================================================
	/**
	 *	insert
	 */
	public static int insert(Object target) {
		//interface check
		IUpdatable targetIF = null;
		try {
			targetIF = (IUpdatable)target;
		} catch(ClassCastException cce) {
			Assert.check(false, "IUpdatable not inpremented");
		}
		//get table name
		String tableName = getTableName(target);
		
		Map map = null;
		try {
			map = PropertyUtils.describe(target);
			//debug
			//displayMap(map);
			
		}catch (Exception e) {
			
			e.printStackTrace();
			
			//RUNTIME ERROR!
			throw new BLRuntimeException("describe error target = " + getTableName(target), e);
		}
		String[] itemNames = targetIF.getUpdateItemNames();
		
		ArrayList values = makeValues(map, itemNames);
		
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO " + tableName + " ");
		for(int i = 0; i < itemNames.length; i++) {
			if (i == 0) {
				sb.append("(");
			} else { 
				sb.append(",");
			}
			sb.append(itemNames[i]);
		}
		sb.append(")");
		sb.append(" VALUES ");
		for(int i = 0; i < itemNames.length; i++) {
			if (i == 0) {
				sb.append("(");
			} else { 
				sb.append(",");
			}
			sb.append("?");
		}
		sb.append(")");
		
		int ret = 0;
		
		ret = executeUpdate(sb.toString(), values, EXECUTE_COUNT_ONE, targetIF.isAutoIncrement());
		
		return ret;
	}
	/**
	 *	update
	 */
	public static int update(Object target) {
		//interface check
		IUpdatable targetIF = null;
		try {
			targetIF = (IUpdatable)target;
		} catch(ClassCastException cce) {
			Assert.check(false, "IUpdatable not inpremented");
		}
		//get table name
		String tableName = getTableName(target);
		
		Map map = null;
		try {
			map = PropertyUtils.describe(target);
			//debug
			//displayMap(map);
			
		}catch (Exception e) {
			//RUNTIME ERROR!
			throw new BLRuntimeException("describe error target = " + getTableName(target), e);
		}
		String[] itemNames = targetIF.getUpdateItemNames();
		
		ArrayList values = makeValues(map, itemNames);
		
		StringBuffer sb = new StringBuffer();
		sb.append("UPDATE " + tableName + " SET ");
		for(int i = 0; i < itemNames.length; i++) {
			if (i != 0) {
				sb.append(",");
			}
			sb.append(itemNames[i] + " = ? ");
		}
		//
		String[] pks = null;
		int updateCount = EXECUTE_COUNT_ANY;
		//排他制御ありの場合、最終更新日付をPKに入れる
		if (targetIF.isExclusion()) {
			int size = targetIF.getPKItems().length;
			pks = new String[size + 1];
			System.arraycopy(targetIF.getPKItems(), 0, pks, 0, size);
			pks[size] = "updateDate";
			
			//追加
			updateCount = EXECUTE_COUNT_ONE;
			
		//そうでなければPKをそのまま入れる
		} else {
			pks = targetIF.getPKItems();
		}
		values = makeValues(map, pks, values);
		
		sb.append(" WHERE ");
		for (int i = 0; i < pks.length; i++) {
			if (i != 0) {
				sb.append(" AND ");
			}
			sb.append(pks[i]);
			sb.append(" = ?");
		}
		
		int ret = 0;
		ret = executeUpdate(sb.toString(), values, updateCount, false);
		
		return ret;
	}
	/**
	 *	delete
	 */
	public static int delete(Object target) {
		//interface check
		IUpdatable targetIF = null;
		try {
			targetIF = (IUpdatable)target;
		} catch(ClassCastException cce) {
			Assert.check(false, "IDeletable not inpremented");
		}
		//get table name
		String tableName = getTableName(target);
		
		Map map = null;
		try {
			map = PropertyUtils.describe(target);
			//debug
			//displayMap(map);
			
		}catch (Exception e) {
			//RUNTIME ERROR!
			throw new BLRuntimeException("describe error target = " + getTableName(target), e);
		}
		String[] pks = null;
		//排他制御ありの場合、最終更新日付をPKに入れる
		if (targetIF.isExclusion()) {
			int size = targetIF.getPKItems().length;
			pks = new String[size + 1];
			System.arraycopy(targetIF.getPKItems(), 0, pks, 0, size);
			pks[size] = "updateDate";
		}
		ArrayList values = makeValues(map, pks);
		
		StringBuffer sb = new StringBuffer();
		sb.append("DELETE FROM " + tableName + " WHERE ");
		for (int i = 0; i < pks.length; i++) {
			if (i != 0) {
				sb.append(" AND ");
			}
			sb.append(pks[i]);
			sb.append(" = ?");
		}
		int ret = 0;
		ret = executeUpdate(sb.toString(), values, EXECUTE_COUNT_ONE, false);
		
		return ret;
	}
	/**
	 *	load
	 */
	public static void load(Object target) {
		//interface check
		ILoadable targetIF = null;
		try {
			targetIF = (ILoadable)target;
		} catch(ClassCastException cce) {
			Assert.check(false, "ILoadable not inpremented");
		}
		
		Map map = null;
		try {
			map = PropertyUtils.describe(target);
			//debug
			//displayMap(map);
			
		}catch (Exception e) {
			//RUNTIME ERROR!
			throw new BLRuntimeException("describe error target = " + getTableName(target), e);
		}
		
		//get table name
		String tableName = getTableName(target);
		
		String[] pks = targetIF.getPKItems();
		ArrayList values = makeValues(map, pks);
		
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT * FROM " + tableName + " WHERE ");
		for (int i = 0; i < pks.length; i++) {
			if (i != 0) {
				sb.append(" AND ");
			}
			sb.append(pks[i]);
			sb.append(" = ?");
		}
		int ret = 0;
		
		ArrayList list = null;
		list = executeQuery(sb.toString(), values);
		
		if (list.size() == 0) {
			//1件もない場合
			log.debug("query is empty");
			
		} else {
			HashMap properties = (HashMap)list.get(0);
			try {
				BeanUtils.populate(target, properties);
			} catch(Exception e) {
				//RUNTIME EXCPTION
				throw new BLRuntimeException("populate error", e);
				
			}
		}
	}
	/**
	 *	findAll
	 */
	public static ArrayList findAll(Object target) {
		//interface check
		ILoadable targetIF = null;
		try {
			targetIF = (ILoadable)target;
		} catch(ClassCastException cce) {
			Assert.check(false, "ILoadable not inpremented");
		}
		
		Map map = null;
		try {
			map = PropertyUtils.describe(target);
			//debug
			//displayMap(map);
			
		}catch (Exception e) {
			//RUNTIME ERROR!
			throw new BLRuntimeException("describe error target = " + getTableName(target), e);
		}
		
		//get table name
		String tableName = getTableName(target);
		
		String[] pks = targetIF.getPKItems();
		/*----------------------------
		ArrayList values = new ArrayList();
		addValues(map, pks, values);
		----------------------------*/
		
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT * FROM " + tableName);
		sb.append(" ORDER BY ");
		for (int i = 0; i < pks.length; i++) {
			if (i != 0) {
				sb.append(",");
			}
			sb.append(pks[i]);
		}
		int ret = 0;
		
		ArrayList list = null;
		list = executeQuery(sb.toString(), new ArrayList(0));
		
		ArrayList retList = new ArrayList(list.size());
		if (list.size() == 0) {
			//1件もない場合
			log.debug("query is empty");
			
		} else {
			for(int i = 0; i < list.size(); i++) {
				HashMap properties = (HashMap)list.get(i);
				Object obj = null;
				try {
					obj = target.getClass().newInstance();
					BeanUtils.populate(obj, properties);
				} catch(Exception e) {
					e.printStackTrace();
				}
				retList.add(obj);
			}
		}
		return retList;
	}
	/**
	 *	find
	 */
	public static ArrayList find(Object target, String sql, ArrayList params)
	{
		//interface check
		ILoadable targetIF = null;
		try {
			targetIF = (ILoadable)target;
		} catch(ClassCastException cce) {
			Assert.check(false, "ILoadable not inpremented");
		}
		ArrayList list = null;
		list = executeQuery(sql, params);
		
		ArrayList ret = new ArrayList(list.size());
		if (list.size() == 0) {
			//1件もない場合
			log.debug("query is empty");
			return null;
			
		} else {
			for(int i = 0; i < list.size(); i++) {
				HashMap properties = (HashMap)list.get(i);
				Object obj = null;
				try {
					obj = target.getClass().newInstance();
					BeanUtils.populate(obj, properties);
				} catch(Exception e) {
					e.printStackTrace();
				}
				ret.add(obj);
			}
		}
		return ret;
	}
//===========================================================================
// private methods
//===========================================================================
	/**
	 * class名からテーブル名を取り出す
	 */
	private static String getTableName(Object obj) {
		String s = obj.getClass().getName();
		int i = s.lastIndexOf(".");
		if (i > -1) {
			s = s.substring(i+1);
		}
		return s;
		
	}
	/*
	 * MAPの表示(デバッグ用)
	 */
	private static void displayMap(Map map) {
		StringBuffer sb = new StringBuffer();
		
		Iterator iter = map.keySet().iterator();
		while (iter.hasNext()) {
			String key = (String)iter.next();
			Object val = map.get(key);
			sb.append(key);
			sb.append(":");
			if (val == null) {
				sb.append("null");
			} else {
				sb.append(val);
				sb.append("(" + val.getClass() + ")");
			}
			sb.append("\n");
		}
		log.debug(sb.toString());
	}
	/*
	 * Valueの生成(新規)
	 */
	private static ArrayList makeValues(Map map, String[] itemNames){
		//
		return makeValues(map, itemNames, new ArrayList(itemNames.length));
	}
	/*
	 * Valueの生成(追加)
	 */
	private static ArrayList makeValues(Map map, String[] itemNames, ArrayList values){
		//
		
		for(int i = 0; i < itemNames.length; i++) {
			values.add(map.get(itemNames[i]));
		}
		return values;
	}
	/**
	 *	executeUpdate
	 */
	private static int executeUpdate(String sql, ArrayList params, int executeCount, boolean autoIncrement) {
		//debug
		log.debug("SQL = " + sql);
		
		Connection conn = null;
		PreparedStatement stmt = null;
		int result = 0;
		try {
			conn = PoolingManager.getConnection();
			stmt = conn.prepareStatement(sql);
			setObject(stmt, params);
			//debug
			//log.debug("param[" + i + "]" + params.get(i) + "(" + params.get(i).getClass() + ")");
			result = stmt.executeUpdate();
			
			//結果件数チェック
			if (executeCount == EXECUTE_COUNT_ONE) {	//1件更新チェック要
				//debug
				//log.debug("execute count over count = " + result);
				if (result == 0) {
					//排他制御エラー
					throw new ExclusionException("update count is zero");
				} else if (result > 1) {
					//更新件数オーバー
					throw new ToManyRecordException("update count = " + result);
				}
			}
			
			conn.commit();
			
			if (autoIncrement) {
				result = getLastInsertId(conn);
			}
			
		} catch (SQLException e) {
			//ＤＢエラーに丸めて上位へ返す
			throw new DbError(e);
			
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn!= null)
					conn.close();
			}
			catch (SQLException sqle) {
				// non-fatal; only closing if pooled.
				// don't overthrow original exception
			}
		}
		return result;
	}
	/**
	 *	executeQuery
	 */
	private static ArrayList executeQuery(String sql, ArrayList params) {
		
		//debug
		log.debug("SQL = " + sql);
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet result = null;
		ArrayList list = null;
		try {
			conn = PoolingManager.getConnection();
			stmt = conn.prepareStatement(sql);
			setObject(stmt, params);
			//debug
			//log.debug("param[" + i + "]" + params.get(i) + "(" + params.get(i).getClass() + ")");
			result = stmt.executeQuery();
			
			list = getCollection(result);
			
		} catch (SQLException e) {
			//ＤＢエラーに丸めて上位へ返す
			throw new DbError(e);
			
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn!= null) {
					conn.close();
				}
			}
			catch (SQLException sqle) {
				// non-fatal; only closing if pooled.
				// don't overthrow original exception
			}
		}
		return list;
	}
	/**
	 *	getCollection
	 */
	public static ArrayList getCollection(ResultSet resultSet) throws SQLException {
		
		// Check prerequisites
		Assert.check(resultSet != null, "result set is null");
		
		// Acquire resultSet MetaData
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols = metaData.getColumnCount();
		
		
		// Use ArrayList to maintain ResultSet sequence
		ArrayList list = new ArrayList();
		
		// Scroll to next record and pump into hashmap
		while (resultSet.next()) {
			// Create hashmap, sized to number of columns
			HashMap properties = new HashMap(cols,1);
			
			for (int i=1; i<=cols ; i++) {
				String columnName = metaData.getColumnName(i);
				/*----------------------------------------------------
				//debug
				log.debug("columnName = " + columnName +
					"(" + metaData.getColumnType(i) + ") "+
					resultSet.getObject(i));
				----------------------------------------------------*/
				
				//null対策
				if (resultSet.getObject(i) == null) {
					//nullだった場合、次のカラムもnullになる事象があったので、
					//無理矢理Stringで取ってみる
					//・・・やっぱりダメ
					//Dateがnullでも次は取れるので、SQL文を指定する場合はDate型から
					//指定すべし
					
					resultSet.getString(i);
					continue;
				}
				
				switch (metaData.getColumnType(i)) {
					// Convert known types
					
					case Types.BIGINT:
						properties.put(columnName,
							new Long(resultSet.getLong(i)));
						break;
					
					case Types.DATE:
						properties.put(columnName,
							resultSet.getDate(i));
						break;
					
					case Types.TIME:
						properties.put(columnName,
							resultSet.getTime(i));
						break;
					
					case Types.TIMESTAMP:
						java.util.Date date = new java.util.Date(resultSet.getTimestamp(i).getTime());
						
						//debug
//						log.debug("timestamp = " + resultSet.getTimestamp(i));
//						log.debug("date      = " + date);
						
						properties.put(columnName,date);
						break;
					
					case Types.DECIMAL:
					case Types.DOUBLE:
						properties.put(columnName,
							new Double(resultSet.getDouble(i)));
						break;
					
					case Types.FLOAT:
						properties.put(columnName,
							new Float(resultSet.getFloat(i)));
						break;
					
					case Types.INTEGER:
						properties.put(columnName,
							new Integer(resultSet.getInt(i)));
						break;
					
					case Types.REAL:
						properties.put(columnName,
							new Double(resultSet.getString(i)));
						break;
					
					case Types.SMALLINT:
						properties.put(columnName,
							new Short(resultSet.getShort(i)));
						break;
					case Types.TINYINT:
						
						//TINYINTはCharacter型で返すためgetStringではとれない。
						
						//JDBCドライバをVer upしたための変更
//						Character c = (Character)resultSet.getObject(i);
//						byte j = (byte)c.charValue();
						Byte c = (Byte)resultSet.getObject(i);
						byte j = (byte)c.byteValue();
						
						properties.put(columnName,
							new Byte(j));
						break;
					case Types.CHAR:
					case Types.CLOB:
					case Types.VARCHAR:
					case Types.LONGVARCHAR:
						// :FIXME: Handle binaries differently?
					case Types.BLOB:
					case Types.VARBINARY:
						properties.put(columnName,
							resultSet.getString(i));
						
						break;
					case Types.LONGVARBINARY:
						//debug
						String s = new String((byte[])resultSet.getObject(i));
						/*------------------------------------
						//debug
						log.debug("LONGVARBINARY = " + resultSet.getObject(i).getClass().getName() + s);
						------------------------------------*/
						properties.put(columnName,s);
						break;

					/*
					:FIXME: Add handlers for
					ARRAY
					BINARY
					BIT
					DECIMAL
					DISTINCT
					JAVA_OBJECT
					NULL
					NUMERIC
					OTHER
					REF
					STRUCT
					*/
					 // Otherwise, pass as *String property to be converted
					default:
						properties.put(columnName + "String",
							resultSet.getString(i));
						break;
				}
			}
			
			list.add(properties);
			
			
			//properties.clear();
			
		} // end while
		
		return list;
	}
	/*
	 * auto increment 値の取得
	 */
	private static int getLastInsertId(Connection conn) {
		
		//debug
		String sql = "SELECT last_insert_id()";
		
		log.debug("SQL = " + sql);
		
		PreparedStatement stmt = null;
		ResultSet result = null;
		int lastInsertId = 0;
		ArrayList list = null;
		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			result.next();
			lastInsertId = result.getInt(1);
			
/*-------------------------------------------------
			//debug
			log.debug("lastInsertId = " + lastInsertId);
-------------------------------------------------*/
			
			
		} catch (SQLException se) {
			//ＤＢエラーに丸めて上位へ返す
			throw new DbError(se);
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			}
			catch (SQLException sqle) {
				// non-fatal; only closing if pooled.
				// don't overthrow original exception
			}
		}
		return lastInsertId;
	}
	private static void setObject(PreparedStatement stmt, ArrayList params) throws SQLException{
		
		//
		for(int i = 0; i < params.size(); i++) {
			
			
			Object o = params.get(i);
			if (o == null) {
/*-------------------------------------------------
				//debug
				log.debug("param["+ i +"]:null");
-------------------------------------------------*/
				
				stmt.setNull(i+1, Types.VARCHAR);
				continue;
			}
			String type = o.getClass().getName();
/*-------------------------------------------------
			//debug
			String a = type.substring(type.lastIndexOf(".") + 1);
			log.debug("param["+ i +"]:"+ a + ":" + params.get(i));
-------------------------------------------------*/
			
			if (type.equals("java.lang.Integer")) {
				stmt.setInt(i+1, ((Integer)params.get(i)).intValue());
			} else if (type.equals("java.lang.Long")) {
				stmt.setLong(i+1, ((Long)params.get(i)).longValue());
			} else if (type.equals("java.lang.Short")) {
				stmt.setShort(i+1, ((Short)params.get(i)).shortValue());
			} else if (type.equals("java.lang.String")) {
				stmt.setString(i+1, (String)params.get(i));
			} else if (type.equals("java.lang.Byte")) {
				stmt.setByte(i+1, ((Byte)params.get(i)).byteValue());
			} else if (type.equals("java.util.Date")) {
				String s = DB_DATE.format((java.util.Date)params.get(i));
				stmt.setString(i+1, s);
			} else if (type.equals("java.sql.Date")) {
				String s = DB_DATE2.format((java.util.Date)params.get(i));
				stmt.setString(i+1, s);
			} else {
				Assert.check(false, params.get(i) + "no support type:" + type);
			}
		}
	}
	/**
	 *	テストメイン
	 */
	public static void main(String[] args)
	{
		System.out.println("------- << DAO Test Start >> -----------");
		
		unitTest1();	//新規ユーザ作成テスト
		
	}
	/**
	 *	unitTest1
	 *
	 * insert test
	 *
	 */
	public static void unitTest1()
	{
		
	}
}