JDBC URL Format For Different Databases – 用于不同数据库的JDBC URL格式

1. Overview

1.概述

When we work with a database in Java, usually we connect to the database with JDBC.

当我们在Java中使用数据库时，通常我们用JDBC连接到数据库。

The JDBC URL is an important parameter to establish the connection between our Java application and the database. However, the JDBC URL format can be different for different database systems.

JDBC URL是在我们的Java应用程序和数据库之间建立连接的一个重要参数。然而，JDBC URL的格式对于不同的数据库系统可能是不同的。

In this tutorial, we’ll take a closer look at the JDBC URL formats of several widely used databases: Oracle, MySQL, Microsoft SQL Server, and PostgreSQL.

在本教程中，我们将仔细研究几个广泛使用的数据库的JDBC URL格式。Oracle、MySQL、Microsoft SQL Server，以及PostgreSQL。

2. JDBC URL Formats for Oracle

2.Oracle的JDBC URL格式

Oracle database systems are widely used in enterprise Java applications. Before we can take a look at the format of the JDBC URL to connect Oracle databases, we should first make sure the Oracle Thin database driver is in our classpath.

Oracle数据库系统被广泛用于企业的Java应用中。在我们看一下连接Oracle数据库的JDBC URL的格式之前，我们首先应该确保Oracle Thin数据库驱动在我们的classpath中。

For example, if our project is managed by Maven, we need to add the ojdbc8 dependency in our pom.xml:

例如，如果我们的项目由Maven管理，我们需要在pom.xml中添加ojdbc8 依赖。

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>21.1.0.0</version>
</dependency>

The Thin driver offers several kinds of JDBC URL formats:

Thin驱动提供了几种JDBC URL格式。

• Connecting to SID
• Connecting to Oracle service name
• URL with tnsnames.ora entries

Next, we’ll go through each of these formats.

接下来，我们将逐一介绍这些格式。

2.1. Connect to Oracle Database SID

2.1.连接到Oracle数据库SID

In some older versions of the Oracle database, the database is defined as a SID. Let’s see the JDBC URL format for connecting to a SID:

在一些旧版本的Oracle数据库中，数据库被定义为一个SID。让我们看看连接到SID的JDBC URL格式。

jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID>

For example, assuming we have an Oracle database server host “myoracle.db.server:1521“, and the name of the SID is “my_sid“, we can follow the format above to build the connection URL and connect to the database:

例如，假设我们有一个Oracle数据库服务器主机”myoracle.db.server:1521“，SID的名字是”my_sid“，我们可以按照上面的格式建立连接URL并连接到数据库。

@Test
public void givenOracleSID_thenCreateConnectionObject() {
    String oracleJdbcUrl = "jdbc:oracle:thin:@myoracle.db.server:1521:my_sid";
    String username = "dbUser";
    String password = "1234567";
    try (Connection conn = DriverManager.getConnection(oracleJdbcUrl, username, password)) {
        assertNotNull(conn);
    } catch (SQLException e) {
        System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
    }
}

2.2. Connect to Oracle Database Service Name

2.2.连接到Oracle数据库服务名称

The format of the JDBC URL to connect Oracle databases via service name is pretty similar to the one we used to connect via SID:

通过服务名称连接Oracle数据库的JDBC URL的格式与我们用于通过SID连接的格式相当相似。

jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service>

We can connect to the service “my_servicename” on the Oracle database server “myoracle.db.server:1521“:

我们可以连接到Oracle数据库服务器”my_servicename“上的服务myoracle.db.server:1521。

@Test
public void givenOracleServiceName_thenCreateConnectionObject() {
    String oracleJdbcUrl = "jdbc:oracle:thin:@//myoracle.db.server:1521/my_servicename";
    ...
    try (Connection conn = DriverManager.getConnection(oracleJdbcUrl, username, password)) {
        assertNotNull(conn);
        ...
    }
    ...
}

2.3. Connect to Oracle Database With tnsnames.ora Entries

2.3.用tnsnames.ora条目连接到Oracle数据库

