使用Ecplise从数据库检索列表时出现问题

Question

我目前正在做一个优惠券项目。在处理我的facade类时，我遇到一个问题，通过使用外键从表中输入客户的ID来获取客户已购买的优惠券列表。

我使用的方法是：

@Override
public List<Coupon> getPurchasedCoupons(int customerID) throws SQLException {
    {
        Connection connection = pool.getConnection();

        ArrayList<Coupon> customerCoupon = new ArrayList<>();
        ArrayList<Long> customerCouponID = new ArrayList<>();
        Statement stmt = null;

        long coupID = 0;
        stmt = connection.createStatement();

        ResultSet resultSet = stmt.executeQuery("SELECT * FROM `couponsystem`.`customers_vs_coupons` WHERE (`CUSTOMER_ID` = '?')");

        while ((resultSet != null) && (resultSet.next())) {
            coupID = resultSet.getLong("COUPON_ID");

            customerCouponID.add(coupID);
        }

        Iterator<Long> myIterator = customerCouponID.iterator();

        while (myIterator.hasNext()) {
            Long couponID = myIterator.next();

            resultSet = stmt.executeQuery(
                    "SELECT * FROM `couponsystem`.`customers_vs_coupons` where COUPON_ID = " + couponID);

            while (resultSet.next()) {
                Coupon coupon = new Coupon(resultSet.getInt(1), resultSet.getInt(2),
                        Category.categoryFor(resultSet.getInt(3)), resultSet.getString(4), resultSet.getString(5),
                        resultSet.getDate(6), resultSet.getDate(7), resultSet.getInt(8), resultSet.getDouble(9),
                        resultSet.getString(10));
                customerCoupon.add(coupon);
            }
        }

        ConnectionPool.getInstance().restoreConnection(connection);

        return customerCoupon;
    }

}

优惠券类别：

import java.util.Date;

public class Coupon {

    private int id;
    private int companyID;
    private Category category;
    private String title;
    private String description;
    private Date startDate;
    private Date endDate;
    private int amount;
    private double price;
    private String image;

    public Coupon(int companyID, Category category, String title, String description, Date startDate, Date endDate,
            int amount, double price, String image) {
        this.companyID = companyID;
        this.category = category;
        this.title = title;
        this.description = description;
        this.startDate = startDate;
        this.endDate = endDate;
        this.amount = amount;
        this.price = price;
        this.image = image;
    }

    public Coupon(int id, int companyID, Category category, String title, String description, Date startDate,
            Date endDate, int amount, double price, String image) {
        this.id = id;
        this.companyID = companyID;
        this.category = category;
        this.title = title;
        this.description = description;
        this.startDate = startDate;
        this.endDate = endDate;
        this.amount = amount;
        this.price = price;
        this.image = image;
    }

    public Coupon() {
        super();
    }

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

    public void setCompanyID(int companyID) {
        this.companyID = companyID;
    }

    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Date getStartDate() {
        return startDate;
    }

    public void setStartDate(Date startDate) {
        this.startDate = startDate;
    }

    public Date getEndDate() {
        return endDate;
    }

    public void setEndDate(Date endDate) {
        this.endDate = endDate;
    }

    public int getAmount() {
        return amount;
    }

    public void setAmount(int amount) {
        this.amount = amount;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public int getId() {
        return id;
    }

    public int getCompanyID() {
        return companyID;
    }

    @Override
    public String toString() {
        return "Coupon: ID = " + id + ", Company ID = " + companyID + ", Category = " + category + ", Title = " + title
                + ", Description = " + description + ", Start Date = " + startDate + ", End Date = " + endDate
                + ", Amount = " + amount + ", Price = " + price + ", IMAGE = " + image;
    }

}

调用方法：

public List<Coupon> getCustomerCoupons() throws SQLException {

    return coup.getPurchasedCoupons(customerID);

}

我的SQL表：

Coupon_vs_Customer表仅包含2行。它们都是其他表的外键。CustomerID连接到“Customers”表，而coupon_ID连接到表“Coupons”

CustomerID   Coupon_ID
1             1
1             2
1             3

正如您在上面看到的，ID为1的客户拥有3张优惠券，我试图在我的eclipse项目的列表中阅读它们。

我没有得到任何异常，但是我得到一个空的ArrayList。我似乎无法解决这个问题，因为我对JDBC还很陌生。

Answer 1

您的第一个查询尝试查找id=“？”的客户。也许这是试图创建一个预先准备好的语句，但您还需要几个步骤

String query = "SELECT COUPON_ID FROM couponsystem.customers_vs_coupons WHERE CUSTOMER_ID = ?";
PreparedStatement statement = conn.prepareStatement(query);
statement.setInt(1, customerID);

ResultSet resultSet = statement.executeQuery();