JDBC学习（第二天）-2-第四章操作BLOB类型字段

 数据库中往往带有照片、视频等大数据文件。那么我们如何处理呢？

package com.atguigu5.blobHJR;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
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 java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.atguigu3.bean.Customer;
import com.atguigu3.preparedstatement.HJR.JDBCutilHJR;
import com.atguigu3.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;

/**
 * 
 * @Description 测试使用PreparedStatement操作Blob类型的数据
 * @author shkstart  Email:shkstart@126.com
 * @version 
 * @date 下午4:08:58
 *
 */
public class BlobTestHJR {
	
	//向数据表customers中插入Blob类型的字段
	@Test
	public void testInsert() throws Exception{  //要用try-catch
		//增
		String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
		FileInputStream is = new FileInputStream(new File("zhangyuhao.jpg"));
		Update(sql, "刘川锋","lcf@126.com","1998-10-01",is);
		is.close();
		//删
		//String sqlString = "delete from customers where id = ?";
		//Update(sqlString, 25);
	}
	public void Update(String sql,Object...args){
		//1.获取数据库的连接
		Connection cnn = null;
		//2.预编译sql语句，返回PreparedStatement的实例
		PreparedStatement ps = null;
		try {
			cnn = JDBCutilHJR.getConnection();
			ps = cnn.prepareStatement(sql);
			//3.填充占位符
			for(int i=0;i< args.length;i++) {
				ps.setObject(i+1, args[i]);
			}
			//4.执行
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//5.资源的关闭
			JDBCutilHJR.closeResource(cnn, ps);		
		}
	}
	
	//查询数据表customers中Blob类型的字段
	@Test
	public void testQuery() throws Exception{
			String sql = "select id,name,email,birth from customers where id = ?"; 
			//因为方法中没有对图片进行处理，故select语句中不能加入图片
			List<Customer> list = Query(Customer.class,sql,12);
				for (Customer customer : list) {
					System.out.print(customer);
				}
			}
	public <T> List<T> Query(Class<T> clazz,String sql,Object...args) throws Exception{
		//1.
		Connection conn = JDBCutilHJR.getConnection();
		//2.
		PreparedStatement ps =  conn.prepareStatement(sql);
		//3.
		for(int i=0;i<args.length;i++) {
			ps.setObject(i+1, args[i]);
		}
		//4.
		ResultSet rs = ps.executeQuery();
		//4-1 元数据
		ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
		//col
		int colCount = rsmd.getColumnCount();
		//集合
	    ArrayList<T> list= new ArrayList();
	    while (rs.next()) {
	    	T t =clazz.newInstance();
	    	for(int i= 0;i<colCount;i++) {
	    		Object colValue = rs.getObject(i+1);
	    		String colLabel = rsmd.getColumnLabel(i+1);
	    		//反射
	    		Field field = clazz.getDeclaredField(colLabel);
	    		field.setAccessible(true);
	    		field.set(t, colValue);	    		
	    	} 	
	    	list.add(t);
		}
		return list;
	}
	//查询数据表customers中Blob类型的字段
		@Test
		public void testQuery1(){
			Connection conn = null;
			PreparedStatement ps = null;
			InputStream is = null;
			FileOutputStream fos = null;
			ResultSet rs = null;
			try {
				conn = JDBCUtils.getConnection();
				String sql = "select id,name,email,birth,photo from customers where id = ?";
				//可以对图片进行处理，
				ps = conn.prepareStatement(sql);
				ps.setInt(1, 16);
				rs = ps.executeQuery();
				if(rs.next()){
		//			方式一：
		//			int id = rs.getInt(1);
		//			String name = rs.getString(2);
		//			String email = rs.getString(3);
		//			Date birth = rs.getDate(4);
					//方式二：
					int id = rs.getInt("id");
					String name = rs.getString("name");
					String email = rs.getString("email");
					Date birth = rs.getDate("birth");
					
					Customer cust = new Customer(id, name, email, birth);
					System.out.println(cust);
					
					//将Blob类型的字段下载下来，以文件的方式保存在本地
					Blob photo = rs.getBlob("photo");
					is = photo.getBinaryStream();
					fos = new FileOutputStream("zhangyuhao.jpg");
					byte[] buffer = new byte[1024];
					int len;
					while((len = is.read(buffer)) != -1){
						fos.write(buffer, 0, len);
					}
					
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally{
				
				try {
					if(is != null)
						is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
				
				try {
					if(fos != null)
						fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
				
				JDBCUtils.closeResource(conn, ps, rs);
			}
			
			
		}
		
}