Servlet—查询、删除、添加

这是没有进行美化的版本

提前创建好需要用的类，参数们，无参、有参的构造方法，get、set，和toString。

public class Goods {
    private int id;
    private String name;
    private int number;
    private int price;
。。。。。。。。。。。。。。。
}

为了防止每写一个功能就要重新建立一个servlet的麻烦，用Switch和方法整合到一起。

查询

1. 继承父类；

2. 重写service方法，要把super一句去掉；

3. 设置域名，注意一定不能拉掉 ‘/’号

4.写method = "selectAll"方法，进行正常的jdbc设置

@WebServlet("/goods")
public class goodsServlet extends HttpServlet {

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        req.setCharacterEncoding("UTF-8");
        String method = req.getParameter("method");
        if (method == null || method == ""){
            method = "selectAll";
        }
        switch (method){
            case "selectAll":
                selectAll(req, resp);
                break;
            case "deleteById":
                deleteById(req, resp);
                break;
            case "insert":
                insert(req, resp);
                break;
            case "selectById":
                selectById(req, resp);
                break;
        }
    }

}

此时我们只用得到selectAll，deleteById，和insert

private void selectAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("goodsServlet.selectAll");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Goods> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id,name,number,price FROM goods";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int number = resultSet.getInt("number");
                int price = resultSet.getInt("price");
                Goods goods = new Goods(id,name,number,price);
                list.add(goods);
            }
            for (Goods goods : list) {
                System.out.println(goods);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCUtil.close(connection,preparedStatement,resultSet);
        }

        req.setAttribute("list",list);/*键值对*/
        req.getRequestDispatcher("/goods_list.jsp").forward(req,resp);
    }

删除

5. 添加所需要的id，req->request请求，因为我们想从外部得到想要删除的信息的id；

6. sql后是数据库表达，‘？’表示占位符，预留位置给收到的id数据，preparedStatement预编译。

7. 加入这句为了使删除完毕后自动跳会显示新数据的页面；
   

private void deleteById(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        // http://localhost:8080/JavaWeb/deleteById?id=1
        System.out.println("goodsServlet.deleteById");

        int id = Integer.parseInt(req.getParameter("id"));
        System.out.println("id = " + id);
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "delete from goods where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            System.out.println(preparedStatement);
            int count = preparedStatement.executeUpdate();
            System.out.println("count: " + count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.close(connection, preparedStatement, null);
        }

        // 删除完成之后再去查找所有，展示最新的数据
        // 重定向 :让浏览器发送这个请求  /JavaWeb/selectAll
        // 302
        resp.sendRedirect(req.getContextPath() + "/goods?method=selectAll");
    }

添加

因为我在数据库设置了id的自动递增，所以在添加时不必再输入和定义id

private void insert(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        System.out.println("goodsServlet.insert");

        String name = req.getParameter("name");
        int number = Integer.parseInt(req.getParameter("number"));
        int price = Integer.parseInt(req.getParameter("price"));
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "insert into goods(name,number,price) value (?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2, number);
            preparedStatement.setInt(3, price);
            System.out.println(preparedStatement);
            int count = preparedStatement.executeUpdate();
            System.out.println("count" + count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.close(connection,preparedStatement,null);
        }
        resp.sendRedirect(req.getContextPath() + "/goods?method=selectAll");
    }

还要建立一个 jsp 文件用于页面上的显示，因为添加时要跳转到另一个界面进行输入，所以我们用submit和action进行，用submit提交可以将信息转达action提供的连接中。

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
    <form action="<%=request.getContextPath()%>/goods?method=insert" method="post">
        商品名称：<input type="text" name="name"/><br />
        商品数量：<input type="text" name="number"/><br />
        商品价格：<input type="text" name="price"/><br />
        <input type="submit" value="添加"/>
    </form>

</body>
</html>

还有整体的网页jsp文件

<%@ page import="java.util.ArrayList" %>
<%@ page import="SERVLET.entity.Goods" %><%--
  Created by IntelliJ IDEA.
  User: 15387
  Date: 2021/8/10
  Time: 20:54
  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>
<a href="<%=request.getContextPath()%>/goods_insert.jsp">添加</a>
    <table border="1" cellpadding="0">
        <tr>
            <td>id</td>
            <td>name</td>
            <td>number</td>
            <td>price</td>
            <td>删除</td>
<%--            <td><a href="/deleteById?id=<%=%>"></a></td>--%>
        </tr>
        <%
            /*request：内置对象，这个对象已经创建建好了，名字也起好了就是request*/
            ArrayList<Goods> list = (ArrayList<Goods>) request.getAttribute("list");
            for (Goods goods : list) {
        %>
                <tr>
                    <td><%=goods.getId()%></td>
                    <td><%=goods.getName()%></td>
                    <td><%=goods.getNumber()%></td>
                    <td><%=goods.getPrice()%></td>
                    <td>
                        <a href="<%=request.getContextPath()%>/goods?method=deleteById&id=<%=goods.getId()%>"> 删除 </a>
                    </td>
                </tr>
        <%
            }
        %>
    </table>

    </table>
</body>
</html>

PreparedStatement 和 Statement 的区别

Statement 非预编译

使用范围：当执行相似SQL(结构相同，具体值不同)语句的次数比较少
优点：语法简单
缺点：采用硬编码效率低，安全性较差。
原理：硬编码，每次执行时相似SQL都会进行编译

示例执行过程:

public void exec(Connection conn){
        try {
                Long beginTime = System.currentTimeMillis();
                conn.setAutoCommit(false);//设置手动提交
                Statement st = conn.createStatement();
                for(int i=0;i<10000;i++){
                   String sql="insert into t1(id) values ("+i+")";
                   st.executeUpdate(sql);
                }
                Long endTime = System.currentTimeMillis();
                System.out.println("Statement用时："+(endTime-beginTime)/1000+"秒");//计算时间
                st.close();
               conn.close();
        } catch (SQLException e) {             
                  e.printStackTrace();
        }
   }

————————————————
版权声明：本文为CSDN博主「离水的鱼儿」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xuebing1995/article/details/72235380

PreparedStatement 预编译

使用范围：当执行相似sql语句的次数比较多（例如用户登陆，对表频繁操作…）语句一样，只是具体的值不一样，被称为动态SQL
优点：语句只编译一次，减少编译次数。提高了安全性（阻止了SQL注入）
缺点: 执行非相似SQL语句时，速度较慢。
原理：相似SQL只编译一次，减少编译次数

实例执行过程:

public void exec2(Connection conn){
     try {
            Long beginTime = System.currentTimeMillis();
            conn.setAutoCommit(false);//手动提交
            PreparedStatement pst = conn.prepareStatement("insert into t1(id) values (?)");
            for(int i=0;i<10000;i++){
                   pst.setInt(1, i);
                   pst.execute();   
            }
            conn.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("Pst用时:"+(endTime-beginTime)+"秒");//计算时间
            pst.close();
            conn.close();
     } catch (SQLException e) {               
              e.printStackTrace();
     }
}

————————————————
版权声明：本文为CSDN博主「离水的鱼儿」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xuebing1995/article/details/72235380