I want to understand the overview of Java Database Connectivity.Then I study about JDBC because it is very important to create java applications.So I use internet and day by day I improve my knowledge using above information.
What is JDBC?
JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent
connectivity between the Java programming language and a wide range of
databases.
The JDBC library
includes APIs for each of the tasks mentioned below that are commonly
associated with database usage.
·
Making a connection to a database.
·
Creating SQL or MySQL statements.
·
Executing SQL or MySQL queries in the database.
·
Viewing & Modifying the resulting records.
Fundamentally, JDBC
is a specification that provides a complete set of interfaces that allows for
portable access to an underlying database. Java can be used to write different
types of executables, such as −
·
Java Applications
·
Java Applets
·
Java Servlets
·
Java ServerPages (JSPs)
·
Enterprise JavaBeans (EJBs).
All of these
different executables are able to use a JDBC driver to access a database, and
take advantage of the stored data.
JDBC provides the
same capabilities as ODBC, allowing Java programs to contain
database-independent code.
Pre-Requisite
Before moving
further, you need to have a good understanding of the following two subjects −
·
Core JAVA Programming
·
SQL or MySQL Database
JDBC Architecture
The JDBC API supports
both two-tier and three-tier processing models for database access but in
general, JDBC Architecture consists of two layers −
·
JDBC API: This provides the
application-to-JDBC Manager connection.
·
JDBC Driver API: This supports the
JDBC Manager-to-Driver Connection.
The JDBC API uses a
driver manager and database-specific drivers to provide transparent
connectivity to heterogeneous databases.
The JDBC driver
manager ensures that the correct driver is used to access each data source. The
driver manager is capable of supporting 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 −
Common JDBC Components
The JDBC API provides
the following interfaces and classes −
·
DriverManager: This class manages a
list of database drivers. Matches connection requests from the java application
with the proper database driver using communication sub protocol. The first
driver that recognizes a certain subprotocol under JDBC will be used to establish
a database Connection.
·
Driver: This interface
handles the communications with the database server. You will interact directly
with Driver objects very rarely. Instead, you use DriverManager objects, which
manages objects of this type. It also abstracts the details associated with
working with Driver objects.
·
Connection: This 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.
·
Statement: You use objects
created from this interface to submit the SQL statements to the database. Some
derived interfaces accept parameters in addition to executing stored
procedures.
·
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.
·
SQLException: This class handles
any errors that occur in a database application.
The JDBC 4.0 Packages
The java.sql and
javax.sql are the primary packages for JDBC 4.0. This is the latest JDBC
version at the time of writing this tutorial. It offers the main classes for
interacting with your data sources.
The new features in
these packages include changes in the following areas −
·
Automatic database driver loading.
·
Exception handling improvements.
·
Enhanced BLOB/CLOB functionality.
·
Connection and statement interface enhancements.
·
National character set support.
JDBC - SQL Syntax
·
Structured Query Language (SQL) is a standardized language that allows you to perform
operations on a database, such as creating entries, reading content, updating
content, and deleting entries.
·
SQL is supported by almost any database you will likely use, and it
allows you to write database code independently of the underlying database.
·
This chapter gives an overview of SQL, which is a prerequisite to
understand JDBC concepts. After going through this chapter, you will be able to
Create, Create, Read, Update, andDelete (often referred to as CRUD operations) data from a database.
Create Database
·
The CREATE DATABASE statement is used for creating a new database. The
syntax is −
·
SQL> CREATE DATABASE DATABASE_NAME;
·
The following SQL statement creates a Database named EMP −
·
SQL> CREATE DATABASE EMP;
·
Drop Database
·
The DROP DATABASE statement is used for deleting an existing database.
The syntax is −
·
SQL> DROP DATABASE DATABASE_NAME;
·
Note: To create or drop a
database you should have administrator privilege on your database server. Be
careful, deleting a database would loss all the data stored in the database.
·
Create Table
·
The CREATE TABLE statement is used for creating a new table. The syntax
is −
·
SQL> CREATE TABLE table_name
·
(
·
column_name column_data_type,
·
column_name column_data_type,
·
column_name column_data_type
·
...
·
);
Example
·
The following SQL statement creates a table named Employees with four
columns −
·
SQL> CREATE TABLE Employees
·
(
·
id INT NOT NULL,
·
age INT NOT NULL,
·
first VARCHAR(255),
·
last VARCHAR(255),
·
PRIMARY KEY ( id )
·
);
·
Drop Table
·
The DROP TABLE statement is used for deleting an existing table. The
syntax is −
·
SQL> DROP TABLE table_name;
Example
·
The following SQL statement deletes a table named Employees −
·
SQL> DROP TABLE Employees;
·
INSERT Data
·
The syntax for INSERT, looks similar to the following, where column1,
column2, and so on represents the new data to appear in the respective columns
−
·
SQL> INSERT INTO table_name VALUES (column1, column2, ...);
Example
·
The following SQL INSERT statement inserts a new row in the Employees
database created earlier −
·
SQL> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
·
SELECT Data
·
The SELECT statement is used to retrieve data from a database. The
syntax for SELECT is −
·
SQL> SELECT column_name, column_name, ...
·
FROM table_name
·
WHERE conditions;
·
The WHERE clause can use the comparison operators such as =, !=, <,
>, <=,and >=, as well as the BETWEEN and LIKE operators.
Example
·
The following SQL statement selects the age, first and last columns from
the Employees table, where id column is 100 −
·
SQL> SELECT first, last, age
·
FROM Employees
·
WHERE id = 100;
·
The following SQL statement selects the age, first and last columns from
the Employees table where first column contains Zara −
·
SQL> SELECT first, last, age
·
FROM Employees
·
WHERE first LIKE '%Zara%';
·
UPDATE Data
·
The UPDATE statement is used to update data. The syntax for UPDATE is −
·
SQL> UPDATE table_name
·
SET column_name = value, column_name = value, ...
·
WHERE conditions;
·
The WHERE clause can use the comparison operators such as =, !=, <,
>, <=,and >=, as well as the BETWEEN and LIKE operators.
Example
·
The following SQL UPDATE statement changes the age column of the
employee whose id is 100 −
·
SQL> UPDATE Employees SET age=20 WHERE id=100;
·
DELETE Data
·
The DELETE statement is used to delete data from tables. The syntax for
DELETE is −
·
SQL> DELETE FROM table_name WHERE conditions;
·
The WHERE clause can use the comparison operators such as =, !=, <,
>, <=,and >=, as well as the BETWEEN and LIKE operators.
Example
·
The following SQL DELETE statement deletes the record of the employee
whose id is 100 −
·
SQL> DELETE FROM Employees WHERE id=100;
No comments:
Post a Comment