BLOB应用程序示例

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

package com.sun.cts.tests.jdbc.help_doc.lob.blob;

import static org.junit.Assert.fail;

import java.io.File;
/**************************************************
 *这个例子演示Blob使用
 **************************************************/
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
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 BlobTest {
	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){
			e.printStackTrace();
		}
	}
	@Test
	public void execute() {
		init();
		Statement stmt = null;
		FileInputStream fis = null;
		PreparedStatement pst = null;
		FileOutputStream fos = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			/* 创建表 */
			String sql = "CREATE TABLE TEST_BLOB(ID INT,FILE BLOB)";
			stmt.execute(sql);
			/* 改变auto-commit状态 */
			con.setAutoCommit(false);
			/* 从表中取数据，写入到大对象中 */
			String fileName = "test/bout.txt";
			fis = new FileInputStream(fileName);
			/* 插入记录 */
			pst = con.prepareStatement("INSERT INTO TEST_BLOB VALUES(?,?)");
			pst.setInt(1, 1);
			pst.setBinaryStream(2, fis, (int)new File(fileName).length());
			pst.execute();
			con.commit();
			
			sql = "SELECT * FROM TEST_BLOB";
			rs = stmt.executeQuery(sql);
			/* 将大对象的数据写到文件中去 */
			if (rs.next()) {
				Blob b = rs.getBlob(2);
				InputStream is = b.getBinaryStream();
				fos = new FileOutputStream("test/bin.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_BLOB ";
			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 (pst != null) {
					pst.close();
				}
				if (fis != null) {
					fis.close();
				}
				if (fos != null) {
					fos.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception se) {
				System.out.println(se);
			}
		}
	}

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

}