python提取blob数据 乱码_java读取blob全身乱码

一、BLOB操作1、入库

(1)JDBC方式//通过JDBC获得数据库连接

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:testdb", "test", "test");

con.setAutoCommit(false);

Statement st=con.createStatement();//插入一个空对象empty_blob()

st.executeUpdate("insert into TESTBLOB (ID, NAME, BLOBATTR) values (1,"thename", empty_blob())");//锁定数据行进行更新，注意“for update”语句

ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1 for update");if(rs.next())

{//得到java.sql.Blob对象后强制转换为oracle.sql.BLOB

oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");

OutputStream outStream=blob.getBinaryOutputStream();//data是传入的byte数组，定义：byte[] data

outStream.write(data, 0, data.length);

}

outStream.flush();

outStream.close();

con.commit();

con.close();

(2)JNDI方式//通过JNDI获得数据库连接

Context context = newInitialContext();

ds= (DataSource) context.lookup("ORA_JNDI");

Connection con=ds.getConnection();

con.setAutoCommit(false);

Statement st=con.createStatement();//插入一个空对象empty_blob()

st.executeUpdate("insert into TESTBLOB (ID, NAME, BLOBATTR) values (1,"thename", empty_blob())");//锁定数据行进行更新，注意“for update”语句

ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1 for update");if(rs.next())

{//得到java.sql.Blob对象后强制转换为weblogic.jdbc.vendor.oracle.OracleThinBlob(不同的App Server对应的可能会不同)

weblogic.jdbc.vendor.oracle.OracleThinBlob blob = (weblogic.jdbc.vendor.oracle.OracleThinBlob) rs.getBlob("BLOBATTR");

OutputStream outStream=blob.getBinaryOutputStream();//data是传入的byte数组，定义：byte[] data

outStream.write(data, 0, data.length);

}

outStream.flush();

outStream.close();

con.commit();

con.close();2、出库//获得数据库连接

Connection con =ConnectionFactory.getConnection();

con.setAutoCommit(false);

Statement st=con.createStatement();//不需要“for update”

ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1");if(rs.next())

{

java.sql.Blob blob= rs.getBlob("BLOBATTR");

InputStream inStream=blob.getBinaryStream();//data是读出并需要返回的数据，类型是byte[]

data = new byte[input.available()];

inStream.read(data);

inStream.close();

conn = this.getConnection();

conn.setAutoCommit(false);

java.sql.Statement st=conn.createStatement();

rs=st.executeQuery(sql);

BLOB inblob= null;if(rs.next()) {

inblob= (BLOB) rs.getBlob("BLOBATTR");

}

data=inblob.getBytes(1,(int)inblob.length());//这个就是数据

}

inStream.close();

con.commit();

con.close();

二、CLOB操作1、入库

(1)JDBC方式//通过JDBC获得数据库连接

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:testdb", "test", "test");

con.setAutoCommit(false);

Statement st=con.createStatement();//插入一个空对象empty_clob()

st.executeUpdate("insert into TESTCLOB (ID, NAME, CLOBATTR) values (1,"thename", empty_clob())");//锁定数据行进行更新，注意“for update”语句

ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1 for update");if(rs.next())

{//得到java.sql.Clob对象后强制转换为oracle.sql.CLOB

oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBATTR");

Writer outStream=clob.getCharacterOutputStream();//data是传入的字符串，定义：String data

char[] c =data.toCharArray();

outStream.write(c,0, c.length);

}

outStream.flush();

outStream.close();

con.commit();

con.close();

(2)JNDI方式//通过JNDI获得数据库连接

Context context = newInitialContext();

ds= (DataSource) context.lookup("ORA_JNDI");

Connection con=ds.getConnection();

con.setAutoCommit(false);

Statement st=con.createStatement();//插入一个空对象empty_clob()

st.executeUpdate("insert into TESTCLOB (ID, NAME, CLOBATTR) values (1,"thename", empty_clob())");//锁定数据行进行更新，注意“for update”语句

ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1 for update");if(rs.next())

{//得到java.sql.Clob对象后强制转换为weblogic.jdbc.vendor.oracle.OracleThinClob(不同的App Server对应的可能会不同)

weblogic.jdbc.vendor.oracle.OracleThinClob clob = (weblogic.jdbc.vendor.oracle.OracleThinClob) rs.getClob("CLOBATTR");

Writer outStream=clob.getCharacterOutputStream();//data是传入的字符串，定义：String data

char[] c =data.toCharArray();

outStream.write(c,0, c.length);

}

outStream.flush();

outStream.close();

con.commit();

con.close();2、出库//获得数据库连接

Connection con =ConnectionFactory.getConnection();

con.setAutoCommit(false);

Statement st=con.createStatement();//不需要“for update”

ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1");if(rs.next())

{

java.sql.Clob clob= rs.getClob("CLOBATTR");

Reader inStream=clob.getCharacterStream();char[] c = new char[(int) clob.length()];

inStream.read(c);//data是读出并需要返回的数据，类型是String

data = newString(c);

inStream.close();

}

inStream.close();

con.commit();

con.close();

需要注意的地方：1、java.sql.Blob、oracle.sql.BLOB、weblogic.jdbc.vendor.oracle.OracleThinBlob几种类型的区别2、java.sql.Clob、oracle.sql.CLOB、weblogic.jdbc.vendor.oracle.OracleThinClob几种类型的区别

公司项目中的用法(博客)：

入库：先插一个oracle.sql.CLOB.empty_lob()进去，然后

String updateBaseSourceSql= "select content from mb_baseSource where id = ? for update";

conn.setAutoCommit(false);

ps=conn.prepareStatement(updateBaseSourceSql);

ps.setLong(1, result);

ResultSet rs=ps.executeQuery();

oracle.sql.CLOB clob= null;if(rs.next()) {

clob= (oracle.sql.CLOB) rs.getClob(1);

}

Writer wr=clob.getCharacterOutputStream();

wr.write(baseSource[4]);

wr.flush();

wr.close();

rs.close();

ps.close();

conn.commit();

出库：

findBaseSourceSql= "select content from mb_baseSource where id = ?";

ps=conn.prepareStatement(findBaseSourceSql);

ps.setLong(1, sourceID);

rs=ps.executeQuery();if(rs.next()) {

CLOB clob= (oracle.sql.CLOB) rs.getClob(1);if (clob != null) {

Readeris =clob.getCharacterStream();

BufferedReader br= new BufferedReader(is);

String s=br.readLine();while (s != null) {

result[6] +=s;

s=br.readLine();

}

}

}

rs.close();

ps.close();

conn.close();