Java + SQL 實現統計「政治類新聞」與「娛樂類新聞」人數並封裝為 Map 完整代碼

需求：查詢 User 表中 newsh 字段為「政治類新聞」或「娛樂類新聞」的用戶總數，結果封裝為 Map 返回。本文提供標準 JDBC、Spring JdbcTemplate、MyBatis、JPA/Hibernate 四種主流數據訪問技術的完整實現，並優化 SQL 寫法與異常處理。

數據表結構假設

CREATE TABLE `User` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50),
    `newsh` VARCHAR(50)  -- 新聞偏好類別：'政治類新聞'、'娛樂類新聞'、'體育新聞' 等
);

優化 SQL：單條聚合查詢替代多次查詢

-- 方案 A：條件聚合，單行多列返回
SELECT 
    SUM(CASE WHEN newsh = '政治類新聞' THEN 1 ELSE 0 END) AS politics_count,
    SUM(CASE WHEN newsh = '娛樂類新聞' THEN 1 ELSE 0 END) AS entertainment_count
FROM `User`
WHERE newsh IN ('政治類新聞', '娛樂類新聞');

-- 方案 B：分組聚合，多行返回 (更通用、易擴展)
SELECT newsh, COUNT(*) AS cnt
FROM `User`
WHERE newsh IN ('政治類新聞', '娛樂類新聞')
GROUP BY newsh;

推薦方案 B：擴展新類別只需修改 IN 列表，代碼無需改動。

實現一：標準 JDBC (最基礎、無框架依賴)

public Map countNewsPreferences(Connection conn) throws SQLException {
    String sql = "SELECT newsh, COUNT(*) AS cnt FROM `User` " +
                 "WHERE newsh IN ('政治類新聞', '娛樂類新聞') GROUP BY newsh";
    
    Map result = new LinkedHashMap<>();  // 保持插入順序
    result.put("政治類新聞", 0L);
    result.put("娛樂類新聞", 0L);
    
    try (PreparedStatement ps = conn.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            String category = rs.getString("newsh");
            long count = rs.getLong("cnt");
            result.put(category, count);
        }
    }
    return result;
}

實現二：Spring JdbcTemplate (企業級標準)

@Repository
public class UserStatisticsDao {
    @Autowired private JdbcTemplate jdbcTemplate;
    
    public Map countNewsPreferences() {
        String sql = "SELECT newsh, COUNT(*) AS cnt FROM `User` " +
                     "WHERE newsh IN (?, ?) GROUP BY newsh";
        
        // 使用 query 方法自動映射 RowMapper
        List> rows = jdbcTemplate.queryForList(sql, "政治類新聞", "娛樂類新聞");
        
        Map result = new LinkedHashMap<>();
        result.put("政治類新聞", 0L);
        result.put("娛樂類新聞", 0L);
        
        for (Map row : rows) {
            String category = (String) row.get("newsh");
            Long count = ((Number) row.get("cnt")).longValue();
            result.put(category, count);
        }
        return result;
    }
}

實現三：MyBatis (XML Mapper + 接口)

<!-- UserMapper.xml -->
<select id="countNewsPreferences" resultType="java.util.Map">
    SELECT newsh as category, COUNT(*) as cnt
    FROM `User`
    WHERE newsh IN ('政治類新聞', '娛樂類新聞')
    GROUP BY newsh
</select>

// UserMapper.java
@Mapper
public interface UserMapper {
    List> countNewsPreferences();
}

// Service 層轉換
@Service
public class UserStatisticsService {
    @Autowired private UserMapper userMapper;
    
    public Map getNewsPreferenceCounts() {
        List> rows = userMapper.countNewsPreferences();
        Map result = new LinkedHashMap<>();
        result.put("政治類新聞", 0L);
        result.put("娛樂類新聞", 0L);
        for (Map row : rows) {
            String category = (String) row.get("category");
            Long count = ((Number) row.get("cnt")).longValue();
            result.put(category, count);
        }
        return result;
    }
}

實現四：Spring Data JPA / Hibernate

// Repository
@Repository
public interface UserRepository extends JpaRepository {
    @Query("SELECT u.newsh, COUNT(u) FROM User u WHERE u.newsh IN :categories GROUP BY u.newsh")
    List countByNewshIn(@Param("categories") List categories);
}

// Service
@Service
public class UserStatisticsService {
    @Autowired private UserRepository userRepository;
    
    public Map countNewsPreferences() {
        List categories = Arrays.asList("政治類新聞", "娛樂類新聞");
        List rows = userRepository.countByNewshIn(categories);
        
        Map result = new LinkedHashMap<>();
        result.put("政治類新聞", 0L);
        result.put("娛樂類新聞", 0L);
        for (Object[] row : rows) {
            String category = (String) row[0];
            Long count = ((Number) row[1]).longValue();
            result.put(category, count);
        }
        return result;
    }
}

通用工具方法：執行任意聚合 SQL 返回 Map

public static Map executeCountQuery(DataSource ds, String sql, Object... params) 
        throws SQLException {
    try (Connection conn = ds.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
        try (ResultSet rs = ps.executeQuery()) {
            Map map = new LinkedHashMap<>();
            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();
            while (rs.next()) {
                String key = (cols >= 2) ? rs.getString(1) : "total";
                long val = rs.getLong(cols >= 2 ? 2 : 1);
                map.put(key, val);
            }
            return map;
        }
    }
}

// 調用
Map stats = executeCountQuery(dataSource,
    "SELECT newsh, COUNT(*) FROM `User` WHERE newsh IN (?,?) GROUP BY newsh",
    "政治類新聞", "娛樂類新聞");

性能優化建議

總結

統計特定分類計數並封裝為 Map，核心是優化 SQL 使用 GROUP BY 聚合，配合預設 0 值初始化 Map確保類別缺失時返回 0 而非 null。四大主流框架均可在 10 行核心代碼內完成，建議根據項目技術棧選擇：Spring Boot 項目優先 JdbcTemplate 或 MyBatis，微服務/高併發場景加 Redis 緩存或異步預計算。