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

最新推荐文章于 2023-02-04 00:04:46 发布

weixin_34323858

最新推荐文章于 2023-02-04 00:04:46 发布

阅读量140

点赞数 1

原文链接：http://blog.51cto.com/lixiyu/1353185

版权

通过一个综合型的例子加深对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"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=GB18030">
<title>Insert title here</title>
</head>
<body>
<%request.setCharacterEncoding("UTF-8"); %>
<jsp:useBean id="book" class="com.lixiyu.Book"></jsp:useBean>
<jsp:setProperty property="*" name="book"/>
<%
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();
}
%>
</body>
<a href="insert.jsp">返回</a>
</html>

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

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加图书信息</title>
<script type="text/javascript">
    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;
        }
    }
</script>
</head>
<body>
    <form action="AddBook.jsp" method="post" οnsubmit="return check(this);">
        <table align="center" width="450">
            <tr>
                <td align="center" colspan="2">
                    <h2>添加图书信息</h2>
                    <hr>
                </td>
            </tr>
            <tr>
                <td align="right">图书名称：</td>
                <td><input type="text" name="name" /></td>
            </tr>
            <tr>
                <td align="right">价　　格：</td>
                <td><input type="text" name="price" /></td>
            </tr>
            <tr>
                <td align="right">数　　量：</td>
                <td><input type="text" name="bookCount" /></td>
            </tr>
            <tr>
                <td align="right">作　　者：</td>
                <td><input type="text" name="author" /></td>
            </tr>
            <tr>
                <td align="center" colspan="2">
                    <input type="submit" value="添　加">
                </td>
            </tr>
        </table>
    </form>
</body>
</html>

最后运行

成功：

查询（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<Book> list=new ArrayList<Book>();//实例化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
    }
}

在web.xml中添加映射：

<welcome-file-list>
   <welcome-file>index.html</welcome-file>
   <welcome-file>index.htm</welcome-file>
   <welcome-file>index.jsp</welcome-file>
   <welcome-file>default.html</welcome-file>
   <welcome-file>default.htm</welcome-file>
   <welcome-file>default.jsp</welcome-file>
 </welcome-file-list>
 <servlet>
   <description></description>
   <display-name>FindServlet</display-name>
   <servlet-name>FindServlet</servlet-name>
   <servlet-class>com.lixiyu.FindServlet</servlet-class>
 </servlet>
 <servlet-mapping>
   <servlet-name>FindServlet</servlet-name>
   <url-pattern>/FindServlet</url-pattern>
 </servlet-mapping>

创建book_list.jsp页面，用于显示查询到的所有图书信息

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.util.List"%>
<%@page import="com.lixiyu.Book"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>所有图书信息</title>
<style type="text/css">
    td{font-size: 12px;}
    h2{margin: 0px}
                                                                                                                                                                                                                                                                                                 
</style>
</head>
<body>
    <table align="center" width="450" border="1" height="180" bordercolor="white" bgcolor="" cellpadding="1" cellspacing="1">
        <tr bgcolor="white">
            <td align="center" colspan="5">
                <h2>所有图书信息</h2>
            </td>
        </tr>
        <tr align="center" bgcolor="#e1ffc1" >
            <!--  <td><b>ID</b></td>-->
            <td><b>图书名称</b></td>
            <td><b>价格</b></td>
            <td><b>数量</b></td>
            <td><b>作者</b></td>
        </tr>
            <%
                // 获取图书信息集合
                    List<Book>list = (List<Book>)request.getAttribute("list");
                    // 判断集合是否有效
                    if(list == null || list.size() < 1){
                        out.print("没有数据！");
                    }else{
                        // 遍历图书集合中的数据
                        for(Book book : list){
            %>
                <tr align="center" bgcolor="white">
                    <!--<td><%=book.getId()%></td>-->
                    <td><%=book.getName()%></td>
                    <td><%=book.getPrice()%></td>
                    <td><%=book.getbookCount()%></td>
                    <td><%=book.getAuthor()%></td>
                <td>
                <form action="UpdateServlet" method="post" οnsubmit="return check(this);">
                <input type="hidden" name="id" value="<%=book.getId()%>">
                <input type="text" name="bookCount" size="3">
                                                                                                                                                                                                                                                                                                             
                </form>
                                                                                                                                                                                                                                                                                                             
                </tr>
                                                                                                                                                                                                                                                                                                             
            <%
                    }
                }
            %>
    </table>
    <br>
</body>
</html>

创建index.jsp主页，用于请求查看所有图书信息：

<body>
<a href="FindServlet">查看所有图书</a>
</body>

运行该实例

修改（update）图书信息操作

在book_list.jsp中添多一列修改：

<td>
                <form action="UpdateServlet" method="post" οnsubmit="return check(this);">
                <input type="hidden" name="id" value="<%=book.getId()%>">
                <input type="text" name="bookCount" size="3">
                <input type="submit" value="修改">
                </form></td>

创建UpdateServlet.jsp编写doPost方法对图书信息请求进行处理：

package com.lixiyu;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
 * Servlet implementation class UpdateServlet
 */
