MySQL JDBC中的参数

jdbc的参数配置

当我们用jdbc连MySQL的时候，有一个连接串，一般形如

jdbc:mysql://127.0.0.1:3307/test_tb?connectTimeout=5000&serverTimezone=UTC&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF8&useConfigs=fullDebug

还有时候我们会用一个properties对象配置参数，这些参数是怎么发挥作用的，而且有什么约束呢，带着这个问题，我们慢慢研究一下jdbc的源码

参数传递

1、jdbc中连接数据库的入口在DriverManager的getConnection中，会把user,password放入properties中

//DriverManager.java
    @CallerSensitive
    public static Connection getConnection(String url,
        java.util.Properties info) throws SQLException {
        return (getConnection(url, info, Reflection.getCallerClass()));
    }

    @CallerSensitive
    public static Connection getConnection(String url,
        String user, String password) throws SQLException {
        java.util.Properties info = new java.util.Properties();

        if (user != null) {
            info.put("user", user);
        }
        if (password != null) {
            info.put("password", password);
        }
        return (getConnection(url, info, Reflection.getCallerClass()));
    }

    @CallerSensitive
    public static Connection getConnection(String url)
        throws SQLException {
        java.util.Properties info = new java.util.Properties();
        return (getConnection(url, info, Reflection.getCallerClass()));
    }

2、MySQL jdbc建立连接的处理在NonRegisteringDriver的connect中,会把连接串拼接成url§{properties}格式，生成ConnectionUrl对象包装起来

//com.mysql.cj.conf.ConnectionUrl#buildConnectionStringCacheKey
    private static String buildConnectionStringCacheKey(String connString, Properties info) {
        StringBuilder sbKey = new StringBuilder(connString);
        sbKey.append("\u00A7"); // Section sign.
        sbKey.append(
                info == null ? null : info.stringPropertyNames().stream().map(k -> k + "=" + info.getProperty(k)).collect(Collectors.joining(", ", "{", "}")));
        return sbKey.toString();
    }

3、然后整个url§{properties}会给ConnectionUrlParser处理，通过CONNECTION_STRING_PTRN正则匹配出scheme(jdbc:mysql), authority(ip:port), path(dbname),query(url中的参数部分)，并通过PROPERTIES_PTRN匹配出url中的参数对

Pattern CONNECTION_STRING_PTRN = Pattern.compile("(?<scheme>[\\w\\+:%]+)\\s*" // scheme: required; alphanumeric, plus, colon or percent
            + "(?://(?<authority>[^/?#]*))?\\s*" // authority: optional; starts with "//" followed by any char except "/", "?" and "#"
            + "(?:/(?!\\s*/)(?<path>[^?#]*))?" // path: optional; starts with "/" but not followed by "/", and then followed by by any char except "?" and "#"
            + "(?:\\?(?!\\s*\\?)(?<query>[^#]*))?" // query: optional; starts with "?" but not followed by "?", and then followed by by any char except "#"
            + "(?:\\s*#(?<fragment>.*))?");
Pattern PROPERTIES_PTRN = Pattern.compile("[&\\s]*(?<key>[\\w\\.\\-\\s%]*)(?:=(?<value>[^&]*))?");

4、最后通过ConnectionUrl的collectProperties，把参数放入ConnectionUrl自己的properties里面去。由此可见，通过url配置参数与properties配置参数效果基本是一样的

//com.mysql.cj.conf.ConnectionUrl#collectProperties
protected void collectProperties(ConnectionUrlParser connStrParser, Properties info) {
        // Fill in the properties from the connection string.
        connStrParser.getProperties().entrySet().stream().forEach(e -> this.properties.put(PropertyKey.normalizeCase(e.getKey()), e.getValue()));

        // Properties passed in override the ones from the connection string.
        if (info != null) {
            info.stringPropertyNames().stream().forEach(k -> this.properties.put(PropertyKey.normalizeCase(k), info.getProperty(k)));
        }

        // Collect properties from additional sources. 
        setupPropertiesTransformer();
        expandPropertiesFromConfigFiles(this.properties);
        injectPerTypeProperties(this.properties);
    }

5、当然参数值，还有其它的设置方式，比如expandPropertiesFromConfigFiles方法里面就是在是预置配置在com/mysql/cj/configurations/xxx.properties里面，例如url中增加&useConfigs=fullDebug，就可以在参数中增加如下四个参数; 还有一些其它的配置，比如dbname，既可以配置在port/后面，也可以以参数的形式配置

profileSQL=true
gatherPerfMetrics=true
useUsageAdvisor=true
logSlowQueries=true
explainSlowQueries=true

最终所有这些参数会封装成一个HostInfo对象

参数名与取值约束

