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>