Chapter -1 The J2EE Platform, JDBC

(Java Database Connectivity)

(1) Introduction to J2EE:
Editions

J2SE J2EE J2ME

The Java 2 platform, Enterprise Edition reduces the cost and complexity of developing multi-
tier services.
It can be rapidly deployed and easily enhanced as the enterprise responds to competitive
pressures.

J2EE is an open standard which is provided by Sun Microsystems for application which run
on servers.

It provides multi-tired architecture for commercial applications. J2EE it is used in order to

maintain Speed, Security and Reliability of Server-Side technology.

It includes J2SE+ most of the other java technologies including JavaMail, Activation,
Servlets, JSF, JMS, EJB and others. Most of the API is component- oriented. They are used
to provide interfaces for business oriented components for enterprise and distributed internet
applications.

Java has emerged as one of the most of the mature and commonly used programming
language for building enterprise software.

Java has manly Three Platform/Editions:

1. J2SE – stands for Java 2 Standard Edition and is normally used for Desktop
Applications
2. J2EE - stands for Java 2 Enterprise Edition for applications which run on server. For
example: Web Site/Web Applications.
3. J2ME - stands for Java 2 Micro Edition for applications which run on small scale devices
like cell phones .for example: games

1 word Question Answer

Sr. No. Question Answer
1. J2SE stands for? Java 2 Standard Edition
2. J2EE stands for? for Java 2 Enterprise Edition
3. J2ME stands for? Java 2 Micro Edition
4. How namy Editions of Java? 3
5. J2EE is used for? Reduce complexity and cost of
developing the application
6. J2EE developed by Sun Microsystem
7 J2EE provide feature like Speed, security, realibility
 (2) Enterprise Architecture Types:
Enterpris
e
Architectur
e

The User Interface The Processing Logic Data Access Layer

A Software application composition can be broken down into three fundamental

logical layer, The User Interface, The Processing, and Data.
1. The User Interface/ Presentation Layer
2. Processing Logic/Business Layer
3. Database/ Data Access Layer

1. The User Interface / Presentation layer:

This is Responsible for displaying interface to the user and collecting data from
the user. Often called as the Presentation Layer. Its job is to presents stuff to the
user and
provides to the software system.
The presentation layer includes the part of the Software that creates and controls the
user interface and validates the user’s action.

2. The Processing / Business layer:

In this layer, the application work and handles the important processing. The logical
layer is called the business rules layer.

3. Reading and Writing data/ Data Access Layer:

All nontrivial business application needs to read and store the Data. The part of the
Software that is responsible for reading and writing data from whatever source is
known as Data Access Layer.

Whenever we Design any enterprise application we have to use concept of N-Tier

architecture. As we are aware of client-server system which is based on 2-Tier architecture
because there is clear separation between the Data and the Logic

The 2-Tier architecture is generally data driven with application existing entirely on the local
machine and the Database deployed at a specific and secure location in the Organization.

There are four types of architecture which are as follows.

§ Single-Tier Architecture
§ 2 -Tier Architecture
§ 3-Tier Architecture
§ N-Tier Architecture

The Single Tier Architecture:

Simple Software application is written to run on a single computer such architecture is called
as Single tier architecture. Because all of the logical application services presentation, the
business rules and the data access layer exit in a single computing layer.
Such systems are relatively easy to manage and Data consistency is simple because the Data
is stored at only one single location. The only problem with such systems is it can not handle
multiple users and do not provide easy means of Sharing the Data across the Firm.
Advantages:
Single tier system is relatively easy to manage and data consistency is simple
because data is stored at only one place.

Disadvantages:
-> Now a day’s single location is not sufficient because of changing business needs.
-> We can not share the data in large amount
-> It can not handle multiple users.

2-Tier Architecture:

Application which is divided into two separate tiers namely Client machine and database Server
machine called as Two Tier Architecture. This is the traditional method of enterprise
development. It includes presentation and business logic. In a 2-Tier application the

processing load is given to the client while to the server simply controls the Traffic between
the application and the Data as shown in figure.

Thin Client:

With 2 tier architecture, if the presentation manager resides only with client tier then the
client is called as thin client. other presentation logic, application logic, business logic,
database logic and database manager resides with the server side.
Request
Server Tier
Client Tier
Presentation Presentation Logic
manager
Application Logic
Response Business Logic

Database Logic
Database Manager
Thick Client:

With 2 tier architecture,if the presentation manager, presentation logic, application logic
resides with the client side then the client is called as thick client other like business
logic,database logic and database manager resides with the server side.

Server Tier
Client Tier

Presentation Business Logic

manager
Presentation Database Logic
Logic
Application Database Manager
Logic
Advantages:
Any changes made in data access logic will not affect the presentation and
business logic. With 2 tier architecture it is easy to develop an application.

Disadvantages:

- In case of limited resource pc, the performance would be suffered due to pc resources.
- It supports a lmited number of users.
- Each client requires its own connection and each connection requires CPU and
memory.

- As number of connection increase, the database performance degrades.

- If the application requires more than one database request at a time it is not possible
for the server to manage it.
- This architecture requires high maintenance.
- When the data is shared among various users, it will caused problem what is to be
displayed to which user regarding the latest data.

3 –Tier architecture:-

Fig: 3 tier architecture

Application which is divided into 3 tier namely Client tier, middle tier, and database (Enterprise
Information System-EIS) tier is known as Three Tier Architecture. Logic physically separates
the business rules. Presentation layer and logic runs on Client machine, application and business
logic runs on J2EE server and database logic is there with database layer.
Thin Client:

With 3 tier architecture,if the presentation manager resides only with client tier then the client
is called as thin client, other presentation logic,application logic,business logic,database logic
and database manager resides with the EIS tier(databse).

Thick Client:

With 3 tier architecture,if the presentation manager,presentation,application logic resides with

client tier then the client is called as thick client. Business logic is only with the business tier.
Database logic and database manager resides with the EIS Tier(databse).

Advantages:

- It improves scalability since the application can be deployed on many machines.

- The database no longer required a connection from every client-it only requires connections
from a smaller number of application server.
- It provides better reusability because the same logic can be initiated from many client or
applications.
- It provides security because client does not have direct access the database.

Disadvantages:

