#需求 一个前端界面，可以根据商品名和品牌名查询商品信息，同时也可以修改和增减
先完成查找
输入商品名，根据商品名模糊查询
如果选择品牌，根据品牌查询商品
如果同时输入了商品名，选择了品牌，则在该品牌下模糊查询商品
开发流程
前端->web->Service->Dao->服务器
前期准备
MYSQL
create database product charset=utf8;
use product;
create table phone(id int,name varchar(20),brand varchar(20),price int, addtime datetime, inventory int);
insert into phone values(0,'小米15ProMax','小米',6599,'2024-07-12',1500);
创建项目
在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。
代码
search.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>商品列表页</title>
<script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.6.0/jquery.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">
<option value="小米">小米</option>
</select>
<input type="submit" id="searchBtn" value="查询">
</form><br/>
<table>
<h4>查询结果:</h4>
<table border="1" id="productTable">
<tr>
<th>编号</th>
<th>商品名称</th>
<th>品牌</th>
<th>价格</th>
<th>进货日期</th>
<th>库存</th>
</tr>
</table>
</div>
<script>
//点击查询按钮进行查询
$("#searchBtn").click(function(e){
e.preventDefault(); // 阻止页面刷新
//获取输入的商品名
let productNameVal=$("#name").val();
//获取品牌
let brandVal=$("#brand").val();
//将参数组织成json对象
let params = {name:productNameVal, brand:brandVal};
//向后端servlet发送异步请求
$.get("search",params,function(data){
console.log("返回数据：",data);
//先清空商品列表
$("#productTable tr:not(:first)").remove();
//遍历商品列表添加的商品表格
for (var i=0;i<data.length;i++){
let product = data[i];
//此处使用模板字符串进行拼接
//首先定义需要拼接进去的字符串变量，然后将字符串变量用${}括起来，在写道需要拼接的地方
//注意，使用单引号'将字符串引起来
let tr=`
<tr>
<td>${product.id}</td>
<td>${product.name}</td>
<td>${product.brand}</td>
<td>${product.price}</td>
<td>${product.addtime}</td>
<td>${product.inventory}</td>
</tr>`;
$("#productTable").append(tr);
}
})
})
</script>
<script>
$(function(){
//页面初始化，查询商品列表
$.get("/search",{},function(data){
console.log(data);
for(var i=0;i<data.length;i++){
let option="<option value='"+data[i]+"'>"+data[i]+"</option>";
$("#brand").append(option);
}
})
})
</script>
</body>
</html>
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;
}
}
utils-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();
}
}
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> 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;
}
}
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 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 + "%");
}
List<Product> list = qr.query(sql, new BeanListHandler<>(Product.class), params.toArray());
return list;
}
}
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();
}
}
