<html>
<head>
<title>Address Book JSP Application</title>
</head>
<body bgcolor="black">
<h3 style='color:white'>Address Book JSP Application</h3>
<%@ page import="java.sql.*" %>
<%@ page import="com.mysql.jdbc.*" %>
<%! //JSP field/method declaration tag
static String method;
static String lastname;
static String result;
static String message;
%>
<% //JSP java code tag
method = request.getMethod();
lastname = request.getParameter("lname");
if (method.equals("POST")) //if POST --> client submitted form, validate
{
message = validate(); //validate //JSP java code tag
if (message.equals("")) //if no errors
result = getData(lastname); //retrieve data from DB
}
%>
<fieldset style='width:450px; height:300px; border-color:gold'>
<legend style='color:gold'>Address Book</legend>
<form name='form' method='POST' action='example5.jsp'>
<table style='background-color:lightyellow'>
<tr><td><b>Enter (partial) last name:
<td><input type='text' name='lname' value='<%=lastname%>'>
<td><input type='submit' value=' Search '>
<tr><td colspan=3><textarea cols=60 rows=15> <%=result%> </textarea>
</table>
</form>
</fieldset>
<p style='color:red'> <%=message%> </p>
</body>
</html>
<%! //JSP field/method declaration tag
//-----------------------------------------------------------------------------------
// Validate data entered
//-----------------------------------------------------------------------------------
static String validate()
{
String error = "";
if (lastname.equals(""))
error = "Please enter last name";
return(error);
}
//-----------------------------------------------------------------------------------
// getdata from database table
//-----------------------------------------------------------------------------------
static String getData(String lastname)
{
String host = "localhost";
String port = "3306";
String user = "demo";
String pswd = "demo";
String db = "demo";
Connection connect = null;
Statement stmt = null;
ResultSet rs = null;
String output = "";
try
{
Class.forName("com.mysql.jdbc.Driver"); //dynamically load the JDBC MySql Driver class
String con = "jdbc:mysql://" + host +":"+ port +"/"+ db;
connect = DriverManager.getConnection(con, user, pswd);
stmt = connect.createStatement();
String sql = "select * from addrbook "
+ "where lower(lname) like '%" +lastname+ "%'"; //contains
rs = stmt.executeQuery(sql);
while(rs.next()) //loop thru each row
{
output += rs.getString("fname") + " "
+ rs.getString("lname") + "\n"
+ rs.getString("street") + "\n"
+ rs.getString("city") + ", "
+ rs.getString("state") + " "
+ rs.getString("zip") + "\n"
+ rs.getString("phone") + "\n"
+ "----------------------- \n";
}
}
catch (Exception e)
{
output = "Could not execute SQL - " + e + "\n\n";
}
finally
{
try
{
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (connect != null) connect.close();
}
catch (SQLException e)
{
output = "Could not close database - " + e + "\n\n";
}
}
return(output);
}
//-----------------------------------------------------------------------------------
%>