20170607 JDBC课堂实践 任务四

• 题目

• 码云链接

• SQL语句

SELECT Code, Name, LifeExpectancy FROM `country` WHERE LifeExpectancy=(SELECT MAX(LifeExpectancy) FROM country)

• 代码

1. 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 List<Message> get() {

        List<Message> messages = null;

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

            Statement statement = conn.createStatement()) {

            ResultSet result = statement.executeQuery("SELECT Code, Name, LifeExpectancy FROM `country` WHERE LifeExpectancy=(SELECT MAX(LifeExpectancy) FROM country)");

            messages = new ArrayList<>();

            while (result.next()) {

                Message message = new Message();

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

                message.setNationName(result.getString(2));

                message.setLifeExpectancy(result.getFloat(3));

                messages.add(message);

            }

        } catch(SQLException ex) {

            throw new RuntimeException(ex);

        }

        return messages;

    }

}

2.MessageDAODemo

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", "");

        //while (true) {

            for (Message message : dao.get()) {

                System.out.printf("%s\t%s\t%.1f\t%n",

                        message.getCode(),

                        message.getNationName(),

                        message.getLifeExpectancy());

        //    }

        }

    }

}

3.Message

import java.io.Serializable;

public class Message implements Serializable {

    private Long id;

    private String cityName;

    private String code;

    private String nationName;

    private Long population;

    public Float getLifeExpectancy() {

        return LifeExpectancy;

    }

    public void setLifeExpectancy(Float lifeExpectancy) {

        LifeExpectancy = lifeExpectancy;

    }

    private Float LifeExpectancy;

    public String getCode() {

        return code;

    }

    public void setCode(String code) {

        this.code = code;

    }

    public String getNationName() {

        return nationName;

    }

    public void setNationName(String nationName) {

        this.nationName = nationName;

    }

    public Long getPopulation() {

        return population;

    }

    public void setPopulation(Long population) {

        this.population = population;

    }

    public Message() {

    }

    public Message(String cityName) {

        this.cityName = cityName;

    }

    public Long getId() {

        return id;

    }

    public void setId(Long id) {

        this.id = id;

    }

    public String getCityName() {

        return cityName;

    }

    public void setCityName(String cityName) {

        this.cityName = cityName;

    }

}

• 运行截图

• 数据库截图