oracle本身支持对大数据类型的各种处理,但是平常大家可能用的不多,其中clob(一般用于大类型的字符型的存取)和blob(一般用于大类型的二进制类型的存取)我想应该就是用的比较少的。所以我写下这篇博文,主要是对这两个数据类型的文件进行的操作,如,保存这类文件进入数据库,读取这类文件到本地磁盘。
本来按照老习惯是该先上效果图的,可惜我的机器现在巨卡,10g跑起来基本让我泪流满面,所以我就偷下懒,图就给省略了 -_-!
第一步,新建一个java项目(为啥是java项目而不是web呢,主要因为它便于测试,出效果也快...)orclTest
第二步,新建一个数据库连接的类,我这里是com.test.InitDB.java,代码如下:
- class InitDB{
- private static Connection con = null;
- private static Statement stmt = null;
- private static ResultSet rs = null;
- //链接oracle数据库
- InitDB()
- {
- try
- {
- Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
- String url = "jdbc:oracle:thin:@localhost :1521:YYSMID";
- String user = "zhangsan";
- String password = "Lc123456";
- con = (Connection) DriverManager.getConnection(url, user, password);
- InitDB.setCon(con);
- }
- catch (Exception e)
- {
- e.printStackTrace();
- }
- }
- public void closCon()
- {
- try
- {
- con.close();
- }
- catch (Exception e)
- {
- e.printStackTrace();
- }
- }
- public void stmt()
- {
- try
- {
- con.close();
- }
- catch (Exception e)
- {
- e.printStackTrace();
- }
- }
- public void rs()
- {
- try
- {
- con.close();
- }
- catch (Exception e)
- {
- e.printStackTrace();
- }
- }
- public static Connection getCon()
- {
- return con;
- }
- public static void setCon(Connection con)
- {
- InitDB.con = con;
- }
- public static ResultSet getRs()
- {
- return rs;
- }
- public static void setRs(ResultSet rs)
- {
- InitDB.rs = rs;
- }
- public static Statement getStmt()
- {
- return stmt;
- }
- public static void setStmt(Statement stmt)
- {
- InitDB.stmt = stmt;
- }
- }
第三步,以及接下来的几步,我们来分别编写clob和blob的插入数据库和读出数据库的代码,新建com.test.InsertBlobData.java,顾名思义,它是用来插入blob类型数据的类。代码如下:
- class InsertBlobData{
- private ResultSet rs = null;
- private InitDB idb = null;
- InsertBlobData()
- {
- idb = new InitDB();
- }
- public void insertBlob(String sql1) throws SQLException
- {
- Connection con = idb.getCon();
- try
- {
- con.setAutoCommit(false);// 不设置自动提交
- BLOB blob = null; // 插入空的Blob
- PreparedStatement pstmt = con
- .prepareStatement("insert into cdl_test(sid,img) values(?,empty_blob())");
- pstmt.setString(1, "100");
- pstmt.executeUpdate();
- pstmt.close();
- rs = con.createStatement().executeQuery(sql1);
- while (rs.next())
- {
- System.out.println("rs length is:");
- oracle.sql.BLOB b = (oracle.sql.BLOB) rs.getBlob("img");
- System.out.println("cloblength is:" + b.getLength());
- File f = new File("d:\\1.jpg"); //1.jpg一张QQ的截图
- System.out.println("file path is:" + f.getAbsolutePath());
- BufferedInputStream in = new BufferedInputStream(
- new FileInputStream(f));
- BufferedOutputStream out = new BufferedOutputStream(b
- .getBinaryOutputStream());
- int c;
- while ((c = in.read()) != -1)
- {
- out.write(c);
- }
- in.close();
- out.close();
- }
- con.commit();
- }
- catch (Exception e)
- {
- con.rollback();// 出错回滚
- e.printStackTrace();
- }
- }
- }
第四步,新建com.test.InsertClobData.java,用于插入clob数据类型的,代码如下:
- class InsertClobData
- {
- private ResultSet rs = null;
- private InitDB idb = null;
- InsertClobData()
- {
- idb = new InitDB();
- }
- public void insertClob(String sql1) throws SQLException
- {
- Connection con = idb.getCon();
- try
- {
- con.setAutoCommit(false);// 不设置自动提交
- BLOB blob = null; // 插入空的Clob
- PreparedStatement pstmt = con
- .prepareStatement("insert into cdl_test(sid,doc) values(?,empty_clob())");
- pstmt.setString(1, "101");
- pstmt.executeUpdate();
- pstmt.close();
- rs = con.createStatement().executeQuery(sql1);
- while (rs.next())
- {
- System.out.println("sdfasdfas");
- oracle.sql.CLOB cb = (oracle.sql.CLOB) rs.getClob("doc");
- File f = new File("d:\\1.txt"); //1.txt一本小说《风云》马荣成
- System.out.println("file path is:" + f.getAbsolutePath());
- BufferedWriter out = new BufferedWriter(cb
- .getCharacterOutputStream());
- BufferedReader in = new BufferedReader(new FileReader(f));
- int c;
- while ((c = in.read()) != -1)
- {
- out.write(c);
- }
- in.close();
- out.close();
- }
- con.commit();
- }
- catch (Exception e)
- {
- con.rollback();// 出错回滚
- e.printStackTrace();
- }
- }
- }
第五步,新建com.test.ReadBlobData.java,用于读取blob类型的数据,代码如下:
- class ReadBlobData
- {
- private ResultSet rs = null;
- private InitDB idb = null;
- ReadBlobData()
- {
- idb = new InitDB();
- }
- public void getBlob(String sql2) throws SQLException
- {
- Connection con = idb.getCon();
- con.setAutoCommit(false);
- try
- {
- System.out.println("sq2 is:" + sql2);
- System.out.println("stmt is:" + con);
- rs = con.createStatement().executeQuery(sql2);
- while (rs.next())
- {
- System.out.println("rs length is:");
- Blob b = (Blob) rs.getBlob("img");
- File f = new File("D:\\1.jpg");
- FileOutputStream fos = new FileOutputStream(f);
- InputStream is = b.getBinaryStream();// 读出数据后转换为二进制流
- byte[] data = new byte[1024];
- while (is.read(data) != -1)
- {
- fos.write(data);
- }
- fos.close();
- is.close();
- }
- con.commit();// 正式提交
- }
- catch (Exception e)
- {
- e.printStackTrace();
- }
- finally
- {
- // rs.close();
- }
- }
- }
第六步,新建com.test.ReadClobData.java,用于读取clob类型的数据,代码如下:
- class ReadClobData
- {
- private ResultSet rs = null;
- private InitDB idb = null;
- ReadClobData()
- {
- idb = new InitDB();
- }
- public void getClob(String sql2) throws SQLException
- {
- Connection con = idb.getCon();
- try
- {
- con.setAutoCommit(false);// 不设置自动提交
- System.out.println("sq2 is:" + sql2);
- rs = con.createStatement().executeQuery(sql2);
- while (rs.next())
- {
- oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("doc");
- File f = new File("d:\\1.txt");
- BufferedReader in = new BufferedReader(clob
- .getCharacterStream());
- // setCharacterStream()方法,可用于将CLOB字段与字节流相关联,
- BufferedWriter out = new BufferedWriter(new FileWriter(f));
- int c;
- while ((c = in.read()) != -1)
- {
- out.write(c);
- }
- out.close();
- in.close();
- }
- con.commit();// 正式提交
- rs.close();
- }
- catch (Exception e)
- {
- e.printStackTrace();
- con.rollback();
- }
- }
- }
最后,就是新建测试类了,每每用着main方法,我就感觉这世界是多么的有爱啊,junit什么的,最讨厌了.....
- public class TestBlob_Clob
- {
- public static void main(String[] args)
- {
- String sql1 = "select * from cdl_test for update";// 悲观锁锁定需更新的行
- String sql2 = "select * from cdl_test";
- System.out.println("\t\t\t欢迎使用:");
- System.out.println("1:插入图片");
- System.out.println("2:插入文本");
- System.out.println("3:读取图片");
- System.out.println("4:读取文本");
- System.out.println("5:退出");
- System.out.println("请选择:");
- while (true)
- {
- try
- {
- Scanner sc = new Scanner(System.in);
- int i = sc.nextInt();
- System.out.println("sss:" + i);
- switch (i)
- {
- case 1:
- InsertBlobData isd = new InsertBlobData();
- //插入图片
- isd.insertBlob(sql1);
- break;
- case 2:
- InsertClobData icd = new InsertClobData();
- //插入小说
- icd.insertClob(sql1);
- break;
- case 3:
- ReadBlobData rb = new ReadBlobData();
- //得到图片
- rb.getBlob(sql2);
- break;
- case 4:
- ReadClobData rc = new ReadClobData();
- //得到小说
- rc.getClob(sql2);
- break;
- case 5:
- System.exit(0);
- }
- }
- catch (Exception e)
- {
- e.printStackTrace();
- }
- }
- }
- }