31-07-2024

ADVANCED JAVA PROGRAMMING (J2EE)

BCA/BSCIT SEM 5

UNIT 1

THE J2EE PLATFORM,

JDBC (JAVA DATABASE CONNECTIVITY)

1 1
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

INTRODUCTION TO J2EE

J2EE stands for Java 2 Platform, Enterprise Edition. J2EE is the standard platform for
developing applications in the enterprise and is designed for enterprise applications
that run on servers.
It is used to develop multi tier architecture-based applications. It is used to reduces
the cost and complexity of developing multi-tier services.
J2EE is a platform-independent environment used for configuring web-based
enterprise applications.
It contains several API, protocols, and run time environment for the development of
network-based web services.
It creates multi-tiered applications, large scale, reliable, and secure network
applications.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Advantages of J2EE:

Enterprise applications are reliable, secure, and scalable.

It increases the productivity of a programmer as it is easy to learn and use with the
help of the Java language.
It can operate on multiple platforms.
It is portable across different machines, vendors, and servers.

Services of J2EE:

J2EE supports pure HTML as well as Java Applets. It is based on Java Server Pages
and Servlet code to create applications.
EJB (Enterprise Java Beans) provides a separate layer for storing the logic.
JDBC (Java Database Connectivity) creates a connection between an application and
a database.

1 2
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

ENTERPRISE ARCHITECTURE STYLES

Two-Tier Architecture
The 2-tier Architecture is based on a client-server
machine.
In this type of architecture, the applications on client-
side interact directly with the database present at the
server-side.
This interaction between client and server uses
Application Program Interface like ODBC and JDBC.
ODBC − Open Database Connectivity
JDBC − Java Database Connectivity
When there are many users at client side to access the
database, this architecture gives a poor performance.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Three-Tier Architecture
Application which is divided into 3 tier namely Client tier, middle tier, and database
tier is known as Three Tier Architecture.
Three-Tier Architecture is an is a well-established software application design pattern
which will organizes the application in the three logical and physical computing tiers
as following:

1. Presentation Tier
It is the user interface and topmost tier in the architecture. Its purpose is to take
request from the client and displays information to the client. It communicates with
other tiers using a web browser as it gives output on the browser.
If we talk about Web-based tiers then these are developed using languages like-
HTML, CSS, JavaScript.

1 3
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

2. Application Tier

It is the middle tier of the architecture also known as the logic tier as the
information/request gathered through the presentation tier is processed in detail
here. It also interacts with the server that stores the data. It processes the client’s
request, formats, it and sends it back to the client. It is developed using languages
like- Python, Java, PHP, etc.

3. Data Tier

It is the last tier of the architecture also known as the Database Tier. It is used to
store the processed information so that it can be retrieved later on when required. It
consists of Database Servers like- Oracle, MySQL, DB2, etc.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

N-Tier Architecture
N-tier architecture is also called multi-tier architecture because the software is
engineered to have the processing, data management, and presentation functions
physically and logically separated.
multi-tier architecture is a cost-effective way to build an application that is flexible,
scalable, and responsive to the expectation of clients.

Multi-tier architecture is composed of:

Clients: It Refers to a program that requests service from a component.
Resource: A resource is anything a component needs to provide a service.
Component: It is part of a tier that consists of a collection of classes or a program that
performs a function to provide a service.
Containers: It is software that manages a component and provides system services to the
component. A container handles resource management, security, and other system-level
services from components that are associated with the container. Example: APIs.

1 4
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

J2EE ARCHITECTURE OR ENTERPRISE ARCHITECTURE

J2EE is a four-tier architecture that consists of:

• Client Tier/ Presentation Tier
• Web Tier
• Enterprise JavaBeans Tier/ Business Tier
• Enterprise Information Systems Tier

1. Client Tier/ Presentation Tier

It consists of programs that interact with the users. These components take a
request from the user to forward to software on the component that processes the
request and returns the result to the client program. Components under Client tier in
J2EE Specification:

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Applet Client- It is a component used by Web clients that operate within Applet
Container. It is a Java-enabled web browser.
Application Client –It is a Java application that operates within the application
container. It is the Java Runtime Environment (JRE).

2. Web Tier
It provides the Internet functionality to the J2EE application. The components on the
web tier use HTTP to receive requests and send responses to the client that resides
on the tier. Web-Tier Provides services to Client-tier using HTTP.
The intermediary activities are:
• Accepting info from other applications sent using POST, GET, and PUT using HTTP.
• Transmit data such as images and dynamic content.
Components of Web Tier:
Servlets
Java server pages (JSP)

1 5
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

3. Enterprise Java Beans Tier Implementation

It contains business logic for J2EE applications. In this tier two or more EJB reside. It is a
keystone for every J2EE Application.
EJB beans are contained in the EJB server- which is a distributed object server that
works on the EJB tier and manages transactions, and security, and ensures multi-
threading when EJB are accessed.
EJB tier provides scalability, life cycle management, and Back-Up to the J2EE
applications.

4. Enterprise Information System Tier

EIS tier links the J2EE application to resources and legacy systems that are available on
the corporate backbone network.
It is the tier where the J2EE applications directly or indirectly interface with a variety of
technologies including DBMS and Mainframes.
EIS-Tier provides connectivity to the resources that are not part of J2EE- which includes
resources such as Legacy Systems, DBMS, and systems provided by third-party.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

THE J2EE PLATFORM

The J2EE platform is a set of standard specifications that describe application

components, APIs, and the runtime containers and services of an application server.

J2EE Applications

J2EE applications are made up of components such as Java Server Pages (JSP),
Java servlets, and Enterprise JavaBeans (EJB) modules. These components enable
software developers to build large-scale, distributed applications. Developers
package J2EE applications in Java Archive (JAR) files (like zip files), which can be
distributed to production sites. Administrators install J2EE applications onto the
Application Server by deploying J2EE JAR files onto one or more server instances.

1 6
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Containers
Each server instance includes two containers: web and EJB. A container is a runtime
environment that provides services such as security and transaction management to
J2EE components. Web components, such as Java Server Pages and servlets, run
within the web container. Enterprise JavaBeans run within the EJB container.

J2EE Services
The J2EE platform provides services for applications, including:

- Naming - A naming and directory service binds objects to names. A J2EE

application can locate an object by looking up its Java Naming and Directory
Interface (JNDI) name.
- Security - The Java Authorization Contract for Containers (JACC) is a set of security
contracts defined for the J2EE containers. Based on the client’s identity, containers
can restrict access to the container’s resources and services.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

- Transaction management - A transaction is an unbreakable unit of work. For

example, transferring funds between bank accounts is a transaction. A transaction
management service ensures that a transaction is either completed or is rolled
back.
- Message Service - Applications hosted on separate systems can communicate with
each other by exchanging messages using the Java Message Service (JMS). JMS is
an integral part of the J2EE platform.

Web Services
Clients can access a J2EE application as a remote web service in addition to
accessing it through HTTP, and JMS. A J2EE application can also act as a client to
web services, which would be typical in network applications.
Web Services Description Language (WSDL) is an XML format that describes web
service interfaces. The Application Server distributes web services interface
descriptions using a registry that other applications can access through the Java API.

1 7
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Client Access
Clients can access J2EE applications in several ways. Browser clients access web
applications using hypertext transfer protocol (HTTP). For secure communication,
browsers use the HTTP secure (HTTPS) protocol that uses secure sockets layer
(SSL).
They can access applications and web services using HTTP/HTTPS, and JMS. They
can use JMS to send messages to and receive messages from applications and
message-driven beans.

External Systems and Resources

On the J2EE platform, an external system is called a resource. For example, a
database management system is a JDBC resource. Each resource is uniquely
identified and by its Java Naming and Directory Interface (JNDI) name.
Applications access external systems through the following APIs and components:

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Java Database Connectivity (JDBC)- A database management system (DBMS)

provides facilities for storing, organizing, and retrieving data.

Java Message Service - Messaging is a method of communication between software

components or applications.

J2EE Connectors - The J2EE Connector architecture enables integrating J2EE

applications and existing Enterprise Information Systems (EIS).

