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

痛定思痛。 2023-09-29 09:15 7阅读 0赞

在本文档中，我将指导您上载文件并将其存储在数据库中数据类型为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消耗内存少于使用字节数组。

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAYWxsd2F5Mg_size_20_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAYWxsd2F5Mg_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAYWxsd2F5Mg_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/6d93524e2adc47e882f105a0a5542ac6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAYWxsd2F5Mg==,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAYWxsd2F5Mg_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/b72e095a50ad4f19852e9fb3cc084718.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAYWxsd2F5Mg==,size_20,color_FFFFFF,t_70,g_se,x_16