JDBC Connection
Jakob Jenkov |
The JDBC Connection class, java.sql.Connection, represents a database connection to a relational database.
Before you can read or write data from and to a database via JDBC, you need to open a connection to the database.
This JDBC connection tutorial will show you how to do that.
Loading the JDBC Driver
The first thing you need to do before you can open a JDBC connection to a database is to load the JDBC driver for the database. Actually, from Java 6 this is no longer necessary, but doing so will not fail. You load the JDBC driver like this:
Class.forName("driverClassName");
Each JDBC driver has a primary driver class that initializes the driver when it is loaded. For instance, to load the H2Database driver, you write this:
Class.forName("org.h2.Driver");
You only have to load the driver once. You do not need to load it before every connection opened. Only before the first JDBC connection opened.
Opening the JDBC Connection
You open a JDBC Connection by call the java.sql.DriverManager class method
getConnection(). There are three variants of this method. I will show each variant in the
following sections.
Open Connection With URL
The first method variant only takes a URL to the database as parameter. This is how calling
getConnection() only with the URL as parameter looks:
String url = "jdbc:h2:~/test"; //database specific url.
Connection connection =
DriverManager.getConnection(url);
The url is the url to your database. You should check the documentation for your database and
JDBC driver to see what the format is for your specific database. The url shown above is for
a H2Database.
Open Connection With URL, User and Password
The second variant of getConnection() takes both a database URL, a user name and a password as
parameters. Here is an example of calling that variant of getConnection() :
String url = "jdbc:h2:~/test"; //database specific url.
String user = "sa";
String password = "";
Connection connection =
DriverManager.getConnection(url, user, password);
The user and password parameters are the user name and password for your database.
Open Connection With URL and Properties
The third variant of getConnection() takes a database URL and a Properties object
as parameter. Here is an example of calling this variant of getConnection():
String url = "jdbc:h2:~/test"; //database specific url.
Properties properties = new Properties( );
properties.put( "user", "sa" );
properties.put( "password", "" );
Connection connection =
DriverManager.getConnection(url, properties);
The Properties object is used to pass special properties the database needs when opening the
connection. Exactly what properties a given database needs, depends on the database and its features etc.
You will have to check the documentation for given database and its JDBC driver to see that.
Closing the JDBC Connection
Once you are done using the database connection you should close it. This is done by calling the Connection.close() method, like this:
connection.close();
It is important to close a JDBC Connection once you are done with it. A database connection takes up an amount of resources, both inside your own application, but especially on the database server. Therefore, keeping database connections open that are unused will require the database to keep unnecessary resources allocated for the connection.
Closing the Connection via Try-With-Resources
It is possible to close a JDBC Connection automatically via the
Java Try-with-resources construct that was added
in Java 7. Here is an example of how to do that:
String url = "jdbc:h2:~/test"; //database specific url.
String user = "sa";
String password = "";
try(Connection connection =
DriverManager.getConnection(url, user, password)) {
//use the JDBC Connection inhere
}
As you can see, the JDBC Connection is opened within the parentheses in the try block.
Inside the try block you can use the database connection as you normally would. Once the execution
exits the try block, the JDBC Connection will get closed automatically for you.
That way you do not forget to close the JDBC Connection yourself.
setAutoCommit()
The JDBC Connection setAutoCommit() method is used to switch the connection into, or
out of, auto commit mode. In auto commit mode each single update sent to the database will be committed immediately,
as if executed within its own transaction. When not in auto commit mode, each database transaction must be
explicitly committed by calling the Connection commit() method. This is explained in
more detail in the tutorial about JDBC Transactions.
Here is an example of switching a JDBC Connection into auto commit mode:
connection.setAutoCommit(true);
And here is an example of switching a JDBC Connection out of auto commit mode:
connection.setAutoCommit(false);
The default mode of a JDBC Connection if the auto commit mode is not specified is to
have auto commit mode switched on.
commit()
The JDBC Connection commit() method commits a transaction. Exactly how transactions
work and should be handled is covered in the JDBC Transactions Tutorial .
Here is a simple example of committing a transaction via a JDBC Connection. Please note that
the correct exception handling has been kept out of this example to make it brief.
connection.setAutoCommit(false); // perform operations on the JDBC Connection // which are to be part of the transaction connection.commit();
Keep in mind, that if some of the operations in the transaction fail, you would most likely want to call
the rollback() method instead of commit().
rollback()
The Java JDBC Connection rollback() method rolls back the operations executed
within the currently ongoing transaction. Exactly how to handle the calls to commit()
and / or rollback() is covered in the JDBC Transactions Tutorial.
Here is a simple example of calling the JDBC Connection rollback() method:
try{
connection.setAutoCommit(false);
// perform operations on the JDBC Connection
// which are to be part of the transaction
connection.commit();
} catch (SQLException e) {
connection.rollback();
}
Notice how rollback() is called within the catch-block of a try-catch block. In case an exception
is thrown while trying to carry out the operations in the transaction, the whole transaction is rolled back.
createStatement()
The JDBC Connection createStatement() creates a JDBC Statement
object. A Statement instance can be used to execute
SQL updates against the database, or to execute
SQL queries against the database. Here is an example of creating a JDBC Statement
instance via the JDBC Connection createStatement() method:
Statement statement = connection.createStatement();
prepareStatement()
The JDBC Connection prepareStatement() creates a JDBC PreparedStatement
object. A PreparedStatement instance can be used to execute
SQL updates against the database, or to execute
SQL queries against the database. Here is an example of creating a JDBC PreparedStatement
instance via the JDBC Connection prepareStatement() method:
String sql = "select * from people where id=?";
PreparedStatement preparedStatement =
connection.prepareStatement(sql);
getMetaData()
The JDBC Connection getMetaData() method returns a
JDBC DatabaseMetaData object which can be used to introspect the database
the JDBC Connection is connected to. What you can do with the DatabaseMetaData
is covered in the JDBC DatabaseMetaData Tutorial. Here is an example of
creating a JDBC DatabaseMetaData object via the JDBC Connection getMetaData()
method:
DatabaseMetaData databaseMetaData = connection.getMetaData();
| Tweet | |
Jakob Jenkov | |
