Groovy web console

subscribe to the feed Subscribe
to this
site

TailorJsonExtractData

Published 3 weeks ago by CamiloG
Actions Execute script  ▶ Edit in console Back to console Show/hide line numbers View recent scripts
package com.tailorsoft;

import groovy.json.JsonSlurper
import java.sql.ResultSetMetaData
import java.text.DecimalFormat
import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import groovy.transform.ToString

/**
 * Created by CAMILO GONZALEZ
 */
class TailorGroovyJson {
	static void main(String [] args) {
  //Connect to database
  def url = 'jdbc:hsqldb:mem:tailorDB'
  def user = 'sa'
  def password = ''
  def driver = 'org.hsqldb.jdbcDriver'
  def sql = Sql.newInstance(url, user, password, driver)

  // Create table PRODUCT
  try {
   def createTableProductDetail = '''CREATE TABLE PRODUCT(
       product         VARCHAR(20),
       orders       VARCHAR(5), 
       total      VARCHAR(10));'''

   sql.execute(createTableProductDetail)

   //Extract data from json
   def url_Json = new URL("https://www.tailorsoft.co/sample.json")
   def connection = url_Json.openConnection()
   connection.requestMethod = 'GET'

   //Validate connection
   if(connection.responseCode == 200) {
    //Extract data from Json url to List
    def jsonList = new JsonSlurper().parse(url_Json)
    def productDetailMap = jsonList.products
    def ordersMap = jsonList.orders

    //Define variables for maping orders and items
    def productId
    def quantity
    //Define decimal Format for total
    def df = new DecimalFormat("#0.00")

    productDetailMap.each {it.numOrders = 0;
     it.total = 0; }

    //Iterate orders and items to assign quantity for orders
    ordersMap.each { order ->
     order.items.each { items ->
      productId = items.productId
      quantity = items.quantity
      productDetailMap.find{it.id.equals(productId)}.numOrders += quantity
     }
    }
    productDetailMap.each{it.total = df.format(Double.parseDouble(it.price) * it.numOrders)}

    //Insert data to table PRODUCT
    productDetailMap.each { product ->
     def productMap = [product:product.name, orders:product.numOrders, total:product.total]
     def ids  = sql.executeInsert("""
                insert into PRODUCT
                values (:product, :orders, :total)
                """, productMap)
    }

    //Table Display
    println '------------------------------------------------'

    def metaClosure = { ResultSetMetaData meta ->
     (1..meta.columnCount).each { index ->
      print meta.getColumnLabel(index).padRight(20)
     }
     println()
    }

    sql.eachRow("select * from PRODUCT", metaClosure) { row ->
     println "${row.product.toString().padRight(20)}${row.orders.padRight(20)}${row.total.padRight(20)}"
    }
    println '------------------------------------------------'

    sql.close()
   }else {
    println 'No successful response when extracting data from URL : ' + url_Json.toString()
   }
  } catch(Exception ex) {
   println("Error->" + ex);
  }
	}
}