Updated April 18, 2023
Introduction to JDBC URL Format
JDBC URL format is used for establishing the database connection with your respective database that you will be used for storing the data for your application written in java language. The Java Database Connectivity API requires a unique URL for connecting to the database which contains all the information about the database server. This varies with respect to the database you will be using. In this article, we will discuss the formation of database URL format in JDBC for the three most widely used databases such as Microsoft SQL Server, MySQL and Oracle. We will learn about its syntax and how it works along with the help of its implementation and examples.
URL Format in Oracle
When you are trying to have a connection of java application with a database of the oracle, you should make sure that the driver of the oracle thin database is installed in our machine in the same classpath. In the case of using maven for project dependency management, you should have the odbc8 dependency tag added in your pox.xml file of the project. There are many formats of URL for JDBC provided by oracle thin driver which are specified below –
Connecting to the database of oracle SID.
Syntax:
Jdbc:oracle:thin:[<name of user>/ <associated password>]@<name of host>[:<address of port>]:<SID>Example
@Test
public void createJDBCconnectionSID() {
String oracleJdbcUrl = "jdbc:oracle:thin:@databaseOfOracle.db.server:1521:myEDUCBADatabase";
String sampleUser = "educbaPayal";
String samplePassword = "payal@123";
try (Connection myConnection= DriverManager.getConnection(oracleJdbcUrl, sampleUser, samplePassword )) {
System.log("Connection Established Successfully!");
assertNotNull(myConnection);
} catch (SQLException sampleException) {
System.err.format("State of SQL : %s\n%s", sampleException.getSQLState(), sampleException.getMessage());
}
}The output of the above code is as shown below if the connection is established by using SID in JDBC –
The second method for oracle is to connect to the service name of the oracle database whose syntax is as specified below –
jdbc:oracle:thin:[<name of user>/<associated password>]@//<name of host>[:<address of port>]/<name of service>The example that shows us the implementation of the same is demonstrated below –
@Test
public void createJDBCconnectionOracleService() {
String sampleURLJDBCOfEDUCBA = "jdbc:oracle:thin:@//databaseOfOracle.db.server:1521/educbaServiceName";
...
try (Connection educbaConnection = DriverManager.getConnection(sampleURLJDBCOfEDUCBA , educbaPayal, payal@123)) {
System.log("Connection with oracle Database established by using specified service name!");
assertNotNull(educbaConnection );
...
}
...
}The third way of establishing a connection to the oracle database by using JDBC is using the tnsnames.ora entries. These entries can be included in JDBC URL by using the below syntax –
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<name of host>)(PORT=<address of port>))(CONNECT_DATA=(SERVICE_NAME=<name of your service>)))Let us consider the example of specifying the URL in this format by using the below extract of code –
@Test
public void establishJDBCConnectionByUsingTNSNames() {
String sampleEducbaJDBCConn= "jdbc:oracle:thin:@" +
"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" +
"(HOST=educbaPayal.db.server)(PORT=1521))" +
"(CONNECT_DATA=(SERVICE_NAME=educbaJDBCService)))";
...
try (Connection conn = DriverManager.getConnection(sampleEducbaJDBCConn, payal, payal@123)) {
System.log("Connection with oracle Database established by using TNS names!");
assertNotNull(conn);
...
}
...
}The output of the above code is as shown below –
MySQL JDBC URL format –
Now, let us study the format of URL used for establishing the connection between the java application and the MySQL database. For this, it is required that you have your MySQL-connector-java dependency specified in your pom.xml file of your Maven project as shown below –
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version> </dependency>The syntax of the URL format for MySQL database is as shown below –
Protocol being used // [name of host][/ name of database][? properties]Let us consider one example to understand the implementation of the above-mentioned syntax of URL for MySQL database with the code extract specified here –
@Test
public void createJDBCConnectionForMySQL() {
String MySQLDatabaseURL= "jdbc:mysql://mysql.db.server:3306/educbaDatabase?useSSL=false&serverTimezone=UTC";
String nameOfUser = "Payal";
String associatedPassword = "Payal@123";
try (Connection sampleConnection = DriverManager.getConnection(MySQLDatabaseURL, nameOfUser , associatedPassword )) {
System.log("Connection with MySQL Database established!");
assertNotNull(sampleConnection);
} catch (SQLException sampleException) {
System.err.format("State of SQL: %s\n%s", sampleException.getSQLState(), sampleException.getMessage());
}
}The output of the above code if the connection gets established with MySQL database is as shown below –
The JDBC URL format used for MySQL has the following specified terminologies in its syntax and in the example are –
Protocol being used – jdbc:mysql
name of host – mysql.db.server:3306
name of database – educbaDatabase
properties – useSSL=false&serverTimezone=UTCMicrosoft SQL Server URL format for JDBC –
In order to establish the connection with SQL server, we have to install the dependency of mysql-jdbc dependency in our maven project’s pom.xml file as shown below –
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>8.4.1.jre11</version>
</dependency>The syntax for establishing the connection with our Microsoft SQL database server is as shown below –
jdbc:sqlserver://[nameOfServer[\nameOfInstance][:numberOfPort]][;property=value[;property=value]]The terminologies used in the above syntax are described below –
- nameOfServer – In order to connect with the server, we will specify the address of the server which can be the name of the domain or the associated IP address that belongs to the database server location.
- nameOfInstance – This is the optional field which will help in the specification of the name of the server where we need to connect. In case if we don’t specify the value of this field then the default instance is considered.
- numberOfPort – The address or the number of the port where we have to connect to the server’s name is the number of ports which has the default value of 1433
- properties – This contains the optional properties that specify the extra additional properties associated with the connection. In the case of multiple properties, we can make the use of a delimiter of semicolons to separate all of them. Note that the duplication of the same property is not allowed in the URL.
Example
Let us consider one example which will help us to implement the connection with MS SQL database connection from java application –
@Test
public void creationOfMSSQLConnectionJDBC() {
String jdbcUrl = "jdbc:sqlserver://mssql.db.server\\instanceOfMsSQL;databaseName=educba";
String nameOfUser = "Payal";
String associatedPassword = "Payal@123";
try (Connection sampleConnection = DriverManager.getConnection(jdbcUrl, nameOfUser, associatedPassword )) {
assertNotNull(sampleConnection);
} catch (SQLException sampleException) {
System.log ("Connection Established with MS SQL Database Server!");
System.err.format("State of SQL : %s\n%s", sampleException.getSQLState(), sampleException.getMessage());
}
}The output of the execution of the above extract of code is as shown in the below image –
Conclusion
The JDBC URL format varies depending on the database you are trying to connect to from your java application. In this article, we learnt about the syntax, required dependency, and the example for connecting to some of the popular databases such as Microsoft SQL, MySQL, and Oracle using JDBC URL.
Recommended Articles
This is a guide to JDBC URL Format. Here we discuss the Introduction, URL Format in Oracle, and examples respectively. You may also have a look at the following articles to learn more –
