Oracle中写入BLOB

通过java往oracle中blob字段写入数据

(2007-10-19 15:53:06)

这两天忙着修改浙大的学院服务系统中关于照片的上传与读取（直接从数据库里），以前都是直接文件读取出来。

ZP：blob

在写入的过程中，出现如下错误：

java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested

往oracle里面类型为blob写入时，必须先插入一个empty_blob，实行update……

具体java里面写入blob的代码如下：

public class applyPhotoBLOB {

final static String sDBDriver = \"oracle.jdbc.driver.OracleDriver\";

public static void main(String[] args) {

Connection connSDC = null;

Connection conn = null;

String sConnStr = \"jdbc:oracle:thin:@127.0.0.1:1521:sledu\";

String sConnStrSDC = \"jdbc:oracle:thin:@10.10.8.12:1521:rac2\";

String sDBUid = \"test\";

String sDBPwd = \"test\";

String sDBUidSDC = \"sdcmanager\";

String sDBPwdSdc = \"sdcmanager_888\";

try

{

applyPhotoBLOB apply = new applyPhotoBLOB();

connSDC = apply.getConn(sConnStrSDC,sDBUidSDC,sDBPwdSdc);

if(connSDC!=null)

{

apply.testBOLB(connSDC);

}

System.out.println(\"处理完成！\");

}

catch(Exception e)

{

System.out.println(e.getMessage());

}

finally

{

try

{

if(conn!=null) conn.close();

if(connSDC!=null) connSDC.close();

}

catch(Exception e)

{

System.out.println(e.getMessage());

}

}

}

public void testBOLB(Connection conn) throws Exception

{

String strSQL = \"Insert Into Values('3071801040','1',empty_blob())\";

updateTable1(strSQL,conn);

conn.setAutoCommit(false);

BKS_XSZPXX(XH,ZPLXM,ZP)

strSQL = \"Select ZP from BKS_XSZPXX where XH='3071801040' For Update\";

Statement stmt = null;

ResultSet rs = null;

stmt = conn.createStatement();

rs = stmt.executeQuery(strSQL);

rs.next();

BLOB blob = (BLOB) rs.getBlob(\"ZP\");

OutputStream os = blob.getBinaryOutputStream();// 建立输出流

BufferedOutputStream output = new BufferedOutputStream(os);

BufferedInputStream input = new BufferedInputStream(new

File(\"F:/3071801040.jpg\").toURL().openStream());

byte[] buff = new byte[2048000]; //用做文件写入的缓冲

int bytesRead;

while(-1 != (bytesRead = input.read(buff, 0, buff.length)))

{

output.write(buff, 0, bytesRead);

//System.out.println(bytesRead);

}

output.close();

input.close();

rs.close();

conn.commit();

conn.setAutoCommit(true);

stmt.close();

}

private int updateTable1(String strSQL,Connection conn) throws Exception

{

PreparedStatement stmt = null;

int result = 0;

try

{

stmt = conn.prepareStatement(strSQL);

result = stmt.executeUpdate();

}

catch(Exception e)

{

throw new Exception(e.getMessage());

}

finally

{

stmt.close();

}

return result ;

}

public Connection getConn(String StrConn,String uid,String pwd) throws Exception

{

Connection conn = null;

try

{

Class.forName(sDBDriver);

conn = DriverManager.getConnection(StrConn,uid,pwd);

}

catch (Exception e)

{

throw new Exception(e.getMessage());

}

return conn;

}

我自己的代码:

/**

* 保存凭证格式

* @param sxStru

* @throws java.lang.Exception

* ljdh 07年01月16 此处特殊处理保存oracle 的blob数据

* 说明：写入Blob字段和写入其它类型字段的方式非常不同，因为Blob自身有一个cursor，

* 你必须使用cursor对blob进行操作，因而你在写入Blob之前，必须获得cursor才能进行写入，

* 那么如何获得Blob的cursor呢？这需要你先插入一个empty的blob，这将创建一个blob的cursor，

* 然后你再把这个 empty的blob的cursor用select查询出来，这样通过两步操作，你就获得了blob的cursor

* ，可以真正的写入blob数据了

*/

public String saveDjgs(JDjgssxStru sxStru) throws Exception {

String sNm, strSql;

Statement stmt = null;

ResultSet rs = null;

con.setAutoCommit(false);

try {

stmt = con.createStatement();

//新加的格式

if (sxStru.NM == null || sxStru.NM.equals(\"\")) {

sNm = getNewNm(stmt, rs);

if (isBill) {

strSql = \"insert into ZJDJGS\" +

\"

(F_XTBH,F_LBBH,F_GSNM,F_QX,F_NAME,F_ZDH,F_ZDL,F_KSH,F_JSH,F_KSL,F_JSL,F_LINE,F_DJGS,F_DWBH)\" +

\" select '\" + sysNum + \"','\" + sysLbbh + \"','\" + sNm + \"','N','\" +

sxStru.GsName + \"',\" + sxStru.MaxRow + \ + sxStru.MaxCol + \ +

sxStru.StartRow + \ + sxStru.EndRow + \ + sxStru.StartCol +

\ + sxStru.EndCol + \ + sxStru.BLine + \"',empty_blob() , F_DWBH \" +

\" from ZJDWZD \" +

\" where F_DWBH like '\"+sDwbh+\"%' and \" +

\" not Exists ( select 1 from ZJDJGS where ZJDWZD.F_DWBH = ZJDJGS.F_DWBH and F_GSNM = '\"+sNm+\"' and \" +

\" F_LBBH ='\"+sysLbbh+\"' )\";

}

else {

strSql = \" insert into

LSZWPZGS(F_GSNM,F_QX,F_NAME,F_ZDH,F_ZDL,F_KSH,F_JSH,F_KSL,F_JSL,F_LINE,F_DWBH)\" +

\" select '\" + sNm + \"','N','\" +sxStru.GsName + \"',\" + sxStru.MaxRow + \ + sxStru.MaxCol + \ +

sxStru.StartRow + \ + sxStru.EndRow + \ + sxStru.StartCol +

\ + sxStru.EndCol + \ + sxStru.BLine + \"' , F_DWBH \" +

\" from ZJDWZD \" +

\" where F_DWBH like '\"+sDwbh+\"%' and \" +

\" not Exists ( select 1 from LSZWPZGS where ZJDWZD.F_DWBH = LSZWPZGS.F_DWBH and F_GSNM = '\"+sNm+\"'and \" +

\" )\";

}

}

//修改的格式

else {

sNm = sxStru.NM;

if (isBill) {

strSql = \"update ZJDJGS set F_NAME = '\" + sxStru.GsName + \"',F_ZDH =\" +

sxStru.MaxRow + \ + sxStru.MaxCol + \ +

sxStru.StartRow + \ + sxStru.EndRow + \ +

sxStru.StartCol + \ + sxStru.EndCol + \ +

sxStru.BLine + \"' ,F_DWBH = '\"+sDwbh+\"'\" + \" where F_XTBH = '\" + sysNum +

\"' AND F_LBBH = '\" + sysLbbh + \"' and F_GSNM = '\" + sxStru.NM +

\"' AND F_DWBH = '\"+sDwbh+\"'\";

}

else {

strSql = \"update LSZWPZGS set F_NAME = '\" + sxStru.GsName +

\"',F_ZDH =\" + sxStru.MaxRow + \ + sxStru.MaxCol +

\ + sxStru.StartRow + \ + sxStru.EndRow +

\ + sxStru.StartCol + \ + sxStru.EndCol +

\ + sxStru.BLine + \"',F_DWBH = '\"+sDwbh+\"'\" +

\" where F_GSNM = '\" +sxStru.NM + \"' AND F_DWBH = '\"+sDwbh+\"' \";

}

}

stmt.executeUpdate(StringFunction.GB2Uni(strSql));

// modify by raomw , do 20100323

strSql =\" SELECT F_DJGS FROM ZJDJGS WHERE F_XTBH = '\" + sysNum +

\"' AND F_LBBH = '\" + sysLbbh + \"' and F_GSNM = '\" + sNm +

\"' AND F_DWBH like '\" + sDwbh +\"%' FOR UPDATE \"; // 使用“FOR UPDATE”得到表的写锁

strSql = JConvertSql.convertSql(stmt, strSql);

PreparedStatement pstmt = con.prepareStatement(strSql);

rs = pstmt.executeQuery(strSql);

if (rs.next()) {

BLOB pBlob = (BLOB) rs.getBlob(1); //获得BLOB

OutputStream out = pBlob.getBinaryOutputStream();

byte[] pBytes = sxStru.FormatData;

out.write(pBytes);

out.close();

}

pstmt.executeUpdate();

con.commit();

con.setAutoCommit(true);

//

stmt.close();

pstmt.close();

}

catch (Exception e) {

con.rollback();

con.setAutoCommit(true);

throw e;

}

return sNm;

}