搭建好项目记得导包:
连接池(根据自己的配置改):
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf-8&useSSL=false
user=root
password=994994
连接数据库工具类:
package com.accp.dao;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
public class BaseDao {
private static String driver;
private static String url;
private static String user;
private static String password;
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
static {
Properties params = new Properties();
String configFile = "database.properties";
InputStream is = BaseDao.class.getClassLoader().getResourceAsStream(configFile);
try {
params.load(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
driver=params.getProperty("driver");
url=params.getProperty("url");
user=params.getProperty("user");
password=params.getProperty("password");
}
//连接
public Connection getConnection() {
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//关闭流
public void closeAll() {
if (null != rs) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (null != pstmt) {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (null != conn) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//增删改
public int executeUpdate(String sql, Object... objects) {
getConnection();
int rows = 0;
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
pstmt.setObject(i + 1, objects[i]);
}
rows = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
closeAll();
}
return rows;
}
//查询
public ResultSet executeQuery(String sql, List<Object> list) {
getConnection();
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < list.size(); i++) {
pstmt.setObject(i + 1, list.get(i));
}
rs = pstmt.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
}
实体类:
package com.accp.entity;
public class StudentInfo {
private Integer sId;
private String sName;
private String sGender;
private Integer sAge;
private String sAddress;
private String sEmail;
public Integer getsId() {
return sId;
}
public void setsId(Integer sId) {
this.sId = sId;
}
public String getsName() {
return sName;
}
public void setsName(String sName) {
this.sName = sName;
}
public String getsGender() {
return sGender;
}
public void setsGender(String sGender) {
this.sGender = sGender;
}
public Integer getsAge() {
return sAge;
}
public void setsAge(Integer sAge) {
this.sAge = sAge;
}
public String getsAddress() {
return sAddress;
}
public void setsAddress(String sAddress) {
this.sAddress = sAddress;
}
public String getsEmail() {
return sEmail;
}
public void setsEmail(String sEmail) {
this.sEmail = sEmail;
}
}
Dao层:
package com.accp.dao;
import java.util.List;
import com.accp.entity.StudentInfo;
public interface StudentInfoDao {
public List<StudentInfo> select(StudentInfo si);
public int update(StudentInfo si);
public int delete(StudentInfo si);
}
Dao层Impl实现类:
package com.accp.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.accp.dao.BaseDao;
import com.accp.dao.StudentInfoDao;
import com.accp.entity.StudentInfo;
public class StudentInfoDaoImpl extends BaseDao implements StudentInfoDao{
@Override
public List<StudentInfo> select(StudentInfo si) {
// TODO Auto-generated method stub
String sql = "select * from studentInfo where 1=1";
List<StudentInfo> list = new ArrayList<StudentInfo>();
List<Object> olist = new ArrayList<Object>();
if(si.getsId()!=null) {
sql += " and sid=?";
olist.add(si.getsId());
}
try {
ResultSet rs = this.executeQuery(sql, olist);
while(rs.next()) {
StudentInfo sis = new StudentInfo();
sis.setsId(rs.getInt("sid"));
sis.setsName(rs.getString("sname"));
sis.setsGender(rs.getString("sgender"));
sis.setsAge(rs.getInt("sage"));
sis.setsAddress(rs.getString("saddress"));
sis.setsEmail(rs.getString("semail"));
list.add(sis);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
this.closeAll();
}
return list;
}
@Override
public int update(StudentInfo si) {
// TODO Auto-generated method stub
String sql = "update studentInfo set sname=?,sgender=?,sage=?,saddress=?,semail=? where sid=?";
return this.executeUpdate(sql, si.getsName(),si.getsGender(),si.getsAge(),si.getsAddress(),si.getsEmail(),si.getsId());
}
@Override
public int delete(StudentInfo si) {
// TODO Auto-generated method stub
String sql = "delete from studentInfo where sid=?";
return this.executeUpdate(sql, si.getsId());
}
}
Service层:
package com.accp.service;
import java.util.List;
import com.accp.entity.StudentInfo;
public interface StudentInfoService {
public List<StudentInfo> select(StudentInfo si);
public int update(StudentInfo si);
public int delete(StudentInfo si);
}
Service层Impl实现类:
package com.accp.service.impl;
import java.util.List;
import com.accp.dao.StudentInfoDao;
import com.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;
import com.accp.service.StudentInfoService;
public class StudentInfoServiceImpl implements StudentInfoService{
StudentInfoDao sid = new StudentInfoDaoImpl();
@Override
public List<StudentInfo> select(StudentInfo si) {
// TODO Auto-generated method stub
return sid.select(si);
}
@Override
public int update(StudentInfo si) {
// TODO Auto-generated method stub
return sid.update(si);
}
@Override
public int delete(StudentInfo si) {
// TODO Auto-generated method stub
return sid.delete(si);
}
}
Servlet层删除:
package com.accp.servlet;
import java.io.IOException;
import java.io.PrintWriter;
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 com.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;
/**
* Servlet implementation class DeleteStudentInfoServlet
*/
@WebServlet("/dsis")
public class DeleteStudentInfoServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public DeleteStudentInfoServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
String id = request.getParameter("id"); //获得要删除的用户ID
StudentInfo si = new StudentInfo();
si.setsId(Integer.valueOf(id));
StudentInfoDaoImpl sis = new StudentInfoDaoImpl();
int count = sis.delete(si); //调用删除方法
//写入流
PrintWriter out = response.getWriter();
out.print(count);
out.flush();
out.close();
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
Servlet层修改:
package com.accp.servlet;
import java.io.IOException;
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 com.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;
/**
* Servlet implementation class UpdateStudentInfoServlet
*/
@WebServlet("/usis")
public class UpdateStudentInfoServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public UpdateStudentInfoServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
/*
* 获得修改后的学员信息
*/
String id = request.getParameter("id");
String name = request.getParameter("name");
name = new String(name.getBytes("ISO-8859-1"), "UTF-8");
String gender = request.getParameter("gender");
gender = new String(gender.getBytes("ISO-8859-1"), "UTF-8");
String age = request.getParameter("age");
String address = request.getParameter("address");
address = new String(address.getBytes("ISO-8859-1"), "UTF-8");
String email = request.getParameter("email");
// 申明学员对象存入值
StudentInfo si = new StudentInfo();
si.setsId(Integer.valueOf(id));
si.setsName(name);
si.setsGender(gender);
si.setsAge(Integer.valueOf(age));
si.setsAddress(address);
si.setsEmail(email);
StudentInfoDaoImpl sis = new StudentInfoDaoImpl();
// 调用修改方法
int count = sis.update(si);
// 修改后提示
if (count > 0) {
response.getWriter().write("<script>alert('修改成功!');window.location.href='ssis'</script>");
} else {
response.getWriter().write("<script>alert('修改失败!');history.go(-1)</script>");
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
Servlet层查询:
package com.accp.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 com.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;
/**
* Servlet implementation class SelectStudentInfoServlet
*/
@WebServlet("/ssis")
public class SelectStudentInfoServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public SelectStudentInfoServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
StudentInfo si = new StudentInfo();
StudentInfoDaoImpl sis = new StudentInfoDaoImpl();
List<StudentInfo> list = sis.select(si); //调用查询方法
request.setAttribute("list",list); //存入作用域
//转发
request.getRequestDispatcher("index.jsp").forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
Servlet层传值到修改页面查看:
package com.accp.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 com.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;
/**
* Servlet implementation class SelectStudentInfoByIdServlet
*/
@WebServlet("/ssibis")
public class SelectStudentInfoByIdServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public SelectStudentInfoByIdServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
String id = request.getParameter("id"); //获得要修改的学员ID
StudentInfo si = new StudentInfo();
si.setsId(Integer.valueOf(id));
StudentInfoDaoImpl sis = new StudentInfoDaoImpl();
List<StudentInfo> list = sis.select(si); //通过ID去查询该学员信息
request.setAttribute("info", list.get(0)); //将信息存入作用域
//转发
request.getRequestDispatcher("upd.jsp").forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
显示页面代码:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!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>Insert title here</title>
<script type="text/javascript" src="js/jquery-1.12.4.js"></script>
<script type="text/javascript">
//删除方法
function del(id) {
if (confirm("确认要删除该学员信息?")) { //确认是否删除
//调用ajax方法
$.ajax({
url : "dsis",
type : "get",
data : "id=" + id,
success : function(data) {
if (data > 0) {
alert("删除成功!");
window.location.href = "ssis";
} else {
alert("删除失败!");
}
}
})
}
}
</script>
<style type="text/css">
table tr:nth-of-type(even) {
background: #ccc;
}
</style>
</head>
<body>
<table align="center" border="1">
<tr align="center">
<td colspan="7" style="font-weight: bold;">学员信息列表</td>
</tr>
<tr align="center">
<td>学员编号</td>
<td>学员姓名</td>
<td>学员性别</td>
<td>学员年龄</td>
<td>家庭住址</td>
<td>Email</td>
<td>操作</td>
</tr>
<c:forEach items="${requestScope.list}" var="item">
<tr align="center">
<td>${item.sId}</td>
<td><a href="ssibis?id=${item.sId }">${item.sName}</a></td>
<td>${item.sGender}</td>
<td>${item.sAge}</td>
<td>${item.sAddress}</td>
<td>${item.sEmail}</td>
<td><a href="javascript:del(${item.sId})">删除</a></td>
</tr>
</c:forEach>
</table>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!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>Insert title here</title>
<script type="text/javascript" src="js/jquery-1.12.4.js"></script>
<script type="text/javascript">
//验证非空方法
function check(){
var name = $("#name").val();
var sex = $("#gender").val();
var age = $("#age").val();
var address = $("#address").val();
var email = $("#email").val();
if(name.trim().length==0){
alert("姓名不能为空");
return false;
}
if(sex.trim().length==0){
alert("性别不能为空");
return false;
}
if(age.trim().length==0){
alert("年龄不能为空");
return false;
}
return true;
}
</script>
</head>
<body>
<form action="usis" method="get">
<table border="1" align="center">
<tr align="center">
<td colspan="2"><h2>学员信息</h2></td>
</tr>
<tr align="center">
<td>学员姓名</td>
<td><input id="name" name="name" type="text" value="${requestScope.info.sName }"/></td>
</tr>
<tr align="center">
<td>学员性别</td>
<td><input id="gender" name="gender" type="text" value="${requestScope.info.sGender }"/></td>
</tr>
<tr>
<td>学员年龄</td>
<td><input id="age" name="age" type="text" value="${requestScope.info.sAge }"/></td>
</tr>
<tr align="center">
<td>家庭住址</td>
<td><input id="address" name="address" type="text" value="${requestScope.info.sAddress }"/></td>
</tr>
<tr align="center">
<td>Email</td>
<td><input id="email" name="email" type="text" value="${requestScope.info.sEmail }"/></td>
</tr>
<tr align="center">
<td colspan="2">
<!--将学员ID放入隐藏域 用于修改 -->
<input name="id" type="hidden" value="${requestScope.info.sId }"/>
<input type="submit" value="修改" onclick="return check()"/></td>
</tr>
</table>
</form>
</body>
</html>