How to insert multiple files into database, using Java Servlet 3.1

Question

once again I need your help. I am trying to insert multiple files into DB table. I managed to insert 1 file - 1 row in the DB table, however I do not know how to do it for multiple files - multiple rows, in one request.

This is the part of my Servlet where I get the file from the html input:

InputStream otherFileInputStream = null;            
Part otherFilesPart = request.getPart("other_files");

if (otherFilesPart != null && otherFilesPart.getSize() != 0) {
    otherFileInputStream = otherFilesPart.getInputStream();                
}

And this is the method that I use for inserting the file into the database:

private int addOtherFiles(long personId, int userId) throws SQLException {
    int res = 0;
    con.setAutoCommit(true);
    String sql = "insert into person_other_files(person_fk, other_files, user_fk) values (?, ?, ?)";
    PreparedStatement ps = con.prepareStatement(sql);
    
    if (otherFileInputStream != null) {
        ps.setLong(1, personId);        
        ps.setBlob(2, otherFileInputStream);
        ps.setInt(3, userId);
        ps.executeUpdate();
        ps.close();
    }        
    return res;
}

When a user uploads 2 or 3 files, there should be 2-3 rows with the files uploaded? Bear in mind, that I do can not use Apache Commons for uploading files. The main idea is: In my website I have 2 different input type file multiple fields, which are corresponding to 2 different DB tables.

If you have a defined amount of file, like 2 files max, yes add 2 column. Else, maybe you have just use something like a JSON which contains a list of all files — Elikill58
– Elikill58, Commented Jul 8, 2021 at 6:34
If I'm understanding this correctly, a user uploads files from his computer to your website and by 2-3 rows you mean a row per file/filepath, right? In any case you probably need to use a for loop to go through every file uploaded by the user. — SikorskyS60
– SikorskyS60, Commented Jul 12, 2021 at 8:40
@SikorskyS60 yes, you understood right. But in my request, I am getting the file name -> "other_files" , because I have a lot of other file fields which I want to submit in different tables. And I can't use get.Parts() because it is only used for 1 multiple type file field. I don't know how to implement your comment for my solution. — NewJavaEnthusiast
– NewJavaEnthusiast, Commented Jul 12, 2021 at 8:55
Please give an example of what is in one of your "files". (Usually, that refers to a CSV file with lots of rows being loaded into one table.) — Rick James
– Rick James, Commented Jul 14, 2021 at 14:20
@RickJames there is an html form with two input type "file" multiple fields. Every user can upload whatever type of file/files they want to each one of them. These files, for example: 2 pds should be uploaded in SQL table as two rows 1 row: first pdf, 2 row: second pdf. — NewJavaEnthusiast
– NewJavaEnthusiast, Commented Jul 14, 2021 at 14:23

Martin.Martinsson · Accepted Answer · 2021-07-20 18:11:45Z

1

Use a loop inside your transaction essentially like this:

 con.setAutoCommit(false); // enable transaction, turning off the auto- 
                           // commit

 String sql = "INSERT INTO person_other_files(person_fk, other_files, user_fk) 
    VALUES (?, ?, ?)";

 for (String file : files) // "files" is your list of files
 {
    PreparedStatement ps = con.prepareStatement(sql);

    Path filePath = Paths.get(file);
    byte[] bytes = Files.readAllBytes(filePath);

    if (bytes != null)
    {
        ps.setLong(1, personId);
        ps.setBlob(2, bytes);
        ps.setInt(3, userId);
        ps.executeUpdate();
        ps.close();
    }
  }

  con.commit(); // commit transaction at the end

Don't forget exception handling (ommitted for clarity)!

Regards

edited Jul 20, 2021 at 18:11

answered Jul 17, 2021 at 13:21

Martin.Martinsson

2,20125 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert multiple files into database, using Java Servlet 3.1

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related