4.13课后作业
科技政策查询系统开发博客
一、系统设计思想
1.1 整体架构设计
本系统采用经典的MVC三层架构设计，将业务逻辑、数据访问和视图展示分离，提高代码的可维护性和可扩展性：
Model层：定义数据模型，封装政策和政策分类的属性和行为
Dao层：负责数据库操作，提供数据的增删改查功能
Servlet层：处理用户请求，调用Dao层获取数据，转发到相应的视图
View层：使用JSP技术展示数据，提供用户交互界面
1.2 核心功能设计
系统主要实现以下核心功能：
- 政策列表展示：分页展示所有政策信息，支持按发布日期排序
- 多条件搜索：支持按政策名称、发文文号、发文机构和全文内容搜索
- 分类筛选：左侧展示政策分类及数量统计，点击可筛选对应分类的政策
- 政策详情查看：点击"查看"按钮可查看政策的详细内容
- 移动端适配：提供专门的移动端页面，适配手机和平板设备
1.3 数据库设计
数据库设计了policy表存储政策信息，包含政策名称、类型、发文机构、发布日期、内容等字段。系统支持自动初始化数据库，创建表结构并插入测试数据。
二、源程序代码
2.1 数据模型层
Policy.java（政策实体类）
package com.hebei.tech.model;
import java.sql.Date;
public class Policy {
private String id;
private String policyName;
private String documentNumber;
private String policyType;
private String policyTypeName; // 分类名称
private String issuingAgency;
private Date issueDate;
private String content;
// 无参构造
public Policy() {}
// 列表页需要的构造
public Policy(String id, String policyName, String documentNumber, String policyType,
String policyTypeName, String issuingAgency, Date issueDate) {
this.id = id;
this.policyName = policyName;
this.documentNumber = documentNumber;
this.policyType = policyType;
this.policyTypeName = policyTypeName;
this.issuingAgency = issuingAgency;
this.issueDate = issueDate;
}
// Getter和Setter方法
public String getId() { return id; }
public void setId(String id) { this.id = id; }
public String getPolicyName() { return policyName; }
public void setPolicyName(String policyName) { this.policyName = policyName; }
public String getDocumentNumber() { return documentNumber; }
public void setDocumentNumber(String documentNumber) { this.documentNumber = documentNumber; }
public String getPolicyType() { return policyType; }
public void setPolicyType(String policyType) { this.policyType = policyType; }
public String getPolicyTypeName() { return policyTypeName; }
public void setPolicyTypeName(String policyTypeName) { this.policyTypeName = policyTypeName; }
public String getIssuingAgency() { return issuingAgency; }
public void setIssuingAgency(String issuingAgency) { this.issuingAgency = issuingAgency; }
public Date getIssueDate() { return issueDate; }
public void setIssueDate(Date issueDate) { this.issueDate = issueDate; }
public String getContent() { return content; }
public void setContent(String content) { this.content = content; }
}
PolicyKind.java（政策分类实体类）
package com.hebei.tech.model;
public class PolicyKind {
private String typeId;
private String type;
private int policyCount; // 该分类下的政策数量
// 无参构造
public PolicyKind() {}
// 全参构造
public PolicyKind(String typeId, String type, int policyCount) {
this.typeId = typeId;
this.type = type;
this.policyCount = policyCount;
}
// Getter和Setter方法
public String getTypeId() { return typeId; }
public void setTypeId(String typeId) { this.typeId = typeId; }
public String getType() { return type; }
public void setType(String type) { this.type = type; }
public int getPolicyCount() { return policyCount; }
public void setPolicyCount(int policyCount) { this.policyCount = policyCount; }
}
2.2 数据库工具类
DBUtil.java（数据库连接工具类）
package com.hebei.tech.util;
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class DBUtil {
private static final DruidDataSource dataSource;
static {
dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/febs?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&characterEncoding=UTF8&useUnicode=true");
dataSource.setUsername("root"); // 改为你的MySQL用户名
dataSource.setPassword("123456"); // 改为你的MySQL密码
dataSource.setInitialSize(5);
dataSource.setMaxActive(20);
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.3 数据访问层
PolicyDao.java（政策数据访问类）
package com.hebei.tech.dao;
import com.hebei.tech.model.Policy;
import com.hebei.tech.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class PolicyDao {
public List<Policy> findByCondition(String policyName, String docNumber,
String issuingAgency, String fullText,
String typeName, int pageNum, int pageSize) {
List<Policy> list = new ArrayList<>();
StringBuilder sql = new StringBuilder(
"SELECT p.id, p.name, p.document, p.type, " +
"p.organ, p.viadata FROM policy p WHERE 1=1"
);
List<Object> params = new ArrayList<>();
if (policyName != null && !policyName.isBlank()) {
sql.append(" AND p.name LIKE ?");
params.add("%" + policyName + "%");
}
if (docNumber != null && !docNumber.isBlank()) {
sql.append(" AND p.document LIKE ?");
params.add("%" + docNumber + "%");
}
if (issuingAgency != null && !issuingAgency.isBlank()) {
sql.append(" AND p.organ LIKE ?");
params.add("%" + issuingAgency + "%");
}
if (fullText != null && !fullText.isBlank()) {
sql.append(" AND (p.name LIKE ? OR p.text LIKE ?)");
params.add("%" + fullText + "%");
params.add("%" + fullText + "%");
}
if (typeName != null && !typeName.isBlank()) {
sql.append(" AND p.type = ?");
params.add(typeName);
}
sql.append(" ORDER BY p.viadata DESC, p.id DESC LIMIT ?, ?");
params.add((pageNum 1) pageSize);
params.add(pageSize);
try (Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
Policy policy = new Policy();
policy.setId(String.valueOf(rs.getLong("id")));
policy.setPolicyName(rs.getString("name"));
policy.setDocumentNumber(rs.getString("document"));
policy.setPolicyType(rs.getString("type"));
policy.setPolicyTypeName(rs.getString("type"));
policy.setIssuingAgency(rs.getString("organ"));
policy.setIssueDate(rs.getDate("viadata"));
list.add(policy);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public int countByCondition(String policyName, String docNumber,
String issuingAgency, String fullText, String typeName) {
StringBuilder sql = new StringBuilder("SELECT COUNT() FROM policy WHERE 1=1");
List<Object> params = new ArrayList<>();
if (policyName != null && !policyName.isBlank()) {
sql.append(" AND name LIKE ?");
params.add("%" + policyName + "%");
}
if (docNumber != null && !docNumber.isBlank()) {
sql.append(" AND document LIKE ?");
params.add("%" + docNumber + "%");
}
if (issuingAgency != null && !issuingAgency.isBlank()) {
sql.append(" AND organ LIKE ?");
params.add("%" + issuingAgency + "%");
}
if (fullText != null && !fullText.isBlank()) {
sql.append(" AND (name LIKE ? OR text LIKE ?)");
params.add("%" + fullText + "%");
params.add("%" + fullText + "%");
}
if (typeName != null && !typeName.isBlank()) {
sql.append(" AND type = ?");
params.add(typeName);
}
try (Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return rs.getInt(1);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public Policy findById(String id) {
String sql = "SELECT FROM policy WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setLong(1, Long.parseLong(id));
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
Policy policy = new Policy();
policy.setId(String.valueOf(rs.getLong("id")));
policy.setPolicyName(rs.getString("name"));
policy.setDocumentNumber(rs.getString("document"));
policy.setPolicyType(rs.getString("type"));
policy.setPolicyTypeName(rs.getString("type"));
policy.setIssuingAgency(rs.getString("organ"));
policy.setIssueDate(rs.getDate("viadata"));
policy.setContent(rs.getString("text"));
return policy;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
PolicyKindDao.java（政策分类数据访问类）
package com.hebei.tech.dao;
import com.hebei.tech.model.PolicyKind;
import com.hebei.tech.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class PolicyKindDao {
public String findNameById(String typeId) {
return typeId;
}
public List<PolicyKind> findAllWithCount() {
List<PolicyKind> list = new ArrayList<>();
String sql = "SELECT type, COUNT() as policy_count FROM policy GROUP BY type ORDER BY type";
try (Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
PolicyKind kind = new PolicyKind();
String typeName = rs.getString("type");
kind.setTypeId(typeName);
kind.setType(typeName);
kind.setPolicyCount(rs.getInt("policy_count"));
list.add(kind);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
}
2.4 Servlet控制层
PolicyListServlet.java（政策列表Servlet）
package com.hebei.tech.servlet;
import com.hebei.tech.dao.PolicyDao;
import com.hebei.tech.dao.PolicyKindDao;
import com.hebei.tech.model.Policy;
import com.hebei.tech.model.PolicyKind;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@WebServlet("/")
public class PolicyListServlet extends HttpServlet {
private final PolicyDao policyDao = new PolicyDao();
private final PolicyKindDao kindDao = new PolicyKindDao();
private static final int PAGE_SIZE = 10;
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {
String policyName = request.getParameter("policyName");
String docNumber = request.getParameter("docNumber");
String issuingAgency = request.getParameter("issuingAgency");
String fullText = request.getParameter("fullText");
String typeId = request.getParameter("typeId");
String pageNumStr = request.getParameter("pageNum");
int pageNum = 1;
if (pageNumStr != null && !pageNumStr.isBlank()) {
try {
pageNum = Integer.parseInt(pageNumStr);
} catch (NumberFormatException e) {
pageNum = 1;
}
}
List<PolicyKind> kindList = new ArrayList<>();
List<Policy> policyList = new ArrayList<>();
int totalCount = 0;
int totalPage = 0;
try {
kindList = kindDao.findAllWithCount();
policyList = policyDao.findByCondition(
policyName, docNumber, issuingAgency, fullText, typeId, pageNum, PAGE_SIZE
);
totalCount = policyDao.countByCondition(
policyName, docNumber, issuingAgency, fullText, typeId
);
totalPage = (totalCount + PAGE_SIZE 1) / PAGE_SIZE;
} catch (Exception e) {
e.printStackTrace();
}
request.setAttribute("kindList", kindList);
request.setAttribute("policyList", policyList);
request.setAttribute("totalCount", totalCount);
request.setAttribute("totalPage", totalPage);
request.setAttribute("currentPage", pageNum);
request.setAttribute("policyName", policyName);
request.setAttribute("docNumber", docNumber);
request.setAttribute("issuingAgency", issuingAgency);
request.setAttribute("fullText", fullText);
request.setAttribute("selectedTypeId", typeId);
request.getRequestDispatcher("/index.jsp").forward(request, response);
} catch (Exception e) {
e.printStackTrace();
response.setContentType("text/html;charset=UTF8");
response.getWriter().println("<html><body><h1>错误</h1><pre>" + e.toString() + "</pre></body></html>");
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
PolicyDetailServlet.java（政策详情Servlet）
package com.hebei.tech.servlet;
import com.hebei.tech.dao.PolicyDao;
import com.hebei.tech.model.Policy;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/detail")
public class PolicyDetailServlet extends HttpServlet {
private final PolicyDao policyDao = new PolicyDao();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String id = request.getParameter("id");
if (id == null || id.isBlank()) {
response.sendRedirect(request.getContextPath() + "/");
return;
}
Policy policy = policyDao.findById(id);
if (policy == null) {
response.sendRedirect(request.getContextPath() + "/");
return;
}
request.setAttribute("policy", policy);
request.getRequestDispatcher("/policyDetail.jsp").forward(request, response);
}
}
2.5 数据库初始化
InitDatabase.java（数据库初始化工具）
package com.hebei.tech.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class InitDatabase {
public static void main(String[] args) {
try {
Connection conn = DBUtil.getConnection();
System.out.println("数据库连接成功！");
// 检查policy表是否存在
String checkTableSql = "SHOW TABLES LIKE 'policy'";
PreparedStatement pstmt = conn.prepareStatement(checkTableSql);
ResultSet rs = pstmt.executeQuery();
if (!rs.next()) {
System.out.println("policy表不存在，正在创建...");
// 创建policy表
String createTableSql = """
CREATE TABLE policy (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
type varchar(255) DEFAULT NULL,
category varchar(255) DEFAULT NULL,
range varchar(255) DEFAULT NULL,
document varchar(255) DEFAULT NULL,
form varchar(255) DEFAULT NULL,
organ varchar(255) DEFAULT NULL,
viadata date DEFAULT NULL,
pubdata date DEFAULT NULL,
perdata date DEFAULT NULL,
field varchar(255) DEFAULT NULL,
theme varchar(255) DEFAULT NULL,
keyword varchar(255) DEFAULT NULL,
superior varchar(255) DEFAULT NULL,
precursor varchar(255) DEFAULT NULL,
succeed varchar(255) DEFAULT NULL,
state varchar(255) DEFAULT NULL,
text longtext,
pdf varchar(255) DEFAULT NULL,
redundancy varchar(255) DEFAULT NULL,
rank varchar(255) DEFAULT NULL,
policykey text,
newrank varchar(255) DEFAULT NULL,
year varchar(255) DEFAULT NULL,
newkey varchar(255) DEFAULT NULL,
secondtheme varchar(255) DEFAULT NULL,
allsum int(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
""";
PreparedStatement createStmt = conn.prepareStatement(createTableSql);
createStmt.execute();
System.out.println("policy表创建成功！");
// 插入测试数据
String[][] testData = {
{"关于促进科技创新的若干政策", "综合", "河北省人民政府", "冀政发[2022]1号", "20220115", "关于进一步促进河北省科技创新发展的若干政策措施..."},
{"科研机构改革实施方案", "科研机构改革", "河北省科学技术厅", "冀科发[2022]5号", "20220320", "为深化科研机构改革，提升创新能力..."},
{"科技计划管理办法", "科技计划管理", "河北省科学技术厅", "冀科计[2022]8号", "20220510", "河北省科技计划项目管理办法..."},
{"企业技术创新激励政策", "企业", "河北省工业和信息化厅", "冀工信[2022]12号", "20220625", "关于鼓励企业加大研发投入的政策..."},
{"高新技术产业化指南", "产业", "河北省发展和改革委员会", "冀发改高[2022]15号", "20220801", "河北省高新技术产业化发展指南..."},
{"科技人才培养计划", "科技人才", "河北省人力资源和社会保障厅", "冀人社发[2022]20号", "20220915", "关于加强科技人才引进和培养的若干措施..."},
{"农村科技推广政策", "农村科技与社会发展", "河北省农业农村厅", "冀农发[2022]25号", "20221020", "农村科技推广体系建设实施方案..."},
{"科技金融支持政策", "科技金融与税收", "河北省财政厅", "冀财金[2022]30号", "20221110", "科技型中小企业融资支持政策..."},
{"知识产权保护办法", "科技成果与知识产权", "河北省市场监督管理局", "冀市监发[2022]35号", "20221205", "加强知识产权保护和运用的若干规定..."}
};
String insertSql = "INSERT INTO policy (name, type, organ, document, viadata, text) VALUES (?, ?, ?, ?, ?, ?)";
PreparedStatement insertStmt = conn.prepareStatement(insertSql);
for (String[] data : testData) {
insertStmt.setString(1, data[0]);
insertStmt.setString(2, data[1]);
insertStmt.setString(3, data[2]);
insertStmt.setString(4, data[3]);
insertStmt.setString(5, data[4]);
insertStmt.setString(6, data[5]);
insertStmt.executeUpdate();
}
System.out.println("成功插入 " + testData.length + " 条测试数据！");
} else {
System.out.println("policy表已存在！");
// 检查是否有数据
String countSql = "SELECT COUNT() FROM policy";
PreparedStatement countStmt = conn.prepareStatement(countSql);
ResultSet countRs = countStmt.executeQuery();
if (countRs.next()) {
int count = countRs.getInt(1);
System.out.println("policy表中有 " + count + " 条记录");
}
}
rs.close();
pstmt.close();
conn.close();
System.out.println("数据库初始化完成！");
} catch (Exception e) {
e.printStackTrace();
}
}
}
三、运行结果截图
3.1 系统主界面
系统主界面展示了政策列表、左侧分类导航和顶部搜索栏。左侧显示了所有政策分类及每个分类下的政策数量，中间是政策列表，显示政策名称、发文机构、颁布日期和政策分类，右侧有"查看"按钮可查看政策详情。
3.2 搜索功能
系统支持多条件搜索，可以同时按政策名称、发文文号、发文机构和全文内容进行搜索。搜索结果会分页显示，每页显示10条记录。
3.3 分类筛选
点击左侧的政策分类，可以筛选出该分类下的所有政策。筛选结果同样支持分页显示。
3.4 政策详情
点击政策列表中的"查看"按钮，会跳转到政策详情页面，显示政策的完整信息，包括政策名称、发文文号、发文机构、发布日期和政策内容。
四、编程总结分析
4.1 系统优点
- 架构清晰：采用MVC三层架构，代码结构清晰，易于维护和扩展
- 功能完善：实现了政策列表展示、多条件搜索、分类筛选和详情查看等核心功能
- 性能优化：使用Druid连接池管理数据库连接，提高了数据库访问性能
- 用户体验好：支持分页显示，搜索条件会保留在页面上，方便用户操作
- 移动端适配：提供了专门的移动端页面，适配不同设备
4.2 存在的问题
- 安全问题：没有对用户输入进行严格的过滤，存在SQL注入风险
- 异常处理：异常处理比较简单，只是打印堆栈信息，没有给用户友好的提示
- 权限管理：没有实现用户登录和权限管理功能，任何人都可以访问系统
- 数据导入：数据导入功能比较简单，不支持批量导入和增量导入
- 界面美化：界面比较简单，不够美观
4.3 改进方向
- 添加安全过滤：使用PreparedStatement防止SQL注入，对用户输入进行XSS过滤
- 完善异常处理：统一异常处理，给用户友好的错误提示
- 实现权限管理：添加用户登录和角色权限管理功能
- 优化数据导入：支持Excel批量导入和增量导入数据
- 美化界面：使用Bootstrap等前端框架美化界面，提升用户体验
- 添加导出功能：支持将政策列表导出为Excel或PDF格式
- 实现全文检索：使用Elasticsearch等搜索引擎实现更高效的全文检索
六、总结
通过本次科技政策查询系统的开发，我深入理解了MVC架构的设计思想，掌握了Java Servlet和JSP技术的使用，学会了使用Druid连接池管理数据库连接。在开发过程中，我遇到了一些问题，比如数据库连接问题、SQL语句错误和前端页面布局问题，但通过查阅资料和不断调试，最终都解决了这些问题。
本次开发也让我认识到了自己的不足，比如在前端开发方面还有待提高，对安全问题的重视程度不够。在今后的学习中，我会加强前端技术的学习，提高自己的全栈开发能力，同时更加重视系统的安全性和用户体验。
需要我补充移动端页面的完整JSP代码和政策详情页的JSP代码吗？
浙公网安备 33010602011771号