package java5.blob;

import java.io.File;
import java.io.FileInputStream;
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 java3.bean.Customer;
import java3.util.JDBCUtils;

/*
 * 	使用preparedStatement操作Blob类型的数据
 */
public class BlobTest {
	//向数据表customers中插入Blob类型的字段
	@Test
	public void testInsert() throws Exception{
		Connection conn = JDBCUtils.getConnedtion();
		String sql="insert into customers(name,email,birth,photo)values(?,?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setObject(1, "lion");
		ps.setObject(2, "1123123@163.com");
		ps.setObject(3, "1111-2-3");
		FileInputStream is = new FileInputStream(new File("2.png"));
		ps.setBlob(4, is);
		
		ps.execute();
		JDBCUtils.closeResource(conn, ps);
		
	}
	
	//查询数据表customers中Blob类型的字段
	@Test
	public void testQuery()  {
		InputStream is=null;
		FileOutputStream fos=null;
		Connection conn =null;
		PreparedStatement ps=null;
		ResultSet rs =null;
		try {
			conn = JDBCUtils.getConnedtion();
			String sql="select id,name,email,birth,photo from customers where id=?";
			ps = conn.prepareStatement(sql);
			ps.setObject(1, 21);
			rs = ps.executeQuery();
			
			if(rs.next()){
				int id=rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date birth = rs.getDate("birth");
				
				Customer customer = new Customer(id, name, email, birth);
				System.out.println(customer);
				
				//将blob类型的字段下载下来，以文件的方式保存在本地
				Blob photo = rs.getBlob("photo");
				is = photo.getBinaryStream();
				fos = new FileOutputStream("3.png");
				byte[] buffer=new byte[1024];
				int len;
				while((len=is.read(buffer))!=-1){
					fos.write(buffer, 0, len);
				}
			
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			
			if(is!=null){
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if(fos!=null){
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			
			JDBCUtils.closeResource(conn, ps, rs);
		}
	}
}