Java Database Connectivity

Home	»	Java	»	Advance Java	»	Java Database Connectivity

Commercial applications generally use a 'Form' to capture data. This is the User Interface (UI) via which, user captures data using a keyboard. The UI for any commerical application can be created using Java's AWT or Swing packages. There are two Ways For Storing the data in the Computer First are the files and Second is the database . The File is that Which Contains Repetitive Type of data and File Always Contains data in a Sequence Order and with no Specific Layout So that We uses database.

The Database Stores the Data in the Form of Tables and Data which is Stored in the Tables is never be Repeated. There are Many Database Software's those are used for Making a Database for Storing data into the Form of Tables. Database uses the Concept of Primary Key in which Records are never to be Repeated and never to be NULL So that Database Provides various Functions For Storing the data and With the Help of Database Software We can also Manipulate the data o Database. There are Many Software's which Provides various Facilities for Creating database and also Provides various Operations for Manipulating the data of database.

SQL is the most Common database Software which is mostly used for Storing the data in the Form of Tables also Provides us DDL , DML, DCL Statements .

Content

• Overview of Java Database
• An Overview of JDBC
• JDBC/ODBC Bridge
• Driver Manager
• Java SQL Package
• Working With Databases

1. DDL Data Definition Language ,used for creating , Dropping , or Changing the database.
2. DML Data Manipulation Language Which is used for Inserting, Updating and deleting Records from the database.
3. DCL Data Control Language :- this is Used for Controlling the Permissions of database , Like Creating a Users , Giving Permissions to users etc.

JAVA provides us various Classes those are used to Connecting JAVA with a database so that if wants to Connect JAVA with a Database then we first Make a Table in the database So First of all Make a Table by using various SQL Commands in the SQL Server.

Overview of Java Database

Database is a collecton of large amount of related data in the form of tables. Tables are a collection of rows and columns. The data contained in a table is in form of fileds such as name, address, phone, email and remark, which are also called columns.

There are a number of Database Management Systems (DBMS) for creating and maintaining databases. You can also use DBMS for retrieving data in the form of reports. For example, you need to retrieve the names of employees that are permanent residents of Delhi from an EMPLOYEE database table. You can use a DBMS to generate the required report by querying the database tables.

You can also use Relational Database Management System (RDBMS) for maintaining databases. Relational databases use the concept of linked two-dimensional tables comprising of rows and columns. You can create a relationship between multiple tables such as one to one and one to many and present the output as another table.

An Overview of JDBC

JDBC is a set of Java Application Programming Interfaces (APIs) that enables you to access any tabular data source using the Java programming language. There are several characteristics of JDBC:

1. Provides Call level interface (CLI) that enables Java to interact with database.
2. Provides the DBMS driver that passes the SQL query to enable connectivity.
3. Provides a simple mechanism to access data from a RDBMS.

CLI enables you to use SQL statements with JDBC.

JDBC is a lower level interface and is used to execute SQL statements directly. Java uses the java.sql package to incorporate SQL statements in Java. JDBC also provides cross-DBMS connectivity to a wide range of databases. The various advantages of JDBC are:

1. Enables you to manipulate data on enterprise level.
2. Does not require any additional software as Java has built-in software for JDBC.
3. Dose not need any configuration on the client side for connectivity.

JDBC consists of Java interfaces that are based on Oracle Database Connectivity (ODBC). ODBC API is the most widely used API for accessing database. JDBC has certain advantages over ODBC.

The below Table describes the difference between ODBC and JDBC.

Point of Distinction	ODBC	JDBC
Access	Cannot access Java directly.	Part of Java.
Pointers	Uses pointer concepts.	Does not use pointers.
Installation	Needs manual installation at the client side.	Automatically installed with Java.
Interface	Uses the C interface	Uses CLI.

Difference between ODBC and JDBC

JDBC/ODBC Bridge

The JDBC architecture describes the interaction of JDBC API with Java application and Java applet. JDBC API consists of several Call level interfaces for interaction with JDBC Driver Manager and ODBC bridge. An ODBC bridge provides the layer for JDBC Driver Manger and JDBC/ODBC drivers for defining databases. JDBC API is responsible for transferring data between an application and a database. The below Figure shows the architecture of JDBC that represents the working of JDBC API, ODBC Bridge and JDBC Driver Manager for interaction with multiple databaes.

