oracle存储大文本clob、blob
浏览数：22 /
时间：2015年06月12日
1 package cn.itcast.web.oracle.util; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.Statement; 7 8 public class JdbcUtil { 9 private static String driver = "oracle.jdbc.driver.OracleDriver"; 10 //1521是主端口，也可能是其它端口去连接oracle数据库 11 private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; 12 private static String username = "scott"; 13 private static String password = "friends"; 14 private static Connection conn; 15 private static Statement stmt; 16 private static ResultSet rs; 17 //注册数据库驱动 18 static{ 19 try { 20 Class.forName(driver); 21 } catch (Exception e) { 22 e.printStackTrace(); 23 throw new RuntimeException("oracle驱动注册失败"); 24 } 25 } 26 //获取数据库连接 27 public static Connection getConnection(){ 28 Connection conn = null; 29 try { 30 conn = DriverManager.getConnection(url,username,password); 31 } catch (Exception e) { 32 e.printStackTrace(); 33 throw new RuntimeException("oracle连接获取失败"); 34 } 35 return conn; 36 } 37 //关闭连接对象 38 public static void close(Connection conn){ 39 if(conn!=null){ 40 try { 41 conn.close(); 42 } catch (Exception e) { 43 e.printStackTrace(); 44 throw new RuntimeException("oracle连接关闭失败"); 45 } 46 } 47 } 48 public static void close(Statement stmt){ 49 if(stmt!=null){ 50 try { 51 stmt.close(); 52 } catch (Exception e) { 53 e.printStackTrace(); 54 throw new RuntimeException("oracle连接关闭失败"); 55 } 56 } 57 } 58 public static void close(ResultSet rs){ 59 if(rs!=null){ 60 try { 61 rs.close(); 62 } catch (Exception e) { 63 e.printStackTrace(); 64 throw new RuntimeException("oracle连接关闭失败"); 65 } 66 } 67 } 68 }
1 package cn.itcast.web.oracle.dao; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileOutputStream; 6 import java.io.FileReader; 7 import java.io.FileWriter; 8 import java.io.InputStream; 9 import java.io.OutputStream; 10 import java.io.Reader; 11 import java.io.Writer; 12 import java.net.URL; 13 import java.sql.Connection; 14 import java.sql.PreparedStatement; 15 import java.sql.ResultSet; 16 17 import org.junit.Test; 18 19 import cn.itcast.web.oracle.util.JdbcUtil; 20 21 //Java测试oracle数据库存取大对象 22 /* 23 create table test_clob( 24 id number primary key, 25 content clob not null 26 ); 27 create table test_blob( 28 id number primary key, 29 content blob not null 30 ); 31 */ 32 public class LobDao { 33 //测试clob对象（存） 34 @Test 35 public void saveClobToOracle() throws Exception{ 36 Connection conn = JdbcUtil.getConnection(); 37 String sql = "insert into test_clob(id,content) values(?,?)"; 38 PreparedStatement pstmt = conn.prepareStatement(sql); 39 pstmt.setInt(1,1); 40 //加载文件 41 URL url = LobDao.class.getClassLoader().getResource("cn/itcast/web/oracle/db/xx.txt"); 42 String path = url.getPath(); 43 File file = new File(path); 44 //获取文件的reader字符流对象 45 Reader reader = new FileReader(file); 46 //为第二个?占位符设置值 47 pstmt.setCharacterStream(2,reader,(int)file.length()); 48 int i = pstmt.executeUpdate(); 49 System.out.println(i>0?"操作成功":"操作失败"); 50 reader.close(); 51 JdbcUtil.close(pstmt); 52 JdbcUtil.close(conn); 53 } 54 //测试clob对象（取） 55 @Test 56 public void getClobFormOracle() throws Exception{ 57 Connection conn = JdbcUtil.getConnection(); 58 String sql = "select content from test_clob where id = 1"; 59 PreparedStatement pstmt = conn.prepareStatement(sql); 60 ResultSet rs = pstmt.executeQuery(); 61 Reader reader = null; 62 Writer writer = null; 63 while(rs.next()){ 64 reader = rs.getCharacterStream("content"); 65 writer = new FileWriter("d:/copy_xx.txt"); 66 int len = 0; 67 char[] cuf = new char[1024]; 68 while((len = reader.read(cuf))>0){ 69 writer.write(cuf,0,len); 70 } 71 } 72 reader.close(); 73 writer.close(); 74 JdbcUtil.close(rs); 75 JdbcUtil.close(pstmt); 76 JdbcUtil.close(conn); 77 } 78 //测试blob对象（存） 79 @Test 80 public void saveBlobToOracle() throws Exception{ 81 Connection conn = JdbcUtil.getConnection(); 82 String sql = "insert into test_blob(id,content) values(?,?)"; 83 PreparedStatement pstmt = conn.prepareStatement(sql); 84 pstmt.setInt(1,1); 85 86 URL url = LobDao.class.getClassLoader().getResource("cn/itcast/web/oracle/db/image.jpg"); 87 String path = url.getPath(); 88 File file = new File(path); 89 InputStream inputStream = new FileInputStream(file); 90 pstmt.setBinaryStream(2,inputStream,(int)file.length()); 91 92 int i = pstmt.executeUpdate(); 93 System.out.println(i>0?"操作成功":"操作失败"); 94 95 inputStream.close(); 96 JdbcUtil.close(pstmt); 97 JdbcUtil.close(conn); 98 } 99 //测试blob对象（取） 100 @Test 101 public void getBlobFromOracle() throws Exception{ 102 Connection conn = JdbcUtil.getConnection(); 103 String sql = "select content from test_blob where id = 1"; 104 PreparedStatement pstmt = conn.prepareStatement(sql); 105 ResultSet rs = pstmt.executeQuery(); 106 InputStream is = null; 107 OutputStream os = null; 108 while(rs.next()){ 109 is = rs.getBinaryStream("content"); 110 os = new FileOutputStream("d:/copy_image.jpg"); 111 byte[] buf = new byte[1024]; 112 int len = 0; 113 while((len=is.read(buf))>0){ 114 os.write(buf,0,len); 115 } 116 } 117 is.close(); 118 os.close(); 119 JdbcUtil.close(rs); 120 JdbcUtil.close(pstmt); 121 JdbcUtil.close(conn); 122 } 123 }
文本：CLOB(Character)
多媒体：BLOB(Binary)
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
