一个关于JDBC增删查改的简单项目

要求掌握JDBC的基本操作。
对book表中的数据进行增删改查；
book表：id int，bookName varchar,price int,description varchar
Book类：id int，bookName String ，price int，description String

基本思路，采用传统的MVC开发模式，先在mysql创建一个book数据库，再定义好接口，然后在实现接口里的增删查改的方法，书本的id通过随机值来生成，通过session和request.setAttribute传输数据到jsp，jsp再用EL表达式获取显示

先创建数据库：
create database Book;
create table book(id int NOT NULL,
bookName varchar(50) NOT NULL,
price int,
description varchar(50),
PRIMARY KEy(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

book的bean代码：
/**

• 书本实体

• @author Naive

• @date: 2019年10月9日 下午4:38:20
  /
  public class Book {
  /

  • id 书本的唯一标识
  • bookName 书名
  • price 价格
  • description 描述
    */
    private int id;
    private String bookName;
    private int price;
    private String description;

  public Book(){

  }
  public Book(int id,
  String bookName,
  int price,
  String description){
  this.id = id;
  this.bookName = bookName;
  this.price = price;
  this.description = description;
  }

  public int getId() {
  return id;
  }
  public void setId(int id) {
  this.id = id;
  }
  public String getBookName() {
  return bookName;
  }
  public void setBookName(String bookName) {
  this.bookName = bookName;
  }
  public int getPrice() {
  return price;
  }
  public void setPrice(int price) {
  this.price = price;
  }
  public String getDescription() {
  return description;
  }
  public void setDescription(String description) {
  this.description = description;
  }

  @Override
  public String toString() {
  return “Book [id=” + id + “, bookName=” + bookName + “, price=” + price + “, description=” + description
  + “, getId()=” + getId() + “, getBookName()=” + getBookName() + “, getPrice()=” + getPrice()
  + “, getDescription()=” + getDescription() + “]”;
  }
  }

接口：
/**

• 定义接口
• @author Naive
• @date: 2019年10月9日 下午4:42:04
  /
  public interface BookDao {
  public String dbpassword=“123456”;
  public String dbusername=“root”;
  public String dbdriver=“com.mysql.jdbc.Driver”;
  public String dburl=“jdbc:mysql://localhost:3306/Book”;
  /*
  • 生成随机id
  • @return 生成的id
    /
    public int Randomvalue();
    /*
  • 增加书本
  • @param book书本类
    /
    public boolean BookAdd(Book book);
    /*
  • 根据id删除书本
  • @param id书本唯一标识
    /
    public boolean BookDelete(int id);
    /*
  • 更新书本
  • @param id确定更新的书本
  • @param book书本类
    /
    public boolean BookUpdate(int id, Book book);
    /*
  • 按指定id查询书本
  • @param id书本唯一标识
  • @return 查询到的书本
    /
    public Book BookSelect(int id);
    /*
  • 查询全部书本
  • @return 全部书本
    */
    public ArrayList BookSelect();
    }

