Spring’s JDBC Template Set Up for a Grail Application

To take an advantage of the Spring JDBC Template for any Grail application, the first thing you need to do, is to define the DataSource bean in resources.groovy , Don’t forget to have the jdbc driver(.jar) corresponding to your backend database server under the lib folder of your grail application.

For example ,resources.groovy is shown as below,

import org.springframework.jdbc.core.JdbcTemplate
import org.apache.commons.dbcp.BasicDataSource




beans = {
myDataSource(BasicDataSource) {
driverClassName = "oracle.jdbc.OracleDriver"
url ="jdbc:oracle:thin:@ . . . . . “
username = "myUser"
password = "myPass"
}
jdbcTemplate(JdbcTemplate)
{
dataSource=myDataSource
}

}



If you have used MS SQL server as your back end database, the driver class name and the url format is as follow.

myDataSource(BasicDataSource) {
driverClassName = "com.microsoft.jdbc.sqlserver.SQLServerDriver"
url = "jdbc:microsoft:sqlserver://myDataBaseServerURL"
}

Similarly for MYSQL database server, the driver class name and the url format could be as follows :

dataSource {
driverClassName = "com.mysql.jdbc.Driver"
url = "jdbc:mysql:// myDataBaseServerURL "
username = "myUser"
password = "myPass"
}

Once its set up you can simply make a query as follows
myResult = jdbcTemplate.queryForList (myQuery)

where myQuery is the query that needs to be execute on the remoteDatabase.