3.29

河北省科技政策树形查询系统设计方案
项目结构
hebei-tech-policy/
├── src/
│ ├── main/
│ │ ├── java/
│ │ │ └── com/hebei/techpolicy/
│ │ │ ├── controller/ # 控制器
│ │ │ │ └── PolicyController.java
│ │ │ ├── dao/ # 数据访问层
│ │ │ │ ├── PolicyDao.java
│ │ │ │ └── PolicyTypeDao.java
│ │ │ ├── model/ # 数据模型
│ │ │ │ ├── Policy.java
│ │ │ │ └── PolicyType.java
│ │ │ ├── service/ # 业务逻辑层
│ │ │ │ ├── PolicyService.java
│ │ │ │ └── impl/
│ │ │ │ └── PolicyServiceImpl.java
│ │ │ └── util/ # 工具类
│ │ │ └── DBUtil.java
│ │ ├── resources/
│ │ │ ├── db.properties # 数据库配置
│ │ │ └── log4j.properties # 日志配置
│ │ └── webapp/
│ │ ├── WEB-INF/
│ │ │ ├── lib/ # 依赖库
│ │ │ └── web.xml # 部署描述符
│ │ ├── css/ # 样式文件
│ │ │ └── style.css
│ │ ├── js/ # JavaScript文件
│ │ │ └── main.js
│ │ └── index.jsp # 主页面
├── pom.xml # Maven配置文件
└── README.md
数据库设计

  1. 政策分类表 (policy_type)
    sql
    复制
    CREATE TABLE policy_type (
    type_id varchar(4) NOT NULL COMMENT '分类ID',
    type_name varchar(50) NOT NULL COMMENT '分类名称',
    PRIMARY KEY (type_id),
    UNIQUE KEY idx_type_name (type_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='政策分类表';
  2. 政策表 (policy)
    sql
    复制
    CREATE TABLE policy (
    id varchar(20) NOT NULL COMMENT '唯一编码(年月日序号)',
    policy_index varchar(50) DEFAULT NULL COMMENT '政策索引号',
    policy_name varchar(200) NOT NULL COMMENT '政策名称',
    document_number varchar(100) DEFAULT NULL COMMENT '序号或文号(发文字号)',
    policy_type varchar(4) NOT NULL COMMENT '政策分类(四位编码)',
    scope varchar(6) DEFAULT NULL COMMENT '施行范围(六位行政区划代码)',
    issuing_agency varchar(200) DEFAULT NULL COMMENT '发文机构(制定机关)',
    issue_date date DEFAULT NULL COMMENT '发文日期(通过日期)',
    record_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '录入日期',
    implementation_date date DEFAULT NULL COMMENT '施行日期',
    subject_words varchar(200) DEFAULT NULL COMMENT '主题词(3-5个)',
    keywords varchar(300) DEFAULT NULL COMMENT '关键词(5-10个)',
    superior_policy varchar(200) DEFAULT NULL COMMENT '上位政策',
    predecessor_policy varchar(200) DEFAULT NULL COMMENT '前驱政策',
    successor_policy varchar(200) DEFAULT NULL COMMENT '后继政策',
    status varchar(10) NOT NULL DEFAULT '有效' COMMENT '时效状态(有效/无效)',
    content text DEFAULT NULL COMMENT '文件正文',
    pdf_path varchar(255) DEFAULT NULL COMMENT 'PDF文档路径',
    index_status varchar(10) NOT NULL DEFAULT '未标引' COMMENT '标引状态(已标引/待标引/未标引)',
    PRIMARY KEY (id),
    KEY idx_policy_type (policy_type),
    KEY idx_scope (scope),
    KEY idx_issue_date (issue_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='政策表';
    核心代码实现
  3. 数据库连接工具类 (DBUtil.java)
    java
    复制
    package com.hebei.techpolicy.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {
private static String driver;
private static String url;
private static String username;
private static String password;

static {
    try {
        InputStream is = DBUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties props = new Properties();
        props.load(is);
        
        driver = props.getProperty("jdbc.driver");
        url = props.getProperty("jdbc.url");
        username = props.getProperty("jdbc.username");
        password = props.getProperty("jdbc.password");
        
        Class.forName(driver);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(url, username, password);
}

public static void closeConnection(Connection conn) {
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

}
2. 政策分类数据访问层 (PolicyTypeDao.java)
java
复制
package com.hebei.techpolicy.dao;

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

import com.hebei.techpolicy.model.PolicyType;
import com.hebei.techpolicy.util.DBUtil;

public class PolicyTypeDao {

public List<PolicyType> getAllPolicyTypes() {
    List<PolicyType> types = new ArrayList<>();
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try {
        conn = DBUtil.getConnection();
        String sql = "SELECT type_id, type_name FROM policy_type ORDER BY type_id";
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        
        while (rs.next()) {
            PolicyType type = new PolicyType();
            type.setTypeId(rs.getString("type_id"));
            type.setTypeName(rs.getString("type_name"));
            types.add(type);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            DBUtil.closeConnection(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    return types;
}

public PolicyType getPolicyTypeById(String typeId) {
    PolicyType type = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try {
        conn = DBUtil.getConnection();
        String sql = "SELECT type_id, type_name FROM policy_type WHERE type_id = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, typeId);
        rs = pstmt.executeQuery();
        
        if (rs.next()) {
            type = new PolicyType();
            type.setTypeId(rs.getString("type_id"));
            type.setTypeName(rs.getString("type_name"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            DBUtil.closeConnection(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    return type;
}

}
3. 政策数据访问层 (PolicyDao.java)
java
复制
package com.hebei.techpolicy.dao;

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

import com.hebei.techpolicy.model.Policy;
import com.hebei.techpolicy.util.DBUtil;

public class PolicyDao {

public List<Policy> getPoliciesByType(String typeId, int page, int pageSize) {
    List<Policy> policies = new ArrayList<>();
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try {
        conn = DBUtil.getConnection();
        String sql = "SELECT * FROM policy WHERE policy_type LIKE ? AND status = '有效' " +
                     "ORDER BY issue_date DESC LIMIT ?, ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, typeId + "%");
        pstmt.setInt(2, (page - 1) * pageSize);
        pstmt.setInt(3, pageSize);
        rs = pstmt.executeQuery();
        
        while (rs.next()) {
            Policy policy = new Policy();
            policy.setId(rs.getString("id"));
            policy.setPolicyName(rs.getString("policy_name"));
            policy.setDocumentNumber(rs.getString("document_number"));
            policy.setPolicyType(rs.getString("policy_type"));
            policy.setIssueDate(rs.getDate("issue_date"));
            policy.setIssuingAgency(rs.getString("issuing_agency"));
            policies.add(policy);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            DBUtil.closeConnection(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    return policies;
}

public int countPoliciesByType(String typeId) {
    int count = 0;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try {
        conn = DBUtil.getConnection();
        String sql = "SELECT COUNT(*) FROM policy WHERE policy_type LIKE ? AND status = '有效'";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, typeId + "%");
        rs = pstmt.executeQuery();
        
        if (rs.next()) {
            count = rs.getInt(1);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            DBUtil.closeConnection(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    return count;
}

}
4. 业务逻辑层 (PolicyService.java 和 PolicyServiceImpl.java)
java
复制
package com.hebei.techpolicy.service;

import java.util.List;

import com.hebei.techpolicy.model.Policy;
import com.hebei.techpolicy.model.PolicyType;

public interface PolicyService {
List getAllPolicyTypes();
PolicyType getPolicyTypeById(String typeId);
List getPoliciesByType(String typeId, int page, int pageSize);
int countPoliciesByType(String typeId);
}
java
复制
package com.hebei.techpolicy.service.impl;

import java.util.List;

import com.hebei.techpolicy.dao.PolicyDao;
import com.hebei.techpolicy.dao.PolicyTypeDao;
import com.hebei.techpolicy.model.Policy;
import com.hebei.techpolicy.model.PolicyType;
import com.hebei.techpolicy.service.PolicyService;

public class PolicyServiceImpl implements PolicyService {

private PolicyTypeDao typeDao = new PolicyTypeDao();
private PolicyDao policyDao = new PolicyDao();

@Override
public List<PolicyType> getAllPolicyTypes() {
    return typeDao.getAllPolicyTypes();
}

@Override
public PolicyType getPolicyTypeById(String typeId) {
    return typeDao.getPolicyTypeById(typeId);
}

@Override
public List<Policy> getPoliciesByType(String typeId, int page, int pageSize) {
    return policyDao.getPoliciesByType(typeId, page, pageSize);
}

@Override
public int countPoliciesByType(String typeId) {
    return policyDao.countPoliciesByType(typeId);
}

}
5. 控制器 (PolicyController.java)
java
复制
package com.hebei.techpolicy.controller;

import java.io.IOException;
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;

import com.hebei.techpolicy.model.Policy;
import com.hebei.techpolicy.model.PolicyType;
import com.hebei.techpolicy.service.PolicyService;
import com.hebei.techpolicy.service.impl.PolicyServiceImpl;

@WebServlet("/policy")
public class PolicyController extends HttpServlet {
private static final long serialVersionUID = 1L;
private PolicyService policyService = new PolicyServiceImpl();
private static final int PAGE_SIZE = 10;

protected void doGet(HttpServletRequest request, HttpServletResponse response) 
        throws ServletException, IOException {
    String action = request.getParameter("action");
    String typeId = request.getParameter("typeId");
    String pageStr = request.getParameter("page");
    int page = 1;
    
    if (pageStr != null && !pageStr.isEmpty()) {
        page = Integer.parseInt(pageStr);
    }
    
    if (typeId == null || typeId.isEmpty()) {
        typeId = "0100"; // 默认显示综合类政策
    }
    
    List<PolicyType> types = policyService.getAllPolicyTypes();
    List<Policy> policies = policyService.getPoliciesByType(typeId, page, PAGE_SIZE);
    int totalCount = policyService.countPoliciesByType(typeId);
    int totalPages = (int) Math.ceil((double) totalCount / PAGE_SIZE);
    
    PolicyType currentType = policyService.getPolicyTypeById(typeId);
    
    request.setAttribute("types", types);
    request.setAttribute("policies", policies);
    request.setAttribute("currentType", currentType);
    request.setAttribute("currentPage", page);
    request.setAttribute("totalPages", totalPages);
    request.setAttribute("totalCount", totalCount);
    
    request.getRequestDispatcher("/index.jsp").forward(request, response);
}

}
6. 数据模型 (Policy.java 和 PolicyType.java)
java
复制
package com.hebei.techpolicy.model;

import java.util.Date;

public class Policy {
private String id;
private String policyIndex;
private String policyName;
private String documentNumber;
private String policyType;
private String scope;
private String issuingAgency;
private Date issueDate;
private Date recordDate;
private Date implementationDate;
private String subjectWords;
private String keywords;
private String superiorPolicy;
private String predecessorPolicy;
private String successorPolicy;
private String status;
private String content;
private String pdfPath;
private String indexStatus;

// Getters and Setters
public String getId() {
    return id;
}
public void setId(String id) {
    this.id = id;
}
// 其他getter和setter方法...

}
java
复制
package com.hebei.techpolicy.model;

public class PolicyType {
private String typeId;
private String typeName;

// Getters and Setters
public String getTypeId() {
    return typeId;
}
public void setTypeId(String typeId) {
    this.typeId = typeId;
}
public String getTypeName() {
    return typeName;
}
public void setTypeName(String typeName) {
    this.typeName = typeName;
}

}
前端页面 (index.jsp)
jsp
复制
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

河北省科技政策查询系统

河北省科技政策查询系统

    <div class="content">
        <div class="sidebar">
            <h2>政策分类</h2>
            <ul class="policy-tree">
                <c:forEach items="${types}" var="type">
                    <c:choose>
                        <c:when test="${type.typeId.length() == 4}">
                            <li class="parent-item ${currentType.typeId.startsWith(type.typeId.substring(0, 2)) ? 'active' : ''}">
                                <a href="policy?typeId=${type.typeId}">${type.typeName}</a>
                                <ul>
                                    <c:forEach items="${types}" var="subType">
                                        <c:if test="${subType.typeId.startsWith(type.typeId.substring(0, 2)) && subType.typeId.length() > 4}">
                                            <li class="child-item ${currentType.typeId == subType.typeId ? 'active' : ''}">
                                                <a href="policy?typeId=${subType.typeId}">${subType.typeName}</a>
                                            </li>
                                        </c:if>
                                    </c:forEach>
                                </ul>
                            </li>
                        </c:when>
                    </c:choose>
                </c:forEach>
            </ul>
        </div>
        
        <div class="main-content">
            <h2>${currentType.typeName}政策</h2>
            <div class="policy-list">
                <table>
                    <thead>
                        <tr>
                            <th width="20%">政策名称</th>
                            <th width="15%">文号</th>
                            <th width="20%">发文机构</th>
                            <th width="15%">发文日期</th>
                            <th width="30%">操作</th>
                        </tr>
                    </thead>
                    <tbody>
                        <c:forEach items="${policies}" var="policy">
                            <tr>
                                <td>${policy.policyName}</td>
                                <td>${policy.documentNumber}</td>
                                <td>${policy.issuingAgency}</td>
                                <td>${policy.issueDate}</td>
                                <td>
                                    <a href="#" class="btn">查看详情</a>
                                    <a href="#" class="btn">下载PDF</a>
                                </td>
                            </tr>
                        </c:forEach>
                    </tbody>
                </table>
            </div>
            
            <div class="pagination">
                <c:if test="${currentPage > 1}">
                    <a href="policy?typeId=${currentType.typeId}&page=${currentPage - 1}" class="page-link">上一页</a>
                </c:if>
                
                <c:forEach begin="1" end="${totalPages}" var="i">
                    <c:choose>
                        <c:when test="${i == currentPage}">
                            <span class="current-page">${i}</span>
                        </c:when>
                        <c:otherwise>
                            <a href="policy?typeId=${currentType.typeId}&page=${i}" class="page-link">${i}</a>
                        </c:otherwise>
                    </c:choose>
                </c:forEach>
                
                <c:if test="${currentPage < totalPages}">
                    <a href="policy?typeId=${currentType.typeId}&page=${currentPage + 1}" class="page-link">下一页</a>
                </c:if>
                
                <span class="total-count">共 ${totalCount} 条记录</span>
            </div>
        </div>
    </div>
    
    <footer>
        <p>河北省科学技术厅 © 2023</p>
    </footer>
</div>
posted @ 2025-03-29 20:13  申shen  阅读(24)  评论(0)    收藏  举报