java - 无法用Java从数据库中读取表中的行

Question

我正在编写代码来实现库存模型。作为其中的一部分，我必须从数据库获取项目并设置项目的属性并将该项目对象添加到列表中并在列表上执行一些功能。数据库中有三个表，每个表包含 3 行。但问题是只有最后一个表中的最后一行被插入了九次。请帮我找出代码中的问题所在。readAllItemsFromDb 方法必须完成上述任务并返回 Item 对象的列表。 Item 类包含 id、description、weight、price、makingDate、useBeforeMonths 等字段及其 set 和 get 方法。

我的方法代码如下--

public List<Item> readAllItemsFromDb() {
    // TODO Auto-generated method stub
    List<Item> list=new ArrayList<Item>();
    Item item=new Item();
    Milk milk=new Milk();
    Cheese cheese=new Cheese();
    Wheat wheat=new Wheat();

    String cheese_type=new String();
    String milk_type=new String();
    String wheat_type=new String();
    String ingred_cheese=new String();

    String cheese_query="select id,description,weight,price,mfg_date,UseBeforeInMonths from cheese_tbl;";
    String milk_query="select id,description,weight,price,mfg_date,UseBeforeInMonths from milk_tbl;";
    String wheat_query="select id,description,weight,price,mfg_date,UseBeforeInMonths from wheat_tbl;";

    try {
        con=dcm.getConnection();//Theres' no problem with Connection 
        st=con.createStatement();
        rs=st.executeQuery(cheese_query);
        while(rs.next()){
            item.setId(rs.getInt(1));
            item.setDescription(rs.getString(2));
            item.setWeight(rs.getFloat(3));
            item.setPrice(rs.getFloat(4));
            item.setManufacturingDate(rs.getDate(5));
            item.setUseBeforeMonths(rs.getInt(6));

            list.add(item);
        }

        rs=st.executeQuery(milk_query);
        while(rs.next()){
            item.setId(rs.getInt(1));
            item.setDescription(rs.getString(2));
            item.setWeight(rs.getFloat(3));
            item.setPrice(rs.getFloat(4));
            item.setManufacturingDate(rs.getDate(5));
            item.setUseBeforeMonths(rs.getInt(6));
            list.add(item);

        }

        rs=st.executeQuery(wheat_query);
        while(rs.next()){
            item.setId(rs.getInt(1));
            item.setDescription(rs.getString(2));
            item.setWeight(rs.getFloat(3));
            item.setPrice(rs.getFloat(4));
            item.setManufacturingDate(rs.getDate(5));
            item.setUseBeforeMonths(rs.getInt(6));

            list.add(item); 
        }


    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return list;
}

Answer 1

您必须在 while 循环中每次迭代开始时创建一个新的 Item 对象。

   while(rs.next()){
        item = new Item();
        item.setId(rs.getInt(1));
        item.setDescription(rs.getString(2));
        item.setWeight(rs.getFloat(3));
        item.setPrice(rs.getFloat(4));
        item.setManufacturingDate(rs.getDate(5));
        item.setUseBeforeMonths(rs.getInt(6));

        list.add(item);
    }

    rs=st.executeQuery(milk_query);
    while(rs.next()){
        item = new Item();
        item.setId(rs.getInt(1));
        item.setDescription(rs.getString(2));
        item.setWeight(rs.getFloat(3));
        item.setPrice(rs.getFloat(4));
        item.setManufacturingDate(rs.getDate(5));
        item.setUseBeforeMonths(rs.getInt(6));

        list.add(item);
    }

    rs=st.executeQuery(wheat_query);
    while(rs.next()){
        item = new Item();
        item.setId(rs.getInt(1));
        item.setDescription(rs.getString(2));
        item.setWeight(rs.getFloat(3));
        item.setPrice(rs.getFloat(4));
        item.setManufacturingDate(rs.getDate(5));
        item.setUseBeforeMonths(rs.getInt(6));

        list.add(item); 
    }