JDBC处理二进制大对象BLOB

1、LOB对象

2、MySql Blob介绍

3、INSERT 或 GET BLOG 实现

/**
     * MySql 插入图片
     * 插入 BLOB 类型的数据必须使用 PreparedStatement，因为BLOB类的数据无法使用字符串拼写。
     * @param sql: INSERT INTO userinfos(userName,password,date,address,picture) VALUES(?,?,?,?,?);
     * @param args: zhangsan,admin,new Date(new java.util.Date().getTime()),chengdu
     */
    public static void mySqlInsertBlob(String sql, Object... args) {
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return;
        }
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            //picture(放在lib文件夹中)
            InputStream inputStream = new FileInputStream("D:\\blob\\viewphoto.jpg");
            preparedStatement.setBlob(5, inputStream);
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection, null, preparedStatement, resultSet);
        }
    }

    /**
     * MySql 插入图片
     * 插入 BLOB 类型的数据必须使用 PreparedStatement，因为BLOB类的数据无法使用字符串拼写。
     * @param sql: "SELECT id,userName,password,address,date,picture FROM userinfos WHERE userName=?"
     * @param args: zhangsan
     */
    public static User mySqlGetBlob(String sql, Object... args) {
        User user = null;
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        List<User> queryResult = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        OutputStream outputStream = null;
        InputStream inputStream = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {//查询出来的结果集的字段顺序与查询sql语句的顺序一致
                user = new User();
                user.setId(resultSet.getInt(1));
                user.setUserName(resultSet.getString(2));
                user.setPassword(resultSet.getString(3));
                user.setAddress(resultSet.getString(4));
                user.setDate(resultSet.getDate(5));
            }
            Blob picture = resultSet.getBlob(6);
            inputStream = picture.getBinaryStream();
            outputStream = new FileOutputStream("D:\\blob\\getViewphoto.jpg");
            byte[] bufferBytes = new byte[1024];
            int len = 0;
            while ((len = inputStream.read(bufferBytes)) != -1) {
                outputStream.write(bufferBytes, 0, len);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection, null, preparedStatement, resultSet);
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return user;
    }