java 如何使用用户输入作为查询参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16354495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use user input as parameter for query?
提问by derek
So I'm very new to java and SQL and they are my first programming languages. I am trying to do some work with JDBC. I want to allow for a user to input an id and return a query based on the variable. If someone could at least point me in the right direction... Here is the code I'm starting with. Mind you its crude but just trying to get a working piece of code so I can better implement it in my main class.
所以我对 java 和 SQL 很陌生，它们是我的第一门编程语言。我正在尝试使用 JDBC 做一些工作。我想允许用户输入一个 id 并根据变量返回一个查询。如果有人至少可以指出我正确的方向......这是我开始使用的代码。请注意，它很粗糙，但只是试图获得一段有效的代码，以便我可以在我的主类中更好地实现它。
Scanner input = new Scanner(System.in);
Class.forName("org.sqlite.JDBC");
Connection conn =
DriverManager.getConnection("jdbc:sqlite:C:\Users\Derek\Documents\Databases\example.sqlite");
Statement stat = conn.createStatement();
PreparedStatement prep1 = conn.prepareStatement(
"insert into MedType values (?, ?);");
PreparedStatement prep2 = conn.prepareStatement(
"insert into Media values (?, ?,?, ?,?, ?);");
System.out.print("Please choose a database(MedType or Media): ");
String db=input.next();
if(db.equalsIgnoreCase("MedType"))
{
System.out.print("Enter in ID: ");
String answer1 = input.next();
System.out.print("");
String answer2 = input.nextLine();
System.out.print("Enter in Description: ");
String answer3 = input.nextLine();
prep1.setString(1, answer1);//add values into cell
prep1.setString(2, answer3);
prep1.addBatch();//add the columns that have been entered
}
conn.setAutoCommit(false);
prep1.executeBatch();
prep2.executeBatch();
conn.setAutoCommit(true);
System.out.print("Please Enter Query(One or All): ");
String q=input.next();
ResultSet rs= stat.executeQuery("select * from MedType;");
if(q.equalsIgnoreCase("all")){
while (rs.next()) {
System.out.print("All ID = " + rs.getString("ID") + " ");
System.out.println("All Description = " + rs.getString("Description"));}
}
if(q.equalsIgnoreCase("one")){
System.out.print("Enter ID: ");
}
int idNum=input.nextInt();
ResultSet oneRs = stat.executeQuery("select * from MedType Where"+ (rs.getString("ID")+"="+idNum));
if(q.equalsIgnoreCase("one")){
while (oneRs.next()) {
System.out.print("ID = " + oneRs.getString("ID") + " ");
System.out.println("Description = " + oneRs.getString("Description"));
}
}
rs.close();
oneRs.close();
conn.close();
}
}
ResultSet oneRs = stat.executeQuery("select * from MedType Where"+
(rs.getString("ID")+"="+idNum));
This is where I'm having trouble. Creating a statement that says return something from the table if its id is equal to the user input. I get this error
这就是我遇到麻烦的地方。创建一个语句，表示如果其 id 等于用户输入，则从表中返回某些内容。我收到这个错误
Exception in thread "main" java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "=": syntax error)
Exception in thread "main" java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "=": syntax error)
回答by Jayant Jadhav
In query you are trying to access single row by passing id.. In generally sql query we are using to access single row by passing some information. select * from MedType where id=3this query will return you result set containing row or rows with id equals to 3. so in your code your query should be select * from MedType where id="+idNum+"if in your db id column is int. and keep this query in if block only i.e
在查询中，您试图通过传递 id 来访问单行。在一般的 sql 查询中，我们使用通过传递一些信息来访问单行。select * from MedType where id=3此查询将返回包含 id 等于 3 的行或行的结果集。因此在您的代码中，您的查询应该是select * from MedType where id="+idNum+"if in your db id column是整数。并将此查询保留在 if 块中，即
if(q.equalsIgnoreCase("one"))
{
System.out.print("Enter ID: ");
int idNum=input.nextInt();
ResultSet oneRs = stat.executeQuery("select * from MedType Where id="+idNum+" ");
// if id column in db is int if it is string then use id='"+idNum+"'
while (oneRs.next())
{
System.out.print("ID = " + oneRs.getString("ID") + " ");
System.out.println("Description = " + oneRs.getString("Description"));
}
}
回答by howiewylie
In your query:
在您的查询中：
select * from MedType Where"+ (rs.getString("ID")+"="+idNum
you seem to try to grab the ID from the first resultset where you return all tuples.
您似乎试图从返回所有元组的第一个结果集中获取 ID。
That won't work as in the where clause the ID won't be there (as there is no result right now without rs.next()). If there was a result then you potentially have something like 'where 3 = 3' (3 would be the result of the previously returned value. Have you tried simply to use:
这不会像在 where 子句中那样工作，ID 不会在那里（因为现在没有结果没有rs.next()）。如果有结果，那么您可能会得到类似 ' where 3 = 3' 的结果（3 将是先前返回值的结果。您是否尝试过简单地使用：
select * from MedType Where ID = " + idNum
Hope that makes sense.
希望这是有道理的。
