Java jdbc close all connections

Best Way to Close JDBC Connection Object

In this post, we will discuss why we should close the JDBC connection object, what are the different ways to close the JDBC objects, and what is the best way among those.

Previously we were learning how to establish a JDBC connection with Oracle, JDBC connection with MySQL, JDBC Connection in Eclipse IDE, some Coding standards & guidelines for JDBC which should be followed at the time of developing a JDBC application. So, we don’t think much about the coding standards. In those applications, we have simply closed the JDBC objects in the main method as,

// close connection rs.close(); st.close(); con.close();

Where, rs.close() closes the ResultSet object, st.close() closes the Statement object and con.close() closes the Connection with database software.

Now, it’s time to talk about coding standards, what are different ways to close the JDBC objects and which is best?

Why we should close the JDBC connection at the end of the application?

The JDBC objects must be closed at the end of the application. Assume we are not closing the JDBC objects, and if many times this application will run then every time objects are created but they are not closed. Hence after a short time period, the memory will be filled up and it can stop the JVM execution. That’s why It is always better to close the database/resource objects after usage.

The order of closing the JDBC object:-

We should close the JDBC objects in reverse order of the object creation. The object creation order is:- con (Connection) then st (Statement) and at last the rs (ResultSet) object, so we should first close the rs (ResultSet) object, then st (Statement) object and at last con (Connection) object.

Writing JDBC closing statements in finally block

We should write the JDBC objects closing statements inside the finally block. The finally block is given in the exception handling for writing the resource-related logic. The finally block execution is compulsory after the execution of the try/catch block.

Why we should write JDBC objects closing statements inside the finally block, not in the try or catch block?

If we don’t want to write the JDBC closing statements inside the finally block then we have to write it inside try block (for success) and also inside all catch blocks (for failure).

But in this way, we need to write the same JDBC closing statements repeatedly. If there are 3 catch blocks are there then we need to write the JDBC closing statements 4 times, 3 times for each catch blocks when the exception is raised, and 1 time for try block when no exception is raised. It gives a code redundancy problem.

So, we should write the JDBC closing statements inside the finally block. The execution of the finally block is compulsory after the execution of try or catch block. Hence JDBC closing statements will be definitely executed.

Using try inside finally block to close connection

The JDBC closing statement can raise the SQLException so to handle those exceptions we should write those closing statements inside try block.

// not recommended approach finally < // close JDBC objects try< rs.close(); st.close(); con.close(); >catch(SQLException se) < se.printStackTrace(); >>

It isn’t a good approach to close the JDBC statements. Before the JDBC closing statement, we should check for the null. If the objects are not null then only close the JDBC objects.

// not recommended approach finally < // close JDBC objects try< if(rs!=null && st!=null && con!=null) < rs.close(); st.close(); con.close(); >> catch(SQLException se) < se.printStackTrace(); >>

Problem with the above code:- If at least one JDBC object is null then the if block will not be executed, and all JDBC objects will remain unclosed and writing finally block will be useless. For example:- If the exception raised at the statements rs.close() then the remaining JDBC objects will not be closed. So, it is not recommended to write this logic.

Best approach to close JDBC connection object

// best approach finally < // close JDBC objects try < if(rs!=null) rs.close(); >catch (SQLException se) < se.printStackTrace(); >try < if(st!=null) st.close(); >catch (SQLException se) < se.printStackTrace(); >try < if(con!=null) con.close(); >catch (SQLException se) < se.printStackTrace(); >>

Now, each JDBC object is closed separately. Exception raised for one object will not be reflected in the other JDBC objects, and if one object is null then other objects will not be disturbed and they will be closed independently. It is recommended to use this approach to close the JDBC objects.

• The rs.close() closes the ResultSet object and doesn’t allow to process the ResultSet object further.
• The st.close() closes the Statement object that is we can’t send further queries to database software using that statement object.
• The con.close() closes the connection with database software.

• Oracle Database JDBC Connection Example
• MySQL database connection in Java
• Eclipse database connection in Java
• Introduction to Java.sql package
• JDBC DriverManager class
• JDBC get Connection
• JDBC Statement interface
• JDBC statement example
• Different types of statements in JDBC
• JDBC PreparedStatement interface
• Statement vs PreParedStatement
• Insert record using PreparedStatement in Java
• Update record using Java PreparedStatement
• Select record using PreparedStatement in Java
• JDBC create table using statements
• Preparedstatement Example
• JDBC program to insert with Surrogate Key

