Home >  Programming Blog >  Groovy and Grails

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 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:

  1. Grails 3 (We are using 3.3.5)
  2. JDK 8
  3. jDTS Driver
  4. Microsoft SQL Server Express (We are using 2012 version)
  5. Terminal or Command Line
  6. 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.


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


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!

Related Articles

Programming Blog
JavaJavascriptHTML 5 TutorialCSS 3MongoDBIonic FrameworkGroovy and GrailsNode.jsReact NativeASP.NET CoreAngularVue.jsReact.jsFlutter TutorialMiscellaneousRust
All Articles
Zizo Wireless