datamanager.java

我的文件是一个数据添加页面

    PreparedStatement ps = null;
    ResultSet rs = null;
    int deleteNum = 0;
    try {
      cn = DBConnection.getConnection();
      cn.setAutoCommit(false);
      for (int i = 0; i < oid.length; i++) {
        deleteNum += deleteDataInfo(oid[i], cn);
      }
      cn.commit();
      cn.setAutoCommit(true);
    } catch (Exception ex) {
      if (Debug.isJavaBeanDebug) {
        ex.printStackTrace();
      }
      if (cn != null && !cn.getAutoCommit()) {
        cn.rollback();
        cn.setAutoCommit(true);
      }
    } finally {
      try {if (rs != null) {rs.close();
      rs = null;
      }
      } catch (Exception ex) {}
      try {if (ps != null) {ps.close();
      ps = null;
      }
      } catch (Exception ex) {}
      try {if (cn != null) {cn.close();
      cn = null;
      }
      } catch (Exception ex) {}
    }
    return deleteNum;
  }


  /**
   * 删除一条数据对象信息
   * 此方法有可能参与一个事务
   * @param oid long 数据对象编号
   * @param conn Connection 数据库连接
   * @return int
   * @throws Exception
   */
  public int deleteDataInfo(long oid, Connection conn) throws Exception {
    Connection cn = null;
    PreparedStatement ps = null;
    int deleteNum = 0;
    try {
      cn = conn;
      ps = cn.prepareStatement("DELETE FROM CharAttribute WHERE OId = ?");
      ps.setLong(1, oid);
      ps.executeUpdate();
      ps.close();
      ps = null;
      ps = cn.prepareStatement("DELETE FROM DoubleAttribute WHERE OId = ?");
      ps.setLong(1, oid);
      ps.executeUpdate();
      ps.close();
      ps = null;
      ps = cn.prepareStatement("DELETE FROM IntegerAttribute WHERE OId = ?");
      ps.setLong(1, oid);
      ps.executeUpdate();
      ps.close();
      ps = null;
      ps = cn.prepareStatement("DELETE FROM BooleanAttribute WHERE OId = ?");
      ps.setLong(1, oid);
      ps.executeUpdate();
      ps.close();
      ps = null;
      ps = cn.prepareStatement("DELETE FROM ClobAttribute WHERE OId = ?");
      ps.setLong(1, oid);
      ps.executeUpdate();
      ps.close();
      ps = null;
      ps = cn.prepareStatement("DELETE FROM DataInfo WHERE OId = ?");
      ps.setLong(1, oid);
      deleteNum = ps.executeUpdate();
      ps.close();
      ps = null;
    } catch (Exception ex) {
      if (Debug.isJavaBeanDebug) {
        ex.printStackTrace();
      }
    } finally {
      try {if (ps != null) {ps.close();
      ps = null;
      }
      } catch (Exception ex) {}
    }
    return deleteNum;
  }


  /**
   * 得到单个数据对象信息
   * @param oid long 数据对象编号
   * @return DataInfo 当出现错误或是未找到信息时返回一个空对象
   */
  public DataInfo getDataInfo(long oid) {
    Connection cn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    DataInfo info = null;
    try {
      cn = DBConnection.getConnection();
      ps = cn.prepareStatement("SELECT * FROM DataInfo WHERE Oid = ?");
      ps.setLong(1, oid);
      rs = ps.executeQuery();
      if(rs.next()) {
        info = getInfo(rs);
      }
    }
    catch(Exception ex) {
      if (Debug.isJavaBeanDebug) {
        ex.printStackTrace();
      }
      info = null;
    }
    finally {
      try {if (rs != null) {rs.close();
      rs = null;
      }
      } catch (Exception ex) {}
      try {if (ps != null) {ps.close();
      ps = null;
      }
      } catch (Exception ex) {}
      try {if (cn != null) {cn.close();
      cn = null;
      }
      } catch (Exception ex) {}
    }
    return info;
  }


  /**
   * 按条件查找数据对象信息
   * @param tid long 数据对象所属的对象类型编号，为必填项
   * @param cname String 数据对象中文名中包含的关键字，忽略此条件则传递""
   * @param ename String 数据对象英文名中包含的关键字，忽略此条件则传递""
   * @param operator String 数据对象的创建人编号，忽略此条件则传递""
   * @param minCreateTime String 数据对象的最早创建时间，忽略此条件则传递""
   * @param maxCreateTime String 数据对象的最晚创建时间，忽略此条件则传递""
   * @param minLastModifyTime String 数据对象的最早 最后修改时间，忽略此条件则传递""
   * @param maxLastModifyTime String 数据对象的最晚 最后修改时间，忽略此条件则传递""
   * @param pageNo int 页码
   * @param pageSize int 每页显示的记录数
   * @return PageList 返回满足条件的信息列表，当查询出现错误或是未找到数据时返回一个长度为0的列表
   */
  public PageList getDataList(long tid, String cname, String ename,
                              String operator,
                              String minCreateTime, String maxCreateTime,
                              String minLastModifyTime, String maxLastModifyTime,
                              int pageNo, int pageSize) {
    PageList infoList = new PageList();
    if(pageNo<1||pageSize<1) return infoList;
    int sumrecords = 0;
    int beginrecord = (pageNo-1)*pageSize + 1;
    int endrecord = beginrecord+pageSize - 1;
    Connection cn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      StringBuffer whereStr = new StringBuffer();
      ArrayList parList = new ArrayList();
      whereStr.append(" WHERE Tid = ?");
      parList.add(new Long(tid));
      if(cname != null && !cname.trim().equals("")) {
        whereStr.append(" AND cname LIKE ?");
        parList.add("%" + cname.trim() + "%");
      }
      if(ename != null && !ename.trim().equals("")) {
        whereStr.append(" AND ename LIKE ?");
        parList.add("%" + ename.trim() + "%");
      }
      if(operator != null && !operator.trim().equals("")) {
        whereStr.append(" AND operator = ?");
        parList.add(ename.trim());
      }
      if(minCreateTime != null && !minCreateTime.trim().equals("")) {
        whereStr.append(" AND CreateTime >= ?");
        parList.add(minCreateTime.trim() + " 0:00:00");
      }
      if(maxCreateTime != null && !maxCreateTime.trim().equals("")) {
        whereStr.append(" AND CreateTime <= ?");
        parList.add(maxCreateTime.trim() + " 23:59:59");
      }
      if(minLastModifyTime != null && !minLastModifyTime.trim().equals("")) {
        whereStr.append(" AND LastModifyTime >= ?");
        parList.add(minLastModifyTime.trim() + " 0:00:00");
      }
      if(maxLastModifyTime != null && !maxLastModifyTime.trim().equals("")) {
        whereStr.append(" AND LastModifyTime <= ?");
        parList.add(maxLastModifyTime.trim() + " 23:59:59");
      }
      cn = DBConnection.getConnection();
      ps = cn.prepareStatement("SELECT COUNT(Oid) AS num FROM DataInfo " + whereStr.toString());
      SQLTools.prepared(parList, ps);
      rs = ps.executeQuery();
      if(rs.next())
      {
        sumrecords = rs.getInt("num");
      }
      rs.close();
      ps.close();
      if(sumrecords>=beginrecord)
      {
        int readernum = pageNo * pageSize;
        ps = cn.prepareStatement("SELECT *FROM DataInfo " + whereStr.toString()
                                 +" ORDER BY Oid DESC LIMIT ? OFFSET ?");
        parList.add(new Integer(endrecord));
        parList.add(new Integer(beginrecord));
        SQLTools.prepared(parList, ps);
        rs = ps.executeQuery();
        while(rs.next()) {
          infoList.add(getInfo(rs));
        }
      }
    }
    catch(Exception ex) {
      if(Debug.isJavaBeanExceptionDebug) ex.printStackTrace();
    }
    finally {
      try{if(rs != null) {rs.close(); rs = null;}}catch(Exception ex) {}
      try{if(ps != null) {ps.close(); ps = null;}}catch(Exception ex) {}
      try{if(cn != null) {cn.close(); cn = null;}}catch(Exception ex) {}
    }
    infoList.pageno=pageNo;
    infoList.pagesize=pageSize;
    infoList.pagesum=sumrecords;
    if(sumrecords>0){
      if(sumrecords%pageSize==0)
        infoList.pagenum=sumrecords/pageSize;
      else
        infoList.pagenum=sumrecords/pageSize+1;
    }else
      infoList.pagenum=0;
    return infoList;
  }


  /**
   * 根据结果集得到对象信息
   * @param rs ResultSet
   * @return AttributeInfo
   */
  private DataInfo getInfo(ResultSet rs) {
    DataInfo info = null;
    ResultSetMetaData rsmd = null;
    try {
      rsmd = rs.getMetaData();
      info = new DataInfo();
      for (int i = 0; i < rsmd.getColumnCount(); i++) {
        String column = rsmd.getColumnName(i + 1).toLowerCase();
        if (column.equals("oid")) {
          info.setOid(rs.getLong("Oid"));
          break;
        } else if (column.equals("tid")) {
          info.setTid(rs.getLong("Tid"));
          break;
        } else if(column.equals("cname")) {
          info.setCname(rs.getString("CName"));
          break;
        } else if(column.equals("ename")) {
          info.setEname(rs.getString("EName"));
          break;
        } else if(column.equals("operator")) {
          info.setEname(rs.getString("Operator"));
          break;
        } else if(column.equals("createtime")) {
          info.setCreateTime(rs.getTimestamp("CreateTime"));
          break;
        } else if(column.equals("lastmodifytime")) {
          info.setLastModifyTime(rs.getTimestamp("LastModifyTime"));
          break;
        } else if(column.equals("digest")) {
          info.setDigest(rs.getString("Digest"));
          break;
        } else {
          break;
        }
      }
    } catch (Exception ex) {
      info = null;
    }
    return info;
  }
}