executeoracle.java

基于数据库操作的封装

package org.shaoye.magic.oracle;

import org.apache.commons.beanutils.*;

import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.ArrayList;
import java.util.Map;
import java.util.Enumeration;
import java.util.Collection;
import java.util.List;
import java.util.Iterator;
import java.sql.Statement;
import java.util.Set;

import org.shaoye.magic.MagicException;
import org.shaoye.magic.PrimaryKeyObj;
import org.shaoye.magic.Column;

import org.panda.Globals;
/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */

public class ExecuteOracle{

  private String table;
  private String owner;
  private String sql;
  private String sql_filed;
  private String sql_value;
  private PreparedStatement pstmt;
  private Statement stmt;
  private Connection conn;
  private ResultSet st;
  private ResultSetMetaData stm;
  private HashMap hashmap;
  //protected Column column = new Column();
  private Hashtable columns = new Hashtable();
  private String key;

  public ExecuteOracle(Connection conn,String table,String owner) throws Exception{
    this.conn = conn;
    this.table = table;
    columns=initColumn(owner,table);
  }

  public ExecuteOracle(Connection conn,String table,String owner,String _key) throws Exception{
    this.conn = conn;
    this.table = table;
    columns=initColumn(owner,table);
    this.key = _key;
  }

  /**
   * @see org.shaoye.magic.Operate#add(Map)
   */
  public void add(Object item) throws SQLException ,Exception{
          if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));
          sql = "";
          sql += "insert into " + table + " (";
          sql_filed = "";
          sql_value = "";

          try{

            java.util.Set set = PropertyUtils.describe(item).keySet();
            java.util.Iterator it = set.iterator();
            while (it.hasNext()) {
              String key= (String)it.next();
              sql_filed += key+",";
              System.out.println("key: "+key);
              //String value = (String)PropertyUtils.getProperty(item,key);
              //System.out.println("value: "+value);
              //sql_value+="'"+value+"',";
            }

            sql_filed=sql_filed.substring(0,sql_filed.length()-1);
            sql_value=sql_value.substring(0,sql_value.length()-1);
            sql = sql + sql_filed + ") values (" + sql_value + ")";
            System.out.println(sql);

            pstmt = conn.prepareStatement(sql);
            System.out.println( "INSERT_SQL: "+ sql);
            pstmt.executeUpdate();
          }
          catch(SQLException sqlex){
            throw sqlex;
          }
          catch(Exception ex){
            throw ex;
          }
          finally{
          }

  }

  /**
   * @see org.shaoye.magic.Operate#add(Map)
   */
  public void add(HashMap item) throws SQLException ,Exception{
          if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));
          sql = "";
          sql += "insert into " + table + " (";
          sql_filed = "";
          sql_value = "";

          try{

            java.util.Set set = item.keySet();
            java.util.Iterator it = set.iterator();
            int y=0;
            while (it.hasNext()) {
              String key= (String)it.next();
              sql_filed += key+",";
              
              String value = (String)item.get(key);
              Set col_keys = this.columns.keySet();
              java.util.Iterator col_iter = col_keys.iterator();
              while(col_iter.hasNext())
              {
            	  String col_key = (String)col_iter.next();
            	  String col_type = "";
            	  System.out.println(y+"--"+col_key+"---"+key);
            	  
            	  if(key.equals(col_key))
            	  {
            		  col_type = ((Column)columns.get(key)).getType();
            		  if(col_type.toUpperCase().indexOf("CHAR") >= 0) {
            			  sql_value+="'"+value+"',";
            		  }
            		  else if(col_type.equalsIgnoreCase("NUMBER")){
            			  sql_value+=value+",";
            		  }
            		  else if(col_type.equalsIgnoreCase("DATE")){
            			  sql_value+="TO_DATE('"+value+"','"
            			           + Globals.DATE_FORMAT_SHORT +"'),";
            		  }
            		  else
            			  continue;
            	  }
              }
            }

            sql_filed=sql_filed.substring(0,sql_filed.length()-1);
            sql_value=sql_value.substring(0,sql_value.length()-1);
            sql = sql + sql_filed + ") values (" + sql_value + ")";
            System.out.println( "INSERT_SQL: "+ sql);
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
          }
          catch(SQLException sqlex){
            throw sqlex;
          }
          catch(Exception ex){
            throw ex;
          }
          finally{
          }

  }

  /**
   *
   * @param item
   * @return
   * @throws SQLException
   * @throws java.lang.Exception
   */
  public ArrayList getItemListMatch( Object item,String sql,int start,int end )
      throws SQLException,Exception
  {
    if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));

    ArrayList list = new ArrayList();
    String strSql = "SELECT rownum s_row,w.* FROM "+table+" w WHERE 1=1 ";
    sql_filed="";
    if(item!=null){
      java.util.Set set = PropertyUtils.describe(item).keySet();
      java.util.Iterator                                                                                                          it = set.iterator();

      while (it.hasNext()) {
        String key = (String) it.next();
        String value = "";
        if(!key.equals("class")){
          value = (String) PropertyUtils.getProperty(item, key);
          sql_filed += "and " + key + " like '%" + value + "%'";
        }
      }
    }
    strSql = strSql + sql_filed + " and rownum<="+end ;
    strSql = " select * from ("+ strSql +") where s_row>"+start;
    System.out.println(strSql);
    pstmt = conn.prepareStatement(strSql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); //Mysql使锟斤拷
    st = pstmt.executeQuery();
    stm = st.getMetaData();
    while(st.next())
    {
            HashMap hashResult = new HashMap();
            for (int s = 0; s < stm.getColumnCount(); s++) {
                    hashResult.put(
                            st.getMetaData().getColumnName(s + 1),
                            st.getString(st.getMetaData().getColumnName(s + 1)));
            }
            list.add(hashResult);
    };
    return list;
  }

  /**
   *
   * @param sql
   * @return
   * @throws SQLException
   * @throws java.lang.Exception
   */ 
  public ArrayList getItemListBySQL(String sql)
      throws SQLException,Exception
  {
    if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));

    ArrayList list = new ArrayList();
    pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); //
    st = pstmt.executeQuery();
    System.out.println( "getItemListBySQL: "+sql );
    stm = st.getMetaData();
    
    while(st.next())
    {
            HashMap hashResult = new HashMap();
            for (int s = 0; s < stm.getColumnCount(); s++) {
                    hashResult.put(
                            st.getMetaData().getColumnName(s + 1).toUpperCase(),
                            st.getString(st.getMetaData().getColumnName(s + 1)));
            }
            list.add(hashResult);
    };
    return list;
  }

  /**
  *
  * @param sql
  * @return
  * @throws SQLException
  * @throws java.lang.Exception
  */ 
 public HashMap getItemBySQL(String sql)
     throws SQLException,Exception
 {
   if (table == null || table.length() == 0)
                 throw (new MagicException("Error 001 : table's name is null"));

   HashMap map = new HashMap();
   pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); //
   System.out.println( "getItemBySQL: "+ sql );
   st = pstmt.executeQuery();
   
   stm = st.getMetaData();
   
   while(st.next())
   {
           for (int s = 0; s < stm.getColumnCount(); s++) {
                   map.put(
                           st.getMetaData().getColumnName(s + 1).toUpperCase(),
                           st.getString(st.getMetaData().getColumnName(s + 1)));
           }
   }
   System.out.println("Map: "+map.size());
   return map;
 }  
  
  /**
   *
   * @param item
   * @return
   * @throws SQLException
   * @throws java.lang.Exception
   */
  public HashMap getItem( Object item)
      throws SQLException,Exception
  {
    if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));
    HashMap hashResult = new HashMap();
    try{
      ArrayList list = new ArrayList();
      String strSql = "SELECT * FROM" + table + "WHERE 1=1 ";
      java.util.Set set = PropertyUtils.describe(item).keySet();
      java.util.Iterator it = set.iterator();

      while (it.hasNext()) {
        String key = (String) it.next();
        String value = (String) PropertyUtils.getProperty(item, key);
        sql_filed += "and " + key + " = '" + value + "'";
      }
      strSql = strSql + sql_filed;
      System.out.println(strSql);
      pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY); //
      st = pstmt.executeQuery();
      stm = st.getMetaData();
      while (st.next()) {
        for (int s = 0; s < stm.getColumnCount(); s++) {
          hashResult.put(
              st.getMetaData().getColumnName(s + 1),
              st.getString(st.getMetaData().getColumnName(s + 1)));
        }
      }