It increases the complexity because to develop the 3 tier application is more difficult than
developing a 2 tier application.

N-Tier Architecture:-

An application which is divided into more than 3 tier can be called as N Tier [Link]
N tier architecture it is not decided how many tiers can be there,it depends on computing and
network hardware on which application is deplolyed.

Basically it is divided into four layers: Application layer, Client tier, business tier (EJB) and
database tier (EIS).

Client Tier:
It consists of the user interface for user request and print the [Link] runs on client
machinme .It basically uses the browser or applet as client side application.

Web Tier:
It consists of JSP and Servlet dynamic webpages to handle HTTp specific request logons,
sessions, accesses the business services and finally constructs response and send back to the
client.

Business Tier:
It consists of the business logic for the J2EE application .For Example: EJB (Enterprise Java
BeanS) the benefit of having a centralized business tier is same business logic can support
different type of client like browser, WAP, other stand alone application.
EIS Tier:

It consists of the DBMS/RDBMS. It handles the users SQL request and generates appropriate
response base on queries. It is responsible for communicating with external recourses such as
legacy systems, ERP systems, messaging systems like MQSeries [Link] also stores all
persistent data in the database.

J2EE is based on N tier architecture application. J2EE makes easy to develop the
Enterprise application based on 2, 3, more application layers. Here it also proves that J2EE is
distributing computing framework and multi-tiered application.
· Advantages

1) Improved maintainability 4) Provides consistency

2) Interoperability 5) Flexibility
3) Scalability 6) Security

· Disadvantages

It is having complex structure and difficult to setup and mainjtain all separated layers.

(3) Enterprise Architecture of J2EE

The architecture of J2EE platform uses a multi-tier distributed application model. The
application logic, according to the function will be divided into different component and
application components make up a J2EE application.
The J2EE application consists of three or four tier.J2EE multi-tier application because the
architecture has defined three tiers namely client-tier, middle tier and back end tier. This
means that the application logic is distributed over different location.

1 Word Question Answer

Sr. No. Question Answer
1. Which layer Responsible for displaying User interface
interface to the user and collecting data from
the user
2. The other name of user interface is? Presentation Layer
3. The logical layer is also called? Business rules layer
4. The part of the Software that is responsible Data Access Layer
for reading and writing data from source?
4. Simple Software application is written to run Single tier architecture
on a single computer such architecture is
called as?
5. In which system is relatively easy to manage Single tier architecture
and data consistency is simple?

6. Application which is divided Client machine Two Tier Architecture

and database Server machine called as?
7. With 2 tier architecture, if the presentation Thin client
manager resides only with client tier then the
client is called as?
8. In which architecture number of connection Two Tier Architecture
increase, the database performance
degrades?
9. In which architecture the database no longer 3- tier Architeture
required a connection from every client-it
only requires connections from a smaller
number of application server?
10. In which architecture client does not have 3- tier Architeture
direct access the database.?

11. which architecture called N tier An application which is divided

architecture? into more than 3 tier
12. EJB stands for? Enterprise Java BeanS
13. JSP stands for? Java Server Pages
(4) J2EE Components:
J2EE has been developed with a group of various components. With the help of
these components our programs will work on internet.

[1] J2EE
Runtime:-
The J2EE architecture provides the uniform mean of accessing platform level
services via its runtime environment such services includes messaging, security,
distributed transaction etc. The runtime infrastructure of it specifies the role and
interface for the application and the run time on which the application could be
deployed.

[2] J2EE
API:-
In the enterprise services, the distributed services require access.
These enterprise services include fraction processes, managing, multi-threading,
database
access to such services in its services APIs. The application programs in the J2EE can
access there APIs through the container.

(5) J2EE API:

[1] Java Database Connectivity(JDBC 2.0) :-

The jdbc API lets you invoke SQL commands from the java programming
method. This API is useful to connect our java database application with any
relational database like ORACLE, MYSQL, MSACCESS. You can also use the jdbc
API from a servlet or JSP page to access your database directly. The jdbc API is divided
into two part.

- An application level interface used by the application component to access the

database.
- Service provider interface to attach a jdbc driver to the J2EE platform.

[2] EJB :- ( Enterprise Java Beans)

An enterprise beans is a body of code with field and method to implement
modules of the business log. An enterprise bean can be considered as building block
that can be used along or with other enterprise beans to execute business logic on
J2EE server. It is useful to define server side component .
There are kinds of enterprise beans.
1). Session beans.
2). Entity beans.
3). Message beans.

[3] Java Servlet (JS)(2.3) :-

This API provides object oriented approach for building dynamic web
application. The Java servlet technology lets you to define the http specific servlet
classes. A servlet class extends the capability of server that host the application.

[4] Java Server Page(JSP 2.1) :-

This API provides easy way for building dynamic web application. The JSP
technology allows you to put the combines snippets(general UDF) of java programming
 language code with static markup text based documents. A JSP page is a document
which contains two types of text.
- Static template data in text based format like html, xml.
- JSP elements that determine how the page constructs the dynamic contain.

[5] Java Server Faces (JSF) :-

JSF is the relatively the new technology that attempts to provide a rich user
interface for the web application used in conjunction with servlet and JSP.

[6] Java Message Services (JMS 1.1) :-

The JMS API is a messaging standard that allows J2EE application
components to create, send, receive and read messages. It enables distributed
communication that is loosely coupled, reliance and asynchronous.

[7] Java Transaction API (JTA 1.0) :-

The JTA API provides the interface for the transaction. The J2EE architecture
provides a default auto-committee to handle the transaction committee and roll back.

[8] Java Mail API (1.3) :-

Many application need to send e-mail notification for that J2EE platform
includes the Java Mail API with Java Mail Service provider that application
component can use to send an internet e-mail. It has two parts.

- An application level interface used by the application to send mails.

- A services provider interface.

[9] Java Naming and Directory Interface (JNDI) :-

JNDI provides the naming & directory functionality. It provides the
application with the methods for performing standard directory operations such as a J2EE
application can store & receive any type of named java object.

(6) CONTAINERS:
· Introduction to J2EE Containers :-

Definition:

Containers are interface between a component and client.

It is a platform or place which supports component.
Container provides communication platform between client and component.

Containers are the central thing of J2EE architecture container are like the rooms in house,
people and thing exists in a room and interface with the infrastructure through well defined
interface.

