JDBC Database Connection

After installing the corresponding driver, you can use JDBC to establish a database connection.

The programming involved in establishing a JDBC connection is very simple. These are these four simple steps-

• Import JDBC package:Add a statement in the Java program import, to import the required classes in Java code.

• Register JDBC driver: This step makes the JVM load the required driver implementation into memory, so that it can meet your JDBC requests.

• Database URL configuration: This is to create a correctly formatted address that points to the database you want to connect to.

• Establish a connection object:Finally, write toDriverManagerObject'sgetConnection()method call code to establish the actual database connection.

Import JDBC package

Import statements tell the Java compiler where to find the classes referenced in the code and are placed at the beginning of the source code.

To use the standard JDBC package (which allows you to select, insert, update, and delete data in SQL tables), add the following import to the source code

import java.sql.*　;　　//　For standard JDBC programs
import java.math.*　;　//　Get BigDecimal and BigInteger support

Register JDBC driver

Before using the driver, you must register the driver in the program. Registering the driver is the process of loading the Oracle driver class file into memory, so it can be used as the implementation of the JDBC interface.

You only need to register the driver once in your program. You can register the driver in one of the following two ways.

Method I - Class.forName()

The most common method to register a driver in Java is Class.forName() Method, dynamically loads the driver class file into memory, and it will be automatically registered. This method is preferable because it allows you to make the driver registration configurable and portable.

The following example uses Class.forName() to register the Oracle driver

try {
　　　Class.forName("oracle.jdbc.driver.OracleDriver");

catch(ClassNotFoundException ex) {
　　　System.out.println("Error: unable to load driver class!");
　　　System.exit(1);

You can usegetInstance() methodTo solve the incompatibility with the JVM, but you must write two additional Exception codes as follows:

try {
　　　Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

catch(ClassNotFoundException ex) {
　　　System.out.println("Error: Unable to load the driver class!");
　　　System.exit(1);
catch(IllegalAccessException ex) {
　　　System.out.println("Error: Access problem occurred during loading!");
　　　System.exit(2);
catch(InstantiationException ex) {
　　　System.out.println("Error: Unable to instantiate the driver!");
　　　System.exit(3);

Method two- DriverManager.registerDriver()

The second method you can use to register a driver is to use the static method DriverManager.registerDriver() .

If the JVM (such as the JVM provided by Microsoft) used is not compatible with JDK, then you should use registerDriver() method.

The following example is used to register the Oracle driver using the registerDriver() method.-

try {
　　　Driver myDriver = new oracle.jdbc.driver.OracleDriver();
　　　DriverManager.registerDriver(myDriver);

catch(ClassNotFoundException ex) {
　　　System.out.println("Error: unable to load driver class!");
　　　System.exit(1);

Database URL format

After loading the driver, you can establish a connection using the DriverManager.getConnection() method. For reference, let me list the three overloaded methods of DriverManager.getConnection()

• getConnection(String url)

• getConnection(String url, Properties prop)

• getConnection(String url, String user, String password)

Here, each form requires a databaseURL.The database URL is the address pointing to your database.

Defining the database URL is where most issues related to establishing connections occur.

The following table lists common JDBC driver names and database URLs.

All highlighted parts in the URL format are static, you only need to change the rest according to the database settings.

Create a connection object

We listed three DriverManager.getConnection() The method to create a connection object.

Using the database URL with username and password

The most commonly used form of getConnection() requires you to pass the database URL,UsernameandPassword

Assuming you are using Oracle'sthinThe driver will specify the host:port:databaseName value for the database part of the URL.

If you are on TCP / IP address192.0.0.1and your Oracle listener is configured to listen on port1521and your database name is EMP, then the complete database URL will be-

jdbc:oracle:thin:@amrood:1521:EMP

Now, you must call the method getConnection() with the correct username and password to getConnectionObject, as shown below:

String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);

Only using the database URL

The second form of the DriverManager.getConnection() method only requires the database URL-

DriverManager.getConnection(String url);

However, in this case, the database URL contains the username and password and has the following conventional form-

jdbc:oracle:driver:username/password@database

Therefore, the above connection can be created as follows-

String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";
Connection conn = DriverManager.getConnection(URL);

Using the database URL and property object

The third form of the DriverManager.getConnection() method requires both the database URL and the Properties object-

DriverManager.getConnection(String url, Properties info);

The property object contains a set of keywords-Pair. It is used to pass driver properties to the driver during the call to getConnection() method.

To establish the same connection as the previous example, please use the following code-

import java.util.*;
String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
Properties info = new Properties();
info.put("user", "username");
info.put("password", "password");
Connection conn = DriverManager.getConnection(URL, info);

Close JDBC Connection

At the end of the JDBC program, it is required to explicitly close all connections to the database to end each database session. However, if you forget, the Java garbage collector will close the connection when cleaning up old objects.

Relying on garbage collection, especially in database programming, is a poor programming practice. You should develop the habit of always using the close() method associated with the connection object to close the database connection.

To ensure that the connection is closed, you can provide a 'finally' block in the code.finallyBlocks always execute, regardless of whether an exception occurs or not.

To close the connection opened above, you should call the close() method as follows:

conn.close();

Explicitly closing the connection can save DBMS resources, which will make your database administrator happy.

To better understand, we recommend that you learnJDBC-Example Code Tutorial.