Grails 框架直接使用sql语句

Grails 框架直接使用sql语句

grails 框架可以通过SQL语句直接对数据库进行增删改查操作，还可以创建数据库表，具体实现如下：

参考文档：Groovy SQL

优秀推荐：Groovy的SQL模块

def dataSource
def dataSource_lookup
// 定义已知数据源

@Transactional
def insertBySql()
{
// 1.第一种插入方式
def product = new Product()
product.name = "Grails"
product.code = "1109"
product.lookup.save()
// 2.第二种插入方式
def db = new groovy.sql.Sql(dataSource_lookup)
def params = [29, 0, 'Groovy', '1105']
db.execute "insert into product (id, version, name, code) values (?, ?, ?, ?)", params
db.close()
// 3.第三种插入方式
def dbParams = [url:'jdbc:postgresql://127.0.0.1:5432/testDataBase2', user:'test', password:'test', driver:'org.postgresql.Driver']
db = groovy.sql.Sql.newInstance(dbParams.url, dbParams.user, dbParams.password, dbParams.driver)
def map = [id:20, version: 1, name:'Grails', code:'9999']
db.execute "insert into product (id, version, name, code) values ($map.id, $map.version, $map.name, $map.code)"
db.close()
render product as JSON
}

@Transactional
def updateBySql()
{
def db = new groovy.sql.Sql(dataSource_lookup)
db.executeUpdate "update product set name='antushengtuhua' where code='1105'"
db.close()
render "success"
}

def queryBySql()
{
// 1.第一种查询
def db = new groovy.sql.Sql(dataSource_lookup)
db.eachRow('select * from product') { row ->
println row
}
// 2.第一种查询，支持分页
db.eachRow('select * from product', 2, 2)
{
println it
}
// 3.第二种查询
def rows = db.rows("select * from product where name like 'Gra%'")
println rows.join('n')
db.close()
render "success"
}

@Transactional
def deleteBySql()
{
def db = new groovy.sql.Sql(dataSource_lookup)
db.execute('delete from product')
db.close()
render "success"
}

@Transactional
def createTable()
{
def db = new groovy.sql.Sql(dataSource_lookup)
db.execute ('''
create table project (
id integer not null,
name varchar(50),
url varchar(100)
);
''')
db.close()
render "success"
}