一:通过JDBC项数据库添加数据

创建图书信息tb_books表,结构如图所示

创建名称为Book 的类

package com.book.web3;
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;
    }
}

创建jdbc.jsp页面,用于添加数据的表单页面,该表单提交到jdbcresult.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>
<body>
<form action="jdbcresult.jsp" method="post">
<table align="center" border="1" width="50%" cellpadding="6">  
    <tr>
        <th colspan="2" align="center" >添加图书信息</th><%--th加粗, cellpadding和android padding含义一样--%>
    </tr>
    <tr>
        <td align="center" >图书编号:</td>   
        <td align="left" ><input type="text" name="id"></td>
    </tr>
    <tr>
        <td align="center" >图书名称:</td> 
        <td align="left" ><input type="text" name="name"></td>
    </tr>
    <tr>
        <td align="center" >价        格:</td>
        <td align="left" ><input type="text" name="price"></td>
    </tr>
    <tr>
        <td align="center" >数        量:</td>
        <td align="left" ><input type="text" name="bookCount"></td>
    </tr>
    <tr>
        <td align="center" >作        者:</td>
        <td align="left" ><input type="text" name="author"></td>
    </tr>
    <tr>
        <th colspan="2" align="center" ><input type="submit" name="submit" value="添加"></th>
    </tr>
</table>
</form>
</body>
</html>

创建jdbcresult.jsp页面,该页面通过JDBC提交的图书信息添加到数据库

<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@page import="java.sql.Connection" %>
<%@page import="java.sql.DriverManager" %>

    <% request.setCharacterEncoding("UTF-8"); %>  
<jsp:useBean id="book" class="com.book.web3.Book"></jsp:useBean>
<jsp:setProperty property="*" name="book"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<body>
<%
try{
    Class.forName("com.mysql.jdbc.Driver");
    String  url = "jdbc:mysql://localhost:3306/student";
    String userName = "roots"; // 用户名
    String userPwd = "123456"; // 密码
    Connection conn = DriverManager.getConnection(url, userName, userPwd);
    String sql = "insert into tb_books(id,name,price,bookCount, author)values(?,?,?,?,?)";

    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setInt(1, book.getId());
    ps.setString(2, book.getName());
    ps.setDouble(3, book.getPrice());
    ps.setInt(4, book.getBookCount());
    ps.setString(5, book.getAuthor());

    int row = ps.executeUpdate();
    if(row > 0){
        out.print("成功添加了 " + row + "条数据! ");
    }

    ps.close();
    conn.close();
}catch(Exception e){
    out.print("图书信息添加失败  " + e.toString());
}
%>
</body>
</html>

在jdbcresult.jsp页面中,首先通过jsp:useBean实例化JavaBean的对象Book,并通过jsp:setProperty对Book对象中的属性赋值,jsp:setProperty标签中property属性的值可以设置为’ * ‘,作用是将与表单中同名的属性值赋值给JavaBean对象中的同名属性,使用这种方式就不必对JavaBean中的属性一一进行赋值,减少代码量

结果展示:

二:查询数据

使用JDBC查询数据操作后,需要通过ResultSet对象来装载查询结果集。

创建名称为 JdbcFindServlet的Servlet对象,用于查询图书信息

package com.book.web3;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class JdbcFindServlet extends HttpServlet  {
    private static final long serialVersionUID = 1L;  

    /** 
     * 构造函数 
     */  
    public JdbcFindServlet()  
    {  
        super();  
    }  

    /** 
     * 初始化 
     */  
    public void init() throws ServletException  
    {}  

    /** 
     * doGet()方法 
     */  
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException  
    {  
        doPost(request, response);  
    }  

    /** 
     * doPost()方法 
     */  
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException  {  
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String  url = "jdbc:mysql://localhost:3306/student";
            String userName = "roots"; // 用户名
            String userPwd = "123456"; // 密码
            Connection conn = DriverManager.getConnection(url, userName, userPwd);
            Statement stmt = conn.createStatement();
            String sql = "select * from tb_books";
            ResultSet rs = stmt.executeQuery(sql);

            List<Book> list = new ArrayList<Book>();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getInt("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);
            rs.close();
            stmt.close();
            conn.close();    
        } catch (Exception e) {
            // TODO: handle exception
        }
        request.getRequestDispatcher("jdbcfindservlet.jsp").forward(request, response);
    }        
    /** 
     * 销毁 
     */  
    public void destroy()  
    {  
        super.destroy();  
    }  
}  

JdbcFindServlet对象在web.xml中的配置如下

<servlet>
        <servlet-name>JdbcFindServlet</servlet-name>
        <servlet-class>com.book.web3.JdbcFindServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>JdbcFindServlet</servlet-name>
        <url-pattern>/findServlet.do</url-pattern>  <!-- 根据不同的url来调用不同的servlet来进行处理。 --> 
    </servlet-mapping>

创建jdbcfindservlet.jsp页面,用于显示查询信息,下面用的脚本处理的相关点击事件

<%@page import="java.util.ArrayList"%>
<%@page import="java.util.List"%>
<%@page import="com.book.web3.Book"%>

<%@ 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>        
<script type="text/javascript">  
    function check(form) {  
        with (form) {  
            if (bookCount.value == "") {  
                alert("请输入更新数量!");  
                return false;  
            }  
            if (isNaN(bookCount.value)) {  
                alert("格式错误!");  
                return false;  
            }  
            return true;  
        }  
    }  
</script>     
</head>
<body>


<table id="table1" align="center" border="1" width="50%" cellpadding="6">  
    <tr>
        <th colspan="6" align="center" >查询到的图书信息</th><%--th加粗, cellpadding和android padding含义一样--%>
    </tr>
    <tr>
        <th align="center" >ID</th> 
        <th align="center" >图书名称</th> 
        <th align="center" >价格</th>
        <th align="center" >数量</th>
        <th align="center" >作者</th>
        <th align="center" >修改数量</th>
    </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">
            <td> <%= book.getId() %></td>
            <td> <%= book.getName() %></td>
            <td> <%= book.getPrice() %></td>
            <td> <%= book.getBookCount() %></td>
            <td> <%= book.getAuthor() %></td>
            <td>
            <form action="updateServlet.do" 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>
        </tr>

    <%    
        }
    }
    %>
</table>

</body>
</html>

创建程序入口界面,index.jsp页面

<a href="findServlet.do">查看所有图书</a>  //和web.xml配置下<url-pattern>一致

运行结果:

三:修改数据

在上面的查询界面中已经预留了修改的页面,修改的表单中含有两个属性id与bookCount,因此指定id作为修改条件

创建修改图书信息名称为JdbcUpdateServlet请求的Servlet对象

package com.book.web3;
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;
public class JdbcUpdateServlet extends HttpServlet  {
    private static final long serialVersionUID = 1L;  

    /** 
     * 构造函数 
     */  
    public JdbcUpdateServlet()  
    {  
        super();  
    }  

    /** 
     * 初始化 
     */  
    public void init() throws ServletException  
    {}  

    /** 
     * doGet()方法 
     */  
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException  
    {  
        doPost(request, response);  
    }  

    /** 
     * doPost()方法 
     */  
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException  {  
        try {
            int id = Integer.valueOf(request.getParameter("id"));
            int bookCount = Integer.valueOf(request.getParameter("bookCount"));

            Class.forName("com.mysql.jdbc.Driver");
            String  url = "jdbc:mysql://localhost:3306/student";
            String userName = "roots"; // 用户名
            String userPwd = "123456"; // 密码
            Connection conn = DriverManager.getConnection(url, userName, userPwd);
            String sql = "update tb_books set bookCount=? where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, bookCount);
            ps.setInt(2, id);
            ps.executeUpdate();

            ps.close();
            conn.close();

        } catch (Exception e) {
            // TODO: handle exception
        }
    }  

    /** 
     * 销毁 
     */  
    public void destroy()  
    {  
        super.destroy();  
    }  
}  

web.xml配置

<servlet>
        <servlet-name>JdbcUpdateServlet</servlet-name>
        <servlet-class>com.book.web3.JdbcUpdateServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>JdbcUpdateServlet</servlet-name>
        <url-pattern>/updateServlet.do</url-pattern>  <!--根据不同的url来调用不同的servlet来进行处理。 --> 
    </servlet-mapping>

操作结果:

修改前

修改后

后面的删除就不介绍了,差不多一样的!

感谢看到这!加油!


一个好奇的人