将数据以二进制和URL的方式存入数据库并且请求出json

最新推荐文章于 2024-01-04 14:42:56 发布

surpass0728

最新推荐文章于 2024-01-04 14:42:56 发布

阅读量1k

点赞数

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/surpass0728/article/details/78574862

版权

本文探讨了两种将文件存入数据库的方法：以URL和二进制字节数组形式。虽然二进制方式占用更多内存，但URL存储更常见。详细介绍了如何使用SpringMVC的MultipartFile进行文件上传，并分别展示了URL存储和Blob类型存储的实现过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在当下文件存入数据库的格式无非有两种：
一种是存入直白的URl
另一种是将文件以二进制字节数组的形式存入数据库
相对来说二进制字节数组会占用较大的内存空间，所以当前普遍用URL进行存储与请求

第一种URL形式

1.简单上传文件界面index.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
    <!--enctype表示上传的将会是二进制流的格式，以规定的二进制进行上传，便于服务器处理-->
    <form action="testFileUpload" method="post" enctype="multipart/form-data">
        File:<input type="file" name="file"/><br>
        Desc:<input type="text" name="desc"/>
        <input type="submit" value="submit"/>
    </form>

</body>
</html>

2.上传文件控制层controller

(使用当下springMVC下MultipartFile完成文件的上传)

获取文件的URl进行存储

package com.springMVC;

import java.io.File;
import java.io.IOException;

import java.sql.ResultSet;
import java.sql.SQLException;


import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.multipart.MultipartFile;


import com.springFile.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;


@Controller
public class UpdateFile {
    @Autowired  
    private HttpServletRequest request;


     PreparedStatement pstmt = null;
     ResultSet rs = null;

    //文件上传
    @RequestMapping("/testFileUpload")
    public String testFileUpload(@RequestParam("desc") String desc,
            @RequestParam("file") MultipartFile file) throws IOException {

            String filePath = request.getSession().getServletContext().getRealPath("/") + "\\fileUpload\\" +  file.getOriginalFilename();//感觉是不是对文件名裁剪空格后保存比较好？


            //转存文件
            try {
                file.transferTo(new File(filePath));
            } catch (IOException e) {
                e.printStackTrace();
            }
            System.out.println("path="+filePath);
            try {
                DBUtil db=new DBUtil();
                Connection conn=(Connection) db.openConnection();
                String sql="insert into filetable(id,name,fileURL)"
                        +" values(?,?,?)";
                PreparedStatement preparedStatement  = (PreparedStatement) conn.prepareStatement(sql);
                preparedStatement.setInt(1, 1);
                preparedStatement.setString(2,"picture");
                preparedStatement.setString(3,filePath);
                //System.out.println("存入数据库了");
                preparedStatement.executeUpdate();
                preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }



        return "success";
    }

}

3.实现请求文件的json数据

package com.springMVC;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;


import net.sf.json.JSONObject;

import com.springFile.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;


@Controller
public class DownloadFile {


     PreparedStatement pstmt = null;
     ResultSet rs = null;

    //文件请求
    @RequestMapping("/testDownload")
    public void testFileDownload(HttpServletRequest request,HttpServletResponse response) throws IOException, SQLException { 
           request.setCharacterEncoding("utf-8");

           JSONObject json=new JSONObject ();
           String id=request.getParameter("id");
           DBUtil db=new DBUtil();
           Connection conn=(Connection) db.openConnection();
           String sql="select name,fileURL from filetable where id="+id;
           pstmt=conn.prepareStatement(sql);
           rs=pstmt.executeQuery();
           if(rs.next()) {
               String name=rs.getString(1);
               String fileURL=rs.getString(2);
               rs.close();
               pstmt.close();
               conn.close();
               json.put(name, fileURL);
               try {
                   response.setContentType("text/html;charset=utf-8");
                   PrintWriter out=response.getWriter();
                   out.println(json.toString());
            } catch (Exception e) {
                    e.printStackTrace();
            }
           }


    }

}

第二种以字节数组存储类型为Blob类型

1.还是使用MultipartFile实现文件的上传

数据实现：文件—流—字节数组

package com.springMVC;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;


import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.multipart.MultipartFile;


import com.springFile.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;


@Controller
public class UploadBlob {
    @Autowired  
    private HttpServletRequest request;


     PreparedStatement pstmt = null;
     ResultSet rs = null;

    //文件上传
    @RequestMapping("/testBolb")
    public String testFileUpload(@RequestParam("desc") String desc,
            @RequestParam("file") MultipartFile file) throws IOException {

            String filePath = request.getSession().getServletContext().getRealPath("/") + "\\fileUpload\\" +  file.getOriginalFilename();//感觉是不是对文件名裁剪空格后保存比较好？

            //转存文件
            try {
                file.transferTo(new File(filePath));
            } catch (IOException e) {
                e.printStackTrace();
            }
            System.out.println("path="+filePath);
            File files=new File(filePath);
            InputStream inputStream = new FileInputStream(files); 
            byte data[]=new byte[] {};
            //将文件保存到字节数组中
            data = inputStreamToByte(inputStream);

            try {
                DBUtil db=new DBUtil();
                Connection conn=(Connection) db.openConnection();
                String sql="insert into imagetable(ImageName,picture)"
                        +" values(?,?)";
                PreparedStatement preparedStatement  = (PreparedStatement) conn.prepareStatement(sql);
                preparedStatement.setString(1,"picture");
                preparedStatement.setBytes(2, data);
                //System.out.println("存入数据库了");
                preparedStatement.executeUpdate();
                preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }



        return "success";
    }
  //将文件保存到字节数组中
    private byte[] inputStreamToByte(InputStream inputStream) throws IOException {

        ByteArrayOutputStream bAOutputStream = new ByteArrayOutputStream();
        int ch;
        while((ch=inputStream.read())!=1) {
            bAOutputStream.write(ch);
        }
        byte data [] =bAOutputStream.toByteArray();
        bAOutputStream.close();
        System.out.println("字节数组是"+data.toString());
        return data;
    }

}

2.直接获取数据库中的Blob类型的字节数组

package com.springMVC;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;


import net.sf.json.JSONObject;

import com.springFile.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;


@Controller
public class DownloadBlob {


     PreparedStatement pstmt = null;
     ResultSet rs = null;

    //文件请求
    @RequestMapping("/testDownloadBlob")
    public void testFileDownload(HttpServletRequest request,HttpServletResponse response) throws IOException, SQLException { 
           request.setCharacterEncoding("utf-8");

           JSONObject json=new JSONObject ();
           String ImageName=request.getParameter("ImageName");
           DBUtil db=new DBUtil();
           Connection conn=(Connection) db.openConnection();
           String sql="select picture from imagetable where ImageName="+ImageName;
           pstmt=conn.prepareStatement(sql);
           rs=pstmt.executeQuery();
           if(rs.next()) {
               byte[] picture=rs.getBytes(1);
               rs.close();
               pstmt.close();
               conn.close();
               json.put(ImageName, picture);
               try {
                   response.setContentType("text/html;charset=utf-8");
                   PrintWriter out=response.getWriter();
                   out.println(json.toString());
            } catch (Exception e) {
                    e.printStackTrace();
            }
           }


    }

}

这里写图片描述