HFR1994 HFR1994 - 5 months ago 40
MySQL Question

Character encoding to utf-8 mismatch in MySQL java

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

enter image description here

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....

Answer

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()