java 将本地图片或者XML文件存到oracle BLOB字段数据库中

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/tongzuo244/article/details/82013692

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import oracle.sql.BLOB;

public class test {
    private static final String DB_URL = "jdbc:oracle:thin:@10.193.40.189:1521:orcl";

    private static final String DB_USER = "system";

    private static final String DB_PASSWORD = "hik12345";

    private static Connection conn = null;

    public static void main(String[] args) throws Exception {
            // insert into blob
            Connection conn = getConnection();

            //存图片
          /*  PreparedStatement ps = conn
                    .prepareStatement("INSERT INTO T_HUMAN_PICTURE (photoid,photo) values ('1',?)");

            String inFile = "D:/test.jpg";

            //设定的是blob类型
            ps.setBlob(1, file2Blob(inFile));
            ps.executeUpdate();*/
            
            //存xml文件
            PreparedStatement ps = conn
                    .prepareStatement("INSERT INTO T_HUMAN_XML (cid,cpxml) values ('1',?)");

            String inFile = "D:/test.xml";

            //设定的是blob类型    
           // ps.setBlob(1, file2Blob(inFile));
            File file = new File(inFile); 
            ps.setBytes(1, File2byte(file));
            ps.executeUpdate();
            
    }
    
    public static byte[] File2byte(File file)
    {
     byte[] buffer = null;
     try
     {
      
      FileInputStream fis = new FileInputStream(file);
      ByteArrayOutputStream bos = new ByteArrayOutputStream();
      byte[] b = new byte[1024];
      int n;
      while ((n = fis.read(b)) != -1)
      {
       bos.write(b, 0, n);
      }
      fis.close();
      bos.close();
      buffer = bos.toByteArray();
     }
     catch (FileNotFoundException e)
     {
      e.printStackTrace();
     }
     catch (IOException e)
     {
      e.printStackTrace();
     }
     return buffer;
    }

