Hochladen und Herunterladen von Dateien aus der Datenbank mit Java Servlet

1- Database

** ORACLE **
create table ATTACHMENT
(
  ID          NUMBER(19) not null,
  FILE_NAME   VARCHAR2(50) not null,
  FILE_DATA   BLOB not null,
  DESCRIPTION VARCHAR2(255)
) ;


alter table ATTACHMENT
  add constraint ATTACHMENT_PK primary key (ID) ;

 
** MYSQL **
create table ATTACHMENT
(
  ID          BIGINT not null,
  FILE_NAME   VARCHAR(50) not null,
  FILE_DATA   BLOB not null,
  DESCRIPTION VARCHAR(255)
) ;


alter table ATTACHMENT
  add constraint ATTACHMENT_PK primary key (ID) ;
** SQL SERVER **
create table ATTACHMENT
(
  ID          BIGINT not null,
  FILE_NAME   VARCHAR(50) not null,
  FILE_DATA   Varbinary(max) not null,
  DESCRIPTION VARCHAR(255)
) ;


alter table ATTACHMENT
  add constraint ATTACHMENT_PK primary key (ID) ;

2- Aus Database hochladen und herunterladen

Im Dokument führe ich Sie, eine File hochzuladen und in Database zu speichern danach die Daten aus Database herunterzuladen. Die hochgeladeten File Daten wird in der Spalte mit dem Datentyp BLOB gespeichert.
Sie können irgendeine Database benutzen. Oben ist es ein Script der Tabellenerstellung ATTACHMENT. Diese Tabelle archiviert die Filedaten, die von Ihnen hochgeladet werden
Sie können mehr studieren, wie man die JDBC benutzt um mit Database zu verbinden bei ...

3- In DB hochladen und speichern

UploadToDBServlet.java
package org.o7planning.servletexamples;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.RequestDispatcher;
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;

import org.o7planning.tutorial.jdbc.ConnectionUtils;

@WebServlet("/uploadToDB")
@MultipartConfig(fileSizeThreshold = 1024 * 1024 * 2, // 2MB
        maxFileSize = 1024 * 1024 * 10, // 10MB
        maxRequestSize = 1024 * 1024 * 50) // 50MB
public class UploadToDBServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        RequestDispatcher dispatcher = request.getServletContext().getRequestDispatcher("/WEB-INF/jsps/uploadToDB.jsp");

        dispatcher.forward(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        Connection conn = null;
        try {
            // Connection to Database
            // (See more in JDBC Tutorial).
            conn = ConnectionUtils.getMyConnection();
            conn.setAutoCommit(false);

            String description = request.getParameter("description");

            // Part list (multi files).
            for (Part part : request.getParts()) {
                String fileName = extractFileName(part);
                if (fileName != null && fileName.length() > 0) {
                    // File data
                    InputStream is = part.getInputStream();
                    // Write to file
                    this.writeToDB(conn, fileName, is, description);
                }
            }
            conn.commit();

            // Upload successfully!.
            response.sendRedirect(request.getContextPath() + "/uploadToDBResults");
        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("errorMessage", "Error: " + e.getMessage());
            RequestDispatcher dispatcher = getServletContext().getRequestDispatcher("/WEB-INF/jsps/uploadToDB.jsp");
            dispatcher.forward(request, response);
        } finally {
            this.closeQuietly(conn);
        }
    }

    private String extractFileName(Part part) {
        // form-data; name="file"; filename="C:\file1.zip"
        // form-data; name="file"; filename="C:\Note\file2.zip"
        String contentDisp = part.getHeader("content-disposition");
        String[] items = contentDisp.split(";");
        for (String s : items) {
            if (s.trim().startsWith("filename")) {
                // C:\file1.zip
                // C:\Note\file2.zip
                String clientFileName = s.substring(s.indexOf("=") + 2, s.length() - 1);
                clientFileName = clientFileName.replace("\\", "/");
                int i = clientFileName.lastIndexOf('/');
                // file1.zip
                // file2.zip
                return clientFileName.substring(i + 1);
            }
        }
        return null;
    }

    private Long getMaxAttachmentId(Connection conn) throws SQLException {
        String sql = "Select max(a.id) from Attachment a";
        PreparedStatement pstm = conn.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        if (rs.next()) {
            long max = rs.getLong(1);
            return max;
        }
        return 0L;
    }

    private void writeToDB(Connection conn, String fileName, InputStream is, String description) throws SQLException {

        String sql = "Insert into Attachment(Id,File_Name,File_Data,Description) " //
                + " values (?,?,?,?) ";
        PreparedStatement pstm = conn.prepareStatement(sql);

        Long id = this.getMaxAttachmentId(conn) + 1;
        pstm.setLong(1, id);
        pstm.setString(2, fileName);
        pstm.setBlob(3, is);
        pstm.setString(4, description);
        pstm.executeUpdate();
    }

    private void closeQuietly(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
        }
    }

}
UploadToDBResultsServlet.java
package org.o7planning.servletexamples;

