JDBC and Matlab: A simple example

by hohonuuli 10/09/2014 09:18:00 AM 8 comments database Java jdbc matlab sql

Matlab is an excellent tool for ad-hoc analysis and prototyping. If you store your data in a relational database, Mathworks provides a database toolbox for fetching your data. However, you do not need to purchase this to interact with your database. As Matlab has excellent Java integration, you can use JDBC directly to talk to pretty much any database you want. In order to do this do the following:

Get a JDBC Driver

First, you will need the JDBC driver required to talk to your database. If you do not know how to find the driver, follow this link for an example on how to locate your driver. Once, you have your JDBC driver, you will need to add it to Matlab's static classpath. If you are running an older version of Matlab, say earlier than R2013b, you probably need to add the full path to the driver in the classpath.txt file. You can edit this by typing open classpath.txt in Matlab. If the file says "Do Not Edit This File" at the top then you will need to add the full path to the driver in ~/.matlab/[matlab version]/javaclasspath.txt. (e.g. ~/.matlab/R2014b/javaclasspath.txt). You may need to create this file if it doesn't exist.

Figure out your Database URL

Next, you'll need to figure out the URL to your database. Refer to your drivers documentation. Here's a few example URLS:

Server	Driver Jar	JDBC URL
SQL Server	sqljdbc4.jar	jdbc:sqlserver://servername.mydomain.org;database=MyDatabase
SQL Server	jtds-1.3.1.jar	jdbc:jtds:sqlserver://servername.mydomain.org:1433/MyDatabase
PostgreSQL	postgresql-9.3-1102.jdbc41.jar	jdbc:postgresql://servername.mydomain.org/MyDatabase

Matlab Code

Now all you need is some Matlab code to fetch your data.

database.m

First, here's a function to setup a database connection:

function c = database(url, user, password, driverName)
% DATABASE - connect to a SQL database
%
% Usage:
%   c = database(url, user, password, driverName)
% 
% Inputs:
%   url = The database URL. e.g. jdbc:jtds:sqlserver://solstice.shore.mbari.org:1433/EXPD
%   user = The user name to connect to the database
%   password = THe password for user
%   driverName = the name of the JDBC driver to use for the connection. 
%       e.g. 'net.sourceforge.jtds.jdbc.Driver' (optional)
%
% Outputs
%   c = A java.sql.Connection object to your database. Remember to call
%   close, either as 'close(c)' or c.close() when you are done with the 
%   connection.
%
% NOTES: In order to connect to a database, you will need the appropriate
% JDBC driver on the classpath. Most drivers do not work on Matlab's dynamic
% classpath. Instead add them to ~/.matlab/R2014b/javaclasspath.txt

% Brian Schlining
% 2014-10-08

if nargin == 4
    d = eval(driverName);
end
c = java.sql.DriverManager.getConnection(url, user, password);

jdbcquery.m

This function runs a query and returns the results as a Matlab structure. Each column of the ResultSet is represented as a separate field in the structure:

function s = jdbcquery(conn, sqlstr)
% Connect to database, execute SQL string, and pass back structure array.
%
% Usage:
%   s = jdbcquery(conn, sqlstr)
% 
% Input   
%   conn: A java.sql.Connection object. See Also database
%   sqlstr: SQL string, i.e. 'SELECT * FROM Observation WHERE ConceptName LIKE ''Pandalus%'''
%
% Output  
%   s: structure array, each field matches the column names in the query.
%
% Note: 
%   The Java class path to the sqljdbc driver must be specified before call.
%   For best results put it in your ~/.matlab/R2014b/javaclasspath.txt file.  
%   This path will vary depending on your Matlab version.
%   
% See also database

% Brian Schlining
% 2014-10-08 - Modified Reiko's version used for EXPD queries
% 2014-11-01 - Using UTC calendar to read dates to avoid timezone issues.

%% Java Imports
import java.sql.ResultSet;

cal = java.util.Calendar.getInstance();
cal.setTimeZone(java.util.TimeZone.getTimeZone('UTC'));

