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();}}}}

用工具类中封装的方法存