Types of
Container

Applet Container Application EJB Container WEB container

Container
 Applet Container: Applet Container is a client container which is used to manage
an execution of the applet on the browser.

Application Container: Application Container is a client container which is used to

manage application client and their container.

EJB Container: EJB Container is a server container which is used to manage

Enterprise bean component.

WEB container: WEB container is a server container which is used to manage an

execution of the JSP and Servlet Components.

1 word Question Answer

[Link] Question Answer
1. which Container is a client container which is used Applet Container
to manage an execution of the applet on the browser
2. which Container is a client container which is used Application Container
to manage application client and their container.
3. which Containeris a server container which is used EJB Container
to manage Enterprise bean component?
4. which container is a server container which is used WEB container
to manage an execution of the JSP and Servlet
Components
5 Web container manage JSP and Servlet
6 EJB Container manage Enterprise beans
7 Applet Container manage Execution of applet from
browser
8 JTA stands for Java Transaction API
9 JNDI stands for Java Naming and Directory
Interface
10 JMS stands for Java Messge Services
11 EJB stand for Enterprise Java Beans

(7) Tomcat as a Web Container:

Tomcat

Servlet container

Developed by the Apache Software

Foundation
Implements the Java Servlet and the Java Server
Pages (JSP)
Apache Tomcat is a servlet container developed by the Apache Software Foundation developed
by the Apache Software Foundation (ASF).

Tomcat implements the Java Servlet and the Java Server Pages (JSP) specifications from Sun
Microsystems, and provides a "pure Java" HTTP web server environment for Java code to
run.

Tomcat should not be confused with the Apache web server, which is a C implementation of
an HTTP web server; these two web servers are not bundled together.

Apache Tomcat includes tools for configuration and management, but can also be configured
by editing XML configuration files.

1 word Question Answer

[Link]. Question Answer
1. Who developed Tomcat? Apache Software Foundation
2. implements the Java Servlet and the Tomcat
Java Server Pages
3. Apache web server and Tomcat are same? No
4. ASF full form Apache Software Foundation
5. JMX full form Java Managment Extensions
6. Tomcat 4.x released in 2001

An overview of the different versions can be found on the Apache website

Tomcat 4.x
· Released 2001
· implements the Servlet 2.3 and JSP 1.2 specifications
· servlet container redesigned as Catalina
· JSP engine redesigned as Jasper
· Coyote HTTP connector
· Java Management Extensions (JMX), JSP and Struts-based administrations

Tomcat 5.x
· implements the Servlet 2.4 and JSP 2.0 specifications
· reduced garbage collection, improved performance and scalability
· native Windows and Unix wrappers for platform integration
· faster JSP parsing

Tomcat 6.x
· implements the Servlet 2.5 and JSP 2.1 specifications
· support for Unified Expression Language 2.1
· designed to run on Java SE 5.0 and later
· support for Comet through the CometProcessor interface
· is not packaged with an admin console as in past releases

CLASSPATH:

To set the classpath follow the steps:

- Right click on my computer

-go to Properties Advanced Environment variable New

-Variable name = CLASSPATH

 c:\ProgramFiles\ApoacheSoftwareFoundation\ApcheTomcat6.0\lib\[Link];
-then press ok

· J2EE as Application Server

An application server is a software framework that provides a generalized approach to

creating an application-server implementation.

it is related to what the application functions are.

It is the server portion for implementation of instance of application.

An application server's function is dedicated to the efficient execution of procedures

(programs, routines, scripts) for supporting its applied applications.

Most Application Server Frameworks contain a comprehensive service layer model.

An application server acts as a set of components accessible to the software developer

through an API defined by the platform itself.

For Web applications, these components are usually performed in the same running
environment as its web server(s), and their main job is to support the construction of dynamic
pages.

Many application servers target much more than just Web page generation: they implement
services like clustering, fail-over, and load-balancing, so developers can focus on
implementing the business logic.

In the case of Java application servers, the server behaves like an extended virtual machine
for running applications, transparently handling connections to the database on one side, and,
often, connections to the Web client on the other.

Other uses of the term may refer to the services that a server makes available or the computer
hardware on which the services run.

Java Platform, Enterprise Edition or Java EE (was J2EE) defines the core set of API and
features of Java Application Servers.

The Web modules include servlets, and JavaServer Pages.

Enterprise JavaBeans are used to manage transactions.

Some Java Application Servers leave off many Java EE features like EJB and JMS
including Tomcat from Apache, and Jetty from Eclipse Foundation. Their focus is more on
Java Servlets and JavaServer Pages.

There are many open source Java application servers that support Java EE like
GlassFish server from
Oracle.

A Java Server Page (JSP) executes in a Web container. JSPs provide a way to create
HTML pages by embedding references to the server logic within the page. HTML coders and
Java programmers can work side by side by referencing each other's code from within their
own.
 The application servers mentioned above mainly serve Web applications, and services via
RMI, EJB, JMS and SOAP. Some application servers target networks other than web-based
ones: Session Initiation Protocol servers, for instance, target telephony networks.

1 Word Question Answer

[Link]. Question Answer

1. JSP Stands for? Java Server Page

2. SOAP stands for? Simple Object Access

Protocol

3. JMS stands for? java Message Service

4. RMI stands for? Remote Method Invocation

5. Application server is work for Load balanceing, fail over,

clustering, memory
management etc

6. Oracle provides which server? Glassfish server

7.

(8) Introduction and Need for JDBC:-

JDBC

Java Database Structure is similar to The result is a set of class

Connectivity understand given in [Link] package.

JDBC Stands for the Java Database Connectivity.

The Role of the JDBC is to most important with respect to java database application.
If you are familiar with SQL and relation Database. The structure of JDBC API is similar and
easy to understand.
JDBC API provides connectivity and Data access across the range of relational database.
JDBC generalize the most common database access function by abstracting the vendor
specific details of the database.
The result is a set of class given in [Link] package.

JDBC enables java application & applet common to the database.

The main idea behind this is we have been able to have different application to the different
database. The [Link] gives us to facility to connect to every database.

JDBC-ODBC:
 ODBC

Open Database Using ODBC we can java has developed

