20170607课堂实践：数据库——20155312

任务二

查询world数据库，获得人口超过500万的所有城市的列表。

• 遇到的问题及解决过程

  • 问题1：忘记了怎么用idea自动生成getter & setter
  • 解决：选择Code->generate->Getter and Setter。

• 运行截图如下
  

• 参考Message.java,City.java的代码如下

import java.io.Serializable;

public class City implements Serializable {
    private Long id;
    private String Name;
    private String CountryCode;
    private String District;
    private Long Population;

    public City() {}

    public City(String Name, String CountryCode , String District,Long Population) {
        this.Name = Name;
        this.CountryCode = CountryCode;
        this.District = District;
        this.Population = Population;
    }

    public Long getPopulation() {
        return Population;
    }

    public void setPopulation(Long population) {
        Population = population;
    }

    public String getDistrict() {

        return District;
    }

    public void setDistrict(String district) {
        District = district;
    }

    public String getName() {

        return Name;
    }

    public void setName(String name) {
        Name = name;
    }

    public String getCountryCode() {

        return CountryCode;
    }

    public void setCountryCode(String countryCode) {
        CountryCode = countryCode;
    }

    public Long getId() {

        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}

• 在MessageDAO中修改了add方法，代码如下：

String sql = String.format(
                    "INSERT INTO city(Name, CountryCode, District,Population) VALUES ('%s', '%s', '%s','%d')",
                    message.getName(), message.getCountryCode(), message.getDistrict(),message.getPopulation());
            statement.executeUpdate(sql);

参考toMessage，新添了toCity方法，代码如下：

private City toCity(ResultSet result) throws SQLException {
        City city = new City();
        city.setId(result.getLong(1));
        city.setName(result.getString(2));
        city.setCountryCode(result.getString(3));
        city.setDistrict(result.getString(4));
        city.setPopulation(result.getLong(5));
        return city;
}

最后修改了get方法，在方法中确定查找范围，代码如下：

public List<City> get() {
        List<City> citys = new ArrayList<>();
        try(Connection conn = DriverManager.getConnection(url, user, passwd);
            Statement statement = conn.createStatement()) {
            ResultSet result =
                    statement.executeQuery("SELECT * FROM `city` WHERE Population > 5000000");
            while (result.next()) {
                City city = toCity(result);
                citys.add(city);
            }
        } catch(SQLException ex) {
            throw new RuntimeException(ex);
        }
        return citys;
    }

• 测试代码MessageDAODemo.java代码如下：

/**
 * Created by dell1 on 2017/4/25.
 */

import static java.lang.System.out;

import java.util.Scanner;

public class MessageDAODemo {
    public static void main(String[] args) throws Exception {
        MessageDAO dao = new MessageDAO(
                "jdbc:mysql://localhost:3306/world?" +
                        "useUnicode=true&characterEncoding=UTF8",
                "root", "");
        Scanner console = new Scanner(System.in, "Big5");
        while (true) {
            out.print("(1) 显示城市信息 (2) 新增城市信息：");
            switch (Integer.parseInt(console.nextLine())) {
                case 1:
                    dao.get().forEach(city -> {
                        out.printf("%d\t%s\t%s\t%s\t%d%n",
                                city.getId(),
                                city.getName(),
                                city.getCountryCode(),
                                city.getDistrict(),
                                city.getPopulation());
                    });
                    break;
                case 2:
                    out.print("Name：");
                    String Name = console.nextLine();
                    out.print("CountryCode：");
                    String CountryCode = console.nextLine();
                    out.print("District：");
                    String District = console.nextLine();
                    out.print("Population：");
                    Long Population = Long.valueOf(console.nextLine());
                    dao.add(new City(Name, CountryCode, District,Population));
            }
        }
    }

}