JSON Format CachedRowSet : Array Of Structs, Array Of Arrays and Struct Of Arrays

RowSets.java

package dao;

import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import javax.json.Json;
import javax.sql.rowset.CachedRowSet;

public class RowSets {
	//////////////////////////////////////////////////////////////////////////////////////
	// PROPERTIES
	//////////////////////////////////////////////////////////////////////////////////////
	
	public static SimpleDateFormat dt_format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
	

	//////////////////////////////////////////////////////////////////////////////////////
	// FORMATTERS
	//////////////////////////////////////////////////////////////////////////////////////
	
	/*
	FORMAT STRUCTURE : Array Of Structs
 	[
 		{ "key":"string", "key":0, "key":null },
 		{ "key":"string", "key":0, "key":null }
 	]
	*/
	public static String json_arrayOfStructs( CachedRowSet rs ){		
		try {
			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			// Get Column Names
			
			ResultSetMetaData	meta	= rs.getMetaData();
			int					col_cnt	= meta.getColumnCount();
			String[]			cols 	= new String[ col_cnt+1 ];
			int					i		= 0;
			
			// Pre-format Columns  "COL_NAME": 
			for( i=1; i <= col_cnt; i++ ) cols[i] = "\"" + meta.getColumnName( i ) + "\":";

			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			 
			StringBuilder	str_bld	= new StringBuilder( "[" );
			Object			val;
			
			while( rs.next() ){
				str_bld.append( "{" );	// Open Struct
				
				// Recreate Struct based on Column Names as the Key Values.
				for( i=1; i <= col_cnt; i++ ){
					if( i != 1 ) str_bld.append( "," );					// Every Key-Value pair must be seperated by a comma
					str_bld.append( cols[ i ] );						// Add Key
					append_data( i, meta, str_bld, rs );
				}
				
				// Close Struct, Add comma for next Array Element
				// NOTE: Must delete last comma at the end of the loop.
				str_bld.append( "}," );
			}
			
			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			// Cleanup
			str_bld.deleteCharAt( str_bld.length() - 1 );	// Remove Last Comma before closing Array
			str_bld.append( "]" );							// Close Array
			return str_bld.toString();
			
		}catch (SQLException e){
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}

		return null;
	}

	
	/*
	FORMAT STRUCTURE : Array Of Arrays
 	{
 		"cols":[ "col1", "col2", "col3" ],
 		"data":[
 			[ 0, null, "string ],
 			[ 0, null, "string ]
 		]
 	}
	*/
	public static String json_arrayOfArrays( CachedRowSet rs ){		
		StringBuilder	str_bld	= new StringBuilder();
		int				i		= 0;
		
		try {
			ResultSetMetaData	meta	= rs.getMetaData();
			int					col_cnt	= meta.getColumnCount();

			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			// Build Column Array
			
			str_bld.append( "{ \"cols\":[" );
			for( i=1; i <= col_cnt; i++ ) {
				if( i != 1 ) str_bld.append( "," );
				str_bld.append( "\"" );
				str_bld.append( meta.getColumnName( i ) );
				str_bld.append( "\"" );
			}
			str_bld.append( "],\"data\":[" );

			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			// Build Array Elements
			
			Object val;
			while( rs.next() ){
				
				str_bld.append( "[" );	// Open a new Array
				
				for( i=1; i <= col_cnt; i++ ){
					if( i != 1 ) str_bld.append( "," ); // Every Key-Value pair must be separated by a comma
					append_data( i, meta, str_bld, rs );
				}
				
				// Close Array, Add comma for next Array Element
				// NOTE: Must delete last comma at the end of the loop.
				str_bld.append( "]," );
			}
			
			
			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			// Cleanup
			str_bld.deleteCharAt( str_bld.length() - 1 );	// Remove Last Comma before closing Array
			str_bld.append( "]}" );	// Close Data Array and Structure

			return str_bld.toString();

		}catch (SQLException e){
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}

		return null;
	}
	
	
	/*
	FORMAT STRUCTURE : Struct Of Arrays
 	{
		"colA" : [ 0, 1, 2 ],
		"colB" : [ null, null, null ],
		"colC" : [ "a", "b", "c" ]
 	}
	*/
	public static String json_structOfArrays( CachedRowSet rs ){		
		int	i = 0;
		
		try {
			ResultSetMetaData	meta	= rs.getMetaData();
			int					col_cnt	= meta.getColumnCount();

			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			// Create Column Buffers
			StringBuilder[] cols = new StringBuilder[ col_cnt + 1 ];

			for( i=1; i <= col_cnt; i++ ) cols[ i ] = new StringBuilder();
		
			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			// Fill Column String Buffers
			StringBuilder	str_bld	= null;
			Object			val;
			
			while( rs.next() ){
				for( i=1; i <= col_cnt; i++ ){
					str_bld = cols[ i ];					// Get Reference to Column String Buffer
					append_data( i, meta, str_bld, rs );
					str_bld.append( "," );					// Must Remove Final comma from all buffers.
				}
			}
			
			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			// Build Final String
			StringBuilder bld = new StringBuilder( "{" );
			
			for( i=1; i <=col_cnt; i++ ){
				if( i != 1 ) bld.append( "," );
				
				// Key
				bld.append( "\"" );
				bld.append( meta.getColumnName( i ) );
				bld.append( "\":[" );
				
				// Value
				str_bld = cols[ i ];
				str_bld.deleteCharAt( str_bld.length() - 1 );
				bld.append( str_bld.toString() );
				bld.append( "]" );
			}
			
			bld.append( "}" );
			
			//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
			// Cleanup
			return bld.toString();

		}catch (SQLException e){
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}

		return null;
	}

	
	//////////////////////////////////////////////////////////////////////////////////////
	// HELPER FUNCTIONS
	//////////////////////////////////////////////////////////////////////////////////////
	
