数据库MySQL

要求

1.下载附件中的world.sql.zip, 参考Intellj IDEA 简易教程：数据库，导入world.sql,提交导入成功截图
2.编写程序，查询世界上超过“你学号前边七位并把最后一位加到最高位，最高位为0时置1”（比如学号20165201，超过3016520；学号20165208，超过1016520）的所有城市列表，提交运行结果截图
3.编写程序，查询世界上的所有中东国家的总人口
4.编写程序，查询世界上的平均寿命最长和最短的国

实现步骤（根据老师实验博客顺序）

“测试数据库是否打开代码”的测试

这一步是为了检验数据库是否连接到了IDEA中，也就是说能否用代码对数据库进行操作。
~~个人感觉这一步放在这里有点问题，放在XAMPP配置好并且将数据库导入到IDEA中以后，才会有意义。~~

代码如下：

import static java.lang.System.out;
import java.sql.*;

public class ConnectionDemo {
    public static void main(String[] args)
                              throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String jdbcUrl = "jdbc:mysql://localhost:3306/demo";
        String user = "root";
        String passwd = "";
        try(Connection conn = 
                DriverManager.getConnection(jdbcUrl, user, passwd)) {
            out.printf("已%s数据库连接%n", 
                    conn.isClosed() ? "关闭" : "打开");
        } 
    }
}

XAMPP的配备

下载XAMPP后打开文件夹中的xampp-control.exe(最好用管理员权限)
点最上面两个项目对应的start（本次作业中最难的一步）

网页版数据库更改（自己瞎起的名字）

~~划重点~~
这一步如果未更改Apache占用端口号直接按下图步骤即可，如果更改则需要更改网站地址（问题解答处会详细说明）*

接下来你就会和老师的步骤对上了~
在此直接引用老师的操作步骤~

配备：

1.建数据库的SQL语句： CREATE SCHEMA demo;

2.留言板数据库表的SQL语句是：

use demo;
CREATE TABLE t_message (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(20) NOT NULL,
email CHAR(40),
msg TEXT NOT NULL
) CHARSET=UTF8;

以上两组代码输入以后，都要点击执行

插件安装

插件是书上提供的，这里我把书上版本的文件下载链接放一下[https://pan.baidu.com/s/1u9CaadJ9wh3DvyywAZlvTQ]，提取码：r5dj
如下图加入即可

测试阶段

测试留言（前面输入的SQL语句这里派上用场）

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 t_message(name, email, msg) VALUES ('%s', '%s', '%s')",  
                  message.getName(), message.getEmail(), message.getMsg());
            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 * FROM t_message");
            while (result.next()) {
                Message message = toMessage(result);
                messages.add(message);
            }
        } catch(SQLException ex) {
            throw new RuntimeException(ex);
        }
        return messages;
    }

    private Message toMessage(ResultSet result) throws SQLException {
        Message message = new Message();
        message.setId(result.getLong(1));
        message.setName(result.getString(2));
        message.setEmail(result.getString(3));
        message.setMsg(result.getString(4));
        return message;
    }
}

import java.io.Serializable;

public class Message implements Serializable {
    private Long id;
    private String name;
    private String email;
    private String msg;

    public Message() {}
    
    public Message(String name, String email, String msg) {
        this.name = name;
        this.email = email;
        this.msg = msg;
    }
    
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }

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

    public String getMsg() {
        return msg;
    }
    public void setMsg(String msg) {
        this.msg = msg;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

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/demo?" + 
                "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(message -> {
                        out.printf("%d\t%s\t%s\t%s%n", 
                                message.getId(), 
                                message.getName(), 
                                message.getEmail(), 
                                message.getMsg());
                    });
                    break;
                case 2:
                    out.print("姓名：");
                    String name = console.nextLine();
                    out.print("邮件：");
                    String email = console.nextLine();
                    out.print("留言：");
                    String msg = console.nextLine();
                    dao.add(new Message(name, email, msg));
            }
        }
    }
    
}

world数据库的插入

如图步骤导入后点执行

至此，配备阶段结束

There are 几个程序的代码

查询人口超过7017510的所有城市列表

代码比较容易（至少看书来敲是这样的），关键是找到URL的数据位置String url = "jdbc:mysql://localhost:3306/world";

/**
 * MysqlTest1
 *
 * @author wxh20175105
 * @date 2019/5/4
 */

import java.sql.*;

public class MysqlTest1 {
    public static void main(String[] args) throws SQLException {
        Connection con;
        Statement sql;
        ResultSet rs;
        String url = "jdbc:mysql://localhost:3306/world";
        String user = "root";
        String passwd = "";
        con = DriverManager.getConnection(url, user,passwd);
        if (con == null) {
            return;
        }
        String sqlStr = "select*from city where population>7017510";
        try {
            sql = con.createStatement();
            rs = sql.executeQuery(sqlStr);
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String countryCode = rs.getString(3);
                String district = rs.getString(4);
                int population = rs.getInt(5);
                System.out.printf("%d\t", id);
                System.out.printf("%s\t", name);
                System.out.printf("%s\t", countryCode);
                System.out.printf("%s\t", district);
                System.out.printf("%d\n", population);
            }
            con.close();
        } catch (SQLException e) {
            System.out.println("Error:" + e);
        }


    }
}

查询中东国家的总人口

/**
 * MysqlTest2
 *
 * @author Wxh20175105
 * @date 2019/5/4
 */

import java.sql.*;

public class MysqlTest2 {
    public static void main(String[] args) throws SQLException {
        Connection con;
        Statement sql;
        ResultSet rs;
        String url = "jdbc:mysql://localhost:3306/world";
        String user = "root";
        String passwd = "";
        con = DriverManager.getConnection(url, user,passwd);
        if (con == null) {
            return;
        }
        String sqlStr = "select Name,Population from country where Region = 'Middle East'";
        try {
            sql = con.createStatement();
            rs = sql.executeQuery(sqlStr);
            int total = 0;
            while (rs.next()) {
                String name = rs.getString(1);
                int population = rs.getInt(2);
                System.out.printf("%s的人口为%d\n", name, population);
                total += population;
            }
            System.out.println("中东国家的总人口为：" + total);
        } catch (SQLException e) {
            System.out.println("Error:" + e);
        }

    }
}

查询世界上的平均寿命最长和最短的国家

import java.sql.*;

/**
 * MysqlTest3
 *
 * @author wxh20175105
 * @date 2019/5/4
 */
public class MysqlTest3 {
    public static void main(String[] args) throws SQLException {
        Connection con;
        Statement sql;
        ResultSet rs;
        String url = "jdbc:mysql://localhost:3306/world";
        String user = "root";
        String passwd = "";
        con = DriverManager.getConnection(url, user,passwd);
        if (con == null) {
            return;
        }
        String sqlStr = "select Name,LifeExpectancy from country order by LifeExpectancy";
        try {
            sql = con.createStatement();
            rs = sql.executeQuery(sqlStr);
            while (rs.next()) {
                float life = rs.getInt(2);
                String name = rs.getString(1);
                rs.first();
                while (life == 0) {
                    rs.next();
                    life = rs.getInt(2);
                }
                name = rs.getString(1);
                System.out.println("世界上平均寿命最短的国家为：" + name);
                rs.last();
                name = rs.getString(1);
                System.out.println("世界上平均寿命最长的国家为：" + name);

            }
        } catch (SQLException e) {
            System.out.println("Error:" + e);
        }
    }
}