Oracle blob 的整理

布满荆棘的人生 2022-01-17 01:43 217阅读 0赞

Oracle 数据库 xt\_inform （title ，text，……）title 主键 varchar ， text blob  
添加数据：（insert into）  
public boolean addTongGao(TongGao tg) \{  
boolean addflag = false;  
  
String strFilelinks = this.getStrFilelinks(tg);  
java.sql.Date issuedate = this.getSQLDate(tg.getIssuedate());  
java.sql.Date enddate = this.getSQLDate(tg.getEnddate());  
String sql = "";  
  
Connection conn = db.getConn();  
try \{  
conn.setAutoCommit(false);  
sql = "Insert Into xt\_inform(title, text, issuedate, enddate, category, department, publisher, top, filelinks) Values(?,empty\_blob(),?,?,?,?,?,?,?)";  
 Object\[\] params = \{tg.getTitle(),  
issuedate,  
enddate,  
tg.getCategory(),  
tg.getDepartment(),  
tg.getPublisher(),  
tg.getTop(),  
strFilelinks\};  
PreparedStatement pstmt = db.getPstmt(conn, sql, params);  
int count = db.getCount(pstmt);  
pstmt.close();  
  
sql = "select text from xt\_inform where title = ? for update";  
 Object\[\] params1 = \{tg.getTitle()\};  
pstmt = db.getPstmt(conn, sql, params1);  
ResultSet rs = db.getResultSet(pstmt);  
if(rs != null) \{  
rs.next();  
 oracle.sql.BLOB blob = (BLOB) rs.getBlob("text");  
  OutputStream out = blob.getBinaryOutputStream();  
 out.write(tg.getText().getBytes(), 0, tg.getText().length());  
 out.flush();  
out.close();  
\}  
conn.commit();  
conn.setAutoCommit(true);  
rs.close();  
pstmt.close();  
addflag = true;  
\} catch (SQLException e) \{  
try \{  
conn.rollback();  
\} catch (SQLException e1) \{  
e1.printStackTrace();  
\}  
e.printStackTrace();  
\} catch (IOException e) \{  
try \{  
conn.rollback();  
\} catch (SQLException e1) \{  
e1.printStackTrace();  
\}  
e.printStackTrace();  
\} finally \{  
db.close(conn);  
\}  
return addflag;  
\}  
更新（update set）  
public boolean updateTongGao(TongGao tg) \{  
boolean updateflag = false;  
  
String strFilelinks = this.getStrFilelinks(tg);  
java.sql.Date issuedate = this.getSQLDate(tg.getIssuedate());  
java.sql.Date enddate = this.getSQLDate(tg.getEnddate());  
  
Connection conn = db.getConn();  
try \{  
conn.setAutoCommit(false);  
  
 String sql = "Update xt\_inform Set title = ?, text = empty\_blob(), issuedate = ?, enddate = ?, category = ?, department = ?, publisher = ?, top = ?, filelinks = ? Where title = ?";  
 Object\[\] params = \{tg.getTitle(),  
issuedate,  
enddate,  
tg.getCategory(),  
tg.getDepartment(),  
tg.getPublisher(),  
tg.getTop(),  
strFilelinks,  
tg.getTitle()\};  
PreparedStatement pstmt = db.getPstmt(conn, sql, params);  
int count = db.getCount(pstmt);  
pstmt.close();  
  
sql = "select text from xt\_inform where title = ? for update";  
Object\[\] params1 = \{tg.getTitle()\};  
pstmt = db.getPstmt(conn, sql, params1);  
ResultSet rs = db.getResultSet(pstmt);  
if(rs != null) \{  
rs.next();  
 oracle.sql.BLOB blob = (BLOB) rs.getBlob("text");  
OutputStream out = blob.getBinaryOutputStream();  
out.write(tg.getText().getBytes(), 0, tg.getText().length());  
 out.flush();  
out.close();  
\}  
conn.commit();  
conn.setAutoCommit(true);  
rs.close();  
pstmt.close();  
updateflag = true;  
\} catch (SQLException e) \{  
try \{  
conn.rollback();  
\} catch (SQLException e1) \{  
e1.printStackTrace();  
\}  
e.printStackTrace();  
\} catch (IOException e) \{  
try \{  
conn.rollback();  
\} catch (SQLException e1) \{  
e1.printStackTrace();  
\}  
e.printStackTrace();  
\} finally \{  
db.close(conn);  
\}  
return updateflag;  
\}  
读取（select from）  
public TongGao getTongGaoByTitle(String title) \{  
TongGao tg = null;  
  
Connection conn = db.getConn();  
try \{  
conn.setAutoCommit(false);  
String sql = "Select \* From xt\_inform Where title = ? ";  
Object\[\] params = \{title\};  
PreparedStatement pstmt = db.getPstmt(conn, sql, params);  
ResultSet rs = db.getResultSet(pstmt);  
if(rs != null) \{  
rs.next();  
 oracle.sql.BLOB blob = ((OracleResultSet)rs).getBLOB("text");  
byte\[\] bytes = new byte\[(int) blob.length()\];  
InputStream in = blob.getBinaryStream();  
in.read(bytes);  
 tg = new TongGao();  
tg.setTitle(rs.getString("title"));  
 tg.setText(new String(bytes, "GBK"));//这里的编码要和数据库里的一样才可以  
 tg.setIssuedate(rs.getDate("issuedate").toString());  
tg.setEnddate(rs.getDate("enddate").toString());  
tg.setCategory(rs.getString("category"));  
tg.setDepartment(rs.getString("department"));  
tg.setPublisher(rs.getString("publisher"));  
tg.setTop(rs.getString("top"));  
tg.setStatus(rs.getString("status"));  
tg.setBz(rs.getString("bz"));  
tg.setFiles(this.getFiles(rs.getString("filelinks")));  
in.close();  
\}  
conn.commit();  
conn.setAutoCommit(true);  
rs.close();  
pstmt.close();  
\} catch (SQLException e) \{  
try \{  
conn.rollback();  
\} catch (SQLException e1) \{  
e1.printStackTrace();  
\}  
e.printStackTrace();  
\} catch (UnsupportedEncodingException e) \{  
try \{  
conn.rollback();  
\} catch (SQLException e1) \{  
e1.printStackTrace();  
\}  
e.printStackTrace();  
\} catch (IOException e) \{  
try \{  
conn.rollback();  
\} catch (SQLException e1) \{  
// TODO Auto-generated catch block  
e1.printStackTrace();  
\}  
e.printStackTrace();  
\} finally \{  
db.close(conn);  
\}  
return tg;  
\}  
  