ODBC bridge provides platform for multiple databases interaction because ODBC is a Microsoft standard product for communication with databases. As a result, ODBC is compatible with Windows environment.

Driver Manager

The driver manager is a class that belongs to the java.sql package. It communicates with the 32 bit ODBC driver. Then this ODBC driver will communicates with the database and its tables. The driver manager loads the driver manager loads the driver manager for ODBC driver or any other for managing database connection. Once a driver has been registered on our computer then driver manager class provides us the interface between the program and the ODBC driver. It also gives a inbuilt methods for managing database driver for exp.

getConnection(JDBC:ODBC: Name of DSN,<user name>, <passwords>) -- This is used for establishing a connection to the specified database by using the JDBC:ODBC bridge and name of system DSN which knows where the database is actually located in computer if it is successful then this will represents as a physical.

Connection to the database After establishing the connection we can create an SQL query for retrieving the records from the tables. All the records will be stored in resultset object. A resulset object is created after running SQL statement. This object is created , the records we have to read its each row and then write it to the console it provides us.

rs.next() method for viewing the rows one by one. Here rs is the object of resutlset for seeing all the records of a database table.

Java SQL Package

This package contains the various types of methods and classes and interfaces for managing the connection for example.

1. Driver Manager Class This class is provided by the java.sql package. It controls the interface between the application and ODBC Driver. This class provides a method called.
1. getConnection(URL, user, password) – Here URL specifies the path of the database where it is located and this is used for establish a connection with this URL.
2. Connection Interface – The object represents a connection with a database. By using this we can create a SQL statement. It provides void close() method for closing the connection to a database. The various Methods those are Provided by the Connection interfaces are as below:-
1. changePassword :- Enable a user To Change his Password.
2. createStatement() :- Prepare an Connection Object To Execute a Statement.
3. commit :- For Saving all the Changes those are Made to Database.
4. close() :- For Closing the Current Connection.
5. preparseStatment():- Creates an Object of Prepared statement interface.
6. isReadOnly() :- used to check Whether a Connection is Open as Read-only.
7. getWarnings():- this will gives you the Warning Messages those are occurred during calling of Connection Object.
3. Statement - CreateStatement( ) –After creating the statement with the connection object it returns a new statement object , which is to be used for executing the sql statements. This will returns the results those are stored in results object. It is used for either executing insert, update and delete statement. The Various Methods those are Provided by the Statement interfaces are :-
1. cancel :-if a user wants to cancel the Executed Statement for some Reasons.
2. close() :- For Closing the Statement Object.
3. execute():- For Executing an SQL Statement through Connection Object.
4. executeUpdate():-Execute a SQL Query for Changing or Updating the Data of the database or when we wants to Execute Update Query to database.
5. setMaxRows():- Used To set the Maximum Number of Rows those will be Returned by the ResultSet Object.
6. getConnection :- Gives name of the Connection Object through which Statement is going to be Processed.
4. Prepared Statement interface – This interface extends the statement interface. This is used for executing multiple times of a SQL statement. So, it is precompiled and stored in a prepared statement object. Prepared statement always Accept Inputs from the user and then Execute the Query to the database . after Executing they gives us the Results . These are also Contains some Pre-compiled SQL Statements and after Executing or after Matching the Search Criteria which is Specified by the user Result will be Stored into the ResultSet Object. Some methods of this interfaces are as followings:-
1. executeQuery( ) – For executing SQL statements.
2. executeUpdate( ) – For executing update, delete or insert statements.
3. setDate :- Used To Set the date object.
4. setString :- Used to Set Name of Column.
2. ResultSet interface – The result of a SQL statement is stored in it and we can access the records of a database by creating the resultset object. The methods of resultset objects are:-
1. void close – This is used for releasing resultset database resource and JDBC resources.
2. void getstring( ) – This method gives us the value of current row as a java string.
3. next( ) – This method is used for seeing the next records and it makes the using row as the current row and then second etc.
4. first() :- To Move the Cursor to the First rows of the database.
5. close():- To Close the Result Set.
6. afterLast() :- To Move the Cursor to the Last Value in the ResultSet.
7. beforeFirst:- To Move the Cursor to First value of the ResultSet Object.
8. isLast():- To Check Whether this is a last row of not.
9. isFirst() :- To Check Whether this is a first row of not.
10. getInt() :- Gives Value of Currect Column as a type of Int and we can also use getFloat, getString(),getDouble() etc.
11. InsertRow :- For Inserting a new Row in the database.
12. isAfterLast():- Gives True of False if a Cursor is after the Last Row of Resultset Object.
13. last() :-To Move the Cursor to the Last Record of ResultSet Interface.
14. rowInserted() :- Gives True of False , to Check Whether a Row has Successfully inserted or not.
15. rowUpdated() :- Gives True of False , to Check Whether a Row has Successfully Updated or not.
16. rowDeleted() :- Gives True of False , to Check Whether a Row has Successfully Deleted or not.
3. ResultSetMetaData Interface – The Meta Data is a Special type of Object which provided data about Data or We can ay that this Object is used for Retrieving the information about the data which is Stored in the Database Like , How Many Columns a Table Contains , what's the Name of Table , what's the Size of the Table etc. All this information can be Retrieved with the Help of MetaData Interface. First Create an Object of those Interface. This object is returned by the getMetaData( ) Constructor and it is used for accessing the information about a database for ex:
The ResultSetMetaData interface Provides you the information Like:-

