3.JDBC_WebApplication
Create Table studentvisa
create table studentvisa
(
passportNo varchar(100),
holderName varchar(100),
nationality varchar(100),
city varchar(100),
mobileNo varchar(100)
)
Insert data into table :
insert into studentvisa values('p000123', 'ram krishn mishra', 'Índian', 'Bhopal', '01234566')
Index Page: (index.html)
<html>
<head>
<title> Student Visa Application </title>
</head>
<body>
<TABLE style="background-color: #ffffcc;" align="center">
<TR>
<TD align="center"><h2>Display Student Visa Info</h2></TD>
</TR>
<TR>
<TD align="center"><A HREF="displayvisa.jsp">
<font size="4" color="blue">show data from table</font>
</A></TD>
</TR>
</TABLE>
</body>
</html>
displayvisa.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>display data from the table using jsp</title>
</head>
<body>
<h2> Student Visa Info</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 20200000db. */
String connectionURL = "jdbc:mysql://172.16.100.8:3306/20200000db";
// 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.
ResultSet rs = 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, "2020A7PS0000U", "a");
/* createStatement() is used for create statement object that is used for sending sql statements to the specified database. */
statement = connection.createStatement();
// sql query to retrieve values from the secified table.
String QueryString = "SELECT * from studentvisa";
rs = statement.executeQuery(QueryString);
%>
<TABLE cellpadding="15" border="1" style="background-color: #ffffcc;">
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString(1)%></TD>
<TD><%=rs.getString(2)%></TD>
<TD><%=rs.getString(3)%></TD>
<TD><%=rs.getString(4)%></TD>
<TD><%=rs.getString(5)%></TD>
</TR>
<% } %>
<%
// close all the connections.
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="index.html" method="get" >
<button type="submit"><-- back</button></TD>
</TR>
</TABLE>
</font>
</body>
</html>