We can also include tnsnames.ora entries in the JDBC URL to connect to Oracle databases:

我们还可以在JDBC URL中包括tnsnames.ora项，以连接到Oracle数据库。

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<service>)))

Let’s see how to connect to our “my_servicename” service using entries from the tnsnames.ora file:

让我们看看如何使用tnsnames.ora文件中的条目连接到”my_servicename“服务。

@Test
public void givenOracleTnsnames_thenCreateConnectionObject() {
    String oracleJdbcUrl = "jdbc:oracle:thin:@" +
      "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" +
      "(HOST=myoracle.db.server)(PORT=1521))" +
      "(CONNECT_DATA=(SERVICE_NAME=my_servicename)))";
    ...
    try (Connection conn = DriverManager.getConnection(oracleJdbcUrl, username, password)) {
        assertNotNull(conn);
        ...
    }
    ...
}

3. JDBC URL Formats for MySQL

3.用于MySQL的JDBC URL格式

In this section, let’s discuss how to write the JDBC URL to connect to MySQL databases.

在本节中，让我们讨论如何编写JDBC URL以连接到MySQL数据库。

To connect to a MySQL database from our Java application, let’s first add the JDBC driver mysql-connector-java dependency in our pom.xml:

为了从我们的Java应用程序连接到MySQL数据库，我们首先在pom.xml中添加JDBC驱动程序mysql-connector-java依赖。

<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:

接下来，让我们看看MySQL JDBC驱动程序所支持的连接URL的通用格式。

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

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

让我们看看连接到主机”mysql.db.server“上的MySQL数据库”my_database“的一个例子。

@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:

上面例子中的JDBC URL看起来很简单。它有四个结构块。

• 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.

然而，有时我们可能会面临更复杂的情况，如不同类型的连接或多个MySQL主机，等等。

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

接下来，我们将仔细看看每个构件。

3.1. Protocol

3.1. 议定书

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

除了普通的”jdbc:mysql:“协议外，connector-java JDBC 驱动程序仍然支持一些特殊连接的协议：。

• 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.

当我们谈论负载平衡和JDBC复制时，我们可能意识到应该有多个MySQL主机。

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

接下来，让我们看看连接URL的另一部分的细节–hosts。

3.2. Hosts

3.2.主机

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

我们已经在前一节中看到了定义单一主机的JDBC URL例子–例如，mysql.db.server:3306.。

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

然而，如果我们需要处理多个主机，我们可以用逗号分隔的列表列出主机。host1, host2,…, hostN.。

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

我们也可以用方括号将逗号分隔的主机列表括起来。[host1, host2,…, hostN].

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

让我们看看几个连接到多个MySQL服务器的JDBC URL例子。

• 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

3.3.属性和用户凭证

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:

有效的全局属性将被应用于所有的主机。属性前面有一个问号”?“，写成key=value对，用”&“符号分开。

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“:

此外，我们可以在每个主机前加上格式为”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 URL包含一个主机列表，并且所有主机都使用相同的用户凭证，我们可以给主机列表加上前缀。

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.

毕竟，也可以在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.

当我们调用获取连接的方法时，我们可以将用户名和密码传递给DriverManager.getConnection(String url, String user, String password)/a>方法。

4. JDBC URL Format for Microsoft SQL Server

4.Microsoft SQL Server的JDBC URL格式

Microsoft SQL Server is another popular database system. To connect an MS SQL Server database from a Java application, we need to add the mssql-jdbc dependency into our pom.xml:

Microsoft SQL Server是另一个流行的数据库系统。为了从Java应用程序中连接MS SQL Server数据库，我们需要将mssql-jdbc依赖性加入我们的pom.xml。

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>8.4.1.jre11</version>
</dependency>

Next, let’s look at how to build the JDBC URL to obtain a connection to MS SQL Server.

接下来，让我们看看如何建立JDBC URL以获得与MS SQL Server的连接。

The general format of the JDBC URL for connection to the MS SQL Server database is:

连接到MS SQL Server数据库的JDBC URL的一般格式是。

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

Let’s have a closer look at each part of the format.

