增加修改:
public void modifyPiMemo(TblpiMemo tblpiMemo) {
Connection conn = null;
try {
Statement sta = null;
ResultSet rs = null;
PreparedStatement ps = null;
Context ctx = Context.getInstance();
Person per = ctx.getCurrentPerson();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String date = df.format(new Date());
StringBuffer sql = new StringBuffer(
" merge into TBL_OS_PIMEMO tbl using (select count(*) cu from TBL_OS_PIMEMO where ITEMID='"
+ tblpiMemo.getItemId()
+ "' and USER_ID='"+per.getUuid()+"') n on (n.cu>1) "
+ "when matched then update set MEMO=EMPTY_CLOB(),lastuptime='"
+ date
+ "' "
+ " when not matched then insert (id,ITEMID,MEMO,lastuptime,USER_ID,USER_NAME) values(sys_guid(),'"
+ tblpiMemo.getItemId()
+ "',EMPTY_CLOB(),'"
+ date
+ "','"+per.getUuid()+"','"+per.getFullName()+"')");
// insert into TBL_OS_PIMEMO (id,ITEMID,MEMO) select
// sys_guid(),'"+tblpiMemo.getItemId()+"',? from dual
conn = DBConnectionProvider.getConnection(Constants.JNDI_OSEMS);
//conn.setAutoCommit(false);
sta = conn.createStatement();
sta.executeUpdate(sql.toString());
conn.commit();
String getSql = " select MEMO from TBL_OS_PIMEMO where ITEMID='"
+ tblpiMemo.getItemId() + "' for update ";
rs = sta.executeQuery(getSql);
if (rs.next()) {
// 获取clob对象，此处的clob是oracle.sql.Clob
CLOB clob = (CLOB) rs.getClob(1);
clob.putString(1, tblpiMemo.getMemo());
// 执行更新操作
getSql = " update TBL_OS_PIMEMO set MEMO=? where ITEMID='"
+ tblpiMemo.getItemId() + "'";
ps = conn.prepareStatement(getSql);
// 给clob字段赋值
ps.setClob(1, clob);
ps.executeQuery();
}
conn.commit();
rs.close();
ps.close();
sta.close();
} catch (Exception dbe) {
dbe.printStackTrace();
} finally {
try {
conn.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
删除：
public void delMsg(TblPiIdea piIdea) {
Connection conn = null;
try {
Statement sta = null;
String sql = " delete from TBL_OS_PIIDEA where id='"+piIdea.getId()+"'";
conn = DBConnectionProvider.getConnection(Constants.JNDI_OSEMS);
sta = conn.createStatement();
sta.executeUpdate(sql);
sta.close();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
查询：
public RecordSet findIdeaById(TblPiIdea piIdea) {
Connection conn = null;
RecordSet rs = null;
try {
String sql = " select * from TBL_OS_PIIDEA where id='"+piIdea.getId()+"'";
conn = DBConnectionProvider.getConnection(Constants.JNDI_OSEMS);
DataBaseExecutor de = DataBaseExecutor.getExecutor(conn);
rs = de.find(sql);
if (rs != null && rs.size() > 0) {
return rs;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (Exception e) {
// TODO: handle exception
}
}
return null;
}