JSP读取数据库、JDBC、Cookie存取

一个JSP读取数据库和Cookie存取存取的实例

项目运行结果如下

JDBC DbHelper.java

package util;

import java.sql.Connection;
import java.sql.DriverManager;

public class DbHelper {

    // 数据库驱动
    private static final String driver = "com.mysql.jdbc.Driver";
    // 数据库
    private static final String url = "jdbc:mysql://localhost:3306/db_test";
    // 数据库用户名和密码
    private static final String username = "root";
    private static final String password = "root";

    private static Connection connection = null;

    // 静态代码块，负责加载驱动
    static {
        try {
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 单例模式返回数据库连接对象
    public static Connection getConnection() throws Exception {

        if (connection == null) {
            connection = DriverManager.getConnection(url, username, password);
            return connection;
        }
        return connection;
    }

    // 主函数测试本类方法
    public static void main(String[] args) {
        System.out.println("主函数启动");
        try {
            Connection conn = DbHelper.getConnection();
            if (conn != null) {
                System.out.println("数据库连接正常");
            } else {
                System.out.println("数据库连接异常");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

实体类 Shopping.java

package entity;

public class Shopping {
    private int id;
    // 名称、产地、价格、库存、图片
    private String name;
    private String city;
    private int price;
    private int number;
    private String picture;

    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 String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    public String getPicture() {
        return picture;
    }

    public void setPicture(String picture) {
        this.picture = picture;
    }
}

逻辑层 ShoppingDAO.java

package dao;

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

import entity.Shopping;
import util.DbHelper;

// 业务逻辑类
public class ShoppingDAO {

    // 获取所有商品信息
    public ArrayList<Shopping> getAllShopping() {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rSet = null;
        // 商品集合
        ArrayList<Shopping> list = new ArrayList<Shopping>();

        try {
            conn = DbHelper.getConnection();
            String sql = "SELECT * FROM shopping;";
            stat = conn.prepareStatement(sql);
            rSet = stat.executeQuery();
            while (rSet.next()) {
                Shopping sh = new Shopping();
                sh.setId(rSet.getInt("id"));
                sh.setName(rSet.getString("name"));
                sh.setCity(rSet.getString("city"));
                sh.setNumber(rSet.getInt("number"));
                sh.setPrice(rSet.getInt("price"));
                sh.setPicture(rSet.getString("picture"));
                list.add(sh);
            }
            return list;

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            // 释放数据集对象
            if (rSet != null) {
                try {
                    rSet.close();
                    rSet = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            // 释放语句对象
            if (stat != null) {
                try {
                    stat.close();
                    stat = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    // 根据商品Id获得商品资料
    public Shopping getShoppingById(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rSet = null;

        try {
            connection = DbHelper.getConnection();
            String sql = "SELECT * FROM shopping WHERE id = ?;";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            rSet = statement.executeQuery();
            if (rSet.next()) {
                Shopping item = new Shopping();
                item.setId(rSet.getInt("id"));
                item.setName(rSet.getString("name"));
                item.setCity(rSet.getString("city"));
                item.setNumber(rSet.getInt("number"));
                item.setPrice(rSet.getInt("price"));
                item.setPicture(rSet.getString("picture"));
                return item;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            // 释放数据集对象
            if (rSet != null) {
                try {
                    rSet.close();
                    rSet = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            // 释放语句对象
            if (statement != null) {
                try {
                    statement.close();
                    statement = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }

        return null;
    }

    // 获取最近浏览的前5条商品信息
    public ArrayList<Shopping> getViewList(String list) {

        ArrayList<Shopping> itemlist = new ArrayList<Shopping>();
        int iCount = 5; // 每次返回前五条记录
        if (list != null && list.length() > 0) {
            String[] arr = list.split("#");
            System.out.println("当前缓存的个数：arr.length=" + arr.length);
            // 如果商品记录大于等于5条，则返回最新的5条数据，否则返回所有的数据
            if (arr.length >= 5) {
                for (int i = arr.length - 1; i >= arr.length - iCount; i--) {
                    itemlist.add(getShoppingById(Integer.parseInt(arr[i])));
                }
            } else {
                for (int i = arr.length - 1; i >= 0; i--) {
                    itemlist.add(getShoppingById(Integer.parseInt(arr[i])));
                }
            }
            return itemlist;
        } else {
            return null;
        }
    }

}

index.jsp

<body>
    <h2>商品展示</h2>
    <br>

    <div class="contentBox">
        <% ShoppingDAO dao = new ShoppingDAO(); ArrayList<Shopping> list = dao.getAllShopping(); if (list != null && list.size() > 0) { for (int i = 0; i < list.size(); i++) { Shopping sh = list.get(i); %>
        <div class="item">
            <!-- 点击图片跳转详情页 -->
            <a href="detail.jsp?id=<%=sh.getId()%>"> <img alt="" src="images/<%=sh.getPicture()%>" /></a>
            <div class="price">
                ¥<%=sh.getPrice()%></div>
            <div class="name"><%=sh.getName()%></div>
            <div class="city">
                产地:<%=sh.getCity()%></div>
        </div>
        <% } } %>
    </div>
</body>

detail.jsp

<body>

    <h1>商品详情</h1>
    <br>

    <% ShoppingDAO dao = new ShoppingDAO(); Shopping sh = dao.getShoppingById(Integer.parseInt(request.getParameter("id"))); if (sh != null) { %>
    <div class="content">
        <img alt="" src="images/<%=sh.getPicture()%>">
        <div class="rightBox">
            <div class="name"><%=sh.getName()%></div>
            <div class="price">
                ¥
                <%=sh.getPrice()%></div>
            <div class="city">
                产地：<%=sh.getCity()%></div>
            <div class="sales">
                销量：<%=sh.getNumber()%>件
            </div>
        </div>
    </div>
    <% } %>

    <!-- 浏览历史记录 -->
    <div class="history">
        浏览历史记录

        <% String list = ""; /* 从客户端读取Cookies集合 */ Cookie[] cookies = request.getCookies(); if (cookies != null && cookies.length > 0) { /* 遍历Cookies集合 */ for (Cookie c : cookies) { if (c.getName().equals("COOKIE")) { /* 获取Cookie的值 */ list = c.getValue(); } } } /* 用#分割各个id */ list += request.getParameter("id") + "#"; // 如果浏览记录超过100条，清零 String[] arr = list.split("#"); if (arr != null && arr.length > 0) { if (arr.length >= 100) { list = ""; } } /* 创建Cookie对象 */ Cookie cookie = new Cookie("COOKIE", list); System.out.println("当前缓存到Cookie的值(商品id)----" + list); /* 写入Cookie对象 */ response.addCookie(cookie); %>

        <!-- 浏览过的商品 -->
        <% ArrayList<Shopping> shopplist = dao.getViewList(list); if (shopplist != null && shopplist.size() > 0) { for (Shopping shh : shopplist) { %>

        <div class="historyItem">
            <a href="detail.jsp?id=<%=shh.getId()%>"><img alt="" src="images/<%=shh.getPicture()%>"> </a>
            <div class="name"><%=shh.getName()%></div>
            <div class="city">
                产地：<%=shh.getCity()%></div>
            <div class="price">
                ¥<%=shh.getPrice()%></div>
        </div>

        <% } } %>

    </div>

</body>

需要注意的点：
在项目中需要导入MySQL JAR包，否则JDBC 报错
在Tomcat中导入MySQL JAR包，否则JSP 页面报错
JSP 需要 import 导入实体类和逻辑层

完整源代码地址