让我们仔细看看格式的每个部分。

• serverName – the address of the server we’ll connect to; this could be a domain name or IP address pointing to the server
• instanceName – the instance to connect to on serverName; it’s an optional field, and the default instance will be chosen if the field isn’t specified
• portNumber – this is the port to connect to on serverName (default port is 1433)
• properties – can contain one or more optional connection properties, which must be delimited by the semicolon, and duplicate property names are not allowed

Now, let’s say we have an MS SQL Server database running on host “mssql.db.server“, the instanceName on the server is “mssql_instance“, and the name of the database we want to connect is “my_database“.

现在，假设我们有一个运行在主机”mssql.db.server“上的MS SQL Server数据库，服务器上的instanceName是”mssql_instance“，而我们想要连接的数据库名称是”my_database“。

Let’s try to obtain the connection to this database:

让我们尝试获得与该数据库的连接。

@Test
public void givenMssqlDb_thenCreateConnectionObject() {
    String jdbcUrl = "jdbc:sqlserver://mssql.db.server\\mssql_instance;databaseName=my_database";
    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());
    }
}

5. JDBC URL Format for PostgreSQL

5.用于PostgreSQL的JDBC URL格式

PostgreSQL is a popular, open-source database system. To work with PostgreSQL, the JDBC driver postgresql should be added as a dependency in our pom.xml:

PostgreSQL是一个流行的、开源的数据库系统。为了与PostgreSQL一起工作，JDBC驱动程序postgresql应该被添加到我们的pom.xml中作为一个依赖。

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.18</version>
</dependency>

The general form of the JDBC URL to connect to PostgreSQL is:

连接到PostgreSQL的JDBC URL的一般形式是。

jdbc:postgresql://host:port/database?properties

Now, let’s look into each part in the above JDBC URL format.

现在，让我们来看看上述JDBC URL格式中的每个部分。

The host parameter is the domain name or IP address of the database server.

host参数是数据库服务器的域名或IP地址。

If we want to specify an IPv6 address, the host parameter must be enclosed by square brackets, for example, jdbc:postgresql://[::1]:5740/my_database.mysql

如果我们要指定一个IPv6地址，host参数必须用方括号括起来，例如，jdbc:postgresql://[:1]:5740/my_database.mysql。

The port parameter specifies the port number PostgreSQL is listening on. The port parameter is optional, and the default port number is 5432.

port参数指定PostgreSQL监听的端口号。端口参数是可选的，默认端口号是5432。

As its name implies, the database parameter defines the name of the database we want to connect to.

顾名思义，database参数定义了我们要连接的数据库的名称。

The properties parameter can contain a group of key=value pairs separated by the “&” symbol.

properties参数可以包含一组key=value对，用”&“符号分隔。

After understanding the parameters in the JDBC URL format, let’s see an example of how to obtain the connection to a PostgreSQL database:

在了解了JDBC URL格式中的参数后，让我们看一个如何获得与PostgreSQL数据库连接的例子。

@Test
public void givenPostgreSqlDb_thenCreateConnectionObject() {
    String jdbcUrl = "jdbc:postgresql://postgresql.db.server:5430/my_database?ssl=true&loglevel=2";
    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());
    }
}

In the example above, we connect to a PostgreSQL database with:

在上面的例子中，我们用以下方式连接到一个PostgreSQL数据库。

• host:port – postgresql.db.server:5430
• database – my_database
• properties – ssl=true&loglevel=2

6. Conclusion

6.结论

This article discussed the JDBC URL formats of four widely used database systems: Oracle, MySQL, Microsoft SQL Server, and PostgreSQL.

这篇文章讨论了四个广泛使用的数据库系统的JDBC URL格式。Oracle、MySQL、Microsoft SQL Server和PostgreSQL。

We’ve also seen different examples of building the JDBC URL string to obtain connections to those databases.

我们还看到了建立JDBC URL字符串以获得对这些数据库的连接的不同例子。

As always, the full source code of the article is available over on GitHub.

一如既往，该文章的完整源代码可在GitHub上获得。