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.
The following tools, frameworks and modules are required for this tutorial:
* Grails 3 (We are using 3.3.5)
* JDK 8
* 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.
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.
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.SQLServer2012Dialectenvironments: 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`.
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.sqlserverimport grails.databinding.BindingFormatclass 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.
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.sqlserverimport 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.
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-apprun-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 200X-Application-Context: application:developmentContent-Type: application/json;charset=UTF-8Transfer-Encoding: chunkedDate: 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 201X-Application-Context: application:developmentLocation: http://localhost:8080/product/show/1Content-Type: application/json;charset=UTF-8Transfer-Encoding: chunkedDate: 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 200X-Application-Context: application:developmentContent-Type: application/json;charset=UTF-8Transfer-Encoding: chunkedDate: 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 200X-Application-Context: application:developmentLocation: http://localhost:8080/product/show/1Content-Type: application/json;charset=UTF-8Transfer-Encoding: chunkedDate: 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 204X-Application-Context: application:developmentDate: 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!