为什么我的代码会产生错误:语句未返回结果集 [英] Why does my code produce the error: 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执行相同的查询，则会抛出一个异常，指出

However, if I execute the same query in Java, an Exception is thrown stating

该语句未返回结果集.

The statement did not return a result set.

这是Java代码:

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

这篇关于为什么我的代码会产生错误:语句未返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！