Servlet+JSP+JDBC
1. 创建数据库表
create table t_student(
stu_id int primary key auto_increment,
stu_name varchar(20),
stu_pass varchar(20),
stu_age int,
stu_address varchar(30)
);
2. 创建javaweb项目ServletJSPJDBC,[修改Content Root为sjj],修改工程访问名称
3. 导入第三方依赖【druid-1.1.10.jar mysql-connector-java-5.1.38-bin.jar】
4. 在src下创建数据库连接配置文件【xxxxxxxx.properties】
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8
username=root
password=123456
initialSize=100
maxActive=300
maxWait=60000
5. 创建javabean
package com.wangxing.student.bean;
/**
* 保存学生信息的javabean
* @author Administrator
*
*/
public class StudentBean {
private int stuid;
private String stuname;
private String stupass;
private int stuage;
private String stuaddress;
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
public String getStupass() {
return stupass;
}
public void setStupass(String stupass) {
this.stupass = stupass;
}
public int getStuage() {
return stuage;
}
public void setStuage(int stuage) {
this.stuage = stuage;
}
public String getStuaddress() {
return stuaddress;
}
public void setStuaddress(String stuaddress) {
this.stuaddress = stuaddress;
}
}
6. 创建数据访问接口以及实现类
package com.wangxing.student.dao.impl;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.wangxing.student.bean.StudentBean;
import com.wangxing.student.dao.StudentDao;
public class StudentDaoImpl implements StudentDao{
private static StudentDaoImpl studentDao=null;
// 定义保存druid数据源对象
private DataSource dataSource = null;
private StudentDaoImpl() {
if(dataSource==null){
try {
Properties pro=new Properties();
InputStream inStream=this.getClass().getResourceAsStream("/mysqldata.properties");
pro.load(inStream);
dataSource=DruidDataSourceFactory.createDataSource(pro);
}catch(Exception e) {
e.printStackTrace();
}
}
}
public static StudentDaoImpl getStudentDaoImpl(){
if(studentDao==null){
studentDao=new StudentDaoImpl();
}
return studentDao;
}
@Override
public void insertStudent(StudentBean student)throws Exception{
Connection conn = dataSource.getConnection();
String insert = "insert into t_student values(null,?,?,?,?);";
PreparedStatement statement = conn.prepareStatement(insert);
statement.setString(1, student.getStuname());
statement.setString(2, student.getStupass());
statement.setInt(3, student.getStuage());
statement.setString(4, student.getStuaddress());
int temp = statement.executeUpdate();
statement.close();
conn.close();
}
@Override
public void updateStudent(StudentBean student)throws Exception{
Connection conn = dataSource.getConnection();
String update = "update t_student set stu_name=?,stu_pass=?,stu_age=?,stu_address=? where stu_id=?;";
PreparedStatement statement = conn.prepareStatement(update);
statement.setString(1, student.getStuname());
statement.setString(2, student.getStupass());
statement.setInt(3, student.getStuage());
statement.setString(4, student.getStuaddress());
statement.setInt(5, student.getStuid());
int temp = statement.executeUpdate();
statement.close();
conn.close();
}
@Override
public void deleteStudent(int stuid)throws Exception {
Connection conn = dataSource.getConnection();
String delete = "delete from t_student where stu_id=?;";
PreparedStatement statement = conn.prepareStatement(delete);
statement.setInt(1, stuid);
int temp = statement.executeUpdate();
statement.close();
conn.close();
}
@Override
public StudentBean selectStudentById(int stuid)throws Exception {
StudentBean student=null;
Connection conn = dataSource.getConnection();
String select = "select * from t_student where stu_id=?;";
PreparedStatement statement = conn.prepareStatement(select);
statement.setInt(1, stuid);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
int id = resultSet.getInt("stu_id");
String name = resultSet.getString("stu_name");
String pass = resultSet.getString("stu_pass");
int age = resultSet.getInt("stu_age");
String address = resultSet.getString("stu_address");
student= new StudentBean();
student.setStuid(id);
student.setStuname(name);
student.setStupass(pass);
student.setStuage(age);
student.setStuaddress(address);
}
resultSet.close();
statement.close();
conn.close();
return student;
}
@Override
public List<StudentBean> selectStudent()throws Exception {
List<StudentBean> studenlist=new ArrayList<StudentBean>();
Connection conn = dataSource.getConnection();
String select = "select * from t_student;";
PreparedStatement statement = conn.prepareStatement(select);
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()) {
int id = resultSet.getInt("stu_id");
String name = resultSet.getString("stu_name");
String pass = resultSet.getString("stu_pass");
int age = resultSet.getInt("stu_age");
String address = resultSet.getString("stu_address");
StudentBean student= new StudentBean();
student.setStuid(id);
student.setStuname(name);
student.setStupass(pass);
student.setStuage(age);
student.setStuaddress(address);
studenlist.add(student);
}
resultSet.close();
statement.close();
conn.close();
return studenlist;
}
@Override
public boolean selectStudentByNameAndPass(StudentBean stu) throws Exception{
boolean flag=true;
Connection conn = dataSource.getConnection();
String select = "select * from t_student where stu_name=? and stu_pass=?;";
PreparedStatement statement = conn.prepareStatement(select);
statement.setString(1, stu.getStuname());
statement.setString(2, stu.getStupass());
ResultSet resultSet = statement.executeQuery();
flag=resultSet.next();
resultSet.close();
statement.close();
conn.close();
return flag;
}
}
7. 创建业务访问接口以及实现类
package com.wangxing.student.service.impl;
import java.util.List;
import com.wangxing.student.bean.StudentBean;
import com.wangxing.student.dao.StudentDao;
import com.wangxing.student.service.StudentService;
public class StudentServiceImpl implements StudentService{
private StudentDao stuentDao=null;
public StudentServiceImpl(StudentDao stuentDao){
this.stuentDao=stuentDao;
}
@Override
public void insertStudent(StudentBean student) throws Exception {
stuentDao.insertStudent(student);
}
@Override
public void updateStudent(StudentBean student) throws Exception {
stuentDao.updateStudent(student);
}
@Override
public void deleteStudent(int stuid) throws Exception {
stuentDao.deleteStudent(stuid);
}
@Override
public StudentBean selectStudentById(int stuid) throws Exception {
return stuentDao.selectStudentById(stuid);
}
@Override
public List<StudentBean> selectStudent() throws Exception {
return stuentDao.selectStudent();
}
@Override
public boolean selectStudentByNameAndPass(StudentBean stu)throws Exception {
return stuentDao.selectStudentByNameAndPass(stu);
}
}
8. 创建具体业务处理的Servlet
package com.wangxing.student.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wangxing.student.bean.StudentBean;
import com.wangxing.student.dao.impl.StudentDaoImpl;
import com.wangxing.student.service.StudentService;
import com.wangxing.student.service.impl.StudentServiceImpl;
/**
* 处理添加操作的servlet
* @author Administrator
*
*/
public class StudentInsertServlet extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String stuname=req.getParameter("username");
String stupass=req.getParameter("password");
String stuage=req.getParameter("stuage");
String stuaddress=req.getParameter("stuaddress");
StudentBean student=new StudentBean();
student.setStuname(stuname);
student.setStupass(stupass);
student.setStuage(Integer.parseInt(stuage));
student.setStuaddress(stuaddress);
StudentService studentService=new StudentServiceImpl(StudentDaoImpl.getStudentDaoImpl());
try {
studentService.insertStudent(student);
resp.sendRedirect("studentselectlist");
} catch (Exception e) {
e.printStackTrace();
resp.sendRedirect("studentselectlist");
}
}
}
package com.wangxing.student.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wangxing.student.bean.StudentBean;
import com.wangxing.student.dao.impl.StudentDaoImpl;
import com.wangxing.student.service.StudentService;
import com.wangxing.student.service.impl.StudentServiceImpl;
/**
* 处理修改操作的servlet
* @author Administrator
*
*/
public class StudentUpdateServlet extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String stuid=req.getParameter("stuid");
String stuname=req.getParameter("username");
String stupass=req.getParameter("password");
String stuage=req.getParameter("stuage");
String stuaddress=req.getParameter("stuaddress");
StudentBean student=new StudentBean();
student.setStuid(Integer.parseInt(stuid));
student.setStuname(stuname);
student.setStupass(stupass);
student.setStuage(Integer.parseInt(stuage));
student.setStuaddress(stuaddress);
StudentService studentService=new StudentServiceImpl(StudentDaoImpl.getStudentDaoImpl());
try {
studentService.updateStudent(student);
resp.sendRedirect("studentselectlist");
} catch (Exception e) {
e.printStackTrace();
resp.sendRedirect("studentselectlist");
}
}
}
package com.wangxing.student.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wangxing.student.bean.StudentBean;
import com.wangxing.student.dao.impl.StudentDaoImpl;
import com.wangxing.student.service.StudentService;
import com.wangxing.student.service.impl.StudentServiceImpl;
/**
* 处理删除操作的servlet
* @author Administrator
*
*/
public class StudentDeleteServlet extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String stuid=req.getParameter("stuid");
StudentService studentService=new StudentServiceImpl(StudentDaoImpl.getStudentDaoImpl());
try {
studentService.deleteStudent(Integer.parseInt(stuid));
resp.sendRedirect("studentselectlist");
} catch (Exception e) {
e.printStackTrace();
resp.sendRedirect("studentselectlist");
}
}
}
package com.wangxing.student.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wangxing.student.bean.StudentBean;
import com.wangxing.student.dao.impl.StudentDaoImpl;
import com.wangxing.student.service.StudentService;
import com.wangxing.student.service.impl.StudentServiceImpl;
/**
* 处理根据id查询操作的servlet
* @author Administrator
*
*/
public class StudentSelectStudentServlet extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String stuid=req.getParameter("stuid");
StudentService studentService=new StudentServiceImpl(StudentDaoImpl.getStudentDaoImpl());
try {
StudentBean student=studentService.selectStudentById(Integer.parseInt(stuid));
req.setAttribute("student", student);
req.getRequestDispatcher("ebiter.jsp").forward(req, resp);
} catch (Exception e) {
e.printStackTrace();
}
}
}
package com.wangxing.student.servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wangxing.student.bean.StudentBean;
import com.wangxing.student.dao.impl.StudentDaoImpl;
import com.wangxing.student.service.StudentService;
import com.wangxing.student.service.impl.StudentServiceImpl;
/**
* 处理查询所有操作的servlet
* @author Administrator
*
*/
public class StudentSelectListServlet extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
StudentService studentService=new StudentServiceImpl(StudentDaoImpl.getStudentDaoImpl());
try {
List<StudentBean> studentlist=studentService.selectStudent();
req.setAttribute("studentlist", studentlist);
req.getRequestDispatcher("/main.jsp").forward(req, resp);
} catch (Exception e) {
e.printStackTrace();
}
}
}
package com.wangxing.sjj.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wangxing.sjj.bean.StudentBean;
import com.wangxing.sjj.dao.impl.StudentDaoImpl;
import com.wangxing.sjj.service.StudentService;
import com.wangxing.sjj.service.impl.StudentServiceImpl;
public class StudentLoginServlet extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String name=req.getParameter("username");
String pass=req.getParameter("password");
StudentBean stu=new StudentBean();
stu.setStuname(name);
stu.setStupass(pass);
StudentService studentService=new StudentServiceImpl(StudentDaoImpl.getStudentDaoImpl());
try{
boolean flag=studentService.selectStudentByNameAndPass(stu);
if(flag){
req.getSession().setAttribute("username", name);
req.getRequestDispatcher("studentselectlist").forward(req, resp);
}else{
req.setAttribute("error", "用户名密码错误!");
req.getRequestDispatcher("login.jsp").forward(req, resp);
}
}catch(Exception e){
e.printStackTrace();
req.setAttribute("error", "用户名密码错误!");
resp.sendRedirect("login.jsp");
}
}
}
package com.wangxing.sjj.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class StudentLogoutServlet extends HttpServlet{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.getSession().invalidate();
resp.sendRedirect("login.jsp");
}
}
9. 添加字符过滤器
10. 配置web.xml
11. 创建JSP页面
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>学生登陆</title>
</head>
<body>
<center>
<form action="login" method="post">
<table border="1px">
<tr align="center"><td colspan="2"><h1>学生登陆</h1></td></tr>
<tr align="center">
<td>学生账号</td>
<td><input type="text" name="username"></td>
</tr>
<tr align="center">
<td>登陆密码</td>
<td><input type="text" name="password"></td>
</tr>
<tr align="center">
<td colspan="2">
<input type="submit" name="登陆">
</td>
</tr>
</table>
<c:if test="${error!=''}">
<font color="red">${error}</font>
</c:if>
</form>
</center>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>主页</title>
</head>
<body>
<c:if test="${username==null}">
<jsp:forward page="login.jsp"></jsp:forward>
</c:if>
<center>
<table border="1px" width="800px">
<tr align="center"><td colspan="7"><h1>学生信息</h1></td></tr>
<tr align="left">
<td colspan="6"><h1><a href="ebiter.jsp">新增信息</a></h1></td>
<td><h1><a href="logout">退出</a></h1></td>
</tr>
<tr align="center">
<td>学生编号</td>
<td>学生姓名</td>
<td>学生密码</td>
<td>学生年龄</td>
<td>学生地址</td>
<td colspan="2">相关操作</td>
</tr>
<c:if test="${studentlist.size()==0}">
<tr align="center"><td colspan="7">没有记录</td></tr>
</c:if>
<c:if test="${studentlist.size()!=0}">
<c:forEach items="${studentlist}" var="stu">
<tr align="center">
<td>${stu.stuid}</td>
<td>${stu.stuname}</td>
<td>${stu.stupass}</td>
<td>${stu.stuage}</td>
<td>${stu.stuaddress}</td>
<td><a href="studentselectstudent?stuid=${stu.stuid}">修改</a></td>
<td><a href="studentdelete?stuid=${stu.stuid}">删除</a></td>
</tr>
</c:forEach>
</c:if>
</table>
</center>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>编辑信息</title>
</head>
<body>
<c:if test="${username==null}">
<jsp:forward page="login.jsp"></jsp:forward>
</c:if>
<center>
<c:if test="${student==null}">
<form action="studentinsert" method="post">
</c:if>
<c:if test="${student!=null}">
<form action="studentupdate" method="post">
<input type="hidden" name="stuid" value="${student.stuid}">
</c:if>
<table border="1px">
<tr align="center"><td colspan="2"><h1>编辑信息</h1></td></tr>
<tr align="center">
<td>学生账号</td>
<td><input type="text" name="username" value="${student.stuname}"></td>
</tr>
<tr align="center">
<td>登陆密码</td>
<td><input type="password" name="password" value="${student.stupass}"></td>
</tr>
<tr align="center">
<td>学生年龄</td>
<td><input type="text" name="stuage" value="${student.stuage}"></td>
</tr>
<tr align="center">
<td>学生地址</td>
<td><input type="text" name="stuaddress" value="${student.stuaddress}"></td>
</tr>
<tr align="center">
<td colspan="2">
<input type="submit" name="保存">
</td>
</tr>
</table>
</form>
</center>
</body>
</html>