com.google.visualization.datasource.util.SqlDataSourceHelper.java Source code

Java tutorial

Introduction

Here is the source code for com.google.visualization.datasource.util.SqlDataSourceHelper.java

Source

// Copyright 2009 Google Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package com.google.visualization.datasource.util;

import com.google.common.collect.Lists;
import com.google.visualization.datasource.base.DataSourceException;
import com.google.visualization.datasource.base.ReasonType;
import com.google.visualization.datasource.base.TypeMismatchException;
import com.google.visualization.datasource.datatable.ColumnDescription;
import com.google.visualization.datasource.datatable.DataTable;
import com.google.visualization.datasource.datatable.TableCell;
import com.google.visualization.datasource.datatable.TableRow;
import com.google.visualization.datasource.datatable.value.BooleanValue;
import com.google.visualization.datasource.datatable.value.DateTimeValue;
import com.google.visualization.datasource.datatable.value.DateValue;
import com.google.visualization.datasource.datatable.value.NumberValue;
import com.google.visualization.datasource.datatable.value.TextValue;
import com.google.visualization.datasource.datatable.value.TimeOfDayValue;
import com.google.visualization.datasource.datatable.value.Value;
import com.google.visualization.datasource.datatable.value.ValueType;
import com.google.visualization.datasource.query.AbstractColumn;
import com.google.visualization.datasource.query.AggregationColumn;
import com.google.visualization.datasource.query.AggregationType;
import com.google.visualization.datasource.query.ColumnColumnFilter;
import com.google.visualization.datasource.query.ColumnIsNullFilter;
import com.google.visualization.datasource.query.ColumnSort;
import com.google.visualization.datasource.query.ColumnValueFilter;
import com.google.visualization.datasource.query.ComparisonFilter;
import com.google.visualization.datasource.query.CompoundFilter;
import com.google.visualization.datasource.query.NegationFilter;
import com.google.visualization.datasource.query.Query;
import com.google.visualization.datasource.query.QueryFilter;
import com.google.visualization.datasource.query.QueryGroup;
import com.google.visualization.datasource.query.QuerySelection;
import com.google.visualization.datasource.query.QuerySort;
import com.google.visualization.datasource.query.SimpleColumn;
import com.google.visualization.datasource.query.SortOrder;

import com.ibm.icu.util.Calendar;
import com.ibm.icu.util.GregorianCalendar;
import com.ibm.icu.util.TimeZone;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.text.StrBuilder;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.List;

/**
 * A utility class, with static methods that are specific for creating a
 * data source based on a SQL database table.
 * For now, it can be based on mysql database only.
 *
 * @author Liron L.
 */
public class SqlDataSourceHelper {

    /**
     * Log.
     */
    private static final Log log = LogFactory.getLog(SqlDataSourceHelper.class.getName());

    /**
     * A private constructor - all methods are static.
     */
    private SqlDataSourceHelper() {
    }

    /**
     * Executes the given query on the given SQL database table, and returns the
     * result as a DataTable.
     *
     * @param query The query.
     * @param databaseDescription The information needed to connect to the SQL database and table.
     *
     * @return DataTable A data table with the data from the specified sql table,
     *     after applying the specified query on it.
     *
     * @throws DataSourceException Thrown when the data source fails to perform the action.
     */
    public static DataTable executeQuery(Query query, SqlDatabaseDescription databaseDescription)
            throws DataSourceException {
        Connection con = getDatabaseConnection(databaseDescription);
        String tableName = databaseDescription.getTableName();

        // Build the sql query.
        StrBuilder queryStringBuilder = new StrBuilder();
        buildSqlQuery(query, queryStringBuilder, tableName);
        List<String> columnIdsList = null;
        if (query.hasSelection()) {
            columnIdsList = getColumnIdsList(query.getSelection());
        }
        Statement stmt = null;
        try {
            // Execute the sql query.
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(queryStringBuilder.toString());

            DataTable table = buildColumns(rs, columnIdsList);

            // Fill the data in the data table.
            buildRows(table, rs);
            return table;
        } catch (SQLException e) {
            String messageToUser = "Failed to execute SQL query: " + "\"" + queryStringBuilder.toString() + "\"\n"
                    + "SQL error message: " + e.getMessage();
            throw new DataSourceException(ReasonType.INTERNAL_ERROR, messageToUser);
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    /* ignore close errors */ }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    /* ignore close errors */ }
            }
        }
    }

    /**
     * Returns a connection to the SQL database.
     *
     * @param databaseDescription The database description.
     *
     * @return The SQL database connection.
     *
     * @throws DataSourceException Thrown when the database connection is failed.
     */
    private static Connection getDatabaseConnection(SqlDatabaseDescription databaseDescription)
            throws DataSourceException {
        Connection con;
        // Set the connection's parameters.
        String userName = databaseDescription.getUser();
        String password = databaseDescription.getPassword();
        String url = databaseDescription.getUrl();
        try {
            // Connect to the database.
            // We should add connection pooling to avoid heavy creation of connections.
            con = DriverManager.getConnection(url, userName, password);
        } catch (SQLException e) {
            log.error("Failed to connect to database server.", e);
            throw new DataSourceException(ReasonType.INTERNAL_ERROR, "Failed to connect to database server.");
        }
        return con;
    }

    /**
     * Builds the sql query.
     *
     * @param query The query.
     * @param queryStringBuilder A string builder to build the sql query.
     * @param tableName The sql table name.
     *
     * @throws DataSourceException On errors to create the data table.
     */
    private static void buildSqlQuery(Query query, StrBuilder queryStringBuilder, String tableName)
            throws DataSourceException {
        appendSelectClause(query, queryStringBuilder);
        appendFromClause(query, queryStringBuilder, tableName);
        appendWhereClause(query, queryStringBuilder);
        appendGroupByClause(query, queryStringBuilder);
        appendOrderByClause(query, queryStringBuilder);
        appendLimitAndOffsetClause(query, queryStringBuilder);
    }

    /**
     * Appends the LIMIT and OFFSET clause of the sql query to the given string
     * builder. If there is no LIMIT on the number of rows, uses the system row
     * limit.
     *
     * @param query The query.
     * @param queryStringBuilder The string builder holding the string query.
     */
    static void appendLimitAndOffsetClause(Query query, StrBuilder queryStringBuilder) {
        if (query.hasRowLimit()) {
            queryStringBuilder.append("LIMIT ");
            queryStringBuilder.append(query.getRowLimit());
        }
        if (query.hasRowOffset()) {
            queryStringBuilder.append(" OFFSET ").append(query.getRowOffset());
        }
    }

    /**
     * Appends the GROUP BY clause of the sql query to the given string builder.
     *
     * @param query The query.
     * @param queryStringBuilder The string builder holding the string query.
     */
    static void appendGroupByClause(Query query, StrBuilder queryStringBuilder) {
        if (!query.hasGroup()) {
            return;
        }
        queryStringBuilder.append("GROUP BY ");
        QueryGroup queryGroup = query.getGroup();
        List<String> groupColumnIds = queryGroup.getColumnIds();
        List<String> newColumnIds = Lists.newArrayList();
        for (String groupColumnId : groupColumnIds) {
            newColumnIds.add('\"' + groupColumnId + '\"');
        }
        queryStringBuilder.appendWithSeparators(newColumnIds, ", ");
        queryStringBuilder.append(" ");
    }

    /**
     * Appends the ORDER BY clause of the sql query to the given string builder.
     *
     * @param query The query.
     * @param queryStringBuilder The string builder holding the string query.
     */
    static void appendOrderByClause(Query query, StrBuilder queryStringBuilder) {
        if (!query.hasSort()) {
            return;
        }
        queryStringBuilder.append("ORDER BY ");
        QuerySort querySort = query.getSort();
        List<ColumnSort> sortColumns = querySort.getSortColumns();
        int numOfSortColumns = sortColumns.size();
        for (int col = 0; col < numOfSortColumns; col++) {
            ColumnSort columnSort = sortColumns.get(col);
            queryStringBuilder.append(getColumnId(columnSort.getColumn()));
            if (columnSort.getOrder() == SortOrder.DESCENDING) {
                queryStringBuilder.append(" DESC");
            }
            if (col < numOfSortColumns - 1) {
                queryStringBuilder.append(", ");
            }
        }
        queryStringBuilder.append(" ");
    }

    /**
     * Appends the WHERE clause of the sql query to the given string builder.
     *
     * @param query The query.
     * @param queryStringBuilder The string builder holding the string query.
     */
    static void appendWhereClause(Query query, StrBuilder queryStringBuilder) {
        if (query.hasFilter()) {
            QueryFilter queryFilter = query.getFilter();
            queryStringBuilder.append("WHERE ").append(buildWhereClauseRecursively(queryFilter)).append(" ");
        }
    }

    /**
     * Builds the sql WHERE clause recursively from the given query filter.
     * The WHERE clause structure is a tree where the leafs are comparison
     * filters and the internal nodes are compound filters. The recursion builds
     * a string like an in-order walk on the tree. Each filter (i.e. a node in
     * the tree) has parenthesis around it.
     *
     * @param queryFilter The query filter.
     *
     * @return The sql query WHERE clause as a StrBuilder.
     */
    private static StrBuilder buildWhereClauseRecursively(QueryFilter queryFilter) {
        StrBuilder whereClause = new StrBuilder();

        // Base case of the recursion: the filter is not a compound filter.
        if (queryFilter instanceof ColumnIsNullFilter) {
            buildWhereClauseForIsNullFilter(whereClause, queryFilter);
        } else if (queryFilter instanceof ComparisonFilter) {
            buildWhereCluaseForComparisonFilter(whereClause, queryFilter);
        } else if (queryFilter instanceof NegationFilter) {
            whereClause.append("(NOT ");
            whereClause.append(buildWhereClauseRecursively(((NegationFilter) queryFilter).getSubFilter()));
            whereClause.append(")");
        } else {
            // queryFilter is a CompoundFilter.
            CompoundFilter compoundFilter = (CompoundFilter) queryFilter;

            int numberOfSubFilters = compoundFilter.getSubFilters().size();

            // If the compound filter is empty, build a where clause according to the
            // logical operator: nothing AND nothing -> WHERE "true", nothing OR
            // nothing -> WHERE "false" (match the query language rules).
            if (numberOfSubFilters == 0) {
                if (compoundFilter.getOperator() == CompoundFilter.LogicalOperator.AND) {
                    whereClause.append("true");
                } else {// OR
                    whereClause.append("false");
                }
            } else {
                List<String> filterComponents = Lists.newArrayList();
                for (QueryFilter filter : compoundFilter.getSubFilters()) {
                    filterComponents.add(buildWhereClauseRecursively(filter).toString());
                }
                String logicalOperator = getSqlLogicalOperator(compoundFilter.getOperator());
                whereClause.append("(").appendWithSeparators(filterComponents, " " + logicalOperator + " ")
                        .append(")");
            }
        }
        return whereClause;
    }

    /**
     * Builds a WHERE clause for is-null filter.
     * 
     * @param whereClause A string builder representing the WHERE clause of the SQL query.
     * @param queryFilter The query filter.
     */
    private static void buildWhereClauseForIsNullFilter(StrBuilder whereClause, QueryFilter queryFilter) {
        ColumnIsNullFilter filter = (ColumnIsNullFilter) queryFilter;

        whereClause.append("(").append(getColumnId(filter.getColumn())).append(" IS NULL)");
    }

    /**
     * Builds the WHERE clause for comparison filter. This is the base case of
     * the recursive building of the WHERE clause of the sql query.
     *
     * @param whereClause A string builder representing the WHERE clause of the SQL query.
     * @param queryFilter The query filter.
     */
    private static void buildWhereCluaseForComparisonFilter(StrBuilder whereClause, QueryFilter queryFilter) {
        StrBuilder first = new StrBuilder();
        StrBuilder second = new StrBuilder();

        // Build the left part and the right part of the clause according to the filter's type.
        if (queryFilter instanceof ColumnColumnFilter) {
            ColumnColumnFilter filter = (ColumnColumnFilter) queryFilter;
            first.append(getColumnId(filter.getFirstColumn()));
            second.append(getColumnId(filter.getSecondColumn()));
        } else { // The filter is a ColumnValueFilter
            ColumnValueFilter filter = (ColumnValueFilter) queryFilter;
            first.append(getColumnId(filter.getColumn()));
            second.append(filter.getValue().toString());
            if ((filter.getValue().getType() == ValueType.TEXT) || (filter.getValue().getType() == ValueType.DATE)
                    || (filter.getValue().getType() == ValueType.DATETIME)
                    || (filter.getValue().getType() == ValueType.TIMEOFDAY)) {
                second.insert(0, "\"");
                second.insert(second.length(), "\"");
            }
        }
        whereClause.append(buildWhereClauseFromRightAndLeftParts(first, second,
                ((ComparisonFilter) queryFilter).getOperator()));
    }

    /**
     * Returns the sql operator of the given CompoundFilter.LogicalOperator as a string.
     *
     * @param operator The CompoundFilter.LogicalOperator.
     *
     * @return A string representation of the SQL operator.
     */
    private static String getSqlLogicalOperator(CompoundFilter.LogicalOperator operator) {
        String stringOperator;
        switch (operator) {
        case AND:
            stringOperator = "AND";
            break;
        case OR:
            stringOperator = "OR";
            break;
        default:// Should never get here.
            throw new RuntimeException("Logical operator was not found: " + operator);
        }
        return stringOperator;
    }

    /**
     * Builds the where clause of the SQL query sql from the two given values and
     * the operator between these two values.
     *
     * @param value1 The first value in the where clause (either column id or value)
     * @param value2 The second value in the where clause (either column id or value)
     * @param operator The ComparisonFilter.Operator.
     *
     * @return A string builder representing the where clause of the SQL query.
     */
    private static StrBuilder buildWhereClauseFromRightAndLeftParts(StrBuilder value1, StrBuilder value2,
            ComparisonFilter.Operator operator) {
        StrBuilder clause;
        switch (operator) {
        case EQ:
            clause = value1.append("=").append(value2);
            break;
        case NE:
            clause = value1.append("<>").append(value2);
            break;
        case LT:
            clause = value1.append("<").append(value2);
            break;
        case GT:
            clause = value1.append(">").append(value2);
            break;
        case LE:
            clause = value1.append("<=").append(value2);
            break;
        case GE:
            clause = value1.append(">=").append(value2);
            break;
        case CONTAINS:
            value2 = new StrBuilder(value2.toString().replace("\"", ""));
            clause = value1.append(" LIKE ").append("\"%").append(value2).append("%\"");
            break;
        case STARTS_WITH:
            value2 = new StrBuilder(value2.toString().replace("\"", ""));
            clause = value1.append(" LIKE ").append("\"").append(value2).append("%\"");
            break;
        case ENDS_WITH:
            value2 = new StrBuilder(value2.toString().replace("\"", ""));
            clause = value1.append(" LIKE ").append("\"%").append(value2).append("\"");
            break;
        case MATCHES:
            throw new RuntimeException("SQL does not support regular expression");
        case LIKE:
            value2 = new StrBuilder(value2.toString().replace("\"", ""));
            clause = value1.append(" LIKE ").append("\"").append(value2).append("\"");
            break;
        default:// Should never get here.
            throw new RuntimeException("Operator was not found: " + operator);
        }
        clause.insert(0, "(").append(")");
        return clause;
    }

    /**
     * Appends the SELECT clause of the sql query to the given string builder.
     *
     * @param query The query.
     * @param queryStringBuilder The string builder holding the string query.
     */

    static void appendSelectClause(Query query, StrBuilder queryStringBuilder) {
        queryStringBuilder.append("SELECT ");

        // If it's a selectAll query, build "select *" clause.
        if (!query.hasSelection()) {
            queryStringBuilder.append("* ");
            return;
        }

        List<AbstractColumn> columns = query.getSelection().getColumns();
        int numOfColsInQuery = columns.size();

        // Add the Ids of the columns to the select clause
        for (int col = 0; col < numOfColsInQuery; col++) {
            queryStringBuilder.append(getColumnId(columns.get(col)));
            if (col < numOfColsInQuery - 1) {
                queryStringBuilder.append(", ");
            }
        }
        queryStringBuilder.append(" ");
    }

    /**
     * Returns the column id in SQL.
     *
     * @param abstractColumn The column.
     *
     * @return The column id for the data table.
     */
    private static StrBuilder getColumnId(AbstractColumn abstractColumn) {
        StrBuilder columnId = new StrBuilder();

        // For simple column the id is simply the column id.
        if (abstractColumn instanceof SimpleColumn) {
            columnId.append("\"").append(abstractColumn.getId()).append("\"");
        } else {
            // For aggregation column build the id from the aggregation type and the
            // column id (e.g. for aggregation type 'min' and column id "salary", the
            // sql column id will be: min("salary");
            AggregationColumn aggregationColumn = (AggregationColumn) abstractColumn;
            columnId.append(getAggregationFunction(aggregationColumn.getAggregationType())).append("(\"")
                    .append(aggregationColumn.getAggregatedColumn()).append("\")");
        }
        return columnId;
    }

    /**
     * Returns a list with the selected column ids in the table description.
     *
     * @param selection The query selection.
     *
     * @return a list with the selected column ids.
     */
    private static List<String> getColumnIdsList(QuerySelection selection) {
        List<String> columnIds = Lists.newArrayListWithCapacity(selection.getColumns().size());
        for (AbstractColumn column : selection.getColumns()) {
            columnIds.add(column.getId());
        }
        return columnIds;
    }

    /**
     * Returns a string representation of the given aggregation type.
     *
     * @param type The aggregation type.
     *
     * @return The aggragation type's string representation.
     */
    private static String getAggregationFunction(AggregationType type) {
        return type.getCode();
    }

    /**
     * Appends the FROM clause of the sql query to the given string builder. Takes
     * the table name from the configuration file. If no table name is given,
     * takes the table name from the query.
     *
     * @param query The query.
     * @param queryStringBuilder The string builder holding the string query.
     * @param tableName The database table name.
     *
     * @throws DataSourceException Thrown when no table name provided, or when the
     *     table name in the data source doesn't match the table name in the
     *     query.
     */
    static void appendFromClause(Query query, StrBuilder queryStringBuilder, String tableName)
            throws DataSourceException {
        if (StringUtils.isEmpty(tableName)) {
            log.error("No table name provided.");
            throw new DataSourceException(ReasonType.OTHER, "No table name provided.");
        }
        queryStringBuilder.append("FROM ");
        queryStringBuilder.append(tableName);
        queryStringBuilder.append(" ");
    }

    /**
     * Returns the table description which includes the ids, labels and types of
     * the table columns.
     *
     * @param rs The result set holding the data from the sql table.
     * @param columnIdsList The list of the column ids in the data table.
     *
     * @return The table description.
     *
     * @throws SQLException Thrown when the connection to the database failed.
     */
    static DataTable buildColumns(ResultSet rs, List<String> columnIdsList) throws SQLException {
        DataTable result = new DataTable();
        ResultSetMetaData metaData = rs.getMetaData();
        int numOfCols = metaData.getColumnCount();
        // For each column in the table, create the column description. SQL indexes
        // are 1-based.
        for (int i = 1; i <= numOfCols; i++) {
            String id = (columnIdsList == null) ? metaData.getColumnLabel(i) : columnIdsList.get(i - 1);
            ColumnDescription columnDescription = new ColumnDescription(id,
                    sqlTypeToValueType(metaData.getColumnType(i)), metaData.getColumnLabel(i));
            result.addColumn(columnDescription);
        }
        return result;
    }

    /**
     * Converts the given SQL type to a value type.
     *
     * @param sqlType The sql type to be converted.
     *
     * @return The value type that fits the given sql type.
     */
    private static ValueType sqlTypeToValueType(int sqlType) {
        ValueType valueType;
        switch (sqlType) {
        case Types.BOOLEAN:
        case Types.BIT: {
            valueType = ValueType.BOOLEAN;
            break;
        }
        case Types.CHAR:
        case Types.VARCHAR:
            valueType = ValueType.TEXT;
            break;
        case Types.INTEGER:
        case Types.SMALLINT:
        case Types.BIGINT:
        case Types.TINYINT:
        case Types.REAL:
        case Types.NUMERIC:
        case Types.DOUBLE:
        case Types.FLOAT:
        case Types.DECIMAL:
            valueType = ValueType.NUMBER;
            break;
        case Types.DATE:
            valueType = ValueType.DATE;
            break;
        case Types.TIME:
            valueType = ValueType.TIMEOFDAY;
            break;
        case Types.TIMESTAMP:
            valueType = ValueType.DATETIME;
            break;
        default:
            valueType = ValueType.TEXT;
            break;
        }
        return valueType;
    }

    /**
     * Populates the data table and returns it.
     *
     * @param dataTable The data table to populates, that should already contains the
     *     column descriptions.
     * @param rs The result set holding the results of running the query on the
     *     relevant sql database table. The result set's data required for
     *     building the rows of the data table.
     *
     * @throws SQLException Thrown when the connection to the database failed.
     */
    static void buildRows(DataTable dataTable, ResultSet rs) throws SQLException {
        List<ColumnDescription> columnsDescriptionList = dataTable.getColumnDescriptions();
        int numOfCols = dataTable.getNumberOfColumns();

        // Get the value types of the columns.
        ValueType[] columnsTypeArray = new ValueType[numOfCols];
        for (int c = 0; c < numOfCols; c++) {
            columnsTypeArray[c] = columnsDescriptionList.get(c).getType();
        }

        // Build the data table rows, and in each row create the table cells with
        // the information in the result set.
        while (rs.next()) {
            TableRow tableRow = new TableRow();
            for (int c = 0; c < numOfCols; c++) {
                tableRow.addCell(buildTableCell(rs, columnsTypeArray[c], c));
            }
            try {
                dataTable.addRow(tableRow);
            } catch (TypeMismatchException e) {
                // Should not happen. An SQLException would already have been thrown if there was such a
                // problem.
            }
        }
    }

    /**
     * Creates a table cell from the value in the current row of the given result
     * set and the given column index. The type of the value is determined by the
     * given value type.
     *
     * @param rs The result set holding the data from the sql table. The result
     *     points to the current row.
     * @param valueType The value type of the column that the cell belongs to.
     * @param column The column index. Indexes are 0-based.
     *
     * @return The table cell.
     *
     * @throws SQLException Thrown when the connection to the database failed.
     */
    private static TableCell buildTableCell(ResultSet rs, ValueType valueType, int column) throws SQLException {
        Value value = null;

        // SQL indexes are 1- based.
        column = column + 1;

        switch (valueType) {
        case BOOLEAN:
            value = BooleanValue.getInstance(rs.getBoolean(column));
            break;
        case NUMBER:
            value = new NumberValue(rs.getDouble(column));
            break;
        case DATE:
            Date date = rs.getDate(column);
            // If date is null it is handled later.
            if (date != null) {
                GregorianCalendar gc = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
                // Set the year, month and date in the gregorian calendar.
                // Use the 'set' method with those parameters, and not the 'setTime'
                // method with the date parameter, since the Date object contains the
                // current time zone and it's impossible to change it to 'GMT'.
                gc.set(date.getYear() + 1900, date.getMonth(), date.getDate());
                value = new DateValue(gc);
            }
            break;
        case DATETIME:
            Timestamp timestamp = rs.getTimestamp(column);
            // If timestamp is null it is handled later.
            if (timestamp != null) {
                GregorianCalendar gc = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
                // Set the year, month, date, hours, minutes and seconds in the
                // gregorian calendar. Use the 'set' method with those parameters,
                // and not the 'setTime' method with the timestamp parameter, since
                // the Timestamp object contains the current time zone and it's
                // impossible to change it to 'GMT'.
                gc.set(timestamp.getYear() + 1900, timestamp.getMonth(), timestamp.getDate(), timestamp.getHours(),
                        timestamp.getMinutes(), timestamp.getSeconds());
                // Set the milliseconds explicitly, as they are not saved in the
                // underlying date.
                gc.set(Calendar.MILLISECOND, timestamp.getNanos() / 1000000);
                value = new DateTimeValue(gc);
            }
            break;
        case TIMEOFDAY:
            Time time = rs.getTime(column);
            // If time is null it is handled later.
            if (time != null) {
                GregorianCalendar gc = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
                // Set the hours, minutes and seconds of the time in the gregorian
                // calendar. Set the year, month and date to be January 1 1970 like
                // in the Time object.
                // Use the 'set' method with those parameters,
                // and not the 'setTime' method with the time parameter, since
                // the Time object contains the current time zone and it's
                // impossible to change it to 'GMT'.
                gc.set(1970, Calendar.JANUARY, 1, time.getHours(), time.getMinutes(), time.getSeconds());
                // Set the milliseconds explicitly, otherwise the milliseconds from
                // the time the gc was initialized are used.
                gc.set(GregorianCalendar.MILLISECOND, 0);
                value = new TimeOfDayValue(gc);
            }
            break;
        default:
            String colValue = rs.getString(column);
            if (colValue == null) {
                value = TextValue.getNullValue();
            } else {
                value = new TextValue(rs.getString(column));
            }
            break;
        }
        // Handle null values.
        if (rs.wasNull()) {
            return new TableCell(Value.getNullValueFromValueType(valueType));
        } else {
            return new TableCell(value);
        }
    }
}