1、建立连接的时候，会创建一个com.mysql.cj.jdbc.ConnectionImpl对象，它有两个属性：HostInfo(hostInfo为我们声明的参数)，和PropertySet系统参数
2、所有PropertySet配置的属性名称，都必须是com.mysql.cj.conf.PropertyKey类中定义的名字
3、所有PropertySet配置的属性值的设置规则，都必须是com.mysql.cj.conf.PropertyDefinitions#PROPERTY_KEY_TO_PROPERTY_DEFINITION中定义的规则
值的规则有，boolean, enum, string,int,long等几种PropertyDefinition
4、属性值的用com.mysql.cj.conf.RuntimeProperty保存
其中UML关系如下：
连接与参数属性

参数属性名与属性值

连接相关的对象

参数使用

1、TCP连接
创建连接默认是通过com.mysql.cj.protocol.StandardSocketFactory做TCP连接，当然也可以通过socketFactory参数来配置
其中给socket配置的时候，使用到了tcpNoDelay，tcpKeepAlive，tcpRcvBuf，tcpSndBuf，tcpTrafficClass几个参数
连接地址使用到了ip, port参数
连接参数使用了connectTimeout和socketTimeout
可以通过useReadAheadInput，useUnbufferedInput两个bool参数决定使用哪种输入流
如果单次连接失败，还会根据initialTimeout(int)，maxReconnects(int)来重试
2、协议连接
协议连接配置是在com.mysql.cj.protocol.a.NativeProtocol里面做的，
其中使用到useNanosForElapsedTime，maintainTimeStats(bool)，maxQuerySizeToLog(int)，autoSlowLog(bool)，，maxAllowedPacket(int)，profileSQL(bool)，autoGenerateTestcaseScript(bool),useServerPrepStmts(bool),
慢查询相关logSlowQueries(bool),slowQueryThresholdMillis(int),useNanosForElapsedTime(bool),slowQueryThresholdNanos(int)
3、读取服务端参数
首先读取一个服务端发来的数据包，把服务端的参数设置到com.mysql.cj.protocol.a.NativeCapabilities对象里去，包括protocolVersion,serverVersion,threadId,seed,flag,capabilityFalgs，serverDefaultCollationIndex,authPluginDataLength
其中capabilityFalgs为服务端参数集，具体值及其意思，可以从com.mysql.cj.protocol.a.NativeServerSession中的那些值判断看出来

public static final int CLIENT_LONG_PASSWORD = 0x00000001; /* new more secure passwords */
public static final int CLIENT_FOUND_ROWS = 0x00000002;
public static final int CLIENT_LONG_FLAG = 0x00000004; /* Get all column flags */
public static final int CLIENT_CONNECT_WITH_DB = 0x00000008;
public static final int CLIENT_COMPRESS = 0x00000020; /* Can use compression protcol */
public static final int CLIENT_LOCAL_FILES = 0x00000080; /* Can use LOAD DATA LOCAL */
public static final int CLIENT_PROTOCOL_41 = 0x00000200; // for > 4.1.1
public static final int CLIENT_INTERACTIVE = 0x00000400;
public static final int CLIENT_SSL = 0x00000800;
public static final int CLIENT_TRANSACTIONS = 0x00002000; // Client knows about transactions
public static final int CLIENT_RESERVED = 0x00004000; // for 4.1.0 only
public static final int CLIENT_SECURE_CONNECTION = 0x00008000;
public static final int CLIENT_MULTI_STATEMENTS = 0x00010000; // Enable/disable multiquery support
public static final int CLIENT_MULTI_RESULTS = 0x00020000; // Enable/disable multi-results
public static final int CLIENT_PS_MULTI_RESULTS = 0x00040000; // Enable/disable multi-results for server prepared statements
public static final int CLIENT_PLUGIN_AUTH = 0x00080000;
public static final int CLIENT_CONNECT_ATTRS = 0x00100000;
public static final int CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA = 0x00200000;
public static final int CLIENT_CAN_HANDLE_EXPIRED_PASSWORD = 0x00400000;
public static final int CLIENT_SESSION_TRACK = 0x00800000;
public static final int CLIENT_DEPRECATE_EOF = 0x01000000;

4、配置客户端参数
根据capabilityFalgs及propertySet的值来设置clientParams的值，包括useCompression，createDatabaseIfNotExist，useAffectedRows，allowLoadLocalInfile，interactiveClient，allowMultiQueries，disconnectOnExpiredPasswords，connectionAttributes，
根据capabilityFalgs及propertySet的来useInformationSchema，sslMode做一些校验
还会使用defaultAuthenticationPlugin，disabledAuthenticationPlugins，authenticationPlugins，serverRSAPublicKeyFile，allowPublicKeyRetrieval来做认证插件相关配置
认证阶段，还会使用到user, password, database的信息
认证完之后，还会根据useCompression，traceProtocol，enablePacketDebug，packetDebugBufferSize来配置本地的环境
5、设置session参数可以通过参数sessionVariables来配置

