使用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 blob_data(id int primary key auto_increment, resume blob); */public class BlobData {@Testpublic 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);}}@Testpublic 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);}}}?