A comprehensive step by step tutorial on builds REST API Web Service using Grails 3 and MS (Microsoft) SQL Server. In this tutorial, we are using jTDS which is one of the free JDBC-ODBC drivers that exist today. jTDS is an open-source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000, 2005, 2008 and 2012) and Sybase Adaptive Server Enterprise (10, 11, 12 and 15). jTDS is based on FreeTDS and is currently the fastest production-ready JDBC driver for SQL Server and Sybase ASE. And of course, in Grails 3 side we will use Grails Rest-API profile.

Table of Contents:

The following tools, frameworks, and modules are required for this tutorial:

Before starting to the main steps, make sure you have installed JDK 8, Grails 3 Framework and Microsoft SQL Server 2012. We are running MS SQL Server 2012 on the different machine which TCP/IP connection is enabled.



Create a new Grails 3 Application with REST-API Profile

As usual, we always start the steps of the tutorial from zero. Open the terminal or command line then go to your Grails project folder. Type this command to create a new Grails 3 application.

grails create-app grails-rest-sqlserver --profile=rest-api --features=hibernate5

That command will create a Grails 3.3.2 application with REST-API profile. Next, go to the newly created application folder.

cd grails-rest-sqlserver

Next, enter the Grails interactive console by type this command.

grails

For sanitation, run the application for the first time by type this command in the Grails interactive console.

run-app

And you will see JSON response when you open the `localhost:8080` from the browser.



Install and Configure jDTS Library for MS SQL Server

To install jDTS library, simply open and edit `build.gradle` then add this line inside dependencies block.

runtime 'net.sourceforge.jtds:jtds:1.3.1'

Then comment out this line.

// runtime "com.h2database:h2"

Next, open and edit `grails-app/conf/application.yml` then replace datasource and environments lines with this.

dataSource: pooled: true jmxExport: true driverClassName: net.sourceforge.jtds.jdbc.Driver username: "sa" password: "q" dialect: org.hibernate.dialect.SQLServer2012Dialect environments: development: dataSource: dbCreate: update url: jdbc:jtds:sqlserver://192.168.0.5:1433;DatabaseName=GrailsRest test: dataSource: dbCreate: update url: jdbc:jtds:sqlserver://192.168.0.5:1433;DatabaseName=GrailsRestTest production: dataSource: dbCreate: none url: jdbc:jtds:sqlserver://192.168.0.5:1433;DatabaseName=GrailsRest properties: jmxEnabled: true initialSize: 5 maxActive: 50 minIdle: 5 maxIdle: 25 maxWait: 10000 maxAge: 600000 timeBetweenEvictionRunsMillis: 5000 minEvictableIdleTimeMillis: 60000 validationQuery: SELECT 1 validationQueryTimeout: 3 validationInterval: 15000 testOnBorrow: true testWhileIdle: true testOnReturn: false jdbcInterceptors: ConnectionState defaultTransactionIsolation: 2 # TRANSACTION_READ_COMMITTED

Next, create the new Database in MS SQL Server Management Studio with the name `GrailsRest` and `GrailsRestTest` and set the owner of the Database to `sa`.



Create a Grails Domain Class

We will use MS SQL Server as datastore only, so it's not necessary to create the stored procedure or anything in MS SQL Server side. All MS SQL Server table structure will be created just from Domain class. To create a domain class, type this command from the Grails 3 interactive console.

create-domain-class grails.rest.sqlserver.Product

That command will create a Product domain class inside the package `grails.rest.sqlserver`. Next, open and edit `grails-app/domain/grails/rest/sqlserver/Product.groovy` then replace all codes with this.

package grails.rest.sqlserver import grails.databinding.BindingFormat class Product { String sku String prodName String prodDesc Double prodCost Double prodPrice @BindingFormat('dd/MM/yy HH:mm') Date prodCreated = new Date() @BindingFormat('dd/MM/yy HH:mm') Date prodModified static constraints = { sku maxSize: 8 prodName maxSize: 50 prodDesc maxSize: 255 prodModified nullable: true } }

As you can see above, we will receive event date fields as a string with the format "dd/MM/yy HH:mm" and it will binding as Groovy Date using `@BindingFormat` annotation.



Create a Grails RESTful Controller

