package org.elsys.ip.rest.repository;

import org.elsys.ip.rest.model.Country;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CountryRepositoryJDBC {
    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://localhost/country"; //change

    //Database credentials
    private static final String USER = "root";
    private static final String PASS = "root";

    public List<Country> getCountryList () {
        List<Country> countries = new ArrayList<>();
        Connection conn = null;

        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            Statement stmt = null;
            ResultSet rs = null;

            try {
                stmt = conn.createStatement();
                String sql = "SELECT id, name FROM country";
                rs = stmt.executeQuery(sql);

                while (rs.next()) {
                    Country country = new Country();
                    country.setId(rs.getInt("id"));
                    country.setName(rs.getString("name"));
                    countries.add(country);
                }
            } finally {
                if (stmt != null && !stmt.isClosed()) {
                    stmt.close();
                }

                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

        return countries;
    }
}