10.文本大对象操作之BLOB

package jdbc;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;





public class Demo_main {
	/*
	 * BLOB(Binary Large Object)用于存储1大量的二进制,一般通过流来读取数据
	 * 在建表的时候我们可以有多种数据类型,
	 * 例如:tinyblob的最大长度是255即[(2^8)-1]字符的BLOB列
	 * 		blob的最大长度65,535即[(2^16)-1]字符的BLOB列
	 * 		mediumblob的最大长度为16,777,215[(2^24)-1]字符的BLOB列
	 * 		longblob的最大长度是4,294,967,295即4GB也就是[(2^32)-1]字符的BLOB列
	 */
	public static void main(String[] args){
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		Connection c = null;
		ResultSet rs = null;
			try {
//加载驱动类
				Class.forName("com.mysql.jdbc.Driver");
//建立连接
				c = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc的使用","root","root");				
//执行SQL语句					
			//插入数据	
				ps1 = c.prepareStatement("insert into picture (picture) values (?)");
				ps1.setBlob(1, new FileInputStream("E:/博客/JDBC/java/JDBC博客/10.文本大对象操作之BLOB/图片/1.JPG"));
				ps1.execute();
			//获取数据
				
				
				ps2 = c.prepareStatement("select * from picture where id=?");
				ps2.setObject(1, 1);
				rs = ps2.executeQuery();
				while(rs.next()) {
					Blob bl = rs.getBlob("picture");
					InputStream is = bl.getBinaryStream();
					OutputStream os = new FileOutputStream("E:/博客/JDBC/java/JDBC博客/10.文本大对象操作之BLOB/图片/2.JPG");
					int i = 0;
						while((i=is.read())!=-1){
							os.write(i);//用输出流从数据库复制一张出去
							//System.out.print((char)i);//因为控制台不能数出图片,所以只能看到一堆类似于乱码的东西
						}
					System.out.println();
					is.close();
				}
				
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (IOException e) {						
				e.printStackTrace();
			}finally {
//关闭
				try {
					if(ps1 != null) {
						ps1.close();
						}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				try {
					if(ps2 != null) {
						ps2.close();
						}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				try {
					if(c != null) {
						c.close();						
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
	}

}