編寫程序：使用MVC模式開發程序，完成數據的模糊查詢。
要求：
（1）index.jsp用於輸入要查詢的數據。
（2）result.jsp:用於顯示結果。
（3）queryServlet：用於驗證數據、實例化JavaBean、調用連接數據庫、控制頁面跳轉
（4）queryDAO：用於連接數據庫及進行數據庫的操作如：查詢、刪除、更改等
（5）Student：JavaBean用於數據的封裝，方便將查詢結果在servlet與jsp頁面之間進行傳遞等
以上幾個部分共同構成了MVC模式，JSP為MVC模式當中的V，Servlet為C，queryDAO與JavaBean合在一起為M。
[html]
<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
請輸入要查詢的內容：
<form action="queryServlet" method="post">
<input name="name">
<input type="submit" value="模糊查詢">
</form>
</body>
</html>
[html]
<%@ page language="java" import="java.util.*,com.mars.*" pageEncoding="gb2312"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>查詢結果</title>
</head>
<body>
查詢結果如下：
<table border="1">
<tr>
<td>學號</td>
<td>姓名</td>
<td>年齡</td>
<td>性別</td>
<td>地址</td>
</tr>
<%
ArrayList arrayList = (ArrayList)request.getAttribute("arrayList");
for(int i=0; i<arrayList.size();i++){
Student student = (Student)arrayList.get(i);
%>
<tr>
<td><%=student.getId()%></td>
<td><%=student.getName()%></td>
<td><%=student.getAge()%></td>
<td><%=student.getSex()%></td>
<td><%=student.getAddress()%></td>
</tr>
<%
}
%>
</table>
</body>
</html>
[html]
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>queryServlet</servlet-name>
<servlet-class>com.mars.queryServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>queryServlet</servlet-name>
<url-pattern>/queryServlet</url-pattern>
</servlet-mapping>
</web-app>
[java]
package com.mars;
public class Student {
private int id;
private String name;
private String sex;
private int age;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
[java]
package com.mars;
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.mars.QueryDAO;
public class queryServlet extends HttpServlet {
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("GBK");
response.setCharacterEncoding("GBK");
String name = request.getParameter("name");
QueryDAO qd = new QueryDAO();
ArrayList arrayList = qd.queryLike(name);
request.setAttribute("arrayList", arrayList);
request.getRequestDispatcher("/result.jsp").forward(request, response);
}
}
[java]
package com.mars;
import java.sql.*;
import java.util.ArrayList;
import com.mars.Student;
public class QueryDAO {
private Connection conn = null;
public QueryDAO() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testmvc", "root", "root");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public ArrayList queryLike(String name) {
ArrayList arrayList = new ArrayList();
String SQL = "select * from student where name like" + "'%" + name
+ "%'" + "or address like " + "'%" + name + "%'";
try {
ResultSet resultSet = conn.createStatement().executeQuery(SQL);
while (resultSet.next()) {
Student student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setAge(resultSet.getInt("age"));
student.setSex(resultSet.getString("sex"));
student.setAddress(resultSet.getString("address"));
arrayList.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}
return arrayList;
}
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void close(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
}
}
[sql]
drop database testmvc;
create database testmvc;
use testmvc;
create table student (
id int not null primary key auto_increment,
name varchar(10),
age int,
sex varchar(2),
address varchar(50)
);
insert into student values(null,'張三',20,'男','北京市東城區天安門');
insert into student values(null,'李四',21,'男','上海市東城區天安門');
insert into student values(null,'王五',22,'男','天津市東城區天安門');
insert into student values(null,'趙六',23,'男','南京市東城區天安門');
insert into student values(null,'孫七',24,'男','福建省東城區天安門');
insert into student values(null,'錢八',25,'男','長沙市東城區天安門');
insert into student values(null,'劉九',26,'男','武漢市東城區天安門');