public class UpdateServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        int id = Integer.valueOf(request.getParameter("id"));
        int bookCount = Integer.valueOf(request.getParameter("bookCount"));
        try {
            // 加载数据库驱动，注册到驱动管理器
            Class.forName("com.mysql.jdbc.Driver");
            // 数据库连接字符串
            String url = "jdbc:mysql://localhost:3306/db_test";
            // 数据库用户名
            String username = "root";
            // 数据库密码
            String password = "lixiyu";
            // 创建Connection连接
            Connection conn = DriverManager.getConnection(url,username,password);
            // 更新SQL语句
            String sql = "update tb_books set bookcount=? where id=?";
            // 获取PreparedStatement
            PreparedStatement ps = conn.prepareStatement(sql);
            // 对SQL语句中的第一个参数赋值
            ps.setInt(1, bookCount);
            // 对SQL语句中的第二个参数赋值
            ps.setInt(2, id);
            // 执行更新操作
            ps.executeUpdate();
            // 关闭PreparedStatement
            ps.close();
            // 关闭Connection
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 重定向到FindServlet
        response.sendRedirect("FindServlet");
    }
}

添加xml映射

<servlet>
    <display-name>UpdateServlet</display-name>
    <servlet-name>UpdateServlet</servlet-name>
    <servlet-class>com.lixiyu.UpdateServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>UpdateServlet</servlet-name>
    <url-pattern>/UpdateServlet</url-pattern>
  </servlet-mapping>

运行后

删除（delete）图书信息操作

编写删除操作的servlet，命名为DeleteServlet:

package com.lixiyu;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
 * Servlet implementation class DeleteServlet
 */
public class DeleteServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
                                                                                                                                         
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 获取图书id
        int id = Integer.valueOf(request.getParameter("id"));
        try {
            // 加载数据库驱动，注册到驱动管理器
            Class.forName("com.mysql.jdbc.Driver");
            // 数据库连接字符串
            String url = "jdbc:mysql://localhost:3306/db_test";
            // 数据库用户名
            String username = "root";
            // 数据库密码
            String password = "lixiyu";
            // 创建Connection连接
            Connection conn = DriverManager.getConnection(url,username,password);
            // 删除图书信息的SQL语句
            String sql = "delete from tb_books where id=?";
            // 获取PreparedStatement
            PreparedStatement ps = conn.prepareStatement(sql);
            // 对SQL语句中的第一个占位符赋值
            ps.setInt(1, id);
            // 执行更新操作
            ps.executeUpdate();
            // 关闭PreparedStatement
            ps.close();
            // 关闭Connection
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 重定向到FindServlet
        response.sendRedirect("FindServlet");
    }
}

添加xml映射：

<servlet>
    <description></description>
    <display-name>DeleteServlet</display-name>
    <servlet-name>DeleteServlet</servlet-name>
    <servlet-class>com.lixiyu.DeleteServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>DeleteServlet</servlet-name>
    <url-pattern>/DeleteServlet</url-pattern>
  </servlet-mapping>
</web-app>

整合CRUD操作

在前面book_list.jsp页面中进行整合：

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.util.List"%>
<%@page import="com.lixiyu.Book"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>所有图书信息</title>
<style type="text/css">
td{font-size: 12px;}
h2{margin: 0px}
</style>
</head>
<body>
<table align="center" width="450" border="1" height="180" bordercolor="white" bgcolor="" cellpadding="1" cellspacing="1">
<tr bgcolor="white">
<td align="center" colspan="5">
<h2>所有图书信息</h2>
</td>
</tr>
<tr align="center" bgcolor="#e1ffc1" >
<!--  <td><b>ID</b></td>-->
<td><b>图书名称</b></td>
<td><b>价格</b></td>
<td><b>数量</b></td>
<td><b>作者</b></td>
<td><b>修改数量</b></td>
<td><b>删 除</b></td>
</tr>
<%
// 获取图书信息集合
List<Book>list = (List<Book>)request.getAttribute("list");
// 判断集合是否有效
if(list == null || list.size() < 1){
out.print("没有数据！");
}else{
// 遍历图书集合中的数据
for(Book book : list){
%>
<tr align="center" bgcolor="white">
<!--<td><%=book.getId()%></td>-->
<td><%=book.getName()%></td>
<td><%=book.getPrice()%></td>
<td><%=book.getbookCount()%></td>
<td><%=book.getAuthor()%></td>
<td>
<form action="UpdateServlet" method="post" οnsubmit="return check(this);">
<input type="hidden" name="id" value="<%=book.getId()%>">
<input type="text" name="bookCount" size="3">
<input type="submit" value="修改">
</form>
</td>
<td><a href="DeleteServlet?id=<%=book.getId() %>">删除</a>
</td>
</tr>
<%
}
}
%>
</table>
<br>
<center>
<form   action="insert.jsp" method="post" οnsubmit="return check(this);">
 <input type="submit" name="" value="添加数据" >
</form></center>
</body>
</html>

将前面AddBook.jsp中的页面链接进行修改