JavaMail - Through the JavaMail API, applications can connect to a Simple Mail
Transport Protocol (SMTP) server to send and receive email.

1 8
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

INTRODUCTION TO J2EE APIS (SERVLET, JSP, EJB, JMS, JAVAMAIL,

JSF, JNDI)

Servlets
Servlets are the Java programs that run on the Java-enabled web server or
application server. They are used to handle the request obtained from the web
server, process the request, produce the response, and then send a response back
to the web server.

JSP
JSP stands for Java Server Pages. It is a server-side technology which is used for
creating web applications. It is used to create dynamic web content. JSP consists of
both HTML tags and JSP tags. In this, JSP tags are used to insert JAVA code into
HTML pages. It is an advanced version of Servlet Technology.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

EJB
Enterprise Java Beans (EJB) is the Java APIs for standard manufacture of enterprise
software. EJB is a server-side software element that summarizes business logic of
an application. To run EJB application we need an application server.

JMS
JMS (Java Message Service) is an API that provides the facility to create, send and
read messages. Messaging is a technique to communicate applications or software
components. JMS is mainly used to send and receive message from one application
to another.

JavaMail
The JavaMail API provides a framework to build mail and messaging applications.
The JavaMail API provides a set of abstract classes defining objects that create a
mail system.

1 9
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

JSF
Java Server Faces (JSF) technology includes a set of APIs, which represent different
UI components. These APIs further help in handling events on the UI components
and validate user inputs through the UI components. JSF framework provides the
flexibility of creating simple as well as complex applications.

JNDI
The interface used by the Java programming language is by the name Java Naming
and Directory Interface (JNDI). It is an API that communicates with servers and uses
naming conventions to get files from databases or files.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

INTRODUCTION TO CONTAINERS

• The application server which controls and provides services through an interface
is called a container.
• Containers are interface between a component and client.
• It is a platform or place which stores and supports component.
• The J2EE container helps in the deployment and execution of J2EE components.
• In J2EE there are 5 containers types.

Three of these are server-side containers:

The server, which provides the J2EE platform.

An EJB container contains (enterprise java bean) EJB components.
A web container that manages servlets and JSP.

1 10
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

The other two containers are user-side:

An application container contains standalone graphical user interface(GUI) and
batch-type programs.
An applet container that contains a java plugin.

Application client container:

This container contains A host application

component, it runs on the user computer, and
it can interact with its other components.
Applications can also interact with data
resources.
This container maintains the execution of
application client components.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Web container:
This container contains A component that works on the server-side.
A web application executes within a web container of a web server which is also
known as servlet container.
Web container manages the execution of the JSP page and servlet components for
java enterprise edition(J2EE) applications.

JavaBeans container(enterprise java bean):

EJB container is a server-side component architecture.
An EJB container provides a run-time environment for beans within the application
server.
So, EJB container works as a bridge between the user-written business logic within
the bean and the rest of the application on the server-side.

1 11
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

TOMCAT AS A WEB CONTAINER

• Tomcat is an open-source web server and servlet. The Apache Software

Foundation has developed it.
• It is used widely for hosting Java-based applications on the web.
• It is built on Java technologies and implements the Java Servlet and JavaServer
Pages (JSP) specifications.
• Tomcat acts as a bridge between web servers and Java-based applications,
facilitating the execution of dynamic content and processing client requests.
• Tomcat follows a modular architecture comprising connectors, containers, and
the Catalina Servlet container. This architecture enables Tomcat to efficiently
handle client requests and generate responses.
• Connectors are accepting and processing requests that the clients sent to
Tomcat.
• These connectors can be HTTP or HTTPS.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Tomcat Installation
Installing Tomcat on Windows is easy. It is a step-by-step process. Go to the Apache
Tomcat website.

Once you go there, check for the recent Tomcat version and download it. When the
download is done, you need to set up essential environment variables.

After setting up the environment variables, you can start Tomcat. You can start
Tomcat in different ways. It can be run as a service or the startup scripts that are
given can be used. You can also start it in manual mode from the command line.

When Tomcat starts, it binds to a specific port, typically 8080 by default.

