Grails/Hibernate Criteria – Using Switch Case in Order by Clause

In one of our recent project we came across a scenario where we need to sort the results based on two columns using a switch case. I have the following grails domains.

class Contact {
    String firstName
    String lastName
}

class Company {
    String name
}

class Customer {
    Company company
    Contact contact
}

class Order {
    Customer customer
}

Now we have a usecase where we need sort Orders by customer name, but customer can either be a Contact or a Company. That means name is firstName + lastName if the customer is of type Contact and use name column of Company if the Customer is of type Company. So we need to use some sort of switch case statement in order by clause. After spending time on Google, found that Hibernate does not support switch case statement in order by clause. So we decided to write a custom order class by extending the Hibernate Order class, overwritten the toSqlString method to return a switch case sql statement. Here is how it looks. Note: This solution is written very specific to SQL Server, we may need to change the SQL accordingly based on the database used.

import org.hibernate.criterion.Order;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;

public class CustomerOrder extends Order {

    private String companyAlias
    private String userAlias
    private boolean ascending

    protected CustomerOrder(String userAlias, String companyAlias, boolean ascending) {
        super("", true);
        this.companyAlias = companyAlias
        this.userAlias = userAlias
        this.ascending = ascending
    }

    public String toString() {
        return companyAlias  + "-" + userAlias;
    }

    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        String[] firstName = criteriaQuery.getColumnsUsingProjection(
            criteria, "${userAlias}.firstName");
        String[] lastName = criteriaQuery.getColumnsUsingProjection(
            criteria, "${userAlias}.lastName");

        String[] companyId = criteriaQuery.getColumnsUsingProjection(
            criteria, "${companyAlias}.id");
        String[] companyName = criteriaQuery.getColumnsUsingProjection(
            criteria, "${companyAlias}.name");

        """
            CASE WHEN ${companyId[0]} IS NULL
                THEN LOWER(RTRIM(LTRIM(${lastName[0]} + ', ' + ${firstName[0]})))
                ELSE LOWER(RTRIM(LTRIM(${companyName[0]})))
            END ${ascending ? "asc" : "desc" }
        """
    }

    public static Order by(String userAlias, String companyAlias, String direction) {
        boolean ascending = (!direction || direction.equalsIgnoreCase('asc'));
        return new CustomerOrder(userAlias, companyAlias, ascending);
    }
}

And this is how the custom order by class can be used.

def searchCriteria = Order.createCriteria()
def instances = searchCriteria.list(criteria) {

    createAlias('customer', 'cust', CriteriaSpecification.INNER_JOIN)
    createAlias('cust.company', 'cmp', CriteriaSpecification.LEFT_JOIN)
    createAlias('cust.user', 'user', CriteriaSpecification.LEFT_JOIN)

     if(sortCol) {
        if(sortCol == 'customer') {
            order(EntityAssetOrder.by('cust', 'cmp', direction))
        }
        else {
            order(sortCol, direction)
        }
    }
}

ExtJS – Form Warnings

ExtJS has pretty nice support for displaying form errors. But there is no easy way to display warnings. This functionality can be achieved by overwriting couple of ExtJS classes.

  • Override the Ext.form.field.Text to add a method to toggle the warning css classes.
  • Override the validateValue method of Ext.form.field.Base to also take care of executing warning related validations.

Here is the Sencha Fiddle with all the source code.

Dynamic Java Source Code Compiler and Executor

Recently a person that i know is working on a college Project, Online Examination. He needs an ability where the user the can write and execute java code from an Online Exam. And he requested me to help out in writing the compiler. Here is what i came up for him, this is very light weight version of compiler which do not take care of security and stuff. But this might act as starting point for guys who are trying the same thing.

package com.javachap.dyncompiler;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintStream;
import java.io.Writer;
import java.lang.reflect.Method;
import java.net.URL;
import java.net.URLClassLoader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import javax.tools.Diagnostic;
import javax.tools.DiagnosticCollector;
import javax.tools.JavaCompiler;
import javax.tools.JavaFileObject;
import javax.tools.StandardJavaFileManager;
import javax.tools.StandardLocation;
import javax.tools.ToolProvider;

