package gu.sql2java;

import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;

import static com.google.common.base.Preconditions.*;
import static gu.sql2java.SimpleLog.*;
import static com.google.common.base.MoreObjects.*;

import java.math.BigDecimal;
import java.net.URL;
import java.nio.ByteBuffer;
import java.sql.PreparedStatement;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collections;
import java.util.EnumMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.concurrent.Callable;
import javax.sql.DataSource;

import com.google.common.base.Function;
import com.google.common.base.Strings;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

import gu.sql2java.exception.DaoException;
import gu.sql2java.exception.DataAccessException;
import gu.sql2java.exception.RuntimeDaoException;


/**
 * The Manager provides connections and manages transactions transparently.
 * <br>
 * It is a singleton, you get its instance with the getInstance() method.
 * All of the XxxxManager classes use the Manager to get database connections.
 * Before doing any operation, you must pass either a
 * datasource or a jdbc driver/url/username/password.
 * @author guyadong
 */
public final class Manager implements Constant,SqlRunner
{
    private static class Singleton{
        private static final Manager INSTANCE = new Manager();
    }
    private static InheritableThreadLocal<Connection> transactionConnection = new InheritableThreadLocal<Connection>();
    /** JDBC properties from properties file */
    private static Properties databaseProperties;
    
    static{
        String envVar="config_folder";
        String propFile="database.properties";
        String confFolder="conf";
        databaseProperties = ConfigUtils.loadAllProperties(propFile, confFolder, envVar, Manager.class, false);
    }
    /**
     * inject properties to {@link #databaseProperties}<br>
     * be effected only while called before initializing singleton instance 
     * @param properties
     * @see JdbcProperty
     */
    public static final void injectProperties(Map<String,String> properties){
        if(null != properties){
            EnumMap<JdbcProperty, String> enumMap = new EnumMap<JdbcProperty,String>(JdbcProperty.class);
            JdbcProperty property;
            for(Entry<String, String> entry:properties.entrySet()){
                if(null != (property = JdbcProperty.fromKey(entry.getKey()))){
                    enumMap.put(property, entry.getValue());
                }
            }
            injectProperties(enumMap);
        }
    }
    /**
     * inject properties to {@link #databaseProperties}<br>
     * be effected only while called before initializing singleton instance 
     * @param properties
     */
    public static final void injectProperties(EnumMap<JdbcProperty,String> properties){
        if(null != properties){
            Boolean isDebug = false;
            if(properties.containsKey(JdbcProperty.DEBUG)){
                isDebug = "true".equalsIgnoreCase(properties.get(JdbcProperty.DEBUG));
                databaseProperties.setProperty(JdbcProperty.DEBUG.key, isDebug.toString());
            }
            String prefix=isDebug?"debug.":"work.";
            String value;
            JdbcProperty key;
            for(Entry<JdbcProperty, String> entry : properties.entrySet()){
                value = entry.getValue();
                key = entry.getKey();
                if( key != JdbcProperty.DEBUG && null !=value && !value.isEmpty()){
                    databaseProperties.setProperty(key.withPrefix(prefix), value);
                }
            }
        }
    }
    private volatile DataSource dataSource = null;
    private String jdbcDriver = null;
    private String jdbcUrl = null;
    private String jdbcUsername = null;
    private String jdbcPassword = null;
    private volatile String generatedkeyRetrieve = null;
    private volatile String generatedkeyStatement = null;
    private volatile AutoKeyRetrieveType  retrieveType;
    private String maxIdleTime;
    private String idleConnectionTestPeriod;
    private String maxPoolSize;
    private String minPoolSize;
    private boolean isDebug = false;
    
    /**
     * Returns the manager singleton instance.
     */
    private Manager()
    {
        loadProperties(databaseProperties);
    }

    /**
     * Returns the manager singleton instance.
     */
    public static Manager getInstance()
    {
        return Singleton.INSTANCE;
    }
    
    /** dispose pool */
    public void disposePool(){
        try{
            DataSources.destroy(dataSource);
        }catch (Exception e) {
            log("dispose pool wrong ..." + e);
        }
    }
    