connect access, oracle, sql, [Link] package for
connectivity
visual FoxPro this interface
ODBC stands for the Open Database connectivity, where we want to our regular database
like access, oracle, sql, visual FoxPro etc. We have to use the JDBC ODBC driver, ODBC is
standard which is purposed by Microsoft and we known that Microsoft is widely used in
computer technology,

so sun Microsystems has accepted ODBC as Universal driver and java has developed
[Link] package for this interface.

To connect JDBC by using ODBC we have to develop one ODBC driver by using the
Control panel 32-bit ODBC program from your window application.

The Steps for the Creating DSN are as follows.

àDouble click on 32-ODBC Program
àClick on New Button
àGive the DSN Name
àSelect appropriate Driver
àClick on to Complete Preparation

JDBC V/S ODBC:-

· ODBC can not be directly used with Java because it uses C interface. Calls from Java to
native C code have number of drawbacks in the security, implementation, robustness
and automatic portability of applications.

· ODBC make use of Pointer which has been totally removed from java.

· ODBC mixes simple advanced features together and has complex options for simple
[Link] JDBC is designed to keep things simple while allowing advanced capabilities
when required.

· ODBC requires manual installation of the ODBC driver manager and driver on all
client [Link] drivers are wriiten in Java and JDBC code is automatically
installable, secure and portable on all java platform.

· JDBC API is a natural Java interface and is built on ODBC JDNC retains some of the
basic features of ODBC like SQL call level interface.

JDBC API
JDBC API Is collection of methods, classes and interfaces that enable java applications to
communicate with database.
For this database should be installed on the computer and RDBMS should provide a driver.
JDBC classes and interfaces defined in the package [Link] constitute the JDBC API.

The relations between a java program, JDBC API, the JDBC driver and database is shown in
figure 3.1
Java
Program

JDBC API

JDBC
DRIVER
Database
 Using JDBC API a java application can perform insert, delete, update and select rows
in a [Link] can also retrieve the data from the database and show itin a proper formate to
the user according to the SQL query.

The JDBC API can be used in 3 tier databse architecture.

In 2 ier architecture,java programs invoke the method of JDBC API and communicate
with the database server. so there is a direct interaction with the database server.

1 Word Question Answer

[Link]. Question Answer
1. JDBC Stands for? Java DatabaseConnectivity

2. ODBC stands for? Open Database connectivity

3. The result is a set of class given in which [Link] package
package?
4. Which package gives us to facility to [Link]
connect to every database?

5. sun Microsystems has accepted as ODBC

Universal driver

6. Which package is dwveloped by java? [Link]

7. is designed to keep things simple while JDBC

allowing advanced capabilities when
required.
8. can not be directly used with Java. ODBC

9. requires manual installation. ODBC

10. API Is collection of methods, classes JDBC API

and interfaces.

(9) JDBC Architecture:

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 JDBC driver for defining database.

JDBC API is responsible for transferring data between an application and a

database. The JDBC architecture is divided into two parts: JDBC Driver

types and JDBC API

 1 Word Question Answer
[Link] Question Answer
1. JDBC Architecture describes the interaction of Java application and java
JDBC API with ? applet
2 is responsible for transferring data between JDBC API
an application and a database.

(10) JDBC Data types:-

The JDBC driver converts the Java data type to the appropriate JDBC type before sending it
to the database. It uses a default mapping for most data types. For example, a Java int is
converted to an SQL INTEGER. Default mappings were created to provide consistency
between drivers.

The following table summarizes the default JDBC data type that the Java data type is
converted to when you call the setXXX() method of the PreparedStatement or
CallableStatement object or the [Link]() method.

SQL JDBC/Java setXXX updateXXX

VARCHAR [Link] setString updateString
CHAR [Link] setString updateString
LONGVARCHAR [Link] setString updateString
BIT boolean setBoolean updateBoolean
NUMERIC [Link] setBigDecimal updateBigDecimal
TINYINT byte setByte updateByte
SMALLINT short setShort updateShort
INTEGER int setInt updateInt
BIGINT long setLong updateLong
 REAL float setFloat updateFloat
FLOAT float setFloat updateFloat
DOUBLE double setDouble updateDouble
VARBINARY byte[ ] setBytes updateBytes
BINARY byte[ ] setBytes updateBytes
DATE [Link] setDate updateDate
TIME [Link] setTime updateTime
TIMESTAMP [Link] setTimestamp updateTimestamp
CLOB [Link] setClob updateClob
BLOB [Link] setBlob updateBlob
ARRAY [Link] setARRAY updateARRAY
REF [Link] SetRef updateRef
STRUCT [Link] SetStruct updateStruct

JDBC 3.0 has enhanced support for BLOB, CLOB, ARRAY, and REF data types.

Handling NULL Values:

SQL's use of NULL values and Java's use of null are different concepts. So how do
you handle SQL NULL values in Java?
(1) Avoid using getXXX( ) methods that return primitive data types.
(2) Use wrapper classes for primitive data types, and use the ResultSet object's wasNull( )
method to test whether the wrapper class variable that received the value returned by
the getXXX( ) method should be set to null.

Here is one example to handle a NULL

value: Statement stmt =
[Link]( );
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = [Link](sql);

int id = [Link](1);
if( [Link]( ) ) {
id = 0;
}

1 Word Question Answer

[Link]. Question Answer
1. converts the Java data type to the appropriate JDBC driver
JDBC type before sending it to the database.
2. were created to provide consistency between Default mapping
drivers.

3. How do you handle SQL NULL values in Java? Avoid using getXXX ( )

methods that return primitive

data types.
(11) JDBC Database Drivers:
JDBC Drivers specification classifies JDBC drivers into four groups. Groups are referred to
as JDBC Drivers types and address a specific need for Communicating with various DBMS
Type -1 : JDBC to ODBC Driver
The JDBC-ODBC bridge driver converts all JDBC calls
into ODBC calls and sends them to ODBC [Link]
ODBC driver then converts the call to the database server
.For example: MS ACCESS do not provide JDBC Driver
[Link] they provide ODBC [Link] can access
such database by using ODBC-JDBC Bridge.

Advantages:
 It allows access to any database
Disadvantages:

- This driver not fully written in java so not portable.

