Java tutorial
/* * Copyright (c) 2013, OpenCloudDB/MyCAT and/or its affiliates. All rights reserved. * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. * * This code is free software;Designed and Developed mainly by many Chinese * opensource volunteers. you can redistribute it and/or modify it under the * terms of the GNU General Public License version 2 only, as published by the * Free Software Foundation. * * This code is distributed in the hope that it will be useful, but WITHOUT * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License * version 2 for more details (a copy is included in the LICENSE file that * accompanied this code). * * You should have received a copy of the GNU General Public License version * 2 along with this work; if not, write to the Free Software Foundation, * Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA. * * Any questions about this component can be directed to it's project Web address * https://code.google.com/p/opencloudb/. * */ package io.mycat.server.packet.util; import io.mycat.MycatServer; import io.mycat.backend.PhysicalDBPool; import io.mycat.backend.PhysicalDatasource; import io.mycat.server.config.node.DBHostConfig; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import java.util.Map.Entry; import java.util.Properties; import java.util.concurrent.Callable; import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.SystemUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.alibaba.fastjson.JSON; import com.google.common.util.concurrent.ListenableFuture; /** * ?fix ? collationIndex charset * utf8mb4 collationIndex 45, 46 ?? 4546? * mysqld(my.cnf?)?collation_server=utf8mb4_bin? * ?45?'java.lang.RuntimeException: Unknown charsetIndex:46' * ? collation_server=utf8mb4_bincollation_server? * ?46???45,46? * ? MycatServer.startup() config.initDatasource(); ? * CharsetUtil.initCharsetAndCollation(config.getDataHosts()); * mysqldinformation_schema.collations? collationIndex charset * mysqld????(??mysqldcollationIndex charset ?) * @author mycat */ public class CharsetUtil { public static final Logger logger = LoggerFactory.getLogger(CharsetUtil.class); /** collationIndex charsetName */ private static final Map<Integer, String> INDEX_TO_CHARSET = new HashMap<>(); /** charsetName collationIndex */ private static final Map<String, Integer> CHARSET_TO_INDEX = new HashMap<>(); /** collationName CharsetCollation */ private static final Map<String, CharsetCollation> COLLATION_TO_CHARSETCOLLATION = new HashMap<>(); /** * ? charset collation(? mycat.xml dataHosts mysqld? charset collation ) * ConcurrentHashMap * @param charsetConfigMap mycat.xml charset-config collationIndex --> charsetName */ public static void asynLoad(Map<String, PhysicalDBPool> dataHosts, Map<String, Object> charsetConfigMap) { MycatServer.getInstance().getListeningExecutorService().execute(new Runnable() { public void run() { CharsetUtil.load(dataHosts, charsetConfigMap); } }); } /** * ? ? charset collation(? mycat.xml dataHosts mysqld? charset collation ) * @param charsetConfigMap mycat.xml charset-config collationIndex charsetName */ public static void load(Map<String, PhysicalDBPool> dataHosts, Map<String, Object> charsetConfigMap) { try { if (dataHosts != null && dataHosts.size() > 0) CharsetUtil.initCharsetAndCollation(dataHosts); // mysqld? charset collation else logger.debug("param dataHosts is null"); // ? collationIndex --> charsetName for (String index : charsetConfigMap.keySet()) { int collationIndex = Integer.parseInt(index); String charsetName = INDEX_TO_CHARSET.get(collationIndex); if (StringUtils.isNotBlank(charsetName)) { INDEX_TO_CHARSET.put(collationIndex, charsetName); CHARSET_TO_INDEX.put(charsetName, collationIndex); } logger.debug("load charset and collation from mycat.xml."); } } catch (Exception e) { logger.error(e.getMessage()); } } /** * <pre> * ? dataHosts mysqld? charset collation * mysql> SELECT ID,CHARACTER_SET_NAME,COLLATION_NAME,IS_DEFAULT FROM INFORMATION_SCHEMA.COLLATIONS; * +-----+--------------------+--------------------------+------------+ * | ID | CHARACTER_SET_NAME | COLLATION_NAME | IS_DEFAULT | * +-----+--------------------+--------------------------+------------+ * | 1 | big5 | big5_chinese_ci | Yes | * | 84 | big5 | big5_bin | | * | 3 | dec8 | dec8_swedish_ci | Yes | * | 69 | dec8 | dec8_bin | | *</pre> */ private static void initCharsetAndCollation(Map<String, PhysicalDBPool> dataHosts) { if (COLLATION_TO_CHARSETCOLLATION.size() > 0) { // ?? logger.debug(" charset and collation has already init ..."); return; } // mycat.xml? heartbeat(?)??CharsetCollation?????? // ???mycat.xml? dataHost?CharsetCollation; DBHostConfig dBHostconfig = getConfigByDataHostName(dataHosts, "jdbchost"); if (dBHostconfig != null) { if (getCharsetCollationFromMysql(dBHostconfig)) { logger.debug(" init charset and collation success..."); return; } } // ?? ? mycat.xml dataHost ??mysqld? charset collation ? for (String key : dataHosts.keySet()) { PhysicalDBPool pool = dataHosts.get(key); if (pool != null && pool.getSource() != null) { PhysicalDatasource ds = pool.getSource(); if (ds != null && ds.getConfig() != null && "mysql".equalsIgnoreCase(ds.getConfig().getDbType())) { DBHostConfig config = ds.getConfig(); if (getCharsetCollationFromMysql(config)) { logger.debug(" init charset and collation success..."); return; // ? for } } } } logger.error(" init charset and collation from mysqld failed, please check datahost in mycat.xml." + SystemUtils.LINE_SEPARATOR + " if your backend database is not mysqld, please ignore this message."); // Mycat-server?mycat.xml?mysqld????sqlserveroracle // mysqld????? // ???mysqld??? getCharsetInfoFromFile(); logger.info(" backend database is not mysqld, read charset info from file."); } public static DBHostConfig getConfigByDataHostName(Map<String, PhysicalDBPool> dataHosts, String hostName) { PhysicalDBPool pool = dataHosts.get(hostName); if (pool != null && pool.getSource() != null) { PhysicalDatasource ds = pool.getSource(); return ds.getConfig(); } return null; } public static final String getCharset(int index) { return INDEX_TO_CHARSET.get(index); } /** * ? charset collationIndex, collationIndex * index?index, ? getIndexByCollationName * getIndexByCharsetNameAndCollationName * @param charset * @return */ public static final int getIndex(String charset) { if (StringUtils.isBlank(charset)) { return 0; } else { Integer i = CHARSET_TO_INDEX.get(charset.toLowerCase()); if (i == null && "Cp1252".equalsIgnoreCase(charset)) charset = "latin1"; // ??http://www.cp1252.com/ The windows 1252 codepage, also called Latin 1 i = CHARSET_TO_INDEX.get(charset.toLowerCase()); return (i == null) ? 0 : i; } } /** * ? collationName charset collationIndex * @param charset * @param collationName * @return */ public static final int getIndexByCharsetNameAndCollationName(String charset, String collationName) { if (StringUtils.isBlank(collationName)) { return 0; } else { CharsetCollation cc = COLLATION_TO_CHARSETCOLLATION.get(collationName.toLowerCase()); if (cc != null && charset != null && charset.equalsIgnoreCase(cc.getCharsetName())) return cc.getCollationIndex(); else return 0; } } /** * ? collationName collationIndex, * @param collationName * @return */ public static final int getIndexByCollationName(String collationName) { if (StringUtils.isBlank(collationName)) { return 0; } else { CharsetCollation cc = COLLATION_TO_CHARSETCOLLATION.get(collationName.toLowerCase()); if (cc != null) return cc.getCollationIndex(); else return 0; } } private static boolean getCharsetCollationFromMysql(DBHostConfig config) { String sql = "SELECT ID,CHARACTER_SET_NAME,COLLATION_NAME,IS_DEFAULT FROM INFORMATION_SCHEMA.COLLATIONS"; try (Connection conn = getConnection(config)) { if (conn == null) return false; try (Statement statement = conn.createStatement()) { ResultSet rs = statement.executeQuery(sql); while (rs != null && rs.next()) { int collationIndex = new Long(rs.getLong(1)).intValue(); String charsetName = rs.getString(2); String collationName = rs.getString(3); boolean isDefaultCollation = (rs.getString(4) != null && "Yes".equalsIgnoreCase(rs.getString(4))) ? true : false; INDEX_TO_CHARSET.put(collationIndex, charsetName); if (isDefaultCollation) { // ? charsetName collationIndexcollationIndex CHARSET_TO_INDEX.put(charsetName, collationIndex); } CharsetCollation cc = new CharsetCollation(charsetName, collationIndex, collationName, isDefaultCollation); COLLATION_TO_CHARSETCOLLATION.put(collationName, cc); } if (COLLATION_TO_CHARSETCOLLATION.size() > 0) return true; return false; } catch (SQLException e) { logger.warn(e.getMessage()); } } catch (SQLException e) { logger.warn(e.getMessage()); } return false; } /** * ? DBHostConfig cfg ??(java.sql.Connection) * mysqld??mycat-server * ????mysql?????mysql??mysqld??handshake * "serverCharsetIndex":46?connection serverCharsetIndex * ?? handshake * {"packetId":0,"packetLength":78,"protocolVersion":10,"restOfScrambleBuff":"OihYY2tvakVadV5Y", * "seed":"YiJ+eWVsb2c=","serverCapabilities":63487, * "serverCharsetIndex":46,"serverStatus":2,"serverVersion":"NS42LjI3LWxvZw==","threadId":65} * mysql??mysqld?JDBC???url?JDBC?? * @param cfg * @return * @throws SQLException */ public static Connection getConnection(DBHostConfig cfg) { if (cfg == null) return null; String url = new StringBuffer("jdbc:mysql://").append(cfg.getUrl()).append("/mysql") .append("?characterEncoding=UTF-8").toString(); Connection connection = null; long millisecondsEnd2 = System.currentTimeMillis(); try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection(url, cfg.getUser(), cfg.getPassword()); } catch (ClassNotFoundException | SQLException e) { if (e instanceof ClassNotFoundException) logger.error(e.getMessage()); else logger.warn(e.getMessage() + " " + JSON.toJSONString(cfg)); } long millisecondsEnd = System.currentTimeMillis(); logger.debug(" function getConnection cost milliseconds: " + (millisecondsEnd - millisecondsEnd2)); return connection; } /** * ? index_to_charset.properties ? collationIndex charsetName * ??SELECT ID,CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS order by id; * * ?charset_to_default_index.properties?charsetNamecollationIndex * ??SELECT CHARACTER_SET_NAME,ID FROM INFORMATION_SCHEMA.COLLATIONS where Default='Yes'; * * ???? * * ?? ? mysql??? */ public static void getCharsetInfoFromFile() { Properties pros = new Properties(); try { pros.load(CharsetUtil.class.getClassLoader().getResourceAsStream("index_to_charset.properties")); Iterator<Entry<Object, Object>> it = pros.entrySet().iterator(); while (it.hasNext()) { Entry<Object, Object> entry = it.next(); Object key = entry.getKey(); Object value = entry.getValue(); INDEX_TO_CHARSET.put(Integer.parseInt(key.toString()), value.toString()); } // System.out.println(JSON.toJSONString(INDEX_TO_CHARSET)); pros.clear(); pros.load( CharsetUtil.class.getClassLoader().getResourceAsStream("charset_to_default_index.properties")); it = pros.entrySet().iterator(); while (it.hasNext()) { Entry<Object, Object> entry = it.next(); Object key = entry.getKey(); Object value = entry.getValue(); CHARSET_TO_INDEX.put(key.toString(), Integer.parseInt(value.toString())); } // System.out.println(JSON.toJSONString(CHARSET_TO_INDEX)); } catch (IOException e) { e.printStackTrace(); } } public static void main(String[] args) { getCharsetInfoFromFile(); } } /** * ? mysqld ? ??collation?collationindex? collation * (?)collation collation? collationindex, * collationName collationIndex ?collationIndex??collationIndex?? * collationIndex ? index( collationindex) * collationIndex * mysqld collation index ???? * @author Administrator * */ class CharsetCollation { // mysqld??????javaunicode??? // ???jarcom.mysql.jdbc.CharsetMapping?? private String charsetName; private int collationIndex; // collation? private String collationName; // collation ?? private boolean isDefaultCollation = false; // collation?collation public CharsetCollation(String charsetName, int collationIndex, String collationName, boolean isDefaultCollation) { this.charsetName = charsetName; this.collationIndex = collationIndex; this.collationName = collationName; this.isDefaultCollation = isDefaultCollation; } public String getCharsetName() { return charsetName; } public void setCharsetName(String charsetName) { this.charsetName = charsetName; } public int getCollationIndex() { return collationIndex; } public void setCollationIndex(int collationIndex) { this.collationIndex = collationIndex; } public String getCollationName() { return collationName; } public void setCollationName(String collationName) { this.collationName = collationName; } public boolean isDefaultCollation() { return isDefaultCollation; } public void setDefaultCollation(boolean isDefaultCollation) { this.isDefaultCollation = isDefaultCollation; } }