CLOB应用程序示例

在这一节中我们将给出一个应用Clob对象的完整例子。 在示例中，我们读取文件，写到数据库中，然后从数据库中将文件内容读出来，写到另外一个文件里。

package com.sun.cts.tests.jdbc.help_doc.lob.clob;
import static org.junit.Assert.fail;

import java.io.File;
/**************************************************
 *这个例子演示Clob使用
 **************************************************/
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.junit.Test;



public class ClobTest {
	private Connection con;
	public String DBUSER = "";
	public String DBPASSWD = "";
	public String DBURL="";
	public String DBDRIVER="";
	
	public void init() {
		Properties prop = new Properties();
		try{
			FileInputStream in = new FileInputStream("."+File.separator+"testFile"+File.separator+"cts_env.properties");
			prop.load(in);
			in.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		//从配置文件读取
		DBUSER = prop.getProperty("DBUSER");
		DBPASSWD = prop.getProperty("DBPASSWD");
		DBURL = prop.getProperty("DBURL");
		DBDRIVER = prop.getProperty("DBDRIVER");
		try{
			Class.forName(DBDRIVER);
			con = DriverManager.getConnection(DBURL, DBUSER, DBPASSWD);
		}catch(Exception e){
			fail(e.getMessage());
			e.printStackTrace();
		}
	}

	@Test
	public void execute() {
		init();
		Statement stmt = null;
		Reader reader = null;
		PreparedStatement pst = null;
		FileOutputStream fos = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			// 创建表
			String sql = "CREATE TABLE TEST_CLOB(ID INT,FILE CLOB)";
			stmt.execute(sql);
			// 改变auto-commit状态
			con.setAutoCommit(false);
			// 从表中取数据，写入到大对象中
			File file = new File("test/cout.txt");
			reader = new FileReader(file);
			// 插入记录
			pst = con.prepareStatement("INSERT INTO TEST_CLOB VALUES(?,?)");
			pst.setInt(1, 1);
			pst.setCharacterStream(2, reader, (int)file.length());
			pst.execute();
			con.commit();
			
			sql = "SELECT * FROM TEST_CLOB";
			rs = stmt.executeQuery(sql);
			// 将大对象的数据写到文件中去
			if (rs.next()) {
				Clob c = rs.getClob(2);
				InputStream is = c.getAsciiStream();
				fos = new FileOutputStream("test/cin.txt");
				byte[] buf = new byte[1000];
				int length = 0;
				while ((length = is.read(buf)) >= 0) {
					fos.write(buf, 0, length);
				}
				fos.flush();
			}
			con.commit();
			//删除表
			sql="DROP TABLE TEST_CLOB ";
			stmt.executeUpdate(sql);
			con.commit();
		} catch (SQLException se) {
			fail(se.getMessage());
		} catch (IOException ie) {
			System.out.println(ie);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (reader != null) {
					reader.close();
				}
				if (pst != null) {
					pst.close();
				}
				if (fos != null) {
					fos.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception ie2) {
				System.out.println(ie2);
			}
		}
	}

	public static void main(String[] args) {
		ClobTest test = new ClobTest();
		test.execute();
	}
}