在SQL查询中动态设置数据库 [英] dynamically set the db in a sql query

问题描述

我尝试在mysql中的几个dbs中运行相同的查询：

  def m ='xxx'
 def dbs = ['DB05DEC05'，'DB06DEC06'，'DB07DEC07'，'DB08DEC08'，'DB09DEC09'，'DB10DEC10'] 
 def sql = Sql.newInstance（jdbc：mysql：// localhost：3306 ，root，，org.gjt.mm.mysql.Driver）
 dbs.each {
 db-> sql.eachRow（select * from $ {db} .mail where mid = $ m，{println\t $ db $ {it.mid}}）; 
} 
  

这会产生一个错误：

 您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册，以便在''DBJAN05DEC05'.mail附近使用正确的语法，其中mid ='xxx'
  

Groovy显然使用引号做了一些自定义的东西，并要求不要在sql中使用引号（注意mid = $ m，如果使用mid ='$ m'它会警告您不要使用引号） 。
问题是，在第一个$我根本不知道需要引号，引号是问题...

groovy 1.7在vista上。
谢谢

编辑：我发现了一个类似的问题，但它没有被接受的答案... Groovy GString问题

解决方案

问题是SQL查询方法可以看到GString及其嵌入的变量引用，并将每个引用转换为一个？在准备好的声明中。

select * from table where col = $ {value}）

...相当于：

  sql.query（select * from table where col =？，[value]）
  

但是：

  sql .query（select * from $ {db} .table where col = $ {value}）
  

相当于：

  sql.query（select * from？.table where col =？，[db，value ]）
  

...在数据库层失败，因为select语句无效。 p>

显而易见的解决方法是使用query（）的显式准备语句版本。

  dbs.each {db-> 
 sql.eachRow（select * from $ {db} .mail where mid =？，m，{
 println\t $ db $ {it.mid}
} ）; 
} 
  

然而，Sql类为您提供了一个expand（）方法，为此设计。

  dbs.each {db  - > 
 sql.eachRow（
select * from $ {Sql.expand（db）}。mail where mid = $ {m}，
 {println\ $ $ db $ { it.mid}}）; 
} 
  

I try to run the same query in several dbs in mysql:

def m='xxx'
def dbs =  ['DB05DEC05','DB06DEC06','DB07DEC07','DB08DEC08','DB09DEC09','DB10DEC10']
def sql =Sql.newInstance("jdbc:mysql://localhost:3306", "root","", "org.gjt.mm.mysql.Driver")
dbs.each{
 db-> sql.eachRow("select * from ${db}.mail where mid=$m", { println "\t$db ${it.mid}"} );
}

This gives an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DBJAN05DEC05'.mail where mid='xxx'

Groovy apparently does some custom stuff with quotes and asks you not to use quotes in the sql (notice mid=$m, if you use mid='$m' it warns you against using the quotes). The problem is that in the first $ I dont know want quotes at all, the quotes are the problem...

groovy 1.7 on vista. thanks

editing: I have found a similar question, but it does not have an accepted answer either... Groovy GString issues

解决方案

The problem is that the SQL query method sees the GString, with its embedded variable references, and turns each reference into a ? in a prepared statement.

So:

sql.query("select * from table where col = ${value}")

... is equivalent to:

sql.query("select * from table where col = ?", [ value ])

But also:

sql.query("select * from ${db}.table where col = ${value}")

is equivalent to:

sql.query("select * from ?.table where col = ?", [ db, value ])

... which fails at the DB layer because the select statement is not valid.

The obvious workaround is to use the explicit prepared statement version of query().

dbs.each{ db-> 
    sql.eachRow("select * from ${db}.mail where mid=?", m, { 
       println "\t$db ${it.mid}"
    });
}

However, the Sql class gives you an expand() method, that appears to be designed for this purpose.

dbs.each{ db -> 
   sql.eachRow(
      "select * from ${Sql.expand(db)}.mail where mid=${m}", 
      { println "\t$db ${it.mid}"} );
}

这篇关于在SQL查询中动态设置数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！