jdbc+javaServlet图书案例

1.创建maven项目

2.添加web

点击添加web后会出现

3.添加依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>javaEE_tow</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.2</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.1</version>
        </dependency>

    </dependencies>
</project>

5.配置tomcat

先到工件里面创建lib文件目录,然后把maven依赖加进去

配置添加下工件

6.创建utils包,编写工具类

package com.chen.utils;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Dbutil {
    static  String url="jdbc:mysql://localhost:3306/school";
    static  String userName="root";
    static  String password="123456";

    static {
        try {
            //加载并注册驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取数据库连接对象
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, userName, password);
            System.out.println("------开始获取-----");
            System.out.println(connection);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    //关闭连接对象
    public static void closeConnection(Connection connection) {
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //提交事务
    public static void commit(Connection connection) {
        try {
            connection.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

7.创建BookMapper和bookDao

package com.chen.mapper;

import com.chen.pojo.Book;

import javax.servlet.http.HttpServletRequest;
import java.util.List;


public interface BookMapper {

    List<Book> getAllBook();
    List<Book> getPriceBooks(Double star,Double end);
    int addBook(Book b);
    int updateBook(Book b);
    Book selectOne(String id);


}
package com.chen.dao;

import com.chen.mapper.BookMapper;
import com.chen.pojo.Book;
import com.chen.utils.Dbutil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDao implements BookMapper {

    public List<Book> getAllBook() {
        List<Book> books = new ArrayList<>();
        Connection conn = Dbutil.getConnection();
        String sql = "SELECT*FROM t_book";
        //接口对象怎么来
        Statement st = null;
        try {
            st = conn.createStatement();
            //接口对象通过什么方法执行sql
            ResultSet rs = st.executeQuery(sql);
            Book b = null;
            while (rs.next()) {
                b = new Book();
                b.setBookId(rs.getString("book_id"));
                b.setBookName(rs.getString("book_name"));
                b.setBookPrice(rs.getDouble("book_price"));
                books.add(b);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return books;
    }


    public List<Book> getPriceBooks(Double star, Double end) {
        List<Book> books = new ArrayList<>();
        Connection conn = Dbutil.getConnection();
        String sql = "SELECT*FROM t_book where book_price > " + star + " and book_price < " + end + ";";
        //接口对象怎么来
        Statement st = null;
        try {
            st = conn.createStatement();
            //接口对象通过什么方法执行sql
            ResultSet rs = st.executeQuery(sql);
            Book b = null;
            while (rs.next()) {
                b = new Book();
                b.setBookId(rs.getString("book_id"));
                b.setBookName(rs.getString("book_name"));
                b.setBookPrice(rs.getDouble("book_price"));
                books.add(b);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return books;
    }

    public int addBook(Book b) {
        Connection conn = Dbutil.getConnection();
        String sql = "INSERT INTO t_book(book_id,book_name,book_price) VALUES(?,?,?)";//几个字段就有几个问号
        PreparedStatement pst = null;
        int i = 0;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, b.getBookId());
            pst.setString(2, b.getBookName());
            pst.setDouble(3, b.getBookPrice());
            i = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public int updateBook(Book b) {
        System.out.println("进入修改");
        Connection conn = Dbutil.getConnection();
        String sql = "update t_book  set book_name=?,book_price=? where book_id=?";
        PreparedStatement pst = null;
        int i = 0;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, b.getBookName());
            pst.setDouble(2, b.getBookPrice());
            pst.setString(3, b.getBookId());
            i = pst.executeUpdate();//返回修改条数
            System.out.println("修改了" + i + "条数据!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public Book selectOne(String id) {
        Book s = new Book();
        Connection conn = Dbutil.getConnection();
        String sql = "SELECT * FROM t_book WHERE book_id = ?";
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                s.setBookId(rs.getString("book_id"));
                s.setBookPrice(rs.getDouble("book_price"));
                s.setBookName(rs.getString("book_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return s;
    }


}

8.创建实体

package com.chen.pojo;

public class Book {
    public String getBookId() {
        return bookId;
    }

    public void setBookId(String bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public Double getBookPrice() {
        return bookPrice;
    }

    public void setBookPrice(Double bookPrice) {
        this.bookPrice = bookPrice;
    }

    @Override
    public String toString() {
        return "Book{" +
                "bookId='" + bookId + '\'' +
                ", bookName='" + bookName + '\'' +
                ", bookPrice=" + bookPrice +
                '}';
    }

    public Book(String bookId, String bookName, Double bookPrice) {
        this.bookId = bookId;
        this.bookName = bookName;
        this.bookPrice = bookPrice;
    }

    public Book() {
    }

    String bookId;
     String bookName;
     Double bookPrice;
}

9.编写servlet

package com.chen.servlet;

import com.chen.dao.BookDao;
import com.chen.pojo.Book;

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 java.io.IOException;

@WebServlet(urlPatterns = "/addBook", name = "添加")
public class BookAddServlet extends HttpServlet {
    private BookDao bookDao=new BookDao();
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        req.setCharacterEncoding("UTF-8");
        // 获取参数
        String id = req.getParameter("bookId");
        String name = req.getParameter("bookName");
        String price = req.getParameter("bookPrice");
        Book book = new Book(id, name, Double.parseDouble(price));
        System.out.println(book);
        int i = bookDao.addBook(book);
        System.out.println(i);
        if(i>0){
            req.getRequestDispatcher("index.jsp").forward(req, resp);
        }else{
            resp.getWriter().println("添加图书失败");
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        super.doPost(req,resp);
    }
}
package com.chen.servlet;

import com.chen.dao.BookDao;
import com.chen.pojo.Book;
import com.mysql.jdbc.StringUtils;

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 java.io.IOException;
import java.util.ArrayList;
import java.util.List;


@WebServlet(urlPatterns = "/index", name = "查询")
public class BookQueryServlet extends HttpServlet {
    private BookDao bookDao = new BookDao();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        req.setCharacterEncoding("UTF-8");
        //    获取参数判断是否是点击查询
        String minPrice = req.getParameter("minPrice");
        String maxPrice = req.getParameter("maxPrice");
        List<Book> bookList;
        if (!StringUtils.isNullOrEmpty(minPrice)&&!StringUtils.isNullOrEmpty(maxPrice)) {
//        获取参数
            bookList = bookDao.getPriceBooks(Double.parseDouble(minPrice), Double.parseDouble(maxPrice));
            System.out.println("---------条件查询-----------");
            System.out.println(bookList);
        } else {
//           查全
            bookList = bookDao.getAllBook();
            System.out.println("---------查全-----------");
            System.out.println(bookList);
        }
        req.setAttribute("bookList", bookList);
        req.getRequestDispatcher("/page/home.jsp").forward(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        super.doPost(req, resp);
    }

}
package com.chen.servlet;

import com.chen.dao.BookDao;
import com.chen.pojo.Book;
import com.mysql.jdbc.StringUtils;

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 java.io.IOException;

@WebServlet(urlPatterns = "/updateBook", name = "修改")
public class BookUpdateServlet extends HttpServlet {
    private BookDao bookDao=new BookDao();
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("进入");
        resp.setCharacterEncoding("UTF-8");
        req.setCharacterEncoding("UTF-8");

        // 获取参数
        String id = req.getParameter("id");
        if(!StringUtils.isNullOrEmpty(id)){
            Book book = bookDao.selectOne(id);
            req.setAttribute("book",book);
            req.getRequestDispatcher("/page/updateBook.jsp").forward(req, resp);
        }else{
//         修改数据
            // 获取参数
            String bookId = req.getParameter("bookId");
            String name = req.getParameter("bookName");
            String price = req.getParameter("bookPrice");
            Book book = new Book(bookId, name, Double.parseDouble(price));
            System.out.println("-----修改数据-------");
            System.out.println(book);
            int i = bookDao.updateBook(book);
            if(i>0){
                req.getRequestDispatcher("index.jsp").forward(req, resp);
            }else{
                resp.getWriter().println("修改图书失败");
            }
        }

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println(1111);
        req.getRequestDispatcher("/page/updateBook.jsp").forward(req, resp);
        super.doPost(req,resp);
    }
}

10.编写jsp

index.jsp

<%--
  Created by IntelliJ IDEA.
  User: xuan8
  Date: 2024/4/8
  Time: 19:22
  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>
  </body>
</html>
<jsp:forward page="/index?minPrice=&maxPrice=" />

addBook.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.min.js"></script>
<head>
    <title>添加图书</title>
    <style>
        body {
            display: flex;
            justify-content: center;
            align-items: center;
            height: 100vh;
        }

        .container {
            max-width: 500px;
        }
    </style>
</head>
<body>
<div class="container">
    <h1 class="text-center">添加图书</h1>

    <form method="get" action="<%=request.getContextPath()%>/addBook">
        <div class="mb-3">
            <label for="bookId" class="form-label">图书编号</label>
            <input type="text" class="form-control" id="bookId" name="bookId" required>
        </div>
        <div class="mb-3">
            <label for="bookName" class="form-label">图书名称</label>
            <input type="text" class="form-control" id="bookName" name="bookName" required>
        </div>
        <div class="mb-3">
            <label for="bookPrice" class="form-label">图书价格</label>
            <input type="number" class="form-control" id="bookPrice" name="bookPrice" step="0.01" required>
        </div>
        <div class="text-center">
            <button type="submit" class="btn btn-primary">添加</button>
        </div>
    </form>
</div>

</body>
</html>

 home.jsp

<%@ page import="java.util.List" %>
<%@ page import="com.chen.pojo.Book" %>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
    <title>图书列表</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.min.js"></script>


</head>

<body>
<style>
    table .tr .td{
        text-align: center;
    }
</style>
<h1 style="text-align: center">图书列表</h1>
<form class="mb-3" method="get" action="<%=request.getContextPath()%>/index">
    <div class="row">
        <div class="col">
            <label for="minPrice" class="form-label">最低价格</label>
            <input type="number"    class="form-control" id="minPrice" name="minPrice">
        </div>
        <div class="col">
            <label for="maxPrice" class="form-label">最高价格</label>
            <input type="number"   class="form-control" id="maxPrice" name="maxPrice">
        </div>
    </div>
    <div class="text-center mt-3">
        <button type="submit" class="btn btn-primary">查询</button>
        <button type="button" class="btn btn-primary btn—add">添加数据</button>
    </div>
</form>
<table class="table table-striped">
    <thead>
    <tr>
        <th>图书编号</th>
        <th>图书名称</th>
        <th>图书价格</th>
        <th>操作</th>
    </tr>
    </thead>
    <tbody>
    <%
        List<Book> bookList = (List<Book>) request.getAttribute("bookList");

    %>
    <%-- 使用 bookList 进行循环渲染表格行 --%>
    <% for (Book book : bookList) { %>
    <tr>
        <td><%= book.getBookId() %></td>
        <td><%= book.getBookName() %></td>
        <td><%= book.getBookPrice() %></td>
        <td>
            <a href="<%=request.getContextPath()%>/updateBook?id=<%= book.getBookId()%>">修改</a>&nbsp;&nbsp;
        </td>
    </tr>
    <% } %>
    </tbody>
</table>
</body>
</html>
<script>
   let add =document.querySelector(".btn—add");
   add.addEventListener("click",()=>{
       window.location.href = "<%=request.getContextPath()%>/page/addBook.jsp"
   })
</script>

 目录分布

  • 2
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值