JDBC操作数据库CRUD综合应用实例



通过一个综合型的例子加深对JDBC操作数据库的增、删、改、查的运用。经典的图书信息录入实例设计数据库CREATETABLE`tb_books`(`id`int(10)unsignedNOTNULLAUTO_I

通过一个综合型的例子加深对JDBC操作数据库的增、删、改、查的运用。

经典的图书信息录入实例

设计数据库

CREATE TABLE `tb_books` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `price` double NOT NULL, `bookCount` int(10) unsigned NOT NULL, `author` varchar(45) NOT NULL, PRIMARY KEY (`id`) )

写一个Book类对图书信息进行封装

package com.lixiyu; public class Book { private int id; private String name; private double price; private int bookCount; private String author; public int getId(){ return id; } public void setId(int id){ this.id=id; } public String getName(){ return name; } public void setName(String name){ this.name=name; } public double getPrice(){ return price; } public void setPrice(double price){ this.price=price; } public int getbookCount(){ return bookCount; } public void setbookCount(int bookCount){ this.bookCount=bookCount; } public String getAuthor(){ return author; } public void setAuthor(String author){ this.author=author; } }

添加（insert）图书信息操作

创建AddBook.jsp页面，用于对添加图书信息进行处理

<%@ page language="java" contentType="text/html; charset=GB18030" pageEncoding="GB18030"%> <%@page import="java.sql.Connection"%> <%@page import="java.sql.DriverManager"%> <%@page import="java.sql.PreparedStatement"%> <%request.setCharacterEncoding("UTF-8"); %> <% try{ Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动，注册到驱动管理器 String url="jdbc:mysql://localhost:3306/db_test";//数据库连接字符串 String username="root";//数据库用户名 String password="lixiyu";//数据库密码 Connection conn=DriverManager.getConnection(url,username,password);//创建Connection连接 String sql="insert into tb_books(name,price,bookCount,author)values(?,?,?,?)";//添加图书信息sql语句 PreparedStatement ps=conn.prepareStatement(sql);//获取PreparedStatement ps.setString(1,book.getName());//对SQL语句中的第1个参数赋值 ps.setDouble(2,book.getPrice()); ps.setInt(3,book.getbookCount()); ps.setString(4,book.getAuthor());//对SQL语句中的第4个参数赋值 int row=ps.executeUpdate();//执行更新操作，返回所影响的行数 if(row>0){ out.print("成功添加了"+row+"条数据"); } ps.close(); conn.close(); }catch(Exception e){ out.print("图书信息添加失败！"); e.printStackTrace(); } %>

创建insert.jsp，用于创建添加图书信息所需的表单

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> function check(form){ with(form){ if(name.value == ""){ alert("图书名称不能为空"); return false; } if(price.value == ""){ alert("价格不能为空"); return false; } if(author.value == ""){ alert("作者不能为空"); return false; } return true; } }

最后运行

查询（select）图书信息操作

创建FindServlet的servlet对象用于查询所有图书的信息。编写doGet()方法，建立数据库连接，并将所有查询数据集合放置HttpServletRequest对象中，将请求转发到jsp页面中：

package com.lixiyu; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * Servlet implementation class FindServlet */ public class FindServlet extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public FindServlet() { 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 try{ Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://localhost:3306/db_test"; String username="root"; String password="lixiyu"; Connection conn=DriverManager.getConnection(url,username,password); Statement stmt=conn.createStatement();//获取statement对象 String sql="select * from tb_books"; ResultSet rs=stmt.executeQuery(sql); List list=new ArrayList();//实例化list对象 while(rs.next()){ Book book=new Book(); book.setId(rs.getInt("id"));//对id属性赋值 book.setName(rs.getString("name")); book.setPrice(rs.getDouble("price")); book.setbookCount(rs.getInt("bookCount")); book.setAuthor(rs.getString("author")); list.add(book);//将图书对象添加到集合中 } request.setAttribute("list", list);//将图书集合放置到request中 rs.close();//关闭ResultSet stmt.close();//关闭Statement conn.close();//关闭Connection }catch(ClassNotFoundException e){ e.printStackTrace(); }catch(SQLException e){ e.printStackTrace(); } request.getRequestDispatcher("book_list.jsp").forward(request, response);//请求转发到book_List.jsp } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub } }