jdbc讀取數據庫文本數據

1.在mysq的mydatabasel數據庫中建立一張表

use database;
create table if not exists my_clob_test(
id int primary key auto_increment,
big_text text not null
)charset utf8;

2. 工具類（jdbc鏈接過程代碼）

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Statement;

public final class JdbcUtils {

	private static String url = "jdbc:mysql://localhost:3306/mydatabase";
	private static String user = "root";
	private static String password = "123";

	private JdbcUtils() {
	}

	/**
	 * 註冊驅動
	 */
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 創建鏈接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;

		try {
			conn = (Connection) DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 釋放資源
	 * 
	 * @param rs
	 * @param st
	 * @param conn
	 */
	public static void free(ResultSet rs, Statement st, Connection conn) {
		// 釋放ResultSet
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 釋放Statement
			try {
				if (st != null)
					st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				// 釋放Connection
				try {
					if (conn != null)
						conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}

	}

}

3.運行下面的函數create()向數據庫中插入一條文本數據

/**
	 * 寫數據到表中
	 * @param name
	 * @param birthday
	 * @param money
	 * @throws IOException 
	 */
	static void create() throws IOException {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			// 2.創建鏈接
			conn = (Connection) JdbcUtils.getConnection();
			// 3.建立語句
			String sql = "insert into my_clob_test(big_text) values(?)";
			ps = (PreparedStatement) conn.prepareStatement(sql);			
			//將該目錄下的文件內容寫到數據庫的my_clob_test表中
			File file = new File("src/cn/itcast/jdbc/JdbcUtilsSing.java"); 
			Reader reader = new BufferedReader(new FileReader(file));
			//將「？」代替成數據流
			ps.setCharacterStream(1,reader,file.length());
			
			// 4.執行語句
			int i = ps.executeUpdate();
			reader.close();
			
			System.out.println("i=" + i);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.free(rs, ps, conn); // 關閉資源
		}
	}

4.運行下面函數read()讀取數據庫中的文本數據，而後將內容寫到當前目錄的copy.java文件中

/**
	 * 讀取text數據
	 * @throws IOException
	 */
	static void read() throws IOException {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			// 2.創建鏈接
			conn = (Connection) JdbcUtils.getConnection();
			// 3.建立語句
			String sql = "select big_text from my_clob_test";
			ps = (PreparedStatement) conn.prepareStatement(sql);
			// 4.執行語句
			rs = ps.executeQuery();
			
			//5.處理結果
			while(rs.next()){
				
				//	Clob clob = rs.getClob(1);
				//Reader reader = clob.getCharacterStream();
				//以上兩句語句可使用下面一句代碼代替
				Reader reader = rs.getCharacterStream(1);
				
				//封裝數據源
				File file = new File("copy.java");
				//建立寫數據流
				Writer writer =new BufferedWriter(new FileWriter(file));
				
				//建立緩存區
				char[] buff = new char[1024];
				//讀寫數據方式1
				int len=0;
				while((len = reader.read(buff))>0){
					writer.write(buff,0,len);
				}
				//讀寫數據方式2
//				for(int i = 0;(i = reader.read(buff))>0;){
//					writer.write(buff,0,i);
//				}
				
				writer.close();
				reader.close();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.free(rs, ps, conn); // 關閉資源
		}
	}