By launching a web browser and going to http://localhost:8080/, you can view the
Tomcat server through this port.

1 12
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Deploying a web application in Tomcat

Tomcat uses a concept called the Web Application Archive (WAR) file to package and
deploy web applications.

You first need to build an application as a WAR file to get it deployed. The WAR file
contains all the necessary components of your web application, including servlets,
JSP files, HTML files, and resource files.

To create war file, go inside the project directory of your project (outside the WEB-
INF), then write the following command in CMD:
jar -cvf projectname.war *

Once you have the WAR file, copy it to the "web apps" directory within the Tomcat
installation directory. Tomcat automatically deploys the application by unpacking the
WAR file and creating the necessary directory structure.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

JDBC ARCHITECTURE

JDBC stands for Java Database

Connectivity. JDBC is a Java API to
connect and execute the query with the
database.
It is a specification from Sun
Microsystems that provides a standard
abstraction(API or Protocol) for Java
applications to communicate with various
databases.
The classes and interfaces of JDBC allow
the application to send requests made by
Architecture of JDBC
users to the specified database.

1 13
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Application: It is a java applet or a servlet that communicates with a data source.

The JDBC API: The JDBC API allows Java programs to execute SQL statements and
retrieve results. Some of the important interfaces defined in JDBC API are as follows:
Driver interface , ResultSet Interface , RowSet Interface , PreparedStatement
interface, Connection inteface, and Classes defined in JDBC API are as follows:
DriverManager class, Types class, Blob(Binary Large Objects) class, Clob(Character
Large Objects) class.

DriverManager: It plays an important role in the JDBC architecture. It uses some

database-specific drivers to effectively connect enterprise applications to databases.

JDBC drivers: To communicate with a data source through JDBC, you need a JDBC
driver that intelligently communicates with the respective data source.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Types of JDBC Architecture(2-tier and 3-tier)

The JDBC architecture consists of two-tier and three-tier processing models to

access a database. They are as described below:

Two-tier model: A java application communicates directly to the data source. The
JDBC driver enables the communication between the application and the data
source. When a user sends a query to the data source, the answers for those
queries are sent back to the user in the form of results.

Three-tier model: In this, the user’s queries are sent to middle-tier services, from
which the commands are again sent to the data source. The results are sent back to
the middle tier, and from there to the user.

1 14
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

TYPES OF JDBC DRIVERS

JDBC drivers are the software components which implements interfaces in JDBC
APIs to enable java application to interact with the database.
JDBC Drivers uses JDBC APIs which was developed by Sun Microsystem, but now
this is a part of Oracle. There are 4 types of JDBC drivers.
Type-3 driver or Network Protocol driver
Type-1 driver or JDBC-ODBC bridge driver

Type-2 driver or Native-API driver Type-4 driver or Thin driver

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

1. JDBC-ODBC bridge driver – Type 1 driver

Type-1 driver or JDBC-ODBC bridge driver uses ODBC driver to connect to the
database. The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC
function calls. Type-1 driver is also called Universal driver because it can be used to
connect to any of the databases.

2. Native-API driver – Type 2 driver ( Partially Java driver)

The Native API driver uses the client -side libraries of the database. This driver
converts JDBC method calls into native calls of the database API. In order to interact
with different database, this driver needs their local API, that’s why data transfer is
much more secure as compared to type-1 driver. This driver is not fully written in
Java that is why it is also called Partially Java driver.

1 15
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

3. Network Protocol driver – Type 3 driver (fully Java driver)

The Network Protocol driver uses middleware (application server) that converts JDBC
calls directly or indirectly into the vendor-specific database protocol. Here all the
database connectivity drivers are present in a single server, hence no need of
individual client-side installation.

4. Thin driver – Type 4 driver (fully Java driver)

Type-4 driver is also called native protocol driver. This driver interact directly with
database. It does not require any native database library, that is why it is also known
as Thin Driver.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

INTRODUCTION TO MAJOR JDBC CLASSES AND INTERFACE

JDBC API is available in two packages java.sql, core API and javax.sql JDBC optional
packages. Following are the important classes and interfaces of JDBC.

