JDBC in Java

By,

Praveen

JDBC: JDBC is a Java API that can access any kind of tabular data, especially data stored in a Relational Database. It is working on all platforms.

JDBC stands for Java Data base Connectivity. It is a standard Java API. It establishes connectivity between the Java programming and  databases (ie mysql,mongoDB , sqllite).
It perform following tasks
1. Making a connection to a database.
2. Creating SQL statements.
3. Executing SQL  queries in the database.
4. Viewing & Modifying the resulting records.

JDBC is provides a complete set of interfaces. It allows for portable access to an underlying database.
Java is using to write different types of executables.
They are -
1. Java Applications
2. Java Applets
3. Java Servlets
4. Java Server Pages (JSPs)
5. Enterprise JavaBeans (EJBs).
All above mention executables are used with JDBC driver to access a database, and to store data.

JDBC Architecture
The JDBC API models support 1. Two-tier and 2. Three-tier processing models for database access. But in generally, JDBC Architecture consists of two layers −
1. JDBC API: This API provides the application-to-JDBC Manager connection.
2. JDBC Driver API: This API supports the JDBC Manager-to-Driver Connection.
1. The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases.
2. The JDBC driver manager ensures that the correct driver is used to access each data source. It is capable to support multiple concurrent drivers connected to multiple heterogeneous databases.

Following is the architectural diagram, which shows the location of the driver manager with respect to the JDBC drivers and the Java application −
JDBC Components
The JDBC API provides the following some interfaces and classes −
1. DriverManager : This class managing a list of database drivers. It matches the connection request from the java application with the proper database driver. To this, this is using communication sub protocol. The first driver that recognizes a certain sub protocol under JDBC will be used to establish a database Connection.
2. Driver               : This driver interface handles all the communications with the database server.
3. Connection       : This connection interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with database is through connection object only.
4. Statement          : By using objects created from this interface to submit the SQL statements to the database.
5. ResultSet          : These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data.
6. SQLException   : This class handles any errors that occur in a database application.
.
SQL Quesries
Create Database
To create a new data database we using CREATE DATABASE syntax. The syntax is
                CREATE DATABASE DATABASE_NAME;
                EX. CREATE DATABASE EMP;

Drop Database
To Delete a database.
The syntax is –
DROP DATABASE DATABASE_NAME
EX. DROP DATABASE emp;

Create Table
To create a table in database.
The syntax is –
                     CREATE TABLE table_name;
        Ex. CREATE TABLE student;
Drop Table
        To delete a table.
        DROP TABLE table_name;
        Ex. DROP TABLE student;

INSERT Data into Table
            To insert data into table
                                                Syntax -  INSERT INTO table_name VALUES (column1, column2, ...);
                                                Ex. INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');