Speeding up Matlab-JDBC SQL queries

% Load the appropriate JDBC driver class into Matlab's memory
% (but not directly, to bypass JIT pre-processing - we must do it in run-time!)
driver = eval('com.mysql.jdbc.Driver');  % or com.microsoft.sqlserver.jdbc.SQLServerDriver or whatever
% Connect to DB
dbPort = '3306'; % mySQL=3306; SQLServer=1433; Oracle=...
connectionStr = ['jdbc:mysql://' dbURL ':' dbPort '/' schemaName];  % or ['jdbc:sqlserver://' dbURL ':' dbPort ';database=' schemaName ';'] or whatever
dbConnObj = java.sql.DriverManager.getConnection(connectionStr, username, password);
% Send an SQL query statement to the DB and get the ResultSet
stmt = dbConnObj.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
try stmt.setFetchSize(1000); catch, end  % the default fetch size is ridiculously small in many DBs
rs = stmt.executeQuery(sqlQueryStr);
% Get the column names and data-types from the ResultSet's meta-data
MetaData = rs.getMetaData;
numCols = MetaData.getColumnCount;
data = cell(0,numCols);  % initialize
for colIdx = numCols : -1 : 1
    ColumnNames{colIdx} = char(MetaData.getColumnLabel(colIdx));
    ColumnType{colIdx}  = char(MetaData.getColumnClassName(colIdx));  % http://docs.oracle.com/javase/7/docs/api/java/sql/Types.html
end
ColumnType = regexprep(ColumnType,'.*\.','');
% Get the data from the ResultSet into a Matlab cell array
rowIdx = 1;
while rs.next  % loop over all ResultSet rows (records)
    for colIdx = 1 : numCols  % loop over all columns in the row
        switch ColumnType{colIdx}
            case {'Float','Double'}
                data{rowIdx,colIdx} = rs.getDouble(colIdx);
            case {'Long','Integer','Short','BigDecimal'}
                data{rowIdx,colIdx} = double(rs.getDouble(colIdx));
            case 'Boolean'
                data{rowIdx,colIdx} = logical(rs.getBoolean(colIdx));
            otherwise %case {'String','Date','Time','Timestamp'}
                data{rowIdx,colIdx} = char(rs.getString(colIdx));
        end
    end
    rowIdx = rowIdx + 1;
end
% Close the connection and clear resources
try rs.close();   catch, end
try stmt.close(); catch, end
try dbConnObj.closeAllStatements(); catch, end
try dbConnObj.close(); catch, end  % comment this to keep the dbConnObj open and reuse it for subsequent queries

// Copyright (c) Yair Altman UndocumentedMatlab.com
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
public class JDBC_Fetch {
public static int DEFAULT_MAX_ROWS = 100000;   // default cache size = 100K rows (if DB does not support non-forward-only ResultSets)
public static Object[] getData(ResultSet rs) throws SQLException {
try {
if (rs.last()) {  // data is available
int numRows = rs.getRow();    // row # of the last row
rs.beforeFirst();             // get back to the top of the ResultSet
return getData(rs, numRows);  // fetch the data
} else {  // no data in the ResultSet
return null;
}
} catch (Exception e) {
return getData(rs, DEFAULT_MAX_ROWS);
}
}
public static Object[] getData(ResultSet rs, int maxRows) throws SQLException {
// Read column number and types from the ResultSet's meta-data
ResultSetMetaData metaData = rs.getMetaData();
int numCols = metaData.getColumnCount();
int[] colTypes = new int[numCols+1];
int numDoubleCols = 0;
int numBooleanCols = 0;
int numStringCols = 0;
for (int colIdx = 1; colIdx <= numCols; colIdx++) {
int colType = metaData.getColumnType(colIdx);
switch (colType) {
case Types.FLOAT:
case Types.DOUBLE:
case Types.REAL:
colTypes[colIdx] = 1;  // double
numDoubleCols++;
break;
case Types.DECIMAL:
case Types.INTEGER:
case Types.TINYINT:
case Types.SMALLINT:
case Types.BIGINT:
colTypes[colIdx] = 1;  // double
numDoubleCols++;
break;
case Types.BIT:
case Types.BOOLEAN:
colTypes[colIdx] = 2;  // boolean
numBooleanCols++;
break;
default: // 'String','Date','Time','Timestamp',...
colTypes[colIdx] = 3;  // string
numStringCols++;
}
}
// Loop over all ResultSet rows, reading the data into the 2D matrix caches
int rowIdx = 0;
double [][] dataCacheDouble  = new double [numDoubleCols] [maxRows];
boolean[][] dataCacheBoolean = new boolean[numBooleanCols][maxRows];
String [][] dataCacheString  = new String [numStringCols] [maxRows];
while (rs.next() && rowIdx < maxRows) {
int doubleColIdx = 0;
int booleanColIdx = 0;
int stringColIdx = 0;
for (int colIdx = 1; colIdx <= numCols; colIdx++) {
try {
switch (colTypes[colIdx]) {
case 1:  dataCacheDouble[doubleColIdx++][rowIdx]   = rs.getDouble(colIdx);   break;  // numeric
case 2:  dataCacheBoolean[booleanColIdx++][rowIdx] = rs.getBoolean(colIdx);  break;  // boolean
default: dataCacheString[stringColIdx++][rowIdx]   = rs.getString(colIdx);   break;  // string
}
} catch (Exception e) {
System.out.println(e);
System.out.println(" in row #" + rowIdx + ", col #" + colIdx);
}
}
rowIdx++;
}
// Return only the actual data in the ResultSet
int doubleColIdx = 0;
int booleanColIdx = 0;
int stringColIdx = 0;
Object[] data = new Object[numCols];
for (int colIdx = 1; colIdx <= numCols; colIdx++) {
switch (colTypes[colIdx]) {
case 1:   data[colIdx-1] = dataCacheDouble[doubleColIdx++];    break;  // numeric
case 2:   data[colIdx-1] = dataCacheBoolean[booleanColIdx++];  break;  // boolean
default:  data[colIdx-1] = dataCacheString[stringColIdx++];            // string
}
}
return data;
}
}

% Get the data from the ResultSet using the JDBC_Fetch wrapper
data = cell(JDBC_Fetch.getData(rs));
for colIdx = 1 : numCols
switch ColumnType{colIdx}
case {'Float','Double'}
data{colIdx} = num2cell(data{colIdx});
case {'Long','Integer','Short','BigDecimal'}
data{colIdx} = num2cell(data{colIdx});
case 'Boolean'
data{colIdx} = num2cell(data{colIdx});
otherwise %case {'String','Date','Time','Timestamp'}
%data{colIdx} = cell(data{colIdx});  % no need to do anything here!
end
end
data = [data{:}];