Character encoding to utf-8 mismatch in MySQL java

Question

I´m using a servlet to store MySQL information. Values are display correctly on console but when I tried to store them in MySQL they change. I have researched in various sites(most of them replaced characters with ?), mine are replace with other weird utf-8 characters such as:

Ádmínistrádór --> Ã�dmÃnistrÃ¡dÃ³r (I know the word is mispelled)

Here is my information:

MySQL --> 5.6

Database Creation:

CREATE DATABASE glassfish_realm CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Table creation:

CREATE TABLE glassfish_users (user_name varchar(20) NOT NULL PRIMARY KEY, id varchar(20) NOT NULL,name varchar(20) NOT NULL, lastname_father varchar(255) NOT NULL,lastname_mother varchar(20) NOT NULL,birthdate varchar(255) NOT NULL,password varchar(255) NOT NULL,contrasena varbinary(255) NOT NULL,photo MEDIUMBLOB,photo_name varchar(255),tipo varchar(255) NOT NULL) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE glassfish_users_roles (user_name varchar(20) NOT NULL, role_name varchar(20) NOT NULL, PRIMARY KEY (user_name), FOREIGN KEY (user_name) REFERENCES glassfish_users (user_name) ON UPDATE CASCADE ON DELETE CASCADE) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Finally MyServlet:

    boolean isMultiPart = ServletFileUpload.isMultipartContent(request);
    request.setCharacterEncoding("UTF-8");

    Item data=new Item();
    FileInputStream fis = null;
    File file=null;
    String filename="";
    String privilegio="";
    String message="";

    if (isMultiPart) {
        ServletFileUpload upload = new ServletFileUpload();

        try {
            FileItemIterator iterate = upload.getItemIterator(request);

            while (iterate.hasNext()) {
                FileItemStream item = iterate.next();

                if (item.isFormField()) {
                    String fieldName = item.getFieldName();
                    InputStream inStream = item.openStream();
                    byte[] b = new byte[inStream.available()];
                    inStream.read(b);
                    String value = new String(b);

                    data.withString(fieldName,value);
                } else {
                    //String path = "/home/ec2-user/img";
                    String path = "C:/Users/frhec/Desktop/photos";

                    if((item.getContentType().substring(item.getContentType().lastIndexOf("/")+1).equals("octet-stream"))){
                        filename=data.getString("id")+".jpeg";
                        String relativeWebPath = "/resources/img/profile.jpg";
                        String absoluteDiskPath = this.getServletContext().getRealPath(relativeWebPath);
                        file=new File(absoluteDiskPath);
                        fis = new FileInputStream(file);
                    }else{
                        filename=data.getString("id")+"."+(item.getContentType().substring(item.getContentType().lastIndexOf("/")+1));
                        file=com.devcti.io.FileUpload.processFile(path, item,data.getString("id"));
                        fis = new FileInputStream(file);
                    }
                }
            }
        } catch (FileUploadException e) {
            e.printStackTrace();
        }
    }

    Statement stmt=null;
    PreparedStatement pre=null;
    Connection conn=null;

    try{
        ResultSet rs = null;

        ...Conection to Database...

        if(data.get("id")==null||data.getString("id").equals("")){
            stmt = conn.createStatement();              
            String sql = "SELECT user_name from glassfish_users_roles;";
            rs = stmt.executeQuery(sql);                

            rs.next();
            data=data.removeAttribute("id");   
            String id=generateID("HSUS", rs.getMetaData().getColumnCount());
            stmt.close();               

            conn.setAutoCommit(false);

            pre = conn.prepareStatement("INSERT INTO glassfish_users (ser_name, password,contrasena,tipo,photo_name,photo) VALUES (?,?,?,?,?,?,SHA2(?, 256),AES_ENCRYPT(?,'key'),?,?,?)");
            Map<String, Object> map = data.asMap();
            int cont=1;
            pre.setString(cont, id);

            cont++;
            pre.setString(cont, filename);
            cont++;
            pre.setBinaryStream(cont, fis, (int) file.length());
            pre.executeUpdate();
            conn.commit();
            pre.close();

            stmt = conn.createStatement();              
            sql = "INSERT INTO glassfish_users_roles (user_name, role_name) VALUES ('"+data.getString("user_name")+"','"+privilegio+"');";
            stmt.executeUpdate(sql); 

            conn.commit();

            stmt.close();
            conn.close();

           ....Omited Code....

Thank you very much

EDIT:

Somebody mark it as a posible duplicate, when I wasen´t even asking how upload files to a server....

Try changing `setString()` to `setNString()`. Don't know if it helps, depends on the JDBC driver. — Andreas, Jun 12 '16 at 14:49
Got this stacktrace: Method com/mysql/jdbc/PreparedStatement.setNString(ILjava/lang/String;)V is abstract @Andreas — HFR1994, Jun 12 '16 at 14:52
*You* are likely the one that messes up the encoding when you call `new String(b)`. How do you know that the byte stream is in the server default code page? Use `parseParameterMap()` instead of `getItemIterator`. Much easier to use. — Andreas, Jun 12 '16 at 15:09
Got it fixed using new String(b, StandardCharsets.UTF_8); Thanks for the observation @Andreas — HFR1994, Jun 12 '16 at 15:30
Posting tip: there is no need to add "please help" to titles - it will generally be edited out, and can come across as begging. — halfer, Jun 13 '16 at 11:25

score 0 · Accepted Answer · answered Jun 12 '16 at 15:29

0

Got it fixed with @Andreas observation. Instead of using

new String(b)

I used

new String(b, StandardCharsets.UTF_8);

Thank you very much. I´ll take a look al parseParameterMap()

answered Jun 12 '16 at 15:29

HFR1994

536
1
5
16

Character encoding to utf-8 mismatch in MySQL java

1 Answers1