java - Servlet Oracle 的耗尽结果集问题

Question

每次运行此代码时，都会出现排气结果集错误。我不确定我做错了什么，但我尝试删除 .next();一个或所有结果集的代码，然后给出的错误是下一个结果集未被调用。

我不确定我做错了什么。只是好奇人们可能认为问题是什么？我之前在 servlet 代码中做过类似的事情，但只使用了 1 个语句，然后使用了 1 个准备好的语句。这次我使用了 2 个语句和 1 个准备好的语句。

String opt1 = req.getParameter("RecName"); //Retrieves info from HTML form
String ingr1 = req.getParameter("Ing1"); //Retrieves info from HTML form

stmt = con.createStatement();
stmt1 = con.createStatement();

ResultSet rs11 = stmt.executeQuery("SELECT recipe_ID FROM GM_Recipes WHERE rec_name='" + op1 + "'"); //choose recipe_ID from sql table
rs11.next();

ResultSet rs12 = stmt.executeQuery("SELECT ingredient_ID FROM GM_IngredientDB WHERE ing_name='" + ingr1 + "'"); //choose ingredient_ID from sql table
rs12.next();

int olo = ((Number) rs11.getObject(1).intValue(); //convert resultset value to int
int olo1 = ((Number) rs11.getObject(1).intValue(); //convert resultset value to int

PreparedStatement pstmt1 = con.prepareStatement("INSERT INTO GM_RecLnk(recipe_ID,ingredient_ID) VALUES (?,?)");
pstmt1.clearParameters();

pstmt1.setInt(1,olo);
pstmt1.setInt(2,olo1);

ResultSet rs1 = pstmt1.executeQuery();
rs1.next();

Answer 1

关于代码的一些想法(在评论中)

stmt = con.createStatement();
stmt1 = con.createStatement();

ResultSet rs11 = stmt.executeQuery("SELECT recipe_ID FROM GM_Recipes WHERE rec_name='" + op1 + "'"); //choose recipe_ID from sql table

//Check if you HAVE a line here!
if(!rs11.next()) {
  System.out.println("No Recipe Found");
}

//Use stmt1 - that's why you created it?!    
ResultSet rs12 = stmt1.executeQuery("SELECT ingredient_ID FROM GM_IngredientDB WHERE ing_name='" + ingr1 + "'"); //choose ingredient_ID from sql table
if(!rs12.next()) {
  System.out.println("No Ingredient Found");
}

int olo = ((Number) rs11.getObject(1).intValue(); //convert resultset value to int
//Read Ingredient from rs12 -> that's where you selected it into
int olo1 = ((Number) rs12.getObject(1).intValue(); //convert resultset value to int

虽然这可能会为您指明解决当前问题的正确方向，但您应该考虑学习干净的代码。

考虑一下这段代码，它使用了 try-with-resource，并使用准备好的语句重构了一些方法。

   //Replace exiting code
    String opt1 = req.getParameter("RecName"); //Retrieves info from HTML form
    String ingr1 = req.getParameter("Ing1"); //Retrieves info from HTML form

    int recipieId = getRecipeId(con, opt1);
    int ingredientId = getIngredientId(con, ingr1);

    if(recipeId > 0 && ingredientId > 0) {
        //Process result
        insertRecLnk(con, recipeId, ingredientId);
    } else {
        System.out.println("No INSERT");            
    }


//Helper functions

protected int getRecipeId(Connection con, String rec) {
    try(PreparedStatement st = con.prepareStatement("SELECT recipe_ID FROM GM_Recipes WHERE rec_name=?")) {
        st.setString(1, rec);
        try(ResultSet rs11 = st.executeQuery()) {
          //choose recipe_ID from sql table                     
            if(rs11.next()) {
              return rs11.getInt(1);  
            }
        }
    } catch(SQLException e) {
        e.printStackTrace();
    }
    System.out.println("No Recipe Found");
    return -1;
}

protected int getIngredientId(Connection con, String ing) {
    try(PreparedStatement st = con.prepareStatement("SELECT ingredient_ID FROM GM_IngredientDB WHERE ing_name=?")) {
        st.setString(1, ing);
        try(ResultSet rs11 = st.executeQuery()) {
          //choose recipe_ID from sql table                     
            if(rs11.next()) {
              return rs11.getInt(1);  
            }
        }
    } catch(SQLException e) {
        e.printStackTrace();
    }
    System.out.println("No Ingredient Found");
    return -1;
}

protected void insertRecLnk(Connection con, int rId, int iId) {
    try(PreparedStatement ps = con.prepareStatement("INSERT INTO GM_RecLnk(recipe_ID,ingredient_ID) VALUES (?,?)")) {
        ps.setInt(1, rId);
        ps.setInt(2, iId);
        ps.executeUpdate();
    } catch(SQLException e) {
        e.printStackTrace();
    }
}