Example usage for org.springframework.jdbc.core JdbcTemplate queryForList

List of usage examples for org.springframework.jdbc.core JdbcTemplate queryForList

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.*
  5. JdbcTemplate
  6. queryForList

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate queryForList.

Prototype

@Override
    public List<Map<String, Object>> queryForList(String sql) throws DataAccessException

Source Link

Usage

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@Override
public HashMap<ProfileRecipientFields, ValidatorResults> getRecipientsByType(int adminID, Integer[] types,
        int datasource_id) {
    final JdbcTemplate aTemplate = getJdbcTemplateForTemporaryTable();
    final String prefix = "cust_" + adminID + "_tmp_";
    final String tableName = prefix + datasource_id + "_tbl";
    final String typesAsString = StringUtils.join(types, ",");
    String sqlStatement = "SELECT recipient, validator_result FROM " + tableName + " "
            + "WHERE status_type IN (" + typesAsString + ")";
    List<Map> resultList = aTemplate.queryForList(sqlStatement);
    HashMap<ProfileRecipientFields, ValidatorResults> recipients = new HashMap<ProfileRecipientFields, ValidatorResults>();
    for (Map row : resultList) {
        Object recipientBean = ImportUtils.deserialiseBean((byte[]) row.get("recipient"));
        final ProfileRecipientFields recipient = (ProfileRecipientFields) recipientBean;
        ValidatorResults validatorResults = null;
        if (row.get("validator_result") != null) {
            Object validatorResultsBean = ImportUtils.deserialiseBean((byte[]) row.get("validator_result"));
            validatorResults = (ValidatorResults) validatorResultsBean;
        }/*w  w w  .  jav a2  s .co m*/
        recipients.put(recipient, validatorResults);
    }

    return recipients;
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@Override
public List<String> getTemporaryTableNamesBySessionId(String sessionId) {
    List<String> result = new ArrayList<String>();
    final JdbcTemplate template = createJdbcTemplate();
    String query = "SELECT TEMPORARY_TABLE_NAME FROM IMPORT_TEMPORARY_TABLES WHERE SESSION_ID='" + sessionId
            + "'";
    List<Map> resultList = template.queryForList(query);
    for (Map row : resultList) {
        final String temporaryTableName = (String) row.get("TEMPORARY_TABLE_NAME");
        result.add(temporaryTableName);/* w ww .j  av a  2s .com*/
    }
    return result;
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@Override
public HashMap<ProfileRecipientFields, ValidatorResults> getRecipientsByTypePaginated(Integer[] types, int page,
        int rownums, Integer adminID, int datasourceId) {
    HashMap<ProfileRecipientFields, ValidatorResults> recipients = new HashMap<ProfileRecipientFields, ValidatorResults>();
    if (types == null || types.length == 0) {
        return recipients;
    }//from   www .j  a  v  a2  s . com

    final JdbcTemplate aTemplate = getJdbcTemplateForTemporaryTable();
    final String prefix = "cust_" + adminID + "_tmp_";
    final String tableName = prefix + datasourceId + "_tbl";
    String typesStr = "(" + StringUtils.join(types, ",") + ")";
    int offset = (page) * rownums;
    String sqlStatement = "SELECT * FROM " + tableName + " WHERE status_type IN " + typesStr;
    if (AgnUtils.isMySQLDB()) {
        sqlStatement = sqlStatement + " LIMIT  " + offset + " , " + rownums;
    }
    if (AgnUtils.isOracleDB()) {
        sqlStatement = "SELECT * FROM ( SELECT recipient, validator_result, rownum r FROM ( " + sqlStatement
                + " )  WHERE 1=1 ) WHERE r BETWEEN " + (offset + 1) + " AND " + (offset + rownums);
    }
    List<Map> tmpList = aTemplate.queryForList(sqlStatement);

    for (Map row : tmpList) {
        Object recipientBean = ImportUtils.deserialiseBean((byte[]) row.get("recipient"));
        final ProfileRecipientFields recipient = (ProfileRecipientFields) recipientBean;
        ValidatorResults validatorResults = null;
        if (row.get("validator_result") != null) {
            Object validatorResultsBean = ImportUtils.deserialiseBean((byte[]) row.get("validator_result"));
            validatorResults = (ValidatorResults) validatorResultsBean;
        }
        recipients.put(recipient, validatorResults);
    }

    return recipients;
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@Override
public PaginatedList getInvalidRecipientList(CSVColumnState[] columns, String sort, String direction, int page,
        int rownums, int previousFullListSize, Integer adminID, int datasource_id) throws Exception {
    final JdbcTemplate aTemplate = getJdbcTemplateForTemporaryTable();
    final String prefix = "cust_" + adminID + "_tmp_";
    final String tableName = prefix + datasource_id + "_tbl";
    int totalRows = aTemplate.queryForInt("SELECT count(temporary_id) FROM " + tableName + " WHERE status_type="
            + NewImportWizardService.RECIPIENT_TYPE_FIELD_INVALID);
    if (previousFullListSize == 0 || previousFullListSize != totalRows) {
        page = 1;/*from w  w  w.  jav  a 2 s .  c  om*/
    }

    int offset = (page - 1) * rownums;
    String sqlStatement = "SELECT * FROM " + tableName + " where status_type="
            + NewImportWizardService.RECIPIENT_TYPE_FIELD_INVALID;
    if (AgnUtils.isMySQLDB()) {
        sqlStatement = sqlStatement + " LIMIT  " + offset + " , " + rownums;
    }
    if (AgnUtils.isOracleDB()) {
        sqlStatement = "SELECT * from ( select recipient, validator_result, rownum r from ( " + sqlStatement
                + " )  where 1=1 ) where r between " + (offset + 1) + " and " + (offset + rownums);
    }
    List<Map> tmpList = aTemplate.queryForList(sqlStatement);

    List<Map> result = new ArrayList<Map>();
    for (Map row : tmpList) {
        Map newBean = new HashMap();
        final ProfileRecipientFields recipient = (ProfileRecipientFields) ImportUtils
                .deserialiseBean((byte[]) row.get("recipient"));
        final ValidatorResults validatorResult = (ValidatorResults) ImportUtils
                .deserialiseBean((byte[]) row.get("validator_result"));
        for (CSVColumnState column : columns) {
            if (column.getImportedColumn()) {
                newBean.put(column.getColName(), Toolkit.getValueFromBean(recipient, column.getColName()));
            }
        }
        newBean.put(NewImportWizardService.VALIDATOR_RESULT_RESERVED, validatorResult);
        newBean.put(NewImportWizardService.ERROR_EDIT_RECIPIENT_EDIT_RESERVED, recipient);
        result.add(newBean);
    }

    PaginatedListImpl paginatedList = new PaginatedListImpl(result, totalRows, rownums, page, sort, direction);
    return paginatedList;
}

From source file:org.agnitas.dao.impl.MailingDaoImpl.java

public PaginatedList getMailingList(int companyID, String types, boolean isTemplate, String sort,
        String direction, int page, int rownums) {

    JdbcTemplate aTemplate = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
    List<String> charColumns = Arrays.asList(new String[] { "shortname", "description", "mailinglist" });

    int offset = (page - 1) * rownums;

    String mailingTypes = "  AND  mailing_type in (" + types + ") ";
    if (isTemplate) {
        mailingTypes = " ";
    }/*w  w  w . ja  va 2s  .  c o  m*/

    String orderby = null;
    String defaultorder = " send_null ASC, senddate DESC, mailing_id DESC  ";
    if (sort != null && !"".equals(sort.trim())) {
        orderby = getUpperSort(charColumns, sort);
        orderby = orderby + " " + direction;
    } else {
        orderby = defaultorder;
    }

    String sqlStatement = " SELECT *, case when senddate is null then 0 else 1 end as send_null "
            + " FROM (   SELECT a.mailing_id , a.shortname  , a.description ,   min(c."
            + AgnUtils.changeDateName() + ") senddate, m.shortname mailinglist "
            + " FROM  (mailing_tbl  a LEFT JOIN mailing_account_tbl c ON (a.mailing_id=c.mailing_id AND c.status_field='W')) "
            + " LEFT JOIN mailinglist_tbl m ON (  a.mailinglist_id=m.mailinglist_id AND  a.company_id=m.company_id) "
            + "  WHERE a.company_id = " + companyID + " AND a.deleted<>1 AND a.is_template="
            + (isTemplate ? 1 : 0) + mailingTypes
            + "  GROUP BY  a.mailing_id, a.shortname, a.description, m.shortname ) openemm ORDER BY " + orderby;

    int totalsize = aTemplate.queryForInt("select count(*) from ( " + sqlStatement + ") agn");

    sqlStatement = sqlStatement + " LIMIT " + offset + " , " + rownums;

    List<Map> tmpList = aTemplate.queryForList(sqlStatement);

    DynaProperty[] properties = new DynaProperty[] { new DynaProperty("mailingid", Long.class),
            new DynaProperty("shortname", String.class), new DynaProperty("description", String.class),
            new DynaProperty("mailinglist", String.class), new DynaProperty("senddate", Timestamp.class) };
    BasicDynaClass dynaClass = new BasicDynaClass("mailing", null, properties);

    List<DynaBean> result = new ArrayList<DynaBean>();
    for (Map row : tmpList) {
        DynaBean newBean;
        try {
            newBean = dynaClass.newInstance();

            newBean.set("mailingid", row.get("MAILING_ID"));
            newBean.set("shortname", row.get("SHORTNAME"));
            newBean.set("description", row.get("DESCRIPTION"));
            newBean.set("mailinglist", row.get("MAILINGLIST"));
            newBean.set("senddate", row.get("SENDDATE"));
            result.add(newBean);
        } catch (IllegalAccessException e) {
            AgnUtils.logger().error("IllegalAccessException: " + e);
            AgnUtils.logger().error(AgnUtils.getStackTrace(e));

        } catch (InstantiationException e) {
            AgnUtils.logger().error("InstantiationException: " + e);
            AgnUtils.logger().error(AgnUtils.getStackTrace(e));
        }
    }

    DynaBeanPaginatedListImpl paginatedList = new DynaBeanPaginatedListImpl(result, totalsize, rownums, page,
            sort, direction);

    return paginatedList;
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

/**
 * Find Subscriber by providing a column-name and a value. Only exact machtes possible.
 *
 * @return customerID or 0 if no matching record found
 * @param col Column-Name// ww  w .  j  a  va  2 s .  c  o  m
 * @param value Value to search for in col
 */
@Override
public int findByKeyColumn(Recipient cust, String col, String value) {
    int val = 0;
    String aType = null;
    String getCust = null;

    try {
        if (cust.getCustDBStructure() == null) {
            cust.loadCustDBStructure();
        }

        if ("email".equalsIgnoreCase(col)) {
            value = AgnUtils.normalizeEmail(value);
        }

        aType = (String) cust.getCustDBStructure().get(col);

        if (aType != null) {
            if (aType.equalsIgnoreCase("DECIMAL") || aType.equalsIgnoreCase("INTEGER")
                    || aType.equalsIgnoreCase("DOUBLE")) {
                try {
                    val = Integer.parseInt(value);
                } catch (Exception e) {
                    val = 0;
                }
                getCust = "SELECT customer_id FROM customer_" + cust.getCompanyID() + "_tbl cust WHERE cust."
                        + SafeString.getSQLSafeString(col, 30) + "=" + val;
            }

            if (aType.equalsIgnoreCase("VARCHAR") || aType.equalsIgnoreCase("CHAR")) {
                getCust = "SELECT customer_id FROM customer_" + cust.getCompanyID() + "_tbl cust WHERE cust."
                        + SafeString.getSQLSafeString(col, 30) + "='" + SafeString.getSQLSafeString(value)
                        + "'";
            }

            if (logger.isInfoEnabled()) {
                logger.info("RecipientDaoImpl:findByKeyColumn: " + getCust);
            }

            JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
            // cannot use queryForInt, because of possible existing doublettes
            @SuppressWarnings("unchecked")
            List<Map<String, Integer>> custList = tmpl.queryForList(getCust);
            if (custList.size() > 0) {
                Map<String, Object> map = new CaseInsensitiveMap<Object>(custList.get(0));
                cust.setCustomerID(((Number) map.get("customer_id")).intValue());
            } else {
                cust.setCustomerID(0);
            }
        }
    } catch (Exception e) {
        logger.error("findByKeyColumn (sql: " + getCust + ")", e);
        cust.setCustomerID(0);
    }
    return cust.getCustomerID();
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

@Override
public int findByColumn(int companyID, String col, String value) {
    Recipient cust = (Recipient) applicationContext.getBean("Recipient");
    cust.setCompanyID(companyID);//from  ww  w  . j ava2 s.  c o  m
    int custID = 0;
    int val = 0;
    String aType = null;
    String getCust = null;

    if (cust.getCustDBStructure() == null) {
        cust.loadCustDBStructure();
    }
    if (col.toLowerCase().equals("email")) {
        value = value.toLowerCase();
    }

    aType = (String) cust.getCustDBStructure().get(col.toLowerCase());

    if (aType != null) {
        if (aType.equalsIgnoreCase("VARCHAR") || aType.equalsIgnoreCase("CHAR")) {
            getCust = "select customer_id from customer_" + companyID + "_tbl cust where lower(cust."
                    + SafeString.getSQLSafeString(col, 30) + ")=lower('" + SafeString.getSQLSafeString(value)
                    + "')";
        } else {
            try {
                val = Integer.parseInt(value);
            } catch (Exception e) {
                val = 0;
            }
            getCust = "select customer_id from customer_" + companyID + "_tbl cust where cust."
                    + SafeString.getSQLSafeString(col, 30) + "=" + val;
        }
        try {
            JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
            //custID = tmpl.queryForInt(getCust);
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> results = tmpl.queryForList(getCust);
            if (results.size() > 0) {
                Map<String, Object> map = results.get(0);
                custID = ((Number) map.get("customer_id")).intValue();
            }
        } catch (Exception e) {
            custID = 0;
        }
    }
    return custID;
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

/**
 * Loads complete Mailinglist-Binding-Information for given customer-id from Database
 *
 * @return Map with key/value-pairs as combinations of mailinglist-id and BindingEntry-Objects
 *///  w  w  w  .  j  a  v a2s .c  o m
@Override
public Map<Integer, Map<Integer, BindingEntry>> loadAllListBindings(int companyID, int customerID) {
    Recipient cust = (Recipient) applicationContext.getBean("Recipient");
    cust.setListBindings(new Hashtable<Integer, Map<Integer, BindingEntry>>()); // MailingList_ID as keys
    Map<Integer, BindingEntry> mTable = new Hashtable<Integer, BindingEntry>(); // Media_ID as key, contains rest of data (user type, status etc.)
    String sqlGetLists = null;
    BindingEntry aEntry = null;
    int tmpMLID = 0;

    try {
        sqlGetLists = "SELECT mailinglist_id, user_type, user_status, user_remark, " + AgnUtils.changeDateName()
                + ", mediatype FROM customer_" + companyID + "_binding_tbl WHERE customer_id=" + customerID
                + " ORDER BY mailinglist_id, mediatype";
        JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> list = tmpl.queryForList(sqlGetLists);
        Iterator<Map<String, Object>> i = list.iterator();

        while (i.hasNext()) {
            Map<String, Object> map = i.next();
            int listID = ((Number) map.get("mailinglist_id")).intValue();
            Integer mediaType = new Integer(((Number) map.get("mediatype")).intValue());

            aEntry = (BindingEntry) applicationContext.getBean("BindingEntry");
            aEntry.setCustomerID(customerID);
            aEntry.setMailinglistID(listID);
            aEntry.setUserType((String) map.get("user_type"));
            aEntry.setUserStatus(((Number) map.get("user_status")).intValue());
            aEntry.setUserRemark((String) map.get("user_remark"));
            aEntry.setChangeDate((java.sql.Timestamp) map.get(AgnUtils.changeDateName()));
            aEntry.setMediaType(mediaType.intValue());

            if (tmpMLID != listID) {
                if (tmpMLID != 0) {
                    cust.getListBindings().put(tmpMLID, mTable);
                    mTable = new Hashtable<Integer, BindingEntry>();
                    mTable.put(mediaType, aEntry);
                    tmpMLID = listID;
                } else {
                    mTable.put(mediaType, aEntry);
                    tmpMLID = listID;
                }
            } else {
                mTable.put(mediaType, aEntry);
            }
        }
        cust.getListBindings().put(tmpMLID, mTable);
    } catch (Exception e) {
        logger.error("loadAllListBindings: " + sqlGetLists, e);
        AgnUtils.sendExceptionMail("sql:" + sqlGetLists, e);
        return null;
    }
    return cust.getListBindings();
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

/**
 * Checks if E-Mail-Adress given in customerData-HashMap is registered in blacklist(s)
 *
 * @return true if E-Mail-Adress is blacklisted
 *///from  www  .j a va 2 s .com
@Override
public boolean blacklistCheck(String email, int companyID) {
    boolean returnValue = false;
    String sqlSelect = null;

    try {
        JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
        sqlSelect = "SELECT email FROM cust_ban_tbl WHERE '" + SafeString.getSQLSafeString(email)
                + "' LIKE email";
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> list = tmpl.queryForList(sqlSelect);
        if (list.size() > 0) {
            returnValue = true;
        }
        if (AgnUtils.isProjectEMM()) {
            sqlSelect = "SELECT email FROM cust" + companyID + "_ban_tbl WHERE '"
                    + SafeString.getSQLSafeString(email) + "' LIKE email";
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> list2 = tmpl.queryForList(sqlSelect);
            if (list2.size() > 0) {
                returnValue = true;
            }
        }
    } catch (Exception e) {
        logger.error("blacklistCheck: " + sqlSelect, e);
        AgnUtils.sendExceptionMail("sql:" + sqlSelect, e);
        returnValue = true;
    }
    return returnValue;
}

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

@Override
public Map<Integer, String> getAdminAndTestRecipientsDescription(int companyId, int mailingId) {
    String sql = "SELECT bind.customer_id, cust.email, cust.firstname, cust.lastname FROM mailing_tbl mail, "
            + "customer_" + companyId + "_tbl cust, customer_" + companyId + "_binding_tbl bind WHERE "
            + "bind.user_type in ('A', 'T') AND bind.user_status=1 AND bind.mailinglist_id="
            + "mail.mailinglist_id AND bind.customer_id=cust.customer_id and mail.mailing_id=" + mailingId
            + " ORDER BY bind.user_type, bind.customer_id";
    JdbcTemplate jdbcTemplate = new JdbcTemplate((DataSource) applicationContext.getBean("dataSource"));
    @SuppressWarnings("unchecked")
    List<Map<String, Object>> tmpList = jdbcTemplate.queryForList(sql);
    HashMap<Integer, String> result = new HashMap<Integer, String>();
    for (Map<String, Object> map : tmpList) {
        int id = ((Number) map.get("customer_id")).intValue();
        String email = (String) map.get("email");
        String firstName = (String) map.get("firstname");
        String lastName = (String) map.get("lastname");

        if (firstName == null)
            firstName = "";

        if (lastName == null)
            lastName = "";

        result.put(id, firstName + " " + lastName + " &lt;" + email + "&gt;");
    }/*w  w  w . j  av a2 s  . c om*/
    return result;
}