Correct way to upload binary file to MySQL through Spring MVC

Question

I am trying to upload file to MySQL, however I wasn't able to do that correctly. I am using a cloud platform to run Java Spring application as a web site.

Controller:

byte[] bytes = file.getBytes();    // file is MultipartFile
DFile newFile = new DFile();       // my object
newFile.setName(name);             // name column
newFile.setType(type);             // 
Blob blob = new SerialBlob(bytes); // set the blob from binary
newFile.setData(blob);
fileService.insertFile(newFile);   // calls to insert into MySQL

Dao: two different ways

        length = (int)blob.length();
        byte[] b = blob.getBytes(1, length);
        InputStream is=new ByteArrayInputStream(b);
        LobHandler lobHandler = new DefaultLobHandler();
        // method 1
        jdbcTemplate.update(
                sql,
                new Object[] { dFile.getName(), dFile.getType(),
                        blob,},
                new int[] {Types.VARCHAR, Types.VARCHAR, Types.BLOB});
        // method 2
        Connection con;
        PreparedStatement ps;
        ps = con.prepareStatement(sql);
        ps.setString(1, dFile.getName());
        ps.setString(2, dFile.getType());
        ps.setBinaryStream(3,  is, length);

        int count = ps.executeUpdate();

Original Data from file

0000-0010:  ff d8 ff e0-00 10 4a 46-49 46 00 01-01 01 00 90  ......JF IF......
0000-0020:  00 90 00 00-ff db 00 43-00 02 01 01-02 01 01 02  .......C ........
0000-0030:  02 02 02 02-02 02 02 03-05 03 03 03-03 03 06 04  ........ ........
0000-0040:  04 03 05 07-06 07 07 07-06 07 07 08-09 0b 09 08  ........ ........

Data served from MySQL

0000-0010:  ef bf bd ef-bf bd ef bf-bd ef bf bd-00 10 4a 46  ........ ......JF
0000-0020:  49 46 00 01-01 01 00 ef-bf bd 00 ef-bf bd 00 00  IF...... ........
0000-0030:  ef bf bd ef-bf bd 00 43-00 02 01 01-02 01 01 02  .......C ........
0000-0040:  02 02 02 02-02 02 02 03-05 03 03 03-03 03 06 04  ........ ........

How I created the table File:

  String create ="CREATE TABLE File (
  "name varchar(255) character set utf8 not null, 
  type  varchar(64) character set utf8 not null,    
  data mediumblob,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  primary key (name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Fact:

The web site has an old and a new phpAdmin page to perform sql actions. The old web interface wasn't able to upload correct image files. The wrong file seems to be the same if uploaded using my code. The new PhpAdmin has an option Binary when upload and it works.
I am using UTF-8 in all web serving, including CharsetFilter in web.xml, and SQL character set.
I am using similar code but reversed in Controller to serve file which can show correct image file if I load using the new web phpAdmin.

Question:

What went wrong? I am guessing it's possibly to be character encoding. How to fix the problem?

Sébastien · Accepted Answer · 2014-10-04 17:37:19Z

3

Have you tried this:

jdbcTemplate.execute("INSERT INTO File (name, type, data) VALUES (?, ?, ?)",
    new AbstractLobCreatingPreparedStatementCallback(lobHandler){
        @Override
        protected void setValues(PreparedStatement ps,
            LobCreator lobCreator) throws SQLException,
            DataAccessException {
            ps.setString(1, dFile.getName());
            ps.setString(2, dFile.getType());
            Blob blob = dFile.getData();
            int length = (int)blob.length();
            byte[] b = dFile.getData(); //blob.getBytes(1, length);
            int length = b.length;
            InputStream is=new ByteArrayInputStream(b);
            ps.setBinaryStream(3,  is, length);
        }

});

edited Oct 4, 2014 at 17:37

Sébastien

12.1k12 gold badges60 silver badges83 bronze badges

answered Oct 4, 2014 at 17:32

Nebula Era

2632 silver badges10 bronze badges

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

Comments

Hai Bi · Accepted Answer · 2014-10-04 17:03:51Z

2

Try to use following style

insert into File (name, type, data) values ("good.bin", "binary", 0x01020304)

answered Oct 4, 2014 at 17:03

Hai Bi

1,1731 gold badge11 silver badges21 bronze badges

Collectives™ on Stack Overflow

Correct way to upload binary file to MySQL through Spring MVC

Fact:

Question:

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Fact:

Question:

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related