/*
 * 请告诉我 'France' 和 'UK' 的顾客（customer）的国家（country）和城市（city）的组合方式（不重复）。
 * 查询结果应当包含如下信息：
 *
 * +──────────+───────+
 * | country  | city  |
 * +──────────+───────+
 *
 * 结果应当首先按照 country 排序，再按照 city 排序。
 */

SELECT DISTINCT `customers`.`country`, `customers`.`city`
FROM `customers`
WHERE `customers`.`country` = 'France' OR `customers`.`country` = 'UK'
ORDER BY `customers`.`country`, `customers`.`city`;

SELECT `orderNumber`,(`quantityOrdered`*`priceEach`) AS `subtotal`
FROM `orderdetails`
ORDER BY `subtotal` DESC
/*或者可以用order by `quantityOrdered`*`priceEach`
*/
LIMIT 5;

public TodoItemRepository(ServiceConfiguration configuration) {
    this.configuration = configuration;
}

/**
 * Create an item and save it into database. The `checked` status should default to `false`.
 *
 * @param name The name of the item.
 * @return The `id` of the item created.
 * @throws IllegalArgumentException The name is `null`.
 */
public long create(String name) throws Exception {
    // TODO:
    //   Please implement the method.
    // <-start-

    if (name == null) {
        throw new IllegalArgumentException("Name must be provided.");
    }

    try (
            Connection connection = createConnection();//看看能不能连接上数据库
            final PreparedStatement statement = connection.prepareStatement(
                    "INSERT INTO `todo_items` (`name`) VALUES (?)", Statement.RETURN_GENERATED_KEYS);//创建statement对象 https://juejin.cn/post/6844903584492945422 返回generated keys
    ) {
        statement.setString(1, name);
        statement.execute();//执行语句
        final ResultSet resultSet = statement.getGeneratedKeys();//生成一个result 可以认为是一行 resultset会指向statement的前一行
        resultSet.next();//前进到下一行
        return resultSet.getLong(1);//https://stackoverflow.com/questions/19873190/statement-getgeneratedkeys-method 得到属性为long的值
    }
}
    // --end-->
}

/**
 * Change item checked status to specified state.
 *
 * @param id The item to update.
 * @param checked The new checked status.
 * @return If the item exist returns true, otherwise returns false.
 */
public boolean changeCheckedStatus(long id, boolean checked) throws Exception {
    // TODO:
    //   Please implement the method.
    // <-start-
    try (
            Connection connection = createConnection();
            final PreparedStatement statement = connection.prepareStatement(
                    "UPDATE `todo_items` SET `checked`=? WHERE `id`=?");
    ) {
        statement.setBoolean(1, checked);
        statement.setLong(2, id);
        return statement.executeUpdate() > 0;//https://blog.csdn.net/u012501054/article/details/80323176 execute executUpdate区别
    }
    // --end-->
}

/**
 * Get item by its id.
 *
 * @param id The id of the item.
 * @return The item entity. If the item does not exist, returns empty.
 */
public Optional<TodoItem> findById(long id) throws Exception {
    // TODO:
    //   Please implement the method.
    // <-start-
    try (
            Connection connection = createConnection();
            final PreparedStatement statement = connection.prepareStatement(
                    "SELECT `name`, `checked` FROM `todo_items` WHERE `id`=?");
    ) {
        statement.setLong(1, id);
        final ResultSet resultSet = statement.executeQuery();
        if (resultSet.next()) {
            final String name = resultSet.getString("name");
            final boolean checked = resultSet.getBoolean("checked");
            return Optional.of(new TodoItem(id, name, checked));
        }

        return Optional.empty();
    }
}
    // --end-->
}