- Performance is slow compare to all drivers because
JDBC calls are converted to ODBC calls and ODBC calls
are converted to database specific [Link] step in reverse order when query is obtained.
- Client system requires ODBC installation to use the
drivers.
-Not good for web application or for large amount of
database based application.

(2) Type-2 : NATIVE API Driver

The Java/Native Code driver Communicates directly
with database server. Therefore the vendor database
library needs to be loaded on each client [Link]
converts JDBC calls into database specific calls for
[Link] example: Oracle will have native API

Advantages:
They offer better performance than JDBC-ODBC [Link] as the layers of
communication are less than type-1 driver and also use native API which is database specific.
Disadvantages:

- Native API must be installed in client system and hence type-2 driver cannot be
used for internet.
- It is not written in java which forms a portability issues
- If we change the database we have to change Native Api as it is specific to
database.
- Mostly outdated because it is not thread safe.
- It is not suitable for distributed application.

(3)Type-3 JDBC Drivers/NET Protocol Driver:

This drivers follows a 3 tired [Link] 3 tired approach

JDBC requests are passed to a middle tier [Link]
middle Tier server then translates request to the database
specific native connectivity interface and forward the
request to the database [Link] middle server is written
in java can use type-1 or type-2 driver to forward the
request to the database server.

It is used to connect client application/applet to

database server over TCP/IP connection .The presence
of any vendor database library on client computer is not
required because type-3 driver is servere based.

Advantages:

- It is sever based so no need for any vendor database library to be present on client
machine.
- This is fully written in java and hence portable, so it is suitable for web
 application.
- It provides portability, performance, security and scalability.
- Net protocol can be designed to make client JDBC driver very small and fast to
load.
- It provides support for feature such as caching, load balancing, advanced system
administration such as logging and auditing.
- Very flexible and allows access to multiple database using one driver
- They are efficient among all drivers.
Disadvantages:
Type-3 driver requires database specific coding to be done on middle tier and it requires
additional server for that. Additionally traversing the recordset may take longer because the
data comes-through the backend server or backend database

(4)Type-4 JDBC Driver/Native Protocol

Type-4 driver are completely written in java
to achieve platform [Link] converts JDBC
calls
into vendor specific DBMS [Link]
client application can communicate directly
with the
database server when type-4 driver is used.

Type-4 driver is better than other driver

because type-4 driver do not have to translate
database request to ODBC calls or a native
connectivity interface or pass on to another server.

Disadvantages
:
Different driver is needed for each database.

Advantages
:

- They are written in java to

achieve platform [Link]
reduces
deployment administration [Link] is most suitable for web
application.
- Number of layers is very less Type-4 do not translate database request to
ODBC
or native connectivity interface or to pass to the server so performance is good.
- You do not need to install special software in the client [Link] driver
can be downloaded dynamically.

1 Word Question Answer

[Link]. Question Answer
1. driver Communicates directly with database Java/Native Code
server?
2. Which driver provides portability, performance, JDBC Drivers/NET Protocol
security and scalability? Driver
 3. are completely written in java to achieve JDBC Driver/Native Protocol
platform indepence
4. driver can be downloaded dynamically? JDBC Driver/Native Protocol

(12) JDBC Process:

Processing SQL Statements with JDBC

In general, to process any SQL statement with JDBC, you follow these steps:

· Establishing a connection.
· Create a statement.
· Execute the query.
· Process the ResultSet object.
· Close the connection.

(1) Establishing Connections

First, establish a connection with the data source you want to use. A data source can be a
DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver.
This connection is represented by a Connection object.

(2) Creating Statements

A Statement is an interface that represents a SQL statement. You execute Statement objects,
and they generate ResultSet objects, which is a table of data representing a database result
set. You need a Connection object to create a Statement object.

There are three different kinds of statements:

· (1) Statement: Used to implement simple SQL statements with no parameters.

· (2) PreparedStatement: (Extends Statement.) Used for precompiling SQL statements
that might contain input parameters.
· (3) CallableStatement: (Extends PreparedStatement.) Used to execute stored
procedures that may contain both input and output parameters.

(3) Executing Queries

To execute a query, call an execute method from Statement such as the following:

· execute: Returns true if the first object that the query returns is a ResultSet object.
Use this method if the query could return one or more ResultSet objects. Retrieve
the ResultSet objects returned from the query by repeatedly
calling [Link].
· executeQuery: Returns one ResultSet object.

· executeUpdate: Returns an integer representing the number of rows affected by the

SQL statement. Use this method if you are using INSERT,
DELETE, or UPDATESQL statements.

(4) Processing ResultSet Objects

You access the data in a ResultSet object through a cursor. Note that this cursor is not a
database cursor. This cursor is a pointer that points to one row of data in the ResultSet
object. Initially, the cursor is positioned before the first row. You call various methods
defined in
the ResultSet object to move the cursor.

(5) Closing Connections

When you are finished using a Statement, call the method [Link] to immediately
release the resources it is using. When you call this method, its ResultSetobjects are
closed.

1 Word Question Answer

[Link]. Question Answer
1. What is the first step of JDBC Process? Establishing a connection.

2. A Statement is an interface that represents . SQL statement

3. used for precompiling SQL statements that PreparedStatement

might contain input parameters.
4. used to execute stored procedures that may CallableStatement
contain both input and output parameters.
5. returns true if the first object that the query Execute
returns is a ResultSet object.
6. returns one ResultSet object. executeQuery

7. returns an integer representing the number of executeUpdate

rows affected by the SQL statement.
8. You access the data in a ResultSet object Cursor
through .
9. When you are finished using a Statement, call the [Link]
method .

(13) JDBC Exception Handling:-

Exception handling allows you to handle exceptional conditions such as program-
defined errors in a controlled fashion.
When an exception condition occurs, an exception is thrown. The term thrown means
that current program execution stops, and control is redirected to the nearest applicable
catch
clause. If no applicable catch clause exists, then the program's execution ends.

JDBC Exception handling is very similar to Java Excpetion handling but for JDBC, the
most common exception you'll deal with is [Link].

SQLException Methods:

A SQLException can occur both in the driver and the database. When such an
exception occurs, an object of type SQLException will be passed to the catch clause.
The passed SQLException object has the following methods available for retrieving
additional information about the exception:
 Method Description
