mysql中怎么读取blob类型数据_MySQL 中Blob类型数据的插入和读取

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.OutputStream;

import java.sql.Blob;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import org.junit.Test;

public class TestBlob {

@Test

public void getBlob(){//读取Blob数据

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

con = JDBCTools.getConnection();

String sql = "SELECT id,name,age,picture FROM animal WHERE id=5";

ps = con.prepareStatement(sql);

rs = ps.executeQuery();

if(rs.next()){

int id = rs.getInt(1);

String name = rs.getString(2);

int age = rs.getInt(3);

Blob picture = rs.getBlob(4);//获得Blob对象

//开始读入文件

InputStream in = picture.getBinaryStream();

OutputStream out = new FileOutputStream("cat.png");

byte[] buffer = new byte[1024];

int len = 0;

while((len = in.read(buffer)) != -1){

out.write(buffer, 0, len);

}

}

} catch (Exception e) {

e.printStackTrace();

}

}

@Test

public void insertBlob(){//插入Blob

Connection con = null;

PreparedStatement ps = null;

try {

con = JDBCTools.getConnection();

String sql = "INSERT INTO animal(name,age,picture) VALUES(?,?,?)";

ps = con.prepareStatement(sql);

ps.setString(1, "TheCat");

ps.setInt(2, 8);

InputStream in = new FileInputStream("J:/test1/TomCat.png");//生成被插入文件的节点流

//设置Blob

ps.setBlob(3, in);

ps.executeUpdate();

} catch (Exception e) {

e.printStackTrace();

}finally{

JDBCTools.release(con, ps);

}

}

class JDBCTools {//JDBC工具类 用来创建链接和释放链接

public static Connection getConnection() throws Exception{//链接数据库

String driverClass = null;

String url = null;

String user = null;

String password = null;

Properties properties = new Properties();

InputStream in = Review.class.getClassLoader().getResourceAsStream("jdbc.properties");

properties.load(in);

driverClass = properties.getProperty("driver");

url = properties.getProperty("jdbcurl");

user = properties.getProperty("user");

password = properties.getProperty("password");

Class.forName(driverClass);

return DriverManager.getConnection(url, user, password);

}

public static void release(Connection con , Statement state){//关闭数据库链接

if(state != null){

try {

state.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if(con != null){

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

public static void release(ResultSet rs , Connection con , Statement state){//关闭数据库链接

if(rs != null)

{

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if(state != null){

try {

state.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if(con != null){

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

}

public class Review {

public Connection getConnection() throws Exception{//链接数据库

String driverClass = null;

String url = null;

String user = null;

String password = null;

Properties properties = new Properties();

InputStream in = Review.class.getClassLoader().getResourceAsStream("jdbc.properties");

properties.load(in);

driverClass = properties.getProperty("driver");

url = properties.getProperty("jdbcurl");

user = properties.getProperty("user");

password = properties.getProperty("password");

Class.forName(driverClass);

return DriverManager.getConnection(url, user, password);

}

}