Grails 4: How to load datasource configuration from external file in grails 4 In Grails, how do I put my DB username and password in an external property file

I'm trying to take certain database configurations from variables and put them into an external properties file. I am writing a grails 4.0.11 application. My datasource written in application.groovy file. I want to load datasource configuration like username,password,DB from an external file. Is there any way to do it in grails 4+ versions. Here is my datasource configuration in application.groovy using static database name and other properties like username, password etc:-

hibernate { cache { queries = false use_second_level_cache = true use_query_cache = true } } dataSource { pooled = true jmxExport = true dialect = "org.hibernate.dialect.MySQL5InnoDBDialect" driverClassName = "org.mariadb.jdbc.Driver" username = 'root' password = '' dbCreate = "update" url = "jdbc:mysql://localhost/db2?useUnicode=yes" + "&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true" + "&useLegacyDatetimeCode=false&serverTimezone=UTC" properties = { jmxEnabled = true initialSize = 5 maxActive = 50 minIdle = 5 maxIdle = 25 maxWait = 10000 maxAge = 10 * 60000 timeBetweenEvictionRunsMillis = 5000 minEvictableIdleTimeMillis = 60000 validationQuery = "SELECT 1" validationQueryTimeout = 3 validationInterval = 15000 testOnBorrow = true testWhileIdle = true testOnReturn = false ignoreExceptionOnPreLoad = true jdbcInterceptors = "ConnectionState;StatementCache(max=200)" defaultTransactionIsolation = Connection.TRANSACTION_READ_COMMITTED // safe default abandonWhenPercentageFull = 100 // settings are active only when pool is full removeAbandonedTimeout = 120 removeAbandoned = true logAbandoned = false // causes stacktrace recording overhead, use only for debugging } }

Yes, what we can do that is to put database configurations to a file named db_name.properties under [src/main/webapp] directory with following contents: db_name=some_data_base_name db_user=root_user db_password=some_password Keeping these information will not load automatically. We have to do something magic to load these information into system. We can define database configuration for grails 4 in 3 different ways - 1. grails-app/conf/config/application.yml 2. grails-app/conf/application 3. grails-app/conf/application.groovy So from above list we can easily set our target file to load grails 4 application datasource information because we can write code inside groovy files. First of all remove any datasource related block from above 2 files and add configuration to grails-app/conf/application.groovy file as early statement with some modification. Now we will load database information from some properties file. We sill use Properties to load database information from file. Check below code snippet:

import grails.util.BuildSettings import java.sql.Connection grails { gorm { failOnError = true 'default' { mapping = { cache true version false autoTimestamp false id generator:'assigned' '*'(cascadeValidate: 'none') } } } } Properties ppt = new Properties() File file = new File(BuildSettings.BASE_DIR.absolutePath + "/src/main/webapp/db.properties") println("Setting up db name-${file.absolutePath}, exists=${file.exists() ? 1 : 0}") if (file.exists()) { file.getCanonicalFile().withInputStream { InputStream stream -> ppt.load(stream) } } println(ppt) hibernate { cache { queries = false use_second_level_cache = true use_query_cache = true } } dataSource { pooled = true jmxExport = true dialect = "org.hibernate.dialect.MySQL5InnoDBDialect" driverClassName = "org.mariadb.jdbc.Driver" username = 'root' password = '' dbCreate = "update" url = "jdbc:mysql://localhost/${ppt.get("db.name", "none_db_selected")}?useUnicode=yes" + "&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true" + "&useLegacyDatetimeCode=false&serverTimezone=UTC" properties = { jmxEnabled = true initialSize = 5 maxActive = 50 minIdle = 5 maxIdle = 25 maxWait = 10000 maxAge = 10 * 60000 timeBetweenEvictionRunsMillis = 5000 minEvictableIdleTimeMillis = 60000 validationQuery = "SELECT 1" validationQueryTimeout = 3 validationInterval = 15000 testOnBorrow = true testWhileIdle = true testOnReturn = false ignoreExceptionOnPreLoad = true jdbcInterceptors = "ConnectionState;StatementCache(max=200)" defaultTransactionIsolation = Connection.TRANSACTION_READ_COMMITTED // safe default abandonWhenPercentageFull = 100 // settings are active only when pool is full removeAbandonedTimeout = 120 removeAbandoned = true logAbandoned = false // causes stacktrace recording overhead, use only for debugging } }

In above example I only set database name, you can set anything from that configuration file as I did for database name.

Windows Batch Script to create new MySQL database and import

Create a file named restore.bat with following contents & double to click to create new database & import data to selected database

@ECHO OFF
cd C:\xampp\mysql\bin
mysql.exe -uroot -hlocalhost -e "drop database if exists some_db_name";
mysql.exe -uroot -hlocalhost -e "create database some_db_name default character set utf8 default collate utf8_general_ci;";
echo DB Created
mysql.exe -uroot -hlocalhost some_db_name < "C:\tmp\back_up.sql"
echo DB Restored
pause
exit;

Grail's Create Or Change Database Connection Manually Or Run-time | Using multiple data-sources in a Grail's project | Grail's Multiple Data Sources | Grail's multi tenant data-sources

In src/groovy, create a groovy class named 'UserHolder' with following contents:

package a.b.c

/**
 * Created by pritom on 14/08/2014.
 */
class UserHolder {
    public static Integer DEFAULT = 1;
    private static final ThreadLocal contextHolder = new ThreadLocal();
    public static String DS_PREFIX = "dataSource_";
    public static String DS_POSTFIX = "User";

    static void setEnvironment(Map environment) {
        contextHolder.set(environment);
    }

    static getEnvironment() {
        return contextHolder.get();
    }

    static void clear() {
        contextHolder.remove();
    }
}

Also create a groovy class named 'SwitchableDataSource' in src/groovy with following contents:

package a.b.c

import grails.util.Holders
import org.springframework.context.ApplicationContext
import org.springframework.jdbc.datasource.DriverManagerDataSource
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource

import javax.sql.DataSource

/**
 * Created by pritom on 14/08/2014.
 */
class SwitchableDataSource extends AbstractRoutingDataSource {
    def applicationContext

    public void setApplicationContext(ApplicationContext applicationContext) {
        this.applicationContext = applicationContext
    }

    protected DataSource determineTargetDataSource() {
        def user = UserHolder.getEnvironment();
        try {
            DriverManagerDataSource ds = super.determineTargetDataSource();
            return ds;
        }
        catch (Exception ex) {
            println "--->Error:: ${ex.getMessage()}";
            try {
                def ga = Holders.getGrailsApplication();
                String beanFullName = UserHolder.DS_PREFIX + user.id + UserHolder.DS_POSTFIX;
                if(user && user.id && ga.mainContext.containsBean( beanFullName ) ) {
                    println "Using data source: '${beanFullName}'";
                    return ga.mainContext.getBean(beanFullName);
                }
            }
            catch (Exception ex2) {
                println "--->Error:: ${ex2.getMessage()}";
            }
        }
    }

    @Override
    protected Object determineCurrentLookupKey() {
        def user = UserHolder.getEnvironment();
        return user?.id ?: UserHolder.DEFAULT;
    }

    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(targetDataSources);
    }
}

Edit resource.groovy file under grails-app/conf/spring folder:

import a.b.c.SwitchableDataSource
import a.b.c.UserHolder
import org.springframework.jdbc.datasource.DriverManagerDataSource

// Place your Spring DSL code here
beans = {
    parentDataSource(DriverManagerDataSource) { bean ->
        bean.'abstract' = true;
        driverClassName = 'com.mysql.jdbc.Driver'
        username = "root"
    }

    "rootDataSource"(DriverManagerDataSource) { bean ->
        bean.parent = parentDataSource;
        bean.scope = 'prototype';
        url = "jdbc:mysql://localhost/user${UserHolder.DEFAULT}?useUnicode=yes&characterEncoding=UTF-8";
        username = "root"
    }

    def dataSources = [:]
    dataSources[UserHolder.DEFAULT] = ref("rootDataSource");

    dataSource(SwitchableDataSource) {
        targetDataSources = dataSources
    }
}

Now create another groovy class named 'DataSourceService' to bind datasource to your project dynamically/runtime with following contents:

package a.b.c

import grails.spring.BeanBuilder
import org.codehaus.groovy.grails.commons.GrailsApplication
import org.springframework.beans.BeansException
import org.springframework.context.ApplicationContext
import org.springframework.context.ApplicationContextAware
import org.springframework.context.support.GenericApplicationContext

/**
 * Created by pritom on 14/08/2014.
 */
class DataSourceService implements ApplicationContextAware {
    ApplicationContext applicationContext;
    public GrailsApplication grailsApplication;

    @Override
    void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.applicationContext = applicationContext;
    }

    /**
     * Add new bean to system
     * @param beanName
     * @param dsurl
     * @param uid
     * @param pwd
     * @return
     */
    def registerBean( String beanName, String dsurl, String uid, String pwd ) {
        String beanFullName = UserHolder.DS_PREFIX + beanName + UserHolder.DS_POSTFIX;
        if( !applicationContext.containsBean( beanFullName ) ) {
            def bb = new BeanBuilder()
            bb.beans {
                "$beanFullName" { bean ->
                    bean.parent = ref('parentDataSource');
                    bean.scope = 'prototype';
                    url = dsurl;
                    username = uid
                    password = pwd
                }
            }
            bb.registerBeans( applicationContext );

            println "Added $beanFullName"
        }
        else {
            println "Already got a bean called $beanFullName"
        }
    }

    /**
     * Remove bean from system
     * @param beanName
     * @return
     */
    def deRegisterBean( String beanName ) {
        if( applicationContext.containsBean( beanName ) ) {
            (applicationContext as GenericApplicationContext).removeBeanDefinition( beanName )
            println "Removed $beanName"
        }
        else {
            println "Trying to deRegister a bean $beanName that I don't know about"
        }
    }
}

Now create a filter in grails-app/filters named 'SecurityFilters' with following contents:

package filters

import a.b.c.UserHolder

class SecurityFilters {

    def filters = {
        all(controller: '*', action: '*') {
            before = {
     /* This line is for specify which user request to handle */
                if (params.int('user')) {
                    UserHolder.setEnvironment([id: params.int('user')]);
                }
            }
            after = { Map model ->

            }
            afterView = { Exception e ->

            }
        }
    }
}

Example of adding a new datasource to system:

DataSourceService dataSourceService = new DataSourceService();
dataSourceService.setApplicationContext(grailsApplication.mainContext);
dataSourceService.grailsApplication = grailsApplication;
dataSourceService.registerBean(params.user, "jdbc:mysql://localhost/user${params.user}?useUnicode=yes&characterEncoding=UTF-8", "root", "");

Suppose params.user = 3 here.

That's it Download code

GRAILS Database Data To XML File

import grails.converters.XML

grailsApplication.domainClasses.each { def domainClass ->
    dataMap[domainClass.clazz.simpleName] = domainClass.clazz.findAll();
}
String xmlString = new XML(dataMap).toString()

Reading i18n messages from the database with Grails

At first create a model class:

package com.locale.messaging

class Message {
    String code
    Locale locale
    String text

    static constraints = {
        code(unique: ["locale"])
    }

    static mapping = {
        version(false);
    }
}

Then implement a class that extends the org.springframework.context.support.AbstractMessageSource class. In the example below I am using simple GORM finders to lookup a message using the code and locale

package com.locale.messaging

import com.locale.messaging.Message
import net.sf.ehcache.Ehcache
import org.springframework.context.support.AbstractMessageSource
import net.sf.ehcache.Element;
import java.text.MessageFormat

/**
 * Created with IntelliJ IDEA.
 * User: pritom
 * Date: 9/12/13
 * Time: 9:09 AM
 * To change this template use File | Settings | File Templates.
 */
class DatabaseMessageSource extends AbstractMessageSource {
    Ehcache messageCache
    def messageBundleMessageSource

    @Override
    protected MessageFormat resolveCode(String code, Locale locale) {
        def key = "${code}_${locale.language}_${locale.country}_${locale.variant}";
        def format = messageCache.get(key)?.value;
        if (!format) {
            Message message = Message.findByCodeAndLocale(code, locale);
            if (message) {
                format = new MessageFormat(message.text, message.locale);
            } else {
                format = messageBundleMessageSource.resolveCode(code, locale);
            }
            messageCache.put(new Element(key, format))
        } else {
            format = (MessageFormat) format;
        }
        return format;
    }
}

Then configure an appropriate cache bean (I'm using Ehcache) in Spring and wire it into your MessageSource in grails-app/conf/spring/resources.groovy file:

import com.bitmascot.util.DatabaseMessageSource
import org.springframework.cache.ehcache.EhCacheFactoryBean

// Place your Spring DSL code here
beans = {
    messageCache(EhCacheFactoryBean) {
        eternal = false;
        timeToIdle = 5000;
    }
    messageSource(DatabaseMessageSource) {
        messageCache = messageCache
        messageBundleMessageSource = ref("messageBundleMessageSource")
    }

    messageBundleMessageSource(org.codehaus.groovy.grails.context.support.PluginAwareResourceBundleMessageSource) {
        basenames = "grails-app/i18n/messages"
    }
}

http://graemerocher.blogspot.com/2010/04/reading-i18n-messages-from-database.html

Getting Started with Grails and MySQL database

Is is so much simple.

Create a file suppose named 'db-config.properties' in 'conf' folder with the following contents:
dataSource.driverClassName = com.mysql.jdbc.Driver
dataSource.username = root
dataSource.password =
dataSource.url = jdbc:mysql://localhost/license?useUnicode=yes&characterEncoding=UTF-8
dataSource.dbCreate = update
dataSource.logSql = true
dataSource.insertDummy = true
dataSource.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

/* Your custom class to do some extra work if need */
dataSource.configClass = src.gorm.GormConfiguration;


Where root is your database username.
Where 'license' is your database name.

And make a entry in 'Config.groovy' file under 'conf' folder as following:
grails.config.locations = [ "classpath:db-config.properties" ]

And you need to must do one thing is to download a  java-mysql connector and put it inside lib folder of your project.
Sometimes you need to clean your project to adjust these changes.

You can use more config file like this as:

grails.config.locations =
[
    "classpath:db-config.properties",
    "classpath:some-config.properties" ,
    "file:${userHome}/.grails/${appName}-config.propertis"
]

Php create a singletone mysql database connection class

<?php

class Database {

    private static $db;
    private $connection;

    private function __construct() {
        $this->connection = new MySQLi("localhost", "my_user", "my_password", "my_db", "3306");
    }

    function __destruct() {
        $this->connection->close();
    }

    public static function getConnection() {
        if ($db == null) {
            $db = new Database();
        }
        return $db->connection;
    }
}

?>

Then just use $db = Database::getConnection(); wherever I need it.

CakePHP: Accessing database.php values

$fields = get_class_vars('DATABASE_CONFIG')

print_r($fields['default']);

or you can use;    App::Import('ConnectionManager'); $ds = ConnectionManager::getDataSource('default'); $dsc = $ds->config; print_r($dsc); Will output like this: Array ( [persistent] => [host] => localhost [login] => root [password] => [database] => db_name [port] => 3306 [driver] => mysql [prefix] => )