/**
 * DynamicCompiler Compiles the Source Code and Executes it.
 * 
 * @author JavaChap
 */

public class DynamicCompiler {
    
    /**
     * Creates Source and Class files in the Directory.
     */
    private static final String WORKING_DIRECTORY = 
    		System.getProperty("java.io.tmpdir") + File.separator + "dynamic-compiler";
    
    /**
     * Java Source Code
     */
    private String source;
    
    /**
     * Folder where Source Files will be Created
     */
    private File sourceFolder;
    
    /**
     * Folder where Class files will be Generated.
     */
    private File distFolder;
    
    /**
     * Constructor
     * @param source
     */
    public DynamicCompiler(String source) {
        this.source = source;
    }
    
    /**
     * Creates a Main.java file in Source Folder 
     * @return Created Main.java File.
     * @throws IOException
     */
    private File createSourceFile() throws IOException {
        long now = new Date().getTime();
        sourceFolder = new File(WORKING_DIRECTORY + File.separator + String.valueOf(now));
        sourceFolder.mkdirs();
        
        distFolder = new File(sourceFolder + File.separator + "classes");
        distFolder.mkdirs();

        File srcFile = new File(sourceFolder, "Main.java");
    
        Writer writer = null;
        try {
            writer = new FileWriter(srcFile);
            writer.write(this.source);
            writer.flush();
        } 
        finally {
            try {
                writer.close();
            } 
            catch (Exception e) {
            }
        }
        System.out.println("Java Source Created: " + srcFile.getPath());
        return srcFile;
    }
    
    /**
     * Compiles the Java File
     * 
     * @return Compilation Result
     * @throws IOException
     */
    public CompileResult compile() throws IOException {
        if(this.source == null) {
            throw new IllegalArgumentException("Source Code is Null");
        }
        
        // create the source file in filesystem.
        File srcFile = createSourceFile();
        
        DiagnosticCollector diagnostics = new DiagnosticCollector();
        JavaCompiler compiler = ToolProvider.getSystemJavaCompiler();
        StandardJavaFileManager fileManager = compiler.getStandardFileManager(diagnostics, null, null);

        List optionList = new ArrayList();
        fileManager.setLocation(StandardLocation.CLASS_OUTPUT, Arrays.asList(distFolder));
         
        Iterable compilationUnit = fileManager
                .getJavaFileObjectsFromFiles(Arrays.asList(srcFile));
        JavaCompiler.CompilationTask task = compiler.getTask(null, fileManager, diagnostics, optionList, null,
                compilationUnit);
        
        boolean success = task.call();
        
        CompileResult compileResult = new CompileResult();
        compileResult.setSuccess(success);
        
        if(!success) {
            for (Diagnostic diagnostic : diagnostics.getDiagnostics()) {
                String message = String.format("line:%d error:%s", diagnostic.getLineNumber(),
                        diagnostic.getMessage(Locale.getDefault()));
                compileResult.addMessage(message);
            }
        }
        
        // close the file manager;
        fileManager.close();
        
        return compileResult;
    }
    
