How to convert result table to JSON Array or JSON Object or JSON String in MySQL

I'd like to convert result table to JSON Array of Object in MySQL using preferably only plain MySQL commands. For example with query SELECT name, phone FROM person; | name | phone | | Jack | 12345 | | John | 23455 | the expected JSON output would be as below as per my requirement: [ { "name": "Jack", "phone": 12345 }, { "name": "John", "phone": 23455 } ]

The maximum value for group_concat_max_len is 18446744073709551615. To set the variable forever use SET GLOBAL group_concat_max_len=4294967295;

Example 1: SELECT i.id, JSON_OBJECT('id', ig.id, "name", ig.name) AS 'group_json' FROM item i LEFT JOIN item_group ig ON i.item_group_id=ig.id ORDER BY i.id DESC LIMIT 10;

Example 2 (Group Concat): SELECT ig.item_sales_rate_id, GROUP_CONCAT(DISTICT JSON_OBJECT('id', ig.id, "status", ig.status)) AS 'group_json' FROM item_sales_rate i LEFT JOIN item_sales_uom ig ON i.id=ig.item_sales_rate_id WHERE ig.item_sales_rate_id IS NOT NULL GROUP BY ig.item_sales_rate_id ORDER BY i.id ASC LIMIT 10;

MySQL casting from decimal to string - mysql cast to varchar - mysql convert decimal to string char varchar

MySQL - casting from decimal to string select CAST(ROUND(345345345345345345345353453453453454.4345345,6) AS CHAR(65)) AS 'big_number'; don't try to cast to varchar, there is a bug at MySQL end

Grails on Groovy - join tables using createAlias with additional condition along with sql on condition for additional filter of data

We frequently use createAlias to create alias between entity. It actually create connection based on foreign key and primary key between two entities. Below is a example how we create alias between entities:

Table1.createCriteria().list { createAlias("scores", "scores") }

Which will produce SQL like below where we can see that foreign key of table1 create a link with table2_child based on primary key id as following:

select ... from table1 this_ inner join table2_child scores1_ on (this_.id=scores1_.table1_id)

But if we need more on add to filter when joining them, yeah, we can do that. We can add extra conditions to on part of that SQL. To do so, first need to create a AliasBuiilder.groovy file like below:

package com.gradle_app import org.hibernate.criterion.Criterion import org.hibernate.sql.JoinType abstract class AliasBuilder { static final Object synk0 = new Object() static Long number = 0L static void fixCriteriaBuilderProps(criteriaBuilder) { if (!criteriaBuilder.properties.containsKey("aliases")) { criteriaBuilder.metaClass.aliases = [:] criteriaBuilder.metaClass.data = [:] } criteriaBuilder = null } static String createAlias0(criteriaBuilder, String field, String aliasPath = field, JoinType joinType = JoinType.INNER_JOIN, Criterion customCriterion = null) { aliasPath = aliasPath ? aliasPath : field joinType = joinType ? joinType : JoinType.INNER_JOIN fixCriteriaBuilderProps(criteriaBuilder) def subCriteria = criteriaBuilder.criteria.subcriteriaList.find { it.path == aliasPath } if(subCriteria) { try { return subCriteria.alias } finally { criteriaBuilder = field = aliasPath = joinType = subCriteria = null } } else { String alias = "${field}_custom_name_${nextSerialNumber()}".toLowerCase() criteriaBuilder.createAlias(aliasPath, alias, joinType, customCriterion) String other = alias.substring(0, criteriaBuilder.sessionFactory.dialect.getMaxAliasLength()) + (criteriaBuilder.properties.aliases.size() + 1) + "_" criteriaBuilder.properties.aliases[alias] = other criteriaBuilder.properties.data[aliasPath] = alias try { return alias } finally { criteriaBuilder = field = aliasPath = joinType = subCriteria = alias = other = null } } } static String nextSerialNumber() { Long serial = null try { synchronized (synk0) { number = serial = number + 1L if (number == Long.MAX_VALUE) { number = 1L } } return serial.toString() } finally { serial } } }

And another CustomCriteria.groovy file like this: package com.gradle_app import org.hibernate.Criteria import org.hibernate.HibernateException import org.hibernate.criterion.CriteriaQuery import org.hibernate.criterion.Criterion import org.hibernate.engine.spi.TypedValue import org.hibernate.internal.util.StringHelper class CustomCriteria implements Criterion { private static final TypedValue[] NO_VALUES = new TypedValue[0] public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException { String alias = criteriaQuery.getSQLAlias(criteria) return "${alias}.status='1'" } public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException { return NO_VALUES } }

