July 25, 2022

JDBC Connection Interface

Using Java Connection interface you can create a session with a specific database. SQL statements are executed and results are returned within the context of a connection. Connection interface in JDBC API resides in java.sql package.

How to get Connection instance

You can create Connection object using DriverManager.getConnection() method.

Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@myhost:1521:emp", "dbuser", "dbpwd");

Once you have a Connection object you can-

  1. Get a Statement object.
  2. Using Connection object you can also get metadata about the DB it is connecting to like DB version, driver info, tables in DB and so on.
  3. Manage transaction by using commit() and rollback() methods of Connection object and also set transaction isolation level.

Methods in Connection interface

We’ll go through some of the commonly used methods of the Connection interface categorized by functionality.

For getting Statement instance

Connection has methods to get a Statement, PreparedStatement and a CallableStatement.

  • createStatement()- Creates a Statement object for sending SQL statements to the database.
  • prepareStatement(String sql)- Creates a PreparedStatement object for sending parameterized SQL statements to the database. Read more about PreparedStatement Interface in this post JDBC PreparedStatement Interface.
  • prepareCall(String sql)- Creates a CallableStatement object for calling database stored procedures. Read more about CallableStatement Interface in this post JDBC CallableStatement Interface.

There methods are overloaded to pass ResultSet type and concurrency level too. See the whole list of methods here- https://docs.oracle.com/en/java/javase/14/docs/api/java.sql/java/sql/Connection.html

For managing transaction

Connection interface in JDBC also provides methods to manage transaction. By default (in auto-commit mode) all the SQL statements will be executed and committed as individual transactions. If you want to group statements in a transaction and manage the transaction then there are following methods-

  • setAutoCommit(boolean autoCommit)- To set this connection's commit mode to true or false. true to enable auto-commit mode; false to disable it. By default, new connections are in auto-commit mode.
  • Commit()- To commit the transaction explicitly. Makes all changes made since the previous commit/rollback permanent. This method should be used only when auto-commit mode has been disabled.
  • rollback()- Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.
  • setTransactionIsolation(int level)- To change the transaction isolation level for this Connection object to the specified level.

Possible transaction isolation levels are defined as constants in the Connection interface.

  • TRANSACTION_NONE- A constant indicating that transactions are not supported.
  • TRANSACTION_READ_COMMITTED- A constant indicating that dirty reads are prevented; non-repeatable reads and phantom reads can occur.
  • TRANSACTION_READ_UNCOMMITTED- A constant indicating that dirty reads, non-repeatable reads and phantom reads can occur.
  • TRANSACTION_REPEATABLE_READ- A constant indicating that dirty reads and non-repeatable reads are prevented; phantom reads can occur.
  • TRANSACTION_SERIALIZABLE- A constant indicating that dirty reads, non-repeatable reads and phantom reads are prevented.

For getting DB Metadata

  • getMetaData()- Returns a DatabaseMetaData object that contains metadata about the connected database.

That's all for the topic JDBC Connection Interface. If something is missing or you have something to share about the topic please write a comment.

You may also like

No comments:

Post a Comment