JAVA DATABASE CONNECTIVITY
JDBC
Overview
Components
Architecture
Types
of Drivers
RDBMS
concepts
Common
SQL Statements
Steps
of JDBC programs
JDBC
•
Java
Database Connectivity
in short called as JDBC.
•
It is an application programming interface
that defines how a java programmer can access the database in tabular
format from Java code using a set of standard interfaces and classes
written in the Java programming language.
USES OF JDBC
•
JDBC
helps the programmers to write java applications that manage these three
programming activities:
1. It helps us to connect to a data source, like a database.
2. It helps us in sending queries and updating statements to the database and
3. Retrieving and processing the results received from the database in terms of answering to your query
1. It helps us to connect to a data source, like a database.
2. It helps us in sending queries and updating statements to the database and
3. Retrieving and processing the results received from the database in terms of answering to your query
Components of JDBC
•
JDBC
has four Components:
1. The JDBC API.
2. The JDBC Driver Manager.
3. The JDBC Driver.
2. The JDBC Driver Manager.
3. The JDBC Driver.
1. The JDBC API
•
provides
the facility for accessing the relational database from the Java
Applications
•
The
latest version of JDBC 4.0 application programming interface is divided
into two packages
i) java.sql
ii) javax.sql
i) java.sql
ii) javax.sql
•
Java
SE and Java EE platforms are included in both the packages.
•
JDBC
API provides access to databases by defining classes and interfaces that
represent objects such as:
•
Driver
manager
•
Database
drivers
•
Database
connections
•
SQL
statements
•
Result
Set
•
Database
metadata
•
Prepared
statements
•
Callable
statements
2. The JDBC Driver
Manager
•
The
JDBC Driver Manager is a very important class that defines objects which
connect Java applications to a JDBC driver.
•
The
main responsibility of JDBC database driver is to load all the drivers
found in the system properly as well as to select the most appropriate
driver from opening a connection to a database.
3. The JDBC Driver
•
It
provides access to Database by converting non-database queries to database
queries.
•
There
are 4 types of drivers
1. JDBC-ODBC Bridge Driver
2. Native API Driver
3. JDBC Net protocol Driver
4. Native protocol Driver
Understanding JDBC
Architecture
•
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
Understanding JDBC
Drivers
•
A JDBC
driver translates standard JDBC calls into a network or database
protocol or into a database library API call that facilitates communication
with the database. This translation layer provides JDBC applications with
database independence.
•
If the back-end database changes, only the
JDBC driver need be replaced with few code modifications required.
1. JDBC-ODBC Bridge
Driver
•
Type 1
drivers act as a "bridge" between JDBC and another
database connectivity mechanism such as ODBC
•
In
this driver the java statements are converted to a jdbc statements. JDBC
statements calls the ODBC by using the JDBC-ODBC Bridge.
And finally the query is executed by the database. This driver has serious
limitation for many applications.
2. Native API Driver
•
Type 2
drivers use the Java Native Interface (JNI) to make calls to a local
database library API. This driver converts the JDBC calls into a database
specific call for databases such as SQL, ORACLE etc. This driver communicates
directly with the database server. It requires some native code to connect to
the database. Type 2 drivers are usually faster than Type 1 drivers.
3.JDBC Net protocol
Driver
•
Type 3
drivers are pure Java drivers that use a proprietary network protocol to
communicate with JDBC middleware on the server. The middleware then translates
the network protocol to database-specific function calls. Type 3 drivers are
the most flexible JDBC solution because they do not require native database
libraries on the client and can connect to many different databases on the back
end. Type 3 drivers can be deployed over the Internet without client installation
4.Native protocol
Driver
•
Type 4
drivers are pure Java drivers that implement a proprietary database protocol
(like Oracle's SQL*Net) to communicate directly with the database
•
because
Type drivers communicate directly with the database engine rather than through
middleware or a native library, they are usually the fastest JDBC Drivers
available. This driver directly converts the java statements to SQL statements
Relational Database
Concepts
•
A
database is an organized collection of information. A simple example of a
database are like your telephone directory, recipe book etc.
•
A
Relational model is the basis for any relational database management system
(RDBMS).
•
A
relational model has mainly three components:
1) A collection of objects or relations.
2) Operators that act on the objects or relations.
3) Data integrity methods.
1) A collection of objects or relations.
2) Operators that act on the objects or relations.
3) Data integrity methods.
•
To
design a database we need three things:
1) Table
2) Rows
3) Columns
2) Rows
3) Columns
•
A table
is one of the most important ingredient to design the database. It is also
known as a relation, is a two dimensional structure used to hold related
information. A database consists of one or more tables.
•
A
table contains rows : Rows is a collection of instance of one thing,
such as the information of one employee.
•
A
table contains columns: Columns contains all the information of a single
type. Each column in a table is a category of information referred to as a
field.
•
One
item of data, such as single phone number of a person is called as a Data
Value.
Common SQL statements
•
The
commonly used SQL statements are:
1) Select
2) Insert
3) Update
4) Delete
2) Insert
3) Update
4) Delete
1.SQL Select statement
•
The
SELECT statement is used to select from a table.
•
Select
column_names FROM table_name;
•
The
result from a SQL query is stored in a resultset. The SELECT statement has
mainly three clauses.
1) Select: specifies the
table columns that are retrieved.
2) From: tells from where
the tables has been accessed
3) Where: specifies which
tables are used. The Where
clause is optional, if not used then all the
table rows will be selected.
2.SQL INSERT Statement
•
To
Insert a single or multiple records into the database. We can specify the name
of the column in which we want to insert the data.
•
insert
into tablename values (value1, value2..);
•
The
Insert statement has mainly three clauses.
1) Insert: It
specifies which table column has to
be inserted in the table.
2) Into: It tells in which the data will be stored.
3) Values: In this we insert the values we have to insert.
2) Into: It tells in which the data will be stored.
3) Values: In this we insert the values we have to insert.
3.SQL UPDATE Statement
•
The
Update statement is used to modify the data in the table. Whenever we want to
update or delete a row then we use the Update statement.
•
UPDATE
table_name Set colunm_name = new_value WHERE
column_name = some_name;
•
The
Update statement has mainly three clauses.
1)
UPDATE: It specifies which table column has to be updated.
2) Set: It sets the column in which the data has to be updated.
3) Where: It tells which tables are used.
2) Set: It sets the column in which the data has to be updated.
3) Where: It tells which tables are used.
4.SQL DELETE Statement
•
This
delete statement is used to delete rows in a table.
•
DELETE
FROM table_name WHERE column_name
= some_name;
•
The
Delete statement has following clauses.
1)
Delete: It specifies which table column has to be deleted.
2) From: It tells from where the Table has been accessed.
3) Where: It tells which tables are used.
2) From: It tells from where the Table has been accessed.
3) Where: It tells which tables are used.
Steps for using JDBC
- Import the sql package
- Load the driver
- Connect to Database
- Create a Statement
- Execute a Statement
- Retrieve & Process Results
- Close the Connection, Statement
1.Import the package
•
Classes
and interfaces present in the sql package need to be imported
import java.sql.*;
2.Load the JDBC driver
•
Class.forName()
is used to load JDBC driver by taking name of the driver as argument.
Class.forName(String driver_name);
•
The
Driver name for Oracle is:
Class.forName("oracle.jdbc.OracleDriver");
•
For
SQL Server:
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver");
•
For
Point Base:
Class.forName(“com.pointbase.jdbc.jdbcUniversalDriver");
3.Connect to the
Database
•
DriverManager.getConnection()
connects the application component to the Database, by taking three arguments:
–
url à String containing driver name and
database to be accessed
–
userID
–
password
Connection
con=
DriverManager.getConnection(String url,
Sring uid, String pwd)
•
Connection
String for Oracle Driver:
con=DriverManager.getConnection(
"jdbc:oracle:thin:@10.0.0.2:1521:hcl", "scott","tiger");
•
Connection
String for SQL Driver:
con=DriverManager.getConnection(
"jdbc:odbc:hcl",“hcl",“hcl
");
•
Connection
String for PointBase Driver:
con=DriverManager.getConnection(
“jdbc:pointbase:server://localhost/sample“,
”pbpublic”,”pbpublic”);
4.Create a statement
•
con.createStatement()
is used to create a statement. It is then used to execute a query
Statement stmt = con.createStatement();
5.Execute a Statement
•
stmt.executeQuery()
takes the SQL query as argument and executes the query.
•
Execution
of some queries retrieves a result set
ResultSet rs = stmt.executeQuery(String query)
6.Process the result
•
Result
of SQL statements are stored in a ResultSet object.
•
Mainly
we use two methods to retrieve & process the result set:
–
next()
è to move to next row
–
getXXX(int
colindex) è
to retrive & process the data(XXX stands for specific data type)
7.Close the statement
& connection
•
close()
is used to terminate the connection
–
rs.close()è closes resultset
–
con.close()
è closes connections
•
Closing
the connection will automatically close the result set.
1.PROG FOR GETTING CONNECTIONS
import
java.sql.*;
public
class DB {
private static final String
DRIVER_CLASS_NAME="oracle.jdbc.OracleDriver ";
private static final String DATABASE_URL =
"jdbc:oracle:thin:@10.0.0.2:1521:hcl";
private static final String USERNAME
=“scott”;
private static final String PASSWORD =
“tiger";
public static Connection getConnection()
throws SQLException {
try {
Class.forName(DRIVER_CLASS_NAME);
} catch(ClassNotFoundException e) {
System.out.println(e);
System.exit(1);
}
Connection connection =
DriverManager.getConnection(DATABASE_URL,
USERNAME,PASSWORD);
return(connection);
2.TO CREATE A TABLE THROUGH JAVA APPLICATIONS
import
java.sql.*;
public
class MakeAnimalTable {
Statement stmt = null;
Connection conn = null;
public static void main(String arg[]) {
MakeAnimalTable mat = new
MakeAnimalTable();
mat.createTable();}
public void createTable() {
try {
conn = DB.getConnection();
stmt = conn.createStatement();
int status =
stmt.executeUpdate("create table animals2 " +
"(name varchar(16) primary
key, legs int)");
System.out.println("Status
from creating table: " + status);
stmt.close();
conn.close();
} catch(SQLException e) {
System.out.println(e);
} }}
3.DEMONSTRATION TO INSERT VALUES IN TO A DATABASE
TABLES
import
java.sql.*;
public
class MakeAnimal {
Statement stmt = null;
Connection conn = null;
public static void main(String arg[]) {
MakeAnimal mat = new MakeAnimal();
mat.add("Lynx",4);
mat.add("Duck",2);
mat.add("Moose",4);
mat.add("Halibut",1);
}
public void add(String name,int legs) {
try {
conn = DB.getConnection();
stmt = conn.createStatement();
int status =
stmt.executeUpdate("insert into animals2 " +
"values('" + name +
"', " + legs + ")");
System.out.println("Status
from adding " + name + ": " + status);
stmt.close();
conn.close();
} catch(SQLException e) {
System.out.println(e);
} }}
4.TO DEMO THE RETRIEVAL & DROPPING OF TABLE
import
java.sql.*;
public
class DumpAnimalTable {
Statement stmt = null;
Connection conn = null;
public static void main(String arg[]) {
DumpAnimalTable dat = new
DumpAnimalTable();
dat.open();
dat.showTable();
dat.dropTable();
dat.close();
}
public
void open() {
try {
conn = DB.getConnection();
stmt = conn.createStatement();
} catch(SQLException e) {
System.out.println(e);
}
}
public
void showTable() {
try {
ResultSet set =
stmt.executeQuery("select * from animals2");
while(set.next()) {
String name = set.getString(1);
int legs = set.getInt(2);
System.out.println(name + legs);
}
} catch(SQLException e) {
System.out.println(e);
}
}
public
void dropTable() {
try {
int status =
stmt.executeUpdate("drop table animals2");
System.out.println("Status
from dropping table: " + status);
} catch(SQLException e) {
System.out.println(e);
}
}
public void close() {
try {
stmt.close();
conn.close();
} catch(SQLException e) {
System.out.println(e);
}
} }
0 comments:
Post a Comment