Correct way to upload binary file to MySQL through

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?

回答1:

Try to use following style

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

回答2:

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

});