JDBC SQL SERVER：该语句未返回结果集 [英] JDBC SQL SERVER: The statement did not return a result set

问题描述

我正在从Microsoft SQL Server Studio执行以下查询，该查询工作正常并显示结果：

I am executing the following query from Microsoft SQL Server Studio, which works fine and displays results:

SELECT *
INTO   #temp_table
FROM   md_criteria_join
WHERE  user_name = 'tecgaw'

UPDATE #temp_table
SET    user_name = 'tec'
WHERE  user_name != 'tec'

SELECT *
FROM   md_criteria_join
WHERE  user_name = 'tec'
   AND view_name NOT IN (SELECT view_name
                         FROM   md_criteria_join
                         WHERE  user_name = 'tecgaw')
UNION
SELECT *
FROM   #temp_table
ORDER  BY view_name,
      user_name,
      crit_usage_seq,
      crit_join_seq 

但是，如果我在Java中执行相同的查询，则会抛出异常语句没有返回结果集。这是Java代码：

However, if I execute the same query in Java, an Exception is thrown stating "The statement did not return a result set." Here's the Java code:

statement = conn.getConnection().createStatement();
resultSet = stmt.executeQuery(sql.toString());

这是因为我不能在一个语句中执行多个SQL查询（即，创建 #temp_table ，更新它，然后使用它我的select语句？？

Is that because I cannot do multiple SQL queries in one statement (I.e., Creating the #temp_table, updating it, and then using for it my select statement)?

推荐答案

我在StackOverflow中找到了类似的问题，此处。您应该启用连接以支持多个语句，并使用; 将它们分开。具体的例子请看答案。但它仅适用于MySql。

I have found similar question in StackOverflow here. You should enable connection to support multiple statements and separate them using ;. For concrete examples see that answer. However it is for MySql only.

此外，我认为您可以将SQL重写为单个查询

Also I think you can rewrite your SQL into single query

SELECT columnA, columnB, 'tec' as user_name from md_criteria_join
WHERE (
       user_name = 'tec' 
   AND view_name NOT IN (
       SELECT view_name 
       FROM md_criteria_join 
       WHERE user_name = 'tecgaw')
   )
   OR user_name = 'tecgaw' 
ORDER BY view_name, user_name, crit_usage_seq, crit_join_seq

另一种选择是将语句移动到存储过程并使用CallableStatement从JDBC中调用它

Another option is to move your statements to stored procedure and ivoke it from JDBC using CallableStatement

或许您应该尝试使用多个这样的jdbc语句执行它

Or maybe you should try executing it with multiple jdbc statements like this

Connection conn = conn.getConnection(); //just to make sure its on single connection
conn.createStatement("SELECT INTO #temp_table").executeUpdate();
conn.createStatement("UPDATE #temp_table").executeUpdate();
conn.createStatement("SELECT ...").executeQuery();

注意你必须关闭资源，为了获得更好的性能，你可以使用addBatch和executeBatch方法

Note you have to close resources and maybe for better performance you could use addBatch and executeBatch methods

这篇关于JDBC SQL SERVER：该语句未返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！