	// Formats Values for JSON by placing from a CacheRowSet into a StringBuilder
	private static void append_data( int i, ResultSetMetaData meta, StringBuilder str_bld, CachedRowSet rs ){
		Object val;
		
		try {
			switch( meta.getColumnType( i ) ) {
				//----------------------------------
				// Custom Data Handers
				case Types.INTEGER :
				case Types.SMALLINT :
				case Types.FLOAT :
					str_bld.append( ((val=rs.getObject(i)) == null)? "null" : val.toString() );
				break;
				
				case Types.TIMESTAMP :
					if( rs.getObject( i ) == null ) str_bld.append( "null" );
					else{
						str_bld.append( "\"" );
						str_bld.append( dt_format.format( rs.getTimestamp( i ) ) );
						str_bld.append( "\"" );
					}
				break;

				//----------------------------------
				// Else treat anything as a string
				// Null is Null, String is JSON Escaped.
				default:
					str_bld.append( ((val=rs.getObject(i)) == null)?
						"null" :
						"\"" + Json.encodePointer( val.toString() ) + "\""
					);
				break;
			}
		}catch (SQLException e) {
			System.out.println( e.getMessage() );
			e.printStackTrace();
		}
	}	
}

XDb.java

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

public class Db {
	  public static String data_source = "java:/comp/env/jdbc/MySQLDS"; 

	  // Execute SQL Query from a DataSource setup in Tomcat
    public static CachedRowSet query( String sql ){
        CachedRowSet	rtn		= null;
        Context	  		ctx		= null;
		    Connection		conn	= null;
		
    	//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        try{
        	// Get Database Connection
        	ctx				    = new InitialContext();
			    DataSource ds	= (DataSource) ctx.lookup( data_source );
			    conn			    = ds.getConnection();	
        	conn.setAutoCommit( false );

        	// Execute SQL Query and Get Results
          Statement		  stmt	  = conn.createStatement();
          ResultSet		  result	= stmt.executeQuery( sql );
          RowSetFactory fact	  = RowSetProvider.newFactory();
            
          rtn = fact.createCachedRowSet();
          rtn.populate( result );
        
        //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        }catch( SQLException ex ){
        	System.out.println( ex.getMessage() );
          ex.printStackTrace();
        }catch( NamingException e ){
        	System.out.println( e.getMessage() );
			    e.printStackTrace();
		    }
        
    	  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        return rtn;
    }
    
    // url = "jdbc:mysql://localhost:3306/db_name";
    // Execute SQL Query straight from a database connection
    public static CachedRowSet query( String sql, String url, String user, String pwd ){
        CachedRowSet	rtn		= null;
		    Connection		conn	= null;
		
    	  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        try{
        	// Get Database Connection
			    conn =  DriverManager.getConnection( url, user, pwd ); 	
        	conn.setAutoCommit( false );

        	// Execute SQL Query and Get Results
          Statement		stmt	= conn.createStatement();
          ResultSet		result	= stmt.executeQuery( sql );
          RowSetFactory	fact	= RowSetProvider.newFactory();
            
          rtn = fact.createCachedRowSet();
          rtn.populate( result );
        
        //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        }catch( SQLException ex ){
        	System.out.println( ex.getMessage() );
          ex.printStackTrace();
		    }
        
    	  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        return rtn;
    }    
}