java - 准备语句: getIngredientByName

Question

我将如何修改PreparedStatement 的这段代码？我已经了解了如何使用数字变量执行此操作，但我似乎在使用字符串变量时遇到了一些问题...

@Path("/ingredients/name")
@GET
@Produces("text/plain")
public String getIngredientByName(@QueryParam("name") String theName) 
throws SQLException, ClassNotFoundException
{
 //Obtaining an ingredient from the database
 String connectStr="jdbc:mysql://localhost:3306/fooddb";
 String username="root";
 String password="csci330pass";
 String driver="com.mysql.jdbc.Driver";
 Class.forName(driver);
 Connection con = DriverManager.getConnection(connectStr, username, password);
 Statement stmt = con.createStatement();
 ResultSet rs = stmt.executeQuery("SELECT id, name, category FROM ingredient WHERE 
 name='" + theName + "'");

 String result = "";
 while (rs.next()) 
 {
  int theId3 = rs.getInt("id");
  String theName3 = rs.getString("name");
  String theCategory = rs.getString("category");
  result += "id: "+theId3+ " , name: "+theName3 + "("+theCategory+")" + "\n" + "\n";
 }
  return result;
 }//END METHOD
}//END CODE

我担心的另一个问题是，此代码可能会丢失某些内容，因为每当我尝试输入与这些结果不同的凭据时，它仅返回第一个结果。同样，此代码对应于我最近创建的一个index.html 文件...

Answer 1

I've seen how to do this with a number variable, but I seem to be having some trouble with String variables

这是完全相同的过程，但您使用 setString，如 JavaDocs 中所述。和 Using Prepared Statements

public String getIngredientByName(@QueryParam("name") String theName)
        throws SQLException, ClassNotFoundException {
    //Obtaining an ingredient from the database
    String connectStr = "jdbc:mysql://localhost:3306/fooddb";
    String username = "root";
    String password = "csci330pass";
    String driver = "com.mysql.jdbc.Driver";
    Class.forName(driver);
    String result = "";
    try (Connection con = DriverManager.getConnection(connectStr, username, password)) {
        try (PreparedStatement stmt = con.prepareStatement("SELECT id, name, category FROM ingredient WHERE name =?")) {
            stmt.setString(1, theName);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    int theId3 = rs.getInt("id");
                    String theName3 = rs.getString("name");
                    String theCategory = rs.getString("category");
                    result += "id: " + theId3 + " , name: " + theName3 + "(" + theCategory + ")" + "\n" + "\n";
                }
            }
        }
    }
    return result;
}//END METHOD

您也没有管理您的资源，您需要确保一旦您竞争使用资源，就关闭它们，如果您不小心，这将导致数据库出现问题

看看The try-with-resources Statement