Grails, SQL Server

Error in Grails – Cannot load JDBC driver class ‘com.microsoft.sqlserver.jdbc.SQLServerDriver’

If you are configuring a Grails application to connect to SQL Server database, there are chances that you might run into this issue. You can get this error because of many reasons. Here are few things you can try to get rid of the error:

1. As it is clear from the error message, the root cause of the error is SQLServerDriver class is not loaded. That’s because the driver is missing in the class path. In order to add SQLServerDriver to your class path, you need to download sqljdbc4.jar file and place it in your application’s /lib directory.

2. Make sure that you have updated DataSource.groovy file located at grails-app/conf to include the data source information. Please refer to this post to learn how you can include the data source information in your DataSource.groovy file.

3. If you think your driver and data source is properly configured, but you are still getting this error; the following steps might be useful:

    Stop the server using grails stop-app
    Run the grails clean command
    Close the command prompt or terminal window
    Open a new terminal window and start the Grails using grails -Dserver.port=8090 run-app
    Issue should now be resolved, hopefully!
Grails, SQL Server

Configuring Grails application to use SQL Server database

1. Create a new Grails application by typing in the following command in terminal window:

grails create-app grailsDbConn

2. Change to the application directory by typing the following command in terminal window:

cd grailsDbConn

3. Create a new Domain Class by typing in the following command in terminal window:

grails create-domain-class db.Employee

4. Open Employee.groovy file in your favorite IDE and edit it to the following:

package db

class Employee {
    
    String employeeID
    String firstName
    String lastName
    String dateOfBirth

    static constraints = {
    }
}

5. Create a new Controller by typing in the following command in terminal window:

grails create-controller db.Employee

6. Open EmployeeController.groovy in your favorite IDE and edit it to the following:

package db

class EmployeeController {

    def scaffold = Employee
}

7. Open DataSource.groovy file located at grails-app/conf in your favorite IDE to include the data source information

        dataSource {
            dbCreate = "create-drop"
            driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
            url="jdbc:sqlserver://localhost:1433;DatabaseName=[YOUR_DB_NAME]"
            username = "sa"
            password = "[YOUR_PASSWORD]"
        }

The updated DataSource.groovy file will look like the following:

dataSource {
    pooled = true
    driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url="jdbc:sqlserver://localhost:1433;DatabaseName=[YOUR_DATABASE]"
    username = "sa"
    password = "[YOUR_PASSWORD]"
    dbCreate = "create-drop"
    loggingSql = true
}
hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
//    cache.region.factory_class = 'org.hibernate.cache.ehcache.EhCacheRegionFactory' // Hibernate 4
}
 
// environment specific settings
environments {
    development {
        dataSource {
            dbCreate = "create-drop"
            driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
            url="jdbc:sqlserver://localhost:1433;DatabaseName=[YOUR_DATABASE]"
            username = "sa"
            password = "[YOUR_PASSWORD]"
        }
    }
    test {
        dataSource {
            dbCreate = "update"
            url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000"
        }
    }
    production {
        dataSource {
            dbCreate = "update"
            url = "jdbc:h2:prodDb;MVCC=TRUE;LOCK_TIMEOUT=10000"
            properties {
               maxActive = -1
               minEvictableIdleTimeMillis=1800000
               timeBetweenEvictionRunsMillis=1800000
               numTestsPerEvictionRun=3
               testOnBorrow=true
               testWhileIdle=true
               testOnReturn=false
               validationQuery="SELECT 1 from dual"
               jdbcInterceptors="ConnectionState"
            }
        }
    }
}

8. Download sqljdbc4.jar and place that in your application’s /lib directory.

9. Start Grails by typing in grails run-app in terminal window.

Please note that a Grails application will be run with the built in Tomcat server on port 8080 by default. If you may have Tomcat running on port 8080, you may want to specify a different port by using the server.port argument. In this case, run Grails by typing in grails -Dserver.port=8090 run-app in the terminal window.

10. Once the application is loaded successfully, you should have an Employee table in the database. Creating a new Employee using the app should create a new record in the table.

Grails

How to install Grails on Windows?

1. Download the latest Grails release from the Grails Download page.

2. Extract the downloaded zip file into an appropriate location; like C:\grails-2.3.1

3. Create an environment variable GRAILS_HOME that points to the path where you extracted the zip file, like GRAILS_HOME=C:\grails-2.3.1

4. If your JAVA_HOME environment variable is not set, create an environment variable JAVA_HOME that points to your Java installation directory, like JAVA_HOME=C:\Program Files\Java\jdk1.7.0_51

5. Modify PATH environment variable to append a reference to the ‘bin’ directory within the Grails directory, like %GRAILS_HOME%\bin;

6. Verify that the Grails is installed correctly by typing grails -version in the command prompt. You should see the version we just installed as the output.