src/main/java/eu/etaxonomy/cdm/vaadin/statement/CdmStatementDelegate.java

   1 // $Id$
   2 /**
   3 * Copyright (C) 2015 EDIT
   4 * European Distributed Institute of Taxonomy
   5 * http://www.e-taxonomy.eu
   6 *
   7 * The contents of this file are subject to the Mozilla Public License Version 1.1
   8 * See LICENSE.TXT at the top of this package for the full license terms.
   9 */
  10 package eu.etaxonomy.cdm.vaadin.statement;
  11
  12 import java.sql.Connection;
  13 import java.sql.SQLException;
  14 import java.util.Collection;
  15 import java.util.List;
  16
  17 import com.vaadin.data.Container.Filter;
  18 import com.vaadin.data.util.filter.Compare;
  19 import com.vaadin.data.util.sqlcontainer.RowItem;
  20 import com.vaadin.data.util.sqlcontainer.SQLUtil;
  21 import com.vaadin.data.util.sqlcontainer.query.FreeformStatementDelegate;
  22 import com.vaadin.data.util.sqlcontainer.query.OrderBy;
  23 import com.vaadin.data.util.sqlcontainer.query.generator.StatementHelper;
  24
  25 /**
  26  * @author cmathew
  27  * @date 10 Mar 2015
  28  *
  29  */
  30 public class CdmStatementDelegate implements FreeformStatementDelegate {
  31
  32     private List<Filter> filters;
  33     private List<OrderBy> orderBys;
  34
  35     private final String select_query;
  36     private final String count_query;
  37     private final String contains_query;
  38
  39     public CdmStatementDelegate(String select_query, String count_query, String contains_query) {
  40         this.select_query = select_query;
  41         this.count_query = count_query;
  42         this.contains_query = contains_query;
  43     }
  44
  45     /* (non-Javadoc)
  46      * @see com.vaadin.data.util.sqlcontainer.query.FreeformQueryDelegate#getQueryString(int, int)
  47      */
  48     @Override
  49     public String getQueryString(int offset, int limit) throws UnsupportedOperationException {
  50         throw new UnsupportedOperationException("Use getQueryStatement method.");
  51     }
  52
  53     /* (non-Javadoc)
  54      * @see com.vaadin.data.util.sqlcontainer.query.FreeformQueryDelegate#getCountQuery()
  55      */
  56     @Override
  57     public String getCountQuery() throws UnsupportedOperationException {
  58         throw new UnsupportedOperationException("Use getCountStatement method.");
  59     }
  60
  61     /* (non-Javadoc)
  62      * @see com.vaadin.data.util.sqlcontainer.query.FreeformQueryDelegate#setFilters(java.util.List)
  63      */
  64     @Override
  65     public void setFilters(List<Filter> filters) throws UnsupportedOperationException {
  66         this.filters = filters;
  67     }
  68
  69
  70     /* (non-Javadoc)
  71      * @see com.vaadin.data.util.sqlcontainer.query.FreeformQueryDelegate#setOrderBy(java.util.List)
  72      */
  73     @Override
  74     public void setOrderBy(List<OrderBy> orderBys) throws UnsupportedOperationException {
  75         this.orderBys = orderBys;
  76     }
  77
  78     /* (non-Javadoc)
  79      * @see com.vaadin.data.util.sqlcontainer.query.FreeformQueryDelegate#storeRow(java.sql.Connection, com.vaadin.data.util.sqlcontainer.RowItem)
  80      */
  81     @Override
  82     public int storeRow(Connection conn, RowItem row) throws UnsupportedOperationException, SQLException {
  83         // TODO Auto-generated method stub
  84         return 0;
  85     }
  86
  87     /* (non-Javadoc)
  88      * @see com.vaadin.data.util.sqlcontainer.query.FreeformQueryDelegate#removeRow(java.sql.Connection, com.vaadin.data.util.sqlcontainer.RowItem)
  89      */
  90     @Override
  91     public boolean removeRow(Connection conn, RowItem row) throws UnsupportedOperationException, SQLException {
  92         // TODO Auto-generated method stub
  93         return false;
  94     }
  95
  96     /* (non-Javadoc)
  97      * @see com.vaadin.data.util.sqlcontainer.query.FreeformQueryDelegate#getContainsRowQueryString(java.lang.Object[])
  98      */
  99     @Override
 100     public String getContainsRowQueryString(Object... keys) throws UnsupportedOperationException {
 101         throw new UnsupportedOperationException("Use getContainsRowQueryStatement method.");
 102     }
 103
 104     /* (non-Javadoc)
 105      * @see com.vaadin.data.util.sqlcontainer.query.FreeformStatementDelegate#getQueryStatement(int, int)
 106      */
 107     @Override
 108     public StatementHelper getQueryStatement(int offset, int limit) throws UnsupportedOperationException {
 109         StatementHelper sh = new StatementHelper();
 110         StringBuffer query = new StringBuffer(select_query);
 111         if (filters != null) {
 112             String filterString = getWhereStringForFilters(filters, sh);
 113             query.append(filterString);
 114         }
 115         query.append(getOrderByString());
 116         if (offset != 0 || limit != 0) {
 117             query.append(" LIMIT ").append(limit);
 118             query.append(" OFFSET ").append(offset);
 119         }
 120         sh.setQueryString(query.toString());
 121         return sh;
 122     }
 123
 124     private String getOrderByString() {
 125         StringBuffer orderBuffer = new StringBuffer("");
 126         if (orderBys != null && !orderBys.isEmpty()) {
 127             orderBuffer.append(" ORDER BY ");
 128             OrderBy lastOrderBy = orderBys.get(orderBys.size() - 1);
 129             for (OrderBy orderBy : orderBys) {
 130                 orderBuffer.append(SQLUtil.escapeSQL(orderBy.getColumn()));
 131                 if (orderBy.isAscending()) {
 132                     orderBuffer.append(" ASC");
 133                 } else {
 134                     orderBuffer.append(" DESC");
 135                 }
 136                 if (orderBy != lastOrderBy) {
 137                     orderBuffer.append(", ");
 138                 }
 139             }
 140         }
 141         return orderBuffer.toString();
 142     }
 143
 144     /* (non-Javadoc)
 145      * @see com.vaadin.data.util.sqlcontainer.query.FreeformStatementDelegate#getCountStatement()
 146      */
 147     @Override
 148     public StatementHelper getCountStatement() throws UnsupportedOperationException {
 149         StatementHelper sh = new StatementHelper();
 150         StringBuffer query = new StringBuffer(count_query);
 151         if (filters != null) {
 152             String filterString = getWhereStringForFilters(filters, sh);
 153             query.append(filterString);
 154         }
 155         sh.setQueryString(query.toString());
 156         return sh;
 157     }
 158
 159     /* (non-Javadoc)
 160      * @see com.vaadin.data.util.sqlcontainer.query.FreeformStatementDelegate#getContainsRowQueryStatement(java.lang.Object[])
 161      */
 162     @Override
 163     public StatementHelper getContainsRowQueryStatement(Object... keys) throws UnsupportedOperationException {
 164         StatementHelper sh = new StatementHelper();
 165         StringBuffer query = new StringBuffer(contains_query);
 166         sh.addParameterValue(keys[0]);
 167         sh.setQueryString(query.toString());
 168         return sh;
 169     }
 170
 171     public static String getWhereStringForFilters(List<Filter> filters,
 172             StatementHelper sh) {
 173         if (filters == null || filters.isEmpty()) {
 174             return "";
 175         }
 176         StringBuilder where = new StringBuilder(" WHERE ");
 177         where.append(getJoinedFilterString(filters, "AND", sh));
 178         return where.toString();
 179     }
 180
 181     public static String getJoinedFilterString(Collection<Filter> filters,
 182             String joinString, StatementHelper sh) {
 183         StringBuilder result = new StringBuilder();
 184         for (Filter f : filters) {
 185             result.append(getWhereStringForFilter(f, sh));
 186             result.append(" ").append(joinString).append(" ");
 187         }
 188         // Remove the last instance of joinString
 189         result.delete(result.length() - joinString.length() - 2,
 190                 result.length());
 191         return result.toString();
 192     }
 193
 194     public static String getWhereStringForFilter(Filter filter, StatementHelper sh) {
 195         Compare compare = (Compare) filter;
 196         sh.addParameterValue(compare.getValue());
 197         String prop = compare.getPropertyId().toString();
 198         switch (compare.getOperation()) {
 199         case EQUAL:
 200             return prop + " = ?";
 201         case GREATER:
 202             return prop + " > ?";
 203         case GREATER_OR_EQUAL:
 204             return prop + " >= ?";
 205         case LESS:
 206             return prop + " < ?";
 207         case LESS_OR_EQUAL:
 208             return prop + " <= ?";
 209         default:
 210             return "";
 211         }
 212     }
 213
 214 }