在实际开发中,程序一般不把大文本或二进制数据直接保存到数据库。但当需要时则应采用以下方式
大数据也称之为LOB(Large Objects)。
LOB分为:clob和blob
clob用于存储大文本。mysql中为Text
blob用于存储二进制数据,例如图像、声音、二进制文等。
对MySQL而言只有blob,而没有clob,mysql存储大文本采用的是Text。
Text和blob分为:
TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
处理大文本
存入方法:PreparedStatement.setCharacterStream(index,reader, length);
注:length长度须设置,并且设置为int型 ,jdk1.6以后才有long型的length 获取方法:(对MySQL中的Text类型)
reader = resultSet. getCharacterStream(i);
reader = resultSet.getClob(i).getCharacterStream();
string s = resultSet.getString(i); 不推荐,文本太大,返回字符串会导致内存溢出
例:存储
public void insert() throws SQLException, FileNotFoundException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "insert into testclob(id,resume) values(?,?)";
st = conn.prepareStatement(sql);
st.setString(1, "1");
//创建文件对象,为了获取文件的长度大小
File file = new File("src/1.txt");
//由于是普通工程,就用传统读取了
FileReader reader = new FileReader(file);
st.setCharacterStream(2, reader, (int) file.length());
int num = st.executeUpdate();
if(num>0){
System.out.println("插入成功!!");
}
}finally{
JdbcUtils.release(conn, st, rs);
}
}
取出
public void read() throws SQLException, IOException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "select id,resume from testclob where id='1'";
st = conn.prepareStatement(sql);
rs = st.executeQuery();
if(rs.next()){
// 获取流的方法有两种
// InputStream in = resultSet.getBlob(i).getBinaryStream();
Reader reader = rs.getCharacterStream("resume");
FileWriter writer = new FileWriter("c:\\1.txt");
//读取流
try {
int len = 0;
char buffer[] = new char[1024];
while ((len = reader.read(buffer)) > 0) {
writer.write(buffer, 0, len);
}
} finally {
if (reader != null) {
reader.close();
}
writer.close();
}
}
}finally{
JdbcUtils.release(conn, st, rs);
}
}
处理二进制数据
存入方法:PreparedStatement. setBinaryStream(i, inputStream, length);
获取方法:InputStreamin = resultSet.getBinaryStream(i);
InputStream in = resultSet.getBlob(i).getBinaryStream();
例:存储
public void insert() throws SQLException, FileNotFoundException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "insert into testblob(id,image) values(?,?)";
st = conn.prepareStatement(sql);
st.setString(1, "1");
File file = new File("src/1.jpg");
FileInputStream in = new FileInputStream(file);
st.setBinaryStream(2, in, (int) file.length());
st.executeUpdate();
}finally{
JdbcUtils.release(conn, st, rs);
}
}
读取
// 读取不能直接在DOS中读取,否则读出的全是乱码
public void read() throws SQLException, IOException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "select id,image from testblob where id='1'";
rs = conn.prepareStatement(sql).executeQuery();
if(rs.next()){
InputStream in = rs.getBinaryStream("image");
OutputStream out = new FileOutputStream("c:\\1.jpg");;
try {
int len = 0;
byte buffer[] = new byte[1024];
while ((len = in.read(buffer)) > 0) {
out.write(buffer, 0, len);
}
} finally {
if (in != null)
in.close();
if (out != null)
out.close();
}
}
}finally{
JdbcUtils.release(conn, st, rs);
}
}