使用JDBC处理文本数据或二进制数据

[coolxing按: 转载请注明作者和出处, 如有谬误, 欢迎在评论中指正.]

LOB(Large Object)分为CLOB和BLOB, CLOB表示文本数据, BLOB用于表示二进制数据.

MySql存储文本数据使用的是TEXT而不是CLOB, MySql中的TEXT和BLOB分别分为:

TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT, 占用的内存空间分别为256B, 64K, 16M, 64G.

TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB, 占用的内存空间同上.

如果LOB数据过大, 并不适合使用数据库处理, 因为处理这些数据需要长时间占用数据库的链接资源.

读取与存储TEXT数据:

/*
 * create table text_data(id int primary key auto_increment, resume text);
 */
public class TextData {
	@Test
	public void insertToDatabase() {
		Connection conn = null;
		PreparedStatement st = null;
		
		String sql = "insert into text_data(resume) values(?)";
		// 获得文本文件的绝对路径
		String path = TextData.class.getClassLoader().getResource("db.properties").getPath();
		File file = new File(path);
		try {
			conn = JdbcUtils.getConnection();
			st = conn.prepareStatement(sql);
			st.setCharacterStream(1, new FileReader(file), (int)file.length());
			st.executeUpdate();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.release(null, st, conn);
		}
	}
	
	@Test
	public void readTextFromDatabase() {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		Reader reader = null;
		FileWriter writer = null;
		
		String sql = "select resume from text_data where id=1";
		try {
			conn = JdbcUtils.getConnection();
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			if (rs.next()) {
				reader = rs.getCharacterStream("resume");
				writer = new FileWriter("d://db.properties");
				char[] chs = new char[1024];
				int len = 0;
				while((len = reader.read(chs)) != -1) {
					writer.write(chs, 0, len);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			FileUtils.fileClose(reader, writer);
			JdbcUtils.release(null, st, conn);
		}
	}
}

读取与存储二进制数据:

/*
 * create table blob_data(id int primary key auto_increment, resume blob);
 */
public class BlobData {
	@Test
	public void insertToDatabase() {
		Connection conn = null;
		PreparedStatement st = null;

		String sql = "insert into blob_data(resume) values(?)";
		// 获得文本文件的绝对路径
		String path = TextData.class.getClassLoader().getResource(
				"db.properties").getPath();
		File file = new File(path);
		try {
			conn = JdbcUtils.getConnection();
			st = conn.prepareStatement(sql);
			st.setBinaryStream(1, new FileInputStream(file), (int) file
					.length());
			st.executeUpdate();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.release(null, st, conn);
		}
	}

	@Test
	public void readBlobFromDatabase() {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		InputStream in = null;
		OutputStream out = null;

		String sql = "select resume from blob_data where id=1";
		try {
			conn = JdbcUtils.getConnection();
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			if (rs.next()) {
				in = rs.getBinaryStream("resume");
				out = new FileOutputStream("d://db.properties");
				byte[] buffer = new byte[1024];
				int len = 0;
				while((len = in.read(buffer)) != -1) {
					out.write(buffer, 0, len);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			FileUtils.streamClose(in, out);
			JdbcUtils.release(null, st, conn);
		}
	}
}