Java tutorial
// 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); } } }