• notice
  • Congratulations on the launch of the Sought Tech site

JDBC URL format for different databases

1 Overview

When we use Java to deal with the database, usually we use JDBC to connect to the database.

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

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

2.Oracle's JDBC URL format

The Oracle database system is widely used in enterprise Java applications. Before looking at the JDBC URL format used to connect to the Oracle database, we should first make sure that the Oracle Thin database driver is in our classpath.

For example, if our project is managed by Maven, we need to pom.xmladd ojdbc14dependencies :

<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc14</artifactId>
<version>10.2.0.4.0</version>
</dependency>

Due to some licensing issues, the Maven Central repository only points to the POM file of this artifact Therefore, we need to download the jar and install it manually into our Maven repository.

The Thin driver provides several JDBC URL formats:

  • Connect to SID

  • Connect to Oracle service name

  • tnsnames.oraURL with entry

Next, we will introduce each format.

2.1. Connect to Oracle database SID

In some older versions of Oracle databases, the database is defined as SID. Let's look at the JDBC URL format used to connect to the SID:

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

For example, suppose we have an Oracle database server host " myoracle.db.server:1521", and the name of the SID is" my_sid", we can construct the connection URL and connect to the database according to the above format:

@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 the Oracle database service name

The format of the JDBC URL to connect to the Oracle database via the service name is very similar to the format we used to connect via SID:

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

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

@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. Use tnsnames.oraentry to connect to Oracle database

We can also include an tnsnames.oraentry in the JDBC URL to connect to the Oracle database:

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

Let's see how to tnsnames.oraconnect to the " my_servicename" service using the entry in the file :

@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.MySQL JDBC URL format

In this section, we discuss how to write a JDBC URL to connect to a MySQL database.

To connect to a MySQL database from a Java application, first let us pom.xmladd the JDBC driver mysql-connector-javadependency :

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

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

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

Let us see an example mysql.db.serverof connecting to the MySQL database " my_database" on the host " " :

@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 very simple. It has four components:

  • protocol - jdbc:mysql:

  • host - mysql.db.server:3306

  • database - my_database

  • properties - useSSL=false&serverTimezone=UTC

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

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

3.1. protocol

In addition to the ordinary " jdbc:mysql:" protocol, the connector-javaJDBC driver still supports some special connection protocols:

  • Load balancing JDBC connection –jdbc:mysql:loadbalance:

  • Copy the JDBC connection -jdbc:mysql:replication:

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

Next, let's check the details of the other part of the connection URL hosts.

3.2 Multiple domain names

In the previous section, we have seen an example of a JDBC URL that defines a single host, such asmysql.db.server:3306.

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

We can also use square brackets [host1, host2,…,hostN]with a comma-separated list of hosts: [host1, host2,…,hostN].

Let's look at a few examples of JDBC URLs 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 take a closer look at the last example above, we will see that after the database name, there are definitions of some attributes and user credentials. Next, we will introduce these.

3.3. Attributes and user credentials

The effective global attributes will be applied to all hosts. The attributes are preceded by a question mark " ??", written in key=valuepairs, and &separated by the " "** symbol** :

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

We can also put the user credentials in the attribute list :

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

Similarly, we can add the prefix of the user credentials for each host in the format " user:[email protected]" :

jdbc:mysql://root:[email protected]:3306/db_name

In addition, if our JDBC URL contains a host list, and all hosts use the same user credentials, we can add a prefix before the host list :

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

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

When we call the method to get the connection, we DriverManager.getConnection(String url, String user, String password)can pass the username and password to the DriverManager.getConnection(String url, String user, String password)method.

4.JDBC URL format of Microsoft SQL Server

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

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

Next, let's see how to construct a JDBC URL to get a connection with MS SQL Server.

The general format of the JDBC URL used to connect to the MS SQL Server database is:

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

Let's take a closer look at each part of the format.

  • serverName –The address of the server we will connect to; this may be the domain name or IP address that points to the server

  • instanceName –To serverNameinstance connected; This is an optional field, if this field is not specified, the default instance selected

  • portNumber–This is serverNamethe port to connect on (the default port is 1433)

  • properties -Can contain one or more optional connection attributes, which must be separated by semicolons, and duplicate attribute names are not allowed

Now, suppose we have a mssql.db.serverMS SQL Server database running on the host " ", the server instanceNameis" mssql_instance", and the name of the database we want to connect to is " my_database".

Let's try to get a 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.PostgreSQL JDBC URL format

PostgreSQL is a popular open source database system. To use PostgreSQL, pom.xmlthe JDBC driver should be postgresqladded as a dependency in ours :

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

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

Now, let us study each part of the above JDBC URL format.

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

If you want to specify an IPv6 address, the hostparameters must be enclosed in square brackets, such as jdbc:postgresql://[::1]:5740/my_database.mysql

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

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

propertiesThe parameter can contain &a set of key=valuepairs separated by the " " symbol .

After understanding the parameters of the JDBC URL format, let us look at an example of how to obtain a connection to the PostgreSQL database:

@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 above example, we use the following command to connect to the PostgreSQL database:

  • host:port – postgresql.db.server:5430

  • database - my_database

  • Attributes- ssl=true&loglevel=2

Six, conclusion

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

We have also seen different examples of constructing JDBC URL strings to obtain connections to these databases.


Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

buy lipitor 40mg online & lt;a href="https://lipiws.top/"& gt;order atorvastatin 40mg online cheap& lt;/a& gt; buy atorvastatin without a preion

Ndljlx

2024-03-07

Leave a Reply

+