1. getColumnCount( ) – It will returns the number of columns.
2. getColumnName (int) – It returns the name of the column.
3. getTableName( ) – It will return the name of a table.
4. getColumnType() :- Gives you the Type of Column.

There is also a DatabaseMetaData interface which Contains various Methods for Retriving the information about the Database like :-

1. getConnection:-Gives Name of Connection Object.
2. getUserName() :- Gives Name of User
3. getURL :- Gives URL of database.
4. getMaxRowSize() :- Gives Maximum Rows that a Table can Contains.
5. getDriverName():- Gives you the Name of the Driver,


4. SQL Exception Class – This class extends the java.lang. Exception class. It provides us the information about the database access error. Its methods provides are:-
1. int getErrorCode – Returns the code of exception occusence.
2. String getSQLState( ) – It returns the SQL state.
3. SQL Exception getNext Exception( ) – It is used to obtain the next exception by changing to the current exception.
5. Arrays :- Provides various Methods of Arrays Like getArray() , getBaseTypeName() etc.
6. SQLData:- This will Map the user defined data type with the user defined data type and in this a user can use the various Methods of SQL Like readSQL() and writeSQL() etc.
7. Date Class :- this Class Contains Various Convertion Methods for Converting the Data into the Date Objects With the Help of this Class we can find the Number of days and the Various Methods like getyear() , getMonth(), setDate(), getDate() etc.
8. Time Class :- As Same Like Date Class this Class Provides us various Methods those are deal with Time Like getTime(),setTime(), getHours(), setMinutes(),toString(), getSecond() etc.

Working With Databases

You can create new database objects and manipulate the data stored in the existing objects. You can access and manipulate the information stored in a database using the classes and interfaces available in the java.sql package. Manipulation of data includes inserting new records, updating the existing records and deleting the obsolete records. JDBC accesses databases using Data Source Name (DSN), therefore, you need to create a DSN you start working with database. Various steps to access and manipulate database information in JDBC are:

1	Create the Database.
2	Creating a DSN.
3	Connecting to a Database.
4	Creating & Executing Statements.
5	Closing a database connection.

JDBC - Programming:

1	View records from the Access table.
2	Inserts records into the Access table.
3	Updates records into the Access table.
4	Deletes records from the Access table.
5	Drops the Access table.

JDBC - Working With User Interfaces - GUI:

1	Inserts records into the Access table - GUI.
2	View records from the Access table - GUI.
3	Updates records into the Access table - GUI.
4	Deletes records from the Access table - GUI.