项目需求
一个可以实现增删查改手机库存数据的网页，要求在网页上就能对数据进行操作，并且数据库中的数据也要实时同步。
项目规划
前端HTML，简单的页面，先不涉及CSS，直观的实现功能。
后端Java，实现增删查改的业务逻辑，前端的每个需求都要对应一个Servlet，Servlet在分别调用Service，Service再根据需求调用DAO。
数据库MYSQL，提前建立一个数据库，然后新建一个表，插入一条数据来测试功能是否完善。
开发流程
MYSQL数据库开发
HTML前端→resources→utils→model→Servlet→Service→DAO
MYSQL用于存放数据
HTML用于与用户交互
resources用户存放DruidUtils.java可以存放数据库的用户密码等等也可以不用，再utils中也可代替，所以也可以省略这一步骤。
uitls用于java获取数据库的连接，当然也可以把resources文件中的DuridUtils.java的信息写入这里，从而代替resources
model用于实例化数据库中的各个字段名，把他们转化成java中可用的变量、方法等等。
Servlet与前端HTML交互，可以接收前端传过来的参数然后传递给Service，然后将处理后的数据传回前端。
Service可以把Servlet传过来的请求与参数传递给DAO，然后再将得到的数据传递给Servlet
DAO负责直接操纵数据库，然后将查询结果返回给Service。
开发
MYSQL开发
创建数据库
create database product charset=utf8;
使用数据库
use product;
创建表
create table phone(
id int unsigned primary key auto_increment not null,
name varchar(20) not null,
brand varchar(20) not null,
price int,
addtime date,
inventory int
);
插入一条数据
insert into phone value('小米15ProMax','小米',6599,'2024-07-12',1500);
然后检查一下
select * from phone;
创建Maven工程
在java开发工具中File-New-Dynamic Web Project,创建一个Maven项目。
在Java Resources-src/main/java下右键New-Class路径com.model，文件名Product.java。
在Java Resources-src/main/java下右键New-Class路径com.service，文件名ProductService.java。
在Java Resources-src/main/java下右键New-Class路径com.dao，文件名ProductDAO.java。
在Java Resources-src/main/java下右键New-Class路径com.utils，文件名DruidUtils.java。
在Java Resources-src/main/java下右键New-Servlet路径com.servlet，文件名BrandSearchServlet.java、AddServlet.java、DeleteServlet.java、HetBrandServlet.java、PageServlet.java、SelectAllServlet.java、UpdateServlet.java。
然后在项目-src-main-webapp-WEB-INF右键创建一个search.html
前端开发
search.html代码
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>商品列表页</title>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body>
<h1 align="center">商品列表</h1><br/>
<div align="center">
<form>
<input type="text" id="name" name="name">
<label for="brand"></label>
<select id="brand" name="brand">
</select>
<input type="submit" id="searchBtn" value="查询">
</form><br/>
<h2>新增商品</h2>
<form id="addForm">
名称：<input type="text" name="addname"><br>
品牌：<input type="text" name="addbrand"><br>
价格：<input type="text" name="addprice"><br>
日期：<input type="text" name="addaddtime"><br>
库存：<input type="text" name="addinventory"><br>
<button type="button" onclick="addProduct()">新增</button>
</form>
<h4>查询结果:</h4>
<table border="1" id="productTable">
<tr>
<th>编号</th>
<th>商品名称</th>
<th>品牌</th>
<th>价格</th>
<th>进货日期</th>
<th>库存</th>
<th>操作</th>
</tr>
</table>
<!-- 分页按钮 -->
<div style="margin-top:10px">
<button onclick="prevPage()">上一页</button>
<span id="pageInfo">第 1 页</span>
<button onclick="nextPage()">下一页</button>
</div>
</div>
<script>
// ===================== 分页参数 =====================
let page = 1;
let size = 5;
let totalPage = 1;
// ===================== 页面加载 =====================
$(function(){
loadPageData();
loadBrands();
$("#searchBtn").click(function(e){
e.preventDefault();
page = 1;
loadPageData();
});
$("#brand").change(function(){
page = 1;
loadPageData();
});
});
// ===================== 【核心：分页加载】 =====================
// 分页加载
function loadPageData() {
//获取输入框的值
let name = $("#name").val();
let brand = $("#brand").val();
console.log("前端品牌：", brand); // 看这里输出！
$.ajax({
url: "page",//对应后端 @WebServlet("/page")
type: "get",//对应后端doget
data: {
page: page,
size: size,
name: name,
brand: brand
},
success: function(res){
//后端返回数据后，渲染表格
$("#productTable tr:not(:first)").remove();
let list = res.list || [];
for(let i=0; i<list.length; i++){
let p = list[i];
let tr = `
<tr>
<td>${p.id}</td>
<td>${p.name}</td>
<td>${p.brand}</td>
<td>${p.price}</td>
<td>${p.addtime}</td>
<td>${p.inventory}</td>
<td>
<button onclick='updateProduct(${p.id})'>修改</button>
<button onclick='deleteProduct(${p.id})'>删除</button>
</td>
</tr>`;
$("#productTable").append(tr);
}
let total = res.total || 0;
totalPage = total == 0 ? 1 : Math.ceil(total / size);
$("#pageInfo").text(`第 ${page} 页 / 共 ${totalPage} 页`);
}
});
}
// ===================== 上一页 =====================
function prevPage() {
if(page > 1) {
page--;
loadPageData();
}
}
// ===================== 下一页 =====================
function nextPage() {
console.log("当前页:"+page,"总页数:"+totalPage);
if(page < totalPage) {
page++;
loadPageData();
}
}
// ===================== 加载品牌 =====================
function loadBrands(){
$.get("HetBrandServlet", function(data){
$("#brand").empty();
$("#brand").append("<option value=''>全部品牌</option>");
for(var i=0;i<data.length;i++){
let b = data[i];
$("#brand").append(`<option value='${b}'>${b}</option>`);
}
});
}
// ===================== 新增 =====================
function addProduct(){
$.post("AddServlet",$("#addForm").serialize(),function(res){
alert("新增成功");
page=1;
loadPageData();
});
}
// ===================== 删除 =====================
function deleteProduct(id){
if(confirm("确定删除？")){
$.get("delete?id="+id, function(){
loadPageData();
});
}
}
// ===================== 修改 =====================
function updateProduct(id){
let name = prompt("请输入新商品名称");
let brand = prompt("请输入新品牌");
let price = prompt("请输入新价格");
let addtime=prompt("请输入新的进货时间");
let inventory = prompt("请输入新库存");
$.post("update", {
id: id,
name: name,
brand: brand,
price: price,
addtime:addtime,
inventory: inventory
}, function(res){
alert("修改成功！");
loadPageData();
});
}
</script>
</body>
</html>
Java开发
DruidUtils.java代码
package com.utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidUtils {
private static DataSource dataSource;
static {
try {
Properties pro = new Properties();
// 这里直接写死，不需要配置文件！
pro.setProperty("driverClassName", "com.mysql.cj.jdbc.Driver");
pro.setProperty("url", "jdbc:mysql://localhost:3306/product?useSSL=false&serverTimezone=Asia/Shanghai");
pro.setProperty("username", "root");
pro.setProperty("password", "123456");
pro.setProperty("initialSize", "5");
pro.setProperty("maxActive", "10");
pro.setProperty("maxWait", "3000");
dataSource = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接池
public static DataSource getDatasource() {
return dataSource;
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
}
model-Product.java代码
package com.model;
public class Product {
private int id;
private String name;
private String brand;
private int price;
private String addtime;
private int inventory;
public Product() {}
public Product(String name,String brand){
this.name=name;
this.brand=brand;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getBrand() {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getAddtime() {
return addtime;
}
public void setAddtime(String addtime) {
this.addtime = addtime;
}
public int getInventory() {
return inventory;
}
public void setInventory(int inventory) {
this.inventory = inventory;
}
@Override
public String toString() {
return "Product [id="+id+",name="+name+",brand="+brand+",price="+price+",addtime="+addtime+",inventory="+inventory;
}
}
servlet-BrandSearchServlet.java代码
package com.servlet;
import java.io.IOException;
import java.io.PrintWriter;
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.alibaba.fastjson.JSON;
import com.model.Product;
import com.service.ProductService;
/**
* Servlet implementation class BrandSearchServlet
*/
@WebServlet("/search")
public class BrandSearchServlet extends HttpServlet {
//商品服务
private ProductService productService = new ProductService();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 1. 解决跨域（必须加！）将jsonString返回给前端
response.setHeader("Access-Control-Allow-Origin", "*");
response.setContentType("application/json;charset=UTF-8");
// 2. 获取前端传递的参数
String name = request.getParameter("name");
String brand = request.getParameter("brand");
// 调用商品服务层查询商品列表
List<Product> productList = productService.select(name, brand);
//将品牌列表Java对象转为JSON字符串
String jsonString = JSON.toJSONString(productList);
PrintWriter out =response.getWriter();
out.print(jsonString);
out.flush();
out.close();
}
}
servlet-AddServlet.javadaima
package com.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.model.Product;
import com.service.ProductService;
/**
* Servlet implementation class AddServlet
*/
@WebServlet("/AddServlet")
public class AddServlet extends HttpServlet {
private ProductService productService=new ProductService();
//POST
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("application/json;charset=UTF-8");
//1.获取前端的参数
String name=request.getParameter("addname");
String brand=request.getParameter("addbrand");
String price=request.getParameter("addprice");
String addtime=request.getParameter("addaddtime");
String inventory=request.getParameter("addinventory");
//2.封装成Product
Product product=new Product();
product.setName(name);
product.setBrand(brand);
product.setPrice(Integer.parseInt(price));
product.setAddtime(addtime);
product.setInventory(Integer.parseInt(inventory));
//3.调用service
productService.add(product);
//4.返回
response.getWriter().write("{\"success\":true}");
}
}
servlet-DeleteServlet.java代码
package com.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.service.ProductService;
/**
* Servlet implementation class DeleteServlet
*/
@WebServlet("/delete")
public class DeleteServlet extends HttpServlet {
private ProductService service = new ProductService();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
System.out.println(Integer.parseInt(id));
service.delete(Integer.parseInt(id));
response.getWriter().write("success");
}
}
servlet-HetBrandServlet.java代码
package com.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.alibaba.fastjson.JSON;
import com.model.Product;
import com.service.ProductService;
/**
* 下拉框专用
*/
@WebServlet("/HetBrandServlet")
public class HetBrandServlet extends HttpServlet {
private ProductService service = new ProductService();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("application/json;charset=UTF-8");
// 查询所有商品
List<String> brandlist = service.brandselect();
// 返回给前端
String json = JSON.toJSONString(brandlist);
response.getWriter().write(json);
}
}
servlet-PageServlet.java代码
package com.servlet;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.alibaba.fastjson.JSON;
import com.model.Product;
import com.service.ProductService;
/**
* Servlet implementation class PageServlet
*/
@WebServlet("/page")
public class PageServlet extends HttpServlet {
private ProductService service = new ProductService();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("application/json;charset=UTF-8");
// 获取前端传过来的参数
String pageStr = request.getParameter("page");
String sizeStr = request.getParameter("size");
String name = request.getParameter("name");
String brand = request.getParameter("brand");
if (brand == null) brand = "";
// 打印查看是否拿到
System.out.println("page=" + pageStr);
System.out.println("size=" + sizeStr);
System.out.println("name=" + name);
System.out.println("brand=" + brand);
// 默认值
int page = 1;
int size = 5;
if (pageStr != null && !pageStr.isEmpty()) {
page = Integer.parseInt(pageStr);
}
if (sizeStr != null && !sizeStr.isEmpty()) {
size = Integer.parseInt(sizeStr);
}
// 查询数据和总条数
List<Product> list = service.selectByPage(page, size, name, brand);
System.out.println(list+"检查后端是否返回总条数");
int total = service.selectCount(name, brand);
System.out.println("总条数 total = " + total); // 检查知否正常获取总条数
// 返回给前端
Map<String, Object> map = new HashMap<>();
map.put("list", list);
map.put("total", total);
response.getWriter().write(JSON.toJSONString(map));
}
}
servlet-SelectAllServlet.java代码
package com.servlet;
import java.io.IOException;
import java.io.PrintWriter;
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.alibaba.fastjson.JSON;
import com.model.Product;
import com.service.ProductService;
/**
* Servlet implementation class SelectAllServlet
*/
@WebServlet("/SelectAllServlet")
public class SelectAllServlet extends HttpServlet {
private ProductService productService = new ProductService();
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 1. 解决跨域（必须加！）将jsonString返回给前端
response.setHeader("Access-Control-Allow-Origin", "*");
response.setContentType("application/json;charset=UTF-8");
// 调用商品服务层查询商品列表
List<Product> productList = productService.selectall();
//将品牌列表Java对象转为JSON字符串
String jsonString = JSON.toJSONString(productList);
PrintWriter out =response.getWriter();
out.print(jsonString);
out.flush();
out.close();
}
}
servlet-UpdateServlet.java代码
package com.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.model.Product;
import com.service.ProductService;
/**
* Servlet implementation class UpdateServlet
*/
@WebServlet("/update")
public class UpdateServlet extends HttpServlet {
private ProductService service = new ProductService();
// protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// // TODO Auto-generated method stub
// response.getWriter().append("Served at: ").append(request.getContextPath());
// }
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
String name = request.getParameter("name");
String brand = request.getParameter("brand");
String price = request.getParameter("price");
String addtime =request.getParameter("addtime");
String inventory = request.getParameter("inventory");
Product p = new Product();
p.setId(Integer.parseInt(id));
p.setName(name);
p.setBrand(brand);
p.setPrice(Integer.parseInt(price));
p.setAddtime(addtime);
p.setInventory(Integer.parseInt(inventory));
service.updateProduct(p);
response.getWriter().write("success");
}
}
service-ProductService.java代码
package com.service;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.dao.ProductDAO;
import com.model.Product;
public class ProductService {
public ProductDAO productDAO=new ProductDAO();
public List<Product> selectall(){
List<Product> productList = null;
try {
productList=productDAO.selectall();
}catch(SQLException e) {
e.printStackTrace();
}
return productList;
}
//下拉框
public List<String> brandselect(){
List<String> brandList=null;
try {
brandList=productDAO.selectbrand();
}catch(SQLException e){
e.printStackTrace();
}
return brandList;
}
public List<Product> select(String name,String brand){
List<Product> productList = null;
// 用列表接收
//检测参数是否正常传递
// System.out.println(name+brand+"Service参数");
try {
productList=productDAO.selectProduct(name, brand);
}catch(SQLException e){
e.printStackTrace();
}
return productList;
}
public void add(Product product) {
try {
productDAO.addProduct(product);
}catch(SQLException e) {
e.printStackTrace();
}
}
public void delete(int id) {
try {
productDAO.deleteProduct(id);
}catch(SQLException e) {
e.printStackTrace();
}
}
public void findById(int id) {
try {
productDAO.findById(id);
}catch(SQLException e) {
e.printStackTrace();
}
}
public void updateProduct(Product p) {
try {
productDAO.updateProduct(p);
}catch(SQLException e) {
e.printStackTrace();
}
}
// 分页查询
public List<Product> selectByPage(int page, int size, String name, String brand) {
try {
System.out.println(productDAO.selectByPage(page, size, name, brand)+"检查service是否正常取值");
return productDAO.selectByPage(page, size, name, brand);
} catch (SQLException e) {
e.printStackTrace();
return new ArrayList<>();
}
}
// 总条数
public int selectCount(String name, String brand) {
try {
return productDAO.selectCount(name, brand);
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
}
}
dao-ProductDAO.java代码
package com.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.model.Product;
import com.utils.DruidUtils;
public class ProductDAO {
//根据数据源创建QueryRunner
QueryRunner qr=new QueryRunner(DruidUtils.getDatasource());
public List<Product> selectProduct(String name,String brand) throws SQLException{
String sql = "select * from phone where 1=1 ";
List<Object> params = new ArrayList<>();
if (name != null && !name.trim().isEmpty()) {
sql += " and name like ? ";
params.add("%" + name + "%");
}
if (brand != null && !brand.trim().isEmpty()) {
sql += " and brand like ? ";
params.add("%" + brand + "%");
}
sql +=";";
List<Product> list = qr.query(sql, new BeanListHandler<>(Product.class), params.toArray());
return list;
}
public void addProduct(Product product) throws SQLException{
String sql="insert into phone(name,brand,price,addtime,inventory) values(?,?,?,?,?);";
Object[] params = {
product.getName(),
product.getBrand(),
product.getPrice(),
product.getAddtime(),
product.getInventory()
};
qr.update(sql,params);
}
//删除
public void deleteProduct(int id) throws SQLException{
String sql="delete from phone where id=?;";
qr.update(sql,id);
}
public List<String> selectbrand() throws SQLException{
String sql="select distinct brand from phone;";
// ColumnListHandler：只拿第一列数据（这里就是 brand）
return qr.query(sql, new ColumnListHandler<>());
}
//查询所有
public List<Product> selectall() throws SQLException{
String sql="select * from phone;";
return qr.query(sql, new BeanListHandler<>(Product.class));
}
//根据ID查询
public Product findById(int id) throws SQLException{
String sql="select * from phone where id=?;";
return qr.query(sql,new BeanHandler<>(Product.class),id);
}
//更新
public void updateProduct(Product p) throws SQLException {
String sql="update phone set name=?,brand=?,price=?,inventory=?,addtime=? where id=?";
Object[] params = {
p.getName(),
p.getBrand(),
p.getPrice(),
p.getInventory(),
p.getAddtime(),
p.getId()
};
qr.update(sql, params);
}
// 分页查询（带条件）
public List<Product> selectByPage(int page, int size, String name, String brand) throws SQLException {
int start = (page - 1) * size;
String sql = "SELECT * FROM phone WHERE 1=1";
List<Object> params = new ArrayList<>();
// 统一处理：只要不为空就拼接 + 加参数
if (brand != null && !brand.isBlank()) {
sql += " AND brand LIKE ?";
params.add("%" + brand + "%");
}
if (name != null && !name.isBlank()) {
sql += " AND name LIKE ?";
params.add("%" + name + "%");
}
sql += " LIMIT ?,?";
params.add(start);
params.add(size);
System.out.println(qr.query(sql, new BeanListHandler<>(Product.class), params.toArray())+"检查DAO查询结果");
return qr.query(sql, new BeanListHandler<>(Product.class), params.toArray());
}
// 查询总条数（带条件）
public int selectCount(String name, String brand) throws SQLException {
String sql = "SELECT COUNT(*) FROM phone WHERE 1=1";
List<Object> params = new ArrayList<>();
// 判断空字符串，不是判断 null！
if (brand != null && !brand.trim().isEmpty()) {
sql += " AND brand LIKE ?";
params.add("%" + brand + "%");
}
// 判断空字符串，不是判断 null！
if (name != null && !name.trim().isEmpty()) {
sql += " AND name LIKE ?";
params.add("%" + name + "%");
}
Long count = qr.query(sql, params.toArray(), new ScalarHandler<>());
System.out.println(count.intValue() + " 查看DAO总条数是否正常取值");
return count.intValue();
}
}
