You will likely encounter a headache problem when bringing a large project to cloud-based deployment development, especially testing the access of databases if you have to request of accessing internally managed database servers and set up HTTP proxy like we have been faced with. In our case, we need to test database permissions and rules which the DB team has opened for our project works or not. This sample project aims to test two approaches of the testing database connection. The first approach is to use Hibernate native SQL queries via DataSource while the second one will play with the plain JDBC.

Disclaimer: This project is built on Grails 2.5.6. I am not a hundred per cent sure it will still work in other versions of Grails.

Using Hibernate Native SQL Query

Explanation

Please take a look at ArticleService where we leverage the core interface SessionFactory of org.hibernate.SessionFactory to create SQL queries and extract the results. I have stolen the idea of Hubert Klein Ikkink in his post on DZone forum. In this post, Hubbert claimed that we have to use native SQL queries in some cases without having other choices, for examples executing stored procedures. As the heading suggests, we must define database properties intoDataSource.groovy in grails-app/conf. The shared post explains how to use the session factory.

Code Snippet

Let’s have a look at the recipe:

String sql = "some update SQL"
Session session = sessionFactory.openSession()
Connection c = session.connection()
try {
    try {
        Statement s = c.createStatement()
        s.executeUpdate sql
    }
    catch (all e) {
        log.warn "Error executing statement $sql ($e)"
    }
}
finally {
    session.close()
    c.close()
}

Applying this approach, the ArticleService is built like the following snippet.

import grails.transaction.Transactional
import org.apache.commons.logging.LogFactory

@Transactional
class ArticleService {
    private static final logger = LogFactory.getLog(ArticleService.class)
    // Auto inject SessionFactory we can use
    // to get the current Hibernate session.
    def sessionFactory

    List getArticles() {

        // Get the current Hibernate session.
        final session = sessionFactory.currentSession

        final String query = 'select id, title, year from article order by title desc'

        // Create native SQL query.
        final sqlQuery = session.createSQLQuery(query)

        // Use Groovy with() method to invoke multiple methods
        // on the sqlQuery object.
        final results = sqlQuery.with {
            // Get all results.
            list()
        }
        results
    }
}

Using Plain JDBC

Explanation

Please have a go at the service  NewsService where we define Connection and Statement object, then call the executeQuery method to obtain ResultSet. Iterate on this result set to extract data we are needing. You’re entirely free to take a look at the references I have learned about this approach such as this post on Grails Asia, Using try-with-resources with JDBC objects or Lesson: JDBC Basics.

Code Snippet

import grails.transaction.Transactional
import org.slf4j.Logger
import org.slf4j.LoggerFactory

import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement

@Transactional
class NewsService {
    private static Logger logger = LoggerFactory.getLogger(NewsService.class)

    def getAllNews() {
        Connection conn
        List news = new ArrayList()
        Statement stmt
        try {
            Class.forName("com.mysql.jdbc.Driver")
            String url = "jdbc:mysql://host.docker.internal:3306/test"
            String userDb = "dbuser"
            String pwdDb = "dbs3cr3t"
            Properties properties = new Properties()
            properties.put("user", userDb)
            properties.put("password", pwdDb)
            conn = DriverManager.getConnection(url, properties)
            stmt = conn.createStatement()
            String sql = "SELECT * FROM news"
            ResultSet result = stmt.executeQuery(sql)
            while (result.next()) {
                news.add result.getString("content")
            }
        } catch (Exception e) {
            logger.error(e.message)
        } finally {
            stmt.close()
            conn.close()
        }
        news
    }
}

Source Code

The entire project is publicly available on bitbucket repository of this blog.

If you are interested in my post and our work in general, please consider to support us by clicking on Buy Me A Coffee link in the box below. We are grateful to have your encouraging contribution.

Have fun with my post!