数据库的表为
drop table filelist;
commit;
CREATE TABLE FILELIST (
FILENAME VARCHAR2(50)primary key,
FILESIZE NUMBER(20) NULL,
FILEBODY BLOB NULL,
) ;
commit;
//源程序
import java.io.*;
import java.util.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.text.*;public class dbBean {
public static void main(String args[]) throws java.io.IOException,
java.sql.SQLException { byte a[] = null;
java.io.FileInputStream fin = null;
java.io.FileOutputStream fout = null;
oracle.jdbc.OracleResultSet ors = null;
oracle.jdbc.driver.OraclePreparedStatement opst = null;
Connection conn = null;
try { java.io.File f1 = new java.io.File("c:/cookie.jpg");
java.io.File f2 = new java.io.File("c:/testout.jpg");
System.out.println(f1); fin = new java.io.FileInputStream(f1);
fout = new java.io.FileOutputStream(f2); int flength = (int) f1.length();
System.out.println("file length::" + flength);
a = new byte[flength]; int i = 0;
int itotal = 0;
//将文件读入字节数组
for (; itotal < flength; itotal = i + itotal) { i = fin.read(a, itotal, flength - itotal); }
fin.close(); System.out.println("read itotal::" + itotal);
//Oracle的 BLOB用EMPTY_BLOB()初始化
String mysql =
"insert into filelist (FileName,FileSize,FileBody) values (?,?, empty_blob())";
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.99.15:1521:oracle", "scott", "tiger");
opst = (oracle.jdbc.driver.OraclePreparedStatement) conn.prepareStatement(
mysql);
opst.setString(1, "wordtemplate");
opst.setInt(2, flength); opst.executeUpdate();
opst.clearParameters(); //插入其它数据后,定位BLOB字段
mysql = "select filebody from filelist where filename=? for update";
opst = (oracle.jdbc.driver.OraclePreparedStatement) conn.prepareStatement(
mysql);
opst.setString(1, "wordtemplate");//这里运行出错了! 不知怎改?
ors = (oracle.jdbc.OracleResultSet) opst.executeQuery();
if (ors.next()) { oracle.sql.BLOB blob = ors.getBLOB(1);
int j = blob.putBytes(1, a);
System.out.println("j:" + j); conn.commit();
ors.close();
} System.out.println("insert into ok"); byte b[] = null; //保存从BLOB读出的字节
opst.clearParameters();
mysql = "select filebody from filelist where filename=? for update";
opst = (oracle.jdbc.driver.OraclePreparedStatement) conn.prepareStatement(
mysql);
opst.setString(1, "wordtemplate");
ors = (oracle.jdbc.OracleResultSet) opst.executeQuery();
if (ors.next()) {
Blob blob2 = ors.getBlob(3); System.out.println("blob2 length:" + blob2.length());
b = blob2.getBytes(1, flength); //从BLOB取出字节流数据
System.out.println("b length::" + b.length);
conn.commit();
}
ors.close();
//将从BLOB读出的字节写入文件
fout.write(b, 0, b.length);
fout.close(); System.out.println("write itotal::" + b.length); }
catch (Exception e) {
System.out.println("errror :" + e.toString());
e.printStackTrace(); }
finally { //关闭所有数据联接 } }
}出现错误为
java.sql.SQLException: ORA-01002: 读取违反顺序
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)errror :java.sql.SQLException: ORA-01002: 读取违反顺序
at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1198) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2400) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527) at dbBean.main(dbBean.java:62)
drop table filelist;
commit;
CREATE TABLE FILELIST (
FILENAME VARCHAR2(50)primary key,
FILESIZE NUMBER(20) NULL,
FILEBODY BLOB NULL,
) ;
commit;
//源程序
import java.io.*;
import java.util.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.text.*;public class dbBean {
public static void main(String args[]) throws java.io.IOException,
java.sql.SQLException { byte a[] = null;
java.io.FileInputStream fin = null;
java.io.FileOutputStream fout = null;
oracle.jdbc.OracleResultSet ors = null;
oracle.jdbc.driver.OraclePreparedStatement opst = null;
Connection conn = null;
try { java.io.File f1 = new java.io.File("c:/cookie.jpg");
java.io.File f2 = new java.io.File("c:/testout.jpg");
System.out.println(f1); fin = new java.io.FileInputStream(f1);
fout = new java.io.FileOutputStream(f2); int flength = (int) f1.length();
System.out.println("file length::" + flength);
a = new byte[flength]; int i = 0;
int itotal = 0;
//将文件读入字节数组
for (; itotal < flength; itotal = i + itotal) { i = fin.read(a, itotal, flength - itotal); }
fin.close(); System.out.println("read itotal::" + itotal);
//Oracle的 BLOB用EMPTY_BLOB()初始化
String mysql =
"insert into filelist (FileName,FileSize,FileBody) values (?,?, empty_blob())";
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.99.15:1521:oracle", "scott", "tiger");
opst = (oracle.jdbc.driver.OraclePreparedStatement) conn.prepareStatement(
mysql);
opst.setString(1, "wordtemplate");
opst.setInt(2, flength); opst.executeUpdate();
opst.clearParameters(); //插入其它数据后,定位BLOB字段
mysql = "select filebody from filelist where filename=? for update";
opst = (oracle.jdbc.driver.OraclePreparedStatement) conn.prepareStatement(
mysql);
opst.setString(1, "wordtemplate");//这里运行出错了! 不知怎改?
ors = (oracle.jdbc.OracleResultSet) opst.executeQuery();
if (ors.next()) { oracle.sql.BLOB blob = ors.getBLOB(1);
int j = blob.putBytes(1, a);
System.out.println("j:" + j); conn.commit();
ors.close();
} System.out.println("insert into ok"); byte b[] = null; //保存从BLOB读出的字节
opst.clearParameters();
mysql = "select filebody from filelist where filename=? for update";
opst = (oracle.jdbc.driver.OraclePreparedStatement) conn.prepareStatement(
mysql);
opst.setString(1, "wordtemplate");
ors = (oracle.jdbc.OracleResultSet) opst.executeQuery();
if (ors.next()) {
Blob blob2 = ors.getBlob(3); System.out.println("blob2 length:" + blob2.length());
b = blob2.getBytes(1, flength); //从BLOB取出字节流数据
System.out.println("b length::" + b.length);
conn.commit();
}
ors.close();
//将从BLOB读出的字节写入文件
fout.write(b, 0, b.length);
fout.close(); System.out.println("write itotal::" + b.length); }
catch (Exception e) {
System.out.println("errror :" + e.toString());
e.printStackTrace(); }
finally { //关闭所有数据联接 } }
}出现错误为
java.sql.SQLException: ORA-01002: 读取违反顺序
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)errror :java.sql.SQLException: ORA-01002: 读取违反顺序
at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1198) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2400) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527) at dbBean.main(dbBean.java:62)
import java.sql.*;
public class BlobOperation
{
public static void addLob(long id, String binFile) throws SQLException
{
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;try
{
con = ConnectionFactory.getConnection(); //换成你自己取连接的方法
con.setAutoCommit(false);String sql = "INSERT INTO Blob_Tbl(id, binfile, bincontent)";
sql += " VALUES(?, ?, ?)";
ps = con.prepareStatement(sql);ps.setLong(1, id);
ps.setString(2, binFile);
ps.setBlob(3, oracle.sql.BLOB.empty_lob());ps.executeUpdate();
//DatabaseUtils.closeObject(ps);ps = con.prepareStatement("SELECT bincontent FROM Blob_Tbl WHERE id = " + id + " for update ");
rs = ps.executeQuery();if (rs.next())
{
oracle.sql.BLOB binContent = (oracle.sql.BLOB) rs.getBlob(1);/* write blob content */
OutputStream binOut = binContent.getBinaryOutputStream();
BufferedOutputStream out = new BufferedOutputStream(binOut);
BufferedInputStream in = new BufferedInputStream(new FileInputStream(binFile));
int c;
while ((c = in.read()) != -1)
{
out.write(c);
}
in.close();
out.close();
}
con.commit();
} catch (Exception e)
{
e.printStackTrace();
try
{
con.rollback();
} catch (SQLException se)
{
}
throw new SQLException(e.getMessage());
} finally
{
DatabaseUtils.closeObject(rs, ps, con);
}
}public static void fetchLob(long id, String filename) throws SQLException
{
Connection con = null;
Statement st = null;
ResultSet rs = null;try
{
con = ConnectionFactory.getConnection();String sql = "SELECT * From Blob_Tbl Where id = " + id;
st = con.createStatement();rs = st.executeQuery(sql);
while (rs.next())
{String binFile = rs.getString("binfile");
oracle.sql.BLOB binContent = (oracle.sql.BLOB) rs.getBlob("bincontent");/* read blob content */
BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(filename));
BufferedInputStream in = new BufferedInputStream(binContent.getBinaryStream());int c;
while ((c = in.read()) != -1)
{
out.write(c);
}
in.close();
out.close();
}} catch (Exception e)
{
throw new SQLException(e.getMessage());
} finally
{
DatabaseUtils.closeObject(rs, st, con);
}
}public static void main(String[] args) throws Exception
{
if (args.length == 0)
{
addLob(1, "a.jpg");
} else
{
fetchLob(1, args[0]);
}
}
}一个例子,有的地方你需要自己改下