Java tutorial
/******************************************************************************* * * Copyright 2010 Alexandru Craciun, and individual contributors as indicated * by the @authors tag. * * This is free software; you can redistribute it and/or modify it * under the terms of the GNU Lesser General Public License as * published by the Free Software Foundation; either version 3 of * the License, or (at your option) any later version. * * This software is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this software; if not, write to the Free * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA * 02110-1301 USA, or see the FSF site: http://www.fsf.org. ******************************************************************************/ package org.netxilia.spi.impl.storage.db; import java.util.ArrayList; import java.util.Collection; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import org.apache.commons.beanutils.PropertyUtils; import org.apache.log4j.Logger; import org.netxilia.api.display.Styles; import org.netxilia.api.exception.NotFoundException; import org.netxilia.api.exception.StorageException; import org.netxilia.api.formula.Formula; import org.netxilia.api.model.CellCreator; import org.netxilia.api.model.CellData; import org.netxilia.api.model.CellData.Property; import org.netxilia.api.model.CellDataWithProperties; import org.netxilia.api.model.SheetDimensions; import org.netxilia.api.model.SheetFullName; import org.netxilia.api.model.SheetType; import org.netxilia.api.reference.AreaReference; import org.netxilia.api.reference.CellReference; import org.netxilia.api.utils.CollectionUtils; import org.netxilia.api.utils.IListElementCreator; import org.netxilia.api.utils.Matrix; import org.netxilia.api.utils.MatrixBuilder; import org.netxilia.api.value.GenericValueType; import org.netxilia.api.value.GenericValueUtils; import org.netxilia.api.value.IGenericValue; import org.netxilia.api.value.IGenericValueParseService; import org.netxilia.api.value.StringValue; import org.netxilia.spi.impl.storage.db.ddl.schema.DbTable; import org.springframework.beans.factory.annotation.Autowired; /** * This is the most complicated mapper because is stores value in the data table and all the order properties in a * compressed (SparseMatrix) format in the properties table. * * @author <a href='mailto:ax.craciun@gmail.com'>Alexandru Craciun</a> * */ public class CellsMapper extends AbstractMapper { private final static Logger log = Logger.getLogger(CellsMapper.class); private final static IGenericValue IGNORE_VALUE = new StringValue("__IGNORE"); private final IListElementCreator<IGenericValue> GENERIC_VALUE_CREATOR = CollectionUtils .sameElementCreator(IGNORE_VALUE); @Autowired private SparseMatrixMapper matrixMapper; @Autowired private NormalRowsMapper rowsMapper; @Autowired private IGenericValueParseService parseService; public SparseMatrixMapper getMatrixMapper() { return matrixMapper; } public void setMatrixMapper(SparseMatrixMapper matrixMapper) { this.matrixMapper = matrixMapper; } public IGenericValueParseService getParseService() { return parseService; } public void setParseService(IGenericValueParseService parseService) { this.parseService = parseService; } public NormalRowsMapper getRowsMapper() { return rowsMapper; } public void setRowsMapper(NormalRowsMapper rowsMapper) { this.rowsMapper = rowsMapper; } private void saveRow(SheetDbSession session, DbTable valuesTable, int row, List<IGenericValue> cells, List<DbColumnStorageInfo> columnStorages) throws NotFoundException { DbRowStorageInfo rowStorage = session.getStorageService().getOrCreateRowStorage(session, row); StringBuilder query = new StringBuilder("UPDATE " + valuesTable.getName() + " SET "); List<Object> params = new ArrayList<Object>(cells.size()); for (int col = 0; col < cells.size(); ++col) { IGenericValue value = cells.get(col); if (value == IGNORE_VALUE) { continue; } DbColumnStorageInfo columnStorage = columnStorages.get(col); String rawValue = value != null ? value.getStringValue() : null; int maxCellSize = valuesTable.getColumn(columnStorage.getDbColumnName()).getSize(); if (rawValue != null && rawValue.length() > maxCellSize) { log.warn(new CellReference(row, col) + " value was truncated from " + rawValue.length() + " to " + maxCellSize); rawValue = rawValue.substring(0, maxCellSize); } if (params.size() > 0) { query.append(","); } query.append(columnStorage.getDbColumnName()).append(" = ?"); params.add(rawValue); } if (params.size() == 0) { return; } query.append(" WHERE id = ?"); params.add(rowStorage.getId()); session.getWorkbookData().update(query.toString(), params.toArray()); } private int getMaxColumn(Collection<CellDataWithProperties> cells) { int maxColumn = 0; for (CellDataWithProperties saveCell : cells) { CellReference ref = saveCell.getCellData().getReference(); maxColumn = Math.max(maxColumn, ref.getColumnIndex()); } return maxColumn; } private void saveNormalData(DbSheetStorageInfo sheetStorage, SheetDbSession session, SheetFullName sheetName, Collection<CellDataWithProperties> cells, List<DbColumnStorageInfo> columnStorage) throws NotFoundException { String valueTableName = sheetStorage.getDbTableName(); DbTable valuesTable = session.getWorkbookData().getSchema().getTable(valueTableName); if (valuesTable == null) { // the table should be created by the sheet throw new StorageException("The table " + valueTableName + " does not exist"); } // TODO - quick save for one cell // cells grouped by row // for each row, the map contains the value in the corresponding column position // the special generic value is used to mark a cell to be ignored when saving Map<Integer, List<IGenericValue>> rows = new LinkedHashMap<Integer, List<IGenericValue>>(); for (CellDataWithProperties saveCell : cells) { if (!saveCell.getProperties().contains(CellData.Property.value)) { continue; } CellReference ref = saveCell.getCellData().getReference(); List<IGenericValue> row = rows.get(ref.getRowIndex()); if (row == null) { row = new ArrayList<IGenericValue>(ref.getColumnIndex() + 1); rows.put(ref.getRowIndex(), row); } CollectionUtils.atLeastSize(row, ref.getColumnIndex() + 1, GENERIC_VALUE_CREATOR); row.set(ref.getColumnIndex(), saveCell.getCellData().getValue()); } // save each row for (Map.Entry<Integer, List<IGenericValue>> entry : rows.entrySet()) { saveRow(session, valuesTable, entry.getKey(), entry.getValue(), columnStorage); } } public void saveCells(SheetDbSession session, SheetFullName sheetName, Collection<CellDataWithProperties> cells) throws NotFoundException { DbSheetStorageInfo sheetStorage = session.getStorageService().getSheetStorage(session); long t1 = System.currentTimeMillis(); List<DbColumnStorageInfo> columnStorage = session.getStorageService().createColumnStorage(session, getMaxColumn(cells)); if (sheetStorage.getType() == SheetType.normal) { saveNormalData(sheetStorage, session, sheetName, cells, columnStorage); } long t2 = System.currentTimeMillis(); SparseMatrixCollection matrices = session.getStorageService().getCellsStorage(session); matrices.setListener(matrixMapper.getMatrixCollectionListener(session, sheetStorage)); try { for (CellDataWithProperties saveCell : cells) { saveCellProperties(matrices, session, sheetName, sheetStorage.getType(), saveCell.getCellData(), saveCell.getProperties()); } } finally { matrices.getListener().save(); matrices.setListener(null); } long t3 = System.currentTimeMillis(); if (log.isDebugEnabled()) { log.debug("Mapper save:" + " data=" + (t2 - t1) + " props=" + (t3 - t2)); } } /** * save a cell's property (including value for sheets of type other than "normal") */ private CellData saveCellProperties(SparseMatrixCollection matrices, SheetDbSession session, SheetFullName sheetFullName, SheetType sheetType, CellData cell, Collection<Property> properties) throws NotFoundException { // make sure the corresponding row exists session.getStorageService().getOrCreateRowStorage(session, cell.getReference().getRowIndex()); for (Property property : properties) { if (Property.value == property) { // TODO - store rich values if (sheetType != SheetType.normal) { // create column // create row String rawValue = cell.getValue() != null ? cell.getValue().getStringValue() : null; matrices.put(cell.getReference().getRowIndex(), cell.getReference().getColumnIndex(), property.name(), rawValue); } // add also type matrices.put(cell.getReference().getRowIndex(), cell.getReference().getColumnIndex(), CellData.Property.type.name(), cell.getValue() != null ? cell.getValue().getValueType().name() : null); } else { // update the other properties Object propertyValue = null; try { propertyValue = PropertyUtils.getSimpleProperty(cell, property.name()); } catch (Exception e) { log.error("Cannot get cell property " + property + ":" + e, e); } matrices.put(cell.getReference().getRowIndex(), cell.getReference().getColumnIndex(), property.name(), propertyValue != null ? propertyValue.toString() : null); // save modification right away } } return cell; } public Matrix<CellData> loadCells(SheetDbSession data, SheetFullName sheetFullName, AreaReference area) throws StorageException, NotFoundException { DbSheetStorageInfo sheetStorage = data.getStorageService().getSheetStorage(data); SheetDimensions dimensions = data.getStorageService().getSheetDimensions(data); if (dimensions.getRowCount() == 0 || dimensions.getColumnCount() == 0) { // empty sheet return new Matrix<CellData>(); } if (area.getFirstColumnIndex() >= dimensions.getColumnCount() || area.getFirstRowIndex() >= dimensions.getRowCount()) { // the desired area is completely outside the sheet return new Matrix<CellData>(); } AreaReference boundArea = area.bind(dimensions.getRowCount(), dimensions.getColumnCount()); if (boundArea.getSheetName() == null) { boundArea = boundArea.withSheetName(sheetFullName.getSheetName()); } List<List<String>> values = null; if (sheetStorage.getType() == SheetType.normal) { values = rowsMapper.loadValues(data, sheetStorage, boundArea); } SparseMatrixCollection matrices = data.getStorageService().getCellsStorage(data); MatrixBuilder<CellData> cells = new MatrixBuilder<CellData>(new CellCreator(boundArea.getSheetName(), boundArea.getFirstRowIndex(), boundArea.getFirstColumnIndex())); for (CellReference cell : boundArea.iterable(dimensions.getRowCount(), dimensions.getColumnCount())) { int relativeRowIndex = cell.getRowIndex() - boundArea.getFirstRowIndex(); int relativeColIndex = cell.getColumnIndex() - boundArea.getFirstColumnIndex(); // load the other properties String type = matrices.get(cell.getRowIndex(), cell.getColumnIndex(), CellData.Property.type.name()); String style = matrices.get(cell.getRowIndex(), cell.getColumnIndex(), CellData.Property.styles.name()); String formula = matrices.get(cell.getRowIndex(), cell.getColumnIndex(), CellData.Property.formula.name()); String rawValue = null; if (sheetStorage.getType() == SheetType.normal) { if (relativeRowIndex < values.size()) { List<String> row = values.get(relativeRowIndex); if (row != null && relativeColIndex < row.size()) { rawValue = row.get(relativeColIndex); } } } else { rawValue = matrices.get(cell.getRowIndex(), cell.getColumnIndex(), CellData.Property.value.name()); } IGenericValue value = null; try { // TODO - store rich values value = GenericValueUtils.rawStringToGeneric(rawValue, type != null ? GenericValueType.valueOf(type) : GenericValueType.STRING); } catch (Exception e) { log.error("Could not convert [" + rawValue + "] to type " + type + ". Re-parse it."); value = parseService.parse(rawValue); } CellData cellData = new CellData(cell, value, Formula.valueOf(formula), Styles.valueOf(style)); cells.set(relativeRowIndex, relativeColIndex, cellData); } return cells.build(); } }