getErrorCode( ) Gets the error number associated with the exception.
Gets the JDBC driver's error message for an error
getMessage( ) handled by the driver or gets the Oracle error number
and message for a database error.
Gets the XOPEN SQLstate string. For a JDBC driver
error, no useful information is returned from this
getSQLState( )
method. For a database error, the five-digit XOPEN
SQLstate code is returned. This method can return null.
getNextException( ) Gets the next Exception object in the exception chain.
Prints the current exception, or throwable, and its
printStackTrace( )
backtrace to a standard error stream.
printStackTrace(PrintStream Prints this throwable and its backtrace to the print stream
s) you specify.
printStackTrace(PrintWriter Prints this throwable and its backtrace to the print writer
w) you specify.

By utilizing the information available from the Exception object, you can catch an
exception and continue your program appropriately. Here is the general form of a try block:

try {
// Your risky code goes between these curly braces!!!
}
catch(Exception ex) {
// Your exception handling code goes between these
// curly braces, similar to the exception clause
// in a PL/SQL block.
}
finally {
// Your must-always-be-executed code goes between these
// curly braces. Like closing database connection.
}

1 Word Question Answer

[Link]. Question Answer
1. Gets the error number associated with the exception. getErrorCode( )
2. Prints the current exception, or throwable, and its printStackTrace( )
backtrace to a standard error stream.
3. Gets the XOPEN SQLstate string. For a JDBC driver getSQLState( )
error, no useful information is returned from this method.
For a database error, the five-digit XOPEN SQLstate code
is returned. This method can return null.
4. Gets the error number associated with the exception. getErrorCode( )

(14) JDBC API FOR DATABASE CONNECTIVITY

1) Connection Interface:
 This defines connection to different database .This provides following methods.

(1) createStatement()

It is used to create a Statement object for sending SQL Statements to the database.
For example:
Statement st;
St=[Link]
();
OR
St=[Link](ResultSet.TYPE_SCROLL_INSENSITIVE,[Link]
_
UPDATABLE);

This method having the following syntax

- Public Statement createStatement() throws SQLException

- Public Statement createStatement(int resultType,int concurrency) throws
SQLException

(2) prepareStatement()

It is used to create a Prepared Statement object for sending SQL request to [Link]
statement with or without IN Parameters can be pre compiled /parameterized and stored in a
Prepared Statement [Link] is used to execute this statements multiple times. For example:

PreparedStatement pstm;
pstm=[Link] ();

This method having the following syntax

- Public PreparedStatement prepareStatement(String sql) throws SQLException

(3) PrepareCall ()

It is used to create a Callable Statement object for calling stored procedure of database. Callable
Statement object provides method for setting up it’s IN and OUT parameters and methods for
executing call to stored procedure. For example:

CallableStatement cstm;
cstm=[Link] (“{ call insertdata (?,?)}”);
[Link](1,sid);
[Link](2,sname);
[Link]();

(4) Close ()

It is used to release [Link] do not wait for it to release it automatically.

[Link] ();

This method having the following syntax

- Public void close() throws SQLException

(5) Commit ()
It is used to save all the state of the database which are changed by transaction. It can be
written as follows:
[Link]();

This method having the following syntax

- Public void commit() throws SQLException

(6) rollback ()

It is used to cancel the effect of current running transaction.

It can be written as follows:
[Link] ();
This method having the following syntax

- Public void rollback() throws SQLException

(7) SetAutoCommit ()

It is used to do auto commit all the transaction .if we set auto commit with connection
all SQL statements executed and committed together as individual [Link] has
Boolean type parameter .If we pass true then it enables the auto commit and if we
pass false it disables the auto commit.

It can be written as follows:

[Link] (true);

This method having the following syntax

- Public void setAutoCommit (boolean b) throws SQLException

(8) getAutoCommit ()

It is used to get curruent commited state. It can be written as follows:

Boolean b=[Link] ();

This method having the following syntax

- Public boolean getAutoCommit () throws SQLException

(9) isClosed()

It is used to check whether the connection has been closed or not.

Boolean

b=[Link](); This method having the

following syntax

Public boolean isClosed () throws SQLException

(10) getMetaData()

It is used to get metadata(data about data)related to current connection with [Link]

provides information like table driver name,driver type,its version and so [Link] ses
DatabaseMetaData interface object to get information about database drivers. For example:
Connection con;
con=[Link] (url,””, “”);
DatabaseMetadata ds=[Link] ();
 1 Word Question Answer
[Link]. Question Answer
1. is used to create a Statement object for createStatement()
sending SQL Statements to the database
2. is used to execute this statements multiple prepareStatement()
times.
3. is used to create a Callable Statement object PrepareCall ()
for calling stored procedure of database.
4. used to release connection. Close ()
5. is used to save all the state of the database Commit ()
which are changed by transaction
6. is used to cancel the effect of current rollback ()
running transaction.

(15) DriverManager Class

DriverManager class belongs to [Link] package. It consists of static method to manage

JDBC Drivers.
Each and every driver must register with DriverManager class. There are many JDBC
Drivers used for different JDBC servers.

For example: JDBC Drivers for MS ACCESS is different from ORACLE Driver. It
controls interface between application and JDBC Drivers.

DriverManager class has one method getConnection() to establish connection with

different database server.

Public static Connection getConnection (String url) throws SQLException

Public static Connection getConnection (String url, Properties info) throws SQLException
Public static Connection getConnection (String url, String username, String password) throws
SQLException

(16) Statement Interface

Statement object is used to execute SQL statements and obtain the result produced after
excuting the SQL [Link] is having following methods.

(1) ExecuteQuery ():

It is used to fetch records from database using select [Link] passes SQL statement as a
[Link] returns single Resultset Objects that contains rows, columns and metadata that
represent data requested by [Link]:

Statement st;
St=[Link] ();
ResultSet rs=[Link] (“select * from tablename”);
Syntax:
Public static ResultSet executeQuery (String url) throws SQLException

(2) ExecuteUpdate ():

It is used to perform insert, update, and delete operation on record of [Link] returns
integer value indicating no. of records updated in [Link] passes SQL as a parameter.

