MySQL的JDBC URL格式

【暂时援引英文】In this section, let's discuss how to write the JDBC URL to connect to MySQL databases.

To connect to a MySQL database from our Java application, let's first add the JDBC driver Maven Central Repository Search in our pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.22</version>
</dependency>

Next, let's take a look at the generic format of the connection URL supported by the MySQL JDBC driver:

protocol//[hosts][/database][?properties]

Let's see an example of connecting to the MySQL database “my_database” on the host “mysql.db.server“:

@Test
public void givenMysqlDb_thenCreateConnectionObject() {
    String jdbcUrl = "jdbc:mysql://mysql.db.server:3306/my_database?useSSL=false&serverTimezone=UTC";    
    String username = "dbUser";
    String password = "1234567";
    try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
        assertNotNull(conn);
    } catch (SQLException e) {
        System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
    }
}

The JDBC URL in the example above looks straightforward. It has four building blocks:

• protocol – jdbc:mysql:
• host – mysql.db.server:3306
• database – my_database
• properties – useSSL=false&serverTimezone=UTC

However, sometimes, we may face more complex situations, such as different types of connections or multiple MySQL hosts, and so on.

Next, we'll take a closer look at each building block.

3.1. Protocol

Except for the ordinary “jdbc:mysql:” protocol, the connector-java JDBC driver still supports protocols for some special connections:

• Load-balancing JDBC connections – jdbc:mysql:loadbalance:
• JDBC replication connections – jdbc:mysql:replication:

When we talk about the load-balancing and JDBC replication, we may realize that there should be multiple MySQL hosts.

Next, let's check out the details of another part of the connection URL — hosts.

3.2. Hosts

We've seen the JDBC URL example of defining a single host in a previous section — for example, mysql.db.server:3306.

However, if we need to handle multiple hosts, we can list hosts in a comma-separated list: host1, host2,…,hostN.

We can also enclose the comma-separated host list by square brackets: [host1, host2,…,hostN].

Let's see several JDBC URL examples of connecting to multiple MySQL servers:

• jdbc:mysql://myhost1:3306,myhost2:3307/db_name
• jdbc:mysql://[myhost1:3306,myhost2:3307]/db_name
• jdbc:mysql:loadbalance://myhost1:3306,myhost2:3307/db_name?user=dbUser&password=1234567&loadBalanceConnectionGroup=group_name&ha.enableJMX=true

If we look at the last example above closely, we'll see that after the database name, there are some definitions of properties and user credentials. We'll look at these next.

3.3. Properties and User Credentials

Valid global properties will be applied to all hosts. Properties are preceded by a question mark “?” and written as key=value pairs separated by the “&” symbol:

jdbc:mysql://myhost1:3306/db_name?prop1=value1&prop2=value2

We can put user credentials in the properties list as well:

jdbc:mysql://myhost1:3306/db_name?user=root&password=mypass

Also, we can prefix each host with the user credentials in the format “user:password@host“:

jdbc:mysql://root:mypass@myhost1:3306/db_name

Further, if our JDBC URL contains a list of hosts and all hosts use the same user credentials, we can prefix the host list:

jdbc:mysql://root:mypass[myhost1:3306,myhost2:3307]/db_name

After all, it is also possible to provide the user credentials outside the JDBC URL.

We can pass the username and password to the DriverManager.getConnection(String url, String user, String password) method when we call the method to obtain a connection.

JDBC URL Format For Different Databases：JDBC URL Format For Different Databases | Baeldung