学习JDBC（第四天）操作BLOB类型字段

操作BLOB类型字段

我们先了解一下什么是BLOB类型字段，看下图

BLOB类型就是在数据库中插入图片，视频等信息。

Mysql中四种BLOB类型（图片一般用MediumBlob，视频用LongBlob)

对代码进行了解

//主要代码块
public class BlobTest {

	//向数据表customers中插入Blob类型字段
	
	public void testInsert() throws Exception{
		Connection coon=JDBCUtils.getConnection();
		String sql="insert into customers(name,email,birth,photo)values(?,?,?,?)";
		PreparedStatement ps=coon.prepareStatement(sql);
		
		ps.setObject(1, "名字");
		ps.setObject(2, "qq.com");
		ps.setObject(3, "2001-1-16");
		FileInputStream is=new FileInputStream(new File("sp20230719_071013_046.png"));
		ps.setBlob(4, is);
		
		ps.execute();
		JDBCUtils.closeResource(coon, ps);
	}

}

new File中为图片路径

JDBCUtils中代码块

package com.atue3.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.atguigu2.connection.ConnectionTest;
import com.mysql.cj.xdevapi.Statement;

//操作数据库的工具类
public class JDBCUtils {
	//获取数据库连接
	public static Connection getConnection() throws Exception
	{
		InputStream is=ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");        
		               //ClassLoader.getSystemClassLoader()
		Properties pros=new Properties();
		pros.load(is);
		
		String user=pros.getProperty("user");
		String password=pros.getProperty("password");
		String url=pros.getProperty("url");
		String driverClass=pros.getProperty("driverClass");
		
		
		Class.forName(driverClass);
		
		Connection coon=DriverManager.getConnection(url,user,password);
		
		return coon;
		
	}
	
	//关闭资源的操作
	public static void closeResource(Connection coon,PreparedStatement ps)
	{
		try {
			if(ps!=null)
				ps.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
		try {
			if(coon!=null)
				coon.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
	}
	public static void closeResource(Connection coon,PreparedStatement ps,ResultSet rs)
	{
		try {
			if(ps!=null)
				ps.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
		try {
			if(coon!=null)
				coon.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
		try {
			if(rs!=null)
				rs.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
	}
	

}

接下来我们学习从数据表中读取Blob类型数据

public void testQuery() throws Exception{
		Connection coon=null;
		PreparedStatement ps=null;
		InputStream is=null;
		FileOutputStream fos=null;
		//图片获取用流来获取
		ResultSet rs=null;
		try {
			coon = JDBCUtils.getConnection();
			String sql="select id,name,photo from customer where id=?";
			ps = coon.prepareStatement(sql);
			ps.setInt(1, 21);
			is = null;
			fos = null;
			rs = ps.executeQuery();
			if(rs.next())
			{
				int id=rs.getInt(1);
				String name=rs.getString(2);
				Customer cust=new Customer(id,name);
				System.out.println(cust);
				//以上为前面学习


				//将Blob类型字段下载下来，以文件方式保存到本地
				Blob photo=rs.getBlob("photo");
				is=photo.getBinaryStream();
				fos=new FileOutputStream("---本地路径----");//图片保存到此路径
				byte[] buffer=new byte[1024];
				int len;
				while((len=is.read(buffer))!=-1)
				{
					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)
			{
				e.printStackTrace();
			}
			try {
				if(fos!=null)
					fos.close();
			}catch(IOException e)
			{
				e.printStackTrace();
			}
			
			JDBCUtils.closeResource(coon, ps,rs);
		}
		
	}

JDBCUtils为中间部分共用代码块，这里就不再强调