And you can use the above custom condition using as below example: void checkCustomCriteria() { List list = Table1.createCriteria().list { //setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP) String scores = AliasBuilder.createAlias0(delegate, "scores", "scores", JoinType.LEFT_OUTER_JOIN, new CustomCriteria()) "projections" { "groupProperty" "id", "id" addProjectionToList(Projections.sqlProjection( "GROUP_CONCAT(${delegate.aliases[scores]}.id) as scores", ["scores"] as String[], [StringType.INSTANCE] as Type[] ), "scores") } } list.each { dataRow-> println("Data row=${dataRow}") } }

Which will generate below SQL with additional condition on statement ( scores1_.status='1' ) select ... from table1 this_ inner join table2_child scores1_ on this_.id=scores1_.table1_id and ( scores1_.score.status='1' ) where this_.id<>?

Laravel Blade @include .html files | Laravel Blade Highlight Change Tags when use AngularJS along with Laravel

I am going to use AngularJS along with Laravel, and I wanted to change Laravel tags to [[ ]], it can achived by below codes: Blade::setContentTags('[[', ']]'); // for variables and all things Blade Blade::setEscapedContentTags('[[[', ']]]'); // for escaped data

But my solution is to have Angular and Blade works very simple, I create a another xxx.php every time instead of xxx.blade.php. I need some Angular and name this partial just '.php' and not '.blade.php'. Lets say I have a user create form named "create.blade.php" inside "views/user" directory using below content: @extends('layouts.user') @section('content') <form> <h2>Create User</h2> @include('user.createBody'); </form> @endsection Now I will create an php file named "createBody.php" inside "views/user" directory and put some html there: <div class="row"> <h1>User Name={{userName}}</h1> <div class="col-lg-4 col-sm-6"> <label>Name</label> <input type="text" class="form-control" ng-model="userName"> </div> <div class="col-lg-4 col-sm-6"> <label>ID</label> <input type="text" class="form-control"> </div> </div> So here we can use {{ }} tags both in Laravel and AngularJS

How to use static content / resource files in app root directory in grails

In Grails latest version like 4 and 5, it provides static assets management mechanism more standard way. We can use different files under the corresponding folders.

All the images will go under the images directory, js files go under the javascripts directory and CSS goes under the stylesheets directory. These files will be accessed by the URL path start with "/assets/".

Now, what if you don't want to use this structure or need to use the root path for some static file content.

For this, grails provide another option to manage static resources. So, what you can do is create the /resources/public directory under /src/main/ and use static resources there.

For example, if you had a file under /src/main/resources/public/images/example.jpg path then you can access that file using https://example.com/static/images/example.jpg.

This means, your file can be accessed with "/static/" URL by default. If you want to access the file in the app root directory, setup the following configuration under application.groovy grails.resources.pattern = '/**'

Now, for the above example the file can be accessed with https://example.com/images/example.jpg URL and if the file is in /public/ directory that file can be accessed with root path https://example.com/file.js

Tracking of Stackoverflow Exception from Grails / Gradle Project - Caused by: java.lang.StackOverflowError: null

In many cases we may face Stackoverflow exception and in my many cases I didn't find the exact point where this exception occurred.

Then I start checked my debug output and found that from UrlMappingUtils.java class in package org.grails.web.mapping is the reason of StackoverflowException rather than throwing the main Exception.

This is because of handling of Exception is not handled properly.

Then I set a breakpoint inside method public static String forwardRequestForUrlMappingInfo and found the original Exception from there.

Add resources and config files to your JAR using Gradle Build for Grails Application

How do I add config files or any other resources into my jar using gradle? How to include everything inside src/main/java to compile I have a project. Inside the project I have few files like x.properties, x.csv and many more under src/main/webapp package, I used gradle to compile, I didn’t see src/main/webapp directory in my build folder. How can I include everything to build? Because I need to include contents inside src/main/webapp directory to be include in my jar so that resources can be accessible by who will use my jar.

So first step is to tell compiler to include files in jar file using below code snippet - Add below code snippet to build.gradle: sourceSets { main { resources { srcDir "src/main/webapp" include "*.properties" include "*.csv" exclude "*.png" exclude "a1" exclude "a2" srcDir "src/main/webapp/a1" exclude "**/*.text" include "**/*.csv" srcDir "src/main/webapp/a2" include "**/*.html" } } }

My project structure is like below:

And the files stored in the generated jar file as below:

Now the process to use the resources file into the project, add below configuration to build.gradle file (which project will use jar created above): copy{ from(zipTree("path_to_jar_file/plain.jar")) into("./") include "src/main/webapp/**/*.*" [will extract all to root directory] include "src/main/webapp/kkk/*.*" [will extract all files in folder named kkk to root directory] }

Above configuration will extract all file from (jar) "src/main/webapp" to projectRoot/src/main/webapp directory as below: