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.).
Establishing connection with data source like database
Executing queries to the database
Retrieving and processing result received from database as a result of query execution in step 2
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 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
Single driver implementation for different databases
Vendor independent driver
Supports all databases supported by the ODBC driver
Type-1 driver should not be used in production environment and with auto-installation applications likes Applets.
Slow execution speed due to large number of calls and translations
Fully dependent on ODBC driver
ODBC client library should be installed at client machine
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
Fast processing as compared to Type-1 Driver
Contains exclusive feature provided by DB vendor and supported by JDBC specification
Native librariesto be installed on client machine
Executes DB specific native functions on JVM, aby bug in Type-2 driver might crash the JVM
Increase the cost of application in case it runs on different platforms
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
Additional features like Pool Management, performance improvement and connection availability
It is auto downloadable so best suits for Applets
No native library needed to be installed in client machine
Database independent, middleware takes care of converting calls to DB specific calls
Database details are not required at client side because it is available at middleware server
Easy to switch among databases, without changing client side driver classes
Performes task slowly because network call is involved
Cost of middleware server is more
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
This is lightweight driver
Auto downloadable
No native library needed to be installed at client side
No middleware server required
This uses database specific proprietary protocol so it is vendor dependent
In order to access database and perform operations using JDBC API, you can follow below steps:
Establishing a connection
Create a statement
Execute the query
Process the result
Close the connections
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.
getConnection(String url) - connects to database pointing to url
getConnection(String url, Properties info) - connects to database pointing to url by using passed properties
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;
}
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
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:
Statement: Used to implement simple SQL statements with no parameters.
PreparedStatement
Extends Statement
Used for precompiling SQL statements that might contain input parameters
CallableStatement
Extends PreparedStatement
Used to execute stored procedures that may contain both input and output parameters
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.
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.
executeQuery: Returns one ResultSet object.
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.
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
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 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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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.
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
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);
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
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.
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.
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 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.
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
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);
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.
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();