Java 循环显示 SQL 国家/地区表中的多种语言

Question

我正在处理一项 Java 作业，该作业需要从 COUNTRY_LANGS_SQL 表中提取语言，然后使用循环来显示每个国家/地区的国家/地区、人口、中位年龄和语言。我能够显示 4 个国家、人口、中位年龄，但对于我的一生，我无法弄清楚我需要声明什么语句才能让它拉动和循环语言。我尝试了多种变体没有成功，所以我希望有人能看看并指出我做错了什么。任何帮助将不胜感激。

更新3:所以我现在非常接近让它成功执行和显示语言列，它只是不显示该国家/地区的语言(见下文)

Name: Canada  Population: 34568211  Median Age: 41.5  ID      :   1  Language:   []
Name: Germany  Population: 81147265  Median Age: 45.7  ID      :   2  Language:   []
Name: South Africa  Population: 49601098  Median Age: 25.5  ID      :   3  Language:   []
Name: Japan  Population: 127253075  Median Age: 45.8  ID      :   4  Language:   []

我现在在 readLanguages 方法中也只有一个错误”类型 List 的 getId() 方法未定义”

/**
 * @return list of countries read from the country database
 */
public List<Country> getCountries() {
    return countries;
}

private void readLanguages() {
    try (
        Connection connection = getConnection();
        PreparedStatement stmt = connection.prepareStatement(GET_COUNTRY_LANGS_SQL)
    ) {

        for (Country country : countries); {
            stmt.setInt(0, countries.getId());
            ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            int i = 0;
            countries.get(i).addLanguage(rs.getString("language")); 
            rs.close();
        }
    }}
    catch (SQLException e) {
        System.err.println("ERROR: " + e.getMessage());
        e.printStackTrace();
    }

它似乎没有从 Country 类中提取 Id，或者它没有正确循环。有什么想法吗？

1)CountriesDB.java

        package edu.pcc.cis233j.countries;

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

/**
 * Read data from the countries database
 * 
 * @author 
 */
public class CountriesDB {
    private static final String DB_NAME = "Countries";
    private static final String DB_URL = "jdbc:jtds:sqlserver://cisdbss.pcc.edu/" + DB_NAME;
    private static final String USERNAME = "";
    private static final String PASSWORD = "";
    private static final String GET_COUNTRIES_SQL = "SELECT * FROM COUNTRY";
    private static final String GET_COUNTRY_LANGS_SQL = "SELECT * FROM COUNTRY_LANGUAGE WHERE CountryId = ?";

    private List<Country> countries;

    /**
     * Create a CountriesDB object
     * Read from the Countries database and populate the countries list
     */
    public CountriesDB() {
        countries = readCountries();
    }

    /**
     * Create and return a connection to the database
     * @return connection to the countries database
     */
    private Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);
        return connection;
    }

    /**
     * Read country info from the Country table.
     * If an error occurs, a stack trace is printed to standard error and an empty list is returned.
     * @return the list of countries read
     */
    private List<Country> readCountries() {
        List<Country> countries = new ArrayList<>();
        try (
                Connection connection = getConnection();
                PreparedStatement stmt = connection.prepareStatement(GET_COUNTRIES_SQL);
                ResultSet rs = stmt.executeQuery()
                ) {
            while (rs.next()) {
                countries.add(new Country(rs.getInt("Id"),
                        rs.getString("Name"),
                        rs.getLong("Population"),
                        rs.getDouble("MedianAge")));
            }
        }
        catch (SQLException e) {
            System.err.println("ERROR: " + e.getMessage());
            e.printStackTrace();
        }
        return countries;
    }

    /**
     * @return list of countries read from the country database
     */
    public List<Country> getCountries() {
        return countries;
    }

    private void readLanguages() {
        try (
            Connection connection = getConnection();
            PreparedStatement stmt = connection.prepareStatement(GET_COUNTRY_LANGS_SQL)
        ) {

            for (Country country : countries); {
                stmt.setInt(0, countries.getId());
                ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                int i = 0;
                countries.get(i).addLanguage(rs.getString("language")); 
                rs.close();
            }
        }}
        catch (SQLException e) {
            System.err.println("ERROR: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

2)Country.java

       package edu.pcc.cis233j.countries;

import java.util.ArrayList;
import java.util.List;

/**
 * A country in the world
 * @author Your Name & Cara Tang
 */
public class Country {
    private int id;
    private String name;
    private long population;
    private double medianAge;
    private List<String> language = new ArrayList<>();


    /**
     * Create a Country object with the given properties
     */
    public Country(int id, String name, long population, double medianAge) {
        this.id = id;
        this.name = name;
        this.population = population;
        this.medianAge = medianAge;
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public long getPopulation() {
        return population;
    }

    public double getMedianAge() {
        return medianAge;
    }

    public List<String> getLanguage() {
        return language;
        }

    public void addLanguage(String string) {
         language.add(string);
        }

}

3) CountryMain.java

    package edu.pcc.cis233j.countries;

import java.util.List;

/**
 * Read from the Country database and print data on the countries
 * @author TJ
 */
public class CountryMain {
    public static void main(String[] args) {
        CountriesDB cdb = new CountriesDB();
        List<Country> countries = cdb.getCountries();

        for (Country country : countries) {
              System.out.println("Name: " + country.getName()
                                   + "  Population: " + country.getPopulation()
                                   + "  Median Age: " + country.getMedianAge()
                                   + "  ID      :   " + country.getId()
                                   + "  Language:   " + country.getLanguage()
                      );
        }
    }
}

Answer 1

首先，行:

countries.add(new Country(rs.getString("Language"),
                    rs.getString("Language")));

在声明或填充之前引用rs。此外，他们似乎试图用两个字符串(都相同)建立一个国家。

第二，

countries.add(new Language ());

这很疯狂，因为国家/地区中有 Country 对象，因此您不能只添加一个 Language 对象，然后再添加一个新对象(不是来自数据库)。

这几乎就好像你没有自己编写代码，因此不理解它......

我认为你需要的是更接近这个:

private void readLanguages() {
    try (
        Connection connection = getConnection();
        PreparedStatement stmt = connection.prepareStatement(GET_COUNTRY_LANGS_SQL)
    ) {
        ResultSet rs = null;
        for (Country country : countries); {
            stmt.setInt(1, country.getId());
            rs = stmt.executeQuery();
            while (rs.next()) {
              country.setLanguage(rs.getString("Language")); //assumes 1 per country - others are discarded
            }
            rs.close();
        }
    }
}

不是完美的 java - 但应该让你知道哪里出了问题。

其他说明:

• 执行此操作的简单方法(假设每个国家/地区一种语言)是在 SELECT 中连接表。
• 完成后您还应该关闭数据库连接。
• 我不确定您的 try (stuff) {statements} 语法是否正确。
• 如果每个国家/地区有多种语言，您需要在数据结构/类中提供支持。
• 我假设您的 Country 类具有字段的 getter 和 setter。

注意:问题已更新，但我认为这仍然是最相关的。