Источник

JDBC — Database Connections

After you’ve installed the appropriate driver, it is time to establish a database connection using JDBC.

The programming involved to establish a JDBC connection is fairly simple. Here are these simple four steps −

• Import JDBC Packages − Add import statements to your Java program to import required classes in your Java code.
• Register JDBC Driver − This step causes the JVM to load the desired driver implementation into memory so it can fulfill your JDBC requests.
• Database URL Formulation − This is to create a properly formatted address that points to the database to which you wish to connect.
• Create Connection Object − Finally, code a call to the DriverManager object’s getConnection( ) method to establish actual database connection.

Import JDBC Packages

The Import statements tell the Java compiler where to find the classes you reference in your code and are placed at the very beginning of your source code.

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

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

Register JDBC Driver

You must register the driver in your program before you use it. Registering the driver is the process by which the Oracle driver’s class file is loaded into the memory, so it can be utilized as an implementation of the JDBC interfaces.

You need to do this registration only once in your program. You can register a driver in one of two ways.

Approach I — Class.forName()

The most common approach to register a driver is to use Java’s Class.forName() method, to dynamically load the driver’s class file into memory, which automatically registers it. 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)

You can use getInstance() method to work around noncompliant JVMs, but then you’ll have to code for two extra Exceptions as follows −

try < Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); >catch(ClassNotFoundException ex)

Approach II — DriverManager.registerDriver()

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

You should use the registerDriver() method if you are using a non-JDK compliant JVM, such as the one provided by Microsoft.

The following example uses registerDriver() to register the Oracle driver −

try < Driver myDriver = new oracle.jdbc.driver.OracleDriver(); DriverManager.registerDriver( myDriver ); >catch(ClassNotFoundException ex)

Database URL Formulation

After you’ve loaded the driver, you can establish a connection using the DriverManager.getConnection() method. For easy reference, let me list the three overloaded DriverManager.getConnection() methods −

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

Here each form requires a database URL. A database URL is an address that points to your database.

Formulating a database URL is where most of the problems associated with establishing a connection occurs.

Following table lists down the popular JDBC driver names and database URL.

RDBMS	JDBC driver name	URL format
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://hostname/ databaseName
ORACLE	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@hostname:port Number:databaseName
DB2	COM.ibm.db2.jdbc.net.DB2Driver	jdbc:db2:hostname:port Number/databaseName
Sybase	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds:hostname: port Number/databaseName

All the highlighted part in URL format is static and you need to change only the remaining part as per your database setup.

Create Connection Object

We have listed down three forms of DriverManager.getConnection() method to create a connection object.

Using a Database URL with a username and password

The most commonly used form of getConnection() requires you to pass a database URL, a username, and a password −

Assuming you are using Oracle’s thin driver, you’ll specify a host:port:databaseName value for the database portion of the URL.

If you have a host at TCP/IP address 192.0.0.1 with a host name of amrood, and your Oracle listener is configured to listen on port 1521, and your database name is EMP, then complete database URL would be −

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

Now you have to call getConnection() method with appropriate username and password to get a Connection object as follows −

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

Using Only a Database URL

A second form of the DriverManager.getConnection( ) method requires only a database URL −

DriverManager.getConnection(String url);

However, in this case, the database URL includes the username and password and has the following general form −

jdbc:oracle:driver:username/password@database

So, the above connection can be created as follows −

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

Using a Database URL and a Properties Object

A third form of the DriverManager.getConnection( ) method requires a database URL and a Properties object −

DriverManager.getConnection(String url, Properties info);

A Properties object holds a set of keyword-value pairs. It is used to pass driver properties to the driver during a call to the getConnection() method.

To make the same connection made by the previous examples, 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);

Closing JDBC Connections

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

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

To ensure that a connection is closed, you could provide a ‘finally’ block in your code. A finally block always executes, regardless of an exception occurs or not.

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

Explicitly closing a connection conserves DBMS resources, which will make your database administrator happy.

For a better understanding, we suggest you to study our JDBC — Sample Code tutorial.

Источник