mysql数据库中使用blob存储- 使用
base64加密图片数据
前言
这个方法并不好，因为传输的数据量还是蛮大的，可以存一些诸如头像的小图片，但是如果要存较大的图片会很慢。
不过只是课程作业中简单的功能，这样子简单又快捷，无所谓啦。
详情
数据库
首先设计数据库表，这里使用longblob
A BLOB is a binary large object that can hold a variable amount of data.
The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.
These differ only in the maximum length of the values they can hold.
BLOB是存储二进制大对象的。可以用来存储图片、视频、音频等数据。
根据可以存储文件大小的不同，分为TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB。
| 类型 | 可存储大小 |
|---|---|
| TINYBLOB | 0-255Byte |
| BLOB | 0-65KB |
| MEDIUMBLOB | 0-16MB |
| LONGBLOB | 0-4GB |
| 网络上流传甚广的是上面的表,但是和下面官方文档里有些出入,不过问题不大.大体意思是相似的. | |
| mysql不同数据类型的存储空间需求 |
程序
存入数据库
本地图片测试
使用本地图片做测试，确保没有问题。
@Testvoid upload() {/*加载驱动*/try {Class.forName("com.mysql.cj.jdbc.Driver");//获取连接String url = "jdbc:mysql://localhost:3306/findperson?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";String user = "root";String password = "pwd";try {Connection connection = DriverManager.getConnection(url, user, password);/*插入图片*/byte[] arr = getImgStr("D:\\Code\\Resource\\img\\comment-avatar\\2.jpg");Blob blob = connection.createBlob();blob.setBytes(1, arr);String sql = "insert into pictures (sid,pic) values(1,?)";PreparedStatement ps = connection.prepareStatement(sql);ps.setBlob(1, blob);ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();}} catch (ClassNotFoundException | IOException e) {e.printStackTrace();}}前后端数据交流
upload-cn#components-upload-demo-avatar
使用antd上传组件，相关代码这里复制，这里就不凑字数了。
前端传来的数据是用MultipartFile接收的。
public void uploadUserAvatar(Integer id,MultipartFile avatar) throws IOException, SQLException {byte[] bytes = avatar.getBytes();upload(id,bytes);//函数如下}
void upload(Integer id, byte[] bytes) {try {Class.forName(driver);//获取连接String url = durl;String user = duser;String password = dpassword;try {Connection connection = DriverManager.getConnection(url, user, password);/*插入图片*/byte[] arr = bytes;Blob blob = connection.createBlob();blob.setBytes(1, arr);String sql = "insert into pictures (sid,pic) values(?,?)";PreparedStatement ps = connection.prepareStatement(sql);ps.setInt(1,id);ps.setBlob(2, blob);ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();}} catch (ClassNotFoundException e) {e.printStackTrace();}}
从数据库中提出并展示
@Overridepublic MockMultipartFile getStudentAvatar(Integer sid) throws SQLException, ClassNotFoundException {byte[] bytes = read(sid);MockMultipartFile multipartFile = new MockMultipartFile("avatar.png",bytes);return multipartFile;}
public static byte[] read(Integer sid) throws ClassNotFoundException, SQLException {Class.forName(driver);//获取连接String url = durl;String user = duser;String password = dpassword;Connection connection = DriverManager.getConnection(url, user, password);String sql = ("select pic from pictures where sid = ?");//根据需求自己写PreparedStatement statement = null;ResultSet resultSet = null;byte[] bytes = null;try {statement = connection.prepareStatement(sql);statement.setInt(1,sid);resultSet = statement.executeQuery();//创建blob接受resultset得到的blob数据while (resultSet.next()) {Blob blob = resultSet.getBlob("pic");bytes = blob.getBytes(1, (int) blob.length());}} catch(SQLException throwables) {throwables.printStackTrace();}return bytes;}
控制层
@GetMapping(value = "/user-manage/avatar",produces = MediaType.IMAGE_PNG_VALUE)@ResponseBodyString getStudentAvatar() throws SQLException, ClassNotFoundException, IOException {MockMultipartFile studentAvatar = userService.getStudentAvatar(currentUser.getId());byte[] bytes = studentAvatar.getBytes();//图片的字节数组BASE64Encoder encoder = new BASE64Encoder();String data = encoder.encode(bytes);return data;}
前端获取数据，注意拼接一下
axios.get("http://localhost:8080/user-manage/avatar").then(res => {console.log("获取的头像数据",res)setImageUrl("data:image/jpeg;base64,"+res.data)})
直接就可以展示
<img
src={imageUrl}
alt="avatar"
style={{width: '100%',
}}
/>
