JDCB-从数据表中插入blob信息和读取blob信息

向数据表插入blob信息

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

import com.atguigu.util.JDBCUtil;

public class BlobInsertTest {
	@SuppressWarnings("resource")
	@Test
	public void testBlob() throws Exception {
		//获取连接
		Connection conn = JDBCUtil.getConnection();
		//生成一个文件
		File file = new File("啥都不是.png");
		//将文件转换成一个字节输入流
		FileInputStream is = new FileInputStream(file);
		//编写sql语句
		String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
		//预编译sql语句
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setObject(1, "zz");
		ps.setObject(2, "84484@qq.com");
		ps.setObject(3, "1999-9-9");
		ps.setBlob(4, is);
		//执行sql语句
		ps.execute();
		JDBCUtil.closeResourse(conn, ps);
		
	}
}

从数据表中读取blob信息

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.atguigu.bean.Customers;
import com.atguigu.util.JDBCUtil;

public class BlobInsert {

	@Test
	public void testBlobSelect()  {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		InputStream is = null;
		FileOutputStream fos= null;
		try {
			//连接数据库
			conn = JDBCUtil.getConnection();
			//预编译sql语句，填充占位符
			String sql = "select id,name,email,birth,photo from customers where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setObject(1, 25);
			
			rs = ps.executeQuery();
			//如果结果集有数据
			if(rs.next()) {
				//结果集的get方法有两种 1.可以通过索引来得到信息 2.可以通过列的别名得到
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date birth = rs.getDate("birth");
				Blob photo = rs.getBlob("photo");
				
				//得到信息后  new一个customers的对象
				Customers cust = new Customers(id, name, email, birth);
				System.out.println(cust);
				//以流的形式获得blob实例的blob值
				is = photo.getBinaryStream();
				// 创建一个向指定 File 对象表示的文件中写入数据的文件输出流。
				fos = new FileOutputStream("zz.png");
				//创建一个缓冲
				byte[] buffer = new byte[1024];
				
				int len;
				//is一次性读取buffer个字节，如果有数据，把长度返回给len，如果没有数据则返回-1
				while((len = is.read(buffer)) != -1) {
					//将buffer里的值从0-len通过fos输出
					fos.write(buffer, 0, len);
				}
			}
		}  catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			
			try {
				if(is!=null) {
					
					is.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if(fos !=null) {
					
					fos.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			JDBCUtil.closeResourse(conn, ps, rs);
		}	
	
	}
}