数据库BLOB和CLOB插入-oracle-java
目录
数据库BLOB和CLOB插入 oracle java
============oracle 表 sql
CREATE TABLE "SMALL19RAIN"."R_TABLE_LOB"
( "R_ID" NUMBER NOT NULL ENABLE,
"R_CLOB" CLOB,
"R_BLOB" BLOB
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SMALLRAIN19TABLESPACE"
LOB ("R_CLOB") STORE AS SECUREFILE (
TABLESPACE "SMALLRAIN19TABLESPACE" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("R_BLOB") STORE AS SECUREFILE (
TABLESPACE "SMALLRAIN19TABLESPACE" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;==============java 文件
package org.rain.db;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/**
* 数据库BlOB和CLOB插入
*/
public class SmallRainDBLOB {
public static void main(String[] args) {
Connection smallrainConn = null;
// Statement smallrainStmt = null;
PreparedStatement smallrainStmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
smallrainConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "smallrain123456789",
"Xiaoyu123456789");
SmallRainDBLOB smallrainDB = new SmallRainDBLOB();
// 插入clob数据String
smallrainStmt = smallrainConn.prepareStatement("INSERT INTO R_TABLE_LOB (R_CLOB,R_ID) VALUES (?,?)");
String smallRainclobText = "oracle数据库大数据ClOB";
StringReader smallrainReaderString = new StringReader(smallRainclobText);
smallrainStmt.setClob(1, smallrainReaderString);
smallrainStmt.setInt(2, 333);
smallrainStmt.executeUpdate();
// 插入clob文件数据,将文本文件放到当前java文件目录下，文件名为 22228.txt
smallrainStmt = smallrainConn.prepareStatement("INSERT INTO R_TABLE_LOB (R_CLOB,R_ID) VALUES (?,?)");
Reader smallrainFileReader = new FileReader(smallrainDB.getClass().getResource("22228.txt").getFile());
smallrainStmt.setCharacterStream(1, smallrainFileReader);
smallrainStmt.setInt(2, 222);
smallrainStmt.executeUpdate();
// 插入blob数据
smallrainStmt = smallrainConn.prepareStatement("INSERT INTO R_TABLE_LOB (R_ID, R_BLOB) VALUES (?,?)");
// 将图片文件放到当前java文件目录下，文件名为 345445.png
FileInputStream fis = new FileInputStream(smallrainDB.getClass().getResource("345445.png").getFile());
smallrainStmt.setInt(1, 6666);
smallrainStmt.setBinaryStream(2, fis);
smallrainStmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (smallrainStmt != null) {
smallrainStmt.close();
}
} catch (Exception e) {
e.printStackTrace();
}
try {
if (smallrainConn != null) {
smallrainConn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}