第二篇——JDBC操作数据库之查询数据

基于MVC开发

步骤说明：
1、代码请参考——第一篇JDBC数据库之准备数据。

2、在第一篇基础上继续实现功能——第二篇JDBC数据库之查询数据。

以数据库中查找图书信息，并将信息显示在jsp页面当中为例，下面贴上代码片段：

一、准备数据库（详细请看第一篇）

二、创建实体类（Book.java）

1.与数据库对应的属性

2.设置setting和getter

package com.entity;

/**
 * Created by Ray on 2018/3/11 0011.
 **/
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;
    }
}

三、数据库连接（Dbconn.java）

1.连接数据库

2.关闭数据库连接

/**
 * Created by Ray on 2018/3/10 0010.
 **/
public class dbconn {
//    数据库,端口号
    private static final String url = "jdbc:mysql://localhost:3306/book?characterEncoding=utf-8";
//    数据库用户名
    private static final String username = "root";
//    数据库登录密码
    private static final String password = "root";
//    数据库驱动固定
    private static final String jdbcName = "com.mysql.jdbc.Driver";

   /**
   * @Author: Ray
   * @Date: 2018/3/11 0011
   * @Description: 数据库连接
   * @Return: Connection对象
   */
    public Connection getConnection(){
        //创建Connection连接对象
        Connection conn = null;
        try{
            //加载数据库驱动并将其注册到驱动管理列表中
            Class.forName(jdbcName);
            //连接数据库操作
            conn = DriverManager.getConnection(url,username,password);
        }catch (Exception e){
            e.printStackTrace();
        }
        //返回conn
        return conn;
    }

    /**
    * @Author: Ray
    * @Date: 2018/3/11 0011
    * @Description: 关闭数据库连接
    * @Return:
    */
    public void closeConnection(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                if(preparedStatement != null){
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }finally {
                        if(connection != null){
                            try {
                                connection.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }
    }
}

四、添加模型（BookModel.java）

1.查询所有图书信息的sql语句

2.将book对象添加到集合中

package com.model;

import com.dbutil.Dbconn;
import com.entity.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Ray on 2018/3/11 0011.
 **/
public class BookModel {

    Dbconn Dbconn = new Dbconn();
    Connection conn = null;
    PreparedStatement ps;
    ResultSet rs;

    /**
    * @Author: Ray
    * @Date: 2018/3/11 0011
    * @Description: 查询所有图书信息
    * @Return: booklist
    */
    public List<Book> bookList(){
        //创建booklist集合
        List booklist = new ArrayList();
        Book Book = null;
        try{
            //创建Connection连接
            conn = Dbconn.getConnection();
            //查询所有图书信息的sql语句
            String sql = "select * from booktable";
            //获取PreparedStatement
            ps = conn.prepareStatement(sql);
            //执行查询
            rs = ps.executeQuery();
            //判断光标向后移动,并判断是否有效
            while (rs.next()){
                //实例化Book对象
                Book = new Book();
                //对id属性赋值
                Book.setId(rs.getInt("id"));
                //对name属性赋值
                Book.setName(rs.getString("name"));
                //对price属性赋值
                Book.setPrice(rs.getDouble("price"));
                //对bookCount属性赋值
                Book.setBookCount(rs.getInt("bookCount"));
                //对author属性赋值
                Book.setAuthor(rs.getString("author"));
                //将book对象添加到集合中
                booklist.add(Book);
            }
            //关闭连接
            Dbconn.closeConnection(conn,ps,rs);
        }catch (Exception e){
            e.printStackTrace();
        }
        //返回booklist集合
        return booklist;
    }
}

五、添加控制器（BookList.java）

1.调用模型---调用查询方法

2.request传递值---请求转发

package com.control;

import com.model.BookModel;

import java.io.IOException;
import java.util.List;

/**
 * Created by Ray on 2018/3/11 0011.
 **/
public class BookList extends javax.servlet.http.HttpServlet {
    protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
        //调用模型
        BookModel BookModel = new BookModel();
        //调用查询方法,返回booklist集合
        List booklist = BookModel.bookList();
        //不同页面间传值,传递一次后,request失去作用
        request.setAttribute("booklist",booklist);
        //request请求转发 不会改变路径
        request.getRequestDispatcher("/bookList.jsp").forward(request,response);
    }
}

六、jsp页面（显示图书列表）

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page import="com.entity.*" %>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>图书列表</title>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">

	<link rel="stylesheet" type="text/css" href="css/bookList.css">


</head>

<body>
    <form action="" method="post">
        <table width="80%" align="center">
            <div class="list">图书列表</div>
            <tr>
                <td>图书编号</td>
                <td>图书名称</td>
                <td>图书价格</td>
                <td>图书数量</td>
                <td>图书作者</td>
                <td>图书修改</td>
                <td>图书删除</td>
            </tr>
            <c:forEach var="bookitem" items="${booklist}">
                <tr>
                    <td>${bookitem.id}</td>
                    <td>${bookitem.name}</td>
                    <td>${bookitem.price}</td>
                    <td>${bookitem.bookCount}</td>
                    <td>${bookitem.author}</td>
                    <td>dd</td>
                    <td>dd</td>
                </tr>
            </c:forEach>
        </table>
    </form>
</body>
</html>

七、web.xml（配置文件）

1.设置servlet

2.设置servlet-mapping

这个很关键,不能出错,否则访问会出现404错误

<?xml version="1.0" encoding="UTF-8"?>
<web-app>
    <servlet>
        <servlet-name>BookList</servlet-name>
        <servlet-class>com.control.BookList</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>BookList</servlet-name>
        <url-pattern>/BookList</url-pattern>
    </servlet-mapping>

</web-app>

八、bookList.css

位置:webapp-css

@CHARSET "UTF-8";

a {
    text-decoration: none;
}

ul {
    padding-bottom: 20px;
}

li {
    float: left;
    padding-left: 60px;
    list-style: none;
}

table{
    border:solid 1px;
}

.list{
    text-align:center;
    font-size:36px;
    font-weight: bold;
}

九、页面效果（未完善）