MVC模型的添加和删除

1.先创建数据库

-- 判断存在即刻删除数据库
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) value('张三','666','18965423548','南阳');
insert into t_user(username,password,phone,address) value('李四','333','18754263548','许昌');
insert into t_user(username,password,phone,address) value('小美','123','18565234759','信阳');
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,'清爽冰凉随心爽！');

然后进行分层

 然后添加JDBC连接数据库 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCUtil {
    private static String driver="com.mysql.cj.jdbc.Driver";
    private static String url="jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC";
    private static String user="root";
    private static String password="root";

    private static Connection con=null;

    /**
     * 获得数据库连接对象
     */
    public static Connection getCon() {

        try {
            //1.加载驱动包
            Class.forName(driver);
            //2.获得数据库连接对象
            con=DriverManager.getConnection(url,user,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return con;
    }

    public static void close(ResultSet rs, PreparedStatement pstm,Connection con) {
        try{
            if (rs!=null){
                rs.close();
            }
            if (pstm!=null){
                pstm.close();
            }
            if (con!=null){
                con.close();
            }

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

    public static void close(PreparedStatement pstm, Connection con) {
        try{
            if (pstm!=null){
                pstm.close();
            }
            if (con!=null){
                con.close();
            }

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

然后进行添加和删除的操作

先进行JDBC的操作

public class GoodsDao {
    private Connection con=null;//数据库连接对象
    private PreparedStatement pstm=null;//预处理对象
    private ResultSet rs=null;//结果集对象
    private int row=0;//增删改受影响的行数
    public List<Goods> selectAll(){
        List<Goods> goodsList=new ArrayList<>();
        try{
            con=JDBCUtil.getCon();
            //3.定义sql语句
            String sql="select*from t_goods";
            //4.获取预处理对象
            pstm=con.prepareStatement(sql);
            //5.执行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"));

                //把Goods对象存储到集合中
                goodsList.add(goods);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.close(rs,pstm,con);
        }
        return goodsList;
    }
    public int goodsAdd(Goods goods){
        int n=0;
        try{
            //加载驱动包
            con = JDBCUtil.getCon();
            //编写sql语句
            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());
            //执行sql语句
            n= pstm.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.close(pstm, con);
        }
        return n;
    }
    public int goodsUpdate(String gname,String price,String mark,int gid){
        int n=0;
        try{
            //加载驱动包
            con = JDBCUtil.getCon();
            //编写sql语句
            String sql="update t_goods set price=?,mark=?, gname=? where gid=?";
            //获取预处理对象
            pstm = con.prepareStatement(sql);
            //传参
            pstm.setObject(1,gname);
            pstm.setObject(2,price);
            pstm.setObject(3,mark);
            pstm.setObject(4,gid);

            //执行sql语句
            n= pstm.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.close(pstm, con);
        }
        return n;
    }
    public int GoodsDelete(int gid){
        int n=0;
        try{
            //加载驱动包
            con = JDBCUtil.getCon();
            //编写sql语句
            String sql="delete from t_goods where gid=?";
            //获取预处理对象
            pstm = con.prepareStatement(sql);
            //传参
            pstm.setObject(1,gid);
            //执行sql语句
            n= pstm.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil.close(pstm, con);
        }
        return n;
    }
}

在主页中进行添加操作

 点击添加商品会跳转到添加商品的网页

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>增加</title>
</head>
<body>
<h2>进行添加</h2>
<form action="goodsAdd" method="post">
    商品名称:<input type="text" name="paomian" value="" placeholder="商品名称"/><br/>
    商品价格:<input type="number" step="0.01" name="jiage" value="" placeholder="商品价格"/><br/>
    商品说明:<input type="text" name="shuoming" value="" placeholder="商品说明"/><br/>
    <input type="submit" value="添加"/>
    <a href="zhuye.jsp">返回主页</a>
</form>
</body>
</html>

然后进行添加商品

 点击添加会跳转到添加操作的后端

@WebServlet("/goodsAdd")
public class GoodsAdd 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 {
        //1.设置请求和响应的编码
        request.setCharacterEncoding("utf-8");//设置请求的编码
        response.setCharacterEncoding("utf-8");//设置响应的编码

        //获取请求参数
        String paomian = request.getParameter("paomian");
        Double jiage = Double.parseDouble(request.getParameter("jiage"));
        String shuoming = request.getParameter("shuoming");

        //封装到Goods对象中
        Goods goods=new Goods();
        goods.setGname(paomian);
        goods.setPrice(jiage);
        goods.setMark(shuoming);
        System.out.println(goods);

        GoodsDao goodsDao=new GoodsDao();
        int n = goodsDao.goodsAdd(goods);
        if (n>0){
            System.out.println("添加成功");
            //请求转发
            request.getRequestDispatcher("selectAllGoods").forward(request,response);
        }else{
            response.sendRedirect("error.jsp");
        }
    }
}

最后添加成功就会跳转到执行查询的JDBC操作然后进行查询跳转到主页

 同理，点击删除则会跳转到执行删除的JDBC操作，然后通过点击删除进行服务器的操作

可以通过id删除数据

@WebServlet("/delete")
public class GoodsDelete 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"));
        //1.设置请求和响应的编码
//        request.setCharacterEncoding("utf-8");//设置请求的编码
//        response.setCharacterEncoding("utf-8");//设置响应的编码

        //获取请求参数
//        String gname = request.getParameter("gname");
        GoodsDao goodsDao=new GoodsDao();
        int n = goodsDao.GoodsDelete(gid);
        if (n>0){
            System.out.println("删除成功");
            //请求转发
            request.getRequestDispatcher("selectAllGoods").forward(request,response);
        }else{
            request.setAttribute("error_msg","删除出现了问题");
            request.getRequestDispatcher("error.jsp").forward(request,response);
        }
    }
}