20155320 十六周课堂实践

20155320 十六周课堂实践

关于数据库的相关课堂实践1，由于课上我没有成功连上数据库。课下全部重新安装了一遍数据库，终于连接上了数据库。

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

起初用的是原始的messagedao代码，但是message都是红的，后来导入message的代码，修改增加人口等要素及函数,完善后的MessageDAO如下

import java.sql.*;

import java.util.*;



public class MessageDAO {

    private String url;

    private String user;

    private String passwd;



    public MessageDAO(String url, String user, String passwd) {

        this.url = url;

        this.user = user;

        this.passwd = passwd;

    }



    public void add(Message message) {

        try(Connection conn = DriverManager.getConnection(url, user, passwd);

            Statement statement = conn.createStatement()) {

            String sql = String.format(

                    "INSERT INTO city( Name, Population) VALUES ('%s', '%d')",

                    message.getName(),message.getPopulation());

            statement.executeUpdate(sql);

        } catch(SQLException ex) {

            throw new RuntimeException(ex);

        }

    }



    public List<Message> get() {

        List<Message> messages = new ArrayList<>();

        try(Connection conn = DriverManager.getConnection(url, user, passwd);

            Statement statement = conn.createStatement()) {

            ResultSet result =

                    statement.executeQuery(

                            "SELECT DISTINCT city.Name,city.Population\n" +

                                    "FROM  city INNER JOIN\n" +

                                    "        country ON country.Capital = city.ID\n" +

                                    "WHERE  country.Continent = \"Asia\"");

            while (result.next()) {

                Message message = toCity(result);

                messages.add(message);

            }

        } catch(SQLException ex) {

            throw new RuntimeException(ex);

        }

        return messages;

    }



    private Message toCity(ResultSet result) throws SQLException {

        Message message = new Message();

        message.setName(result.getString(1));

        message.setPopulation(result.getLong(2));

        return message;

    }

}

运行结果为：