    /**
     * configure with the parameters given in the properties object
     * @param properties the properties object to be used
     */
    public void loadProperties(Properties properties){
        isDebug = Boolean.valueOf(properties.getProperty("isDebug"));
        String prefix=isDebug ? "debug." : "work.";
        jdbcDriver = properties.getProperty(JdbcProperty.JDBC_DRIVER.withPrefix(prefix));
        jdbcUrl = properties.getProperty(JdbcProperty.JDBC_URL.withPrefix(prefix));
        jdbcUsername = properties.getProperty(JdbcProperty.JDBC_USERNAME.withPrefix(prefix));
        jdbcPassword = properties.getProperty(JdbcProperty.JDBC_PASSWORD.withPrefix(prefix));
        
        generatedkeyRetrieve = properties.getProperty(JdbcProperty.GENERATEDKEY_RETRIEVE.name());
        generatedkeyStatement = properties.getProperty(JdbcProperty.GENERATEDKEY_STATEMENT.name());
        
        maxPoolSize = properties.getProperty(JdbcProperty.C3P0_MAXPOOLSIZE.withPrefix(prefix));
        minPoolSize = properties.getProperty(JdbcProperty.C3P0_MINPOOLSIZE.withPrefix(prefix));
        maxIdleTime = properties.getProperty(JdbcProperty.C3P0_MAXIDLETIME.withPrefix(prefix));
        idleConnectionTestPeriod = properties.getProperty(JdbcProperty.C3P0_IDLECONNECTIONTESTPERIOD.withPrefix(prefix));
    }
    private void logDatabaseParameters(){
        if(isDebug){
            log("database using debug environment parameter: ");
            log("jdbcUrl = " + jdbcUrl);
            log("jdbcUsername = " + jdbcUsername);
            log("jdbcPassword = " + jdbcPassword);
            log("maxPoolSize = " + maxPoolSize);
            log("minPoolSize = " + minPoolSize);
            log("maxIdleTime = " + maxIdleTime);
            log("idleConnectionTestPeriod = " + idleConnectionTestPeriod);
        }
    }    
    /**
     * use key synchronized to be sure the ds created once 
     * @return 
     */
    private DataSource getDataSource(){
        // double check
        if (dataSource == null){
                synchronized (this) {
                        if (dataSource == null){
                                try{
                                        logDatabaseParameters();
                                        //set C3P0 properties
                                        ComboPooledDataSource cpds = new ComboPooledDataSource();                
                                        cpds.setDriverClass(jdbcDriver);
                                        cpds.setUser(jdbcUsername);
                                        cpds.setPassword(jdbcPassword);
                                        cpds.setJdbcUrl(jdbcUrl);
                                        cpds.setMaxPoolSize(Integer.parseInt(maxPoolSize));
                                        cpds.setMinPoolSize(Integer.parseInt(minPoolSize));
                                        cpds.setMaxIdleTime(Integer.parseInt(maxIdleTime));
                                        cpds.setIdleConnectionTestPeriod(Integer.parseInt(idleConnectionTestPeriod));
                                        dataSource = cpds; 
                                }catch (Exception e){
                                        throw new IllegalArgumentException(String.format("can't get connection by argument...driver/url/username/password[%s/%s/%s/%s]",jdbcDriver,jdbcUrl,jdbcUsername,jdbcPassword),e);
                                }
                        }
                }
        }
        return dataSource;
    }
    
    /**
     * Gets an auto commit connection.
     * <br>
     * Normally you do not need this method that much ;-)
     *
     * @return an auto commit connection
     */
    public Connection getConnection() throws SQLException
    {
        return firstNonNull(transactionConnection.get(), getDataSource().getConnection());
    }

    /**
     * Releases the database connection.
     * <br>
     * Normally you should not need this method ;-)
     */
    public void releaseConnection(Connection c)
    {
        Connection tc = transactionConnection.get();
        if (tc != null){
            return;
        }
        try{
            if (c != null){
                c.close();
            }
        }catch (SQLException x){
            log("Could not release the connection: "+x.toString());
        }
    }

    /**
     * Initiates a database transaction.
     * <br>
     * When working within a transaction, you should invoke this method first.
     * The connection is returned just in case you need to set the isolation level.
     *
     * @return a non-auto commit connection with the default transaction isolation level
     */
    public Connection beginTransaction() throws SQLException
    {
        Connection c = this.getConnection();
        c.setAutoCommit(false);
        transactionConnection.set(c);
        return c;
    }

