需求:
1.展示全部
2.模糊查询
3.修改用户
4.删除用户
首先写数据库脚本:
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`StudentId` INT(20) NOT NULL AUTO_INCREMENT,
`StudentName` VARCHAR(20) NOT NULL,
`city` VARCHAR(20) NOT NULL,
`sex` VARCHAR(20) NOT NULL,
`birthday` DATETIME NOT NULL,
`desc` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`StudentId`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*Data for the table `student` */
INSERT INTO `student`(`StudentId`,`StudentName`,`city`,`sex`,`birthday`,`desc`) VALUES (1,'tom','河南','男','2018-05-02 00:00:00','青鸟学生'),(2,'jim','河北','男','2018-10-05 00:00:00','高中生'),(3,'lucy','山东','女','2018-06-21 00:00:00','小学生'),(4,'jeery','山西','女','2017-09-30 00:00:00','初中生'),(5,'王子','北京','男','1998-10-28 00:00:00','毕业生');
Pojo层:写与数据库中的对应数据get/set方法
Dao层:
01:StudentDao :
public interface StudentDao {
//展示所有信息
List<Student> getAll();
//模糊查询
List<Student> getStudentLikeName(String studentName);
//修改学生信息
int updateStudentById(Student student);
//删除学生信息
int deleteStudentById(int studentId);
}
02:impl下的StudentDaoImpl
public class StudentDaoImpl extends BaseDao implements StudentDao {
@Override
public List<Student> getAll() {
String sql="SELECT * FROM student";
ResultSet rs = this.executeQuery(sql, null);
List<Student> studentList=new ArrayList<>();
try {
while (rs.next()){
Student student =new Student();
student.setStudentId(rs.getInt("StudentId"));
student.setStudentName(rs.getString("StudentName"));
student.setCity(rs.getString("city"));
student.setSex(rs.getString("sex"));
student.setBirthday(rs.getDate("birthday"));
student.setDesc(rs.getString("desc"));
studentList.add(student);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return studentList;
}
@Override
public List<Student> getStudentLikeName(String studentName) {
String sql="SELECT * FROM student WHERE StudentName LIKE ?";
studentName="%"+studentName+"%";
Object[] objects={studentName};
ResultSet rs = this.executeQuery(sql, objects);
List<Student> studentList=new ArrayList<>();
try {
while (rs.next()){
Student student =new Student();
student.setStudentId(rs.getInt("StudentId"));
student.setStudentName(rs.getString("StudentName"));
student.setCity(rs.getString("city"));
student.setSex(rs.getString("sex"));
student.setBirthday(rs.getDate("birthday"));
student.setDesc(rs.getString("desc"));
studentList.add(student);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return studentList;
}
@Override
public int updateStudentById(Student student) {
String sql="UPDATE student SET StudentName=?, city=?,sex=?,birthday=?,`desc`=? WHERE StudentId=?";
Object[] objects={student.getStudentName(),student.getCity(),student.getSex(),student.getBirthday(),student.getDesc(),student.getStudentId()};
return this.executeUpdate(sql,objects);
}
@Override
public int deleteStudentById(int studentId) {
String sql="DELETE FROM student where studentId=?";
Object[] objects={studentId};
return this.executeUpdate(sql,objects);
}
Service层:
01:StudentService
public interface StudentService {
//展示所有信息
List<Student> getAll();
//模糊查询
List<Student> getStudentLikeName(String studentName);
//修改学生信息
boolean updateStudentById(Student student);
//删除学生信息
boolean deleteStudentById(int studentId);
}
02:StudentServiceimpl
public class StudentServiceimpl implements StudentService {
//数据访问层
StudentDao sd=new StudentDaoImpl();
@Override
public List<Student> getAll() {
return sd.getAll();
}
@Override
public List<Student> getStudentLikeName(String studentName) {
return sd.getStudentLikeName(studentName);
}
@Override
public boolean updateStudentById(Student student) {
if (sd.updateStudentById(student)>0){
return true;
}
return false;
}
@Override
public boolean deleteStudentById(int studentId) {
if (sd.deleteStudentById(studentId)>0){
return true;
}
return false;
}
Servlet层:
@WebServlet("/StudentServlet")
public class StudentServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决乱码问题
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
//处理响应乱码
response.setContentType("text/html;charset=utf-8");
//-------------------------
String path = request.getParameter("path");
//创建业务逻辑层
StudentService ss=new StudentServiceimpl();
//创建session
HttpSession session = request.getSession();
if (path==null){
//用户第一次进入,查询全部
List<Student> students=ss.getAll();
//把集合存入到session中
session.setAttribute("students",students);
response.sendRedirect("show.jsp");
}else if (path.equals("like")){
//模糊查询
//从前台获取文本框中的内容
String name = request.getParameter("stuName");
List<Student> students = ss.getStudentLikeName(name);
//把集合存入session
session.setAttribute("students",students);
response.sendRedirect("show.jsp");
}else if (path.equals("del")){
String idStr = request.getParameter("id");
//转换为int
int id =Integer.valueOf(idStr);
//调用删除的方法
boolean flag=ss.deleteStudentById(id);
response.getWriter().print(flag);
response.getWriter().flush();
response.getWriter().close();
}else if (path.equals("update")){
//修改学生信息
String name = request.getParameter("stuName");
String city = request.getParameter("city");
String sex = request.getParameter("sex");
String birthday = request.getParameter("birthday");
String desc = request.getParameter("desc");
String idStr = request.getParameter("stuId");
int id=Integer.valueOf(idStr);
System.out.println(name);
System.out.println(city);
Date b=null;
//转换日期
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
//转换日期
try {
b=sdf.parse(birthday);
} catch (ParseException e) {
e.printStackTrace();
}
//准备一个Student对象
Student student=new Student();
student.setStudentId(id);
student.setStudentName(name);
student.setCity(city);
student.setSex(sex);
student.setBirthday(b);
student.setDesc(desc);
//调用修改的方法
boolean flag = ss.updateStudentById(student);
System.out.println(flag);
if (flag){
response.getWriter().print("<script>alert('登陆成功');window.location='StudentServlet';</script>");
}else {
response.getWriter().print("<script>alert('登陆失败');window.location='StudentServlet';</script>");
}
}
页面 jsp层:
01:show.jsp:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>Title</title>
<script src="jquery-1.8.0.min.js"></script>
</head>
<body>
<table align="center">
<tr>
<th colspan="7">学生信息</th>
</tr>
<tr>
<td colspan="7">
<form action="StudentServlet?path=like" method="post">
姓名:<input type="text" name="stuName"><input type="submit" value="搜索">
</form>
</td>
</tr>
<tr>
<td>编号</td>
<td>姓名</td>
<td>地址</td>
<td>性别</td>
<td>出生日期</td>
<td>备注</td>
<td>操作</td>
</tr>
<c:forEach var="stu" items="${students}">
<tr id="N${stu.studentId}">
<td>${stu.studentId}</td>
<td>${stu.studentName}</td>
<td>${stu.city}</td>
<td>${stu.sex}</td>
<td>${stu.birthday}</td>
<td>${stu.desc}</td>
<td><a href="update.jsp?id=${stu.studentId}">修改</a> <a href="javascript:;" onclick="del(${stu.studentId})">删除</a></td>
</tr>
</c:forEach>
</table>
<script>
function del(id) {
$.get("StudentServlet","path=del&id="+id,function (data) {
if (data=="true"){ //页面删除
var tr=$("#N"+id);
tr.remove();
}
},"text")
}
$("tr:odd").css("background","pink");
$("tr:even").css("background","red");
</script>
</body>
</html>
02:update.jsp:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%
String idStr=request.getParameter("id");
Integer id = Integer.valueOf(idStr);
session.setAttribute("id",id);
%>
<form action="StudentServlet?path=update" method="post">
<c:forEach var="student" items="${students}">
<c:if test="${student.studentId==sessionScope.id}">
<table border="1">
<tr>
<td>编号:<input type="text" name="stuId" value="${student.studentId}"></td>
</tr>
<tr>
<td>姓名:<input type="text" name="stuName" value="${student.studentName}"></td>
</tr>
<tr>
<td>地址:<input type="text" name="city" value="${student.city}"></td>
</tr>
<tr>
<c:if test="${student.sex=='男'}">
<td>性别:<input type="radio" name="sex" value="男" checked>男
<input type="radio" name="sex" value="女">女
</td>
</c:if>
<c:if test="${student.sex =='女'}">
<td>性别:<input type="radio" name="sex" value="男" >男
<input type="radio" name="sex" value="女" checked>女
</td>
</c:if>
</tr>
<tr>
<td>出生日期:<input type="text" name="birthday" value="${student.birthday}"></td>
</tr>
<tr>
<td>备注:<input type="text" name="desc" value="${student.desc}"></td>
</tr>
<tr>
<td><input type="submit" value="提交"></td>
</tr>
</table>
</c:if>
</c:forEach>
</form>
</body>
</html>