JDBC

JDBC stands for Java Database Connectivity. Java provides JDBC API as a part of it's JSE implementation. The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database(Oracle, MySQL, SQL Server etc.).

JDBC helps to perform below operation with data sources:

1. Establishing connection with data source like database

2. Executing queries to the database

3. Retrieving and processing result received from database as a result of query execution in step 2

JDBC Architecture

Basic code snippet to perform above mentioned steps

public void connectToAndQueryDatabase(String username, String password) {

Connection con = DriverManager.getConnection(

"jdbc:myDriver:myDatabase", username, password);

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");

while (rs.next()) {

int x = rs.getInt("a");

String s = rs.getString("b");

float f = rs.getFloat("c");

}

}

Type-1 Driver: JDBCD-ODBC Bridge Driver

• Type-1 Driver acts as bridge between JDBC and other database connectivity mechanism like ODBC.

• Type-1 Driver example is Sun JDBC-ODBC bridge driver(in sun.jdbc.odbc package)

• This driver converts JDBC calls into ODBC calls and redirects the request to ODBC driver

• ODBC driver uses SP(Stored Procedure) APIs to make DBMS specific call

Type-2 Driver: Java to Native API(Partly Java Driver)

• Type-2 Driver converts JDBC calls into database vendor specific native call using JNI

• These database specific calls then dispatched to db specific native libraries

• Native libraries sends request to db server by native protocol

• Example: Weblogic Type-2 Driver by BEA Weblogic

Type-3 Driver: Java to Network Protocol(Pure Java Driver)

• Type-3 Driver converts JDBC calls into middleware server specific calls

• Driver communicates with middleware over a socket

• Middleware converts these calls into database specific calls

• These type of drivers also known as net-protocol drivers

• Example: IDS Driver, WebLogic RMI Driver

Type-4 Driver: Java to Database Protocol(Pure Java Driver)

• Type-4 implements the database protocol to interact directly with a database

• This driver prepares a DBMS specific network message and then communicates with DB server over a socket

• Type-4 Driver uses DB specific proprietary protocol for communication

• Generally these type of drivers are implemented by DBMS vendors, since the protocols used are proprietary

• Example: Oracle Thin driver, WebLogic & Mysqlserver4 for MY SQL server from BEA system

In order to access database and perform operations using JDBC API, you can follow below steps:

1. Establishing a connection

2. Create a statement

3. Execute the query

4. Process the result

5. Close the connections

Establishing a connection

DriverManager class lets Java Application to connect to a data source. JDBC drivers need to be added before establishing the data base connection. If you are using JDBC 4.0 or later, drivers found within the classpath, will be automatically loaded by DriverManager class.

Syntax:

Connection conn = DriverManager.getConnection(url, user, password);

DriverManager class provides three ways to connect to the database.

1. getConnection(String url) - connects to database pointing to url

2. getConnection(String url, Properties info) - connects to database pointing to url by using passed properties

3. getConnection(String url, String user, String password) - connects to database pointing to url, user and password

Note:

• All above three methods returns reference to Connection type object

• DriverManager.getConnection() throws SQLException

Below is the code snippet of how to establish connection to the database:

public Connection getConnection() throws SQLException {

Connection conn = null;

String url = "jdbcURL";

String user = "username";

String password = "password";

conn = DriverManager.getConnection(url, user, password);

System.out.println("Connected to database");

return conn;

}

URL

• A database connection URL is a string that your DBMS JDBC driver uses to connect to a database.

• It can contain information such as where to search for the database, the name of the database to connect to, and configuration properties.

• The exact syntax of a database connection URL is specified by your DBMS. Below are the some examples of URLs

  • MySQL: jdbc:mysql://localhost:3306/

  • Java DB: jdbc:derby:testdb;create=true

  • H2: jdbc:h2:~/test

Create a statement

A Statement is an interface which represents SQL statement. You need connection object to create a Statement object. This Statement object executes and returns ResultSet object, which is a table of data representing a database result set.

Syntax:

Statement stmt = con.createStatement();

There are mainly three type of statements:

1. Statement: Used to implement simple SQL statements with no parameters.

2. PreparedStatement

   • Extends Statement

   • Used for precompiling SQL statements that might contain input parameters

3. CallableStatement

   • Extends PreparedStatement

   • Used to execute stored procedures that may contain both input and output parameters

Execute the query

To execute a query, an execute method can be called using Statement object.

Syntax:

ResultSet rs = stmt.executeQuery(query);

There are mainly three methods that helps to execute SQL queries.

1. execute:

   • Returns true if the first object that the query returns is a ResultSet object.

   • Use this method if the query could return one or more ResultSet objects.

   • Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet.

2. executeQuery: Returns one ResultSet object.

3. executeUpdate:

   • Returns an integer representing the number of rows affected by the SQL statement.

   • Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.

Process the result

ResultSet object obtained after executing SQL query contains results returns from the database. You can iterate over result set using cursor pointer. Initially this cursor points before first row, ResultSet provides various methods to move the cursor eg: ResultSet.next() method.

Below is the code snippet to retrieve and process the result from database:

try {

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT * FROM LEARN_JDBC");

while (rs.next()) {

System.out.print(rs.getInt(1));

System.out.print(" ");

System.out.println(rs.getString(2));

}

//...

Note:

• This cursor is not database cursor

Close the connections

When you are done with using ResultSet, Statement and/or Connection, call respective close method to immediately release the resources it is using.

Syntax:

} finally {

if (stmt != null) { stmt.close(); }

}

and

} finally {

if (conn!= null) { conn.close(); }

}

Note:

• When you call Statement.close method, its ResultSet objects are closed

• ResultSer.close, Statement.close and Connection.close throws SQLException

• While calling close methods you need to either add throws SQLException clause to your method or just surround this code snippet with try and catch like given below:

Catch the SQLException while closing the resources:

finally {

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

DriverManager class

• DriverManager class acts as basic service for managing a set of JDBC drivers

• DriverManager.getConnection methods returns the Connection object to the supplied database

• DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers

• Driver must be present in the classpath so that it can be loaded using Class.forName(driverClassName)

• Since JDBC 4.0, Applications no longer need to explicitly load JDBC drivers using Class.forName()

• Existing programs which currently load JDBC drivers using Class.forName() will continue to work without modification

Commonly used methods of DriverManager class - Modifier and TypeMethod and Description

static void registerDriver(Driver driver)

Registers the given driver with the DriverManager.

static void deregisterDriver(Driver driver)

Removes the specified driver from the DriverManager's list of registered drivers.

static Connection getConnection(String url)

Attempts to establish a connection to the given database URL.

static Connection getConnection(String url, Properties info)

Attempts to establish a connection to the given database URL.

static Connection getConnection(String url, String user, String password)

Attempts to establish a connection to the given database URL.

static Driver getDriver(String url)

Attempts to locate a driver that understands the given URL.

static Enumeration<Driver> getDrivers()

Retrieves an Enumeration with all of the currently loaded JDBC drivers to which the current caller has access.

Connection interface

• A Connection object represents a connection with specific database and acts as session for database operations

Connection con = DriverManager.getConnection(..);

• All SQL statement execution and results are returned within the context of connection

• The Connection interface is a factory of Statement, PreparedStatement and DatabaseMetaData i.e. object of Connection can be used to get the object of Statement, PreparedStatement and DatabaseMetaData.

Stetement stmt = con.createStatement();

PreparedStatement stmt = con.prepareStatement(sqlQuery);

DatabaseMetaData dbmd = con.getMetaData();

Where con represents Connection object.

• The Connection interface provide many methods for transaction management like commit(), rollback() etc

Modifier and TypeMethod and Description - -Modifier and TypeMethod and Description

Statement createStatement()

Creates a Statement object for sending SQL statements to the database.

Statement createStatement(int resultSetType, int resultSetConcurrency)

Creates a Statement object that will generate ResultSet objects with the given type and concurrency.

Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)

Creates a Statement object that will generate ResultSet objects with the given type, concurrency, and holdability.

PreparedStatement prepareStatement(String sql)

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

CallableStatement prepareCall(String sql)

Creates a CallableStatement object for calling database stored procedures.

void setAutoCommit(boolean autoCommit)

Sets this connection's auto-commit mode to the given state.

void commit()

Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.

void rollback()

Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.

void close()

Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.

Statement Interface

• Represents object that is used for executing a static SQL statement and returning the results it produces

• Connection.createStement() creates a Statement object for sending SQL statements to the database

• SQL Statements without parameters are normally executed using Statement Objects

• In case if same SQL statement is executed many times, it may be more efficient to use PreparedStatement object

• Statement.execute, Statement.executeUpdate and Statement.executeQuery methods used to execute static SQL statement

• ResultSet object is returned as result which static SQL statement produces while execution

• By default, only one ResultSet object per Statement object can be open at the same time

• All execution methods in the Statement interface implicitly close a current ResultSet object of the statement if an open one exists

• Reading one ResultSet object long with another, each must have been generate by different Statement objects

Commonly used methods of Statement interface - Modifier and TypeMethod and Description

boolean execute(String sql)

Executes the given SQL statement, which may return multiple results.

ResultSet executeQuery(String sql)

Executes the given SQL statement, which returns a single ResultSet object.

int executeUpdate(String sql)

Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

int[] executeBatch()

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

ResultSet Interface

• Represents table of data, usually generated by executing a statement that queries database

• A ResultSet object maintains a cursor to its current row of data

• Initially cursor points before the first row

• ResultSet.next method moves the cursor to next row of data

• ResultSet.next method returns false if there is no more rows in ResultSet object

• ResultSet cursor moves forward only

• To iterate through ResultSet object while loop can be used

• A default ResultSet object is not upgradable – what does it mean?

• It is possible to produce ResultSet objects that are scrollable and/or updatable(insensitive to updates by others)

