Grails：创建动态SQL连接 [英] Grails: Create dynamic SQL-Connection

问题描述

对于我的应用程序，我需要运行时的动态数据库连接。
我知道，有很多方法可以创建多个数据源，但它们并非动态的我想。
场景：

用户可以输入数据库凭证并连接到远程数据库，以将单个行和表导入到其他数据库。为此，我需要动态连接到远程数据库。

我试图在服务中这样做，就像他们在

注意：在这种情况下，GORM是可有可无的，我可以使用纯SQL代替。

任何想法？谢谢..
$ b $编辑：Grails 2.3.4

解决方案

您可以在运行时注册DataSource bean：

给定一个Grails服务：

 打包任何
 
导入groovy.sql.Sql 
导入org.springframework.context。* 
导入org.apache.tomcat.jdbc.pool .DataSource 
 import org.springframework.context.support.GenericApplicationContext 
 $ b $ class DataSourceService实现ApplicationContextAware {
 
 ApplicationContext applicationContext 
 
 def registerBean（如果（！applicationContext.containsBean（beanName））{
 def bb = new grails.spring.BeanBuilder（）
 bb.beans {
$ beanName（DataSource）{
 driverClassName =com.mysql.jdbc.Driver
 url = dsurl 
 username = uid 
 password = pwd 
 validationQuery =SELECT 1
 testOnBorrow = true 
 maxActive = 1 
 maxIdle = 1 
 minIdle = 1 
 initialSize = 1 
 
 
 bb.registerBeans（applicationContext）
 log.info添加$ beanName
} 
 else {
 log.error已经有一个名为$ beanName的bean
} 
} 
 
 def deRegisterBean（String beanName）{
 if（applicationContext.containsBean（beanName ））{
（applicationContext as GenericApplicationContext）.removeBeanDefinition（beanName）
 log.infoRemoved $ beanName
} 
 else {
 log.error试图注销一个bean $ beanNa我不知道

} 
 
 def getSql（String beanName）{
 Sql.newInstance（applicationContext.getBean（beanName））
 
 
  

然后，你应该可以调用服务来注册一个新的数据源：

  dataSourceService.registerBean（'myDS'，'jdbc：mysql：// localhost：3306 / mysql'， 'test'，'test'）
  

获取一个Groovy Sql对象：

  dataSourceService.getSql（'myDS'）.rows（'SELECT * FROM whatever'）
  

并在完成时移除bean

  dataSourceService.deRegisterBean 'myDS'）
  

手指交叉...我从我的一个项目中抽取了代码，改变/未测试它; - ）

更新

运行时数据源插件已经创建，它使用了本文中概述的方法，允许在运行时添加/删除数据源。

For my application I need dynamic database connections at runtime. I know, there are ways to create multiple datasources but they are not that dynamically I think. Scenario:

A user can enter database credentials and connect to a remote database to import single rows and tables to an other database. For this purpose I need to connect to the remote database dynamically.

I've tried to do that in a service like they've said in If I use groovy sql class in grails, does it use the grails connection pooling?

Note: GORM is dispensable in this case, I can use plain SQL instead.

Any ideas? Thank you..

Edit: Grails 2.3.4

解决方案

You can do this sort of thing to register DataSource beans at runtime:

Given a Grails Service:

package whatever

import groovy.sql.Sql
import org.springframework.context.*
import org.apache.tomcat.jdbc.pool.DataSource
import org.springframework.context.support.GenericApplicationContext

class DataSourceService implements ApplicationContextAware {

    ApplicationContext  applicationContext

    def registerBean( String beanName, String dsurl, String uid, String pwd ) {
        if( !applicationContext.containsBean( beanName ) ) {
            def bb = new grails.spring.BeanBuilder()
            bb.beans {
                "$beanName"( DataSource ) {
                    driverClassName = "com.mysql.jdbc.Driver"
                    url             = dsurl
                    username        = uid
                    password        = pwd
                    validationQuery = "SELECT 1"
                    testOnBorrow    = true
                    maxActive       = 1
                    maxIdle         = 1
                    minIdle         = 1
                    initialSize     = 1
                }
            }
            bb.registerBeans( applicationContext )
            log.info "Added $beanName"
        }
        else {
            log.error "Already got a bean called $beanName"
        }
    }

    def deRegisterBean( String beanName ) {
        if( applicationContext.containsBean( beanName ) ) {
            (applicationContext as GenericApplicationContext).removeBeanDefinition( beanName )
            log.info "Removed $beanName"
        }
        else {
            log.error "Trying to deRegister a bean $beanName that I don't know about"
        }
    }

    def getSql( String beanName ) {
        Sql.newInstance( applicationContext.getBean( beanName ) )
    }
}

Then, you should be able to call the service to register a new datasource:

dataSourceService.registerBean( 'myDS', 'jdbc:mysql://localhost:3306/mysql', 'test', 'test' )

Get a Groovy Sql object for it:

dataSourceService.getSql( 'myDS' ).rows( 'SELECT * FROM whatever' )

And remove the bean when done

dataSourceService.deRegisterBean( 'myDS' )

Fingers crossed... I've yanked that code from a project of mine and changed/not-tested it ;-)

Update

The runtime-datasources plugin has been created which uses the approach outlined in this post to allow datasources to be added/removed at runtime.

这篇关于Grails：创建动态SQL连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！