1. 往mysql中读写字符文本
public class Demo1 {
/* 创建数据库
create database LOBTest;
use LOBTest;
create table testclob
(
id int primary key auto_increment,
resume text
);
*/
@Test
public void add() {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try
{
conn = JdbcUtils.getConnection();
String sql = "insert into testclob(resume) values(?)";
st = conn.prepareStatement(sql);
String path = Demo1.class.getClassLoader().getResource("db.properties").getPath();
File file = new File(path);
FileReader fr = new FileReader(file);
st.setCharacterStream(1, fr, file.length());
int num = st.executeUpdate();
if(num > 0)
System.out.println("插入成功!!");
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
JdbcUtils.release(conn, st, rs);
}
}
@Test
public void read()
{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try
{
conn = JdbcUtils.getConnection();
String sql = "select resume from testclob where id = ?";
st = conn.prepareStatement(sql);
st.setInt(1, 2);
rs = st.executeQuery();
if(rs.next())
{
Reader reader = rs.getCharacterStream("resume");
FileWriter fw = new FileWriter("write.txt");
char[] buffer = new char[1024];
int len = 0;
while( (len=reader.read(buffer)) > 0)
{
fw.write(buffer, 0, len);
}
fw.close();
reader.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
JdbcUtils.release(conn, st, rs);
}
}
}
2. 往mysql中读写二进制文件
public class Demo2 {
/*
create table testblob
(
id int primary key auto_increment,
image blob
);
*/
@Test
public void add()
{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try
{
conn = JdbcUtils.getConnection();
String sql = "insert into testblob(image) values(?)";
st = conn.prepareStatement(sql);
String path = Demo1.class.getClassLoader().getResource("1.JPG").getPath();
File file = new File(path);
FileInputStream sf = new FileInputStream(file);
st.setBinaryStream(1, sf, file.length());
int num = st.executeUpdate();
if(num > 0)
System.out.println("插入成功!!");
sf.close();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
JdbcUtils.release(conn, st, rs);
}
}
@Test
public void read()
{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try
{
conn = JdbcUtils.getConnection();
String sql = "select image from testblob where id = ?";
st = conn.prepareStatement(sql);
st.setInt(1, 1);
rs = st.executeQuery();
if(rs.next())
{
InputStream is = rs.getBinaryStream("image");
byte[] buffer = new byte[1024];
int len = 0;
FileOutputStream fos = new FileOutputStream("1_back.JPG");
while( (len=is.read(buffer))>0 )
{
fos.write(buffer, 0, len);
}
fos.close();
is.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
JdbcUtils.release(conn, st, rs);
}
}
}