Grails 3 and Microsoft SQL Server: Building CRUD RESTful API

by Didin J. on Sep 22, 2018 Grails 3 and Microsoft SQL Server: Building CRUD RESTful API

A comprehensive step by step tutorial on build RESTful API Web Service using Grails 3 and Microsoft SQL Server

A comprehensive step by step tutorial on builds RESTful 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 exists today. 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:

* Grails 3 (We are using 3.3.5)
* JDK 8
* jDTS Driver
* Microsoft SQL Server Express (We are using 2012 version)
* Terminal or Command Line
* Text Editor or IDE

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.


1. 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.

Grails 3 and Microsoft SQL Server: Building CRUD RESTful API - RESTful Page


2. 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`.


3. Create a 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.


4. Create a 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 Board domain class that contains this codes.

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.


5. Run and Test RESTful API CRUD Application

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

stop-app
run-app

To get all board 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 board to RESTful 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 board data, type this command.

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

Then a single board 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.

Thanks!

The following resources might be useful for you:

Loading…