一、配置web.xml
在WEB-INF目录下的web.xml中配置请求Servlet类的映射。这里是com.xxx.StudentSertvlet。同时在配置的请求中也可以设置初始化参数。见下图init-param。
<?xml version="1.0" encoding="utf-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
version="3.0" metadata-complete="false">
<servlet>
<servlet-name>StudentSertvlet</servlet-name>
<servlet-class>_03_MyJDBC.StudentSertvlet</servlet-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
<init-param>
<param-name>username</param-name>
<param-value>小明</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>StudentSertvlet</servlet-name>
<url-pattern>/index</url-pattern>
</servlet-mapping>
</web-app>
二、编写映射的Servlet类。
编写增删查改的方法。
public class StudentSertvlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private String encoding;
private StudentServiceImpl studentServiceImpl;// 要调用的service层对象
/**
* 此方法仅在servlet启动时初始化一次
*/
@Override
public void init(ServletConfig config) throws ServletException {
// 获取web.xml中的字符编码
String encoding = config.getInitParameter("encoding");
if (encoding != null) {
this.encoding = encoding;
}
studentServiceImpl = new StudentServiceImpl();
}
/**
* 重写servlet中的通用处理方法
*/
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding(encoding);
String cmd = req.getParameter("cmd");
String id = req.getParameter("id");
String keyword = req.getParameter("keyword");
String beginDate = req.getParameter("beginDate");
String endDate = req.getParameter("endDate");
String currentPage = req.getParameter("currentPage");
String pageSize = req.getParameter("pageSize");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
StudentQueryObject qo = new StudentQueryObject();
req.setAttribute("qo", qo);
if (StringUtil.hasLength(keyword)) {
qo.setKeyword(keyword);
}
if (StringUtil.hasLength(beginDate)) {
try {
qo.setBeginDate(sdf.parse(beginDate));
} catch (ParseException e) {
e.printStackTrace();
}
}
if (StringUtil.hasLength(endDate)) {
try {
qo.setEndDate(sdf.parse(endDate));
} catch (ParseException e) {
e.printStackTrace();
}
}
if (StringUtil.hasLength(currentPage)) {
qo.setCurrentPage(Integer.parseInt(currentPage));
}
if (StringUtil.hasLength(pageSize)) {
qo.setPageSize(Integer.parseInt(pageSize));
}
// crud相关操作
if ("save".equals(cmd)) {
Student stu = new Student();
String name = req.getParameter("name");
if (StringUtil.hasLength(name)) {
stu.setName(name);
}
String attendDate = req.getParameter("attendDate");
if (StringUtil.hasLength(attendDate)) {
try {
stu.setAttendDate(sdf.parse(attendDate));
} catch (ParseException e) {
e.printStackTrace();
}
}
String local = req.getParameter("local");
if (StringUtil.hasLength(local)) {
stu.setLocal(Boolean.valueOf(local));
}
// 如果存在Id就更新，若不存在就保存
if (StringUtil.hasLength(id)) {
stu.setId(Long.valueOf(id));
studentServiceImpl.update(stu);
} else {
studentServiceImpl.save(stu);
}
resp.sendRedirect("index");
} else if ("delete".equals(cmd)) {
if (StringUtil.hasLength(id)) {
studentServiceImpl.delete(Long.valueOf(id));
}
resp.sendRedirect("/index");
} else if ("edit".equals(cmd)) {
if (StringUtil.hasLength(id)) {
Student stu = studentServiceImpl.getOne(Long.valueOf(id));
req.setAttribute("stu", stu);
}
req.getRequestDispatcher("/WEB-INF/views/edit.jsp").forward(req, resp);
} else {
PageResult pageResult = studentServiceImpl.getAll(qo);
req.setAttribute("pageResult", pageResult);
req.getRequestDispatcher("/WEB-INF/views/list.jsp").forward(req, resp);
}
}
}
三、编写Service
一般service都要面向接口编程，这里为了节省时间， 就不写service的接口了。
public class StudentServiceImpl {
private StudentDAO dao = new StudentDAO();
public int save(Student stu) {
return dao.save(stu);
}
public int delete(Long id) {
return dao.delete(id);
}
public int update(Student stu) {
return dao.update(stu);
}
public Student getOne(Long id) {
return dao.getOne(id);
}
public PageResult getAll(StudentQueryObject qo) {
return dao.getAll(qo);
}
}四、编写DAO层
这里用到的StudentResultHandler处理结果，等一下再讲解。
public class StudentDAO {
public int save(Student stu) {
String sql = "insert into student(name,attendDate,local) values(?,?,?)";
int result = StudentResultHandler.update(sql, stu.getName(), stu.getAttendDate(), stu.isLocal());
return result;
}
public int delete(Long id) {
String sql = "delete from student where id=?";
int result = StudentResultHandler.update(sql, id);
return result;
}
public int update(Student stu) {
String sql = "update student set name=?,attendDate=?,local=? where id=?";
int result = StudentResultHandler.update(sql, stu.getName(), stu.getAttendDate(), stu.isLocal(), stu.getId());
return result;
}
public Student getOne(Long id) {
String sql = "select * from student where id=?";
List<Student> list = StudentResultHandler.query(sql, new ResultHandler<>(Student.class), id);
return list.get(0);
}
public PageResult getAll(StudentQueryObject qo) {
//查询总条数据
List<Object> params = qo.getParams();
String sql = "select count(*) from student" + qo.getQuery();
System.out.println(sql);
Integer totalCount = StudentResultHandler.query(sql, new IReslutHandler<Long>() {
@Override
public Long handler(ResultSet rs) throws Exception {
rs.next();
return rs.getLong(1);
}
}, params.toArray()).intValue();
//查询分页后的结果集
sql = "select * from student" + qo.getQuery() + " limit ?,?";
params.add((qo.getCurrentPage() - 1) * qo.getPageSize());
params.add(qo.getPageSize());
List<Student> listData = StudentResultHandler.query(sql, new ResultHandler<Student>(Student.class), params.toArray());
return new PageResult(qo.getCurrentPage(), qo.getPageSize(), totalCount, listData);
}
}五、StudentResultHandler的讲解
这里操作数据库，update方法传入sql和可变参数params，可进行参数占位符设置，从而可以进行简单的增、删、改操作。而query方法，除也sql和可变参数params外，还要把jdbc的行数据封装成对象通过泛型返回。
public class StudentResultHandler {
/**
*
* @param sql
* @param params
* @return
* @Description 描述:增、删、改操作
*/
public static int update(String sql, Object... params) {
int primaryKey = 0;
try {
Class<?> clazz = params.getClass();
Connection conn = JDBCUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
ps.executeUpdate();
//生成主键的返回,必须在生成主键之后
ResultSet keys = ps.getGeneratedKeys();
if (keys.next()) {
primaryKey = (int) keys.getLong(1);
}
} catch (Exception e) {
e.printStackTrace();
//资源的关闭
}
return primaryKey;
}
public static <T> T query(String sql, IReslutHandler<T> rsh, Object... params) {
try {
Connection con = JDBCUtil.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
//参数的设置
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
ResultSet rs = ps.executeQuery();
return rsh.handler(rs);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
同时这里也引入的jdbcUtil的工具类，如下：
public class JDBCUtil {
private static Connection connection = null;
//配置文件的加载
private static Properties p = null;
static {
try {
p = new Properties();
//获取类加载器
ClassLoader loader = Thread.currentThread().getContextClassLoader();
InputStream inputStream = loader.getResourceAsStream("db.properties");
p.load(inputStream);
//驱动类的加载
Class.forName(p.getProperty("driverClassName"));
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
//连接对象的 获取
connection = DriverManager.getConnection(p.getProperty("url"), p.getProperty("user"),
p.getProperty("password"));
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
user=root
password=admin
url=jdbc:mysql://localhost:3306/test
user=root
password=admin
六、IReslutHandler接口和实现处理类ResultHandler。
IReslutHandler接口，接口只是为了方便用来接收传入对象。
import java.sql.ResultSet;
public interface IReslutHandler<T> {
T handler(ResultSet rs) throws Exception;
}
public class ResultHandler<T> implements IReslutHandler<List<T>> {
private Class<T> type;
public ResultHandler(Class<T> type) {
this.type = type;
}
@Override
public List<T> handler(ResultSet rs) throws Exception {
List<T> list = new ArrayList<>();
while (rs.next()) {
T obj = type.newInstance();
BeanInfo beanInfo = Introspector.getBeanInfo(type, Object.class);
PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
for (PropertyDescriptor pd : pds) {
pd.getWriteMethod().invoke(obj, rs.getObject(pd.getName()));
}
list.add(obj);
}
return list;
}
}
七、实体
Student实体类
@Data
@NoArgsConstructor
public class Student {
private Long id;
private String name;
private Date attendDate;
private boolean local;
public Student(String name, Date attendDate, boolean local) {
this.name = name;
this.attendDate = attendDate;
this.local = local;
}
}@Setter
@Getter
public class StudentQueryObject {
private Integer currentPage = 1;
private Integer pageSize = 5;
private String keyword;
private Date beginDate;
private Date endDate;
List<String> conditions = new ArrayList<>();
List<Object> params = new ArrayList<>();
public void addQuery() {
if (StringUtil.hasLength(keyword)) {
this.conditions.add(" name like ? ");
this.params.add("%" + keyword + "%");
}
if (beginDate != null) {
this.conditions.add(" attendDate >= ? ");
this.params.add(beginDate);
}
if (endDate != null) {
this.conditions.add(" attendDate <= ?");
this.params.add(endDate);
}
}
public String getQuery() {
addQuery();
StringBuilder sql = new StringBuilder();
if (this.params.size() > 0) {
sql.append(" where ");
}
//用and分隔sql
String join = StringUtils.join(this.conditions, " and ");
sql.append(join);
//把他隔后的数据返回
return sql.toString();
}
}
分页对象，主要是封装分而集合，总条数、上一页、当前页等信息。
@Setter
@Getter
public class PageResult {
private Integer currentPage;
private Integer pageSize;
private Integer prePage;
private Integer nextPage;
private Integer totalPage;
private Integer totalCount;
private List<?> listData;
public PageResult(Integer currentPage, Integer pageSize, Integer totalCount, List<?> listData) {
this.currentPage = currentPage;
this.pageSize = pageSize;
this.totalCount = totalCount;
this.listData = listData;
this.totalPage = this.totalCount % this.pageSize == 0 ? this.totalCount / this.pageSize : this.totalCount
/ this.pageSize + 1;
this.prePage = this.currentPage > 1 ? this.currentPage - 1 : 1;
this.nextPage = this.currentPage < this.totalPage ? this.currentPage + 1 : this.totalPage;
}
}
八、附前端jsp页面
列表页面list.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>
<script type="text/javascript">
function goPage(currentPage) {
document.getElementById("currentPage").value = currentPage;
document.forms[0].submit();
}
</script>
</head>
<body>
<center>
<h1>学生信息列表</h1>
<form action="/index" method="post">
关键字:
<input name="keyword" value="${qo.keyword}" placeholder="名字">
开始日期：
<input name="beginDate" value="${qo.beginDate}" />
——
<input name="endDate" value="${qo.endDate}" />
<input type="submit" value="提交" />
<table border="1px" cellpadding="5px" cellspacing="0" width="80%"
style="text-align: center; cursor: pointer;">
<tr>
<td colspan="5">
<a href="${pageContext.request.contextPath}/index?cmd=edit">增加</a>
</td>
</tr>
<tr>
<th>序号</th>
<th>姓名</th>
<th>入学日期</th>
<th>当地 人</th>
<th>操作</th>
</tr>
<c:forEach items="${pageResult.listData}" var="stu" varStatus="vs">
<tr style='background-color:${vs.count%2==0?"gray":""}'>
<td>${vs.count}</td>
<td>${stu.name}</td>
<td>${stu.attendDate }</td>
<td>${stu.local }</td>
<td>
<a
href="${pageContext.request.contextPath}/index?id=${stu.id}&cmd=edit">编辑</a>
<a
href="${pageContext.request.contextPath}/index?id=${stu.id}&cmd=delete">删除</a>
</td>
</tr>
</c:forEach>
</table>
共条${pageResult.totalCount}条数据
<a href="javascript:goPage(1)">首页</a>
<a href="javascript:goPage(${pageResult.prePage})">上一页</a>
<a href="javascript:goPage(${pageResult.nextPage})">下一页</a>
<a href="javascript:goPage(${pageResult.totalPage})">尾页</a>
页面大小:
<input name="pageSize" value="${qo.pageSize}" />
<input type="hidden" name="currentPage" id="currentPage">
<input type="submit" value="提交" />
</form>
</center>
</body>
</html>编辑页面
<%@ 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>
</head>
<body>
<center>
<h1>${stu==null?"增加":"编辑"}学生</h1>
<form action="/index?cmd=save" method="post">
<input type="hidden" name="id" value="${stu.id}">
<table align="center">
<tr>
<td>姓名：</td>
<td>
<input name="name" value="${stu.name}" />
</td>
</tr>
<tr>
<td>入学日期：</td>
<td>
<input name="attendDate" value="${stu.attendDate}" />
</td>
</tr>
<tr>
<td>本地人：</td>
<td>
<input name="local" value="${stu.local}" />
</td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="提交" />
</td>
</tr>
</table>
</form>
</center>
</body>
</html>
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