    /**
     * upload file
     * 通过二进制的方式来上传文件
     */
    public static void testFile2byte(){
        try {
            // insert into blob
            Connection conn = getConnection();

            PreparedStatement ps = conn
                    .prepareStatement("INSERT INTO WORD_FILE (GYO_NUM,WORD_KB,WORD_FILENAME,BIKO,USR_ID,YMDT,WORD_FILE) values (4,'KYK002','20171114test.doc','備考','VENAS',TO_DATE('17-11-14','RR-MM-DD'),?)");

            String inFile = "C:/Users/zhangrw/Desktop/test/2nANQz3wsFN8rkrTZa5P8xQY8PRBhyHw.jpg";

            byte [] result = file2Byte(inFile);
            //设定的是自己码文件
            ps.setBytes(1, result);
            ps.executeUpdate();
        } catch (SQLException e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        }

    }
    /**
     * 将文件转化为Blob的对象
     * create file 2 blob
     * @param inFile
     * @return
     */
    public static Blob file2Blob(String inFile){

        try {

            byte [] result = file2Byte(inFile);
            //creat a new blob
            BLOB blob = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION);

            //set start is 1
            //这个setBytes 是指定起点,然后设定字节
            blob.setBytes(1, result);

            //pub byte 这个方法是添加byte
//          blob.putBytes(1, result);

            return blob;
        } catch (Exception e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 将文件转化为二进制的数据
     * create file 2 byte
     * @param inFile
     * @return
     */
    public static byte[] file2Byte(String inFile){
        InputStream in = null;
        try {
            in = new FileInputStream(new File(inFile));

            int len = in.available();
            byte [] result = new byte[len];
            in.read(result);

            return result;
        } catch (Exception e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        }
        return null;

    }

    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");

            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
        } catch (Exception e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {

        try {
            if (conn == null) {
                Class.forName("oracle.jdbc.driver.OracleDriver");

                conn = DriverManager
                        .getConnection(DB_URL, DB_USER, DB_PASSWORD);
            }
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }
}
 

展开阅读全文

【求助】Oracle 数据库blob字段问题

04-08

winform做照片存储到Oracle中时遇到更新数据卡死的状况,如果不停止会直接报连接超时的错误,rn如果强制停止可以看到该字段有值。但是该条数据一直被占用锁死。rnrn图片大小都不大 ,只有2k-4k左右。且已经转换为byte型。rnrnrnOracleParameter[] parameters = new OracleParameter[2];rn parameters[0] = new OracleParameter("p_customersn", OracleDbType.Varchar2);rn parameters[0].Value = customerSn;rn parameters[1] = new OracleParameter("p_photo", OracleDbType.Blob, photo.Length);rn parameters[1].Value = photo;rn OracleHelper.ExecuteNonQuery(Program.connectionString, CommandType.StoredProcedure, "update_photp", parameters);rnrnrnsql试过两种写法:rn1、这种写法可以存进去,但是一直占用,无法停止。rnPROCEDURE update_photp (p_customersn IN VARCHAR2, p_photo IN BLOB)rn ISrn BEGINrn--取出blob对象rn UPDATE spe_customerrn SET photo = p_photorn WHERE customer_sn = p_customersn;rn END;rn2、这种写法一个字也存不进去rnPROCEDURE update_photp (p_customersn IN VARCHAR2, p_photo IN BLOB)rn ISrn lobloc BLOB;rn query_str VARCHAR2 (1000);rn BEGINrn--取出blob对象rn UPDATE spe_customerrn SET photo = EMPTY_BLOB ()rn WHERE customer_sn = p_customersn;rnrn query_str :=rn 'select photo from spe_customer where customer_sn= :id for update ';rnrn EXECUTE IMMEDIATE query_strrn INTO loblocrn USING p_customersn;rnrn --更新rn DBMS_LOB.WRITE (lobloc, UTL_RAW.LENGTH (p_photo), 1, p_photo);rn --COMMIT;rn END;rnrn请问大家遇到过这种现象吗?rnrn是什么原因导致的?是写法问题,还是数据库有什么问题?rnrn谢谢了 很紧急!~ 非常感谢~~~ 论坛

Oracle中写BLOB字段

12-07

create table baseinforn(rnID number(7),rnXM varchar2(30)rnZP BLOB);rnrncreate table detailinforn(rnid number(7),rnCSRQ date);rnrncreate table TEMPLATEINFOrn(rnid number(7),rnTEMPLATE blob);rnrn写表语句如下:rnCoInitialize(NULL);rntryrnrn m_pConnection.CreateInstance(__uuidof(Connection));rn m_pConnection->Provider=_T("OraOLEDB.Oracle"); rn m_pConnection->Open(_T("YCISMUST"),_T("YCISMUST"),_T("YCISMUST"),adConnectUnspecified);rn rn _RecordsetPtr pRsBaseInfo;rn pRsBaseInfo.CreateInstance(__uuidof(Recordset)); rn pRsBaseInfo->Open( _T("BASEINFO"),rn _variant_t((IDispatch*)m_pConnection), rn adOpenForwardOnly,rn adLockOptimistic,rn adCmdTable);rn rn _RecordsetPtr pRsDetailInfo;rn pRsDetailInfo.CreateInstance(__uuidof(Recordset));rn pRsDetailInfo->Open(_T("DETAILINFO"),rn _variant_t((IDispatch*)m_pConnection),rn adOpenForwardOnly,rn adLockOptimistic,rn adCmdTable);rn rn _RecordsetPtr pRsTemplateInfo;rn pRsTemplateInfo.CreateInstance(__uuidof(Recordset));rn pRsTemplateInfo->Open(_T("TEMPLATEINFO"),rn _variant_t((IDispatch*)m_pConnection),rn adOpenForwardOnly,rn adLockOptimistic,rn adCmdTable);rn rn CFile File;rn CFileException ex;rn rn if (File.Open(m_strFileName,CFile::modeRead,&ex)==TRUE)rn rn rn int nFileLength=0;rn _variant_t vtZP;rn vtZP.vt=VT_UI1|VT_ARRAY;rn SAFEARRAYBOUND Bound[]=File.GetLength(),0;rn vtZP.parray=::SafeArrayCreate(vtZP.vt&~VT_ARRAY,1,Bound);rn PBYTE pTemp=NULL;rn ::SafeArrayAccessData(vtZP.parray,(void**)&pTemp);rn File.Read(pTemp,Bound[0].cElements-Bound[0].lLbound);rn ::SafeArrayUnaccessData(vtZP.parray);rn File.Close();rnrn for (long i=0;i<100;++i)rn rn m_pConnection->BeginTrans();rn rn pRsBaseInfo->AddNew();rn pRsBaseInfo->Fields->Item[_T("ID")]->Value=i;rn pRsBaseInfo->Fields->Item[_T("XM")]->Value=L"赵文忠";rn rn if (pRsBaseInfo->Fields->Item[_T("ZP")]->Attributes==adFldLong) //这样写对不对?rn pRsBaseInfo->Fields->Item[_T("ZP")]->AppendChunk(vtZP); rn elsern pRsBaseInfo->Fields->Item[_T("ZP")]->Value=vtZP;rn //不知上面的写法对不对?我直接用AppendChunk时,rn //在导入第二条数据时就报错(在Debug状态下)rn //其实上面的if语句只执行else语句。rn //但有不少文档说对于BLOB字段,应该用AppendChunck,而不是直接赋值rn pRsBaseInfo->Update();rn rn pRsDetailInfo->AddNew();rn pRsDetailInfo->Fields->Item[_T("ID")]->Value=i;rn rn pRsDetailInfo->Update();rn rn pRsTemplateInfo->AddNew();rn pRsTemplateInfo->Fields->Item[_T("ID")]->Value=i;rn pRsTemplateInfo->Update();rn rn m_pConnection->CommitTrans();rn rn rn ::SafeArrayDestroy(vtZP.parray);rn vtZP.vt=VT_EMPTY;rn rn pRsBaseInfo->Close();rn pRsBaseInfo.Release();rn rn pRsDetailInfo->Close();rn pRsDetailInfo.Release();rn rn pRsTemplateInfo->Close();rn pRsTemplateInfo.Release();rn rn m_pConnection->Close();rn m_pConnection.Release();rnrncatch(_com_error &e)rnrn m_pConnection->RollbackTrans();rn m_pConnection->Close();rn MessageBox(e.Description(),_T("数据库连接出错:") );rnrnCoUninitialize(); 论坛

ORACLEBLOB字段插入

10-23

以下是我的测试程序.rnrnpackage kasicheck;rnrn/**rn * Title: rn * Description: rn * Copyright: Copyright (c) 2003rn * Company: rn * @author unascribedrn * @version 1.0rn */rnrnimport oracle.jdbc.driver.*;rnimport java.sql.*;rnimport java.io.*;rnrnpublic class database rnrn public database() rnrnrn rnrn public static void main(String[] args)rnrn String URL = "jdbc:oracle:thin:@cnc-iki:1521:BMS";rn String USERID = "gyomu";rn String PASSWORD = "gyomu";rn try rn Class.forName("oracle.jdbc.driver.OracleDriver");rn Connection conn = DriverManager.getConnection(URL, USERID, PASSWORD);rn Statement stmt = conn.createStatement();rn String sql = "insert into DBQ_TABLE values(" +rn "'aa'," +rn "'1'," +rn "'1'," +rn "'1'," +rn "'1'," +rn "'1'," +rn "'1'," +rn "'1'," +rn "'1'," +rn "EMPTY_BLOB()"+rn ")";rn System.out.println(sql);rn stmt.executeUpdate(sql);rn conn.commit();rn ResultSet rs = stmt.executeQuery("SELECT MSGDATA FROM DBQ_TABLE WHERE TRNID='aa' for update nowait");rn System.out.println("hahahahahah");rn while(rs.next())rn oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("MSGDATA");rn BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());rn String blobfile = "C:/Documents and Settings/cnc/My Documents/My Pictures/climing.jpg";rn BufferedInputStream in = new BufferedInputStream(new FileInputStream(blobfile));rn int c;rn while ((c=in.read())!=-1) rn out.write(c);rn rn in.close();rn out.close();rn conn.commit();rn conn.commit();rn rn rn catch (Exception ex) rn ex.printStackTrace();rn System.out.println("error");rn rnrn System.out.println("success");rnrn rnrnrn每次执行到 ResultSet rs = stmt.executeQuery("SELECT MSGDATA FROM DBQ_TABLE WHERE TRNID='aa' for update nowait");rn就告诉我FETCH顺序错误.rnrn哪位大虾能告诉我原因么?rnrn 论坛

没有更多推荐了,返回首页