Sunday 10 July 2022

JDBC - Java Database Connectivity

 


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














No comments:

Post a Comment