public long getNextValue(String seqfunctionname, String sequence, Connection con, String dbname)
throws SQLException {
long curValue = 0;
PreparedDBUtil dbutil = new PreparedDBUtil();
try {
// if()
String sql = "select " + sequence + ".nextval from dual";
dbutil.preparedSelect(dbname, sql);
dbutil.executePrepared(con);
if (dbutil.size() <= 0) {
// System.out.println("select " + this.generator + ".nextval from dual");
throw new SQLException(
"[select "
+ sequence
+ ".nextval from dual] from ["
+ dbname
+ "] failed:retrun records is 0.");
}
curValue = dbutil.getInt(0, 0);
return curValue;
} catch (SQLException e) {
throw e;
}
}
/** clob字段的写入 */
@Test
public void testClobWrite() {
PreparedDBUtil dbUtil = new PreparedDBUtil();
try {
dbUtil.preparedInsert("insert into test(id,clobname) values(?,?)");
dbUtil.setString(1, DBUtil.getNextStringPrimaryKey("test"));
dbUtil.setClob(2, "clobvalue"); // 直接将字符串存储到clob字段中
dbUtil.executePrepared();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
}
}
/** 第一种插入blob字段的方法，通用的模式 */
@Test
public void testBlobWrite() {
PreparedDBUtil dbUtil = new PreparedDBUtil();
try {
dbUtil.preparedInsert("insert into test(id,blobname) values(?,?)");
dbUtil.setString(1, DBUtil.getNextStringPrimaryKey("test"));
dbUtil.setBlob(2, new java.io.File("d:/dominspector.rar")); // 直接将文件存储到大字段中
dbUtil.executePrepared();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
}
}
/** 大字段的读取 */
@Test
public void testBlobRead() {
PreparedDBUtil dbUtil = new PreparedDBUtil();
try {
// 查询大字段内容并且将大字段存放到文件中
dbUtil.preparedSelect("select id,blobname from test");
dbUtil.executePrepared();
for (int i = 0; i < dbUtil.size(); i++) {
dbUtil.getFile(i, "blobname", new java.io.File("e:/dominspector.rar")); // 将blob字段的值转换为文件
// Blob blob = dbUtil.getBlob(i, "blobname");//获取blob字段的值到blob变量中。
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
}
}
public void addDocCommentImpeach(CommentImpeach commentImpeach)
throws DocCommentManagerException {
PreparedDBUtil preparedDB = new PreparedDBUtil();
String sql =
"insert into td_cms_doccom_impeachinfo(comment_id,impeacher,email,reason,descriptioninfo,comtime) values(?,?,?,?,?,?)";
try {
preparedDB.preparedInsert(sql);
preparedDB.setInt(1, commentImpeach.getCommentId());
preparedDB.setString(2, commentImpeach.getImpeacher());
preparedDB.setString(3, commentImpeach.getEmail());
preparedDB.setInt(4, commentImpeach.getReason());
preparedDB.setString(5, commentImpeach.getDescription());
preparedDB.setTimestamp(6, new Timestamp(new Date().getTime()));
preparedDB.executePrepared();
} catch (SQLException e) {
e.printStackTrace();
throw new DocCommentManagerException("举报信息插入数据库失败！" + e.getMessage());
} finally {
preparedDB.resetPrepare();
}
}
/** clob字段的读取 */
@Test
public void testClobRead() {
PreparedDBUtil dbUtil = new PreparedDBUtil();
try {
// 查询大字段内容并且将大字段存放到文件中
dbUtil.preparedSelect("select id,clobname from test");
dbUtil.executePrepared();
for (int i = 0; i < dbUtil.size(); i++) {
dbUtil.getFile(i, "clobname", new java.io.File("d:/route" + i + ".txt")); // 读取clob字段到文件中
// String clobvalue = dbUtil.getString(i, "clobname");//获取clob字段到字符串变量中
// Clob clob = dbUtil.getClob(i, "clobname");//获取clob字段值到clob类型变量中
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
dbUtil = null;
}
}
public static void main(String[] args) {
String select = "select user_id,USER_PASSWORD from td_sm_user";
String update = "update td_sm_user set USER_PASSWORD=? where user_id=?";
DBUtil dbUtil = new DBUtil();
// TransactionManager tm = new TransactionManager ();
try {
// tm
dbUtil.executeSelect(select);
for (int i = 0; i < dbUtil.size(); i++) {
String password = dbUtil.getString(i, "USER_PASSWORD");
String userid = dbUtil.getString(i, "user_id");
PreparedDBUtil updatedbUtil = new PreparedDBUtil();
// String newPassword =
updatedbUtil.preparedUpdate(update);
// updatedbUtil.setString(1,)
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/** 针对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;
}
}
private boolean exist(Connection con, long curValue) throws SQLException {
String select =
"select count(1) from " + this.tableName + " where " + this.primaryKeyName + "=?";
PreparedDBUtil dbUtil = new PreparedDBUtil();
try {
// String update = "update tableinfo set table_id_value=" + this.curValue +" where
// table_name='"+ tableName.toLowerCase() + "' and table_id_value <" + this.curValue ;
dbUtil.preparedSelect(this.dbname, select);
if (this.metaType.equals("int")
|| this.metaType.equals("java.lang.Integer")
|| this.metaType.equals("java.lang.integer")
|| this.metaType.equalsIgnoreCase("integer")) {
dbUtil.setInt(1, (int) curValue);
} else if (this.metaType.equals("java.lang.Long")
|| this.metaType.equals("java.lang.long")
|| this.metaType.equalsIgnoreCase("long")) {
dbUtil.setLong(1, curValue);
} else if (this.metaType.equals("java.lang.String")
|| this.metaType.equalsIgnoreCase("string")) {
dbUtil.setString(1, this.prefix + curValue + "");
} else {
dbUtil.setString(1, this.prefix + curValue + "");
}
dbUtil.executePrepared(con);
if (dbUtil.getInt(0, 0) > 0) return true;
} catch (SQLException e1) {
e1.printStackTrace();
throw e1;
} catch (Exception e1) {
e1.printStackTrace();
throw new SQLException(e1.getMessage());
} finally {
dbUtil.resetPrepare();
}
return false;
}
public void addOneComment(DocComment docComment) throws DocCommentManagerException {
// TODO Auto-generated method stub
PreparedDBUtil preparedDB = new PreparedDBUtil();
DBUtil dbUitl = new DBUtil();
String sql =
"insert into td_cms_doc_comment(comment_id, doc_id,doc_comment,user_name,comtime,user_ip,src_comment_id,status) values(?, ?,?,?,?,?,?,?)";
try {
int id =
(Integer)
dbUitl.executeSelectForObject(executor.getSql("getCommentNextId"), Integer.class);
preparedDB.preparedInsert(sql);
preparedDB.setInt(1, id);
preparedDB.setInt(2, docComment.getDocId());
preparedDB.setClob(3, docComment.getDocComment(), "doc_comment");
preparedDB.setString(4, docComment.getUserName());
preparedDB.setTimestamp(5, new Timestamp(new Date().getTime()));
preparedDB.setString(6, docComment.getUserIP());
if (docComment.getSrcCommentId() == 0) preparedDB.setNull(7, Types.NUMERIC);
else preparedDB.setInt(7, docComment.getSrcCommentId());
preparedDB.setInt(8, docComment.getStatus());
preparedDB.executePrepared();
} catch (SQLException e) {
e.printStackTrace();
throw new DocCommentManagerException("新评论插入失败！" + e.getMessage());
} finally {
preparedDB.resetPrepare();
}
}