实现接口代码,JDBC对数据库操作：
/**

• 实现接口，对数据库操作

• @author Naive

• @date: 2019年10月9日 下午4:56:47
  */
  public class BookDaoImpl implements BookDao {
  private Connection connection;
  public BookDaoImpl() {
  // TODO Auto-generated constructor stub
  try {
  Class.forName(dbdriver);
  this.connection =DriverManager.getConnection(dburl,dbusername,dbpassword);
  } catch (ClassNotFoundException | SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
  }
  }

  @Override
  public int Randomvalue() {
  Random ran = new Random();
  int rand = ran.nextInt(999999-100000+1)+100000;//生成随机数
  return rand;
  }

  @Override
  public boolean BookAdd(Book book) {
  String BookAddsql = “INSERT INTO book() VALUES(?,?,?,?)”;
  try {
  PreparedStatement pStatement = this.connection.prepareStatement(BookAddsql);
  pStatement.setInt(1, Randomvalue());
  pStatement.setString(2, book.getBookName());
  pStatement.setInt(3, book.getPrice());
  pStatement.setString(4, book.getDescription());
  pStatement.execute();
  return true;
  } catch (SQLException e) {
  e.printStackTrace();
  return false;
  }
  }

  @Override
  public boolean BookDelete(int id) {
  String BookDeletesql = “delete from book where id=?”;
  try {
  PreparedStatement pStatement = this.connection.prepareStatement(BookDeletesql);
  pStatement.setInt(1, id);
  pStatement.execute();
  return true;
  } catch (SQLException e) {
  e.printStackTrace();
  return false;
  }

  }

  @Override
  public boolean BookUpdate(int id, Book book) {
  String BookUpdatesql = “update book set bookName=?,price=?,description=? where id=?”;
  try {
  PreparedStatement pStatement = this.connection.prepareStatement(BookUpdatesql);
  pStatement.setString(1, book.getBookName());
  pStatement.setInt(2, book.getPrice());
  pStatement.setString(3, book.getDescription());
  pStatement.setInt(4, id);
  pStatement.execute();
  return true;
  } catch (SQLException e) {
  e.printStackTrace();
  return false;
  }

  }

  @Override
  public Book BookSelect(int id) {
  String BookSelectsql = “select * from book where id=?”;
  try {
  PreparedStatement pStatement = this.connection.prepareStatement(BookSelectsql);
  pStatement.setInt(1, id);
  ResultSet rSet=pStatement.executeQuery();
  if (rSet.next()) {
  Book book = new Book(id,
  rSet.getString(“bookName”),
  rSet.getInt(“price”),
  rSet.getString(“description”));
  return book;
  }
  } catch (Exception e) {
  // TODO: handle exception
  }
  return null;
  }

  @Override
  public ArrayList BookSelect() {
  String BookSelectsql = “select * from book”;
  try {
  PreparedStatement pStatement = this.connection.prepareStatement(BookSelectsql);
  ArrayListarrayList = new ArrayList();
  ResultSet rSet=pStatement.executeQuery();
  while (rSet.next()) {
  Book book = new Book(rSet.getInt(“id”),
  rSet.getString(“bookName”),
  rSet.getInt(“price”),
  rSet.getString(“description”));
  arrayList.add(book);
  }
  return arrayList;
  } catch (SQLException e) {
  e.printStackTrace();
  }
  return null;
  }
  }

jsp页面，通过EL表达式输出数据
<%@ taglib prefix=“c” uri=“http://java.sun.com/jsp/jstl/core” %>
<%@ page language=“java” contentType=“text/html; charset=UTF-8”
pageEncoding=“UTF-8”%>

	<tbody>
	<c:forEach var="i" begin="0" end="${num}" items="${sessionScope.arrayList}">
		<tr>
			<td>${i.id}</td>
			<td>${i.bookName}</td>
			<td>${i.price}</td>
			<td>${i.description}</td>
			<td>
				<a href="AddServlet?id=${i.id}"><button>修改</button></a>
				<a href="DelServlet?id=${i.id}"><button>删除</button></a>
			</td>
		</tr>
		</c:forEach>
	</tbody>
</table>
<a href="addbook.jsp"><button type="button">新增书本</button></a>

书本ID	书名	价格	书本描述	操作

修改和新增书本的jsp页面
<%@ page language=“java” contentType=“text/html; charset=UTF-8”
pageEncoding=“UTF-8”%>

书名:

价格:

描述:

然后是servlet

