JDBC API
·
this is a part of the JDBC Specification that
provides an abstraction (interface) to program java applications accessing the
JDBC service
·
we know that the JDBC Service is implemented
into the JDBC Driver
·
the JDBC API is described into two parts:
1.
JDBC CORE API
2.
JDBC Extension API
JDBC CORE API: THIS part of the API supports accessing the
extended services such as:
·
Connection pool
·
Accessing distributed transaction
Understanding
JDBC API
·
WE want to use JDBC API in our java application
to access the tabular datastore such as DB Server.
Fig: JDBC API.JPG
Why should I use Database server?
·
Because to manage the persistence data, why
persist the data because to persist our business data, to do this we need CRUD
sql operations.
·
To connect to the database server, we should
interact with jdbc drivers, to do CRUD operations we should follow SQL
(STRUCTURED QUERY LANGUAGE) because database server only understand one
language called sql .
·
We understand the java application would mainly
want to interact with database server for CRUD (create, read, update and
delete) operations.
·
We also know that the SQL is used to describe
the CRUD operations to database server, that means the Core (central part) of
using the JDBC API is to submit the SQL statement to the JDBC Driver;
requesting it to execute with DATABASE SERVER and carry the result back.
Note:
·
I have developed one java application, I want to
store the data into the database server, to store or to interact with database
server I have to use only one language that is SQL statement; but database
server is located somewhere else, so I need one vehicle( bus i.e jdbc driver)
to carry my sql statements, so we are using jdbc driver to carry our data with sql
statements, so travel over the different networks we should follow the network
API CALL interface. Next I have submitted my SQL ( data) to JDBC drivers, jdbc driver safely
takes my (sql) data to the database server.
·
Here JDBC driver acts as a courier boy.
The Basic steps of working with JDBC API
·
We know that the java application wants to work
with JDBC to access the tabular datastore
·
We also know that the application wants to
access the tabular datastore managing the persistence data.
·
Thus the basic requirement in this process will
be to perform the CRUD operations
The following are the basic steps involved to implement for
accessing the datastore performing CRUD operations:
Steps:
1.
Obtain connection to DB
2.
Get a JDBC Statement
3.
Execute the statement
4.
Close the connection
Fig: JDBC API 1.JPG
·
Here our intention is to execute the statement
ie. Step 3: but to perform step 3 we should have step 2, to do the step 2 we
should have make the establish the connection to database server
Step1: obtaining the connection to DB:
Fig: JDBC STEP1.JPG
·
STEP1 : is just like a connecting to the mail
box, first we have to use login, password to connect to our Mail inbox
·
We know that any database server demands to
establish a session/connectivity before accessing any of its services.
·
Thus even working with JDBC , we start with
establishing the DB Session.
·
Creating a Database session is costlier job it
includes:
1.
Authentication
2.
Allocating the resources for the session.
Note: the DB Session
can not be shared between the clients/threads.
Q: How establish/obtain connection to DB with respect to
java/JDBC?
Ans:
FIG: JDBC API STEP2.JPG
·
We have multiple approaches to do this
·
Lets start with the basic (primary) approach i.e
using driver object direction.
APPROACH 1:
·
Obtaining the connection to db using Driver
object directly: in this approach we use the connect() method of the Driver
object for getting the connection to the server.
Q: What is Driver
object?
Ans: it is a part of JDBC Driver responsible for
establishing a session to the database server.
Q: What is
java.sql.Driver?
Ans:
It is an interface part of JBC API designed to represent the
Driver object. (to the java application)
The following method of Driver object is used to establish
the session to the database server:
Connection connect(String
jdbcUrl, Properties props) throws SQLException
{
-----;
}
·
This method is declared in java.sql.Driver
interface, implemented by the Driver object. Thus connect() is Non-static
method.
·
That means to invoke the connect() method we
need the following three (3) pre-requisit:
(1.) Driver
Object
(2.) jdbcUrl
(3.) Properties
·
Thus we can implement this step in following two
steps:
Step 1: create a Driver object instance
Step 2: invoke the connect() method.
Step 1.1: creating a
Driver class instance
·
We know that the Driver class (i.e:
implementation of java.sql.Driver interface)is provided by the 3rd
party vendor.
·
Thus the Driver class Name varies from one JDBC
Driver to other:
Example:
1.
For
sun JDBC-ODBC Bridge Driver
sun.jdbc.odbc.JdbcOdbcDriver
2.
For
Oracle OCI & thin Driver
oracle.jdbc.driver.OracleDriver
(or)
oracle.jdbc.driver.OracleDri ver
3.
For
MySQL Connector driver
com.mysql.jdbc.Driver
Pre-requisite: (I.E we must know the following before
learning the step1)
Q: How many ways we
can instantiate a java object (with respect to JRE)?
Ans:
We have only 4 ways to do this:
1.
new keyword
2.
newInstance() method of java.lang.Class object
3.
clone()
4.
Deserialization
(1)
And (2) are used for initial state , defined
into the class constructor.
(1.)
new
keyword:
·
Use new keyword when you know the class name
while developing time .
(2)
newInstance():
·
Supports instantiating the object whose class
name is known dynamically at run time.
(3.) clone():
·
To create a new object with a copy of state of
existing object
(4.) deserialization:
·
To create a object with the state from
serialized bytes.
From this pre-requisite discussion we understand the Driver
class instance can be created using any of the following options:
1.
Using new keyword
2.
newInstance() method of java.lang.Class
using new keyword;
·
we may want to write the code as shown below:
java.sql.Driver d=new Oracle.jdbc.driver.OracleDriver();
·
the above statement (for creating Driver class
object) is simple but makes our program tightly bounded to OracleDriver.
·
Migrating to some other driver such as MYSQL Driver needs changes in
the java program.
Note:
Tightly bounded: In case any changes in a program and main
logic may be destroyed is called tightly bounded.
Loosely coupled :
In case without changing the program then it is called loosely coupled.
Example program:
//Message.java
public interface Message
{
String getMessage();
}
//Oraclemsg.java
public class Oraclemsg implements Message
{
public String getMessage()
{
return "I'm from
Oraclemsg";
}
}
//MYSQLmsg.java
public class MYSQLmsg implements Message
{
public String getMessage()
{
return "I'm from
MYSQLmsg";
}
}
//Test.java with using newInstance() method of java.lang.Class
public class Test
{
public static void main(String
args[]) throws Exception
{
String classname=args[0];
Class
c=Class.forName(classname);
Object o=c.newInstance();
Message msg=(Message)o;
System.out.println(msg.getMessage());
}
}
/*
output:
D:\webtechnologies\jdbc\prog\newInstance>javac *.java
D:\webtechnologies\jdbc\prog\newInstance>java Test Oraclemsg
I'm from Oraclemsg
D:\webtechnologies\jdbc\prog\newInstance>java Test MYSQLmsg
I'm from MYSQLmsg
D:\webtechnologies\jdbc\prog\newInstance>
*/
/* (or) we can write the main() by using new operator
//Test.java with using new operator
public class Test
{
public static void main(String
args[])
{
Message msg;
msg=new OracleMsg();// i can't
call MYSQLmsg class method, and i got confused which class object want to
//create the object so here new operator is not recommended, if you use
new operator it leads to write
//hardcode
System.out.println(msg.getMessage());
//if u want to create MYSQLmsg class method again we need to create the
object something like below
msg=new MYSQLmsg();
System.out.println(msg.getMessage());
}
}
*/
Q: How work with the
Class.newInstance() method to create a object?
Ans:
We want to implement the following two statements to do
this:
//Statement 1:
Class c=Class.forName(className);
// Statement 2:
Object o=c.newInstance();
Q: what happens when
at statement 1 (i.e Class.forName())?
Ans:
The following operations are performed inside the forName()
method:
(a.) Find
is the .class with the given name is loaded into the JVM
·
If Yes: return the Class object representing the
given class loaded.
(b.) :
Load the class into the JVM:
·
If Failed throw the respective exception
·
If successful return the Class object
representing Class loaded.
Q: What is the role
of the Class object in the JVM?
Ans:
Fig: newInstanceJVM.JPG
·
We know that for every type loaded into the JVM
a java.lang.Class instance will be created.
·
This object is responsible to represent the type
definition loaded into the JVM to the java application. So that the java
Application can dynamically introspect
the types.
Q: What happens when we invoke the newInstance()?
Ans:
·
The newInstance() method of Class object creates
a new instance of the class this object is representing
·
This uses a no-argument class constructor to
instance the object.
From the above discussion we prefer to instantiate the
Driver object using the newInstance() method of Class object, as it provides a
convenience to dynamically shift between the implementations.
Note:
In case of private constructor:
//Abc.java
public class Abc
{
private Abc()
{
System.out.println("private Constructor()");
}
public static Abc getInstance()
{
//some logic
return(new Abc());
}
}
//Test.java
public class Test
{
public static void main(String
args[])
{
//creating the object in case
of private constructor
Abc a=Abc.getInstance();
}
}
/*
Output:
D:\material\java(E DRIVE)\java\constructor\private>javac
*.java
D:\material\java(E DRIVE)\java\constructor\private>java
Test
private Constructor()
*/
But in case we are creating the object inside the class (Ex:
Abc.java then no problem directly we can create by using new operator: like
below:
//Abc.java
public class Abc
{
private Abc()
{
System.out.println("private Constructor()");
}
public static void main(String
args[])
{
Abc a=new Abc();
}
}
/*
D:\material\java(E DRIVE)\java\constructor\private\inside>javac
*.java
D:\material\java(E DRIVE)\java\constructor\private\inside>java Abc
private Constructor()
*/
List 1.1: Sample code for Step 1.1: creating a Driver class instance
String
driverClassName=”oracle.jdbc.driver.OracleDriver=”oracle.jdbc.driver.OracleDriver”;
Class c=Class.forname(driverClassName);
Driver d=(Driver)c.newInstance();
Step 1.2: Invoking the
connect() method:
·
We know that the connect() method is non-static thus we need a Driver
object reference which we got in the step 1.1
·
However still we also need to prepare the inputs to the connect(0
method so that we can invoke it.
·
The following are the two inputs for connect() method:
1.
jdbcUrl(String)
2.
dbProperties(java.util.Properties)
jdbcUrl(String):
as the name
described this is a simple string used by JDBC to locate the
database
server that we it to connect.
·
That means we are informing to the Driver object that where the
Database server is located.
·
Why should we inform Driver object only? Because this Driver object
only knows how to connect to the Database server. Url contains the address
(location) of the Database server.
·
The JDBC URL describes the location of the resource (DataBase Server)
to JDBC.
Fig: jdbcurl1.JPG
·
The JDBC URL is of the following format:
Jdbc:<sub-protocol>:<driver_specific_info>
·
From the URL Format we understand locating the database server includes
the information (inputs) that varies from one DB to other and also one JDBC
Driver to other.
Example:
In case of Type-1 Driver:
i.e:
JdbcOdbcDriver we use the following URL format:
jdbc:odbc:<ODBC DSN>
Fig: jdbcurl2.JPG
In case of JDBC Type-2 Driver:
Jdbc:oracle:oci:@<TNS_SERVICE_NAME>
Note:
·
If I want to connect to Oracle DB Server, I have to write the
information about Oracle Driver in Driver object and I have to inform to the
OCN Native Driver which port should I connect( ex: 1521) because ports only
connects to the Instance (like DB Names) probably ports may connects more than
one instance (ex: port 1521 may connect Inventory and finance instances (DB).
·
Once I have declared port name (TNS Service), I should inform to which
Data Base I have to connect
Fig: jdbcurl3.JPG
In case of JDBC Type-4 Driver:
Jdbc:
oracle:thin:@<host_name>:<port>:<DB_SERVICE_NAME>
Fig: jdbcurl4.JPG
2. dbProperties: second (2nd)
argument (parameter) of connect() method
·
Apart from providing the URL for locating the database server we also
require to supply necessary parameters such as username and password to the
database server for authentication and establishing the session.
·
The parameter require for
establishing the session to DB differ from one database to other.
·
The java.util.Properties is an implementation of Map which is suitable
to describe the multiple data elements each of them with a unique name.
Map
Key
|
Value
|
User
|
System
|
Password
|
Manager
|
·
Thus the 2nd argument of connect() method is Properties
OBTAINING A
CONNECTION TO DATABASE
Approach1:
Directly using Driver object
+1.JPG)
Fig: STEP 1(approach1) 1.JPG
Note:
The successful
output of step1 is a connection object.
What is connection object?
Is a JDBC
Driver object responsible torespresent the DB_SESSION to the Java Application.
What is java.sql.Connection?
Is a interface
from JDBC API describing the Connection
object to Java Application.
Step-2, 3 and 4 outlines:
Step2: Create a JDBC
Statement
·
A JDBC Statement object is responsible to represent a Statement
executing to the database server.
·
We use the following method of Connection object to create the
Statement object. (Statement object acts as a vehicle in the bridge for
carrying the data)
Statement createStatement() throws SQLException
Step-3: Executing the Statement:
·
We use the following metho of Statement object to execute the sql.
Int
executeUpdate(String sql) throws SQLException
Step-4: closing the Connection:
·
After the use with the connection we want to properly close it and the
following method of Connection object is used to do this.
Close();
Example:
/*
Jdbc first
Example
we want to
write a java program to create a new record with the following values into the
database table
emp:
empno: 101
ename: e101
salary:1000
deptno:10
Considering
the following table in oracle database to exist
create table
emp(empno number, ename varchar2(20), sal number(10,2), deptno number);
*/
//JdbcExample1.java
import
java.sql.*;
import
java.util.Properties;
public class
JdbcExample1
{
public static void main(String args[]) throws
Exception
{
//we want to create a new record, to do this
we want to execute the following sql to the database
String sql="insert into emp
values(101,'e101',1000,10)";
String
driverClassName="oracle.jdbc.driver.OracleDriver";
Class c=Class.forName(driverClassName);
Driver d=(Driver)c.newInstance();
//STEP 1.2: TYPE -2 DRIVER, thin is a name of
the driver
String
JdbcUrl="jdbc:oracle:thin:@localhost:1521:XE";
Properties dbprops=new Properties();
dbprops.put("user","system");
dbprops.put("password","manager");
Connection con=d.connect(JdbcUrl,dbprops);
Statement st=con.createStatement();
st.executeUpdate(sql);
//step 4:
con.close();
System.out.println("record is
saved");
}
}
/*
How to run
this program:
you need to have the following softwares
installed in the machine:
(1) JDK
(2) Oracle DB
Now perform
the following to get this example run:
(1) create the
table in the DB:
We can run the above mentioned create table
SQL Statement using the SQL* plus Editor.
(2) set
ojdbc14.jar (or) ojdbc5.jar (or) ojdbc6.jar into the classpath.
we can find this jar file in the oracle
install folder.
Note: (a) In this example we are working with Oracle's
JDBC Type-4 Driver
·
Here our intention is to execute the query that is step 4 ( executeUpdate(sql))
·
But it is Method method of statement so I must crate Statement object
to execute the Query, here Statement object acts as a vehicle which carries sql
query to Database
·
So I’m creating Statement object, for creating statement object I must
call createStatement() method, which is a member method of Connection object.
·
So for to do this we are creating Connection object
Q:What is classpath?
classpath: is
a system variable to specify the location of resources (Ex: .class) to locate
by the java
compiler &
the Application class Loader
Here the JVM role is locates the native library
from the folders configured into system environment variable named path.
Q:How to set class path? It depends on Operating
System:
In windows:
(1) using system environment variables
setting window
(2) using 'set' internal command
D:\material\java(E
DRIVE)\java\AdvJava\jdbc>javac JdbcExample1.java
D:\material\java(E
DRIVE)\java\AdvJava\jdbc>set classpath=C:\oraclexe\app\oracle
\product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;
D:\material\java(E
DRIVE)\java\AdvJava\jdbc>java JdbcExample1
record is saved
Q: why .(dot)
directory should be specified in the classpath?
Ans:
Because classloader tries to search even our
current program (Ex: JdbcExample1.class) file in the Oracle
install folder
which we are specifieing, but this is our program which is available only E:
drive..with
some other
location so we will get error, to avoid this problem we are informing to the
class loader that
our current
program (Ex: JdbcExample1.class) is available in the current directory (ie. Ex:
D:\material\java(E
DRIVE)\java\AdvJava\jdbc)
Q: What are The possible problems we may get in
running this program?
Ans:
1. ClassNotFoundException:
oracle.jdbc.driver.OracleDriver
to solve this problem we just need to set
ojdbc.jar file into the classpath.
set
classpath=C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;
2. TNS:
listener does not currently know of SID:
THIS error is raised when we use wrong
service id in the URL.
How to find the correct ServiceId?
Ans:
we have multiple ways, the one simple way
is find it from the Windows Services
(control->services->OracleServiceXE..)
We find a service with the name
OracleService<ServiceID> (Ex:
ServiceID=XE)
Q: What are the changes can be done to work with
Oracle's JDBC TYPE-2 Driver?
Ans:
The Oracle's JDBC Driver Type-2 and Type-4
Drivers are implemented into a single Driver implementation
class so we
don't need to change the driver class.
Only that we need to change is JDBCURL.
In the previous example (i.e:
JdbcExample1.java) change the JDBC URL to:
String
JdbcUrl="jdbc:oracle:oci:@XE";
.JPG)
Fig: JdbcExample1(Type-2).JPG
Q: HOW TO RUN THIS PROGRM (USING TYPE-2)?
Ans:
classpath same as described earlier:
In addition we need to set the oracle bin folder into path:
set
path=c:\oraclexe\app\product\10.0.2\server\bin;%path%
Q:what is %path%?
Ans: now we
are setting the class path that means already we may set other class path also,
but I don’t want to overwrite previous class path, I want to concatenate the
previous class path , that can be done by using this %path%
*/
No comments:
Post a Comment