import java.io.IOException;

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

@WebServlet("/uploadToDBResults")
public class UploadToDBResultsServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
 

    public UploadToDBResultsServlet() {
        super();
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        RequestDispatcher dispatcher
            = request.getServletContext().getRequestDispatcher("/WEB-INF/jsps/uploadToDBResults.jsp");

        dispatcher.forward(request, response);
    }

}
/WEB-INF/jsps/uploadToDB.jsp
<!DOCTYPE >
<html>
<head>
<title>Upload files</title>
</head>
<body>

    <div style="padding:5px; color:red;font-style:italic;">
       ${errorMessage}
    </div>
   
    <h2>Upload Files</h2>

    <form method="post" action="${pageContext.request.contextPath}/uploadToDB"
        enctype="multipart/form-data">
       
        Select file to upload:
        <br />
        <input type="file" name="file"  />
        <br />
        <input type="file" name="file" />
        <br />
        Description:
        <br />
        <input type="text" name="description" size="100" />
        <br />
        <br />
        <input type="submit" value="Upload" />
    </form>
   
</body>
</html>
/WEB-INF/jsps/uploadToDBResults.jsp
<!DOCTYPE >
<html>
<head>
<title>Upload files</title>
</head>
<body>

    <h3>Upload has been done successfully!</h3>
    <a href="${pageContext.request.contextPath}/uploadToDB">Continue Upload</a>
     
   
</body>
</html>
Führen Sie die Applikation durch
Die File werden in der Tabelle Attachment hochgeladet und archiviert

4- Aus Database herunterladen

DownloadAttachmentServlet.java
package org.o7planning.servletexamples;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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

import org.o7planning.servletexamples.model.Attachment;
import org.o7planning.tutorial.jdbc.ConnectionUtils;

@WebServlet("/downloadAttachment")
public class DownloadAttachmentServlet extends HttpServlet {
   private static final long serialVersionUID = 1L;


   @Override
   protected void doGet(HttpServletRequest request, HttpServletResponse response)
           throws ServletException, IOException {
       Connection conn = null;
       try {
           // Get Database Connection.
           // (See more in JDBC Tutorial).
           conn = ConnectionUtils.getMyConnection();
           Long id = null;
           try {
               id = Long.parseLong(request.getParameter("id"));
           } catch (Exception e) {

           }
           Attachment attachment = getAttachmentFromDB(conn, id);

           if (attachment == null) {
               // No record found.
               response.getWriter().write("No data found");
               return;
           }

           // file1.zip, file2.zip
           String fileName = attachment.getFileName();
           System.out.println("File Name: " + fileName);

           // abc.txt => text/plain
           // abc.zip => application/zip
           // abc.pdf => application/pdf
           String contentType = this.getServletContext().getMimeType(fileName);
           System.out.println("Content Type: " + contentType);

           response.setHeader("Content-Type", contentType);

           response.setHeader("Content-Length", String.valueOf(attachment.getFileData().length()));

           response.setHeader("Content-Disposition", "inline; filename=\"" + attachment.getFileName() + "\"");

           // For big BLOB data.
           Blob fileData = attachment.getFileData();
           InputStream is = fileData.getBinaryStream();

           byte[] bytes = new byte[1024];
           int bytesRead;

           while ((bytesRead = is.read(bytes)) != -1) {
               // Write image data to Response.
               response.getOutputStream().write(bytes, 0, bytesRead);
           }
           is.close();

       } catch (Exception e) {
           throw new ServletException(e);
       } finally {
           this.closeQuietly(conn);
       }
   }

   private Attachment getAttachmentFromDB(Connection conn, Long id) throws SQLException {
       String sql = "Select a.Id,a.File_Name,a.File_Data,a.Description "//
               + " from Attachment a where a.id = ?";
       PreparedStatement pstm = conn.prepareStatement(sql);
       pstm.setLong(1, id);
       ResultSet rs = pstm.executeQuery();
       if (rs.next()) {
           String fileName = rs.getString("File_Name");
           Blob fileData = rs.getBlob("File_Data");
           String description = rs.getString("Description");
           return new Attachment(id, fileName, fileData, description);
       }
       return null;
   }

   private void closeQuietly(Connection conn) {
       try {
           if (conn != null) {
               conn.close();
           }
       } catch (Exception e) {
       }
   }

}
Führen Sie die Applikation durch

View more categories: