JdbcMoonMissionRepository.java

package com.example;

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

public class JdbcMoonMissionRepository implements MoonMissionRepository {

    private final DataSource dataSource;

    public JdbcMoonMissionRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public List<String> findAllSpacecraftNames() throws SQLException {
        List<String> names = new ArrayList<>();
        String sql = "SELECT spacecraft FROM moon_mission";
        try (Connection connection = dataSource.getConnection();
             Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                names.add(rs.getString("spacecraft"));
            }
        }
        return names;
    }


    @Override
    public ResultSet findMissionById(long missionId, Connection connection) throws SQLException {
        String sql = "SELECT * FROM moon_mission WHERE mission_id = ?";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setLong(1, missionId);
        return stmt.executeQuery();
    }

    @Override
    public int countMissionsByYear(int year) throws SQLException {
        String sql = "SELECT COUNT(*) FROM moon_mission WHERE YEAR(launch_date) = ?";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, year);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
                return 0;
            }
        }
    }
}