使用Java Servlet上传文件到数据库显示文件列表并下载文件

allway2

已于 2022-04-08 14:23:05 修改

阅读量1.4k

点赞数 3

文章标签： java

于 2022-04-08 14:21:20 首次发布

本文链接：https://blog.csdn.net/allway2/article/details/124040354

版权

在本文档中，我将指导您上载文件并将其存储在数据库中数据类型为BLOB的列中，然后从数据库中数据类型为BLOB的列中下载文件。

创建数据库

您可以使用任何数据库，下面是用于创建upload表的脚本，该表用于存储您上载的数据文件。

CREATE TABLE `upload` (
	`id` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
	`file` LONGBLOB NOT NULL,
	`filename` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

上传文件并存储在数据库中

fileupload.jsp

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ page contentType="text/html;charset=UTF-8"%>
<%
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
%>


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 
<html>
    <head>
        <title>文件上传到数据库</title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>
    <body>
        <form method="POST" action="FileUpload" enctype="multipart/form-data" >
			<table>
        		<tr><td>Id</td>
        			<td><input type="text" name="id" /></td>
        		<tr>
        			<td>File</td>
           			<td><input type="file" name="file" id="file" /> </td>
        		</tr>
        		<tr>
        	<td colspan="2">
            	<input type="submit" value="Upload" name="upload" id="upload" /> </td>
        	</tr>
   	</table>
    </form>
   </body>
</html>

FileUpload.java

使用Stream

//中文
package com.example;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;

@WebServlet("/FileUpload")
@MultipartConfig
public class FileUpload extends HttpServlet {

    /**
     *
     */
    private static final long serialVersionUID = 1L;

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");

        final Part filePart = request.getPart("file");
        String id = request.getParameter("id");

        InputStream FileBytes = null;
        final PrintWriter writer = response.getWriter();
        Connection con = null;
        Statement stmt = null;

        try {

            //if (!filePart.getContentType().equals("image/jpeg"))
            //  {
            //             writer.println("<br/> Invalid File");
            //             return;
            //  }
            // else if (filePart.getSize()>1048576 ) { //2mb
            //     {
            //    writer.println("<br/> File size too big");
            //    return;
            //     }
            // }
            String filename = filePart.getSubmittedFileName();
            FileBytes = filePart.getInputStream(); // to get the body of the request as binary data

            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
            } catch (Exception e) {
                System.out.println(e);
                System.exit(0);
            }
            int success = 0;
            PreparedStatement pstmt = con.prepareStatement("INSERT INTO upload VALUES(?,?,?)");
            pstmt.setString(1, id);
            pstmt.setBinaryStream(2, FileBytes); //Storing binary data in blob field.
            pstmt.setString(3, filename); //Storing binary data in blob field.
            success = pstmt.executeUpdate();
            if (success >= 1) {
                System.out.println("Data Stored");
            }
            con.close();

            writer.println("<br/> File Successfully Uploaded<br/><a href='.'>return</a>");

        } catch (FileNotFoundException fnf) {
            writer.println("You  did not specify a file to upload");
            writer.println("<br/> ERROR: " + fnf.getMessage());

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (con != null) {
                // closes the database connection
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }

            if (FileBytes != null) {
                FileBytes.close();
            }
            if (writer != null) {
                writer.close();
            }
        }

    }

}

从显示文件列表并从数据库中下载文件

Upload.java

package com.example;

public class Upload {

    private String id;
    private String filename;

    public Upload() {
    }

    public Upload(String id, String filename) {
        this.id = id;
        this.filename = filename;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getFilename() {
        return filename;
    }

    public void setFilename(String filename) {
        this.filename = filename;
    }

}

UploadDAO.java

package com.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UploadDAO {

    public static Connection getConnection() {
        Connection con = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai",
                    "root", "root");
        } catch (Exception e) {
            System.out.println(e);
        }
        return con;
    }

    public static List<Upload> listAllUploads() throws SQLException {
        List<Upload> listUpload = new ArrayList<>();

        String sql = "SELECT id,filename FROM upload";

        Connection jdbcConnection = getConnection();

        Statement statement = jdbcConnection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()) {

            String id = resultSet.getString("id");
            String filename = resultSet.getString("filename");

            Upload upload = new Upload(id, filename);
            listUpload.add(upload);
        }

        resultSet.close();
        statement.close();

        jdbcConnection.close();

        return listUpload;
    }

}

index.jsp

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ page contentType="text/html;charset=UTF-8"%>
<%
    request.setCharacterEncoding("UTF-8");
    response.setCharacterEncoding("UTF-8");
    response.setContentType("text/html; charset=UTF-8");
%>
<!DOCTYPE html>

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
        <title>View Uploads</title>
    </head>
    <body>

        <%@page import="com.example.*,java.util.*"%>
        <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

        <h1>Uploads List</h1>

        <%
            List<Upload> list = UploadDAO.listAllUploads();
            request.setAttribute("list", list);
        %>

        <table border="1" width="90%">
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Download</th>

            </tr>
            <c:forEach items="${list}" var="u">
                <tr>
                    <td>${u.getId()}</td>
                    <td>${u.getFilename()}</td>
                    <td><a href="DBFileDownload?id=${u.getId()}">Download</a></td>
                </tr>
            </c:forEach>
        </table>
        <br />
        <a href="fileupload.jsp">Add New Upload</a>

    </body>
</html>

DBFileDownloadServlet.java

使用Stream

//中文

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.net.URLEncoder;
import java.io.InputStream;

/**
 * Servlet implementation class GetDetails
 */
@WebServlet("/DBFileDownload")
public class DBFileDownloadServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");

        String id = request.getParameter("id") != null ? request.getParameter("id") : "NA";

        ServletOutputStream sos;
        Connection con = null;
        PreparedStatement pstmt = null;




        sos = response.getOutputStream();




        ResultSet rset = null;
        try {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
            } catch (Exception e) {
                System.out.println(e);
                System.exit(0);
            }
            pstmt = con.prepareStatement("Select file,filename from upload where id=?");
            System.out.println("Select file,filename from upload where id=" + id.trim());
            pstmt.setString(1, id.trim());
            rset = pstmt.executeQuery();
            if (rset.next()) {
                response.setContentType("APPLICATION/OCTET-STREAM");

                response.setHeader("Content-disposition", "inline; filename*=UTF-8''" + URLEncoder.encode(rset.getString("filename"), "UTF-8"));

                InputStream inputStream = rset.getBinaryStream("file");

                int i;
                while ((i = inputStream.read()) != -1) {
                    sos.write(i);
                }

                System.out.println(rset.getBytes("file"));
                System.out.println(rset.getString("filename"));
            } else
                return;

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (con != null) {
                // closes the database connection
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }

        }

        sos.flush();
        sos.close();

    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

}

使用Stream消耗内存少于使用字节数组。