其中包含的一些公共的方法：

public Connection getConn() \{  
Connection conn = null;  
try \{  
Class.forName("oracle.jdbc.driver.OracleDriver");  
String url = "jdbc:oracle:thin:@202.206.196.109:1521:orcl";  
String username = "\*\*\*\*\*";  
String password = "\*\*\*\*\*\*l";  
conn = DriverManager.getConnection(url, username, password);  
\} catch (ClassNotFoundException e) \{  
e.printStackTrace();  
\} catch (SQLException e) \{  
e.printStackTrace();  
\}  
return conn;  
\}  
public PreparedStatement getPstmt(Connection conn, String sql, Object\[\] params) \{  
PreparedStatement pstmt = null;  
if(sql != null && !sql.equals("")) \{  
if(params == null || params.length == 0) \{  
params = new Object\[0\];  
\}  
try \{  
pstmt = conn.prepareStatement(sql);  
for(int i=0;i<params.length;i++) \{  
pstmt.setObject((i+1), params\[i\]);  
\}  
pstmt.execute();  
\} catch (SQLException e) \{  
e.printStackTrace();  
\}  
\}  
return pstmt;  
\}  
  
public ResultSet getResultSet(PreparedStatement pstmt) throws SQLException \{  
return pstmt.getResultSet();  
\}  
  
public int getCount(PreparedStatement pstmt) throws SQLException \{  
return pstmt.getUpdateCount();  
\}  
  
public void close(Connection conn) \{  
if(conn != null) \{  
try \{  
conn.close();  
\} catch (SQLException e) \{  
e.printStackTrace();  
\}  
conn = null;  
\}  
\}  
  
有不对的地方请大家指点，这些代码在我这运行着可以，应该没有问题，希望能帮到一些人（刚弄blob的）~

转载于:https://blog.51cto.com/yin123/433444