Java JDBC 返回空结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4343078/
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
JDBC returning empty result set
提问by dpsthree
I'm using JDBC for very simple database connectivity.
我将 JDBC 用于非常简单的数据库连接。
I have created my connection/statement and executed a query. I check the query object of the statement in the debugger to confirm that it is sending a proper query. I then double checked the query (copied straight from debugger) on the database to make sure it returns data. The returned resultset, however, gives false on .next()
我已经创建了我的连接/语句并执行了一个查询。我在调试器中检查语句的查询对象以确认它正在发送正确的查询。然后我仔细检查了数据库上的查询（直接从调试器复制）以确保它返回数据。然而，返回的结果集在 .next() 上给出 false
Are there any common pitfalls here that I'm missing?
这里有我遗漏的常见陷阱吗？
public List<InterestGroup> getGroups() {
myDB.sendQuery("select distinct group_name From group_members where
username='" + this.username + "'");
ResultSet results = myDB.getResults();
List<InterestGroup> returnList = new ArrayList<InterestGroup>();
try {
while (results.next()) {
returnList.add(new InterestGroup(results.getString("group_name"), myDB));
}
return returnList;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
And the myDB class (simple wrapper that lets me drop the connection/statement code into any project)
和 myDB 类（简单的包装器，让我将连接/语句代码放入任何项目中）
public void sendQuery(String query){
this.query = query;
try {
if(statement == null){
statement = connection.createStatement();
}
results = statement.executeQuery(query);
} catch (SQLException e) {
System.out.println(query);
currentError = e;
results = null;
printError(e, "querying");
}
}
public ResultSet getResults(){
return results;
}
EDIT: Based on suggestions I have mostly revamped my code but still have the same problem. Below is a simplified portion of code that has the same problem.
编辑：根据建议，我主要修改了我的代码，但仍然存在相同的问题。下面是具有相同问题的代码的简化部分。
private boolean attemptLogin(String uName, String pWord) {
ResultSet results;
try{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
connection =DriverManager.getConnection(connectionString,user,password);
PreparedStatement statement = connection.prepareStatement("select username from users where username='testuser'");
results = statement.executeQuery();
if(results != null && results.next()){
System.out.println("found a result");
statement.close();
return true;
}
System.out.println("did not find a result");
statement.close();
return false;
}catch(SQLException e){
e.printStackTrace();
return false;
}
}
I have also hardcoded the query in place for now to eliminate that source of error. Same problem as before (this happens with all queries). Debugger shows all objects getting instantiated and no stack traces are printed. Furthermore, I am able to use the same code (and the more complicated code listed previously) in a different project.
我现在还对查询进行了硬编码，以消除该错误源。与以前相同的问题（所有查询都会发生这种情况）。调试器显示所有对象被实例化，并且没有打印堆栈跟踪。此外，我可以在不同的项目中使用相同的代码（以及前面列出的更复杂的代码）。
采纳答案by dpsthree
I figured it out....stupid Oracle didn't like the number of concurrent connections I had (all two of them, one for console, one for java). Unfortunately, the server is not under my control so I will just have to deal with it. You would think that Oracle would provide a better response. Instead it just returned empty result sets.
我想通了……愚蠢的 Oracle 不喜欢我拥有的并发连接数（所有两个，一个用于控制台，一个用于 Java）。不幸的是，服务器不在我的控制之下，所以我只需要处理它。您会认为 Oracle 会提供更好的响应。相反，它只是返回空的结果集。
Thanks for the responses
感谢您的回复
editSince this was asked/answered there have been a number of people pointed out that the underlying cause is more likely related to the commit/transaction settings in use. Please be sure to see other answers for additional hints and possible solutions.
编辑自从提出/回答这个问题以来，已经有很多人指出根本原因更可能与使用中的提交/事务设置有关。请务必查看其他答案以获取其他提示和可能的解决方案。
回答by wallyk
The most common is having multiple statements in the query:
最常见的是在查询中有多个语句：
desc table;
select * from sometable where etc.;
For statements which do not return results, you have to use a different construct. Even this will cause the client to choke:
对于不返回结果的语句，您必须使用不同的构造。即使这样也会导致客户端窒息：
select * from sometable where whatever;
select * from sometable where something else;
The two same-shaped result sets will biff the client.
两个相同形状的结果集将比对客户端。
回答by Bert F
I then double checked the query (copied straight from debugger) on the database to make sure it returns data.
然后我仔细检查了数据库上的查询（直接从调试器复制）以确保它返回数据。
I've had engineers with this problem demonstrate this verification in front of me. It turns out they were logged in with one database account in the program and with a different database account in the interactive SQL shell. [This was Oracle 8.]
我有遇到这个问题的工程师在我面前演示了这个验证。事实证明，他们在程序中使用一个数据库帐户登录，在交互式 SQL shell 中使用不同的数据库帐户登录。[这是 Oracle 8。]
回答by gmhk
Please check whether the connection and statement Object alive until you iterate the result set, some times we may close unknowingly.
请在迭代结果集之前检查连接和声明对象是否活着，有时我们可能会在不知不觉中关闭。
回答by darioo
I see a few pitfalls in your code, there are a few places where things can go wrong:
我在您的代码中看到了一些陷阱，有一些地方可能会出错：
First, use of regular statements. Use prepared statementsso you won't have problems with SQL injection.
首先，使用正则语句。使用准备好的语句，这样您就不会遇到SQL 注入问题。
Instead of
代替
statement = connection.createStatement();
use
用
statement = connection.prepareStatement(String sql);
With this, your query becomes
有了这个，你的查询变成
"select distinct group_name From group_members where username= ?"
and you set username with
你设置用户名
statement.setString(1, username);
Next, I don't like use of your myDBclass. What if results is null? You're not doing any error checking for that in your public List<InterestGroup> getGroups()method.
其次，我不喜欢使用你的myDB类。如果结果是null什么？你没有在你的public List<InterestGroup> getGroups()方法中做任何错误检查。
public void sendQuery(String query)seems to me like it shouldn't be void, but it should return a ResultSet. Also, search on the net for proper ways to do JDBC exception handling.
public void sendQuery(String query)在我看来它不应该是void，但它应该返回一个ResultSet. 另外，在网上搜索进行 JDBC 异常处理的正确方法。
Also, this line:
此外，这一行：
new InterestGroup(results.getString("group_name"), myDB)
Why do you have myDBas a parameter?
为什么你有myDB一个参数？
I'd suggest adding more System.out.printlnstatements in your code so you can see where things can go wrong.
我建议System.out.println在您的代码中添加更多语句，以便您可以查看哪里可能出错。
回答by dpsthree
In java.sql.connection you should call this method after you create your connection :
在 java.sql.connection 中，您应该在创建连接后调用此方法：
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
maybe there is a similar method in Oracle.
也许在 Oracle 中有类似的方法。
回答by Chirantan
In the past I had similar issues in code such as this:
过去我在代码中遇到过类似的问题，例如：
querystr = "your sql select query string"
resultset = statement.executeQuery(querystr)
while (resultset.next())
{
//do something with the data.
//if you do something fairly involved in this block (sequentially, in the same thread)
//such as calling a function that acts on the data returned from the resultset etc.
//it causes the resultset fetch to wait long enough for resultset.next() to
//unexpectedly return null in the middle of everything
}
What I did in this situation was to load up all data into a local memory data structure with minimum wait on resultset.next(). Then I did whatever I had to on the data from the local data structure after gracefully closing resultset. This behavior was with Oracle 10 on Unix backend/JDK 1.6.0_22 client under Windows XP.
在这种情况下，我所做的是将所有数据加载到本地内存数据结构中，同时对 resultset.next() 的等待时间最少。然后，在优雅地关闭结果集后，我对来自本地数据结构的数据做了我必须做的任何事情。此行为与 Windows XP 下 Unix 后端/JDK 1.6.0_22 客户端上的 Oracle 10 相关。
Hope this helps.
希望这可以帮助。
回答by user3916827
in my case the query which worked in sql developer didn't work in JAVA.
在我的情况下，在 sql developer 中工作的查询在 JAVA 中不起作用。
*select * from table where process_date like '2014-08-06%'* (worked in sql developer)
formating process_date to char helped to make it work in JAVA
将 process_date 格式化为 char 有助于使其在 JAVA 中工作
*select * from table where to_char(process_date) = '06-AUG-14'*
回答by Martin Benns
For me the problem was that on the creation of the primary key column I had NOT NULL ENABLE. As in ...
对我来说，问题是在创建主键列时，我没有启用 NOT NULL。就像在...
CREATE TABLE SYSTEM_SETTINGS (
SYSTEM_SETTING_ID NUMBER(9,0) NOT NULL ENABLE,
"KEY" VARCHAR2(50 BYTE),
"VALUE" VARCHAR2(128 BYTE),
CONSTRAINT "PK_SYSTEM_SETTINGS" PRIMARY KEY (SYSTEM_SETTING_ID))
TABLESPACE USERS;
When I recreated the table without that as in
当我重新创建没有那个的表时
CREATE TABLE SYSTEM_SETTINGS (
SYSTEM_SETTING_ID NUMBER(9,0),
"KEY" VARCHAR2(50 BYTE),
"VALUE" VARCHAR2(128 BYTE),
CONSTRAINT "PK_SYSTEM_SETTINGS" PRIMARY KEY (SYSTEM_SETTING_ID))
TABLESPACE USERS;
It started working via JDBC. I am using ojdbc6.jar for the jdbc driver.
它开始通过 JDBC 工作。我使用 ojdbc6.jar 作为 jdbc 驱动程序。
回答by anuvab1911
The same happened to me. I was using SQL Developerto insert test data into my database and test-reading that using JDBC. But all I got was an empty result-set. I could get the column names and all, but had a problem with reading data. As pointed out by dpsthree earlier, I disconnected from the SQL Developer IDE and then it asked me to Commit upon exiting.
我也遇到了同样的情况。我使用SQL Developer将测试数据插入我的数据库并使用 JDBC 测试读取。但我得到的只是一个空的结果集。我可以获得列名和所有内容，但在读取数据时遇到问题。正如 dpsthree 之前指出的那样，我从 SQL Developer IDE 断开连接，然后它要求我在退出时提交。
Voila! The problem was that the changes to the databases using the insert command weren't committed.
瞧！问题是使用插入命令对数据库的更改没有提交。
For SQL Developer this is located at Preferences > Database > Advanced > Autocommit
对于 SQL Developer，它位于首选项 > 数据库 > 高级 > 自动提交
This solved my problem.
这解决了我的问题。
