有关商品业务的增加、删除、修改、全查。

WXLN.

已于 2023-02-27 08:42:47 修改

阅读量576

点赞数

文章标签：数据库 sql mysql

于 2023-02-25 14:28:33 首次发布

本文链接：https://blog.csdn.net/m0_68758365/article/details/129214957

版权

通过数据库后端把数据响应到jsp前端 java代码通过JDBC连接数据库、做出增加、删除、修改、全查操作。

一、数据库内容

#判断存在即删除数据库
drop database if exists mydb;
#创建数据库
create database mydb;
#使用数据库
use mydb;


#创建表
create table t_user
(
	uid int primary key auto_increment,
	username varchar(20),
	password varchar(20),
	phone varchar(11),
	address varchar(50)
);


insert into t_user(username,password,phone,address) values('张三','666','18965423548','南阳');
insert into t_user(username,password,phone,address) values('李四','333','18754263548','许昌');
insert into t_user(username,password,phone,address) values('小美','123','18565234759','信阳');

select * from t_user where username=? and password=?
select * from t_user;


create table t_goods
(
	gid int primary key auto_increment,
	gname varchar(20),
	price double,
	mark varchar(100)
);

insert into t_goods(gname,price,mark) values('泡面',4.5,'够香够辣就是这个味！');
insert into t_goods(gname,price,mark) values('火腿',8.5,'肉质细腻Q弹！');
insert into t_goods(gname,price,mark) values('雪碧',3.5,'清爽冰凉随心爽！');

select * from t_goods;

1.用户信息

2.商品信息

二.全查操作

1.创建一个关于商品的jsp文件（goods.jsp）

goods.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>
</head>
<body style="background-color: lightblue;margin: 200px auto;
background-image:url(img/3.jpg);background-size: 100% 100%">
<h1>登录成功</h1>
<h2>欢迎来自${user.address}的${user.username}访问主页</h2>
<table>
  <thead>
  <!--<a href="addGoods.jsp">添加商品</a>-->
  <tr>
  <th>商品编号</th>
  <th>商品名称</th>
  <th>商品价格</th>
  <th>商品说明</th>
  </tr>
  </thead>
  <tboby id="List">
          <c:forEach items="${goodsList}" var="goods">

            <tr>
              <td> ${goods.gid}</td>
              <td> ${goods.gname}</td>
              <td> ${goods.price}</td>
              <td> ${goods.mark}</td>
              <td>
                <a href="#">修改</a>
                <a href="dell?gid=${goods.gid}">删除</a>
              </td>
            </tr>
          </c:forEach>
  </tboby>
</table>

</body>
</html>

2.创建一个selectAll方法

ublic List<Goods> selectAll() {
        List<Goods> goodsList = new ArrayList<>();

        try {
            Class.forName(jdbcdirver);
            con = DriverManager.getConnection(jdbcurl, jdbcuser, jdbcpassword);
            String sql = "select * from t_goods";
            pstm = con.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                //从结果集中获取数据，封装到Goods对象中
                Goods goods = new Goods();
                goods.setGid(rs.getInt("gid"));
                goods.setGname(rs.getString("gname"));
                goods.setPrice(rs.getDouble("price"));
                goods.setMark(rs.getString("mark"));
                //把当前行对应的对象存储到集合中
                goodsList.add(goods);
            }
        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstm != null) {
                    pstm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return goodsList;
    }

3.创建一个Servlet类（servletAllGoods类）

package com.nai.servlet;

import com.nai.Dao.impl.GoodsDaoImpl;
import com.nai.bean.Goods;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.util.List;

