JDBC WT

WEB TECHNOLOGIES
JDBC
JDBC Driver is a software component that enables java applications to interact with the
National Institute of Science & Technology
database.
There are 4 types of JDBC drivers:
1. JDBC-ODBC bridge driver

2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)
WEB TECHNOLOGIES
JDBC
Type 1 − JDBC-ODBC Bridge Driver
• In a Type 1 driver, a JDBC bridge is used to access ODBC drivers installed on each client
machine.
• Using ODBC requires configuring on your system a Data Source Name (DSN) that
represents the target database.
• When Java first came out, this was a useful driver because most databases only
supported ODBC access but now this type of driver is recommended only for
experimental use or when no other alternative is available.
JDBC
WEB TECHNOLOGIES
WEB TECHNOLOGIES
JDBC
Type 1 − JDBC-ODBC Bridge Driver
• The JDBC-ODBC bridge driver uses the ODBC driver to connect to the database.
• The JDBC-ODBC bridge driver converts the JDBC method calls to ODBC function calls.
• This is now discouraged because of the thin driver.
Advantages:
• Easy to use.
• Can be easily connected to any database.
Disadvantages:
• Performance degraded because the JDBC method call is converted into ODBC function
calls.
• The ODBC driver needs to be installed on the client machine.
WEB TECHNOLOGIES
JDBC
Type 2 − JDBC-Native API
• In a Type 2 driver, JDBC API calls are converted into native C/C++ API calls, which are
unique to the database.
• These drivers are typically provided by the database vendors and used in the same
manner as the JDBC-ODBC Bridge.
• The vendor-specific driver must be installed on each client machine.
• If we change the Database, we have to change the native API, as it is specific to a

database and they are mostly obsolete now, but you may realize some speed increase
with a Type 2 driver because it eliminates ODBC's overhead.
JDBC
WEB TECHNOLOGIES
WEB TECHNOLOGIES
JDBC
Type 2 − JDBC-Native API
• The Native API Driver uses the client-side libraries of the database.
• The driver converts JDBC method calls to native calls of the database API.
• It is not written entirely in java.
Advantages:
• Performance upgraded than JDBC-ODBC bridge driver.
Disadvantages:
• The native driver needs to be installed on each client machine.
• The vendor client library needs to be installed on the client machine.
WEB TECHNOLOGIES
JDBC
Type 3 − Network Protocol driver
• In a Type 3 driver, a three-tier approach is used to access databases.
• The JDBC clients use standard network sockets to communicate with a middleware
application server.
•
• The socket information is then translated by the middleware application server into the
call format required by the DBMS, and forwarded to the database server.
• This kind of driver is extremely flexible since it requires no code installed on the client
and a single driver can actually provide access to multiple databases.
JDBC
WEB TECHNOLOGIES
WEB TECHNOLOGIES
JDBC
Type 3 − Network Protocol driver
The Network Protocol driver uses middleware (application server) that converts JDBC calls
directly or indirectly into the vendor-specific database protocol. It is fully written in java.
Advantage:
• No client-side library is required because of the application server can perform many
tasks like auditing, load balancing, logging, etc.
Disadvantages:
• Network support is required on the client machine.
• Requires database-specific coding to be done in the middle tier.
• Maintenance of the Network Protocol driver becomes costly because it requires
database-specific coding to be done in the middle tier.
WEB TECHNOLOGIES
JDBC
Type 4 − Thin driver
• In a Type 4 driver, a pure Java-based driver communicates directly with the vendor's
database through a socket connection.
• This is the highest-performance driver available for the database and is usually
provided
by the vendor itself.
• This kind of driver is extremely flexible, you don't need to install special software on the
client or server. Further, these drivers can be downloaded dynamically.
JDBC
WEB TECHNOLOGIES
WEB TECHNOLOGIES
JDBC
Type 4 − Thin driver
The thin driver converts JDBC calls directly into the vendor-specific database protocol.
That is why it is known as a thin driver. It is fully written in Java language.
Advantage:
• Better performance than all other drivers.
• No software is required at the client side or server side.
Disadvantage:
• Drivers depend on the Database.
WEB TECHNOLOGIES
JDBC
Create Table
To create the Employees table in TEST database, use the following steps −
Step 1
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>
Step 2
Login to the database as follows
C:\Program Files\MySQL\bin>mysql -u root -p

Enter password: ********
mysql>
WEB TECHNOLOGIES
JDBC
Step 3
Create the table Employee in TEST database as follows −
mysql> use TEST;

mysql> create table Employees (
id int not null,
age int not null,
first varchar (255),
last varchar (255)
);
Query OK, 0 rows affected (0.08
sec)
mysql>
WEB TECHNOLOGIES
JDBC
Create Data Records
create a few records in the Employee table as follows −
mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');

Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');

mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');

mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');

mysql>
WEB TECHNOLOGIES
JDBC
Accessing a Database
Here is an example that shows how to access the TEST database using Servlet.
// Loading required libraries

import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
public class
DatabaseAccess extends
HttpServlet{
public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {
WEB TECHNOLOGIES
JDBC
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

static final String DB_URL="jdbc:mysql://localhost/TEST";
// Database credentials
static final String USER = "root";
static final String PASS = "password";
// Set response content type

response.setContentType("text/html");
PrintWriter out = response.getWriter();
String title = "Database Result";
String docType =
"<!doctype html public \"-//w3c//dtd
html 4.0 " + "transitional//en\">\n";
WEB TECHNOLOGIES
JDBC
out.println(docType +
"<html>\n" +
"<head><title>" + title + "</title></head>\n" +
"<body bgcolor = \"#f0f0f0\">\n" +
"<h1 align = \"center\">" + title + "</h1>\n");
try {
// Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// Open a connection
Connection conn =
DriverManager.getConnection(DB_URL,
USER, PASS);
// Execute SQL query

Statement stmt =
conn.createStatement(); String sql;
sql = "SELECT id, first, last, age FROM
Employees"; ResultSet rs =
WEB TECHNOLOGIES
JDBC
// Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first =
rs.getString("first");
String last =
rs.getString("last");
//Display values
out.println("ID: " + id +
"<br>");
out.println(", Age: " + age + "<br>");
out.println(", First: " + first + "<br>");
out.println(", Last: " + last + "<br>");
}
out.println("</body></html>");
WEB TECHNOLOGIES
JDBC
// Clean-up environment
rs.close();
stmt.close();
conn.close();
} catch(SQLException se) {
//Handle errors for JDBC
se.printStackTrace();
} catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
}
finally {
//
fi
na
lly
bl
oc
WEB TECHNOLOGIES
JDBC
} catch(SQLException se2) {
} // nothing we can do
try {
if(conn!=null)
conn.close();
}
catch(SQLExce
ption se) {
se.printStack
Trace();
} //end finally
try
} //end try
}
}
WEB TECHNOLOGIES
JDBC
web.xml
....
<servlet>
<servlet-name>DatabaseAccess</servlet-name>
<servlet-class>DatabaseAccess</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DatabaseAccess</servlet-name>
<url-pattern>/DatabaseAccess</url-pattern>
</servlet-mapping>
....
WEB TECHNOLOGIES
JDBC
Now call this servlet using URL http://localhost:8080/DatabaseAccess which would display the following
response −
Database Result
ID: 100, Age: 18, First: Zara, Last: Ali

ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
WEB TECHNOLOGIES
JDBC in JSP
Create Table
To create the Employees table in TEST database, use the following steps −
Step 1
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>
Step 2
Login to the database as follows
C:\Program Files\MySQL\bin>mysql -u root -p

Enter password: ********
mysql>
WEB TECHNOLOGIES
JDBC
Step 3
Create the table Employee in TEST database as follows −
mysql> use TEST;

mysql> create table Employees (
id int not null,
age int not null,
first varchar (255),
last varchar (255)
);
Query OK, 0 rows affected (0.08
sec)
mysql>
WEB TECHNOLOGIES
JDBC
Create Data Records
create a few records in the Employee table as follows −
mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');

mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');

mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');

mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');

mysql>
WEB TECHNOLOGIES
JDBC
SELECT Operation
The following example shows how we can execute the SQL SELECT statement using JTSL in JSP programming
<%@ page import = "java.io.*,java.util.*,java.sql.*"%>

<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
<html>
<head>
<title>SELECT Operation</title>
</head>
<body>
<sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
url = "jdbc:mysql://localhost/TEST"
user = "root" password = "pass123"/>
WEB TECHNOLOGIES
JDBC
<sql:query dataSource = "${snapshot}" var = "result">
SELECT * from Employees;

</sql:query>
<table border = "1" width = "100%">

<tr>
<th>Emp ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
<c:forEach var = "row" items = "${result.rows}">

<tr>
WEB TECHNOLOGIES
JDBC
<td><c:out value = "${row.id}"/></td>
<td><c:out value = "${row.first}"/></td>

<td><c:out value = "${row.last}"/></td>
<td><c:out value = "${row.age}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>
WEB TECHNOLOGIES
JDBC
Access the above JSP, the following result will be displayed −
Emp ID First Name Last Name Age

100 Zara Ali 18
101 Mahnaz Fatma 25
102 Zaid Khan 30
103 Sumit Mittal 28
WEB TECHNOLOGIES
JDBC
INSERT Operation

<html>
<head>
<title>JINSERT Operation</title>
</head>
<body>
WEB TECHNOLOGIES
JDBC
<sql:update dataSource = "${snapshot}" var = "result">
INSERT INTO Employees VALUES (104, 2, 'Nuha', 'Ali');

</sql:update>
</sql:query>
<tr>
<th>Emp ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>

<tr>
WEB TECHNOLOGIES
JDBC

</tr>
</c:forEach>
</table>
</body>
</html>
WEB TECHNOLOGIES
JDBC

100 Zara Ali 18
101 Mahnaz Fatma 25
102 Zaid Khan 30
103 Sumit Mittal 28
104 Nuha Ali 2
WEB TECHNOLOGIES
JDBC
DELETE Operation

<html>
<head>
<title>DELETE Operation</title>
</head>
<body>
WEB TECHNOLOGIES
JDBC
<c:set var = "empId" value = "103"/>
<sql:update dataSource = "${snapshot}" var = "count">

DELETE FROM Employees WHERE Id = ?
<sql:param value = "${empId}" />
</sql:update>

</sql:query>
<tr>
<th>Emp ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
WEB TECHNOLOGIES
JDBC
<tr>
</tr>
</c:forEach>
</table>
</body>
</html>
WEB TECHNOLOGIES
JDBC

100 Zara Ali 18
101 Mahnaz Fatma 25
102 Zaid Khan 30
WEB TECHNOLOGIES
JDBC
UPDATE Operation

<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
<html>
<head>
<title>DELETE Operation</title>
</head>
<body>
WEB TECHNOLOGIES
JDBC
<c:set var = "empId" value = "102"/>
<sql:update dataSource = "${snapshot}" var = "count">

UPDATE Employees SET WHERE last = 'Ali'
<sql:param value = "${empId}" />
</sql:update>
</sql:query>

<tr>
<th>Emp ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
WEB TECHNOLOGIES
JDBC
<tr>
</tr>
</c:forEach>
</table>
</body>
</html>
WEB TECHNOLOGIES
JDBC

100 Zara Ali 18
101 Mahnaz Fatma 25
102 Zaid Ali 30

JDBC WT

Uploaded by

Copyright:

Available Formats

JDBC WT

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

JDBC WT

Uploaded by

Copyright:

Available Formats

WEB TECHNOLOGIES

There are 4 types of JDBC drivers:

1. JDBC-ODBC bridge driver

• The vendor-specific driver must be installed on each client machine.

• If we change the Database, we have to change the native API, as it is specific to a

• In a Type 3 driver, a three-tier approach is used to access databases.

C:\Program Files\MySQL\bin>mysql -u root -p

Create the table Employee in TEST database as follows −

mysql> use TEST;

create a few records in the Employee table as follows −

mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');

mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');

mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');

mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');

// Loading required libraries

public void doGet(HttpServletRequest request, HttpServletResponse response)

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

// Set response content type

// Execute SQL query

ID: 100, Age: 18, First: Zara, Last: Ali

C:\Program Files\MySQL\bin>mysql -u root -p

Create the table Employee in TEST database as follows −

mysql> use TEST;

create a few records in the Employee table as follows −

mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');

mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');

mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');

mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');

<%@ page import = "java.io.*,java.util.*,java.sql.*"%>

SELECT * from Employees;

<table border = "1" width = "100%">

<c:forEach var = "row" items = "${result.rows}">

<td><c:out value = "${row.first}"/></td>

Emp ID First Name Last Name Age

<%@ page import = "java.io.*,java.util.*,java.sql.*"%>

INSERT INTO Employees VALUES (104, 2, 'Nuha', 'Ali');

<c:forEach var = "row" items = "${result.rows}">

<td><c:out value = "${row.first}"/></td>

Emp ID First Name Last Name Age

<%@ page import = "java.io.*,java.util.*,java.sql.*"%>

<sql:update dataSource = "${snapshot}" var = "count">

<sql:query dataSource = "${snapshot}" var = "result">

Emp ID First Name Last Name Age

<%@ page import = "java.io.*,java.util.*,java.sql.*"%>

<sql:update dataSource = "${snapshot}" var = "count">

<table border = "1" width = "100%">

Emp ID First Name Last Name Age

You might also like

<%@ page import = "java.io.,java.util.,java.sql.*"%>

<%@ page import = "java.io.,java.util.,java.sql.*"%>

<%@ page import = "java.io.,java.util.,java.sql.*"%>

<%@ page import = "java.io.,java.util.,java.sql.*"%>