    /**
     * Releases connection used for the transaction and performs a commit or rollback.
     *
     * @param commit tells whether this connection should be committed
     *        true for commit(), false for rollback()
     */
    public void endTransaction(boolean commit) throws SQLException
    {
        Connection c = transactionConnection.get();
        if (c == null)
        {
            return;
        }

        try
        {
            if (commit)
            {
                c.commit();
            }
            else
            {
                c.rollback();
            }
        }
        finally
        {
            c.setAutoCommit(true);
            transactionConnection.set(null);
            releaseConnection(c);
        }
    }
    /**
     * Run {@code Callable<T>} as a transaction.<br>
     * all exceptions but {@code SQLException} threw by {@code Callable<T>} is wrapped into {@code RuntimeException}<br>
     * throw {@code NullPointerException} if {@code fun} be {@code null}<br>
     * @param <T>  type of return result
     * @param fun
     * @param transactionListener listener for transaction status
     * @return
     * @throws RuntimeDaoException
     * @see #beginTransaction()
     * @see #endTransaction(boolean)
     */
    <T>T runAsTransaction(Callable<T> fun,TransactionListener transactionListener) throws RuntimeDaoException{
        checkArgument(fun != null, "fun is null");
        try {
            beginTransaction();
            if(null != transactionListener){
                transactionListener.beginTransaction();
            }
            boolean commit = false;
            try {
                T result = fun.call();
                commit = true;
                return result;
            } catch (SQLException e) {
                throw e;
            } catch (RuntimeException e) {
                throw e;
            }catch (Exception e) {
                throw new RuntimeException(e);
            }finally {
                endTransaction(commit);
                if(null != transactionListener){
                    transactionListener.endTransaction(commit);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeDaoException(e);
        }
    }
    
    /**
         * Run {@code Runnable} as a transaction.no return
         * @param fun
         * @param transactionListener listener for transaction status
         * @throws RuntimeDaoException
         * @see #runAsTransaction(Callable,TableListener.TransactionListener)
         */
        void runAsTransaction(final Runnable fun,TransactionListener transactionListener) throws RuntimeDaoException{
                checkArgument(fun != null, "fun is null");
            runAsTransaction(new Callable<Object>(){
        
                @Override
                public Object call() throws Exception {
                    fun.run();
                    return null;
                }},transactionListener);
        }
        /**
     * @param fun
     * @return
     * @throws RuntimeDaoException
     */
    @Override
    public <T>T runAsTransaction(Callable<T> fun) throws RuntimeDaoException{
        return runAsTransaction(fun, ListenerContainer.TRANSACTION_LISTENER);
    }
    /**
     * Run {@code Runnable} as a transaction.no return
     * @param fun
     * @throws DaoException
     */
    @Override
    public void runAsTransaction(final Runnable fun) throws RuntimeDaoException{
        runAsTransaction(fun,ListenerContainer.TRANSACTION_LISTENER);
    }

////////////////////////////////////////////////////
// cleaning method
////////////////////////////////////////////////////

    /**
     * Closes the passed Statement.
     */
    void close(Statement s)
    {
        try
        {
            if (s != null) {
                s.close();
            }
        }
        catch (SQLException x)
        {
            log("Could not close statement!: " + x.toString());
        }
    }

    /**
     * Closes the passed ResultSet.
     */
    void close(ResultSet rs)
    {
        try
        {
            if (rs != null) {
                rs.close();
            }
        }
        catch (SQLException x)
        {
            log("Could not close result set!: " + x.toString());
        }
    }

    /**
     * Closes the passed Statement and ResultSet.
     */
    void close(Statement s, ResultSet rs)
    {
        close(rs);
        close(s);
    }

        ////////////////////////////////////////////////////
        // Helper methods for fetching numbers using IDs or names
        ////////////////////////////////////////////////////

    /**
     * return all bytes in buffer (position~limit),no change status of buffer
     * @param buffer
     * @return
     */
    private static final byte[] getBytesInBuffer(ByteBuffer buffer){
        if(null == buffer){
                return null;
        }
        int pos = buffer.position();
        try{
            byte[] bytes = new byte[buffer.remaining()];
            buffer.get(bytes);
            return bytes;
        }finally{
            buffer.position(pos);
        }
    }
    /**
     * Set a byte array to the passed prepared statement as a ByteBuffer or as null.
     */
    private static void  setBytes(int sqlType,PreparedStatement ps, int pos, ByteBuffer bytes) throws SQLException
    {
        setBytes(sqlType,ps,pos,getBytesInBuffer(bytes));
    }
    /**
     * Set a byte array to the passed prepared statement as a ByteBuffer or as null.
     */
    private static void  setBytes(int sqlType,PreparedStatement ps, int pos, byte[] bytes) throws SQLException
    {
        if (null == bytes){
            ps.setNull(pos, sqlType);
        }else{
            ps.setBytes(pos, bytes);
        }
    }
    /**
     * Retrieves a date value from the passed result set as a Calendar object.
     */
    static Calendar getCalendar(ResultSet rs, int pos) throws SQLException
    {
        Timestamp date = rs.getTimestamp(pos);
        if (rs.wasNull()) {
            return null;
        }
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar;
    }

    /**
     * Retrieves a date value from the passed result set as a Calendar object.
     */
    static Calendar getCalendar(ResultSet rs, String column) throws SQLException
    {
        Timestamp date = rs.getTimestamp(column);
        if (null == date) {
            return null;
        }
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar;
    }

    /**
     * Set a Calendar object to the passed prepared statement as a date or as null.
     */
    static void  setCalendar(PreparedStatement ps, int pos, Calendar calendar) throws SQLException
    {
        if (calendar == null)
        {
            ps.setNull(pos, Types.TIMESTAMP);
        }
        else
        {
            ps.setTimestamp(pos, new java.sql.Timestamp(calendar.getTimeInMillis()));
        }
    }

    /**
     * 
     * For compatibility with old version {@link ResultSet} without getObject(int,Class) method
     * @param rs
     * @param columnIndex
     * @param type
     * @return
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
    public static <T> T getObject(ResultSet rs,int columnIndex, Class<T> type) throws SQLException {
        if (type == null) {
            throw new SQLException("Type parameter can not be null");
        }
        Object t = null;
        if (type.equals(String.class)) {
            t = rs.getString(columnIndex);
        } else if (type.equals(BigDecimal.class)) {
            t =  rs.getBigDecimal(columnIndex);
        } else if (type.equals(Boolean.class) || type.equals(Boolean.TYPE)) {
            t =  Boolean.valueOf(rs.getBoolean(columnIndex));
        } else if (type.equals(Integer.class) || type.equals(Integer.TYPE)) {
            t =  Integer.valueOf(rs.getInt(columnIndex));
        } else if (type.equals(Long.class) || type.equals(Long.TYPE)) {
            t =  Long.valueOf(rs.getLong(columnIndex));
        } else if (type.equals(Float.class) || type.equals(Float.TYPE)) {
            t =  Float.valueOf(rs.getFloat(columnIndex));
        } else if (type.equals(Double.class) || type.equals(Double.TYPE)) {
            t =  Double.valueOf(rs.getDouble(columnIndex));
        } else if (type.equals(byte[].class)) {
            t =  rs.getBytes(columnIndex);
        } else if (type.equals(ByteBuffer.class)) {
                byte[] array = rs.getBytes(columnIndex);
            t =  (array == null ? null : ByteBuffer.wrap(array));
        } else if (type.equals(java.sql.Date.class)) {
            t =  rs.getDate(columnIndex);
        } else if (type.equals(Time.class)) {
            t =  rs.getTime(columnIndex);
        } else if (type.equals(Timestamp.class)) {
            t =  rs.getTimestamp(columnIndex);
        } else if (type.equals(Calendar.class)) {
                java.util.Date date = (java.util.Date) rs.getObject(columnIndex);
            if (date == null) {
                return null;
            }
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            t =  calendar;
        } else if (type.equals(Clob.class)) {
            t =  rs.getClob(columnIndex);
        } else if (type.equals(Blob.class)) {
            t =  rs.getBlob(columnIndex);
        } else if (type.equals(Array.class)) {
            t =  rs.getArray(columnIndex);
        } else if (type.equals(Ref.class)) {
            t =  rs.getRef(columnIndex);
        } else if (type.equals(URL.class)) {
            t =  rs.getURL(columnIndex);
        } else {
            try {
                t =  type.cast(rs.getObject(columnIndex));
            } catch (ClassCastException e) {
                throw new SQLException("Conversion not supported for type " + type.getName(), e);
            }
        }
        return rs.wasNull() ? null : (T)t;
    }
    
////////////////////////////////////////////////////
// Date helper methods
////////////////////////////////////////////////////

    /**
     * pattern for received date processing.
     */
    private static final String[] PATTERNS = new String[]
    {
        "EEE, dd MMM yyyy HH:mm:ss '-'S '('z')'",
        "EEE, dd MMM yyyy HH:mm:ss '+'S '('z')'",
        "EEE, dd MMM yyyy HH:mm:ss '-'S",
        "EEE, dd MMM yyyy HH:mm:ss '+'S",
        "EEE, dd MMM yyyy HH:mm:ss z",
        "EEE, dd MMM yyyy HH:mm:ss Z",
        "EEE, dd MMM yyyy HH:mm:ss",
        "EEE, d MMM yyyy HH:mm:ss '-'S '('z')'",
        "EEE, d MMM yyyy HH:mm:ss '+'S '('z')'",
        "EEE, d MMM yyyy HH:mm:ss '-'S",
        "EEE, d MMM yyyy HH:mm:ss '+'S",
        "EEE, d MMM yyyy HH:mm:ss z",
        "EEE, d MMM yyyy HH:mm:ss Z",
        "EEE, d MMM yyyy HH:mm:ss",

        "EEE, dd MMM yy HH:mm:ss '-'S '('z')'",
        "EEE, dd MMM yy HH:mm:ss '+'S '('z')'",
        "EEE, dd MMM yy HH:mm:ss '-'S",
        "EEE, dd MMM yy HH:mm:ss '+'S",
        "EEE, dd MMM yy HH:mm:ss z",
        "EEE, dd MMM yy HH:mm:ss Z",
        "EEE, dd MMM yy HH:mm:ss",
        "EEE, d MMM yy HH:mm:ss '-'S '('z')'",
        "EEE, d MMM yy HH:mm:ss '+'S '('z')'",
        "EEE, d MMM yy HH:mm:ss '-'S",
        "EEE, d MMM yy HH:mm:ss '+'S",
        "EEE, d MMM yy HH:mm:ss z",
        "EEE, d MMM yy HH:mm:ss Z",
        "EEE, d MMM yy HH:mm:ss",

        "dd MMM yyyy HH:mm:ss '-'S",
        "dd MMM yyyy HH:mm:ss '+'S",
        "dd MMM yyyy HH:mm:ss '-'S '('z')'",
        "dd MMM yyyy HH:mm:ss '+'S '('z')'",
        "dd MMM yyyy HH:mm:ss z",
        "dd MMM yyyy HH:mm:ss Z",
        "dd MMM yyyy HH:mm:ss",

        "dd MMM yyy HH:mm:ss '-'S",
        "dd MMM yyy HH:mm:ss '+'S",
        "dd MMM yyy HH:mm:ss '-'S '('z')'",
        "dd MMM yyy HH:mm:ss '+'S '('z')'",
        "dd MMM yyy HH:mm:ss z",
        "dd MMM yyy HH:mm:ss Z",
        "dd MMM yyy HH:mm:ss",

        "yyyy.MM.dd HH:mm:ss z",
        "yyyy.MM.dd HH:mm:ss Z",
        "yyyy.MM.d HH:mm:ss z",
        "yyyy.MM.d HH:mm:ss Z",
        "yyyy.MM.dd HH:mm:ss",
        "yyyy.MM.d HH:mm:ss",

        "yy.MM.dd HH:mm:ss z",
        "yy.MM.dd HH:mm:ss Z",
        "yy.MM.d HH:mm:ss z",
        "yy.MM.d HH:mm:ss Z",
        "yy.MM.dd HH:mm:ss",
        "yy.MM.d HH:mm:ss",

        "yyyy MM dd HH:mm:ss",
        "yyyy MM d HH:mm:ss",
        "yyyy MM dd HH:mm:ss z",
        "yyyy MM dd HH:mm:ss Z",
        "yyyy MM d HH:mm:ss z",
        "yyyy MM d HH:mm:ss Z",

        "yy MM dd HH:mm:ss",
        "yy MM d HH:mm:ss",
        "yy MM dd HH:mm:ss z",
        "yy MM dd HH:mm:ss Z",
        "yy MM d HH:mm:ss z",
        "yy MM d HH:mm:ss Z",

        "yyyy-MM-dd HH:mm:ss z",
        "yyyy-MM-dd HH:mm:ss Z",
        "yyyy-MM-d HH:mm:ss z",
        "yyyy-MM-d HH:mm:ss Z",
        "yyyy-MM-dd HH:mm:ss",
        "yyyy-MM-d HH:mm:ss",

        "yy-MM-dd HH:mm:ss z",
        "yy-MM-dd HH:mm:ss Z",
        "yy-MM-d HH:mm:ss z",
        "yy-MM-d HH:mm:ss Z",
        "yy-MM-dd HH:mm:ss",
        "yy-MM-d HH:mm:ss",

        "dd MMM yyyy",
        "d MMM yyyy",

        "dd.MMM.yyyy",
        "d.MMM.yyyy",

        "dd-MMM-yyyy",
        "d-MMM-yyyy",

        "dd MM yyyy",
        "d MM yyyy",

        "dd.MM.yyyy",
        "d.MM.yyyy",

        "dd-MM-yyyy",
        "d-MM-yyyy",

        "yyyy MM dd",
        "yyyy MM d",

        "yyyy.MM.dd",
        "yyyy.MM.d",

        "yyyy-MM-dd",
        "yyyy-MM-d",

        "dd MMM yy",
        "d MMM yy",

        "dd.MMM.yy",
        "d.MMM.yy",

        "dd-MMM-yy",
        "d-MMM-yy",

        "dd MM yy",
        "d MM yy",

        "dd.MM.yy",
        "d.MM.yy",

        "dd-MM-yy",
        "d-MM-yy",

        "yy MMM dd",
        "yy MMM d",

        "yy.MMM.dd",
        "yy.MMM.d",

        "yy-MMM-dd",
        "yy-MMM-d",

        "yy MMM dd",
        "yy MMM d",

        "yy.MMM.dd",
        "yy.MMM.d",

        "yy-MMM-dd",
        "yy-MMM-d",
        // ex: Wed 19, Feb 2003
        "EEE dd, MMM yyyy", 
        // ex: Wed 19, Feb 03
        "EEE dd, MMM yy" 
    };


    /**
     * get a date from a date string representation in one of the registered formats
     * @param strDate the date as string. 
     * @return Date object ,otherwise null If (null or empty) or correct pattern was not found
     */
    static java.util.Date getDateFromString(String strDate)
    {
        java.util.Date dReceivedDate = null;
        if (strDate == null) {
            return dReceivedDate;
        } else {
            strDate = strDate.trim();
        }

        SimpleDateFormat pSimpleDateFormat = new SimpleDateFormat("");
        if (!strDate.isEmpty())
        {
            for (int i=0; i<PATTERNS.length; i++)
            {
                try
                {
                    pSimpleDateFormat.applyPattern(PATTERNS[i]);
                    dReceivedDate = pSimpleDateFormat.parse(strDate);
                    if (dReceivedDate == null)
                    {
                        continue;
                    }
                    return dReceivedDate;
                }
                catch (ParseException pe)
                {
                    ; // ignore this format try the next one
                }
            }
        }
        return dReceivedDate;
    }

    /**
     * Verify that the string represantes the date with one of the registered formats
     * @param strDate the date as string.
     * @return boolean "true" if the string represantes the date in one of the registed formats.
     */
    static boolean isDate(String strDate)
    {
        return null != getDateFromString(strDate);
    }
    
    static String buildProcedureCall(String packageName, String procedureName, int paramCount) {
        return buildProcedureCall(packageName + "." + procedureName, paramCount);
    }
    
    static String buildProcedureCall(String procedureName, int paramCount) {
        StringBuilder sb = new StringBuilder("{call ").append(procedureName).append("(");
        for (int n = 1; n <= paramCount; n++) {
            sb.append("?,");
        }
        if (paramCount > 0) {
            sb.setLength(sb.length()-1);
        }
        return sb.append(")}").toString();
    }
    List<BaseBean> runPreparedStatementAsList(Map<String, Class<?>> targetTypes,
                PreparedStatement ps){
        checkArgument(null !=ps,"ps is null");
        ResultSet rs = null;
        try {
            ps.setFetchSize(100);
            rs = ps.executeQuery();
            UnnameRowMetaData metaData = new UnnameRowMetaData(rs.getMetaData(), targetTypes);
            List<BaseBean> list = Lists.newLinkedList();
            while(rs.next()){
                BaseBean row = new UnnameRow(metaData);
                for(int i=0; i<metaData.defaultColumnIdList.length; ++i){
                        row.setValue(i, rs.getObject(i+1));
                }
                list.add(row);
            }
            return list;
        } catch (DaoException e) {
            throw new RuntimeDaoException(e);
        }catch (SQLException e) {
            throw new RuntimeDaoException(new DataAccessException(e));
        } finally {
            this.close(rs);
        }
    }
    List<BaseBean> runSqlAsList(Map<String, Class<?>> targetTypes, String sql, Object... argList){
        checkArgument(!Strings.isNullOrEmpty(sql),"sql is null or empty");
        PreparedStatement ps = null;
        Connection connection = null;
        try {
            connection = this.getConnection();
            if(isDebug){
                log("runSqlAsList:" + sql);
            }
            ps = connection.prepareStatement(sql,
                    ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY);
            fillPrepareStatement(ps, argList);
            return runPreparedStatementAsList(targetTypes,ps);
        } catch (DaoException e) {
            throw new RuntimeDaoException(e);
        }catch (SQLException e) {
            throw new RuntimeDaoException(new DataAccessException(e));
        } finally {
            this.close(ps);
            this.releaseConnection(connection);
        }
    }
    
    private static Function<BaseBean,Map<String,Object>> VMAP_FUN = new Function<BaseBean,Map<String,Object>>(){

                @Override
                public Map<String, Object> apply(BaseBean input) {
                        return input.asNameValueMap();
                }};

        @Override
    public List<Map<String, Object>> runSqlForMap(Map<String,Class<?>> targetType, String sql,Object... argList) throws RuntimeDaoException{
        List<BaseBean> list = runSqlAsList(targetType, sql, argList);
        if(list.isEmpty()){
                return Collections.emptyList();
        }
                return Lists.transform(list, VMAP_FUN);
    }
    
    private <T> List<T> fetchOnlyOneColumn(List<BaseBean> list,final Class<T> targetType){
        if(list.isEmpty()){
                return Collections.emptyList();
        }
        BaseBean row = list.get(0);
        Object[] values = row.asValueArray();
        
        checkArgument(values.length == 1,"more than  1 column returned");
        return Lists.transform(list, new Function<BaseBean, T>() {
                        @Override
                        public T apply(BaseBean input) {
                                if(null == targetType){
                                        return input.getValue(0);
                                }
                                return targetType.cast(input.getValue(0));
                        }
                });
    }
    <T> List<T> runPreparedStatementAsList(Class<T> targetType, PreparedStatement ps) throws RuntimeDaoException{
        List<BaseBean> list = runPreparedStatementAsList((Map<String,Class<?>>)null, ps);
        return fetchOnlyOneColumn(list,targetType);
    }
    
    @Override
        public List<BaseBean> runSqlAsList(String sql, Object... argList) throws RuntimeDaoException{
                return runSqlAsList(Collections.<String,Class<?>>emptyMap(), sql, argList);
        }
        @Override
    public <T> List<T> runSqlAsList(Class<T> targetType, String sql, Object... argList) throws RuntimeDaoException{
        List<BaseBean> list = runSqlAsList((Map<String,Class<?>>)null, sql, argList);
        return fetchOnlyOneColumn(list,targetType);
    }
    
    @Override
    public <T> T runSqlForValue(Class<T> targetType,String sql, Object... argList) throws RuntimeDaoException{
        List<T> list = runSqlAsList(targetType,sql,argList);
        checkArgument(list.size() == 1,"more than  1 row returned for SQL: %s",sql);
        return list.get(0);
    }
    <T> T runPreparedStatementForValue(Class<T> targetType,PreparedStatement ps) throws RuntimeDaoException{
        List<T> list = runPreparedStatementAsList(targetType,ps);
        checkArgument(list.size() == 1,"more than  1 row returned");
        return list.get(0);
    }
        /**
     * 填充PreparedStatement中的参数对象
     * 
     * @param ps
     * @param argList
     * @throws SQLException
     */
    protected static void fillPrepareStatement(PreparedStatement ps, Object[] argList) throws DaoException{
        try {
            if (!(argList == null || ps == null)) {
                for (int i = 0; i < argList.length; i++) {
                        if (argList[i] instanceof ByteBuffer) { 
                        ps.setBytes(i + 1, getBytesInBuffer((ByteBuffer) argList[i]));
                    } else {
                        ps.setObject(i + 1, argList[i]);
                    }
                }
            }
        } catch (SQLException e) {
            throw new DaoException(e);
        }
    }
    protected static void setPreparedStatement(PreparedStatement ps,int pos,Object value,int sqlType)
                        throws SQLException {           
                if(value instanceof ByteBuffer){
                        setBytes(sqlType, ps, pos, (ByteBuffer)value);
                }else{
                        ps.setObject(pos,value,sqlType);
                }
        }
        
        
        /**
         * For those who do not want to read below, please simply pick up the
         * configuration associated with your database.
         * 
         * More explanation:
         * When you save a bean whose primary key is numeric and has no value set,
         * we assume that you want sql2java to retrieve a key's value generated
         * on the database side.
         * 
         * generatedkey.retrieve can take 3 values:
         * 
         * auto - the standard approach when you have a JDBC 3.0 driver.
         * 
         * before - the key's value is retrieved before inserting the record.
         * 
         * after - the key's value is retrieved after inserting the record 
         * @author guyadong
         *
         */
        enum AutoKeyRetrieveType{
                auto,after,before
        }
        /**
         * 
         * For those who do not want to read below, please simply pick up the
         * configuration associated with your database.
         * 
         * More explanation:
         * When you save a bean whose primary key is numeric and has no value set,
         * we assume that you want sql2java to retrieve a key's value generated
         * on the database side.
         * 
         * If you set it to before or after you also need to configure the
         * autogeneratedkey.statement properties.
         * <TABLE> is replaced at code generation time by the table name.
         * <KEY> is replaced at code generation time by the column name.
         * You may adjust this properties to fit your own naming convention.
         * 
         * PICK THE CONFIGURATION ASSOCIATED WITH YOUR DATABASE
         * (or create one, but in that case let us know so we can add it here... :-)
         */
        private static final ImmutableMap<String, String> AUTOINC_MECHANISM = ImmutableMap.<String, String>builder()
                /*-- HSQL ------*/
                .put("hsql.generatedkey.retrieve","after")
                .put("hsql.generatedkey.statement","CALL IDENTITY()")
                /*-- ORACLE 8i ------*/
                .put("oracle.generatedkey.retrieve","before")
                .put("oracle.generatedkey.statement","SELECT SEQ_<TABLE>.nextval FROM DUAL")
                /*-- MaxDB (SapDB) ------*/
                .put("maxdb.generatedkey.retrieve","before")
                .put("maxdb.generatedkey.statement","SELECT SEQ_<TABLE>_<KEY>.nextval FROM DUAL")
                /*-- MYSQL (without jdbc 3.0 driver) ------*/
                .put("mysql.generatedkey.retrieve","after")
                .put("mysql.generatedkey.statement","SELECT last_insert_id()")
                /*-- SQL SERVER ------*/
                .put("sqlserver.generatedkey.retrieve","after")
                .put("sqlserver.generatedkey.statement","SELECT @@IDENTITY")
                /*-- SYBASE ------*/
                .put("sybase.generatedkey.retrieve","after")
                .put("sybase.generatedkey.statement","SELECT @@IDENTITY")
                /*-- POSTGRESQL ------*/
                .put("postgresql.generatedkey.retrieve","before")
                .put("postgresql.generatedkey.statement","SELECT nextval('<TABLE>_<KEY>_SEQ')")
                /*-- INFORMIX ------*/
                .put("informix.generatedkey.retrieve","after")
                .put("informix.generatedkey.statement","SELECT dbinfo('sqlca.sqlerrd1') FROM systables WHERE tabid=1")
                /*-- FIREBIRD ------
                 - need to create "sequence" number table first "create generator SEQ_<TABLE>"
                 - the 1 is how much to increment the sequence*/
                .put("firebird.generatedkey.retrieve","before")
                .put("firebird.generatedkey.statement","SELECT GEN_ID(SEQ_<TABLE>, 1) FROM RDB$DATABASE")
                /*-- DERBY ------*/
                .put("derby.generatedkey.retrieve","after")
                .put("derby.generatedkey.statement","VALUES IDENTITY_VAL_LOCAL()")
                .build();
        
        private static final String getMechanismPropertyWithSuffix(String engine,String suffix) {
                return checkNotNull(AUTOINC_MECHANISM.get(engine + "." + suffix), "NOT FOUND %s property for %s database",suffix,engine);
        }
        
        private synchronized void autokeyMechanismInit(Connection connection) throws SQLException{              
                DatabaseMetaData meta = checkNotNull(connection,"connection is null").getMetaData();
                if(meta.supportsGetGeneratedKeys()){
                        generatedkeyRetrieve =  "auto";
                        generatedkeyStatement = null;
                }else{
                        String engine = new StringTokenizer(meta.getDatabaseProductName()).nextToken().toLowerCase();
                        generatedkeyRetrieve = getMechanismPropertyWithSuffix(engine,RETRIEVE_SUFFIX).toLowerCase();
                        generatedkeyStatement = getMechanismPropertyWithSuffix(engine,RETRIEVE_SUFFIX);
                }
        }
        
        String getGeneratedkeyRetrieve(Connection c){
                // double check
                if(generatedkeyRetrieve == null){
                        synchronized (this) {
                                if(generatedkeyRetrieve == null){
                                        try {
                                                autokeyMechanismInit(c);
                                        } catch (SQLException e) {
                                                throw new RuntimeException(e);
                                        } 
                                }
                        }
                }
                return generatedkeyRetrieve;
        }
        
        AutoKeyRetrieveType getGeneratedkeyRetrieveType(Connection c){
                if(retrieveType == null){
                        synchronized (this) {
                                if(retrieveType == null){
                                        retrieveType = AutoKeyRetrieveType.valueOf(getGeneratedkeyRetrieve(c));
                                }
                        }
                }
                return retrieveType;
        }
        String getGeneratedkeyStatement(Connection c){
                // double check
                if(generatedkeyStatement == null &&  !AutoKeyRetrieveType.auto.equals(retrieveType)){
                        synchronized (this) {
                                if(generatedkeyStatement == null &&  !AutoKeyRetrieveType.auto.equals(retrieveType)){
                                        try {
                                                autokeyMechanismInit(c);
                                        } catch (SQLException e) {
                                                throw new RuntimeException(e);
                                        }
                                }
                        }
                }
                return generatedkeyStatement;
        }

        public void setDebug(boolean isDebug) {
                this.isDebug = isDebug;
        }

}