java oracle clob

create table TEST_CLOB
(
  ID      INTEGER not null,
  NAME    VARCHAR2(20),
  CONTENT CLOB
)
alter table TEST_CLOB
  add primary key (ID);

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();
	}

}

评论

2 楼 log_cd 2010-06-30  

这个是oracle包问题,我用的是ojdbc14.jar！

1 楼 shim 2010-06-29  

java.sql.SQLException: 不支持的特性
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.dbaccess.DBError.throwUnsupportedFeatureSqlException(DBError.java:689)
at oracle.sql.CLOB.setCharacterStream(CLOB.java:1222)
at test.OracleClob.saveClobDataUseSQL(OracleClob.java:62)
at test.OracleClob.main(OracleClob.java:25)