JAVA DATABASE CONNECTIVITY
Introduction of JDBC
JDBC is Java application programming interface that allows the Java programmers to access database management system from Java code.
JDBC stands for “Java Database Connectivity”. It is a java API which enables the java programs to execute SQL statements. It is an application programming interface that defines how a java programmer can access the database from Java code using a set of standard interfaces and classes written in the Java programming language. The database that we want to connect must follow the ANSI SQL-2 standard.
JDBC Architecture
JDBC is a software abstraction sitting between your application and the database you want to access.
The application layer provides GUIs, business logic etc. and is written in Java.
JDBC layer provides API calls to access different types of databases which the application layer can use.
Database Drivers
JavaSoft develops the JDBC API. JavaSoft has divided JDBC driver into four categories, based on their construction and the type of database they are intended to support. To connect with individual databases, JDBC (the Java Database Connectivity API) requires drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.
These four categories are explained below.
§ Type 1: JDBC-ODBC Bridge Driver
§ Type 2: Native-API Driver
§ Type 3: Network Protocol Driver
§ Type 4: Native Protocol Driver
JDBC-ODBC Bridge Driver
The Type 1 Driver makes use of ODBC which in turn depends on native libraries of the underlying operating system which makes it platform-dependent. This makes any application that uses this driver non-portable as the ODBC driver for the specific operating system is required in the client machine. The database must also support the ODBC drivers for the Type 1 Driver to make a connection.
The bridge is usually used when there is no pure-Java driver available for a particular database.
Advantages
The following are the advantages of the Type 1 Driver:
§ Connectivity to most databases is possible as most databases contain ODBC drivers.
§ Installation of the Type 1 driver is very easy.
Disadvantages
The following are the disadvantages of the Type 1 Driver:
§ The Type 1 Driver is partially written in Java which makes it not portable.
§ The Type 1 Driver is not suitable for Internet Applications (Web Applications)
§ The Type 1 Driver contains a performance overhead as JDBC calls is converted into ODBC calls and then passed on to the ODBC driver.
Native-API Driver
The JDBC Type 2 driver is also known as the Native-API Driver. This driver is an implementation of the lower-level JDBC Driver API that makes use of the client-side libraries of the database to connect. The driver converts JDBC calls into database-specific calls on the client API for databases such as SQL Server, Informix, DB2, Oracle, or Sybase. The Type 2 driver communicates directly with the database server; therefore this style of driver requires that some binary code be loaded on each client machine.
Advantages
The following are the advantages of the Type 2 Driver:
§ The Type 2 Driver is better than the JDBC-ODBC bridge driver, since no ODBC drivers are required.
Disadvantages
The following are the disadvantages of the Type 2 Driver:
§ The client API libraries of the specific database vendor must be installed on the client machine.
§ Not all the database vendors provide client API libraries.
§ The Type 2 Driver is not suitable for Internet Applications (Web Applications)
Network Protocol Driver
The JDBC Type 3 driver is also known as the Pure Java Driver for database Middleware. The JDBC Type 3 driver follows a three-tiered approach where the client code sends the JDBC calls through the network to a middle-tier server. The middle-tier converts the the JDBC calls directly or indirectly into database specific protocol, which will forward the JDBC calls to the database. The conversion logic that translates the JDBC calls resides in the middle-tier, and not in the client side like that of the JDBC Type 4 driver.
Advantages
The following are the advantages of the Type 3 Driver:
§ The translation of the JDBC calls resides on the middle-tier and therefore no database library is required on the client side.
§ On the client side only the JDBC Type 3 driver is required, and any database can be supported, as long as the middle-tier supports the database.
§ The middle-tier which implements the JDBC Type 3 driver can provide additional services like caching, auditing ...
Disadvantages
The following are the disadvantages of the Type 3 Driver:
§ The additional layer might be a bottleneck for the JDBC calls.
Native Protocol Driver
The JDBC Type 4 driver is also known as the Direct to Database Pure Java Driver. The JDBC Type 4 driver converts the JDBC calls directly into database specific calls which mean the client can directly communicate with the database management system (DBMS). The Type 4 drivers are completely written in Java and are therefore platform independent. The Type 4 driver is installed in client side and runs within the Java Virtual Machine.
Advantages
The following are the advantages of the Type 4 Driver:
§ The Type 4 driver is platform independent and therefore easy to deploy to different client environments.
§ The performance of Type 4 drivers is higher as the driver does not need to convert the JDBC calls into ODBC or to Middleware Type drivers.
Disadvantages
The following are the disadvantages of the Type 4 Driver:
§ A vendor-specific driver is required for each of the databases the client whishes to connect.
JDBC APIs for database Connectivity (Java.sql Package)
The JDBC 3.0 API includes the entire API in the java.sql package and the javax.sql package. The JDBC API is a Java API for accessing virtually any kind of tabular data. The JDBC API consists of a set of classes and interfaces written in the Java programming language that provide a standard API for tool/database developers and makes it possible to write industrial-strength database applications entirely in the Java programming language.
also making it possible to interact with other kinds of data sources, such as files containing tabular data.
In simplest terms, a JDBC technology-based driver ("JDBC driver") makes it possible to do three things:
1. Establish
a connection with a data source
2.
Send queries and update statements
to the data source
3.
Process the results
In order to connect any application or an applet to a database there are various classes and interfaces are available in the java.sql package. Depending on the requirements these classes and interfaces can be used.
Some of the classes and interfaces which are used to perform various database tasks.
java.sql.Connection
This interface abstracts most of the interaction with the database. In order to send SQL statements to the database and to read the results a connection is used. It is also used to connect with the database.
java.sql.Driver
This is an interface that abstracts the vendor specific connection protocol. Implementations of this interface can
be found from database vendors as well as third party database driver vendors.
java.sql.DriverManager
This class provides the functionality necessary for managing one or more database drivers. Each driver in turn lets you to connect to a specific database.
java.sql.CallableStatement
This interface can be used to execute stored procedures.
java.sql.PreparedStatement
This is a variant of the java.sql.Statement interface allowing for parameterized SQL statements.
java.sql.ResultSet
This interface abstracts results of executing SQL SELECT statements. This interface provides methods to access the result row-by-row.
java.sql.statement
This interface lets you to execute SQL statements over the underlying connection and access the results.
Connection Interface
This interface is available in the java.sql package. The connection interface is used to establish a connection to the database you want to access. The Connection interface defines following methods:
§ void close()
This method frees the Connection object’s database and other JDBC resources.
§ void commit()
This method makes all the changes made since the last commit or rollback permanent. It throws SQLException.
§ Statement createStatement()
This method creates a Statement object for sending SQL statements to the database. It throws SQLException.
§ Boolean isClosed()
This methods returns true if the connection is close else returns false.
§ CallableStatement prepareCall(String s)
This method creates a CallableStatement object for calling stored procedures. It throws SQLException.
§ PreparedStatement prepareStatement(String s)
This method creates a PreparedStatement object for sending SQL statements with or without IN parameter. It throws SQLException.
§ void rollback()
This method undoes all changes made to the database.
Statement Interface
The Statement object is created for executing static SQL statements. To execute SQL Statements, Statement is the simple and easy.
The Statement interface has several methods which can be used to execute SQL statement.
§ void close()
This method releases the statement object’s database and JDBC resources.
§ boolean execute(String s)
This method executes the SQL statement specified by s. The getResultSet() method is used to retrieve the result.
§ ResultSet executeQuery(String s)
This method executed the SQL statement specified by s and returns the ResultSet object.
§ int executeUpdate(String s)
This method executes the SQL statement specified by s. These statements may be INSERT,UPDATE or DELETE.
§ int getMaxRows()
This method returns the maximum number of rows that are generated by the executeQuery() method.
§ ResultSet getResultSet()
This method retrives the ResultSet generated by the execute() method.
Prepared statement
The Prepared Statement object can be used to execute a dynamic SQL statement with IN parameter. A Prepared Statement can be precompiled and used repeatedly. This interface has methods to handle PreparedStatement objects. The PreparedStatement object is created using PreparedStatement() method in Connection class.
§ Boolean execute()
This method executes the SQL statement in this object. The getResult() method is used to retrieve the result.
§ ResultSet executeQuery()
This method is used to execute SQL statements in this object. It returns the ResultSet object.
§ Int executeUpdate()
This method executes the SQL statement in this object. The SQL statement must be an SQL insert, update and delete statement.
§ ResultSetMetaData getMetaData()
This method retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this object is executed.
§ IN Parameter:
§ An IN parameter is used to execute a dynamic SQL statement.
§ In some certain conditions where in we need to pass different values to an SQL statement, then such values can be specified by using “ ? “ in the SQL statement.
§ The actual values can be passed using the setXXX() method at the time of execution.
§ The “ ? “ in a Java SQL statement stands for an input parameter to be supplied after using setXXX method().
§ E.g.
PreparedStatement st = cnmain.prepareStatement(“Select * from emp where empno=?”); St.setInt(1,1002);
ResultSet rs= st.executeQuery();
S
The general form for setting the IN parameter is as : setXXX(integer, value)
Where xxx is the value type which may be int, float, Boolean etc... Integer is the position of IN parameter in the SQL statement. Value is the value that is to be supplied in place of ?.
Callable statement
§ A callable statement object is used to execute stored procedures defined in the RDBMS.
§ A procedure with OUT parameter can be executed only in this Callable Statement.
§ A callable statement can also contain IN parameter.
§ An OUT parameter has to be registered prior to executing the stored procedure.
§ An OUT parameter in the stored procedure is represented by (?).
§ An OUT parameter is registered using the registerOutParameter() method. This method declares what type of the OUT parameter is.
§ After the CallableStatement is executed, the OUT parameter are to be obtained using the getXXX() method.
§ The general form of registering OUT parameters other than NUMERIC and DECIMAL VALUES is as under :
registerOutParameter(int index, Type type)
where indx is the relative position of the OUT parameter in the SQL. Type is the SQL data type of OUT parameter.
§ E.g.
CallableStatement cstmt = con.prepareCall(EXECUTE ADD_REC(?,?,?));
There are three parameters. Let us assume that first two are IN parameters and the last one is the OUT parameter. The IN and OUT parameters are defined as under :
st.setInt(1,1001); st.setString(2,”Kamal”);
st.registerOutParameter(3,Types.LONGVARCHAR);
The CallableStatement is executed by calling the execute methods.
ResultSet rs = st.executeQuery();
The registered OUT parameter can be retrieved using getXXX() method in the CallableStatement interface.
§ BigDecimal getBigDecimal(int index)
This method retrieves the OUT parameters of JDBC NUMERIC type at the specified index.
§ byte getByte(int index)
This method retrieves the OUT parameter of JDBC NUMERIC type at the specified index location.
§ date getDate(int index)
This method retrieves the OUT parameter of JDBC DATE type at the specified index column.
§ double getDouble(int index)
This method retrieves the OUT parameter of JDBC DOUBLE type at the specified index column.
§ float getFloat(int index)
This method retrieves the OUT parameter of JDBC FLOAT type at the specified index column.
Result set
The executeQuery() and getResultSet() when called on Statement, PreparedStatement, and CallableStatement it returns the object of type ResultSet. The ResultSet object contains results after the execution of SQL statement. The ResultSet object maintains a cursor pointing to the current row of results. The next() method moves cursor to the next row of the result set. The ResultSet interface has many methods to get the results from the result set.
§ boolean absolute(int row)
This method moves the cursor to the specified row numer in the result set.
§ void afterLast()
This method moves the crsor to the end of the result set just after the last row.
§ void close()
This method releases the object’s database.
§ void deleteRow()
This method deletes the current row of this result set.
§ boolean first()
This method moves the cursor to the first row of the result set.
§ BigDecimal getBigDecimal(int index)
This method retrieves the value of the specified column as BigDecimal.
§ boolean getBoolean(int index)
This method retrieves the value of the specified column as Boolean.
§ boolean getBoolean(String clName)
This method retrieves the name of the specified column as Boolean.
§ byte getByte(int index)
This method retrieves the value of the specified column as byte.
§ date getDate(int index)
This method retrieves the value of the specified column as date.
§ float getFloat(int index)
This method retrieves the value of the specified column as float.
§ ResultSetMetaData getMetaData()
This method returns properties of the ResultSet object.
§ int getRow()
This method returns the current row number.
§ Statement getStatement()
This method returns the object which produced the ResultSet.
§ String getString(int index)
This method returns the object which produced the ResultSet.
§ boolean isFirst()
This method checks whether the cursor is in first row.
§ boolean isLast()
This method checks whether the cursor is in last row.
§ boolean next()
This method checks whether the cursor is in next row.
§ boolean previous()
This method checks whether the cursor is in previous row.
Steps of JDBC URL for MS-Access database
1)
Go to Control Panel.
2) Click on
Administrative Tools.
3) Click on
Data Sources (ODBC).
4) Select
MS-Access database and then click on Add.
5) Select
Microsoft Access Driver (*.mdb).
6) Click
over the FINISH.
7) It will
display ODBC Microsoft Access Setup Dialog box.
8) In this dialog box, type the name of the data source.
9) Then
click on Select in the Database
frame.
10) It will
display "Select Database" dialog box.
11) Select the database and click on the ok. ODBC data source is created.
__________________________________________________________
JDBC steps for ODBC Database Connection
Here are the basic steps to connect Java program to MySQL using JDBC APIs
1. Importing required classes
Import java.sql package.
2. Loading the Driver
In the second step of jdbc connection process, we load the driver by calling Class.forName() method. A client can connect to Database server by JDBC driver
The call is made as given below: Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
1. Creating JDBC Connection to database
§ In order to create a connection, the loading of JDBC database driver does not connect to the database.
§ Before executing any SQL statement it is mandatory to establish a connection with a database.
§ To do this the getConnection() method of the DriverManager class is to be invoked which is used to find a specific driver that can create a connection to the URL requested.
§ Here, the DriverManager class searches for the registered drivers which can process the database.
If a driver is not found then an exception is thrown.
§ The call to the getConnection() method is as given below.
Connection con = Drivermanager.getConnection(“jdbc:odbc:dbname”,“ ”, “ ” );
Here, the getConnection() method takes three arguments, the first is the string type argument that is the URL for the database. The second argument is username and the third argument is the password.
2. Creating statement object
§ In order to interact with the database, the SQL statements must be executed.
§ This requires that a Statement object needs to be created to manage the SQL statements.
§ To do this the createStatement() method of the Connection class is to be invoked.
§ Statement st = con.createStatement();
§ The above statement creates a Statement object using the database connection.
§ The Statement class provides methods for executing SQL statements and retrieving the results from the statements execution.
3. Executing statement object
§ The SQL statement can be executed by invoking the executeQuery() method.
String query =”select * from emp”; ResultSet rs = st.executeQuery(query);
§ The above statement sends the query to the database and returns the results of the query as a ResultSet.
§ If there are any errors during the execution of the query then an exception is thrown.
4. Manipulating ResultSet
§ The last step is to iterate the ResultSet.
§ A ResultSet is a collection of results retrieved from a query.
§ There are various methods available in the ResultSet class to iterate through these results. One of the method is next() which places the pointer to the next record. This method returns a Boolean value. If this value is true then it indicates that there is data to fetch.
§ A while loop can be used to fetch rows from a ResultSet.
§ rs.next() is used in the while loop for the iterating.
MS-Access Example
import java.sql.*; class select
{
public static void main(String args[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:student"); Statement st = con.createStatement();
String query = "select * from tblStudent"; ResultSet rs = st.executeQuery(query); while(rs.next())
{
//youtube.com/avadhtutor
int id = rs.getInt("id");
int rollno = rs.getInt("rollno"); String name = rs.getString("name");
String course = rs.getString("course");
System.out.println(id +" "+rollno +" "+name +" "+course);
}
}
catch(Exception e)
{
System.out.println(e);
}
}
}
JDBC steps for other Database Connection
1.
Load the JDBC driver
To load a driver, you specify the class name of the database driver in the Class.forName method. By doing so, you automatically create a driver instance and register it with the JDBC driver manager.
try
{
Class.forName ("connect.microsoft.MicrosoftDriver"); Class.forName ("oracle.jdbc.driver.OracleDriver"); Class.forName ("com.sybase.jdbc.SybDriver");
Class.forName ("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException cnfe)
{
System.err.println ("Error loading driver: " + cnfe);
}
2. Define the connection URL
In JDBC, a connection URL specifies the server host, port, and database name with which to establish a connection.
String host = "localhost"; String dbName = "someName"; int port = 3306;
§ String oracleURL = "jdbc:oracle:thin:@" + host +":" + port + ":" + dbName;
§ String sybaseURL = "jdbc:sybase:Tds:" + host +":" + port + ":" + "?SERVICENAME=" + dbName;
§ String msAccessURL = "jdbc:odbc:" + dbName;
§ String mySqlURL = "jdbc:mysql://"+host+":"+port+"/"+dbName+"";
3. Establish the connection
With the connection URL, username, and password, a network connection to the database can be established. Once the connection is established, database queries can be performed until the connection is closed.
String username = "root"; String password = "admin";
Connection connection =DriverManager.getConnection(mySqlURL, username, password);
4. Create a Statement object
Creating a Statement object enables you to send queries and commands to the database. Statement statement = connection.createStatement();
5. Execute a query or update
Given a Statement object, you can send SQL statements to the database by using the execute, executeQuery, executeUpdate.
String query = "SELECT col1, col2, col3 FROM sometable"; ResultSet resultSet = statement.executeQuery(query);
§ executeQuery
§ Executes an SQL query and returns the data in a ResultSet. The ResultSet may be empty, but never null.
§ executeUpdate.
Used for UPDATE, INSERT, or DELETE commands. Returns the number of rows affected, which could be zero. Also provides support for Data Definition Language (DDL) commands, for example, CREATE TABLE, DROP TABLE, and ALTER TABLE.
6. Process the results
When a database query is executed, a ResultSet is returned. The ResultSet represents a set of rows and columns that you can process by calls to next and various getXxx methods. while(resultSet.next())
{
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + " " + resultSet.getString("firstname") + " " resultSet.getString("lastname"));
}
We suggest that when you access the columns of a ResultSet, you use the column name instead of the column index. That way, if the column structure of the table changes, the code interacting with the ResultSet will be less likely to fail.
7. Close the connection
When you are finished performing queries and processing results, you should close the connection, releasing resources to the database.
connection.close ();
Meta Data
The term "meta" here means information about your data that does not interest the end users at all, but which you need to know in order to handle the data. JDBC provides two meta-data classes: java.sql.ResultSetMetaData and java.sql.DatabaseMetaData. The meta-data described by these classes was included in the original JDBC ResultSet and Connection classes.
Result Set Meta Data
As its name implies, the ResultSetMetaData class provides extra information about ResultSet objects returned from a database query. In the embedded queries you made earlier in the book, you hardcoded into your queries much of the information a ResultSetMetaData object gives you. This class provides you with answers to the following questions:
§ How many columns are in the result set?
§ Are column names case-sensitive?
§ Can you search on a given column?
§ Is NULL a valid value for a given column?
§ How many characters is the maximum display size for a given column?
§ What label should be used in a display header for the column?
§ What is the name of a given column?
§ What table did a given column come from?
§ What is the datatype of a given column?
Methods of Resultset Metadata:
§ getColumnCount()
Returns the number of columns in this ResultSet object.
§ getColumnName(int column)
Get the designated column’s name.
§ getColumnType(in column)
Retrieves the designated column’s SQL type.
§ getSchemaName(int column)
Get the designated column’s table’s schema.
§ getTableName(int column)
Get the designated column’s table name.
§ isAutoIncrement(int column)
Indicateds whether the designated column is automatically numbered, thus read-only.
§ isCaseSensitive(int column)
Indicates whether a column’s case matters.
§ isCurrency(int column)
Indicates whether the designated column is a cash value.
§ isNullable(int column)
Indicated the nullability of values in the designated column.
§ isReadonly(int column)
Indicates whether the designated column is definitely not writable.
Database Meta Data
As the ResultSetMetaData class relates to the ResultSet class, the DatabaseMetaData class relates to the Connection class (in spite of the naming inconsistency). The DatabaseMetaData class provides methods that tell you about the database for a given Connection object, including:
§ What tables exist in the database visible to the user?
§ What username is being used by this connection?
§ Is this database connection read-only?
§ What keywords are used by the database that are not SQL2?
§ Does the database support column aliasing?
§ Are multiple result sets from a single execute () call supported?
§ Are outer joins supported?
§ What are the primary keys for a table?
Methods name:
§ getDatabaseProductName( )
Returns the product name. i.e. the name of the database program.
§ getDatabaseProdouctVersion( )
Returns the version of the database program.
§ getDriverName( )
Returns the type of the JDBC driver.
§ getUserName( )
Returns the current user name.
§ getCatalogs( )
Returns the name of the database file or database.
JDBC Data Types
|
JDBC/Java |
SQL |
JDBC/Java |
|
java.lang.String |
DOUBLE |
double |
|
java.lang.String |
VARBINARY |
byte[ ] |
|
java.lang.String |
BINARY |
byte[ ] |
|
Boolean |
DATE |
java.sql.Date |
|
java.math.BigDecimal |
TIME |
java.sql.Time |
|
Byte |
TIMESTAMP |
java.sql.Timestamp |
|
Short |
CLOB |
java.sql.Clob |
|
Int |
BLOB |
java.sql.Blob |
|
Long |
ARRAY |
java.sql.Array |
Database Exception Handling
|
getErrorCode(
) |
Gets the error number
associated with the exception. |
|
getMessage(
) |
Gets the JDBC driver's error message for an error,
handled by the driver or gets the Oracle error number and message for a
database error. |
|
getSQLState(
) |
Gets the XOPEN SQLstate string. For a JDBC driver error,
no useful information is returned from this method. For a database error, the
five-digit XOPEN SQLstate code is returned. This method can return null. |
|
getNextException(
) |
Gets the next Exception object
in the exception chain. |
