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

问题描述

对于我的应用程序，我需要在运行时动态数据库连接.我知道，有多种方法可以创建多个数据源，但我认为它们并不是动态的.场景:

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.

我已经尝试在服务中做到这一点，就像他们在 如果我在 grails 中使用 groovy sql 类，它是否使用 grails 连接池?

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?

注意:在这种情况下 GORM 是可有可无的，我可以使用普通 SQL 代替.

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

有什么想法吗?谢谢..

Any ideas? Thank you..

Grails 2.3.4

Grails 2.3.4

推荐答案

你可以做这样的事情来在运行时注册 DataSource bean:

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

给定一个 Grails 服务:

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' )

为它获取一个 Groovy Sql 对象:

Get a Groovy Sql object for it:

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

完成后取出豆子

dataSourceService.deRegisterBean( 'myDS' )

手指交叉……我从我的一个项目中提取了该代码并对其进行了更改/未测试;-)

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

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

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屋！