/**
* 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 수
}
/** 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;
}
/** 针对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;
}
}
/**
* 处理增加
*
* @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();
}
}
}
/**
* 处理修改
*
* @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();
}
}
}
/**
* 设置进入修改页面的初始值
*
* @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);
}
}