This time for creating a RESTful controller for CRUD function. In the Grails interactive console type this command.

create-restful-controller grails.rest.sqlserver.Product

That command will create a RESTful controller of the Product domain class that contains these codes of a simple ProductController constructor and declaration of response format for REST API.

package grails.rest.sqlserver import grails.rest.* import grails.converters.* class ProductController extends RestfulController { static responseFormats = ['json', 'xml'] ProductController() { super(Product) } }

This means the controller is ready for use as create-read-update-delete (CRUD) function.



Run and Test Grails MSSQL Server REST API CRUD Application

We will test the REST API using `CURL` from the terminal or command line. Stop and run the Grails 3 application using this command from the Grails 3 interactive console.

stop-app run-app

To get all product list data, type this command in the other terminal tab or command line.

curl -i -H "Accept: application/json" localhost:8080/product

That command will return a response like this.

HTTP/1.1 200 X-Application-Context: application:development Content-Type: application/json;charset=UTF-8 Transfer-Encoding: chunked Date: Fri, 21 Sep 2018 23:38:26 GMT []

To post a product to REST API, type this command.

curl -i -X POST -H "Content-Type: application/json" -d '{"sku":"12345678","prodName":"Macbook Pro Retina MF221","prodDesc":"Macbook Pro Retina MF221, Intel Core i7 3.3GHz, Intel HD 5000 2GB, DDR5 SDRAM 16GB, SSD 1TB","prodCost":999,"prodPrice":1677}' localhost:8080/product

You will see the response below if that command runs successfully.

HTTP/1.1 201 X-Application-Context: application:development Location: http://localhost:8080/product/show/1 Content-Type: application/json;charset=UTF-8 Transfer-Encoding: chunked Date: Fri, 21 Sep 2018 23:48:40 GMT {"id":1,"prodCost":999.0,"prodDesc":"Macbook Pro Retina MF221, Intel Core i7 3.3GHz, Intel HD 5000 2GB, DDR5 SDRAM 16GB, SSD 1TB","prodName":"Macbook Pro Retina MF221","prodCreated":"2018-09-21T23:48:40Z","prodPrice":1677.0,"sku":"12345678"}

To get a single product data, type this command.

curl -i -H "Accept: application/json" localhost:8080/product/1

Then a single product data will be displayed in the terminal.

HTTP/1.1 200 X-Application-Context: application:development Content-Type: application/json;charset=UTF-8 Transfer-Encoding: chunked Date: Fri, 21 Sep 2018 23:55:27 GMT {"id":1,"prodCost":999.0,"prodDesc":"Macbook Pro Retina MF221, Intel Core i7 3.3GHz, Intel HD 5000 2GB, DDR5 SDRAM 16GB, SSD 1TB","prodName":"Macbook Pro Retina MF221","prodCreated":"2018-09-21T23:48:40Z","prodPrice":1677.0,"sku":"12345678"}

To update current or specific data by ID type this command for example just edit a price.

curl -i -X PUT -H "Content-Type: application/json" -d '{"prodPrice":2222}' localhost:8080/product/1

You will this response.

HTTP/1.1 200 X-Application-Context: application:development Location: http://localhost:8080/product/show/1 Content-Type: application/json;charset=UTF-8 Transfer-Encoding: chunked Date: Sat, 22 Sep 2018 00:01:13 GMT {"id":1,"prodCost":999.0,"prodDesc":"Macbook Pro Retina MF221, Intel Core i7 3.3GHz, Intel HD 5000 2GB, DDR5 SDRAM 16GB, SSD 1TB","prodName":"Macbook Pro Retina MF221","prodCreated":"2018-09-21T23:48:40Z","prodPrice":2222.0,"sku":"12345678"}

To delete a data by ID type this command.

curl -i -X DELETE localhost:8080/product/1

If data deleted successfully, you will see this response.

HTTP/1.1 204 X-Application-Context: application:development Date: Sat, 22 Sep 2018 00:02:21 GMT

That's it, the easy way to create a RESTful API using Grails 3, Microsoft SQL Server and REST-API Profile.

You can find the working source code on our GitHub.

That just the basic. If you need more deep learning about Groovy and Grails you can take the following cheap course:

Thanks!