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     @Test101     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 }