教师信息管理系统
查询
上次写完了前端+管理员登录功能，今天完成了查看教师信息&对姓名模糊搜索的功能
这是数据库里的三个表：
涉及到查询的几个基本文件是：
query.jsp：前端ServletQuery：与服务器的接口Teacher.java：业务逻辑层DBTeacher.java：与数据库的接口
前端代码和上次写的差不多，就是在底下新加了一个textarea 用来显示查询的结果。
界面长这样：
嘛虽然很丑但我们又不是搞前端的，重要的还是把功能给实现了对不对。
贴一下前端代码：
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.io.*, java.util.*, java.sql.*, business.*"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>查询</title>
<link href="common.css" rel="stylesheet" type=text/css>
<style type="text/css">
.main2 {
height: 105px;
}
.result {
margin: 106px 0 0 100px;
height: 225px;
}
</style>
</head>
<body class="body">
<div class="center2">
<h2>查询</h2>
<div class="menu2">
<p>
<a href="login.jsp">登录</a>
<p />
<p>
<a>查询</a>
<p />
<p>
<a href="home.html">返回</a>
<p />
</div>
<div class="main2">
<form action="query" method="post">
<p>
姓名: <input type="text" name="TName" /><br />
</p>
<input type="submit" value="查看" name="view"> <input
type="submit" value="搜索" name="search" />
</form>
</div>
<div class="result">
<textarea rows="20" cols="45">
<%
if (request.getAttribute("result") != null) {
List<Teacher> teacher = (List<Teacher>) request.getAttribute("result");
//if (teacher != null) {
for (Teacher x : teacher) {
out.println(" ");
//out.println("id: " + teacher.getId());
out.println("姓名: " + x.getName());
out.println("院: " + x.getDepfather());
out.println("系: " + x.getDepname());
//out.println("dep: " + teacher.getDep());
out.println("职称: " + x.getTitle());
out.println("电话: " + x.getPhone());
out.println("办公地址: " + x.getAddress());
out.println("邮箱: " + x.getEmail());
}
//} else {
/* out.println(" ");
out.println("对不起，没有这名教师的信息"); */
//}
} else if (request.getAttribute("resultAll") != null) {
List<Teacher> teachers = (List<Teacher>) request.getAttribute("resultAll");
for (Teacher x : teachers) {
out.println(" ");
//out.println("id: " + teacher.getId());
out.println("姓名: " + x.getName());
out.println("院: " + x.getDepfather());
out.println("系: " + x.getDepname());
//out.println("dep: " + teacher.getDep());
out.println("职称: " + x.getTitle());
out.println("电话: " + x.getPhone());
out.println("办公地址: " + x.getAddress());
out.println("邮箱: " + x.getEmail());
}
} else {
out.println(" ");
out.println("对不起，没有这名教师的信息");
}
%>
</textarea>
</div>
</div>
</body>
</html>其中的css 样式写在common.css 里面了，主要就是设置了一下div 的位置和背景色，没什么，就不贴了。
前端最主要的部分是一个表单，点击查看或搜索后， 表单把数据发送给服务器，然后由ServletQuery.java 从服务器上把数据拿下来，处理好后再传给后端。
贴一下ServeltQuery.java 的代码：
package servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import business.Teacher;
/**
* Servlet implementation class ServletQuery
*/
@WebServlet("/query")
public class ServletQuery extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Teacher teacher = new Teacher();
request.setCharacterEncoding("UTF-8");
if (request.getParameter("search") != null) {//用户点击了搜索
String name = request.getParameter("TName");
List<Teacher> teachers = teacher.search(name);
/*for(Teacher x: teachers){
System.out.println(x.getName());
System.out.println(x.getDepfather());
System.out.println(x.getDepname());
}*/
request.setAttribute("result", teachers);
}
if (request.getParameter("view") != null) {//用户点击了查看
List<Teacher> allName = teacher.getAll();
request.setAttribute("resultAll", allName);
}
request.getRequestDispatcher("/query.jsp").forward(request, response);
}
}
可以看到用request.getParameter 方法可以将数据从服务器上拿下来，在这里判断一下究竟用户是点击了查看还是搜索，然后二者分别调用不同的方法，Teacher 对象的两个方法
List<Teacher> search( String name) ：通过关键字模糊搜索数据库中符合条件的条目，并返回一个Teacher 类List
List<Teacher> getAll() ：找到数据库中所有的条目并返回一个Teacher 类List
调用这两个方法之后将结果setAttribute 然后传回给前端（话说这个setAttribute 虽然我能用但也没太搞清楚它是干嘛的
贴一下Teacher.java
package business;
import java.util.List;
import database.DBTeacher;
public class Teacher {
int id;
String name;
int dep;
String title;
String phone;
String address;
String email;
String depname;
String depfather;
public Teacher() {
}
public Teacher(String name, int dep, String title, String phone, String address, String email, String depname,
String depfather) {
super();
this.name = name;
this.dep = dep;
this.title = title;
this.phone = phone;
this.address = address;
this.email = email;
this.depname = depname;
this.depfather = depfather;
}
public String getDepname() {
return depname;
}
public void setDepname(String depname) {
this.depname = depname;
}
public String getDepfather() {
return depfather;
}
public void setDepfather(String depfather) {
this.depfather = depfather;
}
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 int getDep() {
return dep;
}
public void setDep(int dep) {
this.dep = dep;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public List<Teacher> search(String name) {
DBTeacher dbteacher = new DBTeacher();
List<Teacher> teacher = dbteacher.getIfo(name);
return teacher;
}
public boolean add(String name, String deptname, String title, String phone, String address, String email){
boolean result = false;
DBTeacher teacher = new DBTeacher();
int deptId = teacher.getDeptId(deptname);
result = teacher.insert(name, deptId, title, phone, address, email);
return result;
}
public boolean delete(String name, String deptname, String title, String phone, String address, String email) {
boolean result = false;
DBTeacher teacher = new DBTeacher();
int deptId = teacher.getDeptId(deptname);
//System.out.println("deptId:" + deptId);
result = teacher.delete(name, deptId, title, phone, address, email);
return result;
}
public boolean update(String name, String deptname, String title, String phone, String address, String email,
String newname, String newdeptname, String newtitle, String newphone, String newaddress, String newemail) {
boolean flag = false;
DBTeacher teacher = new DBTeacher();
int deptId = teacher.getDeptId(deptname);
int newDeptId = deptId;
if(newdeptname.length() == 0)
newDeptId = deptId;
else
newDeptId = teacher.getDeptId(newdeptname);
flag = teacher.update(name, deptId, title, phone, address, email, newname, newDeptId, newtitle, newphone, newaddress, newemail);
return flag;
}
public List<Teacher> getAll() {
DBTeacher teacher = new DBTeacher();
List<Teacher> teachers = teacher.getAll();
return teachers;
}
}
里面很多方法今天没讲到，是针对后面管理员的增删改功能写的，其实这个类在这个项目里毫无必要…不过我习惯这么写了，嫌麻烦的话也可以直接在servlet里调用和数据库交互的类。
数据库就不多说啦，反正就是写sql然后执行嘛，直接贴代码：
package database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import business.Teacher;
public class DBTeacher {
public List<Teacher> getIfo(String name) {
//System.out.println("dbteacher:in getinfo");
// Query query = new Query();//TODO
List<Teacher> teachers = new ArrayList();
DBConnect db = new DBConnect();
Connection conn = db.connect();
PreparedStatement pstmt;
// String sql = "select * from Teachers where TName = ?";
String sql = "select * from Teachers where TName like ?";
ResultSet result;
try {
//System.out.println("in try");
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, "%" + name + "%");
result = pstmt.executeQuery();
while (result.next()) {
//System.out.println("in while");
Teacher teacher = new Teacher();
teacher.setId(result.getInt(1));
teacher.setName(result.getString(2));
teacher.setDep(result.getInt(3));
teacher.setTitle(result.getString(4));
teacher.setPhone(result.getString(5));
teacher.setAddress(result.getString(6));
teacher.setEmail(result.getString(7));
teacher.setDepname(this.getDept(teacher.getDep()));
teacher.setDepfather(this.getDept(this.getFatherId(teacher.getDep())));
teachers.add(teacher);
//System.out.println(teacher.getName());
}
pstmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("in dbteacher: getinfo fail");
e.printStackTrace();
}
return teachers;
}
public int getFatherId(int id) {
// System.out.println("in getfatherid : id :" + id);
// TODO Auto-generated method stub
int fatherId = 0;
DBConnect db = new DBConnect();
Connection conn = db.connect();
PreparedStatement pstmt = null;
ResultSet result = null;
String sql = "select FatherId from Dept where DeptId = ?";
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setInt(1, id);
result = pstmt.executeQuery();
if (result.next()) {
fatherId = result.getInt("FatherId");
// System.out.println("in getfatherid:" + fatherId);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("in dbteacher: get father id fail");
}
return fatherId;
}
public String getDept(int id) {
String name = null;
DBConnect db = new DBConnect();
Connection conn = db.connect();
PreparedStatement pstmt = null;
String sql = "select DeptName from Dept where DeptId = ?";
ResultSet result = null;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setInt(1, id);
result = pstmt.executeQuery();
if (result.next()) {
name = result.getString(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("in dbteacher:get deptname fail");
e.printStackTrace();
}
return name;
}
public int getDeptId(String deptname) {
int deptId = 0;
DBConnect db = new DBConnect();
Connection conn = db.connect();
PreparedStatement pstmt = null;
ResultSet result = null;
String sql = "select DeptId from Dept where DeptName = ?";
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, deptname);
result = pstmt.executeQuery();
if (result.next())
deptId = result.getInt(1);
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("in dbteacehr: get deptid fail");
e.printStackTrace();
}
return deptId;
}
public boolean insert(String name, int deptId, String title, String phone, String address, String email) {
boolean flag = false;
DBConnect db = new DBConnect();
Connection conn = db.connect();
PreparedStatement pstmt = null;
int result = 0;
String sql = "insert into Teachers values(?, ?, ?, ?, ?, ?)";
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setInt(2, deptId);
pstmt.setString(3, title);
pstmt.setString(4, phone);
pstmt.setString(5, address);
pstmt.setString(6, email);
result = pstmt.executeUpdate();
flag = true;
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("in dbteacher: insert fail");
e.printStackTrace();
}
return flag;
}
public boolean delete(String name, int deptId, String title, String phone, String address, String email) {
// System.out.println("in dbdelete");
boolean flag = false;
DBConnect db = new DBConnect();
Connection conn = db.connect();
PreparedStatement pstmt = null;
int result = 0;
String sql = "delete from Teachers where TName = ? and" + " TDeptId = ? and" + " TTitle = ? and"
+ " TPhone = ? and" + " TAddress = ? and" + " TEmail = ?";
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setInt(2, deptId);
pstmt.setString(3, title);
pstmt.setString(4, phone);
pstmt.setString(5, address);
pstmt.setString(6, email);
result = pstmt.executeUpdate();
flag = true;
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("in dbteacher: delete fail");
e.printStackTrace();
}
return flag;
}
public boolean update(String name, int deptId, String title, String phone, String address, String email,
String newname, int newDeptId, String newtitle, String newphone, String newaddress, String newemail) {
// TODO：要考虑左半边表也会出现null的情况
if (newname.length() == 0) {
newname = name;
}
if (newtitle.length() == 0) {
newtitle = title;
}
if (newphone.length() == 0) {
newphone = phone;
}
if (newaddress.length() == 0) {
newaddress = address;
}
if (newemail.length() == 0) {
newemail = email;
}
/*
* System.out.println("newname:" + newname);
* System.out.println("newtitle:" + newtitle);
* System.out.println("newphone:" + newphone);
* System.out.println("newemail:" + newemail);
*/
System.out.println("indb--------------------");
System.out.println(name);
System.out.println(deptId);
System.out.println(title);
System.out.println(phone);
System.out.println(address);
System.out.println(email);
System.out.println(newname);
System.out.println(newDeptId);
System.out.println(newtitle);
System.out.println(newphone);
System.out.println(newaddress);
System.out.println(newemail);
boolean flag = false;
DBConnect db = new DBConnect();
Connection conn = db.connect();
PreparedStatement pstmt = null;
int result = 0;
String sql = "update Teachers " + "set TName = ?, " + "TDeptId = ?, " + "TTitle = ?, " + "TPhone = ?, "
+ "TAddress = ?, " + "TEmail = ? " + "where TName = ? and " + "TDeptId = ? and " + "TTitle = ? and "
+ "TPhone = ? and " + "TAddress = ? and " + "TEmail = ?";
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(7, name);
pstmt.setInt(8, deptId);
pstmt.setString(9, title);
pstmt.setString(10, phone);
pstmt.setString(11, address);
pstmt.setString(12, email);
pstmt.setString(1, newname);
pstmt.setInt(2, newDeptId);
pstmt.setString(3, newtitle);
pstmt.setString(4, newphone);
pstmt.setString(5, newaddress);
pstmt.setString(6, newemail);
result = pstmt.executeUpdate();
flag = true;
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("in dbteacher: updatefail");
e.printStackTrace();
}
return flag;
}
public List<Teacher> getAll() {
List<Teacher> teachers = new ArrayList();
DBConnect db = new DBConnect();
Connection conn = db.connect();
PreparedStatement pstmt = null;
ResultSet result = null;
String sql = "select * from Teachers";
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
result = pstmt.executeQuery();
while (result.next()) {
Teacher teacher = new Teacher();
teacher.setId(result.getInt(1));
teacher.setName(result.getString(2));
teacher.setDep(result.getInt(3));
teacher.setTitle(result.getString(4));
teacher.setPhone(result.getString(5));
teacher.setAddress(result.getString(6));
teacher.setEmail(result.getString(7));
teacher.setDepname(this.getDept(teacher.getDep()));
teacher.setDepfather(this.getDept(this.getFatherId(teacher.getDep())));
teachers.add(teacher);
}
} catch (SQLException e) {
System.out.println("in dbteacher: getall fail");
e.printStackTrace();
}
return teachers;
}
}
把增删改也大概写了一下，但是还有很多疏漏的地方，以及！每次都忘记断开连接…这个习惯很不好！
总结一下这次写查询吧。
- 一开始写的时候，没有写
DBDept类，所以和院系有关的查询也直接放在DBTeacher里了，现在写到一半发现必须要写DBDept类，改起来就有点痛苦，所以说，在敲代码之前，一定一定要先设计，把架构定下来，并且反复斟酌，才不会造成现在这种局面。 本来会写业务逻辑层就是为了把所有逻辑都放在业务逻辑层，数据库只写最基本的增删差改，而不用管逻辑的问题，但是写着写着就把这件事给忘了！想
getInfo里又调用了同类里的其他方法，这部分应该在业务逻辑层处理，而不应该写在数据库层，唉，下一个作业一定要严格一点。暂时就只想到这么多，明天应该在写算法了，可能周四再接着写吧。
扫一扫
4855
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
