title: How to insert special characters into MySQL using Java
body: <p>I have several csv files in am loading into MySQl using Java. In the Description field I have several Special Characters that are causing the load to fail. I am using LOAD DATA INFILE as seen in the code block below. This is nested in a for each loop which parses an array of filenames / tables and runs through each combination until it is finished with all the files.</p>
  
  <p>Here is my jdbc connection string where I am passing a definitive collation param/value for UTF8 collation</p>
  
  <pre><code> static String  url = "jdbc:mysql://localhost:3306/iber_stage?verifyServerCertificate=false&amp;amp;characterEncoding=UTF8";
  </code></pre>
  
  <p>other connection parameters and parsing an array of filenames/tablenames </p>
  
  <pre><code> final String sql1 = ("TRUNCATE TABLE" + tableName);
   final String sql2 = ("LOAD DATA INFILE" + filetoEat  + "INTO TABLE staging." +tableName + "CHARACTER SET UTF8 FIELDS TERMINATED BY',' ENCLOSED BY '\"\' LINES TERMINATED BY '\n' IGNORE 1 LINES");
  
          try {
          Class.forName("com.mysql.jdbc.Driver");
          con = DriverManager.getConnection(url, username, password);
          st = con.createStatement();
          st.executeUpdate(sql1);
          rs = st.executeQuery(sql2);
  
          if (rs.toString() != null) {
              returnMsg = rs.toString();
              System.out.println(returnMsg);        
              updFlag = 0; 
              String strRecs = returnMsg.substring(40);
              updateControlTable(updFlag, strRecs);
          }
  
          } catch (SQLException ex) {
              Logger lgr = Logger.getLogger(update.class.getName());
              lgr.log(Level.SEVERE, ex.getMessage(), ex);
              updFlag = 1;            
  
          } catch (ClassNotFoundException e) {
              Logger lgr = Logger.getLogger(update.class.getName());
              lgr.log(Level.SEVERE, e.getMessage(), e);
              e.printStackTrace();
              updFlag = 1;
  
          } 
  </code></pre>
  
  <p>The code is working fine until it comes across a special character like a degree symbol or micro symbol µ within a Material Description . At that point it throws an Exception</p>
  
  <pre><code>Invalid utf8 character string: 'LUG'
  </code></pre>
  
  <p>The string LUG is followed by a µ symbol. The DB is set to utf8 - <code>utf8_unicode_ci</code> and the column in question is a VARCHAR(60) that holds material descriptions.
  I have tried using  <code>ESCAPED BY '\\'</code> but I can't seem to get it working correctly. I have also tried <code>CHARACTER SET UTF8</code>. I have also tried different collation ie, <code>utf8_general_ci</code> to no avail.</p>
  
  <p>Any insight is greatly appreciated</p>