Class/interface Description

DriverManager This class manages the JDBC drivers. You need to register
your drivers to this. It provides methods such as
registerDriver() and getConnection().

Driver This interface is the Base interface for every driver class i.e.
If you want to create a JDBC Driver of your own, you need to
implement this interface. If you load a Driver class
(implementation of this interface), it will create an instance
of itself and register with the driver manager.

1 16
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Class/interface Description

Statement This interface represents a static SQL statement. Using the

Statement object and its methods, you can execute an SQL
statement and get the results of it.
It provides methods such as execute(), executeBatch(),
executeUpdate() etc. To execute the statements.

PreparedStatement This represents a precompiled SQL statement. An SQL

statement is compiled and stored in a prepared statement,
and you can later execute this multiple times.

CallableStatement Using an object of this interface you can execute the stored
procedures. You can create a CallableStatement using the
prepareCall() method of the Connection interface.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Class/interface Description

Connection This interface represents the connection with a specific

database. This interface provides methods such as close(),
commit(), rollback(), createStatement(), prepareCall(),
prepareStatement() etc.

ResultSet This interface represents the database result set, a table

which is generated by executing statements.

ResultSetMetaData This interface is used to get the information about the

result set such as, number of columns, name of the
column, data type of the column, table name, etc.

1 17
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

CREATING SIMPLE JDBC APPLICATION

There are following six steps involved in building a JDBC application −

Import the packages − Requires that you include the packages containing the JDBC
classes needed for database programming. Most often, import java.sql.* will use.
Open a connection − Requires using the DriverManager.getConnection() method to
create a Connection object, which represents a physical connection with the
database.
Execute a query − Requires using an object of type Statement for building and
submitting an SQL statement to the database.
Extract data from result set − Requires that you use the appropriate ResultSet
method to retrieve the data from the result set.
Clean up the environment − Requires closing all database resources.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Simple JDBC Example to show data in browser from MySQL database

index.html
<h1> <a href="Data">Show data from Database</a></h1>

web.xml
<web-app>
<servlet>
<servlet-name>NewServlet</servlet-name>
<servlet-class>NewServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>NewServlet</servlet-name>
<url-pattern>/Data</url-pattern>
</servlet-mapping>
</web-app>

1 18
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

JDBCServlet.java

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class JDBCServlet extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

try {
Connection connection;
Class.forName("com.mysql.cj.jdbc.Driver");

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/dbname", "root", "");

Statement statement;
statement = connection.createStatement();
ResultSet resultSet;
resultSet = statement.executeQuery("select * from tablename");

int id;
String name;

response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
out.println("<h1>Student Information</h1>");
out.println("<table border='1'><tr><th>Id</th>");
out.println("<th>Name</th></tr>");

1 19
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

while (resultSet.next()) {
id = resultSet.getInt("id");
name = resultSet.getString("stuname");
out.println("<tr><td>"+id+"</td>");
out.println("<td>"+name+"</td></tr>");
}
out.println("</table>");
resultSet.close();
statement.close();
connection.close();
}
catch (IOException | ClassNotFoundException | SQLException exception) {
System.out.println(exception);
}
}
}

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

TYPES OF STATEMENT (STATEMENT INTERFACE, PREPARED

STATEMENT, CALLABLESTATEMENT)

The statement interface is used to create SQL basic statements in Java it provides
methods to execute queries with the database.
Once a connection is obtained, we can interact with the database.
There are different types of statements that are used in JDBC as follows:

Create Statement
Prepared Statement
Callable Statement

1. Create a Statement:
From the connection interface, you can create the object for this interface. It is
generally used for general–purpose access to databases.

1 20
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Syntax:
Statement statement = connection.createStatement();

Implementation:
Once the Statement object is created, there are three ways to execute it.

boolean execute(String SQL): If the ResultSet object is retrieved, then it returns true
else false is returned. Is used to execute SQL DDL statements or for dynamic SQL.

int executeUpdate(String SQL): Returns number of rows that are affected by the
execution of the statement, used when you need a number for INSERT, DELETE or
UPDATE statements.

