Learn Full Advacnce Java Course
https://youtube.com/playlist?list=PLkx9f4H3tJMI4735IyPUp88bKCvatY2IY
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 JDBC Type 1 driver is also
known as the JDBC-ODBC Bridge. This driver
is an implementation of the lower-level JDBC Driver API that makes use of the
ODBC driver to connect to the
database. The driver converts JDBC method calls into ODBC (Open Database
Connectivity) calls and sends them to the ODBC
driver. This implies that the ODBC driver, as well as the client database code,
installed on the client machine.
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.
The JDBC API makes it easy to send
SQL statements to relational database systems and supports all dialects of SQL. But the JDBC API goes
beyond SQL,
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.
|
Learn Full Advacnce Java Course
https://youtube.com/playlist?list=PLkx9f4H3tJMI4735IyPUp88bKCvatY2IY