    /**
     * Executes the Compiled Classes.
     * @return Execution Result
     */
    public ExecutionResult execute() throws Exception {
        
        ExecutionResult result = new ExecutionResult();

        URLClassLoader classLoader = new URLClassLoader(
                    new URL[] {distFolder.toURI().toURL() });
        
        File[] directoryListing = distFolder.listFiles();
        if (directoryListing != null) {
            Method mainMethod = null;
            for (File child : directoryListing) {
                String className = child.getName().replaceAll(".class", "");
                Class clazz = classLoader.loadClass(className);
                mainMethod = clazz.getMethod("main", String[].class);
                if(mainMethod != null) {
                    break;
                }
            }
            
            if(mainMethod != null) {
                ByteArrayOutputStream out = new ByteArrayOutputStream();
                ByteArrayOutputStream err = new ByteArrayOutputStream();
                
                // cache the current system streams. 
                PrintStream origOut = System.out;
                PrintStream origErr = System.err;
                
                System.setOut(new PrintStream(out));
                System.setErr(new PrintStream(err));
                
                try {
                    String[] params = null;
                    mainMethod.invoke(null, (Object) params);
                }
                finally {
                    System.setOut(origOut);
                    System.setErr(origErr);
                }
                
                result.setOutput( out.toString("UTF-8"));
                result.setError(err.toString("UTF-8"));
                
            }
        }
        
        classLoader.close();
        
        return result;
    }

    
    /**
     * Main Method for Testing.
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception{
    	
    	System.out.println(DynamicCompiler.class.getName());
        StringBuilder sourceCode = new StringBuilder(64);
        sourceCode.append("public class Main {\n");
        sourceCode.append("    public static void main(String[] args) {\n");
        sourceCode.append("        System.out.println(\"Hello world\" + name);\n");
        sourceCode.append("    }\n");
        sourceCode.append("}\n");
        
        DynamicCompiler javaSource = new DynamicCompiler(sourceCode.toString());
        CompileResult compileResult = javaSource.compile();
        System.out.println("Compile Success: " + compileResult.isSuccess());
        if(compileResult.isSuccess()) {
            ExecutionResult executionResult = javaSource.execute();
            System.out.println("Output: " + executionResult.getOutput());
            if(executionResult.hasError()) {
                System.out.println("Error: " + executionResult.getError());
            }
        }
        else {
            System.out.println("Compilation Errors:\n" + compileResult.getMessages());
        }
    }
    

    /**
     *  Simple Class to Hold the Compilation Result
     */
    class CompileResult {
    
        public boolean success;
        
        public List messages = new ArrayList();
    
        public boolean isSuccess() {
            return success;
        }
    
        public void setSuccess(boolean success) {
            this.success = success;
        }
    
        public List getMessages() {
            return messages;
        }
    
        public void setMessages(List messages) {
            this.messages = messages;
        }
        
        public void addMessage(String message) {
            this.messages.add(message);
        }
    }
    
    /**
     * Simple Class to Hold the Execution Result
     */
    class ExecutionResult {
        
        public String output;
        public String error;
        
        public String getOutput() {
            return output;
        }
        
        public void setOutput(String output) {
            this.output = output;
        }
        
        public String getError() {
            return error;
        }
        
        public void setError(String error) {
            if(error != null && error.length() > 0) {
                this.error = error;
            }
        }
        
        public boolean hasError() {
            return this.error != null && this.error.length() > 0;
        }
    }
}

ExtJS Fix for Selection model returning wrong record when Grouping feature is enabled

The selection model of grid returns random record using getSelection() method when grouping feature is enabled, In order to fix this issue i added the following code in app.js.

/**
 * Selection Model returns wrong record when grouping feature is enabled
 */
Ext.define('Ext.overrides.view.Table', {
	override: 'Ext.view.Table',
	getRecord: function(node) {
		node = this.getNode(node);
		if (node) {
			return this.dataSource.data.get(node.getAttribute('data-recordId'));
		}
	},
	indexInStore: function(node) {
		node = this.getNode(node, true);
		if (!node && node !== 0) {
			return -1;
		}
		return this.dataSource.indexOf(this.getRecord(node));
	}
});

How to Alias a Host Name on Mac OS X

I Recently moved from Windows to Mac. It wasn’t easy. While i’m still getting used to Mac, I decided to document certain things so that it might be useful for other Mac newbies.

In this blog i will explain how to alias a host name on Mac.

All host aliases are configured in a file /etc/hosts. So lets open that file.

  sudo nano /etc/hosts  

Now lets add a new host alias

cvs.javachap.com   192.168.0.100

Save the file by pressing Ctrl + O (^O)

To make sure host is configured properly, we can issue a ping command to the newly configured host.

