ORACLE用JDBC操作CLOB

来源：互联网发布：什么是审计作业软件编辑：程序博客网时间：2024/05/21 14:48
public class ClobTest {        static String url = "jdbc:oracle:thin:@10.12.10.18:1521:orcl";      static String user = "cwbe1_9999";      static String pwd = "or777";      static String text = "这是要插入到CLOB里面的数据,更新数据！" + "update";        private static int clobImport() throws ClassNotFoundException, SQLException {          DriverManager.registerDriver(new OracleDriver());          Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象          String sql = "insert into ETLNEEDPARAM(F_KEY,F_VALUE) values ('defaultpo',?)";// 要执行的SQL语句          PreparedStatement stmt = conn.prepareStatement(sql);// 加载SQL语句          // PreparedStatement支持SQL带有问号？，可以动态替换？的内容。          Reader clobReader = new StringReader(text); // 将 text转成流形式          stmt.setCharacterStream(1, clobReader, text.length());// 替换sql语句中的？          int num = stmt.executeUpdate();// 执行SQL          if (num > 0) {              System.out.println("ok");          } else {              System.out.println("NO");          }          stmt.close();          conn.close();          return num;      }        private static int clobUpdate(String key) throws ClassNotFoundException, SQLException {            DriverManager.registerDriver(new OracleDriver());          Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象          String sql = "update ETLNEEDPARAM set F_VALUE = ? where F_KEY = ? ";// 要执行的SQL语句          PreparedStatement stmt = conn.prepareStatement(sql);// 加载SQL语句          // PreparedStatement支持SQL带有问号？，可以动态替换？的内容。          Reader clobReader = new StringReader(text); // 将 text转成流形式          stmt.setString(2, key);          stmt.setCharacterStream(1, clobReader, text.length());// 替换sql语句中的？          int num = stmt.executeUpdate();// 执行SQL          if (num > 0) {              System.out.println("ok");          } else {              System.out.println("NO");          }          stmt.close();          conn.close();          return num;      }        private static String clobExport() throws ClassNotFoundException, SQLException, IOException {            CLOB clob = null;          String sql = "select F_VALUE from ETLNEEDPARAM where F_KEY ='test1'";          DriverManager.registerDriver(new OracleDriver());          Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象          PreparedStatement stmt = conn.prepareStatement(sql);          ResultSet rs = stmt.executeQuery();          String content = "";          if (rs.next()) {              clob = (oracle.sql.CLOB) rs.getClob("F_VALUE"); // 获得CLOB字段str              // 注释： 用 rs.getString("str")无法得到 数据 ，返回的 是 NULL;              content = ClobToString(clob);          }          stmt.close();          conn.close();          return content;      }        // 将字CLOB转成STRING类型        public static String ClobToString(CLOB clob) throws SQLException, IOException {          String reString = "";          Reader is = clob.getCharacterStream();// 得到流          BufferedReader br = new BufferedReader(is);          String s = br.readLine();          StringBuffer sb = new StringBuffer();          // 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING          while (s != null) {              sb.append(s);              s = br.readLine();          }          reString = sb.toString();          return reString;      }        public static void main(String[] args) throws IOException,ClassNotFoundException, SQLException {                      // System.out.println(clobImport());          System.out.println(clobUpdate("fmo"));          System.out.println(clobUpdate("epo"));            // System.out.println(clobExport());        }
0 0