java oracle clob string 大字符串存储

这几天在在往数据库里更新一个大字符串，忙了好几天终于试出来一个好方法，现在拿出来分享下：

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

	}

评论

2 楼 bestxiaok 2011-05-04  

SetBigStringTryClob

myy 写道

楼主还不知道 10g 的 jdbc 驱动支持 “SetBigStringTryClob”这个东东吧？

没试过，我经验还少，多多指教啊。。。

1 楼 myy 2011-05-04  

楼主还不知道 10g 的 jdbc 驱动支持 “SetBigStringTryClob”这个东东吧？