学生信息管理系统
准备
- 1.数据库
CREATE TABLE `stu` (
`sid` INT(11) NOT NULL AUTO_INCREMENT,
`sname` CHAR(5) NULL ,
`gender` CHAR(2) NULL ,
`phone` CHAR(12) NULL ,
`birthday` DATE NULL ,
`hobby` CHAR(50) NULL ,
`info` CHAR(50) NULL ,
PRIMARY KEY (`sid`)
)
-
2.创建实体类 Student
-
3.导入相应jar包
- Eclipse项目中web app libraries和 Referenced Libraries区别
* Referenced Libraries是编译环境下使用的JAR包 所谓编译环境下使用的JAR包, 就是说你在Eclipse中进行源文件的编写的时候,所需要引用到的类都从Referenced Libraries这个集合中的JAR包中拿; * /web-inf/lib中的JAR包是运行时环境下使用的JAR包 所谓运行时环境下使用的JAR包,就是说你在运行你的项目的时候所需要使用的JAR包的集合;
查询
- 1.先写一个jsp页面,里面放一个超链接
index.jsp
<h3><a href="StudentListServlet">显示所有学生列表</a></h3>
- 2.写Servlet接受请求,去调用Service,由service调用Dao
- 3.先写Dao,做Dao的实现
public interface StudentDao {
/**
* 查询所有的学生信息
* @return List<student>
* @throws SQLException
*/
List<Student> findAll() throws SQLException;
}
public class StudentDaoImpl implements StudentDao{
@Override
public List<Student> findAll() throws SQLException {
DataSource dataSource = JDBCUtil.getDataSource();
QueryRunner runner = new QueryRunner(dataSource);
return runner.query("select * from stu", new BeanListHandler<Student>(Student.class));
}
}
- 4.再Service,做Service的实现
public interface StudentService {
/**
* 查询所有学生信息
* @return List<student>
* @throws SQLException
*/
List<Student> findAll() throws SQLException;
}
public class StudentServiceImpl implements StudentService{
@Override
public List<Student> findAll() throws SQLException {
StudentDao dao = new StudentDaoImpl();
return dao.findAll();
}
}
- 5.在servlet存储数据,并且做页面响应
public class StudentListServlet extends HttpServlet {
/**
* 负责查询所有的学生信息,然后呈现在页面上
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//1.查询出来所有的学生
StudentService service = new StudentServiceImpl();
List<Student> list = service.findAll();
//2.先把数据存到作用域中
request.setAttribute("list", list);
//3.跳转页面
request.getRequestDispatcher("list.jsp").forward(request, response);
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
- 6.在list。jsp上显示数据
使用EL + JSTL + 表格
增加
- 1.先跳转到增加的页面,编写增加的页面
<tr>
<td colspan="8">
<a href="add.jsp">添加</a>
</td>
</tr>
<!-- 注意对表单form的填写 -->
<form method="post" action="AddStudentServlet">
<tr>
<td colspan="2"><input type="submit" value="添加"></td>
</tr>
</form>
- 2.点击添加,提交数据到servlet,处理数据
public class AddStudentServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
request.setCharacterEncoding("UTF-8");
//获取学生信息
String sname = request.getParameter("sname");
String gender = request.getParameter("gender");
String phone = request.getParameter("phone");
String birthday = request.getParameter("birthday");
String info = request.getParameter("info");
//获取爱好
String [] h = request.getParameterValues("hobby"); //获取爱好数组
String hobby = Arrays.toString(h); //转成string,格式将变为[篮球,游泳 ...]
hobby = hobby.substring(1,hobby.length()-1); //去除两端的中括号
//2.添加到数据库
//string --> Date
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(birthday);
Student student = new Student(sname,gender,phone,date,hobby,info);
StudentService service = new StudentServiceImpl();
service.insert(student);
//3.跳转到列表页
//在查一次数据库,装到作用域中,再跳转
//这里是直接跳转到页面上,那么这个页面会重新翻译一次,上面那个request的请求存放的数据就没有了
//request.getRequestDispatcher("list.jsp").forward(request, response);
//sevlet除了能跳转jsp之外,还能跳转到servlet
request.getRequestDispatcher("StudentListServlet").forward(request, response);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
- 3.调用service
public interface StudentService {
/**
* 添加学生
* @param student 需要添加到数据库的学生对象
* @throws SQLException
*/
void insert(Student student) throws SQLException;
}
public class StudentServiceImpl implements StudentService{
@Override
public void insert(Student student) throws SQLException {
StudentDao dao = new StudentDaoImpl();
dao.insert(student);
}
}
- 4.调用Dao,完成数据持久化,即保存到数据库
public interface StudentDao {
/**
* 添加学生
* @param student 需要添加到数据库的学生对象
* @throws SQLException
*/
void insert(Student student) throws SQLException;
}
public class StudentDaoImpl implements StudentDao{
@Override
public void insert(Student student) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
runner.update("insert into stu values(null,?,?,?,?,?,?)",
student.getSname(),
student.getGender(),
student.getPhone(),
student.getBirthday(),
student.getHobby(),
student.getInfo()
);
}
}
- 5.完成存储工作后,跳转更新后的页面
request.getRequestDispatcher("StudentListServlet").forward(request, response);
删除
- 1.点击超链接,弹出一个确认删除的对话框,如果点击了确定,就删除
list.jsp
<td><a href="#" onclick="doDelete(${stu.sid})">删除</a></td>
- 2.让超链接执行一个js方法
list.jsp
<script type="text/javascript">
function doDelete(sid){
//弹出对话框,如果用户点击确定,就请求servlet
var flag=confirm("是否确定删除?");
if(flag){
//点了确定,访问servlet,在当前页面打开超链接,并传入id
location.href="DeleteStudentServlet?sid="+sid;
}
}
</script>
- 3.在js访问里面判断点击的选项,然后跳转到servlet
public class DeleteStudentServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//1.接收id
int sid = Integer.parseInt(request.getParameter("sid"));
System.out.println(sid);
//2.执行删除
StudentService service = new StudentServiceImpl();
service.delete(sid);
//3.跳转页面
request.getRequestDispatcher("StudentListServlet").forward(request, response);;
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
- 4.servlet收到了请求,然后去调用service,service去调用dao
public interface StudentDao {
/**
* 根据id删除学生
* @param sid
* @throws SQLException
*/
void delete(int sid) throws SQLException;
}
public class StudentDaoImpl implements StudentDao{
@Override
public void delete(int sid) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
runner.update("delete from stu where sid=?",sid);
}
}
public interface StudentService {
/**
* 根据id删除学生
* @param sid
* @throws SQLException
*/
void delete(int sid) throws SQLException;
}
public class StudentServiceImpl implements StudentService{
@Override
public void delete(int sid) throws SQLException {
StudentDao dao = new StudentDaoImpl();
dao.delete(sid);
}
}
更新
- 1.点击列表上的更新,先跳转到一个EditStudentServlet,查询该学生的所有信息
list.jsp
<-- 在这个servlet里面,先根据id去查询这个学生的所有信息出来 -->
<td><a href="EditStudentServlet?sid=${stu.sid }">更新</a></td>
public interface StudentDao {
/**
* 根据id查询学生信息
* @param sid
* @throws SQLException
*/
Student findStudentById(int sid) throws SQLException;
}
public class StudentDaoImpl implements StudentDao{
@Override
public Student findStudentById(int sid) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
return runner.query("select * from stu where sid=?",new BeanHandler<Student>(Student.class), sid);
}
}
public interface StudentService {
/**
* 根据id查询单个学生信息
* @param sid
* @return student
* @throws SQLException
*/
Student findStudentById(int sid) throws SQLException;
}
public class StudentServiceImpl implements StudentService{
@Override
public Student findStudentById(int sid) throws SQLException {
StudentDao dao = new StudentDaoImpl();
return dao.findStudentById(sid);
}
}
public class EditStudentServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//1.获取待修改的学生id
int sid = Integer.parseInt(request.getParameter("sid"));
//2.根据id获取学生对象
StudentService service = new StudentServiceImpl();
Student student = service.findStudentById(sid);
//3,显示数据
//存数据
request.setAttribute("student", student);
//跳转页面
request.getRequestDispatcher("edit.jsp").forward(request, response);;
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
- 2.跳转到更新的页面,然后再页面上显示数据
edit.jsp
<-- 创建一个隐藏的输入框,再里面给定id的值 -->
<input type="hidden" name="sid" value="${student.sid }">
<-- 宏定义 -->
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<-- 文本类型数据的获取 -->
<tr>
<td>姓名</td>
<td><input type="text" name="sname" value="${student.sname }"></td>
</tr>
<-- 单选框数据的获取 -->
<tr>
<td>性别</td>
<td>
<!-- servlet中设置的属性名为student -->
<input type="radio" name="gender" value="男"<c:if test="${student.gender == '男' }">checked</c:if>>男
<input type="radio" name="gender" value="女"<c:if test="${student.gender == '女' }">checked</c:if>>女
</td>
</tr>
<-- 复选框数据的获取 -->
<tr>
<td>爱好</td>
<td>
<!-- 因为爱好有很多个,里面有包含的关系 -->
<input type="checkbox" name="hobby" value="游泳"<c:if test="${fn:contains(student.hobby,'游泳') }">checked</c:if>>游泳
<input type="checkbox" name="hobby" value="篮球"<c:if test="${fn:contains(student.hobby,'篮球') }">checked</c:if>>篮球
<input type="checkbox" name="hobby" value="足球"<c:if test="${fn:contains(student.hobby,'足球') }">checked</c:if>>足球
<input type="checkbox" name="hobby" value="看书"<c:if test="${fn:contains(student.hobby,'看书') }">checked</c:if>>看书
<input type="checkbox" name="hobby" value="写字"<c:if test="${fn:contains(student.hobby,'写字') }">checked</c:if>>写字
</td>
</tr>
- 3.修改完毕后,提交数据到UpdateServlet,获取数据,调用servlet,调用dao
public interface StudentDao {
/**
* 根据id更新学生的信息
* @param sid
* @throws SQLException
*/
void update(Student student) throws SQLException;
}
public class StudentDaoImpl implements StudentDao{
@Override
public void update(Student student) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
runner.update("update stu set sname=?,gender=?,phone=?,birthday=?,hobby=?,info=? where sid=?",
student.getSname(),
student.getGender(),
student.getPhone(),
student.getBirthday(),
student.getHobby(),
student.getInfo(),
student.getSid());
}
}
public interface StudentService {
/**
* 根据id更新学生信息
* @param sid
* @throws SQLException
*/
void update(Student student) throws SQLException;
}
public class StudentServiceImpl implements StudentService{
@Override
public void update(Student student) throws SQLException {
StudentDao dao = new StudentDaoImpl();
dao.update(student);
}
}
public class UpdateStudentServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
request.setCharacterEncoding("UTF-8");
//获取学生信息
int sid = Integer.parseInt(request.getParameter("sid"));
String sname = request.getParameter("sname");
String gender = request.getParameter("gender");
String phone = request.getParameter("phone");
String birthday = request.getParameter("birthday");
//String hobby = request.getParameter("hobby");
String [] h = request.getParameterValues("hobby"); //获取爱好数组
String hobby = Arrays.toString(h); //转成string
hobby = hobby.substring(1,hobby.length()-1); //去除两端的中括号
String info = request.getParameter("info");
//2.更新到数据库
//string --> Date
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(birthday);
Student student = new Student(sid,sname,gender,phone,date,hobby,info);
StudentService service = new StudentServiceImpl();
service.update(student);
//3.跳转到列表页
request.getRequestDispatcher("StudentListServlet").forward(request, response);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
提交上来的数据是没有带id的,需要手动创建一个隐藏的输入框,再里面给定id的值,使提交上来的表单带上id
4.跳转页面,调用StudentListServlet显示列表
模糊查询
<td colspan="8">
按姓名查询:<input type="text" name="sname"/>
按性别查询:<select name="gender">
option value="" >--请选择--
<option value="男">男
<option value="女">女
</select>
<input type="submit" value="查询">
<a href="add.jsp">添加</a>
</td>
public interface StudentDao {
/**
* 模糊查询,根据姓名或根据性别,或两则兼有
* @param sname
* @param sgender
* @return List<Student> 集合
* @throws SQLException
*/
List<Student> searchStudent(String sname,String gender) throws SQLException;
}
public class StudentDaoImpl implements StudentDao{
@Override
public List<Student> searchStudent(String sname, String gender) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
//根据传入的具体参数,生成sql语句
String sql = "select * from stu where 1=1";
List<String> list = new ArrayList<String>();
if(!textUtil.isEmpty(sname)) {
//如果名字不为空
sql = sql + " and sname like ?";
list.add("%"+sname+"%");
}
if(!textUtil.isEmpty(gender)) {
//如果性别不为空
sql = sql + " and gender=?";;
list.add(gender);
}
return runner.query(sql, new BeanListHandler<Student>(Student.class),list.toArray());
}
}
public interface StudentService {
/**
* 模糊查询
* @param sname
* @param sgender
* @return
*/
List<Student> searchStudent(String sname,String gender) throws SQLException;
}
public class StudentServiceImpl implements StudentService{
@Override
public List<Student> searchStudent(String sname, String gender) throws SQLException {
StudentDao dao = new StudentDaoImpl();
return dao.searchStudent(sname, gender);
}
}
public class SearchStudentServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
request.setCharacterEncoding("UTF-8");
//1.渠道要查询的关键数据姓名、性别
String sname = request.getParameter("sname");
String gender = request.getParameter("gender");
//2.找service查询
StudentService service = new StudentServiceImpl();
List<Student> list = service.searchStudent(sname, gender);
request.setAttribute("list", list);
//3.跳转页面
request.getRequestDispatcher("list.jsp").forward(request, response);
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
分页查询
list_page
<td colspan="8">
第${pageBean.currentPage } /${pageBean.totalPage } 页
每页显示${pageBean.pageSize } 条
总的记录数${pageBean.totalSize }
<c:if test="${pageBean.currentPage != 1 }">
<a href="StudentListPageServlet?currentPage=1">首页</a>
<a href="StudentListPageServlet?currentPage=${pageBean.currentPage - 1 }">上一页</a>
</c:if>
<c:forEach begin="1" end="${pageBean.totalPage }" var="i">
<c:if test="${pageBean.currentPage == i }">${i }</c:if>
<c:if test="${pageBean.currentPage != i }">
<a href="StudentListPageServlet?currentPage=${i }">${i }</a>
</c:if>
</c:forEach>
<c:if test="${pageBean.currentPage != pageBean.totalPage }">
<a href="StudentListPageServlet?currentPage=${pageBean.currentPage + 1 }">下一页</a>
<a href="StudentListPageServlet?currentPage=${pageBean.totalPage }">尾页</a>
</c:if>
</td>
public interface StudentDao {
static int PAGE_SIZE = 3;//一页显示多少条记录
/**
* 查询当页的学生数据
* @param currentPage
* @return List<Student> 集合
* @throws SQLException
*/
List<Student> findStudentByPage(int currentPage) throws SQLException;
/**
* 查询总的记录数
* @return 总记录数
* @throws SQLException
*/
int findCount() throws SQLException;
}
public class StudentDaoImpl implements StudentDao{
@Override
public List<Student> findStudentByPage(int currentPage) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
//第一个问好代表返回多少条记录,第二个问好,代表跳过前面的多少条记录
return runner.query("select * from stu limit ? offset ?",new BeanListHandler<Student>(Student.class),PAGE_SIZE,(currentPage - 1)*PAGE_SIZE);
}
@Override
public int findCount() throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
//ScalarHandler : 用于处理平均数,总的个数等
Long result = (Long) runner.query("select count(*) from stu",new ScalarHandler());
return result.intValue();
}
}
public interface StudentService {
/**
* 查询当页的数据
* @param currentPage
* @return List<Student> 集合
* @throws SQLException
*/
PageBean findStudentByPage(int currentPage) throws SQLException;
}
public class StudentServiceImpl implements StudentService{
@Override
public PageBean findStudentByPage(int currentPage) throws SQLException {
//封装分页的该页数据
PageBean<Student> pageBean = new PageBean<Student>();
int pageSize = StudentDao.PAGE_SIZE;
pageBean.setCurrentPage(currentPage);
pageBean.setPageSize(StudentDao.PAGE_SIZE);
//获取当前页学生信息集合
StudentDao dao = new StudentDaoImpl();
List<Student> list = dao.findStudentByPage(currentPage);
pageBean.setList(list);
//总的记录数,总的页数
int count = dao.findCount();
pageBean.setTotalSize(count);
pageBean.setTotalPage(count % pageSize == 0 ? count / pageSize : (count / pageSize) + 1);
return pageBean;
}
}
public class StudentListPageServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//1.获取需要显示的页码数
int currentPage = Integer.parseInt(request.getParameter("currentPage"));
//2。根据页码数,获取页面的数据
StudentService service = new StudentServiceImpl();
PageBean pageBean = service.findStudentByPage(currentPage);
request.setAttribute("pageBean", pageBean);
//3.跳转页面
request.getRequestDispatcher("list_page.jsp").forward(request, response);
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}