%% Fetch Loop
try    
    % query database
    q = conn.prepareStatement(sqlstr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    rs = q.executeQuery();

    metaData = rs.getMetaData();
    columnCount = metaData.getColumnCount();
    n = resultSetSize(rs);
    s = initStruct(metaData, n);
    
    row = 0; 
    % assign variables from resultset
    while rs.next()
        row = row + 1;
        for i = 1:columnCount
            coltype = char(metaData.getColumnTypeName(i));      % assess the column type
            colName = char(metaData.getColumnName(i));
            switch coltype
                case {'bigint', 'int', 'int4', 'decimal', 'double', 'float',  ...
                        'float8', 'float16', 'real', 'serial'}
                    s.(colName)(row) = toDouble(rs.getObject(i));
                case {'date'}
                    s.(colName)(row) = toDate(rs.getDate(i, cal));
                case {'datetime'}
                    s.(colName)(row) = toDate(rs.getTimestamp(i, cal));
                case {'geometry'}
                    s.(colName){row} = rs.getObject(i);
                otherwise % value is a string, so just pass it into the output array
                    s.(colName){row} = toString(rs.getString(i));
            end
        end
    end 

    % close all database connections
    try 
        q.close();     
    catch
        % DO NOTHING ON EXCEPTION
    end
    try 
        rs.close();     
    catch
        % DO NOTHING ON EXCEPTION
    end

catch me
    ex = MException('JDBC:jdbcquery', ['An error occurred while executing the SQL: \n' sqlstr ]);
    ex = ex.addCause(me);
    ex.throw;
end
end


%%
function s = resultSetSize(resultSet)
    ok = resultSet.last();
    if ~ok
        s = 0;
    else
        s = resultSet.getRow();
        resultSet.beforeFirst();
    end
end

%%
function s = initStruct(metaData, rows)
    for i = 1:metaData.getColumnCount()
       colType = char(metaData.getColumnTypeName(i));
       colName = char(metaData.getColumnName(i));
       switch char(colType)
           case {'bigint', 'int', 'int4', 'decimal', 'double', 'float',  ...
                    'float8', 'float16', 'real', 'serial' 'date', 'datetime'}
               s.(colName) = ones(rows, 1);
           otherwise
               s.(colName) = cell(rows, 1);
       end
    end
end

%%
function v = toDouble(n)
    if isempty(n)
        v = NaN;
    else
        v = double(n);
    end
end

%%
function v = toDate(n)
    if isempty(n)
        v = NaN;
    else
        %datenum('01 Jan 1970 00:00:00') = 719529
        v = n.getTime() / 1000 / 60 / 60 / 24 + 719529;
    end
end

%%
function v = toString(n)
    if isempty(n)
        v = '';
    else
        v = char(n);
    end
end

Putting it all together

Finally, you can use it in Matlab like this:

c = database('jdbc:jtds:sqlserver://severname.mydomain.org:1433/mydatabase', 'someusername', 'supersecretpassword');
sql = 'SELECT * FROM SomeTable';
v = jdbcquery(c, sql);
c.close();

If you query a database often you can dump that all into a function for that particular database. For example:

mydbquery.m

function v = mydbquery(sql)

c = database('jdbc:jtds:sqlserver://servername.mydomain.org:1433/mydatabase', 'someusername', 'supersecretpassword');
v = jdbcquery(c, sql);
c.close();

And then you would just use it as a one liner:

>> v = mydbquery('SELECT * FROM Observation WHERE ConceptName LIKE ''Pandalus%''')

v = 

                   id: [1989x1 double]
      VideoFrameID_FK: [1989x1 double]
       ObservationDTG: [1989x1 double]
             Observer: {1989x1 cell}
          ConceptName: {1989x1 cell}
                Notes: {1989x1 cell}
    LAST_UPDATED_TIME: [1989x1 double]
                    X: [1989x1 double]
                    Y: [1989x1 double]
              rowguid: {1989x1 cell}