ResultSet executeQuery(String SQL): Returns a ResultSet object. Used similarly as

SELECT is used in SQL.

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Example:
Statement statement = con.createStatement();
String sql = "select * from table_name";
ResultSet result = statement.executeQuery(sql);

2. Prepared Statement:

It represents a recompiled SQL statement, that can be executed many times. This
accepts parameterized SQL queries. In this, “?” is used instead of the parameter,
one can pass the parameter dynamically by using the methods of PREPARED
STATEMENT at run time.

Implementation: Once the PreparedStatement object is created, there are three

ways to execute it:

1 21
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

execute(): This returns a boolean value and executes a static SQL statement that is
present in the prepared statement object.

executeQuery(): Returns a ResultSet from the current prepared statement.

executeUpdate(): Returns the number of rows affected by the DML statements such
as INSERT, DELETE, and more that is present in the current Prepared Statement.

Example:

String query = "INSERT INTO people(name, age)VALUES(?, ?)";

PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1,“Ravi");
ptstmt.setInt(2,25);

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

3. Callable Statement:

Callable Statements are stored procedures which are a group of statements that we
compile in the database for some task, they are beneficial when we are dealing with
multiple tables with complex scenario & rather than sending multiple queries to the
database.
The Callable Statement interface provided by JDBC API helps in executing stored
procedures.

Syntax: To prepare a CallableStatement

CallableStatement cstmt = con.prepareCall("{call Procedure_name(?, ?}");

Implementation: Once the callable statement object is created

execute() is used to perform the execution of the statement.

1 22
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Example:

Statement s = con.createStatement();
CallableStatement cs = con.prepareCall("{call peopleinfo(?,?)}");
cs.setString(1, "Bob");
cs.setInt(2, 64);
cs.execute();
ResultSet result = s.executeQuery("select * from people");

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

CRUD OPERATION WITH JDBC

Creating, reading, updating, and deleting data in a database is a common task in
many applications, and JDBC (Java Database Connectivity) is a Java API that allows
you to connect to a database and perform these operations.

1. Connect to the database

The first step is to establish a connection to the database.

try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mydb", "username", "password");
System.out.println("Connection established.");
} catch(ClassNotFoundException | SQLException exception){
System.out.println(exception);
}

1 23
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

2. Create a new record

Once you have a connection to the database, you can use the connection object to
create a new record in the database.

try {
String sql = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)";
PreparedStatement statement = con.prepareStatement(sql);
statement.setString(1, "value1");
statement.setString(2, "value2");
statement.setInt(3, 123);
statement.executeUpdate();
System.out.println("Record created.");
} catch(ClassNotFoundException | SQLException exception){
System.out.println(exception);
}

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

3. Read a record
To read a record from the database, you will need to use an SQL SELECT statement.

try {
String sql = "SELECT column1, column2 FROM table_name WHERE id = ?";
PreparedStatement statement = con.prepareStatement(sql);
statement.setInt(1, 1);
ResultSet result = statement.executeQuery();
if (result.next()) {
String column1 = result.getString("column1");
String column2 = result.getString("column2");
System.out.println("Column 1: " + column1);
System.out.println("Column 2: " + column2);
}
} catch(ClassNotFoundException | SQLException exception){
System.out.println(exception);
}

1 24
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

4. Update a record
To update a record in the database, you will need to use an SQL UPDATE statement
and execute it using the connection object.

try {
String sql = "UPDATE table_name SET column1 = ?, column2 = ? WHERE id = ?";
PreparedStatement statement = con.prepareStatement(sql);
statement.setString(1, "new_value1");
statement.setString(2, "new_value2");
statement.setInt(4, 1);
statement.executeUpdate();
System.out.println("Record updated.");
} catch(ClassNotFoundException | SQLException exception){
System.out.println(exception);
}

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

5. Delete a record
To delete a record from the database, you will need to use an SQL DELETE
statement and execute it using the connection object.

try {
String sql = "DELETE FROM table_name WHERE id = ?";
PreparedStatement statement = con.prepareStatement(sql);
statement.setInt(1, 1);
statement.executeUpdate();
System.out.println("Record deleted.");
} catch(ClassNotFoundException | SQLException exception){
System.out.println(exception);
}

1 25
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

CRUD Example:

Index.html

<h1>
<a href="ShowClass"><input type="button" value="Show"></a>
<a href="InsertClass"><input type="button" value="Insert"/></a>
<a href="UpdateClass"><input type="button" value="Update"/></a>
<a href="DeleteClass"><input type="button" value="Delete"/></a>
</h1>

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

web.xml
<web-app>
<servlet>
<servlet-name>ShowClass</servlet-name>
<servlet-class>ShowClass</servlet-class>
</servlet>
<servlet>
<servlet-name>InsertClass</servlet-name>
<servlet-class>InsertClass</servlet-class>
</servlet>
<servlet>
<servlet-name>UpdateClass</servlet-name>
<servlet-class>UpdateClass</servlet-class>
</servlet>
<servlet>
<servlet-name>DeleteClass</servlet-name>
<servlet-class>DeleteClass</servlet-class>
</servlet>

1 26
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

<servlet-mapping>
<servlet-name>ShowClass</servlet-name>
<url-pattern>/ShowClass</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>InsertClass</servlet-name>
<url-pattern>/InsertClass</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>UpdateClass</servlet-name>
<url-pattern>/UpdateClass</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>DeleteClass</servlet-name>
<url-pattern>/DeleteClass</url-pattern>
</servlet-mapping>
</web-app>

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

InsertClass.java

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class InsertClass extends HttpServlet {

@Override
protected void doGet(HttpServletRequest request,HttpServletResponse response)throws
ServletException, IOException {
PrintWriter p = response.getWriter();
try{
Connection con;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo1","root","");

1 27
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

String sql = "insert into user(uname)values(?)";

PreparedStatement pst;
pst = con.prepareStatement(sql);
pst.setString(1, "Neeta");

int count = pst.executeUpdate();

if(count >0){
p.println("Data inserted");
}
}
catch(ClassNotFoundException | SQLException exception){
System.out.println(exception);
}
}
}

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

ShowClass.java

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class ShowClass extends HttpServlet {

@Override
protected void doGet(HttpServletRequest request,HttpServletResponse response)throws
ServletException, IOException {
PrintWriter p = response.getWriter();
try{
Connection con;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo1","root","");

1 28
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

Statement stmt;
ResultSet rs;
stmt = con.createStatement();
rs = stmt.executeQuery("select * from user");
int id;
String name;
while(rs.next()){
id = rs.getInt("id");
name = rs.getString("uname");
p.println("Id: "+id+ "Name: "+name);
}
}
catch(ClassNotFoundException | SQLException exception){
System.out.println(exception);
}
}
}

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

UpdateClass.java

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class UpdateClass extends HttpServlet {

@Override
protected void doGet(HttpServletRequest request,HttpServletResponse response)throws
ServletException, IOException {
PrintWriter p = response.getWriter();
try{
Connection con;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo1","root","");

1 29
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

String sql = "update user set uname=? where id=?";

PreparedStatement pst;

pst = con.prepareStatement(sql);
pst.setString(1, "Hardik");
pst.setInt(2, 4);

int count = pst.executeUpdate();

if(count >0){
p.println("Data updated");
}
}
catch(ClassNotFoundException | SQLException exception){
System.out.println(exception);
}
}
}

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

DeleteClass.java

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public class DeleteClass extends HttpServlet {

@Override
protected void doGet(HttpServletRequest request,HttpServletResponse response)throws
ServletException, IOException {
PrintWriter p = response.getWriter();
try{
Connection con;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo1","root","");

1 30
 31-07-2024

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

String sql = "delete from user where id=?";

PreparedStatement pst;

pst = con.prepareStatement(sql);
pst.setInt(1, 4);

int count = pst.executeUpdate();

if(count >0){
p.println("Data deleted");
}
}
catch(ClassNotFoundException | SQLException exception){
System.out.println(exception);
}
}
}

UNIT 1 THE J2EE PLATFORM, JDBC (JAVA DATABASE CONNECTIVITY)

END OF UNIT 1

1 31