Connect JSP With Mysql

Connect JSP with mysql

In this example we will show you how to connect to MySQL database from your JSP
code. First, you need to create database and then write jsp code to connect jsp to
database.
1. Create a database:
First create a database named usermaster in mysql. Before running the jsp code you
need to paste mySqlConnector.jar in lib directory of jdk.
mysql> create database

usermaster;
(This query create database in my
sql command prompt)
2. Connect JSP with mysql:
Here is the jsp code.
ConnectJspToMysql.jsp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01

Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*" %>

<%@ page import="java.io.*" %>�
<html>
<head>
<title>Connection with mysql database</title>
</head>
<body>
<h1>Connection status </h1>
<%
try {
/* Create string of connection url within specified format
with machine name,
port number and database name. Here machine name id
localhost and
database name is usermaster. */
String connectionURL =
"jdbc:mysql://localhost:3306/usermaster";
// declare a connection by using Connection interface
Connection connection = null;
//�Load JBBC driver "com.mysql.jdbc.Driver"

Class.forName("com.mysql.jdbc.Driver").newInstance();
/* Create a connection by using getConnection() method

that takes parameters of
string type connection url, user name and password to
connect to database. */
connection =
DriverManager.getConnection(connectionURL, "root",
"root");
// check weather connection is established or not by

isClosed() method�
if(!connection.isClosed())
%>
<font size="+3" color="green"></b>
<%
out.println("Successfully connected to " + "MySQL server
using TCP/IP...");
connection.close();
}
catch(Exception ex){
%>
</font>
<font size="+3" color="red"></b>
<%
out.println("Unable to connect to database.");
}
%>
</font>
</body>
</html>
Output:
Output of the program when connection is established with specified mysql database :
Output of the program when unable to connect to specified MySQL database :
Navigation in a database table through

jsp

This is detailed jsp code that shows how to navigate in database. This code shows one
by one record of student from database on clicking a button labeled 'next record', so
on each click of button application fetches next record from database, this is the
navigation in database.
Create a database: Before run this jsp code first create a database named 'student'
through the sql query given below in mysql command prompt :-
mysql> create
database student;
Then create a table named 'stu_info' in the same database through following sql
query :-
create table stu_info (

ID int not null
auto_increment,
Name
varchar(20),
Address
varchar(20),
Phone int(15),
primary key(ID)
);
Create a new directory named "user" in the tomcat-6.0.16/webapps and WEB-INF

directory in same directory. Before running this java code you need to paste a .jar file
named mysql connector.jar in the Tomcat-6.0.16/webapps/user/WEB-INF/lib.

Transitional//EN"
<%@ page import="java.io.*" %>
<HTML>
<HEAD>
<TITLE>Navigating in a Database Table
</TITLE>
</HEAD>
<BODY bgcolor="#ffffcc">
<font size="+3" color="green"><br>Welcome in
roseindia !</font>
<% int current = 0;
// declare a connection by using
Connection interface
/* Create string of connection url
within specified
format with machine name, port
number and database
name. Here machine name id
localhost and database
name is student. */
"jdbc:mysql://localhost:3306/student";
/*declare a resultSet that works
as a table
resulted by execute a specified
sql query. */
ResultSet rs = null;
%>
<FORM NAME="form1"
ACTION="ConnectJspToMysql.jsp" METHOD="get"><%
if (request.getParameter("hidden")
!= null) {
current =
Integer.parseInt(request.getParameter("hidden"
));
}
// Declare statement.
Statement statement = null;
try {
// Load JDBC driver
"com.mysql.jdbc.Driver".
Class.forName("com.mysql.jdbc.Driver").newInst
ance();
/* Create a connection by
using getConnection()
method that takes parameters
of string type connection
url, user name and password to
connection =
DriverManager.getConnection(connectionURL,
"root", "root");
/* createStatement() is used
for create statement
object that is used for
sending sql statements to
the specified database. */
statement =
connection.createStatement();
// executeQuery() method
execute specified sql query.
rs =
statement.executeQuery("select * from
stu_info");
for (int i = 0; i < current;
i++) {
rs.next();
}
if (!rs.next()) {
%>
<FONT size="+2" color="red"></b>
<%
out.println("Sorry ! found some
problems with database.");
} else {
%>
<TABLE style="background-color: #ECE5B6;"
WIDTH="30%" >
<TR><TH width="50%">ID</TH><TD
width="50%"> <%= rs.getInt(1)%>
</TD></tr>
<TR><TH>Name</TH><TD> <%=
rs.getString(2)%> </TD></tr>
<TR><TH>City</TH><TD> <%=
rs.getString(3)%> </TD></tr>
<TR><TH>Phone</TH><TD> <%= rs.getInt(4)%>
</TD></tr>
</TR>
</TABLE>
<BR>
<INPUT TYPE="hidden" NAME="hidden" VALUE="<
%=current + 1%>">
<INPUT TYPE="submit" VALUE="next record">
</FORM>
<%
}
}
catch (Exception ex) {
%>
<%
out.println("Unable to connect
to database.");
} finally {
// close all the connections.
rs.close();
statement.close();
connection.close();
}
%>
</FONT>
</FORM>
</body>
</html>
Save this code as a .jsp file named "ConnectJspToMysql.jsp" in the directory

Tomcat-6.0.16/webapps/user/ and you can run this jsp page with following url in
address bar of the browser "http://localhost:8080/user/ConnectJspToMysql.jsp"
When click on button, application control goes to database and check for the next
record. If next record is found in table, show in browser else show a error message.
Error message page:
Update Database Table using JDBC in

JSP

This example shows how to update the existing record of mysql table using jdbc
connectivity in the jsp page. In this example we have created two jsp pages
update.jsp and updatingDatabase.jsp. In the update.jsp page, we are using a Text
box where user can give his/her name and submit the page. After submitting the
page, updatingDatabase.jsp will be called and the sql query ("update servlet set
name = ? where id = ?") is executed which will modify the table record.
<%@page language="java" session="true"
contentType="text/html;charset=ISO-8859-1" %>
<font color="blue">Please Enter Your Name </font><br><br>
<form name="frm" method="post" action="updatingDatabase.jsp">
<table border = "0">
<tr align="left" valign="top">
<td>Name:</td>
<td><input type="text" name ="name" /></td>
</tr>
<tr align="left" valign="top">
<td></td>
<td><input type="submit" name="submit" value="submit"/></td>
</tr>
</table>
</form>
Type the url: http://localhost:8080/ServletExample/jsp/update.jsp on your

browser. Following output will be displayed:
updatingDatabase.jsp
<%@ page language = "java" contentType =
"text/html; charset = ISO-8859-1"
import = "java.io.*"
import = "java.sql.*"
import = "java.util.*"
import = "javax.sql.*"
import = "java.sql.ResultSet"
import = "java.sql.Statement"
import = "java.sql.Connection"
import = "java.sql.DriverManager"
import = "java.sql.SQLException"
%>
<%
Connection con = null;
PreparedStatement ps = null;
Statement stmt = null;
String name = request.getParameter("name");
Integer id = 5;
%>
<html>
<head>
<title>Updating Database</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver");
con =DriverManager.getConnection
("jdbc:mysql://192.168.10.59:3306/example",
"root", "root");
ps = con.prepareStatement("update servlet set
name = ? where id = ?");
ps.setInt(2, id);
ps.setString(1, name);
ps.executeUpdate();
%>
Database successfully Updated!<br>
<%
if(ps.executeUpdate()>=1){
stmt=con.createStatement();
rs = stmt.executeQuery("SELECT * FROM servlet");
while(rs.next()){
%>
<%=rs.getObject(1).toString()%>
<%=("\t\t\t")%>
<%=rs.getObject(2).toString()%>
<%=("<br>")%>
<%
}
}
} catch (IOException e) {
throw new IOException("Can not display records.", e);
} catch (ClassNotFoundException e) {
throw new SQLException("JDBC Driver not found.", e);
} finally {
try {
if(stmt != null){
stmt.close();
stmt = null;
}
if(ps != null) {
ps.close();
ps = null;
}
if(con != null) {
con.close();
con = null;
}
} catch (SQLException e) {}
}
%>
</body>
</html>
After submitting the name, it will be updated in the database and the records will be
displayed on your browser.
Create dynamic page through JSP

This is detailed jsp code that shows how to create dynamic page. This code shows one
by one record of student from database on click a button named 'next record'. On
each click of button, application fetch next record from database.
Create a database : Before running this jsp code, first create a database named
'student' through the sql query given below in mysql command prompt :-
mysql> create database

student;
Then create a table named 'stu_info' in the same database through following sql query
:-
create table stu_info (

ID int not null
auto_increment,
Name
varchar(20),
Address
varchar(20),
Phone
varchar(15),
primary key(ID)
);
Create your application directory named "user" in the Tomcat server. Before running
this java code you need mysql connector jar in the Tomcat-
6.0.16/webapps/user/WEB-INF/lib.
ConnectJspToMysql.jsp:
This file provides full code how to connect with database and showing the next record
of database in JSP.

Transitional//EN"
<HTML>
<HEAD>
<TITLE>Navigating in a Database Table </TITLE>
</HEAD>
<BODY bgcolor="#ffffcc">
<font size="+3" color="green"><br>Welcome in
www.roseindia.net !</font>
<br>
<font size="+1" color="#6E6E6E"><br>This is dynamic
page that shows data<br>
from database as per user choice.</font>
<% int current = 0;
// declare a connection by using
Connection interface
/* Create string of connection url within
specified format with machine
name, port number and database name.
Here machine name id localhost
and database name is student. */
"jdbc:mysql://localhost:3306/student";
/*declare a resultSet that works as a
table resulted by execute a specified
sql query. */
%>
<FORM NAME="form1" ACTION="ConnectJspToMysql.jsp"
METHOD="get"><%
if (request.getParameter("hidden") !=
null) {
current =
Integer.parseInt(request.getParameter("hidden"));
}
// Declare statement.
Statement statement = null;
try {
// Load JDBC driver
"com.mysql.jdbc.Driver"
Class.forName("com.mysql.jdbc.Driver").newInstance();
/* Create a connection by using
getConnection() method that takes
parameters of string type connection
url, user name and password to
connection =
DriverManager.getConnection(connectionURL, "root",
"root");
/* createStatement() is used for
create statement object that is
used for sending sql statements to the
specified database. */
statement =
// executeQuery() method execute
specified sql query.
rs = statement.executeQuery("select *
from stu_info");
for (int i = 0; i < current; i++) {
rs.next();
}
if (!rs.next()) {
%>
<%
out.println("Sorry ! found some problems with
database.");
} else {
%>
<TABLE style="background-color: #ECE5B6;" WIDTH="30%"
>
<TR><TH width="50%">ID</TH><TD width="50%"> <%=

rs.getInt(1)%> </TD></tr>
<TR><TH>Name</TH><TD> <%= rs.getString(2)%>
</TD></tr>
<TR><TH>City</TH><TD> <%= rs.getString(3)%>
</TD></tr>
<TR><TH>Phone</TH><TD> <%= rs.getString(4)%>
</TD></tr>
</TR>
</TABLE>
<BR>
<INPUT TYPE="hidden" NAME="hidden" VALUE="<%=current
+ 1%>">
<INPUT TYPE="submit" VALUE="next record">
</FORM>
<%
}
}
catch (Exception ex) {
%>
<%
out.println("Unable to connect to
database.");
} finally {
rs.close();
statement.close();
connection.close();
}
%>
</FONT>
</FORM>
</body>
</html>
Save this code as a .jsp file named "ConnectJspToMysql.jsp" in the directory

Tomcat-6.0.16/webapps/user/ and run this jsp page with following url in address bar
of the browser "http://localhost:8080/user/ConnectJspToMysql.jsp".
When click on button, application control goes to database and check for the next
record. If next record is found in table, it is shown in browser else an error message.
Error message page:
Access all the fields from table through

JSP

This is detailed java program to connect java application with MySql database and
execute query to display data from the specified table. Before running this java code
you need mysql-connector-java-3.1.6-bin.jar file and set class path to this file.
This is first jsp page that has a link 'show data from table', which displays all the
data from table when clicked. This is the code of first welcome jsp page.
welcome_to_database_query.jsp

Transitional//EN"

<html>
<head>
<title>display data from the table
using jsp</title>
</head>
<body>
<TABLE style="background-color:
#ffffcc;">
<TR>
<TD align="center">
<h2>To display all the data from the table
click here...</h2></TD>
</TR>
<TR>
<TD align="center"><A
HREF="ConnectJspToMysql.jsp">
<font size="4"
color="blue">show data from
table</font></A></TD>
</TR>
</TABLE>
</body>
</html>
Save this code with the name "welcome_to_database_query.jsp" in the application

directory in Tomcat. Start tomcat server and type url
'http://localhost:8080/user/welcome_to_database_query.jsp' in address bar of browser
and run.
This page has a link, to show data from the database click on the link that calls
another .jsp file named ConnectJspToMysql.jsp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD
HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.d
td">

<html>
<head>
<title>display data from the
table using jsp</title>
</head>
<body>
<h2>Data from the table 'stu_info'
of database 'student'</h2>
<%
try {
/* Create string of
connection url within specified
format with machine name,
port number and database name.
Here machine name id
localhost and database name is
student. */
"jdbc:mysql://localhost:3306/studen
t";
// declare a connection
by using Connection interface
Connection connection =
null;
// declare object of
Statement interface that is used
for
executing sql statements.
Statement statement =
null;
// declare a resultset
that uses as a table for output
data
from tha table.
// Load JBBC driver

"com.mysql.jdbc.Driver".
Class.forName("com.mysql.jdbc.Drive
r").newInstance();
/* Create a connection by
using getConnection()
method that takes
parameters of string type
connection url, user name
and password to connect to
database. */
connection =
DriverManager.getConnection(connect
ionURL, "root", "root");
/* createStatement() is
used for create statement
object that is used for sending sql
statements to the specified
database. */
statement =
// sql query to retrieve

values from the secified table.
String QueryString =
"SELECT * from stu_info";
rs =
statement.executeQuery(QueryString)
;
%>
<TABLE cellpadding="15" border="1"
style="background-color: #ffffcc;">
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getInt(1)%></TD>
<TD><
%=rs.getString(2)%></TD>
<TD><
<TD><
</TR>
<% } %>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
%>
</font>
<font size="+3"
color="red"></b>
<%
out.println("Unable
to connect to database.");
}
%>
</TABLE><TABLE>
<TR>
<TD><FORM
ACTION="welcome_to_database_query.j
sp" method="get" >
<button
type="submit"><--
back</button></TD>
</TR>
</TABLE>
</font>
</body>
</html>
Save this code with name ConnectJspToMysql.jsp in the same dirctory of

welcome_to_database_query.jsp. Click on the link given in the first jsp page,that calls
this jsp page and show all data from the table.
Click on the <--back button to go to first page of the application.

Connect JSP With Mysql

Uploaded by

Copyright:

Available Formats

Connect JSP With Mysql

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

Connect JSP With Mysql

Uploaded by

Copyright:

Available Formats

Connect JSP with mysql

mysql> create database

2. Connect JSP with mysql:

Here is the jsp code.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01

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

//�Load JBBC driver "com.mysql.jdbc.Driver"

/* Create a connection by using getConnection() method

// check weather connection is established or not by

Navigation in a database table through

create table stu_info (

Create a new directory named "user" in the tomcat-6.0.16/webapps and WEB-INF

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01

Save this code as a .jsp file named "ConnectJspToMysql.jsp" in the directory

Error message page:

Update Database Table using JDBC in

Type the url: http://localhost:8080/ServletExample/jsp/update.jsp on your

Create dynamic page through JSP

mysql> create database

create table stu_info (

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01

<TR><TH width="50%">ID</TH><TD width="50%"> <%=

Save this code as a .jsp file named "ConnectJspToMysql.jsp" in the directory

Access all the fields from table through

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01

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

Save this code with the name "welcome_to_database_query.jsp" in the application

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

// Load JBBC driver

// sql query to retrieve

Save this code with name ConnectJspToMysql.jsp in the same dirctory of

Click on the <--back button to go to first page of the application.

You might also like