Beispiel #1
0
  /**
   * Clob 데이타를 수행한다.
   *
   * @param sql String
   * @param data String
   * @return int
   * @throws SQLException
   */
  public int executeClob(String sql, String data) throws SQLException {
    KJFLog.sql(sql);

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    int result = 0;

    try {
      conn = this.getConnection();
      ps = conn.prepareStatement(sql);

      CLOB cb = null;

      rs = ps.executeQuery();
      ;

      if (rs.next()) {
        cb = ((OracleResultSet) rs).getCLOB(1);
      }

      long pos = 0; // CLOB 데이타가 insert되는 위치의 offset값을 나타낸다.

      if (cb == null) {
        pos = 1;
      } else {
        pos = cb.length() + 1;
      }

      // CLOB.putString이 buf에 담긴 데이타를 해당 CLOB 컬럼에 update하는
      // 작업을 하게 된다.
      cb.putString(pos, data);

    } catch (SQLException e) {
      System.out.println(e);
      throw e;

    } finally {
      if (rs != null) rs.close();
      if (ps != null) ps.close();
      this.release(conn);
    }

    return result; // Update나 Insert된  row 수
  }
Beispiel #2
0
 /** CLOB을 그냥 select해서 넣으면 안들어간다. 이거 한번 돌리고 쓰자. */
 public static List<? extends Object[]> convertClob(List<? extends Object[]> items) {
   List<Object[]> newItems = Lists.newArrayList();
   for (Object[] item : items) {
     Object[] newItem = new Object[item.length];
     for (int i = 0; i < item.length; i++) {
       Object columnValue = item[i];
       if (columnValue instanceof CLOB) {
         CLOB clob = (CLOB) columnValue;
         try {
           columnValue = IOUtils.toString(clob.getCharacterStream());
         } catch (Exception e) {
           Throwables.propagate(e);
         }
       }
       newItem[i] = columnValue;
     }
     newItems.add(newItem);
   }
   return newItems;
 }
