devstory

Télécharger et télécharger des fichiers à partir de la base de données à l'aide de Java Servlet

  1. Database
  2. Mettre à jour les fichiers et télécharger de la base de données
  3. Mettre à jour les fichiers et stocker dans la base de données
  4. Télécharger de la base de données

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. Mettre à jour les fichiers et télécharger de la base de données

Dans ce document, je vais vous donner des instruction de télécharger et stocker des fichiers sur la base de donnée, ensuite télécharger les données sur la base de données. Les donnéess qui sont téléchargées sont stockées dans une colonne avec le type de données étant BLOB.
Vous pouvez utiliser n'importe quelle base de données, celle- dessus est un script pour créer la table ATTACHMENT, cette table est utilisée pour stocker des fichiers des données que vous téléchargez.
Vous pouvez apprendre comment utiliser JDBC afin de connecter la base de données ici:

3. Mettre à jour les fichiers et stocker dans la base de données

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>
Exécution des applictions:
Les fichiers ont été téléchargés et stockés dans la table Attachment.

4. Télécharger de la base de données

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) {
       }
   }

}
Exécution des applications: