executeoracle.java

基于数据库操作的封装

  /**
   * 画录入界面，无序排列
   * */
  public HashMap initCol_Comment(String owner,String tblName) throws SQLException {
          String sql = "select s.column_name,s.comments "
        	         + "from all_col_comments s  "
                     + "where s.comments is not null "
                     + "and s.owner=upper('"+ owner +"') "
                     + "and s.table_name=upper('"+ tblName +"')";
          
          System.out.println(sql); 
          pstmt = conn.prepareStatement(sql);
          ResultSet rs = pstmt.executeQuery(); 
          ResultSetMetaData stm = rs.getMetaData();
          HashMap map = new HashMap();
          while(rs.next()){
            map.put(rs.getString("column_name"),rs.getString("comments"));
          }	
          rs.close();
          return map;
  }  

  /**
   * @see org.shaoye.magic.Operate#destroy()
   */
  public void destroy() {

          try {
                  if (st != null)
                          st.close();
                  if (stmt != null)
                          stmt.close();
                  if (pstmt != null)
                          pstmt.close();
                  if (conn != null)
                          conn.close();

          } catch (SQLException ex) {

                  ex.printStackTrace();
          } catch ( Exception e )  {

                  e.printStackTrace();
          }

  }

  /**
   * 删锟斤拷一锟斤拷锟铰�(锟斤拷锟斤拷一锟斤拷锟街讹拷)
   * key -- ArrayList 锟斤拷录锟斤拷锟斤拷锟斤拷锟斤拷斜锟�
   *
   * @see org.shaoye.magic.Operate#remove(Object)
   */
  public void del(Collection collection) throws SQLException {

          if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));
          //001锟斤拷锟斤拷
          List list = new ArrayList();
          list = ( ArrayList )collection;

          sql = "";
          sql += "DELETE FROM " + table + " WHERE " + this.key + "=?";
          try{
            pstmt = conn.prepareStatement(sql);

            for (int i = 0; i < list.size(); i++) {
              Column column = new Column();
              column = (Column) list.get(i);
              this.setData(pstmt, 1, column.getName(), column.getDefaultValue());
              pstmt.addBatch();
            }

            pstmt.executeBatch();
          }
          catch(SQLException sqlex){
            throw sqlex;
          }
          finally{
          }
  }

  /**
   *
   * key -- ArrayList 锟斤拷录锟斤拷锟斤拷锟斤拷锟斤拷斜锟�
   *
   * @see org.shaoye.magic.Operate#remove(Object)
   */
  public void executeUpdateBySQL(String strSQL) throws SQLException {

          if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));

          try{
            pstmt = conn.prepareStatement(strSQL);
            System.out.println( "Execute SQL Statement: "+strSQL );
            pstmt.executeUpdate();
          }
          catch(SQLException sqlex){
            throw sqlex;
          }
          finally{
          }
  }

  /**
   *
   * key -- ArrayList 锟斤拷录锟斤拷锟斤拷锟斤拷锟斤拷斜锟�
   *
   * @see org.shaoye.magic.Operate#remove(Object)
   */
  public void executeUpdateBatchBySQLArray(ArrayList list) throws SQLException {

          if (table == null || table.length() == 0)
                  throw (new MagicException("Error 001 : table's name is null"));

          try{
              stmt = conn.createStatement();

              for (int i = 0; i < list.size(); i++) {
                  stmt.addBatch((String)list.get(i));
                  System.out.println( "BySQLArray: "+list.get(i) );
              }
              stmt.executeBatch();
          }
          catch(SQLException sqlex){
            throw sqlex;
          }
          finally{
          }
  }

  /**
   *
   * @param item Object
   * @param sql String
   * @param start int
   * @param end int
   * @throws SQLException
   * @throws Exception
   * @return ArrayList
   */
  public ArrayList getItemList( 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="";
    try{
      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 + " = '" + 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);
      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);
      }
      ;
    }catch(SQLException sqlex){
      throw sqlex;
    }catch(Exception ex){
      throw ex;
    }finally{
      return list;
    }

  }

  /**
   *
   * @return ArrayList
   * @throws SQLException
   * @throws Exception
   */
  public ArrayList getItemList( )
      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="";
    try{
      System.out.println(strSql);
      pstmt = conn.prepareStatement(strSql, ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
      st = pstmt.executeQuery();
      stm = st.getMetaData();
      while (st.next()) {
        HashMap hashResult = new HashMap();
        for (int s = 0; s < stm.getColumnCount(); s++) {
          System.out.println("ColumnName: "+st.getMetaData().getColumnName(s + 1));
          System.out.println("ColumnValue: "+st.getString(st.getMetaData().getColumnName(s + 1)));
          hashResult.put(
              st.getMetaData().getColumnName(s + 1),
              st.getString(st.getMetaData().getColumnName(s + 1)));
        }
        list.add(hashResult);
      };
    }catch(SQLException sqlex){
      throw sqlex;
    }catch(Exception ex){
      throw ex;
    }finally{
      return list;
    }
  }
  
  /**
   * @see org.shaoye.magic.Operate#add(Map)
   */
  public void addForYsmx(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{

            Set set = item.keySet();
            Iterator it = set.iterator();
            int y=0;
            while (it.hasNext()) {
              String key= (String)it.next();
              String[] ketStr = key.split("_");
              
              
              sql_filed += ketStr[1]+",";
              
              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{
          }

  }  
  

  public String getOwner() {
    return owner;
  }
  public String getTable() {
    return table;
  }
  public void setOwner(String owner) {
    this.owner = owner;
  }
  public void setTable(String table) {
    this.table = table;
  }
  public Hashtable getColumns()
  {
	  return this.columns;
  }
}