数据库（一）

读写LOB

等你下课-周杰伦

LOB：在SQL中，二进制大对象称为BLOB，字符型大对象称为CLOB。

要读取LOB，需要执行select语句后，在ResultSet上调用getBlob和getClob方法，这样就可以获得Blob和Clob类型的对象。可以调用getBytes或getInputStream来从Blob中获取二进制数据。

如果你有一张保存图书封面图像的表，那么可以像下面这样获取

先mldn数据库下创建一个表

create table avator(
    id int(4) primary key auto_increment,
    avator mediumblob
);

提示：直接用二进制字节流输出即可，不要用ImageIO+image流输出！不要用ImageIO+image流输出！不要用ImageIO+image流输出！重要的事情说三遍

关于字节流可能还有需要编码的问题，不过初次测试一切求简

示例程序如下：

package lob;

import java.awt.Image;
import java.awt.Toolkit;
import java.awt.image.BufferedImage;
import java.awt.image.RenderedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Properties;

import javax.imageio.ImageIO;



public class LOB {

	public static void main(String[] args) throws Exception{
		// TODO Auto-generated method stub
		insertBlob(5);
		selectBlob(5);
		
	}
	public static Connection getConnection() throws SQLException, IOException
	{
	      Properties props = new Properties();
	      FileInputStream in = new FileInputStream("db.properties");
	      props.load(in);
	      in.close();

	      String drivers = props.getProperty("driver");
	      if (drivers != null) System.setProperty("driver", drivers);
	      String url = props.getProperty("url");
	      String username = props.getProperty("username");
	      String password = props.getProperty("password");

	      return DriverManager.getConnection(url, username, password);
	}
	public static boolean insertBlob(int id)throws Exception{
		boolean flag=false;
		Connection conn=getConnection();
		Blob blob=conn.createBlob();
		int offset=1;
		//the position in the BLOB value at which to start writing; the first position is 1
		InputStream in=new FileInputStream("fiveCHessBourd.jpg");
		OutputStream out = blob.setBinaryStream(offset);
        //向Blob输出
		byte [] data=new byte[1024];
		int len=-1;
		while((len=in.read(data))!=-1){
			out.write(data, 0, len);
			//System.out.println(Arrays.toString(data));
		}
		in.close();
		out.close();
        //一定要在sql语句执行之前关闭
		String sql="insert into avator (id , avator)value(?,?)";
		PreparedStatement stat=conn.prepareStatement(sql);
		stat.setInt(1, id);
		stat.setBlob(2, blob);
		if(stat.executeUpdate()>0){
			//System.out.println("插入成功");
			flag=true;
		}
		
		stat.close();
		conn.close();
		return flag;
	}
	public static void selectBlob(int id)throws Exception{
		Connection conn=getConnection();
		PreparedStatement stat=conn.prepareStatement("select avator from avator where id=?");
		stat.setInt(1,id);
		ResultSet result = stat.executeQuery();
		if(result.next()){
			
			Blob blob=result.getBlob(1);
			//System.out.println(blob);
			//System.out.println(result.getBinaryStream(1));
            //记住这个技巧：打印类的路径可以看到是谁为它实例化
			InputStream in=blob.getBinaryStream();
			byte [] data=new byte[1024];
			OutputStream out=new FileOutputStream("e:/apple1.jpg");
			int len=-1;
			while((len=in.read(data))!=-1){
				out.write(data, 0, len);
				System.out.println(Arrays.toString(data));
			}
			in.close();
			out.close();
			result.close();
			stat.close();
			conn.close();	
		}
		result.close();
		stat.close();
		conn.close();
	}
}

输出二进制数据如下

错误的方向。。。ImageIO，

//RenderedImage im=(BufferedImage)ImageIO.read(new File("fiveCHessBourd.jpg"));
//根据文件实例化一个Image对象，并读入内存
//ImageIO.write(im, "jpg", out);
//将二进制的图像文件写入BLOB中
//ImageIO.write((BufferedImage)image, "jpg", new File());