Beispiel #3
0
  /** 针对oracle Clob字段的插入操作 */
  @Test
  public void testBigClobWrite() {
    PreparedDBUtil dbUtil = new PreparedDBUtil();
    TransactionManager tm = new TransactionManager();
    try {
      // 启动事务
      tm.begin();
      // 先插入一条记录,blob字段初始化为empty_lob
      dbUtil.preparedInsert("insert into test(id,clobname) values(?,?)");
      String id = DBUtil.getNextStringPrimaryKey("test");
      dbUtil.setString(1, id);
      dbUtil.setClob(2, CLOB.empty_lob()); // 先设置空的blob字段

      dbUtil.executePrepared();

      // 查找刚才的插入的记录,修改blob字段的值为一个文件
      dbUtil = new PreparedDBUtil();
      dbUtil.preparedSelect("select clobname from test where id = ?");
      dbUtil.setString(1, id);
      dbUtil.executePrepared();

      CLOB clob = (CLOB) dbUtil.getClob(0, "clobname");
      if (clob != null) {
        DBUtil.updateCLOB(clob, new java.io.File("d:\\route.txt"));
      }
      tm.commit();

    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      try {
        tm.rollback();
      } catch (RollbackException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
    } finally {
      tm = null;
      dbUtil = null;
    }
  }
Beispiel #4
0
 /**
  * 处理增加
  *
  * @param actionForm ActionForm 表单From
  * @param request HttpServletRequest Web请求
  * @param response HttpServletResponse Web结果
  * @throws CDealException 处理异常
  */
 public static long doAdd(
     AddForm pWebForm, HttpServletRequest request, HttpServletResponse response)
     throws CDealException, SQLException {
   long chapterid;
   Connection mconn = null;
   PreparedStatement pstmt = null;
   try {
     CUserBean loginUser =
         (CUserBean) request.getSession().getAttribute(CConstants.USER_BEAN); // 用户BEAN
     /*TPubFlfggl aPoBean = webForm2PoForm(pWebForm);  // 将页面的Form的数据拷内到实体对象
     aPoBean.setId(CTools.getNewNumID());
     aPoBean.setCreateperson(loginUser.getId());  // 用户 ID
     aPoBean.setCreatetime(CTools.getCurrentTimestamp());  // 当前时间
     CHibernateUtil.saveObject(aPoBean);  // 调用 PO 层进行增加 */
     chapterid = CTools.getNewNumID();
     mconn = CDBManager.getConn();
     mconn.setAutoCommit(false); // 将自动提交设置为否
     String sql =
         "insert into t_pub_law (id,createperson,createtime,title,classification,issuedate,orderno,spare1,spare2,spare3,spare4,businesstype,bigcontent) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
     pstmt = mconn.prepareStatement(sql);
     pstmt.setLong(1, chapterid);
     pstmt.setLong(2, loginUser.getId());
     pstmt.setTimestamp(3, CTools.getCurrentTimestamp());
     pstmt.setString(4, pWebForm.getTPubFlfggl().getTitle());
     CLog.println("4====title=" + pWebForm.getTPubFlfggl().getTitle());
     pstmt.setInt(5, pWebForm.getTPubFlfggl().getClassification());
     CLog.println("5====classification=" + pWebForm.getTPubFlfggl().getClassification());
     pstmt.setTimestamp(
         6,
         CTools.convertStringToTimestamp(
             pWebForm.getTPubFlfggl().getIssueTime_str(), "yyyy-mm-dd"));
     pstmt.setLong(7, pWebForm.getTPubFlfggl().getOrderno());
     pstmt.setString(8, pWebForm.getTPubFlfggl().getSpare1());
     pstmt.setString(9, pWebForm.getTPubFlfggl().getSpare2());
     pstmt.setString(10, pWebForm.getTPubFlfggl().getSpare3());
     pstmt.setString(11, pWebForm.getTPubFlfggl().getSpare4());
     pstmt.setInt(12, pWebForm.getTPubFlfggl().getBusinesstype());
     pstmt.setClob(13, CLOB.empty_lob()); // 构造空CLOB对象
     pstmt.executeUpdate();
     pstmt.close();
     // =======================再次从库表中读出,获得CLOB句柄
     pstmt =
         mconn.prepareStatement("select a.bigcontent from t_pub_law a where a.id = ? for update");
     pstmt.setLong(1, chapterid);
     ResultSet rs = pstmt.executeQuery();
     rs.next();
     CLOB textClob = (CLOB) rs.getClob(1);
     textClob.putString(
         1,
         pWebForm
             .getTPubFlfggl()
             .getBigContent_str()
             .replaceAll("\n", "<br>")); // 将页面的法律法规内容写入Clob
     pstmt = mconn.prepareStatement("update t_pub_law set bigcontent=? where id=?");
     pstmt.setClob(1, textClob);
     pstmt.setLong(2, chapterid);
     pstmt.executeUpdate();
     return chapterid;
   } catch (Exception e) {
     throw new CDealException("执行增加" + mModuleName + "时失败。", e);
   } finally {
     if (pstmt != null) {
       pstmt.close();
     }
     mconn.commit();
     if (mconn != null) {
       mconn.close();
     }
   }
 }
Beispiel #5
0
  /**
   * 处理修改
   *
   * @param actionForm ActionForm 表单From
   * @param request HttpServletRequest Web请求
   * @param response HttpServletResponse Web结果
   * @throws CDealException 处理异常
   */
  public static void doEdit(
      EditForm pWebForm, HttpServletRequest request, HttpServletResponse response)
      throws CDealException, SQLException {
    CUserBean loginUser =
        (CUserBean) request.getSession().getAttribute(CConstants.USER_BEAN); // 用户BEAN
    long chapterid = pWebForm.getTPubFlfggl().getId();
    Connection mconn = null;
    PreparedStatement pstmt = null;
    try {
      /*TPubFlfggl aPoBean = webForm2PoForm(pWebForm);  // 将页面的Form的数据拷内到实体对象
      aPoBean.setModperson(loginUser.getId());  // 用户 ID
      aPoBean.setModtime(CTools.getCurrentTimestamp());  // 当前时间
      CHibernateUtil.updateObject(aPoBean);  // 调用 PO 层进行修改 */
      mconn = CDBManager.getConn();
      mconn.setAutoCommit(false); // 将自动提交设置为否
      String sql =
          "update t_pub_law a set a.modperson=?,a.modtime=?,a.title=?,a.classification=?,a.businesstype=?,a.issuedate=?,a.orderno=?,a.bigcontent = ? where a.id = ? ";
      pstmt = mconn.prepareStatement(sql);
      pstmt.setLong(1, loginUser.getId());
      pstmt.setTimestamp(2, CTools.getCurrentTimestamp());
      pstmt.setString(3, pWebForm.getTPubFlfggl().getTitle());
      pstmt.setInt(4, pWebForm.getTPubFlfggl().getClassification());
      pstmt.setInt(5, pWebForm.getTPubFlfggl().getBusinesstype());
      pstmt.setTimestamp(
          6,
          CTools.convertStringToTimestamp(
              pWebForm.getTPubFlfggl().getIssueTime_str(), "yyyy-MM-dd"));
      pstmt.setLong(7, pWebForm.getTPubFlfggl().getOrderno());
      pstmt.setClob(8, CLOB.empty_lob());
      pstmt.setLong(9, chapterid);
      pstmt.executeUpdate();
      pstmt.close();

      // =======================再次从库表中读出,获得CLOB句柄
      pstmt =
          mconn.prepareStatement("select a.bigcontent from t_pub_law a where a.id = ? for update");
      pstmt.setLong(1, chapterid);
      ResultSet rs = pstmt.executeQuery();
      rs.next();
      CLOB textClob = (CLOB) rs.getClob(1);
      if (textClob == null) {
        CLog.println("340========textClob is null");
      }
      if (pWebForm.getTPubFlfggl().getBigContent_str() == null) {
        CLog.println("343========BigContent_str is null");
      }
      textClob.putString(
          1,
          pWebForm
              .getTPubFlfggl()
              .getBigContent_str()
              .replaceAll("\n", "<br>")); // 将页面的法律法规内容写入Clob
      pstmt = mconn.prepareStatement("update t_pub_law set bigcontent=? where id=?");
      pstmt.setClob(1, textClob);
      pstmt.setLong(2, chapterid);
      pstmt.executeUpdate();
    } catch (Exception e) {
      throw new CDealException("执行修改" + mModuleName + "时失败。", e);
    } finally {
      if (pstmt != null) {
        pstmt.close();
      }
      mconn.commit();
      if (mconn != null) {
        mconn.close();
      }
    }
  }
Beispiel #6
0
  /**
   * 设置进入修改页面的初始值
   *
   * @param actionForm ActionForm 表单From
   * @param request HttpServletRequest Web请求
   * @param response HttpServletResponse Web结果
   * @throws CDealException 处理异常
   */
  public static void setEditDefault(
      EditForm pWebForm, HttpServletRequest request, HttpServletResponse response)
      throws CDealException {
    try {

      // 初始化页面,初始化法律类别下拉菜单
      TreeMap classificationList = new TreeMap();
      classificationList = CCodeMap.getMapByType("法律类别");
      pWebForm.setClassificationList(classificationList);
      CLog.println("\n111111111法律类别");
      // 初始化页面,初始化业务类别下拉菜单
      TreeMap businesstypeList = new TreeMap();
      businesstypeList = CCodeMap.getMapByType("法律法规业务类别");
      pWebForm.setBusinesstypeList(businesstypeList);
      CLog.println("\n业务类别");

      // UploadForm aUploadForm = new UploadForm();
      // aUploadForm.setType("重大事项");
      // aUploadForm.setBid(pWebForm.getTPubFlfggl().getId());
      // CLog.println("\n公共管理333333");
      // com.tjsoft.system.upload.CDeal.setUploadDefault(aUploadForm, request, response);
      // CLog.println("\n公共管理55555555");
      // pWebForm.setUploadedFile(aUploadForm.getUploadedFile());
      // CLog.println("\n111111111hhhhhhhrrrr");

      Connection mConn = null;
      PreparedStatement pstmt = null;
      String content_str = "";
      try {
        mConn = CDBManager.getConn(); // 创建数据库连接
        // String mSQL = "SELECT
        // t.title,t.classification,t.businesstype,to_char(t.Issuedate,'yyyy-mm-dd'),t.orderno,t.content,spare1,spare2,spare3,spare4,to_char(t.createtime,'yyyy-mm-dd') FROM t_pub_law t WHERE t.id=?";
        String mSQL =
            "SELECT t.title,t.classification,t.businesstype,to_char(t.Issuedate,'yyyy-mm-dd'),t.orderno,t.content,spare1,spare2,spare3,spare4,to_char(t.createtime,'yyyy-mm-dd'),t.bigcontent FROM t_pub_law t WHERE t.id=?";
        pstmt = mConn.prepareStatement(mSQL);
        pstmt.setLong(1, pWebForm.getTPubFlfggl().getId()); // 主键
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
          // pWebForm.getTPubFlfggl().setId(rs.getLong(1));
          pWebForm.getTPubFlfggl().setTitle(rs.getString(1)); // 标题
          pWebForm.getTPubFlfggl().setClassification(rs.getInt(2)); // 类别
          pWebForm.getTPubFlfggl().setBusinesstype(rs.getInt(3)); // 业务类别
          pWebForm.getTPubFlfggl().setIssueTime_str(rs.getString(4)); // 颁布日期
          pWebForm.getTPubFlfggl().setOrderno(rs.getLong(5)); // 排序号
          pWebForm.getTPubFlfggl().setContent(rs.getString(6)); // 内容
          pWebForm.getTPubFlfggl().setSpare1(rs.getString(7)); // 备用字段
          pWebForm.getTPubFlfggl().setSpare2(rs.getString(8)); // 备用字段
          pWebForm.getTPubFlfggl().setSpare3(rs.getString(9)); // 备用字段
          pWebForm.getTPubFlfggl().setSpare4(rs.getString(10)); // 备用字段
          pWebForm.getTPubFlfggl().setCreateTime_str(rs.getString(11)); // 录入时间
          // pWebForm.getTPubFlfggl().setBigContent(rs.getClob(12));
          CLOB content = (CLOB) rs.getClob(12);
          if (content != null) {
            Reader is = content.getCharacterStream();
            BufferedReader br = new BufferedReader(is);
            String s = br.readLine();
            while (s != null) {
              content_str += s;
              s = br.readLine();
            }
          }
          pWebForm.getTPubFlfggl().setBigContent_str(content_str.replaceAll("<br>", "\n"));
        } else {
          throw new CDealException(
              "使用编号 " + pWebForm.getTPubFlfggl().getId() + "未能找到数据。", new Exception("查询数据失败。"));
        }
      } catch (Exception e) {
        throw e;
      }

    } catch (Exception e) {
      throw new CDealException("进入修改" + mModuleName + "时失败。", e);
    }
  }