I recently had to execute some SQL scripts when a Grails applications starts up to insert test data into the database.
Executing sql scripts is easy. Just open the SQL File and read the contents of the Script to a String variable.
String sqlFilePath = 'path/to/your/script.sql'
String sqlString = new File(sqlFilePath).text
Create a SQL connection and use it’s execute(String sql) method to execute your script. To configure the SQL connection, you can use the settings from your DataSource.groovy .
def sql = Sql.newInstance(ConfigurationHolder.config.dataSource.url,
ConfigurationHolder.config.dataSource.username,
ConfigurationHolder.config.dataSource.password,
ConfigurationHolder.config.dataSource.driverClassName)
sql.execute(sqlString)
Here is the full sourcecode:
import groovy.sql.Sql
import org.codehaus.groovy.grails.commons.ConfigurationHolder
String sqlFilePath = 'path/to/your/script.sql'
String sqlString = new File(sqlFilePath).text
def sql = Sql.newInstance(ConfigurationHolder.config.dataSource.url,
ConfigurationHolder.config.dataSource.username,
ConfigurationHolder.config.dataSource.password,
ConfigurationHolder.config.dataSource.driverClassName)
sql.execute(sqlString)
This can be very useful when you have to insert a lot of test data into the database.
groovy.sql.Sql中的execute()只能执行一行script代码,且不能执行空行,修正后的代码如下:
String sqlFilePath = 'path/to/script.sql'
String sqlString = new File(sqlFilePath).text
def sql = Sql.newInstance(ConfigurationHolder.config.dataSource.url,
ConfigurationHolder.config.dataSource.username,
ConfigurationHolder.config.dataSource.password,
ConfigurationHolder.config.dataSource.driverClassName)
sqlString.eachLine {line -> if(line.trim())sql.execute(line) }
参考文章:
http://blog.oio.de/2010/05/31/execute-sql-scripts-in-grails-bootstrap-or-integration-tests/
http://www.intelligrape.com/blog/2010/09/14/grails-execute-sql-script-in-bootstrap/
http://grails.1312388.n4.nabble.com/Running-a-mysql-dump-from-grails-BootStrap-fails-unexpectedly-td3690734.html
分享到:
相关推荐
ExecuteSQL.java
SQL Server EXEC和sp_executesql的区别
SQL Server 中 EXEC 与 SP_EXECUTESQL 的区别 MSSQL为我们提供了两种动态执行SQL语句的命令,分别是 EXEC 和 SP_EXECUTESQL ,我们先来看一下两种方式的用法。 先建立一个表,并添加一些数据来进行演示: 复制代码...
系统存储过程,sp_executesql 语言在这里! sp_executesql 执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。 语法 sp_executesql [@stmt =] stmt...
You can use this tool to simulate to execute SQL instead of PL-SQL(Oracle) and T-SQL(SQL server). Good SQL practise tool!
安装npm install execute-scripts用法var executeScripts = require ( 'execute-scripts' ) ;var container = document . createElement ( 'div' ) ;container . innerHTML = [ '[removed][removed]' , '[removed]...
ExecuteSQl函数 可用于VB编程,与数据库连接用
判断execute(sql)执行成功与否,帮助更好的控制代码操作性
摘要1,EXEC的使用2,sp_executesql的使用MSSQL为我们提供了两种动态执行SQL语句的命令,分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势,它提供了输入输出接口,而EXEC没有。还有一个最大的好处就是利用...
有一个word文档,是从网上摘录的,还有一个自己做的一个简单Demo。vs2008开发SSIS,在TASK组件中写入SQL时,如何调用变量。
事务处理函数transaction与executeSQL解析.docx
declare @LikeSql nvarchar(32);–定义一个like变量,如果是存储过程,此处可以存储过程的参数 set @LikeSql = ‘someword%’;...—使用@LikePar变量进行参数化 exec sp_executesql @SelectSql ,N’@LikePa
是一个SQL 脚本,让您创建,生成和执行SQL脚本更容易,MDBScript is a powerful script generation utility for any MS Access Database (*.mdb, *.accdb).Easily generate SQL scripts for any objects existing in ...
From 6.01, DB Query Analyzer provides SQL Execute Schedule function to execute SQL scripts in certain time. Without changing any Windows OS settings or configurations, DB Query Analyzer 6.03 can run ...
1、在创建或打开数据库以后,就可以使用事务 API transaction。每一个事务作为操作数据库的原子操作,不会被打断,从而避免了数据冲突。...2、在回调函数querysql中,可以执行SQL语句,对应的API函数为executeSQL,
Execute black box, white box, and integration tests Configure and manage stored packages and libraries Handle security with authentication and encryption Use LOBs to store text and multimedia content ...
executesql 存储过程代码,需要的朋友可以参考下。
动态SQL 并且把返回的值赋给变量,sp_executesql,动态sql语句基本语法
* SQL Multi Script™ Unlimited: execute multiple scripts against multiple SQL Servers with a single click * SQL Comparison SDK ™: automate functionality with our APIs With the SQL Toolbelt, you ...