Fruit
1 项目准备
1.1创建一个web项目
1.2Tomcat热部署
1.3项目起步测试
1.4数据库fruit及表fruit、user的创建
fruit表
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80029
Source Host : localhost:3306
Source Schema : fruit
Target Server Type : MySQL
Target Server Version : 80029
File Encoding : 65001
Date: 06/10/2022 14:53:13
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for fruit
-- ----------------------------
DROP TABLE IF EXISTS `fruit`;
CREATE TABLE `fruit` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`category` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`varieties` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`origin` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`weight` int(0) NULL DEFAULT NULL,
`price` double NOT NULL,
`synopsis` tinytext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 63 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of fruit
-- ----------------------------
INSERT INTO `fruit` VALUES (1, '苹果', '红富士', '山东', 1000, 16, '果面洁净、色泽鲜艳、酸甜适度,品质优良');
INSERT INTO `fruit` VALUES (2, '梨', '皇冠梨', '河北', 800, 9, '好吃');
INSERT INTO `fruit` VALUES (3, '苹果', '红富士', '河北', 900, 14, '好吃');
INSERT INTO `fruit` VALUES (4, '苹果', '红富士', '山东', 1000, 12, '好吃');
INSERT INTO `fruit` VALUES (31, '梨', '皇冠梨', '河北', 800, 9, '1');
INSERT INTO `fruit` VALUES (32, '苹果', '红富士', '河北', 800, 12, '2');
INSERT INTO `fruit` VALUES (33, '梨', '皇冠梨', '河北', 150, 9, '3');
INSERT INTO `fruit` VALUES (34, '苹果', '红富士苹果', '河北', 800, 8, '1');
INSERT INTO `fruit` VALUES (36, '苹果', '红富士苹果', '河北', 800, 8, '1');
INSERT INTO `fruit` VALUES (37, '苹果', '红富士苹果', '河北', 800, 8, '1');
INSERT INTO `fruit` VALUES (38, '苹果', '红富士苹果', '河北', 800, 8, '1');
INSERT INTO `fruit` VALUES (40, '苹果', '皇冠梨', '河北', 150, 12, '1');
INSERT INTO `fruit` VALUES (41, '苹果', '皇冠梨', '河北', 150, 9, '1');
INSERT INTO `fruit` VALUES (43, '梨', '皇冠梨', '河北', 800, 12, '2');
INSERT INTO `fruit` VALUES (44, '梨', '皇冠梨', '河北', 800, 12, '2');
INSERT INTO `fruit` VALUES (46, '苹果', '红富士苹果', '河北', 800, 9, '1');
INSERT INTO `fruit` VALUES (47, '梨', '皇冠梨', '河北', 150, 9, '4444');
INSERT INTO `fruit` VALUES (50, '梨', '皇冠梨', '河北', 800, 9, '123');
INSERT INTO `fruit` VALUES (51, '', '', '', 0, 0, '');
INSERT INTO `fruit` VALUES (52, '梨', '皇冠梨', '河北', 800, 9, '1111111111');
INSERT INTO `fruit` VALUES (53, '苹果', '红富士苹果', '河北', 800, 12, '1222222');
INSERT INTO `fruit` VALUES (54, '梨', '皇冠梨', '河北', 800, 12, '33333');
INSERT INTO `fruit` VALUES (55, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (56, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (57, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (58, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (59, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (60, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (61, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
INSERT INTO `fruit` VALUES (62, '苹果', '红富士苹果', '河北', 150, 9, '33333333333333');
SET FOREIGN_KEY_CHECKS = 1;
user表
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80029
Source Host : localhost:3306
Source Schema : fruit
Target Server Type : MySQL
Target Server Version : 80029
File Encoding : 65001
Date: 06/10/2022 14:53:20
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`password` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'Anna', '123');
INSERT INTO `user` VALUES (2, 'lily', '123456');
SET FOREIGN_KEY_CHECKS = 1;
1.5基本配置
1.5.1 项目所需要的jar包
1.5.2 c3p0数据库连接数据库
1)连接数据库xml配置
<c3p0-config>
<!--使用默认的配置读取连接池对象-->
<default-config>
<!--连接参数-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/fruit?useSSL=true&characterEncoding=UTF8</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- 连接池参数 -->
<!--初始化申请的连接数量-->
<property name="initialPoolSize">5</property>
<!--最大的连接数量-->
<property name="maxPoolSize">10</property>
<!--超时时间-->
<property name="checkoutTimeout">3000</property>
</default-config>
</c3p0-config>
2)工具类
package com.fc.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Jdbc工具类
*/
public class JdbcUtilsOnC3P0 {
// 提取资源
private static Connection connection = null;
private static Statement statement = null;
private static ResultSet resultSet = null;
// 获取C3P0连接池对象
private static ComboPooledDataSource pool = new ComboPooledDataSource();
// 获取连接
public static Connection getConnection() {
try {
connection = pool.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
// 关闭方法
public static void close(Connection connection) {
close(resultSet, statement, connection);
}
public static void close(Statement statement) {
close(resultSet, statement, connection);
}
public static void close(Statement statement, Connection connection) {
close(resultSet, statement, connection);
}
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2 字符编码过滤器
通用字符编码过滤器的分析:
网站,需要向后台提交中文的数据(有可能是GET也有可能是POST)。中文处理根据不同的请求方式,处理的方式也是不一样的。
需要调用request.getParameter();
方法接收数据,但是这个时候无论是get还是post接收的数据都是存在乱码。
现在调用request.getParameter()方法无论是get还是post请求提交的中文,都没有乱码。
package com.fc.Filter;
import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebFilter(urlPatterns = "/*",initParams = {
@WebInitParam(name="characterEncoding",value = "UTF8"),
@WebInitParam(name="contextType",value = "text/html;charset=UTF-8")
})
public class EncodingFilter implements Filter {
//声明编码集和文本内容
private static String characterEncoding;
private static String contextType;
@Override
public void init(FilterConfig filterConfig) throws ServletException {
characterEncoding = filterConfig.getInitParameter("characterEncoding");
contextType=filterConfig.getInitParameter("contextType");
}
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
//把请求对象和响应对象强转为支持http协议的对象
HttpServletRequest request=(HttpServletRequest)servletRequest;
HttpServletResponse response=(HttpServletResponse)servletResponse;
request.setCharacterEncoding(characterEncoding);
response.setContentType(contextType);
filterChain.doFilter(request,response);
}
@Override
public void destroy() {
}
}
3 登录功能实现
3.1 登录过滤器
(1) 强转,让请求支持HTTP协议
(2) 获取session
(3) 获取请求URI
(4) 判断是否是登录页面,是登录页面则放行进入登录页面
(5) 已登录(session及session中的属性不为空,说明是已登录状态),则放行
(6) 未登录,则直接跳转至登录页面
注意:
getSession(boolean create)意思是返回当前reqeust中的HttpSession
如果当前reqeust中的HttpSession 为null; 当create为true,就创建一个新的Session,否则返回null;
代码实现
package com.fc.Filter;
import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
/**
* 自动登录过滤器
*/
@WebFilter("/*")
public class AutoLoginFilter implements Filter {
@Override
public void init(FilterConfig filterConfig) throws ServletException {
}
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
//强转,让请求支持HTTP协议
HttpServletRequest request=(HttpServletRequest)servletRequest;
HttpServletResponse response=(HttpServletResponse) servletResponse;
//获取session
HttpSession session = request.getSession(false);
//获取请求URI
String uri = request.getRequestURI();
//判断是否是登录页面,是登录页面则放行进入登录页面
if (uri.endsWith("/login.jsp")||uri.endsWith("/login")){
filterChain.doFilter(request,response);
}else if (session!=null&&session.getAttribute("user")!=null){
//已登录(session及session中的属性不为空,说明是已登录状态),则放行
filterChain.doFilter(request,response);
}else {
//未登录,则直接跳转至登录页面
response.sendRedirect("login.jsp");
}
}
@Override
public void destroy() {
}
}
3.2登录jsp页面
<%--
Created by IntelliJ IDEA.
User: HP
Date: 2022/10/7
Time: 8:18
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>登录</title>
<!-- 最新版本的 Bootstrap 核心 CSS 文件 -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
<style type="text/css">
body{
background-color: aquamarine;
}
.login{
width: 500px;
margin: 150px auto;
}
</style>
</head>
<body>
<form class="form-horizontal" action="${pageContext.request.contextPath}/login" method="post">
<div class="main">
<div class="login">
<div class="form-group">
<label for="inputEmail3" class="col-sm-2 control-label">用户名</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputEmail3" placeholder="请输入用户名" name="username">
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">密 码</label>
<div class="col-sm-10">
<input type="password" class="form-control" id="inputPassword3" placeholder="请输入密码" name="password">
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-default">登录</button>
</div>
</div>
</div>
</div>
</form>
</body>
</html>
3.3登录代码实现
package com.fc.servlet;
import com.fc.bean.User;
import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
@WebServlet("/login")
public class LoginServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//获取参数
String username = req.getParameter("username");
String password = req.getParameter("password");
//连接数据库
//获取核心类对象
QueryRunner queryRunner = new QueryRunner();
//获取数据库连接
Connection connection = JdbcUtilsOnC3P0.getConnection();
//准备SQL语句
String sql="select * from user where username= ? and password= ?";
//准备参数
Object[] params={username,password};
List<User> user =null;
//执行SQL语句并获取实体类对象
try {
user = queryRunner.query(connection, sql, new BeanListHandler<>(User.class), params);
} catch (SQLException e) {
e.printStackTrace();
}
if (user!=null){
//登录成功
HttpSession session = req.getSession(true);
//设置过期时间
session.setMaxInactiveInterval(60*60);
//设置属性键值对
session.setAttribute("user",user);
//获取一个cookie
Cookie cookie = new Cookie("JSESSIONID", session.getId());
//设置cookie的过期时间
cookie.setMaxAge(60*60);
//将cookie发送到浏览器
resp.addCookie(cookie);
//跳转到主页
resp.sendRedirect("index.jsp");
}else{
//登录失败
resp.sendRedirect("login.jsp");
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
4 水果信息展示
4.1 页面信息实体类(PageInfo)
变量信息:
int pageCount;//总页数
int totalCount;//总数据量,从数据库获取
int pageSize;//每页显示的数据量
int pageNo;//当前页,从前端获取
List list;//每一页中的所有数据
package com.fc.bean;
import java.util.List;
/**
* 分页信息类
* @param <T>泛型,指明我们要查询的内容
*/
public class PageInfo<T> {
private int pageCount;//总页数
private int totalCount;//总数据量,从数据库获取
private int pageSize;//每页显示的数据量
private int pageNo;//当前页,从前端获取
//泛型类
private List<T> list;//每一页中的所有数据
public PageInfo() {
}
public PageInfo(int totalCount, int pageSize, int pageNo, List<T> list) {
this.totalCount = totalCount;
this.pageSize = pageSize;
this.pageNo = pageNo;
this.list = list;
//判断总数据量能否整除每一条显示的数据
if(totalCount%pageSize==0){
this.pageCount=totalCount/pageSize;
}else {
this.pageCount=totalCount/pageSize+1;
}
}
@Override
public String toString() {
return "PageInfo{" +
"pageCount=" + pageCount +
", totalCount=" + totalCount +
", pageSize=" + pageSize +
", pageNo=" + pageNo +
", list=" + list +
'}';
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageNo() {
return pageNo;
}
public void setPageNo(int pageNo) {
this.pageNo = pageNo;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
}
4.2页面信息service
package com.fc.service;
import com.fc.bean.Fruit;
import com.fc.bean.PageInfo;
import com.fc.dao.PageDao;
import java.util.List;
public class PageService {
public PageInfo<Fruit> getPageInfo(String pageNo,int pageSize){
//判断当前页码是否存在
if (pageNo==null){
pageNo="1";
}
//转型,把String类型的当前页转为int类型
int currentPage = Integer.parseInt(pageNo);
//获取每页中的第一条数据-->start
int start=(currentPage-1)*pageSize;
//创建一个对象,用来进行数据库相关的操作
PageDao pageDao = new PageDao();
//获取每一页中的所有数据
List<Fruit> list = pageDao.getDataList(start, pageSize);
//获取总数据量
int totalCount = pageDao.getTotalCount();
//创建一个分页信息对象
PageInfo<Fruit> pageInfo = new PageInfo<>(totalCount, pageSize, currentPage, list);
//进项传递
return pageInfo;
}
}
4.3页面信息dao(数据访问层,用来进行数据库相关的操作)
package com.fc.dao;
import com.fc.bean.Fruit;
import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* 数据访问层,用来进行数据库相关的操作
*/
public class PageDao {
//获取核心类对象
private QueryRunner queryRunner = new QueryRunner();
//获取数据库的连接
private Connection connection = JdbcUtilsOnC3P0.getConnection();
/**
*获取指定页中的所有数据
*
* @param start 指定页的起始
* @param pageSize 每页查询的条数
* @return 返回一个包含实体类对象的list集合
*/
public List<Fruit> getDataList(int start, int pageSize) {
//准备sql语句
String sql="select * from fruit limit ?,?";
//准备参数
Object[] params={start,pageSize};
//提取list集合
List<Fruit> fruits=null;
try {
//执行sql语句并获取list结果集
fruits= queryRunner.query(connection, sql, new BeanListHandler<>(Fruit.class), params);
} catch (SQLException e) {
e.printStackTrace();
}
return fruits;
}
/**
* 获取总数据量
* @return 总数据量
*/
public int getTotalCount() {
// 准备SQL语句
String sql = "select * from fruit";
// 提取List
List< Fruit> list = null;
try {
// 执行SQL语句并获取所有数据对应的List集合
list = queryRunner.query(connection, sql, new BeanListHandler<>(Fruit.class));
} catch (SQLException e) {
e.printStackTrace();
}
// 获取集合中元素的个数并返回
return list.size();
}
}
4.4页面信息controller(控制层)
写Servlet,用于和前端进行交互
package com.fc.controller;
import com.fc.bean.Fruit;
import com.fc.bean.PageInfo;
import com.fc.service.PageService;
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 java.io.IOException;
/**
* 控制层,写Servlet。用于和前端进行交互
*/
@WebServlet("/page")
public class PageServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 从前端获取参数
String pageNo = req.getParameter("pageNo");
// 声明每页显示的条数
int pageSize = 5;
// 声明一个水果服务对象
PageService pageService = new PageService();
// 从服务对象中获取我需要的PageInfo
PageInfo<Fruit> pageInfo = pageService.getPageInfo(pageNo, pageSize);
//给域对象中设置属性参数键值对
req.setAttribute("pageInfo",pageInfo);
System.out.println(pageInfo);
//转发到主页面展示
req.getRequestDispatcher("index.jsp").forward(req,resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet (req, resp);
}
}
4.5页面信息jsp
<%--
Created by IntelliJ IDEA.
User: HP
Date: 2022/10/13
Time: 12:54
To change this template use File | Settings | File Templates.
--%>
<%@ page import="org.apache.commons.dbutils.QueryRunner" %>
<%@ page import="com.fc.utils.JdbcUtilsOnC3P0" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.util.List" %>
<%@ page import="com.fc.bean.Fruit" %>
<%@ page import="org.apache.commons.dbutils.handlers.BeanListHandler" %>
<%@ page import="java.sql.SQLException" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>水果查询</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
<script src="js/jquery-3.5.1.js"></script>
<style type="text/css">
.fruits-list{
width: 500px;
margin: 60px auto;
}
table{
text-align: center;
}
th{
text-align: center;
}
a{
text-decoration: none;
}
</style>
</head>
<body>
<div class="main">
<div class="fruits-list">
<a href="add.jsp">添加水果</a>
<table class="table table-striped table-bordered table-hover" >
<tr>
<th>ID</th>
<th>类别</th>
<th>品种</th>
<th>产地</th>
<th>总重量(公斤)</th>
<th>单价(元/公斤)</th>
<th>删除</th>
<th>详情</th>
</tr>
<c:forEach var="fruit" items="${pageInfo.list}">
<tr>
<td>${fruit.getId()}</td>
<td>${fruit.getCategory()}</td>
<td>${fruit.getVarieties()}</td>
<td>${fruit.getOrigin()}</td>
<td>${fruit.getWeight()}</td>
<td>${fruit.getPrice()}</td>
<td><a href="delete?id=${fruit.getId()}" class="glyphicon glyphicon-trash" ></a></td>
<td><a href="detail?id=${fruit.getId()}" class="glyphicon glyphicon-edit"></a></td>
</tr>
</c:forEach>
<form action="page" method="get">
<table align="center">
<tr>
<%--首页和上一页--%>
<c:if test="${pageInfo.pageNo == 1}">
<td><a href="javascript:return false;" style="color: orangered">首页</a></td>
<td><a href="javascript:return false;" style="color: orangered">上一页</a></td>
</c:if>
<c:if test="${pageInfo.pageNo != 1}">
<td><a href="page?pageNo=1">首页</a></td>
<td><a href="page?pageNo=${pageInfo.pageNo - 1}">上一页</a></td>
</c:if>
<%--当前页--%>
<td>
<c:forEach var="pageNo" begin="1" end="${pageInfo.pageCount}">
<c:if test="${pageNo == pageInfo.pageNo}">
<input type="submit" name="pageNo" value="${pageNo}" disabled>
</c:if>
<c:if test="${pageNo != pageInfo.pageNo}">
<input type="submit" name="pageNo" value="${pageNo}">
</c:if>
</c:forEach>
</td>
<%--下一页和尾页--%>
<c:if test="${pageInfo.pageNo == pageInfo.pageCount}">
<td><a href="javascript:return false;" style="color: orangered">下一页</a></td>
<td><a href="javascript:return false;" style="color: orangered">尾页</a></td>
</c:if>
<c:if test="${pageInfo.pageNo != pageInfo.pageCount}">
<td><a href="page?pageNo=${pageInfo.pageNo + 1}">下一页</a></td>
<td><a href="page?pageNo=${pageInfo.pageCount}">尾页</a></td>
</c:if>
</tr>
</table>
</form>
</div>
</div>
</body>
</html>
4.6实现效果展示
5 详情信息展示
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>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
<style type="text/css">
.detail{
width: 800px;
margin: 0 auto;
}
</style>
</head>
<body>
<div class="main">
<div class="detail">
<%--<a href="${pageContext.request.contextPath}/fruits">返回上一页</a>--%>
<table class="table table-striped table-hover" >
<tr>
<td>ID</td>
<td>${fruit.id}</td>
</tr>
<tr>
<td>类别</td>
<td>${fruit.category}</td>
</tr>
<tr>
<td>品种</td>
<td>${fruit.varieties}</td>
</tr>
<tr>
<td>产地</td>
<td>${fruit.origin}</td>
</tr>
<tr>
<td>总重量(公斤)</td>
<td>${fruit.weight}</td>
</tr>
<tr>
<td>单价(元/公斤)</td>
<td>${fruit.price}</td>
</tr>
<tr>
<td>详情</td>
<td>${fruit.synopsis}</td>
</tr>
</table>
</div>
</div>
</body>
</html>
servlet
package com.fc.servlet;
import com.fc.bean.Fruit;
import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
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 java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
@WebServlet("/detail")
public class DetailFruitServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
//获取参数id
Integer id = Integer.parseInt(req.getParameter("id"));
System.out.println (id);
//连接数据库,根据id,进行水果信息排查
QueryRunner queryRunner = new QueryRunner ();
Connection connection = JdbcUtilsOnC3P0.getConnection ();
String sql="select * from fruit where id=?";
Object[] params={id};
Fruit fruit=null;
try {
fruit = queryRunner.query (connection, sql, new BeanHandler<Fruit> (Fruit.class), params);
} catch ( SQLException e ) {
e.printStackTrace ();
}
if (fruit!=null){
//给域对象中设置属性参数键值对
req.setAttribute ("fruit",fruit);
System.out.println (fruit);
//转发到详情页面
req.getRequestDispatcher ("detail.jsp").forward (req,resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet (req, resp);
}
}
6 删除一条数据
package com.fc.servlet;
import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.dbutils.QueryRunner;
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 java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
@WebServlet("/delete")
public class DeleteFruitServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
PrintWriter out = resp.getWriter();
String new_id = req.getParameter ("id");
//连接数据库,根据id进行排查
QueryRunner queryRunner = new QueryRunner ();
Connection connection = JdbcUtilsOnC3P0.getConnection ();
String sql = "delete from fruit where id = ?";
System.out.println (new_id);
Object[] params={new_id};
int affectedRows = 0;
try {
affectedRows=queryRunner.update (connection,sql,params);
} catch ( SQLException e ) {
e.printStackTrace ();
}
System.out.println (affectedRows);
//判断是否存在
if (affectedRows!=0){
out.print ("删除成功!");
}else {
out.print ("删除失败");
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet (req, resp);
}
}
7 添加一条数据
jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>添加水果页面</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css" integrity="sha384-HSMxcRTRxnN+Bdg0JdbxYKrThecOKuH5zCYotlSAcp1+c8xmyTe9GYg1l9a69psu" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
<script src="js/jquery-3.5.1.js"></script>
<style type="text/css">
.add-fruits{
width: 500px;
margin: 0 auto;
}
</style>
<script type="text/javascript">
function add() {
var category= $("input[name='category']").val();
var varieties = $("input[name='varieties']").val();
var origin = $("input[name='origin']").val();
var weight = $("input[name='weight']").val();
var price = $("input[name='price']").val();
var synopsis = $("#synopsis").val();
$.ajax({
url:"add",
type:"post",
data: {
category:category,
varieties:varieties,
origin:origin,
weight:weight,
price:price,
synopsis:synopsis
},
success:function () {
// $("#addFruits")[0].reset();
alert("添加成功");
window.location.href="${pageContext.request.contextPath}/index.jsp";
},
error:function () {
alert("添加失败")
}
});
}
</script>
</head>
<body>
<div class="main">
<div class="add-fruits">
<form class="form-horizontal" id="addFruits">
<div class="form-group">
<label for="inputEmail3" class="col-sm-2 control-label">产 品</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputEmail3" placeholder="请输入产品名" name="category" >
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">品 种</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputPassword3" placeholder="请输入品种" name="varieties" >
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">产 地</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputPassword3" placeholder="请输入产地名" name="origin" >
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">重 量</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputPassword3" placeholder="请输入总重量" name="weight" >
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">价 格</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputPassword3" placeholder="请输入价格" name="price" >
</div>
</div>
<div class="form-group">
<label for="inputPassword3" class="col-sm-2 control-label">详细信息</label>
<div class="col-sm-10">
<textarea class="form-control" rows="3" placeholder="请输入详细信息" name="synopsis" id="synopsis"></textarea>
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="button" class="btn btn-default" onclick="add()">提交</button>
<button type="reset" class="btn btn-default">重置</button>
</div>
</div>
</form>
</div>
</div>
</body>
</html>
servlet
package com.fc.servlet;
import com.fc.bean.Fruit;
import com.fc.utils.JdbcUtilsOnC3P0;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
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 java.io.IOException;
import java.io.PrintWriter;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
@WebServlet("/add")
public class AddFruitServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
try {
PrintWriter out = resp.getWriter();
//获取参数
Map<String, String[]> map = req.getParameterMap ();
//声明一个水果类对象
Fruit fruit = new Fruit ();
//使用BeanUtils进行封装
BeanUtils.populate (fruit,map);
//操作数据库
QueryRunner queryRunner = new QueryRunner ();
// 获取数据库的连接
Connection connection = JdbcUtilsOnC3P0.getConnection();
// 准备SQL语句
String sql = "insert into fruit(category,varieties,origin,weight,price,synopsis) values(?,?,?,?,?,?)";
// 准备参数
Object[] params = {fruit.getCategory (),fruit.getVarieties (),fruit.getOrigin (),fruit.getWeight (),fruit.getPrice (),fruit.getSynopsis ()};
// 执行SQL语句
int affectedRows = queryRunner.update(connection, sql, params);
// System.out.println (affectedRows);
if (affectedRows > 0) {
System.out.println ("q");
// resp.sendRedirect("index.jsp");
req.getRequestDispatcher ("index.jsp").forward (req,resp);
} else {
out.print ("添加失败!");
}
} catch ( IllegalAccessException |InvocationTargetException e ) {
e.printStackTrace ();
} catch ( SQLException e ) {
e.printStackTrace ();
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet (req, resp);
}
}
取数据库的连接
Connection connection = JdbcUtilsOnC3P0.getConnection();
// 准备SQL语句
String sql = “insert into fruit(category,varieties,origin,weight,price,synopsis) values(?,?,?,?,?,?)”;
// 准备参数
Object[] params = {fruit.getCategory (),fruit.getVarieties (),fruit.getOrigin (),fruit.getWeight (),fruit.getPrice (),fruit.getSynopsis ()};
// 执行SQL语句
int affectedRows = queryRunner.update(connection, sql, params);
// System.out.println (affectedRows);
if (affectedRows > 0) {
System.out.println (“q”);
// resp.sendRedirect(“index.jsp”);
req.getRequestDispatcher (“index.jsp”).forward (req,resp);
} else {
out.print (“添加失败!”);
}
} catch ( IllegalAccessException |InvocationTargetException e ) {
e.printStackTrace ();
} catch ( SQLException e ) {
e.printStackTrace ();
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet (req, resp);
}
}