I got problem to insert String with correct UTF-8 encoding to MySQL database. The String is not inserted correctly. I found many solutions of this problem on web, but nothing works well for me on Openshift. I´m able to insert it corectly at my local server, but not at cloud configuration. Here is my problem:
From EJB I´m inserting: Příliš žluťoučký kůň pěl ďábelské ódy
but at MySQL is stored: P?�li? ?lu?ou?k� k?? p?l ?�belsk� �
I use PaaS cloud Openshift, JBoss Application server 7, MySQL 5.5
here is my code:
MySQL: database, tables and colums have set correct collation: utf8_czech_ci
mysql> SHOW VARIABLES LIKE '%character%';
Variable_name Value character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 character_sets_dir /opt/rh/mysql55/root/usr/share/mysql/charsets
2.jsp
<form role="form" method="post" action="MakeRezervationServlet">
<input type="hidden" name="term" value="${param.termin}"/>
<div class="form-group">
<label for="userName">Startovní lokace</label>
<input type="text" name="startLocation" value="U autoskoly" class="form-control" id="exampleInputEmail1" >
</div>
<div class="form-group">
<label for="userName">Poznámka</label>
<input type="text" name="note" class="form-control" id="exampleInputEmail1" placeholder="Zde můžete zanechat vzkaz pro instruktora" >
</div>
<button type="submit" class="btn btn-success">Potvrdit termín</button>
3.Servlet. I found by testing, that String is OK in Servlet, there is not problem between Servlet and jsp
package application.servlets;
import java.io.IOException;
import javax.ejb.EJB;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import application.ejb.RezervationLocal;
@WebServlet("/MakeRezervationServlet")
public class MakeRezervationServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
@EJB
private RezervationLocal rezervation;
public MakeRezervationServlet() {
super();
}
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
request.setCharacterEncoding("UTF-8");
String note = request.getParameter("note");
byte[] n = note.getBytes("ISO8859_1");
note = new String(n, "UTF-8");
String startLocation = request.getParameter("startLocation");
byte[] startLoc = startLocation.getBytes("ISO8859_1");
startLocation = new String(startLoc, "UTF-8");
String term = request.getParameter("term");
int idTerm = Integer.valueOf(term);
HttpSession session = request.getSession();
String IDuser;
int intIDuser;
synchronized (session) {
IDuser = (String) session.getAttribute("IDuser");
intIDuser = Integer.valueOf(IDuser);
}
//hardcode předává konstantního instruktora id=3
String message = rezervation.makeRezervation(idTerm, intIDuser, 3, startLocation, note);
request.setAttribute("message", message);
request.getRequestDispatcher("kalendar.jsp").forward(request, response);
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
@Override
public String getServletInfo() {
return "Short description";
}
}
EJB. I found by testing that also in EJB is String still OK
package application.ejb; import java.sql.Connection; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.annotation.Resource; import javax.ejb.EJB; import javax.ejb.Stateless; import javax.sql.DataSource; import javax.ejb.LocalBean; import javax.ejb.Stateless; import javax.naming.Context; import javax.naming.InitialContext; @Stateless @LocalBean public class Rezervation implements RezervationLocal { @EJB private LoggerBeanLocal loggerBean; @EJB private UtilityBeanLocal utilityBean; public Rezervation() { // TODO Auto-generated constructor stub } @Override public String makeRezervation(int idTerm, int idStudent, int idInstructor, String startLocation, String note) { String message = ""; Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; try { try { conn = connect(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } synchronized (conn) { stat = conn.prepareStatement("SELECT ID_TERM FROM TBL_TERMS WHERE ID_STUDENT = ? AND ID_TYPE_OF_STATUS <= 2 ORDER BY ID_TERM"); stat.setInt(1, idStudent); rs = stat.executeQuery(); Boolean podminka1 = false; Boolean podminka2 = false; int counter = 0; String term2 = String.valueOf(idTerm); term2 = term2.substring(0, 8); String term; while (rs.next()) { term = String.valueOf(rs.getInt("ID_TERM")); term = term.substring(0, 8); if (term.equals(term2)) { podminka1 = true; } counter++; if (counter == 14) { podminka2 = true; } } if (podminka1) { message = "Nelze rezervovat více jízd ve stejný den."; } else if (podminka2) { message = "Nelze rezervovat více jízd. Již máte absolvováno nebo rezervováno všech 14 jízd."; } else { stat = conn.prepareStatement("INSERT INTO TBL_TERMS (ID_TERM, ID_STUDENT, ID_INSTRUCTOR, START_LOCATION, NOTE, ID_TYPE_OF_STATUS) VALUES(?,?,?,?,?,?)"); stat.setInt(1, idTerm); stat.setInt(2, idStudent); stat.setInt(3, idInstructor); stat.setString(4, startLocation); stat.setString(5, note); stat.setInt(6, 1); stat.execute(); String date = utilityBean.parseDate(idTerm); message = "Termín " + date + " byl uložen"; loggerBean.log(idStudent, "made rezervation "+idTerm); } } } catch (SQLException ex) { message = "Termín nebyl uložen: " + ex.toString(); } finally { try { if (rs != null) { rs.close(); } if (stat != null) { stat.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { } } return message; } public Connection connect() throws Exception { String jndiName = "java:jboss/datasources/MysqlDS"; Connection con = null; Context ic = new InitialContext(); Object obj = ic.lookup(jndiName); javax.sql.DataSource ds = (javax.sql.DataSource) obj; con = ds.getConnection(); return con; }}
5.standalone.xml -I use in jdbc url //...?useUnicode=true&characterEncoding=UTF-8
<datasource jndi-name="java:jboss/datasources/MysqlDS" enabled="${mysql.enabled}" use-java-context="true" pool-name="MysqlDS" use-ccm="true">
<connection-url>jdbc:mysql://${env.OPENSHIFT_MYSQL_DB_HOST}:${env.OPENSHIFT_MYSQL_DB_PORT}/${env.OPENSHIFT_APP_NAME}?useUnicode=true&characterEncoding=UTF-8</connection-url>
<driver>mysql</driver>
<security>
<user-name>${xxx}</user-name>
<password>${exxx}</password>
</security>
<validation>
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
<background-validation>true</background-validation>
<background-validation-millis>60000</background-validation-millis>
<!--<validate-on-match>true</validate-on-match>-->
</validation>
<pool>
<flush-strategy>IdleConnections</flush-strategy>
</pool>
</datasource>
Any help appreciate. I have already spent a lot of time about solving this issue. Thanks