/**
*

• test的servlet

• 直接运行这个servlet

• @author Naive

• @date 2019年10月10日
  */
  @WebServlet("/TestServlet")
  public class TestServlet extends HttpServlet {
  private static final long serialVersionUID = 1L;

  /**

  • @see HttpServlet#HttpServlet()
    */
    public TestServlet() {
    super();
    // TODO Auto-generated constructor stub
    }

  /**

  • @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    request.setCharacterEncoding(“utf-8”);
    response.setCharacterEncoding(“utf-8”);
    response.setContentType(“text/html;charset=utf-8”);

    HttpSession session = request.getSession();
    BookDaoImpl bookDaoImpl = new BookDaoImpl();
    ArrayList arrayList = bookDaoImpl.BookSelect();
    session.setAttribute(“arrayList”, arrayList);
    request.setAttribute(“num”, arrayList.size());//数组的长度
    session.setMaxInactiveInterval(0);
    request.getRequestDispatcher(“test.jsp”).forward(request, response);
    //response.getWriter().append("Served at: ").append(request.getContextPath());
    }

  /**

  • @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    doGet(request, response);
    }
    }

更新和新增的servlet，通过判断id值是否存在来做更新或新增的操作
/**

• Servlet implementation class Submit
  */
  @WebServlet("/Submit")
  public class Submit extends HttpServlet {
  private static final long serialVersionUID = 1L;

  /**

  • @see HttpServlet#HttpServlet()
    */
    public Submit() {
    super();
    // TODO Auto-generated constructor stub
    }

  /**

  • @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    //response.getWriter().append("Served at: ").append(request.getContextPath());
    }

  /**

  • @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    request.setCharacterEncoding(“utf-8”);
    response.setCharacterEncoding(“utf-8”);
    response.setContentType(“text/html;charset=utf-8”);

    BookDaoImpl bookDaoImpl = new BookDaoImpl();

    if (request.getParameter(“id”)=="") {
    Book book = new Book();
    book.setBookName(request.getParameter(“bookName”));
    book.setPrice(Integer.valueOf(request.getParameter(“price”)));
    book.setDescription(request.getParameter(“description”));
    if (bookDaoImpl.BookAdd(book)) {
    response.getWriter().println("");
    }
    }
    //doGet(request, response);
    }
    }

删除的servlet
/**

• 删除书本

• @author Naive

• @date 2019年10月10日
  */
  @WebServlet("/DelServlet")
  public class DelServlet extends HttpServlet {
  private static final long serialVersionUID = 1L;

  /**

  • @see HttpServlet#HttpServlet()
    */
    public DelServlet() {
    super();
    // TODO Auto-generated constructor stub
    }

  /**

  • @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    request.setCharacterEncoding(“utf-8”);
    response.setCharacterEncoding(“utf-8”);
    response.setContentType(“text/html;charset=utf-8”);

    BookDaoImpl bookDaoImpl = new BookDaoImpl();
    if (bookDaoImpl.BookDelete(Integer.valueOf(request.getParameter(“id”)))) {
    response.getWriter().println("");
    }

    //response.getWriter().append("Served at: ").append(request.getContextPath());
    }

  /**

  • @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    doGet(request, response);
    }

}

这是显示更新数据的servlet
/**

• 显示数据

• @author Naive

• @date 2019年10月10日
  */
  @WebServlet("/AddServlet")
  public class UpServlet extends HttpServlet {
  private static final long serialVersionUID = 1L;

  /**

  • @see HttpServlet#HttpServlet()
    */
    public UpServlet() {
    super();
    // TODO Auto-generated constructor stub
    }

  /**

  • @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    request.setCharacterEncoding(“utf-8”);
    response.setCharacterEncoding(“utf-8”);
    response.setContentType(“text/html;charset=utf-8”);

    BookDaoImpl bookDaoImpl = new BookDaoImpl();
    request.setAttribute(“book”, bookDaoImpl.BookSelect(Integer.valueOf(request.getParameter(“id”))));
    request.getRequestDispatcher(“addbook.jsp”).forward(request, response);
    //response.getWriter().append("Served at: ").append(request.getContextPath());
    }

  /**

  • @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    doGet(request, response);
    }

}

运行结果：

点击新增书本可以输入要新增的书本

添加成功后会显示数据库里的书本信息

修改书本会显示书本的信息再加以修改


可以添加多本书