Statement st;
St=[Link] ();
ResultSet rs=[Link] (“insert into tablename values (val1, val2…, valn”);
Syntax:
Public int executeUpdate (String url) throws SQLException
(3) execute ()

The execute () Statement is used when they returns Multiple result. It returns Boolean
indication that if it returns true it defines that next result is ResultSet object and if it returnd
false then it defines no of records updated or no more results.

Statement st;
St=[Link] ();
String sql=”create table tablename (fieldname type (size), fieldname
type(size))”;
[Link] (sql);
Syntax: Public Boolean execute (String url) throws SQLException

(17) Prepared Statement interface:-

Prepared Statement interface is derived from Statement interface.
It uses a template to create SQL request and uses a Prepared Statement object to send
precompiled SQl statements with one or more parameters. It is more convenient to use a
Prepared Statement object for sending SQL statements to database.

If you want to execute SQL statements many times Prepared Statement object reduces
execution time in comparision to other Statement object.

Main feature of it is SQL statements is given to Prepared Statement object when SQL
statements is [Link] advantage of Prepared Statement object is that in many of the cases
SQL statements is sent to database immediately,where it is compiled.

It also used to execute precompiled statements.

Creating object of Prepared Statement

String sql=”insert into tablename(field1, field1) values(?,?)”;
PreparedStatement pstm=[Link](sql);
SetXXX() methods

· setInt(int parameterindex,int x)
· setString(int parameterindex,int x)
· setChar(int parameterindex,int x)
· setFloat(int parameterindex,int x)
· setDouble(int parameterindex,int x)
· setLong(int parameterindex,int x)
· setShort(int parameterindex,int x)
· setDate(int parameterindex,int x)
· setByte(int parameterindex,int x)
· setBlob(int parameterindex,int x)

Execute methods:
(1) executeQuery ():

It is used to fetch records from database using select [Link] passes SQL statement as a
[Link] returns single Resultset Objects that contains rows, columns and metadata that
represent data requested by [Link]:
String sql=“select * from tablename where fieldname=? ”;
setString(1,”C01”);
PreparedStatement pst =[Link] ();;
ResultSet rs=[Link] ();
Syntax:
Public static ResultSet executeQuery throws SQLException

(2) ExecuteUpdate ():

It is used to perform insert, update, and delete operation on record of [Link] returns
integer value indicating no. of records updated in [Link] passes SQL as a parameter.
Statement st = [Link] ();
Int n=[Link] (“insert into tablename values (val1,val2,…..,valn”);
Syntax:
Public int executeUpdate throws SQLException
(3) execute ()

It is used to execute SQL statements which returns multiple [Link] returns the Boolean
indicating that value if it returns true then it defines that next result is ResultSet obkect and if it
returns false then it defines that number of records updated or no more results is [Link] passes
any sql statements as parameter.

import [Link]; import

[Link]; import
[Link];

(18) Callable Statement

The callable statement is useful to called stored procedure from within a J2EE object. Stored
procedure is a block of code and is identified by a unique name. The type and style of code
depend on dbms vendor and can be return in PL/SQL. The stored procedure is invoked by giving
name of stored procedure.

The callable statement uses three types of parameters when calling a stored procedure. These

The IN parameter contains any data that needs to be passed to the stored procedure and
whose value is assign using the setXxx ().

The OUT parameter contains the value return by the stored procedure if any OUT parameter
must be registered using the register OUT parameter () and then his later retrieve by J2EE
component by using getXxx ().

The INOUT parameter is a single parameter that is use for both that is to pass information to
stored procedure and to retrieve information from stored procedure.

In above program, the statement of try block creates a query that will call the stored
procedure last no. order which retrieve the most recently used order number.

Thestoredprocedurerequiresoneparameterthatisrepresentedby“?“.

The parameter is an OUT parameter that will contain last order no. following the execution
of stored procedure.

The registeroutParameter () requires two parameters. The first parameter is an integer that
represents no. of the parameter. Here one means, the first parameter of stored procedure and
second parameter is data type of value return by the stored procedure.

The execute() of callable statement will be useful to execute the query. After the stored
procedure is executed, getString() is called to return the value of specified parameter of
stored procedure.

Syntax for stored procedure with IN parameters:

{call procedure_name [(?,?,…..)]}

Syntax for stored procedure with IN and OUT parameters:

{ ? = call procedure_name [(?,?,…..)]}

Syntax for stored procedure with no parameters:

{call procedure_name}
CallableStatement sctm=[Link] (“{call insertdata(?,?)}”);

(19) ResultSet :-
It is used to access the data of the table from database.
Resultset object stores the data of the table by executing the query.

It also maintains the cursor position for navigation of the [Link] can ove on first() ,
last() , previous() ,next() from the current row [Link] also fetch data from the table using
getXXX() method depends on columnType(getString(),getInt()etc.).It can fetch data either using
getXXX().Index number always starts with one.

There are six methods of ResultSet object that are used to position the virtual cursor. They
are first() , last() , previous() , next(),absolute() , relative() , and getRow().

Fields of resultset interface:

The Statement object created using creates Statements.

-TYPE_SCROLL_INSENSITIVE: It defines that the cursor can scroll but can not be
modified or updated.

-TYPE_SCROLL_SENSITIVE: It defines that the cursor can scroll and also be modified
or updated.

-TYPE_FORWARD_ONLY: It defines that the cursor from the current row moves forward
only.

-CONCURE_READ_ONLY: It defines that cursor can scroll and also can be modified or
updated.

Methods of ResultSet Interface:

(1) getString() : It is used to fetch the string type of records from [Link] returns the String
object.
(2) getInt() : It is used to fetch the integer type of records from [Link] returns the String
object.
(3) getBoolean() : It is used to fetch the boolean type of records from [Link] returns either
true/false.
(4) getDouble() : It is used to fetch the decimal type of records from [Link] returns the
double value.
(5) getFloat() : It is used to fetch the float type of records from [Link] returns the String
object.
(6) getDate() : It is used to fetch the date type of records from table. (7)
getLong() : It is used to fetch the long type of records from table. (8)
getShort() : It is used to fetch the short type of records from table. (9)
getByte() : It is used to fetch the string type of records from table.
(10) getBlob() : It is used to fetch binary representing large object from table can be image
file,audio file,video file.

Example: ResultSet rs=[Link] (“select * from emp”);

Int id=[Link](1);
String name=[Link](2);
OR
Example: ResultSet rs=[Link](“select * from emp”);
Int id=[Link](“eno”);
String name=[Link](“ename”);
Navigation Method:
1. first(): It is used to move the cursor position on the first record of the table.
2. last():It is used to move the cursor position on the last record of the table.
3. next():It is used to move the cursor position on the next record of the table.
4. previous():It is used to move the cursor position on the previous record of the
table.
5. afterLast():It is used to move the cursor on after the last row.
6. beforeFirst():It is used to move the cursor on before the first row.
7. relative(int row): It is used to move the cursor on relative [Link] rows.
8. absolute(int rows): It is used to move the cursor on absolute row.

(20) OTHER APIs:-

Meta Data :-

Metadata is about data (data about data). There are two types of metadata interface are available
(1) ResultsetMetadata and (2) Database Metadata. The DatabaseMetaData interface is used to
retrieve information about databases, tables, columns and indexes among other information
about the DBMS.

· ResultSetMataData:

This interface is used to obtain information about type and properties of the column in a
ResultSetMataData are used to store following information of Resultset object:

· What is the number of columns in ResultSet?

· What is the name of columns?

· What is the type of columns in?

· What is the maximum size of columns?

· Can you put a null in this column?

Method Description
getColumnCount() Returns the number of columns contained in the
ResultSet
getColumnName(int n) Returns the name of the column specified by the
column number
getColumnType(int n) Returns the data type of the column specified by the
column number.
isNullable(int column); Indicates the null ability of values in the designated
columns.

(21) DatabaseMetaData Interface:

This interface describes the database as whole. Many methods of DatabaseMetaData

interface return list of information in the format of Resultset objects. It provides many
methods that represent comprehensive information of the database.
 · What tables are available?
· What is your username known to the database?
· Is the database in read only moe?
· What is the name of product?
· What is the name of driver?
· What is the version of driver?
Method Description
getDatabaseProductname() Returns the product name of the database
getUserName() Returns the username
getURL() Returns the URL of the database
getSchemas() Returns all the schema names available in this
database.
getPrimaryKeys() Returns primary keys
getProcedures() Returns stored procedure names
getTables() Returns names of tables in the database.
getDriverName() Retrives the name of JDBC Driver
getDriverVersion() Retrives the version of JDBC Driver
isReadOnly() Retrives whether this database is read only.

1 word Question Answer

[Link]. Question Answer
1. DriverManager class belongs to which package? [Link]
2. DriverManager class has one method which is getConnection()
used to establish connection with different
database server
3. is used to fetch records from database ExecuteQuery ()
using select query.
4. is used to perform insert, update, and executeUpdate ()
delete operation on record of database
5. Statement is used when they returns Execute()
Multiple result.
6. is derived from Statement interface. Prepared Statement interface
7. Which statement is useful to called stored Callable
procedure from within a J2EE object.
8. Which parameters callable statement uses? IN, OUT & INOUT
9. parameter is a single parameter that is use INOUT
for both that is to pass information to stored
procedure and to retrieve information from
stored procedure
10. is used to access the data of the table from ResultSet
database.
11. How many methods of ResultSet object that are Six
used to position the virtual cursor?
12. Which interface describes the database as DatabaseMetaData
whole?

(22)Connecting with Databases:-

After you've installed the appropriate driver, it's time to establish a database connection using
JDBC.
 The programming involved to establish a JDBC connection is fairly simple. Here are these
simple four steps:

· Import JDBC Packages: Add import statements to your Java program to import
required classes in your Java code.
· Register JDBC Driver: This step causes the JVM to load the desired driver
implementation into memory so it can fulfill your JDBC requests.
· Database URL Formulation: This is to create a properly formatted address that points to
the database to which you wish to connect.

· Create Connection Object: Finally, code a call to

the DriverManager object's getConnection ( ) method to establish actual database
connection.

Import JDBC Packages:

The Import statements tell the Java compiler where to find the classes you reference in your
code and are placed at the very beginning of your source code.
To use the standard JDBC package, which allows you to select, insert, update, and delete data
in SQL tables, add the following imports to your source code:
import [Link].* ; // for standard JDBC programs
import [Link].* ; // for BigDecimal and BigInteger support

Register JDBC Driver:

You must register your driver in your program before you use it. Registering the driver is the
process by which the Oracle driver's class file is loaded into memory so it can be utilized as
an implementation of the JDBC interfaces.

You need to do this registration only once in your program. You can register a driver in one
of two ways.

Approach (I) - [Link]():

The most common approach to register a driver is to use Java's [Link]() method to
dynamically load the driver's class file into memory, which automatically registers it. This
method is preferable because it allows you to make the driver registration configurable and
portable.
Database URL Formulation:
After you've loaded the driver, you can establish a connection using the
[Link] () method. For easy reference, let me list the three
overloaded [Link] () methods:

· getConnection(String url)

· getConnection(String url, Properties prop)

· getConnection(String url, String user, String password)

Here each form requires a database URL. A database URL is an address that points to your
database.
Formulating a database URL is where most of the problems associated with establishing a
connection occur.

Following table lists down popular JDBC driver names and database URL.
All the highlighted part in URL format is static and you need to change only remaining part
as per your database setup.

Create Connection Object:

Using a database URL with a username and password:
I listed down three forms of [Link] () method to create a
connection object. The most commonly used form of getConnection () requires you to pass a
database URL, a username, and a password:
Closing JDBC connections:
At the end of your JDBC program, it is required explicitly close all the connections to the
database to end each database session. However, if you forget, Java's garbage collector will close
the connection when it cleans up stale objects.

You should make a habit of always closing the connection with the close() method associated
with connection object.

To ensure that a connection is closed, you could provide a finally block in your code. A
finally block always executes, regardless if an exception occurs or not.
To close above opened connection you should call close() method as follows:

[Link]();

Explicitly closing a connection conserves DBMS resources, which will make your database
administrator happy.

1 Word Question Answer

[Link]. Question Answer
1. Method to dynamically load the driver's [Link]()
class file into memory, which automatically
registers it.
2. You should use the method if you are registerDriver()
using a non-JDK compliant JVM, such as the one
provided by Microsoft.
3. garbage collector
Java's will close the connection when it
cleans up stale objects.