ping cvs.javachap.com

Just incase if doesn’t work, we can flush the cache using dscacheutil

sudo dscacheutil -flushcache

Voila, there you go, you have a new host alias configured.

How I Fixed Battery Life On my Mac Book Pro with Retina Display

After upgrading to Mountain Lion, Battery life of my Mac Book Pro with Retina Display has gone worst. Max that i’m getting is around 4 hours. So today i have decided to fix it. According to the Apple Support Forums we can do fix few things that we can do on software side to fix the battery life.

Delete Corrupted Settings File

Many people have commented that this method has worked and this is a very method and doesn’t need a reboot either. Type in the following commands in your terminal.

cd ~/Library/Preferences/
rm com.apple.desktop.plist
killall Dock

Clear PRAM/NVRAM

  • Shut down your Mac.
  • Locate the following keys on the keyboard: Command (⌘), Option, P, and R. You will need to hold these keys down simultaneously in step 4.
  • Turn on the computer.
  • Press and hold the Command-Option-P-R keys before the gray screen appears.
  • Hold the keys down until the computer restarts and you hear the startup sound for the second time.
  • Release the keys.

Reset SMC

  • Shut down the computer.
  • Plug in the MagSafe power adapter to a power source, connecting it to the Mac if its not already connected.
  • On the built-in keyboard, press the (left side) Shift-Control-Option keys and the power button at the same time.
  • Release all the keys and the power button at the same time.
  • Press the power button to turn on the computer.  
    Note
    : The LED on the MagSafe power adapter may change states or temporarily turn off when you reset the SMC.

I did all the above and now i get 8 hours of battery.

Script to Backup MySQL and CVS to Amazon S3

Here is a simple script that i wrote to backup mysql and cvs root folder to Amazon S3. For the script to work you need to have S3Sync to be in path.

#!/bin/bash

# Database Configurations
BUCKET=backup.javachap.com
DB_USER=backup
DB_PWD=readonly
NOW=$(date +_%m_%d_%y)

S3_BACKUP=/opt/s3backup
CVSROOT=/usr/local/cvsroot
CVS_BACKUP_NAME=jc_cvs_backup$NOW.tar.gz
MYSQL_BACKUP_NAME=jc_mysql_backup$NOW.sql.gz

cd $S3_BACKUP

# tar the cvsroot folder
tar czf $CVS_BACKUP_NAME $CVSROOT

# list MySQL databases and dump each
DB_LIST=`mysql -u $DB_USER -p"$DB_PWD" -e'show databases;'`
DB_LIST=${DB_LIST##Database}
for DB in $DB_LIST;
do
  FILENAME=mysql_${DB}${NOW}.sql.gz
  mysqldump -u $DB_USER -p$DB_PWD --opt --flush-logs $DB | gzip > $FILENAME
done

tar czvf $MYSQL_BACKUP_NAME mysql*
rm -f mysql*

cd /opt/s3sync
ruby s3sync.rb -r -v -s --exclude="archieves$|s3backup.sh" /opt/s3backup/ $BUCKET:

mv /opt/s3backup/*.gz /opt/s3backup/archieves/

The 5 Biggest Ways to Boost MySQL Scalability

Sean Hall with some really good MySQL advice:

  1. Tune those queries. Biggest bang for your buck. Enable the slow query log and watch it. Once you’ve found a heavy resource intensive query, optimize it! Tune what receives real-world traffic.
  2. Employ Master-Master Replication. Immediately have a read-only slave for your application to hit as well.
  3. Use Your Memory. Set innodb_buffer_pool_size, key_buffer_size and other key options.
  4. RAID Your Disk I/O. Use RAID 10 mirroring and striping. On EC2 striping across a number of EBS volumes using the Linux md software raid.
  5. Tune Key Parameters. speeds up inserts & updates with innodb_flush_log_at_trx_commit=2; create a tablespace and underlying datafile for each table with innodb_file_per_table.

Full details at the original article.