ProcedureOracle

package com.zxwl.base.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.sql.DataSource;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.SessionFactoryUtils;

public class ProcedureOracle<T>
  implements IProcedure<T>
{
  protected EntityHelper<T> entityHelper;
  private HibernateTemplate hibernateTemplate;

  public String formatFieldName(String fieldName)
  {
    String[] names = fieldName.split("_");
    StringBuilder nameBuffer = new StringBuilder();
    nameBuffer.append(names[0]);
    for (int i = 1; i < names.length; i++)
    {
      nameBuffer.append(names[i].substring(0, 1).toUpperCase());
      nameBuffer.append(names[i].substring(1));
    }

    return nameBuffer.toString();
  }

  public ProcedureOracle(EntityHelper<T> entityHelper)
  {
    this.entityHelper = entityHelper;
  }

  public void setHibernateTemplate(HibernateTemplate hibernateTemplate)
  {
    this.hibernateTemplate = hibernateTemplate;
  }

  public HibernateTemplate getHibernateTemplate()
  {
    return this.hibernateTemplate;
  }

  public int getDatabaseType(Class<?> type)
  {
    if (type == String.class)
    {
      return 12;
    }
    if (type == Integer.class)
    {
      return 2;
    }
    if (type == Short.class)
    {
      return 2;
    }
    if (type == Date.class)
    {
      return 91;
    }
    return 12;
  }

  public void registerParameter(CallableStatement cs, QueryParameter queryParameter)
    throws SQLException
  {
    Set<String> paraNames = queryParameter.findParameterNames();
    for (String paraName : paraNames)
    {
      Parameter parameter = queryParameter.findParameter(paraName);

      if (parameter != null)
      {
        int paraType = getDatabaseType(parameter.findType());
        Object value = parameter.getValue();
        if (parameter.getDirction().equals("IN"))
        {
          cs.setObject(paraName, value, paraType);
        }
        else
        {
          cs.registerOutParameter(paraName, paraType);
          if (value != null)
          {
            cs.setObject(paraName, value, paraType);
          }
        }
      }
    }
  }

  public List<T> queryList(final String procedureName, final QueryParameter queryParameter)
  {
    return (List)getHibernateTemplate().execute(new HibernateCallback()
    {
      public Object doInHibernate(Session session)
        throws HibernateException, SQLException
      {
        Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();

        String queryString = "";
        String queryParams = "";

        int paramCount = 0;
        paramCount = queryParameter.size().intValue();

        paramCount++;

        for (int i = 0; i < paramCount; i++)
        {
          queryParams = queryParams + "?,";
        }

        if (!"".equals(queryParams))
        {
          queryParams = queryParams.substring(0, queryParams.length() - 1);
        }

        queryString = "{call " + procedureName + "(" + queryParams + ")}";
        CallableStatement cs = con.prepareCall(queryString);

        Set<String> paraNames = queryParameter.findParameterNames();
        List resultList = null;
        ResultSet rs = null;
        try
        {
          ProcedureOracle.this.registerParameter(cs, queryParameter);

          cs.registerOutParameter("resultSet", -10);

          cs.execute();

          for (String paraName : paraNames)
          {  
            Parameter parameter = queryParameter.findParameter(paraName);
            if (parameter != null)
            {
              if (parameter.getDirction().equals("OUT"))
              {
                parameter.setValue(cs.getObject(paraName));
              }
            }
          }

          if (ProcedureOracle.this.entityHelper == null)
          {
            throw new Exception("entityHelper异常");
          }
          rs = (ResultSet)cs.getObject("resultSet");
          resultList = ProcedureOracle.this.entityHelper.convert(rs);
        }
        catch (SQLException e)
        {
          e.printStackTrace();
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }
        finally
        {
          if (rs != null)
          {
            rs.close();
          }
          if (cs != null)
          {
            cs.close();
          }
          if (con != null)
          {
            con.close();
          }
        }
        return resultList;
      }
    });
  }

  public void execute(final String procedureName, final QueryParameter queryParameter)
  {
    getHibernateTemplate().execute(new HibernateCallback()
    {
      public Object doInHibernate(Session session)
        throws HibernateException, SQLException
      {
        Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();

        String queryString = "";
        String queryParams = "";

        int paramCount = 0;
        paramCount = queryParameter.size().intValue();

        for (int i = 0; i < paramCount; i++)
        {
          queryParams = queryParams + "?,";
        }

        if (!"".equals(queryParams))
        {
          queryParams = queryParams.substring(0, queryParams.length() - 1);
        }

        queryString = "{call " + procedureName + "(" + queryParams + ")}";

        CallableStatement cs = con.prepareCall(queryString);

        Set<String> paraNames = queryParameter.findParameterNames();
        try
        {
          ProcedureOracle.this.registerParameter(cs, queryParameter);

          cs.execute();

          for (String paraName : paraNames)
          {
            Parameter parameter = queryParameter.findParameter(paraName);
            if (parameter != null)
            {
              if (parameter.getDirction().equals("OUT"))
              {
                parameter.setValue(cs.getObject(paraName));
              }
            }
          }
        }
        catch (Exception localException)
        {
        }
        finally
        {
          if (cs != null)
          {
            cs.close();
          }
          if (con != null)
          {
            con.close();
          }
        }
        return null;
      }
    });
  }

  public List<Map<String, String>> queryMapList(final String procedureName, final QueryParameter queryParameter)
  {
    return (List)getHibernateTemplate().execute(new HibernateCallback()
    {
      public Object doInHibernate(Session session)
        throws HibernateException, SQLException
      {
        Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();

        String queryString = "";
        String queryParams = "";

        int paramCount = 0;
        paramCount = queryParameter.size().intValue();

        paramCount++;

        for (int i = 0; i < paramCount; i++)
        {
          queryParams = queryParams + "?,";
        }

        if (!"".equals(queryParams))
        {
          queryParams = queryParams.substring(0, queryParams.length() - 1);
        }

        queryString = "{call " + procedureName + "(" + queryParams + ")}";

        CallableStatement cs = con.prepareCall(queryString);

        Set<String> paraNames = queryParameter.findParameterNames();

        List resultList = null;
        ResultSet rs = null;
        try
        {
          ProcedureOracle.this.registerParameter(cs, queryParameter);

          cs.registerOutParameter("resultSet", -10);

          cs.execute();

          for (String paraName : paraNames)
          {
            Parameter parameter = queryParameter.findParameter(paraName);
            if (parameter != null)
            {
              if (parameter.getDirction().equals("OUT"))
              {
                parameter.setValue(cs.getObject(paraName));
              }
            }
          }

          rs = (ResultSet)cs.getObject("resultSet");
          int colCount = rs.getMetaData().getColumnCount();
          String[] colNameList = new String[colCount];
          for (int i = 0; i < colCount; i++)
          {
            String fieldName = rs.getMetaData().getColumnName(i + 1);

            colNameList[i] = ProcedureOracle.this.formatFieldName(fieldName.toLowerCase());
          }

          resultList = new ArrayList();

          while (rs.next())
          {
            Map rowMap = new HashMap();
            for (int i = 0; i < colCount; i++)
            {
              Object val = rs.getObject(i + 1);
              if (val != null)
              {
                if ((val instanceof Date))
                {
                  val = rs.getTimestamp(i + 1);
                  rowMap.put(colNameList[i], val.toString());
                }
                else
                {
                  rowMap.put(colNameList[i], val.toString());
                }
              }
              else
              {
                rowMap.put(colNameList[i], null);
              }
            }

            resultList.add(rowMap);
          }
        }
        catch (Exception localException)
        {
        }
        finally
        {
          if (rs != null)
          {
            rs.close();
          }
          if (cs != null)
          {
            cs.close();
          }
          if (con != null)
          {
            con.close();
          }
        }
        return resultList;
      }
    });
  }
}