Statement stmt = con.createStatement(

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");

// rs will be scrollable, will not show changes made by others,

// and will be updatable

• ResultSet interface provides getter methods(like getString, getInt, and so on) to retrieve column value from the current row

• Values can be retrieved using either the index number of the column or the name of the column

• In general using column number will be more efficient, it starts from 1 not 0

• For maximum portability

  • ResultSet columns within each row should be read in left-to-right order

  • Each column should be read only once

• For the getter methods,

  • A JDBC driver attempts to convert the underlying data to the Java type specified in the getter method and returns a suitable Java value.

  • The JDBC specification has a table showing the allowable mappings from SQL types to Java types that can be used by the ResultSet getter methods

• Column names used as input to getter methods are case insensitive

• When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned

• The column name option is designed to be used when column names are used in the SQL query that generated the result set

• The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause

• ResultSet update methods may be used in 2 ways

  • To update a column value in the current row. In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the NAME column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.

rs.absolute(5); // moves the cursor to the fifth row of rs

rs.updateString("NAME", "AINSWORTH"); // updates the

// NAME column of row 5 to be AINSWORTH

rs.updateRow(); // updates the row in the data source

• To insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table using the method insertRow.

rs.moveToInsertRow(); // moves cursor to the insert row

rs.updateString(1, "AINSWORTH"); // updates the

// first column of the insert row to be AINSWORTH

rs.updateInt(2,35); // updates the second column to be 35

rs.updateBoolean(3, true); // updates the third column to true

rs.insertRow();

rs.moveToCurrentRow();

• A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results

• The number, types and properties of a ResultSet object's columns are provided by the ResultSetMetaData object returned by the ResultSet.getMetaData method

Commonly used methods of ResultSet interface - Modifier and TypeMethod and Description

int getInt(int columnIndex)

Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

int getInt(String columnLabel)

Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language.

String getString(int columnIndex)

Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.

String getString(String columnLabel)

Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language.

boolean next()

Moves the cursor forward one row from its current position.

boolean previous(

Moves the cursor to the previous row in this ResultSet object.

boolean last()

Moves the cursor to the last row in this ResultSet object.

PreparedStatement Interface

• An object that represents precompiled SQL statement

• This object can then be used to efficiently execute this statement multiple times

• The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter.

• For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used

Commonly used methods of PreparedStatement interface - Modifier and Type Method and Description

boolean execute()

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

default long executeLargeUpdate()

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

ResultSet executeQuery()

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

int executeUpdate()

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

void setInt(int parameterIndex, int x)

Sets the designated parameter to the given Java int value.

void setString(int parameterIndex, String x)

Sets the designated parameter to the given Java String value.

CallableStatement Interface

• The interface used to execute SQL stored procedures.

• The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs.

• This escape syntax has one form that includes a result parameter

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

• If used, the result parameter must be registered as an OUT parameter.

• The other for that does not include a result parameter

{call <procedure-name>[(<arg1>,<arg2>, ...)]}

• The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.

• IN parameter values are set using the set methods inherited from PreparedStatement.

• The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided in CallableStatement.

• A CallableStatement can return one ResultSet object or multiple ResultSet objects.

• Multiple ResultSet objects are handled using operations inherited from Statement.

• For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters

Commonly used methods of CallableStatement interface - Modifier and Type Method and Description

int getInt(int parameterIndex)

Retrieves the value of the designated JDBC INTEGER parameter as an int in the Java programming language.

int getInt(String parameterName)

Retrieves the value of a JDBC INTEGER parameter as an int in the Java programming language.

String getString(int parameterIndex)

Retrieves the value of the designated JDBC CHAR, VARCHAR, or LONGVARCHAR parameter as a String in the Java programming language.

String getString(String parameterName)

Retrieves the value of a JDBC CHAR, VARCHAR, or LONGVARCHAR parameter as a String in the Java programming language.

DatabaseMetaData Interface

• Provides comprehensive information about the database

• This interface is implemented by drivers vendors

• This helps user to know the capabilities of DBMS in combination with the driver based on JDBC technology that is used with it

• Different RDBMSs often support different features, implement features in different ways, and use different data types.

• In addition, a driver may implement a feature on top of what the DBMS offers

• Information returned by methods in this interface applies to the capabilities of a particular driver and a particular DBMS working together

• Why and when this information is needed

  • A user for this interface is commonly a tool that needs to discover how to deal with the underlying DBMS.

  • This is especially true for applications that are intended to be used with more than one DBMS.

  • For example, a tool might use the method getTypeInfo to find out what data types can be used in a CREATE TABLE statement.

  • Or a user might call the method supportsCorrelatedSubqueries to see if it is possible to use a correlated subquery

  • Or supportsBatchUpdates to see if it is possible to use batch updates

• Some DatabaseMetaData methods return lists of information in the form of ResultSet objects.

• Regular ResultSet methods, such as getString and getInt, can be used to retrieve the data from these ResultSet objects.

• If a given form of metadata is not available, an empty ResultSet will be returned.

• Additional columns beyond the columns defined to be returned by the ResultSet object for a given method can be defined by the JDBC driver vendor and must be accessed by their column label.

• Some DatabaseMetaData methods take arguments that are String patterns.

• These arguments all have names such as fooPattern.

• Within a pattern String, "%" means match any substring of 0 or more characters, and "_" means match any one character.

• Only metadata entries matching the search pattern are returned. If a search pattern argument is set to null, that argument's criterion will be dropped from the search.

Commonly used methods of DatabaseMetaData interface - Modifier and Type Method and Description

String getDatabaseProductName()

Retrieves the name of this database product.

String getDatabaseProductVersion()

Retrieves the version number of this database product.

String getDriverName()

Retrieves the name of this JDBC driver.

String getDriverVersion()

Retrieves the version number of this JDBC driver as a String.

String getURL()

Retrieves the URL for this DBMS.

String getUserName()

Retrieves the user name as known to this database.

See complete program on DatabaseMetaData here

ResulSetMetaData Interface

• An object that can be used to get information about the types and properties of the columns in a ResultSet object.

• The following code fragment

  • creates the ResultSet object rs,

  • creates the ResultSetMetaData object rsmd, and

  • uses rsmd to find out how many columns rs has and whether the first column in rs can be used in a WHERE clause.

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");

ResultSetMetaData rsmd = rs.getMetaData();

int numberOfColumns = rsmd.getColumnCount();

boolean b = rsmd.isSearchable(1);

Commonly used methods of ResultSetMetaData interface - Modifier and Type Method and Description

String getSchemaName(int column)

Get the designated column's table's schema.

String getTableName(int column)

Gets the designated column's table name.

String getColumnName(int column)

Get the designated column's name.

String getColumnTypeName(int column)

Retrieves the designated column's database-specific type name.

int getColumnCount()

Returns the number of columns in this ResultSet object.

See complete program on ResulSetMetaData here

Why

There can be requirement where multiple times DML i.e. insert, update or delete operations are needed to be performed with database. In such case hitting database again and again to execute queries might cause database performance bottle neck.

What

To overcome such problems JDBC API supports batch processing that allows us to perform related operation all together in one shot. Means similar type of database operations can be clubbed/grouped to form a batch and that batch can be submitted to database as single command. This helps improving database performance. Batch processing can be carried out on DMLs(insert, update, delete) and/or database queries that returns nothing like DDL commands.

How

Batch processing in Statement

Batch processing in Statement is two step process

1. Add related DMLs in a batch, to achieve this addBatch(String sql) can be invoked on Statement object

2. Execute the batch, to achieve this executeBatch() can be invoked on Statement object

Modifier and Type Method and Description

void addBatch(String sql)

Adds the given SQL command to the current list of commands for this Statement object.

void clearBatch()

Empties this Statement object's current list of SQL commands.

int[] executeBatch()

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

default long[] executeLargeBatch()

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Batch processing in PreparedStatement

Batch processing in PreparedStatement is two step process

1. Add set of params to a batch, to achieve this addBatch() can be invoked on PreparedStatementobject

2. Execute the batch, to achieve this executeBatch() can be invoked on Statement type object

Modifier and Type Method and Description

void addBatch()

Adds a set of parameters to this PreparedStatement object's batch of commands.

Binary Large Objects

Store Images

• Images are stored in database as BLOB(i.e. binary large object)

• Below is the table to create table to save photo with name

CREATE TABLE MYPROFILE

(

name VARCHAR(40),

photo BLOB

);

• An image can be saved in database using Java FileInputStream

• PreparedStatement.setBinaryStream accepts InputStream as bytes and submits the same to database

• Below code snippet displayes how to do it

fin = new FileInputStream("qualifiedPath//photo.jpg");

pstmt.setBinaryStream(index, fin, fin.available());

int i = pstmt.executeUpdate();

• setBinaryStream method

  • Sets the designated parameter to the given input stream, which will have the specified number of bytes.

  • When a very large binary value is input to a LONGVARBINARY parameter, it may be more practical to send it via a java.io.InputStream object.

  • The data will be read from the stream as needed until end-of-file is reached

Retrieve Images

• ResultSet.getBlob method returns value of the designated column in the current row as a Blob object in the Java programming language

• Same can be converted in byte array and written as output using FileOutputStream.write

• See below code snippet

byte barr[] = b.getBytes(1, (int) b.length());

FileOutputStream fout = new FileOutputStream("fileOutputPath//output.jpg");

fout.write(barr);

Store files

• files are stored in database as CLOB(i.e. character large object)

• Below is the table to create table to save photo with name

CREATE TABLE MYFILE

(

name VARCHAR(40),

file CLOB

);

• An file can be saved in database using Java CharacterStream

• PreparedStatement.setCharacterStream accepts Reader (i.e. FileReader) object and it's length and submits the same to database.

• Below code snippet displayes how to do it

File f = new File("resources//files//jdbc.txt");

FileReader fr = new FileReader(f);

ps.setCharacterStream(columnIndex, fr, (int) f.length());

Note:

• Sets the designated parameter to the given Reader object, which is the given number of characters long. When a very large UNICODE value is input to a LONGVARCHAR parameter, it may be more practical to send it via a java.io.Reader object. The data will be read from the stream as needed until end-of-file is reached. The JDBC driver will do any necessary conversion from UNICODE to the database char format.

• This stream object can either be a standard Java stream object or your own subclass that implements the standard interface.

Retrieve files

• ResultSet.getClob method returns value of the designated column in the current row as a Clob object in the Java programming language

• Same can be converted in Redear object and written as output using FileWriter.write

• See below code snippet

Clob c = rs.getClob(columnIndex);

Reader r = c.getCharacterStream();

FileWriter fw = new FileWriter("resources//files//output.txt");

int j = 0;

while ((j = r.read()) != -1)

fw.write((char) j);

fw.close();

con.close();