JDBC操作BLOB类型字段

JDBC中Statement接口本身不能直接操作BLOB数据类型 

操作BLOB数据类型需要使用PreparedStatement或者CallableStatement(存储过程)

这里演示通过PreparedStatement操作数据库BLOB字段 

设置最大传入字节  一般是4M  可以通过以下命令修改

    set global max_allowed_packet=1024*1024*32;

这里封装的方法本身就可以对Blob字段进行   存   取 

数据库

实体类

JDBC的封装类

package com.utils;
 
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
 
/**
 * @author hrui
 * @date 2023/10/13 13:49
 */
public class DBUtils {
    private static ResourceBundle bundle=ResourceBundle.getBundle("jdbc");
    private static String driver=bundle.getString("jdbc.driver");
    private static String url=bundle.getString("jdbc.url");
    private static String username=bundle.getString("jdbc.username");
    private static String password=bundle.getString("jdbc.password");
 
    static{
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
 
    //通用查询多个
    public static <T> List<T> selectList(Class<T> clazz, String sql, Object...args){
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
 
        try {
            conn=DBUtils.getConnection();
            ps=conn.prepareStatement(sql);
 
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<T> list=new ArrayList<>();
            while(rs.next()){
                T t = clazz.newInstance();
                for(int i=0;i<columnCount;i++){
                    Object object = rs.getObject(i + 1);
                    //String columnName = metaData.getColumnName(i + 1); 这个方法返回实际列名
                    String columnLabel = metaData.getColumnLabel(i + 1);//该方法返回别名,没有别名就返回列名
                    columnLabel = getString(columnLabel);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,object);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
 
        }finally {
            DBUtils.closed(conn,ps,rs);
        }
        return null;
    }
 
    private static String getString(String columnLabel) {
        if (columnLabel.contains("_")) {
            int index = columnLabel.indexOf("_");
            String replace = columnLabel.replace("_", "");
            char c = Character.toUpperCase(replace.charAt(index));
            columnLabel = replace.substring(0, index) + c + replace.substring(index + 1);
        }
        return columnLabel;
    }
 
    //通用查询单个
    public static <T> T selectOne(Class<T> clazz,String sql,Object...args){
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
 
        try {
            conn=DBUtils.getConnection();
            ps=conn.prepareStatement(sql);
 
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            if(rs.next()){
                T t = clazz.newInstance();
                for(int i=0;i<columnCount;i++){
                    Object object = rs.getObject(i + 1);
                    //System.out.println(object.getClass());
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    columnLabel = getString(columnLabel);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,object);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
 
        }finally {
            DBUtils.closed(conn,ps,rs);
        }
        return null;
    }
 
 
 
 
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }
    //通用增删改方法
    public static int update(String sql,Object...args){
        Connection conn =null;
        PreparedStatement ps=null;
        int count=0;
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1, args[i]);
            }
            count = ps.executeUpdate();
            //ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closed(conn,ps,null);
        }
 
        return count;
    }
 
 
    public static void closed(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
}

用工具类中封装的方法    存

取

下面详解下PrepareStatement单独存取数据库Blob字段  

有很多方法

读取

像下面这样也行 这样就保存文件了