oracle对大文本的存储,oracle存储大文本clob、blob

1 packagecn.itcast.web.oracle.dao;2

3 importjava.io.File;4 importjava.io.FileInputStream;5 importjava.io.FileOutputStream;6 importjava.io.FileReader;7 importjava.io.FileWriter;8 importjava.io.InputStream;9 importjava.io.OutputStream;10 importjava.io.Reader;11 importjava.io.Writer;12 importjava.net.URL;13 importjava.sql.Connection;14 importjava.sql.PreparedStatement;15 importjava.sql.ResultSet;16

17 importorg.junit.Test;18

19 importcn.itcast.web.oracle.util.JdbcUtil;20

21 //Java测试oracle数据库存取大对象

22 /*

23 create table test_clob(24 id number primary key,25 content clob not null26 );27 create table test_blob(28 id number primary key,29 content blob not null30 );31 */

32 public classLobDao {33 //测试clob对象(存)

34 @Test35 public void saveClobToOracle() throwsException{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 = newFile(path);44 //获取文件的reader字符流对象

45 Reader reader = newFileReader(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 @Test56 public void getClobFormOracle() throwsException{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 @Test80 public void saveBlobToOracle() throwsException{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 = newFile(path);89 InputStream inputStream = newFileInputStream(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 @Test101 public void getBlobFromOracle() throwsException{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 }