使用Ecplise从数据库检索列表时遇到问题的java
我目前正在做一个优惠券项目。在使用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表:
优惠券与客户表仅包含2行。它们都是其他表的外键。 CustomerID连接到“CUSTOMERS”表,而优惠券ID连接到表“优惠券”
CustomerID Coupon_ID
1 1
1 2
1 3
正如您在上面看到的,ID为1的客户拥有3张优惠券,我正试图在eclipse项目的列表中阅读它们
我没有得到任何异常,但是返回了一个空的arraylist。我似乎无法解决这个问题,因为我是JDBC的新手
# 1 楼答案
您的第一个查询尝试查找id为=“?”的客户。也许这是试图创建一个准备好的语句,但是您还需要一些步骤