[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);
}
}
}
?