//
public void setSessionVariables() {
    String sessionVariables = getPropertySet().getStringProperty(PropertyKey.sessionVariables).getValue();
    if (sessionVariables != null) {
        List<String> variablesToSet = new ArrayList<>();
        for (String part : StringUtils.split(sessionVariables, ",", "\"'(", "\"')", "\"'", true)) {
            variablesToSet.addAll(StringUtils.split(part, ";", "\"'(", "\"')", "\"'", true));
        }

        if (!variablesToSet.isEmpty()) {
            StringBuilder query = new StringBuilder("SET ");
            String separator = "";
            for (String variableToSet : variablesToSet) {
                if (variableToSet.length() > 0) {
                    query.append(separator);
                    if (!variableToSet.startsWith("@")) {
                        query.append("SESSION ");
                    }
                    query.append(variableToSet);
                    separator = ",";
                }
            }
            sendCommand(this.commandBuilder.buildComQuery(null, query.toString()), false, 0);
        }
    }
}

6、查询服务端参数
建立连接之后，会在com.mysql.cj.NativeSession里面请求服务端参数，并把参数存储到ServerSession中

//com.mysql.cj.NativeSession#loadServerVariables
 if (versionMeetsMinimum(5, 1, 0)) {
    StringBuilder queryBuf = new StringBuilder(versionComment).append("SELECT");
    queryBuf.append("  @@session.auto_increment_increment AS auto_increment_increment");
    queryBuf.append(", @@character_set_client AS character_set_client");
    queryBuf.append(", @@character_set_connection AS character_set_connection");
    queryBuf.append(", @@character_set_results AS character_set_results");
    queryBuf.append(", @@character_set_server AS character_set_server");
    queryBuf.append(", @@collation_server AS collation_server");
    queryBuf.append(", @@collation_connection AS collation_connection");
    queryBuf.append(", @@init_connect AS init_connect");
    queryBuf.append(", @@interactive_timeout AS interactive_timeout");
    if (!versionMeetsMinimum(5, 5, 0)) {
        queryBuf.append(", @@language AS language");
    }
    queryBuf.append(", @@license AS license");
    queryBuf.append(", @@lower_case_table_names AS lower_case_table_names");
    queryBuf.append(", @@max_allowed_packet AS max_allowed_packet");
    queryBuf.append(", @@net_write_timeout AS net_write_timeout");
    queryBuf.append(", @@performance_schema AS performance_schema");
    if (!versionMeetsMinimum(8, 0, 3)) {
        queryBuf.append(", @@query_cache_size AS query_cache_size");
        queryBuf.append(", @@query_cache_type AS query_cache_type");
    }
    queryBuf.append(", @@sql_mode AS sql_mode");
    queryBuf.append(", @@system_time_zone AS system_time_zone");
    queryBuf.append(", @@time_zone AS time_zone");
    if (versionMeetsMinimum(8, 0, 3) || (versionMeetsMinimum(5, 7, 20) && !versionMeetsMinimum(8, 0, 0))) {
        queryBuf.append(", @@transaction_isolation AS transaction_isolation");
    } else {
        queryBuf.append(", @@tx_isolation AS transaction_isolation");
    }
    queryBuf.append(", @@wait_timeout AS wait_timeout");

    NativePacketPayload resultPacket = sendCommand(this.commandBuilder.buildComQuery(null, queryBuf.toString()), false, 0);
    Resultset rs = ((NativeProtocol) this.protocol).readAllResults(-1, false, resultPacket, false, null,
            new ResultsetFactory(Type.FORWARD_ONLY, null));
    Field[] f = rs.getColumnDefinition().getFields();
    if (f.length > 0) {
        ValueFactory<String> vf = new StringValueFactory(this.propertySet);
        Row r;
        if ((r = rs.getRows().next()) != null) {
            for (int i = 0; i < f.length; i++) {
                this.protocol.getServerSession().getServerVariables().put(f[i].getColumnLabel(), r.getValue(i, vf));
            }
        }
    }

} else {
    NativePacketPayload resultPacket = sendCommand(this.commandBuilder.buildComQuery(null, versionComment + "SHOW VARIABLES"), false, 0);
    Resultset rs = ((NativeProtocol) this.protocol).readAllResults(-1, false, resultPacket, false, null,
            new ResultsetFactory(Type.FORWARD_ONLY, null));
    ValueFactory<String> vf = new StringValueFactory(this.propertySet);
    Row r;
    while ((r = rs.getRows().next()) != null) {
        this.protocol.getServerSession().getServerVariables().put(r.getValue(0, vf), r.getValue(1, vf));
    }
}

查询到这些数据之后，就可以做一些正常查询时候的设置与判断了