@WebServlet("/selectAllGoods")
public class servletAllGoods extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doPost(request,response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        //查询所有商品的信息
        GoodsDaoImpl goodsDao=new GoodsDaoImpl();
        List<Goods> goodsList=goodsDao.selectAll();
        System.out.println(goodsList);
        //把数据传递到前端页面
        //通过request获取session对象，该对象可以向前端传输数据的容器
        HttpSession session =request.getSession();
        //向session中存入商品信息集合
        session.setAttribute("goodsList",goodsList);
        //登录成功，跳转到上面
        response.sendRedirect("goods.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>
</head>
<body style="background-color: lightblue;margin: 200px auto;
background-image:url(img/3.jpg);background-size: 100% 100%">
<h1>登录成功</h1>
<h2>欢迎来自${user.address}的${user.username}访问主页</h2>
<table>
  <thead>
  <a href="addGoods.jsp">添加商品</a>
  <tr>
  <th>商品编号</th>
  <th>商品名称</th>
  <th>商品价格</th>
  <th>商品说明</th>
  </tr>
  </thead>
  <tboby id="List">
          <c:forEach items="${goodsList}" var="goods">

            <tr>
              <td> ${goods.gid}</td>
              <td> ${goods.gname}</td>
              <td> ${goods.price}</td>
              <td> ${goods.mark}</td>
              <td>
                <a href="#">修改</a>
                <a href="dell?gid=${goods.gid}">删除</a>
              </td>
            </tr>
          </c:forEach>
  </tboby>
</table>

</body>
</html>

其次创建一个有关商品添加的jsp文件

<%--
  Created by IntelliJ IDEA.
  User: 奈申博
  Date: 2023/2/24
  Time: 14:04
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<form action="addGood" method="post">
商品名称:<input  type="text" name="gname" value="" placeholder="商品名称"><br>
商品价格:<input  type="number" step="0.01" name="price" value="" placeholder="商品价格"><br>
商品说明:<input type="text" name="mark" value="" placeholder="商品说明"><br>
<input type="submit" value="添加" >
</form>
</body>
</html>

创建一个添加（add）方法

public int add(Goods goods) {
        System.out.println("执行add");
        try {
            Class.forName(jdbcdirver);
            con = DriverManager.getConnection(jdbcurl, jdbcuser, jdbcpassword);
            String sql = "insert into t_goods(gname,price,mark) values(?,?,?)";
            pstm = con.prepareStatement(sql);
            pstm.setObject(1, goods.getGname());
            pstm.setObject(2, goods.getPrice());
            pstm.setObject(3, goods.getMark());
            row = pstm.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstm != null) {
                    pstm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return row;
    }

最后创建Java代码 Servlet类（AddGoods类）

package com.nai.servlet;

import com.nai.Dao.impl.GoodsDaoImpl;
import com.nai.bean.Goods;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet("/addGood")
public class AddGoods extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doPost(request,response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("执行AddGoods");
            request.setCharacterEncoding("utf-8");
            response.setCharacterEncoding("utf-8");
            //获取请求参数并封装到Goods对象中
        Goods goods= new Goods();
        goods.setGname(request.getParameter("gname"));
        //数据转换
        goods.setPrice(Double.parseDouble(request.getParameter("price")));
       // request.getParameter("price");
        goods.setMark(request.getParameter("mark"));

        //执行JDBC的添加操作
        GoodsDaoImpl  goodsDao= new GoodsDaoImpl();
        int row=goodsDao.add(goods);
        if(row>0){
            //添加成功请求selectAllGoods地址 执行对应的select（查询商品信息 存入session中 跳转到主页）
            request.getRequestDispatcher("selectAllGoods").forward(request,response);
        }else {
            request.setAttribute("user_msg","添加商品出错了！");
            request.getRequestDispatcher("user.jsp").forward(request,response);
        }
    }
}

添加展示图：

四、删除操作

步骤图：

在主页文件中加入删除商品的代码（超链接）


<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
  <title>登陆成功</title>
</head>
<body style="background-color: lightblue;margin: 200px auto;
background-image:url(img/3.jpg);background-size: 100% 100%">
<h1>登录成功</h1>
<h2>欢迎来自${user.address}的${user.username}访问主页</h2>
<table>
  <thead>
  <a href="addGoods.jsp">添加商品</a>
  <tr>
  <th>商品编号</th>
  <th>商品名称</th>
  <th>商品价格</th>
  <th>商品说明</th>
  </tr>
  </thead>
  <tboby id="List">
          <c:forEach items="${goodsList}" var="goods">

            <tr>
              <td> ${goods.gid}</td>
              <td> ${goods.gname}</td>
              <td> ${goods.price}</td>
              <td> ${goods.mark}</td>
              <td>
                <a href="#">修改</a>
                <a href="dell?gid=${goods.gid}">删除</a>
              </td>
            </tr>
          </c:forEach>
  </tboby>
</table>

</body>
</html>

创建一个删除（delect）方法

public int delect(int gid)  {
        System.out.println("执行DE");
        try {
            Class.forName(jdbcdirver);
            con = DriverManager.getConnection(jdbcurl, jdbcuser, jdbcpassword);
            String sql = "delete from t_goods where gid=?";
            pstm = con.prepareStatement(sql);
            pstm.setObject(1,gid);
            row = pstm.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {

                if (pstm != null) {
                    pstm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return row;
    }

再创建一个Servlet类（DellGoods类）

package com.nai.servlet;

import com.nai.Dao.impl.GoodsDaoImpl;
import com.nai.until.JDBCUtil;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.sql.Connection;

@WebServlet("/dell")
public class DellGoods extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //        解决中文乱码问题
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");

        int id =Integer.parseInt(request.getParameter("gid"));
        Connection con = null;
        int user =0;
            GoodsDaoImpl goodsDao = new GoodsDaoImpl();
            user=goodsDao.delect(id);
        if (user>0){
            request.getRequestDispatcher("selectAllGoods").forward(request,response);
        }else {
            response.sendRedirect("user.jsp");
        }

    }

    }

删除展示图：（删除可乐）

五、修改操作

创建一个Servlet类（FindGoodsById类）通过gid查询商品信息

package com.nai.servlet;

import com.nai.Dao.impl.GoodsDaoImpl;
import com.nai.bean.Goods;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet("/findById")
public class FindGoodsById extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doPost(request,response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        int gid=Integer.parseInt(request.getParameter("gid"));
        //根据gid查询商品信息
        GoodsDaoImpl goodsDao= new GoodsDaoImpl();
        Goods goods=goodsDao.selectById(gid);
        if(goods!=null){
            //把数据存储到request域对象中 然后请求转发到页面
            request.setAttribute("goods",goods);
            request.getRequestDispatcher("showGoods.jsp").forward(request,response);

        }else{
            //登录失败  跳转到错误页面
            request.setAttribute("user_msg","修改出现异常");
            request.getRequestDispatcher("user.jsp").forward(request,response);
        }

    }
}

再创建一个修改方法（UpdateGoods类）

public int UpdateGoods(Goods goods){
            int row=0;
        try{
            Class.forName(jdbcdirver);
            con=  DriverManager.getConnection(jdbcurl,jdbcuser,jdbcpassword);
            String sql="update t_goods set gname=?,price=?,mark=? where  gid=?";

            pstm=   con.prepareStatement(sql);
            pstm.setObject(1,goods.getGname());
            pstm.setObject(2,goods.getPrice());
            pstm.setObject(3,goods.getMark());
            pstm.setObject(4,goods.getGid());

             row= pstm.executeUpdate();


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {

                if (pstm != null) {
                    pstm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return row;
    }

和一个jsp文件有关商品修改主页

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>商品信息管理员</title>
</head>
<body>
<form action="updateGoods" method="post">
    商品编号:<input  type="text" name="gid" value="${goods.gid}" placeholder="商品编号" readonly="readonly"><br>
    商品名称:<input type="text" name="gname" value="${goods.gname}" placeholder="商品名称"><br>
    商品价格:<input  type="number" step="0.01" name="price" value="${goods.price}" placeholder="商品价格"><br>
    商品说明:<input type="text" name="mark" value="${goods.mark}" placeholder="商品说明"><br>
<input type="submit" value="修改" >
</form>
</body>
</html>

最后跳转到后端java代码 servlet类中的UpdateGoods类中、引用修改方法

package com.nai.servlet;

import com.nai.Dao.impl.GoodsDaoImpl;
import com.nai.bean.Goods;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet("/updateGoods")
public class UpdateGoods extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            doPost(request,response);
    }
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        //获取表单请求的数据 封装到goods对象中
        Goods goods=new Goods();
        goods.setGid(Integer.parseInt(request.getParameter("gid")));
        goods.setGname(request.getParameter("gname"));
        goods.setPrice(Double.parseDouble(request.getParameter("price")));
        goods.setMark(request.getParameter("mark"));
            //执行JDBC
        GoodsDaoImpl goodsDao =new GoodsDaoImpl();
        int row =goodsDao.UpdateGoods(goods);
        if (row>0){
            request.getRequestDispatcher("selectAllGoods").forward(request,response);
        }else {
            request.setAttribute("user_msg","修改商品出错了！");
            request.getRequestDispatcher("user.jsp").forward(request,response);
        }

    }

}

修改展示：