实现在mysql数据库中保存text大文本和blob大二进制数据

浏览数：47 / 时间：2015年06月12日

项目代码如下：

config配置文件：

className=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/lob
user=root
password=root

com.itheima.util包下DBUtil.java

package com.itheima.util;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {

	private static Properties properties = null;
	
	static {
		properties = new Properties();
		try {
			properties.load(new FileReader(DBUtil.class.getClassLoader().getResource("config.properties").getPath()));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	public static Connection getConn() {
		String className = properties.getProperty("className");
		String url = properties.getProperty("url");
		String user = properties.getProperty("user");
		String password = properties.getProperty("password");
		
		Connection conn;
		try {
			Class.forName(className);
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return conn;
	}
	public static void close (Connection conn, PreparedStatement ps, ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				rs = null;
			}
		}
		if(ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				ps = null;
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				conn = null;
			}
		}
		
	}
}

com.itheima.lob包下TextDemo.java（实现存储大文本数据）

package com.itheima.lob;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.itheima.util.DBUtil;

public class TextDemo {

	/*
	 	create table textdemo (
	 		id int primary key auto_increment,
	 		name varchar(50),
	 		content mediumtext
	 	);
	 	
	 */
	@Test
	public void getText(){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from textdemo";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(2);
				Reader reader = rs.getCharacterStream(3);
				Writer writer = new FileWriter(name);
				int len = 0;
				char []data = new char[1024];
				while((len = reader.read(data)) != -1) {
					writer.write(data, 0, len);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(conn, ps, rs);
		}
	}
	@Test
	public void addText() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "insert into textdemo values(null,?,?)";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "钢铁是怎样炼成.txt");
			File file = new File("1.txt");
			ps.setCharacterStream(2, new FileReader(file), (int)file.length());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(conn, ps, rs);
		}
	}
}

com.itheima.lob包下BlobDemo.java（实现存储大二进制数据）

package com.itheima.lob;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.itheima.util.DBUtil;

/*
 	create table blobdemo(
 		id int primary key auto_increment,
 		name varchar(50),
 		content blob
 	);
 */

public class BlobDemo {

	@Test
	public void getBlob() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from blobdemo";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(2);
				InputStream is = rs.getBlob(3).getBinaryStream();
				OutputStream os = new FileOutputStream(name);
				int len = 0;
				byte[] data = new byte[1024];
				while((len = is.read(data)) != -1) {
					os.write(data, 0, len);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(conn, ps, rs);
		}
	}
	
	@Test
	public void addBlob() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "insert into blobdemo values (null,?,?)";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "洛天依.mp3");
			File file = new File("1.mp3");
			ps.setBinaryStream(2, new FileInputStream(file), (int)file.length());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			DBUtil.close(conn, ps, rs);
		}
	}
}

需要注意的是：

ps.setCharacterStream(2, new FileReader(file), (int)file.length());有三种重载形式