Author: tchemit Date: 2011-11-12 21:21:21 +0100 (Sat, 12 Nov 2011) New Revision: 43 Url: http://forge.codelutin.com/repositories/revision/echobase/43 Log: continue export sql service Added: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/ExportSqlService.java Added: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/ExportSqlService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/ExportSqlService.java (rev 0) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/ExportSqlService.java 2011-11-12 20:21:21 UTC (rev 43) @@ -0,0 +1,217 @@ +/* + * #%L + * EchoBase :: Services + * + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2011 Ifremer, Codelutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program 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 General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * #L% + */ +package fr.ifremer.echobase.services; + +import fr.ifremer.echobase.EchoBaseTechnicalException; +import fr.ifremer.echobase.entities.EchoBaseDAOHelper; +import fr.ifremer.echobase.entities.EchoBaseUser; +import fr.ifremer.echobase.entities.ExportQuery; +import fr.ifremer.echobase.entities.ExportQueryDAO; +import org.apache.commons.lang.StringUtils; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.nuiton.topia.TopiaContext; +import org.nuiton.topia.TopiaException; +import org.nuiton.topia.framework.TopiaContextImplementor; +import org.nuiton.topia.framework.TopiaSQLQuery; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Collection; +import java.util.Date; +import java.util.List; +import java.util.Map; + +/** + * Service to deal with sql export. + * + * @author tchemit <chemit@codelutin.com> + * @since 0.1 + */ +public class ExportSqlService extends AbstractEchoBaseService { + + /** Logger. */ + private static final Log log = LogFactory.getLog(ExportSqlService.class); + + public Collection<ExportQuery> getQueries() { + try { + List<ExportQuery> result = getDAO().findAll(); + return result; + } catch (TopiaException eee) { + throw new EchoBaseTechnicalException("Could not obtain export queries", eee); + } + } + + public ExportQuery getQuery(String queryId) { + + try { + ExportQuery result = getDAO().findByTopiaId(queryId); + return result; + } catch (TopiaException eee) { + throw new EchoBaseTechnicalException("Could not obtain export query with id " + queryId, eee); + } + } + + public void createOrUpdate(ExportQuery exportQuery, EchoBaseUser user) { + try { + ExportQueryDAO dao = getDAO(); + ExportQuery entityToSave; + + // No id, creating new one entity + String id = exportQuery.getTopiaId(); + if (StringUtils.isEmpty(id)) { + entityToSave = dao.create(); + } else { + entityToSave = dao.findByTopiaId(id); + } + + entityToSave.setName(exportQuery.getName()); + entityToSave.setDescription(exportQuery.getDescription()); + entityToSave.setSqlQuery(exportQuery.getSqlQuery()); + entityToSave.setLastModifiedDate(new Date()); + entityToSave.setLastModifiedUser(user); + + dao.update(entityToSave); + getTransaction().commitTransaction(); + } catch (TopiaException eee) { + throw new EchoBaseTechnicalException(eee); + } + } + + protected ExportQueryDAO getDAO() throws TopiaException { + return EchoBaseDAOHelper.getExportQueryDAO(getTransaction()); + } + + public Map<String, Object>[] executeSql(String sql, Pager pager) { + + // get a query to count all rows for the request + GenericSQLQuery sqlQuery = new GenericSQLQuery(sql, pager); + try { + Map<String, Object>[] result = sqlQuery.getResult(getTransaction()); + return result; + } catch (TopiaException eee) { + throw new EchoBaseTechnicalException("Could not execute sql query", eee); + } + } + + public String[] getColumnNames(String sql) { + + String limitSql = sql.trim(); + if (limitSql.endsWith(";")) { + limitSql = limitSql.substring(0, limitSql.length() - 1); + limitSql += " LIMIT 1"; + } + try { + // do a limit query to one result and obtain the column names + // from the meta data of the result set + GenericSQLQuery sqlQuery = new GenericSQLQuery(limitSql, null); + String[] result = sqlQuery.getColumnNames(getTransaction()); + return result; + } catch (TopiaException eee) { + throw new EchoBaseTechnicalException("Could not execute query " + limitSql, eee); + } + } + + private static class GenericSQLQuery extends TopiaSQLQuery<Map<String, Object>> { + + protected String[] columnNames; + + private final String sql; + + private final Pager pager; + + public GenericSQLQuery(String sql, Pager pager) { + this.sql = sql; + this.pager = pager; + } + + public String[] getColumnNames() { + return columnNames; + } + + @Override + protected PreparedStatement prepareQuery(Connection connection) throws SQLException { + return connection.prepareStatement( + sql, + ResultSet.TYPE_SCROLL_INSENSITIVE, + ResultSet.CONCUR_READ_ONLY + ); + } + + @Override + protected void afterExecuteQuery(ResultSet set) throws SQLException { + super.afterExecuteQuery(set); + + // obtain columnNames + columnNames = getColumnNames(set); + + if (pager != null) { + + // must count rows + int nbRows = getNbRows(set); + if (log.isInfoEnabled()) { + log.info("For request " + sql + ", nb rows = " + nbRows); + } + pager.setRecords(nbRows); + pager.computeIndexesAndPageCount(); + } + } + + @Override + protected Map<String, Object> prepareResult(ResultSet set) throws SQLException { + + if (pager != null) { + + // get row number (getRow() begins at 1) + int rowNumber = set.getRow() - 1; + if (rowNumber < pager.getStartIndex() || + rowNumber >= pager.getEndIndex()) { + + // out of pager bound, by returning null + // result will not be take in account + return null; + } + } + + Map<String, Object> result = getRowAsMap(columnNames, set); + return result; + } + + public Map<String, Object>[] getResult(TopiaContext tx) throws TopiaException { + List<Map<String, Object>> rows = + findMultipleResult((TopiaContextImplementor) tx); + return rows.toArray(new Map[rows.size()]); + } + + + public String[] getColumnNames(TopiaContext tx) throws TopiaException { + findSingleResult((TopiaContextImplementor) tx); + return columnNames; + } + + + } +} Property changes on: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/ExportSqlService.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native