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.xml
add ojdbc14
dependencies :
<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.ora
URL 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:1521
the 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.ora
entry to connect to Oracle database
We can also include an tnsnames.ora
entry 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.ora
connect 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.xml
add the JDBC driver mysql-connector-java
dependency :
<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.server
of 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-java
JDBC 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=value
pairs, 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-jdbc
dependency 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 serverinstanceName –
ToserverName
instance connected; This is an optional field, if this field is not specified, the default instance selectedportNumber
–This isserverName
the port to connect on (the default port is1433
)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.server
MS SQL Server database running on the host " ", the server instanceName
is" 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.xml
the JDBC driver should be postgresql
added 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.
host
The parameter is the domain name or IP address of the database server.
If you want to specify an IPv6 address, the host
parameters must be enclosed in square brackets, such as jdbc:postgresql://[::1]:5740/my_database
.mysql
port
The 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 database
parameter defines the name of the database we want to connect to.
properties
The parameter can contain &
a set of key=value
pairs 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.
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