java DataBaseExecutor

package com.icss.core.db;

import com.icss.core.util.UUIDGenerator;
import com.icss.core.util.format.ValueObjectFormat;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public abstract class DataBaseExecutor
{
  protected Connection m_conn = null;

  public static DataBaseExecutor getExecutor(Connection conn) {
    DataBaseExecutor executor = null;
    int dbtype = DataBaseType.getConnectionDBType(conn);
    switch (dbtype) {
    case 1:
      executor = new OracleDataBaseExecutor(conn); break;
    case 8:
      executor = new AccessDataBaseExecutor(conn); break;
    case 0:
      break;
    default:
      throw new RuntimeException("unsupported db type!");
    }
    return executor;
  }
  protected DataBaseExecutor(Connection conn) {
    this.m_conn = conn;
  }
  public Connection getConnection() { return this.m_conn;
  }

  public int create(RecordSet recordSet)
    throws SQLException
  {
    for (int i = 0; i < recordSet.size(); i++)
    {
      create(recordSet.get(i));
    }
    return recordSet.size();
  }

  protected int create(Record record)
    throws SQLException
  {
    PreparedStatement pstmt = null;
    try
    {
      fullPrimaryKeyValueWithUuid(record);

      StringBuffer sb = new StringBuffer("INSERT INTO ");
      sb.append(record.getEntityName()).append(" ( ");
      StringBuffer placeholderBuffer = new StringBuffer();
      String[] fields = record.getFields();
      for (int i = 0; i < fields.length; i++)
      {
        if (i > 0)
        {
          sb.append(",");
          placeholderBuffer.append(",");
        }
        sb.append(fields[i]);
        placeholderBuffer.append("?");
      }
      sb.append(" ) VALUES ( ").append(placeholderBuffer).append(" )");
      pstmt = this.m_conn.prepareStatement(sb.toString());
      Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
      setPreparedStatementParameter(pstmt, record.getFields(), 1, entryColumnTypeCode, record);
      int i = pstmt.execute() ? 1 : 0;
      return i;
    }
    catch (SQLException e)
    {
      throw e;
    }
    finally
    {
      try
      {
        if (pstmt != null)
        {
          pstmt.close();
        }
      }
      catch (SQLException localSQLException2) {
      }
    }
    throw localObject;
  }

  protected int update(Record record)
    throws SQLException
  {
    PreparedStatement pstmt = null;
    try
    {
      String[] fields = record.getFields();
      String[] primaryKeyFields = record.getPrimaryKeyFields();
      String[] fieldsExceptPK = new String[fields.length - primaryKeyFields.length];
      StringBuffer sb = new StringBuffer("UPDATE ");
      sb.append(record.getEntityName()).append(" SET ");
      int i = 0; for (int j = 0; i < fields.length; i++)
      {
        String field = fields[i];
        if (record.containsPrimaryKey(field))
          continue;
        if (j > 0)
        {
          sb.append(",");
        }
        sb.append(fields[i]);
        sb.append("=?");
        fieldsExceptPK[(j++)] = field;
      }

      sb.append(" WHERE 1=1 ");
      for (int i = 0; i < primaryKeyFields.length; i++)
      {
        String pk = primaryKeyFields[i];
        Object pkValue = record.getObjectValue(pk);
        if ((pkValue == null) || ("".equals(pkValue)) || ("null".equals(pkValue)))
        {
          throw new DBException("some of the key column is not evaluated, the column name is " + pk);
        }

        sb.append("AND ");
        sb.append(pk);
        sb.append("=? ");
      }

      pstmt = this.m_conn.prepareStatement(sb.toString());
      Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
      setPreparedStatementParameter(pstmt, fieldsExceptPK, 1, entryColumnTypeCode, record);
      setPreparedStatementParameter(pstmt, primaryKeyFields, fieldsExceptPK.length + 1, entryColumnTypeCode, record);
      int i = pstmt.executeUpdate();
      return i;
    }
    finally
    {
      try
      {
        if (pstmt != null)
        {
          pstmt.close();
        }
      }
      catch (SQLException localSQLException1) {
      }
    }
    throw localObject1;
  }

  public int update(RecordSet recordSet)
    throws SQLException
  {
    for (int i = 0; i < recordSet.size(); i++)
    {
      update(recordSet.get(i));
    }
    return recordSet.size();
  }

  public int delete(RecordSet recordSet)
    throws SQLException
  {
    for (int i = 0; i < recordSet.size(); i++)
    {
      delete(recordSet.get(i));
    }
    return recordSet.size();
  }

  protected int delete(Record record)
    throws SQLException
  {
    PreparedStatement pstmt = null;
    try
    {
      String[] primaryKeyFields = record.getPrimaryKeyFields();
      StringBuffer sb = new StringBuffer("DELETE FROM ");
      sb.append(record.getEntityName());

      sb.append(" WHERE ");
      if (primaryKeyFields.length == 0)
      {
        throw new DBException("you must specify one column as the primary key at least ");
      }
      for (int i = 0; i < primaryKeyFields.length; i++)
      {
        String pk = primaryKeyFields[i];
        Object pkValue = record.getObjectValue(pk);
        if ((pkValue == null) || ("".equals(pkValue)) || ("null".equals(pkValue)))
        {
          throw new DBException("some of the key column is not evaluated, the column name is " + pk);
        }

        if (i > 0)
        {
          sb.append(" AND ");
        }
        sb.append(pk);
        sb.append("=?");
      }

      pstmt = this.m_conn.prepareStatement(sb.toString());
      Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
      setPreparedStatementParameter(pstmt, primaryKeyFields, 1, entryColumnTypeCode, record);
      int i = pstmt.executeUpdate();
      return i;
    }
    finally
    {
      try
      {
        if (pstmt != null)
        {
          pstmt.close();
        }
      }
      catch (SQLException localSQLException1) {
      }
    }
    throw localObject1;
  }

  public int delete(String entryName, String fieldName, List values)
  {
    return 0;
  }

  public int execute(String sql)
    throws SQLException
  {
    PreparedStatement pstmt = null;
    try
    {
      pstmt = this.m_conn.prepareStatement(sql);
      int i = pstmt.executeUpdate();
      return i;
    }
    finally
    {
      try
      {
        if (pstmt != null)
        {
          pstmt.close();
        }
      }
      catch (SQLException localSQLException1) {
      }
    }
    throw localObject;
  }

  public Record findFirst(String sql)
    throws SQLException
  {
    RecordSet recs = find(sql);
    return (recs != null) && (recs.size() > 0) ? recs.get(0) : null;
  }

  public RecordSet find(String sql)
    throws SQLException
  {
    RecordSet records = new RecordSet();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try
    {
      pstmt = this.m_conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();
      Map entryColumnType = getEntryColumnType(rsmd);

      while (rs.next())
      {
        Record record = fillRecord(rs, rsmd, entryColumnType);
        records.add(record);
      }
      RecordSet localRecordSet1 = records;
      return localRecordSet1;
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
        if (pstmt != null)
        {
          pstmt.close();
        }
      }
      catch (SQLException localSQLException1) {
      }
    }
    throw localObject;
  }

  public RecordSet find(String sql, PagingInfo pagingInfo)
    throws SQLException
  {
    List selectColumnList = getFieldsFromSqlStatment(sql);
    StringBuffer selectedFields = new StringBuffer("");
    for (int i = 0; i < selectColumnList.size(); i++)
    {
      selectedFields.append((String)selectColumnList.get(i));
      selectedFields.append(",");
    }
    selectedFields.setCharAt(selectedFields.length() - 1, ' ');

    int iPageSize = pagingInfo.getPageSize();
    int iPageNo = pagingInfo.getCurrentPageNo();

    int iRecordCount = getRecordTotalCount(sql);
    pagingInfo.setTotalRecordCount(iRecordCount);

    StringBuffer sb = new StringBuffer();
    sb.append("SELECT ").append(selectedFields).append(" FROM (");
    sb.append("SELECT TBL.*,ROWNUM RN FROM (");
    sb.append(sql);
    sb.append(")TBL WHERE ROWNUM <= ").append(iPageNo * iPageSize);
    sb.append(") WHERE RN > ").append((iPageNo - 1) * iPageSize);
    return find(sb.toString());
  }

  private int getRecordTotalCount(String sql) throws SQLException
  {
    StringBuffer sb = new StringBuffer();
    sb.append("SELECT COUNT(1) AS CC FROM (");
    sb.append(sql);
    sb.append(") ");

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try
    {
      pstmt = this.m_conn.prepareStatement(sb.toString());
      rs = pstmt.executeQuery();
      int i = rs.next() ? rs.getInt("CC") : 0;
      return i;
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
        if (pstmt != null)
        {
          pstmt.close();
        }
      }
      catch (SQLException localSQLException1) {
      }
    }
    throw localObject;
  }

  protected RecordSet find(Record record, String[] columns, String[] order, PagingInfo pagingInfo) throws SQLException
  {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    RecordSet records = new RecordSet();
    try
    {
      String[] conditionFields = record.getFields();
      String sql = buildSql4FindByRecord(record, columns, conditionFields, order);
      pstmt = this.m_conn.prepareStatement(sql);

      Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
      setPreparedStatementParameter(pstmt, conditionFields, 1, entryColumnTypeCode, record);

      rs = pstmt.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();
      Map entryColumnType = getEntryColumnType(rsmd);
      while (rs.next())
      {
        Record newRecord = fillRecord(rs, rsmd, entryColumnType);
        records.add(newRecord);
      }
      RecordSet localRecordSet1 = records;
      return localRecordSet1;
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
        if (pstmt != null)
        {
          pstmt.close();
        }
      }
      catch (SQLException localSQLException1) {
      }
    }
    throw localObject;
  }

  protected RecordSet findAll(Record record, String[] columns, String[] order)
    throws SQLException
  {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    RecordSet records = new RecordSet();
    try
    {
      String sql = buildSql4FindByRecord(record, columns, null, order);
      pstmt = this.m_conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();
      Map entryColumnType = getEntryColumnType(rsmd);
      while (rs.next())
      {
        Record newRecord = fillRecord(rs, rsmd, entryColumnType);
        records.add(newRecord);
      }
      RecordSet localRecordSet1 = records;
      return localRecordSet1;
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
        if (pstmt != null)
        {
          pstmt.close();
        }
      }
      catch (SQLException localSQLException1) {
      }
    }
    throw localObject;
  }

  protected Record findByPrimaryKey(Record record, String[] selectFields)
    throws SQLException
  {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try
    {
      String[] primaryKeyFields = record.getPrimaryKeyFields();
      String sql = buildSql4FindByRecord(record, selectFields, primaryKeyFields, null);
      pstmt = this.m_conn.prepareStatement(sql);
      Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
      setPreparedStatementParameter(pstmt, primaryKeyFields, 1, entryColumnTypeCode, record);
      rs = pstmt.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();
      Map entryColumnType = getEntryColumnType(rsmd);
      Record ret = null;
      if (rs.next())
      {
        ret = fillRecord(rs, rsmd, entryColumnType);
      }
      Record localRecord1 = ret;
      return localRecord1;
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
        if (pstmt != null)
        {
          pstmt.close();
        }
      }
      catch (SQLException localSQLException1) {
      }
    }
    throw localObject;
  }

  protected void fullPrimaryKeyValueWithUuid(Record record)
  {
    Iterator pkIterator = record.primaryKeyIterator();
    while (pkIterator.hasNext())
    {
      String pk = (String)pkIterator.next();
      Object pkValue = record.getObjectValue(pk);
      if ((pkValue != null) && (!"".equals(pkValue)) && (!"null".equals(pkValue)))
        continue;
      record.addData(pk, UUIDGenerator.getUUID());
    }
  }

  protected void setPreparedStatementParameter(PreparedStatement pstmt, String[] fields, int baseParamIndex, Map<String, Integer> entryColumnTypeCode, Record record)
    throws SQLException
  {
    for (int i = 0; i < fields.length; i++)
    {
      String columnName = fields[i];
      Object columnValue = record.getObjectValue(columnName);
      ValueObjectFormat formatter = ValueObjectFormat.getFormat(columnValue);
      Integer columnTypeCode = (Integer)entryColumnTypeCode.get(columnName);
      if (columnTypeCode == null) throw new RuntimeException("the column " + columnName + " is undefined,please check it and try again.");
      setPreparedStatementParameter(pstmt, i + baseParamIndex, columnTypeCode.intValue(), columnValue, formatter);
    }
  }

  protected Map<String, String> getEntryColumnType(String entryName) throws SQLException
  {
    PreparedStatement ps = null;
    ResultSet rs = null;
    try
    {
      StringBuffer sb = new StringBuffer("SELECT * FROM ").append(entryName).append(" WHERE 1=2");
      ps = this.m_conn.prepareStatement(sb.toString());
      rs = ps.executeQuery();
      Map localMap = getEntryColumnType(rs.getMetaData());
      return localMap;
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
        if (ps != null)
        {
          ps.close();
        }
      }
      catch (Exception localException1) {
      }
    }
    throw localObject;
  }

  protected Map<String, String> getEntryColumnType(ResultSetMetaData rsmd) throws SQLException
  {
    Map retMap = new HashMap();
    for (int i = 1; i <= rsmd.getColumnCount(); i++)
    {
      retMap.put(rsmd.getColumnName(i).toUpperCase(), rsmd.getColumnClassName(i));
    }
    return retMap;
  }

  protected Map<String, Integer> getEntryColumnTypeCode(String entryName) throws SQLException
  {
    PreparedStatement ps = null;
    ResultSet rs = null;
    try
    {
      StringBuffer sb = new StringBuffer("SELECT * FROM ").append(entryName).append(" WHERE 1=2");
      ps = this.m_conn.prepareStatement(sb.toString());
      rs = ps.executeQuery();

      Map retMap = new HashMap();
      ResultSetMetaData rsmd = rs.getMetaData();
      for (int i = 1; i <= rsmd.getColumnCount(); i++)
      {
        retMap.put(rsmd.getColumnName(i).toUpperCase(), Integer.valueOf(rsmd.getColumnType(i)));
      }
      Map localMap1 = retMap;
      return localMap1;
    }
    finally
    {
      try
      {
        if (rs != null)
        {
          rs.close();
        }
        if (ps != null)
        {
          ps.close();
        }
      }
      catch (Exception localException1) {
      }
    }
    throw localObject;
  }

  protected abstract void setPreparedStatementParameter(PreparedStatement paramPreparedStatement, int paramInt1, int paramInt2, Object paramObject, ValueObjectFormat paramValueObjectFormat) throws SQLException;

  protected abstract Record fillRecord(ResultSet paramResultSet, ResultSetMetaData paramResultSetMetaData, Map<String, String> paramMap) throws SQLException;

  private String buildSql4FindByRecord(Record record, String[] selectFields, String[] conditionFields, String[] order) {
    StringBuffer sb = new StringBuffer("SELECT ");
    if ((selectFields == null) || (selectFields.length == 0))
    {
      sb.append(" * ");
    }
    else
    {
      for (int i = 0; i < selectFields.length; i++)
      {
        sb.append(selectFields[i]);
        sb.append(",");
      }
      sb.setCharAt(sb.length() - 1, ' ');
    }
    sb.append(" FROM ");
    sb.append(record.getEntityName());

    if (conditionFields != null)
    {
      sb.append(" WHERE 1=1 ");
      if (conditionFields.length == 0)
      {
        throw new DBException("you do not set the search condition, at least one condition column was specified!");
      }
      for (int i = 0; i < conditionFields.length; i++)
      {
        String column = conditionFields[i];
        Object pkValue = record.getObjectValue(column);
        if ((pkValue == null) || ("".equals(pkValue)) || ("null".equals(pkValue)))
        {
          throw new DBException("some of the condition column is not evaluated, the column name is " + column);
        }

        sb.append("AND ");
        sb.append(column);
        sb.append("=? ");
      }
    }

    if (order != null)
    {
      sb.append(" ORDER BY ");
      for (int i = 0; i < order.length; i++)
      {
        sb.append(order[i]).append(",");
      }
      sb.setCharAt(sb.length() - 1, ' ');
    }
    return sb.toString();
  }

  private List<String> getFieldsFromSqlStatment(String sql)
  {
    List fields = new ArrayList();

    String s = sql;
    Matcher m = null;

    Pattern parenthesis_pattern = Pattern.compile("\\([^\\(\\)]*\\)");
    while (parenthesis_pattern.matcher(s).find())
    {
      s = s.replaceAll("\\([^\\(\\)]*\\)", " ");
    }

    Pattern select_pattern = Pattern.compile("\\s*SELECT\\s+(.*?\\s+FROM)\\s+.*", 2);
    m = select_pattern.matcher(s);
    if (m.find())
    {
      s = m.group(1);
    }

    Pattern fields_pattern = Pattern.compile("\\s*([^\\s\\.]*)\\s*(,|FROM)", 2);
    m = fields_pattern.matcher(s);
    while (m.find())
    {
      fields.add(m.group(1));
    }

    return fields;
  }
}