- package com.logcd.common;
- import java.io.BufferedReader;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.InputStreamReader;
- import java.io.Reader;
- import java.io.Writer;
- import java.net.HttpURLConnection;
- import java.net.URL;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import org.apache.commons.io.FileUtils;
- import oracle.sql.CLOB;
- public class OracleClob {
- public static void main(String[] args) {
- Integer id = saveClobDataUseSQL("测试", getFileContentAsString(
- "D:/uploadDir/test.txt", true));
- readClobDataUseSQL(id, new File("D:/uploadDir/test2.txt"));
- }
- /**
- * 保存Clob数据
- *
- * @param name
- * 数据名称
- * @param data
- * 字串数据
- */
- @SuppressWarnings("deprecation")
- public static Integer saveClobDataUseSQL(String name, String data) {
- Connection conn = getConnection();
- Integer id = (int) (Math.random() * 100000);
- StringBuilder sqlBuilder = new StringBuilder();
- try {
- conn.setAutoCommit(false);
- Statement stmt = conn.createStatement();
- sqlBuilder.append("insert into TEST_CLOB(ID, NAME, CONTENT) ");
- sqlBuilder.append("values ( " + id);
- sqlBuilder.append(",'" + name + "'");
- sqlBuilder.append(", empty_clob()) ");// 插入一个空对象empty_clob()
- stmt.executeUpdate(sqlBuilder.toString());
- // 锁定数据行进行更新,注意“for update”语句
- String sqlUpd = "select CONTENT from TEST_CLOB where ID = " + id
- + " for update";
- ResultSet rs = stmt.executeQuery(sqlUpd);
- if (rs.next()) {
- // 得到java.sql.Clob对象后强制转换为oracle.sql.CLOB
- CLOB clob = (CLOB) rs.getClob("CONTENT");
- Writer outStream = clob.setCharacterStream(0L);
- // data是传入的字符串,定义:String data
- char[] c = data.toCharArray();
- outStream.write(c, 0, c.length);
- outStream.flush();
- outStream.close();
- conn.commit();
- stmt.close();
- }
- } catch (Exception e) {
- try {
- conn.rollback();
- id = null;
- } catch (SQLException e1) {
- e1.printStackTrace();
- }
- e.printStackTrace();
- } finally {
- try {
- conn.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- return id;
- }
- /**
- * 读出数据并存成文件
- *
- * @param id
- * @param file
- */
- public static void readClobDataUseSQL(Integer id, File file) {
- Connection conn = getConnection();
- try {
- Statement st = conn.createStatement();
- String sql = "select CONTENT from TEST_CLOB where ID = " + id;
- ResultSet rs = st.executeQuery(sql);
- if (rs.next()) {
- CLOB clob = (CLOB) rs.getClob("CONTENT");
- String result = convertClobToString(clob);
- System.out.println(result);
- FileUtils.writeStringToFile(file,result, "utf-8");
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- try {
- conn.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- /**
- * 将CLOB转换成字串
- * @param clob
- * @return
- */
- public static String convertClobToString(CLOB clob) {
- String reString = "";
- try {
- Reader is = clob.getCharacterStream();// 得到流
- BufferedReader br = new BufferedReader(is);
- String s = br.readLine();
- StringBuffer sb = new StringBuffer();
- while (s != null) {
- sb.append(s);
- sb.append("/n");
- s = br.readLine();
- }
- reString = sb.toString().trim();
- } catch(Exception e) {
- e.printStackTrace();
- }
- return reString;
- }
- /**
- * 取得数据库连接
- *
- * @return
- */
- public static Connection getConnection() {
- String driver = "oracle.jdbc.driver.OracleDriver";
- String url = "jdbc:oracle:thin:@195.2.199.5:1521:orcl";
- Connection conn = null;
- try {
- Class.forName(driver);
- conn = DriverManager.getConnection(url, "testdb", "logcd");
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException ex) {
- ex.printStackTrace();
- }
- return conn;
- }
- /**
- * 读取文件内容
- *
- * @param _url
- * @param isLocal
- * @return
- */
- public static String getFileContentAsString(String _url, boolean isLocal) {
- StringBuilder strb = new StringBuilder();
- try {
- InputStreamReader read = null;
- ;
- if (!isLocal) {
- URL url = new URL(_url);
- HttpURLConnection connection = (HttpURLConnection) url
- .openConnection();
- read = new InputStreamReader(connection.getInputStream());
- } else {
- File file = new File(_url);
- read = new InputStreamReader(new FileInputStream(file));
- }
- BufferedReader br = new BufferedReader(read);
- char[] cbuf = new char[1024];
- while (br.read(cbuf) != -1) {
- strb.append(cbuf);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- return strb.toString();
- }
- }
Oracle里读取Blob/Clob 乱码的解决办法
最新推荐文章于 2024-07-23 23:58:27 发布