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.TitleDaoImpl.java

@Override
public List<Title> getTitles(int companyID) {
    List<Title> result = new ArrayList<Title>();
    JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
    String sql = "select distinct(title_id), description from title_tbl where company_id in (0," + companyID
            + ") ORDER by description";
    List list = jdbcTemplate.queryForList(sql);
    if (list.size() > 0) {
        for (int i = 0; i < list.size(); i++) {
            Map map = (Map) list.get(i);
            Title title = new TitleImpl();
            title.setCompanyID(companyID);
            title.setDescription((String) map.get("description"));
            title.setId(((Number) map.get("title_id")).intValue());
            result.add(title);//w  ww .j  a va 2s.  c  o  m
        }
    }
    return result;
}

From source file:org.agnitas.web.CampaignAction.java

/**
 * loads the campaigns/*w  w w .j a  va  2s .c  o  m*/
 * @throws InstantiationException 
 * @throws IllegalAccessException 
 *   
 * 
 */

public List<DynaBean> getCampaignList(HttpServletRequest request)
        throws IllegalAccessException, InstantiationException {
    ApplicationContext aContext = getWebApplicationContext();
    JdbcTemplate aTemplate = new JdbcTemplate((DataSource) aContext.getBean("dataSource"));

    List<Integer> charColumns = Arrays.asList(new Integer[] { 0, 1 });
    String[] columns = new String[] { "shortname", "description", "" };

    int sortcolumnindex = 0;
    if (request.getParameter(
            new ParamEncoder("campaign").encodeParameterName(TableTagParameters.PARAMETER_SORT)) != null) {
        sortcolumnindex = Integer.parseInt(request.getParameter(
                new ParamEncoder("campaign").encodeParameterName(TableTagParameters.PARAMETER_SORT)));
    }

    String sort = columns[sortcolumnindex];
    if (charColumns.contains(sortcolumnindex)) {
        sort = "upper( " + sort + " )";
    }

    int order = 1;
    if (request.getParameter(
            new ParamEncoder("campaign").encodeParameterName(TableTagParameters.PARAMETER_ORDER)) != null) {
        order = new Integer(request.getParameter(
                new ParamEncoder("campaign").encodeParameterName(TableTagParameters.PARAMETER_ORDER)));
    }

    String sqlStatement = "SELECT campaign_id, shortname, description FROM campaign_tbl WHERE company_id="
            + AgnUtils.getCompanyID(request) + " ORDER BY " + sort + " " + (order == 2 ? "DESC" : "ASC");
    List<Map> tmpList = aTemplate.queryForList(sqlStatement);

    DynaProperty[] properties = new DynaProperty[] { new DynaProperty("campaignId", Integer.class),
            new DynaProperty("shortname", String.class), new DynaProperty("description", String.class) };

    if (AgnUtils.isOracleDB()) {
        properties = new DynaProperty[] { new DynaProperty("campaignId", BigDecimal.class),
                new DynaProperty("shortname", String.class), new DynaProperty("description", String.class) };
    }

    BasicDynaClass dynaClass = new BasicDynaClass("campaign", null, properties);

    List<DynaBean> result = new ArrayList<DynaBean>();
    for (Map row : tmpList) {
        DynaBean newBean = dynaClass.newInstance();
        newBean.set("campaignId", row.get("CAMPAIGN_ID"));
        newBean.set("shortname", row.get("SHORTNAME"));
        newBean.set("description", row.get("DESCRIPTION"));
        result.add(newBean);

    }
    return result;
}

From source file:org.agnitas.web.EmmActionAction.java

public List<DynaBean> getActionList(HttpServletRequest request)
        throws IllegalAccessException, InstantiationException {
    ApplicationContext aContext = getWebApplicationContext();
    JdbcTemplate aTemplate = new JdbcTemplate((DataSource) aContext.getBean("dataSource"));
    List<Integer> charColumns = Arrays.asList(new Integer[] { 0, 1 });
    String[] columns = new String[] { "r.shortname", "r.description", "", "" };

    int sortcolumnindex = 0;
    if (request.getParameter(
            new ParamEncoder("emmaction").encodeParameterName(TableTagParameters.PARAMETER_SORT)) != null) {
        sortcolumnindex = Integer.parseInt(request.getParameter(
                new ParamEncoder("emmaction").encodeParameterName(TableTagParameters.PARAMETER_SORT)));
    }//from  w w  w.  j a va 2 s.  c om

    String sort = columns[sortcolumnindex];
    if (charColumns.contains(sortcolumnindex)) {
        sort = "upper( " + sort + " )";
    }

    int order = 1;
    if (request.getParameter(
            new ParamEncoder("emmaction").encodeParameterName(TableTagParameters.PARAMETER_ORDER)) != null) {
        order = new Integer(request.getParameter(
                new ParamEncoder("emmaction").encodeParameterName(TableTagParameters.PARAMETER_ORDER)));
    }

    String sqlStatement = "SELECT r.action_id, r.shortname, r.description, count(u.form_id) used "
            + " FROM rdir_action_tbl r LEFT JOIN userform_tbl u ON (u.startaction_id = r.action_id or u.endaction_id = r.action_id) "
            + " WHERE r.company_id= " + AgnUtils.getCompanyID(request)
            + " GROUP BY  r.action_id, r.shortname, r.description " + " ORDER BY " + sort + " "
            + (order == 1 ? "ASC" : "DESC");

    List<Map> tmpList = aTemplate.queryForList(sqlStatement);
    DynaProperty[] properties = new DynaProperty[] { new DynaProperty("actionId", Long.class),
            new DynaProperty("shortname", String.class), new DynaProperty("description", String.class),
            new DynaProperty("used", Long.class) };

    if (AgnUtils.isOracleDB()) {
        properties = new DynaProperty[] { new DynaProperty("actionId", BigDecimal.class),
                new DynaProperty("shortname", String.class), new DynaProperty("description", String.class),
                new DynaProperty("used", BigDecimal.class) };
    }

    BasicDynaClass dynaClass = new BasicDynaClass("emmaction", null, properties);

    List<DynaBean> result = new ArrayList<DynaBean>();
    for (Map row : tmpList) {
        DynaBean newBean = dynaClass.newInstance();
        newBean.set("actionId", row.get("ACTION_ID"));
        newBean.set("shortname", row.get("SHORTNAME"));
        newBean.set("description", row.get("DESCRIPTION"));
        newBean.set("used", row.get("USED"));
        result.add(newBean);
    }
    return result;
}

From source file:org.agnitas.web.MailinglistAction.java

/**
  * Load Mailing lists from database into paginated list.
  *//*  ww  w  .  j  a  v  a 2 s  .  c o m*/
public PaginatedList getMailinglist(HttpServletRequest request, MailinglistForm aForm)
        throws IllegalAccessException, InstantiationException {

    ApplicationContext aContext = getWebApplicationContext();
    JdbcTemplate aTemplate = new JdbcTemplate(dataSource);
    List<Integer> charColumns = Arrays.asList(new Integer[] { 1, 2 });
    String direction = request.getParameter("dir");
    String sort = getSort(request, aForm);

    String sortForQuery = StringUtils.isEmpty(sort) ? "shortname" : sort;
    String upperSort = "upper( " + sortForQuery + " )";

    int rownums = aForm.getNumberofRows() >= 0 ? aForm.getNumberofRows() : 0;

    if (direction == null) {
        direction = aForm.getOrder();
    } else {
        aForm.setOrder(direction);
    }

    String pageStr = request.getParameter("page");
    if (pageStr == null || "".equals(pageStr.trim())) {
        if (aForm.getPage() == null || "".equals(aForm.getPage().trim())) {
            aForm.setPage("1");
        }
        pageStr = aForm.getPage();
    } else {
        aForm.setPage(pageStr);
    }

    if (aForm.isNumberOfRowsChanged()) {
        aForm.setPage("1");
        aForm.setNumberOfRowsChanged(false);
        pageStr = "1";
    }

    String sqlStatement = "SELECT mailinglist_id, shortname, description ";
    String sqlStatementFrompart = " FROM mailinglist_tbl WHERE company_id=" + AgnUtils.getCompanyID(request);
    String orderBypart = " ORDER BY " + upperSort + " " + direction;

    int totalRows = aTemplate.queryForInt("SELECT count(mailinglist_id) " + sqlStatementFrompart);

    Integer page = Integer.parseInt(pageStr);
    page = AgnUtils.getValidPageNumber(totalRows, page, rownums);
    aForm.setPage(page.toString());

    int offset = (page - 1) * rownums;

    if (AgnUtils.isOracleDB()) {
        sqlStatement += ", rownum r";
        sqlStatement = "SELECT * FROM (" + sqlStatement + sqlStatementFrompart + orderBypart
                + ") WHERE r between " + (offset + 1) + " and " + (offset + rownums);
    }
    if (AgnUtils.isMySQLDB()) {
        sqlStatement = sqlStatement + sqlStatementFrompart + orderBypart + " LIMIT  " + offset + " , "
                + rownums;
    }

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

    List<Mailinglist> result = new ArrayList<Mailinglist>();

    for (Map row : tmpList) {

        Mailinglist newBean = new MailinglistImpl();
        newBean.setId(((Number) row.get("MAILINGLIST_ID")).intValue());
        newBean.setShortname((String) row.get("SHORTNAME"));
        newBean.setDescription((String) row.get("DESCRIPTION"));
        result.add(newBean);

    }

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

}

From source file:org.agnitas.web.MailingStatAction.java

public List<DynaBean> getMailingStats(HttpServletRequest request)
        throws IllegalAccessException, InstantiationException {

    ApplicationContext aContext = getWebApplicationContext();
    JdbcTemplate aTemplate = new JdbcTemplate((DataSource) aContext.getBean("dataSource"));

    String sqlStatement = "SELECT a.mailing_id, a.shortname, a.description, b.shortname AS listname "
            + "FROM mailing_tbl a, mailinglist_tbl b WHERE a.company_id=" + AgnUtils.getCompanyID(request) + " "
            + "AND a.mailinglist_id=b.mailinglist_id AND a.deleted=0 AND a.is_template=0 ORDER BY mailing_id DESC";

    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("listname", String.class), };
    if (AgnUtils.isOracleDB()) {
        properties = new DynaProperty[] { new DynaProperty("mailingid", BigDecimal.class),
                new DynaProperty("shortname", String.class), new DynaProperty("description", String.class),
                new DynaProperty("listname", String.class), };
    }/*from  w w  w.j  a v a  2 s .co m*/
    BasicDynaClass dynaClass = new BasicDynaClass("mailingstat", null, properties);
    List<DynaBean> result = new ArrayList<DynaBean>();
    for (Map row : tmpList) {
        DynaBean newBean = dynaClass.newInstance();
        newBean.set("mailingid", row.get("MAILING_ID"));
        newBean.set("shortname", row.get("SHORTNAME"));
        newBean.set("description", row.get("DESCRIPTION"));
        newBean.set("listname", row.get("LISTNAME"));
        result.add(newBean);
    }

    return result;
}

From source file:org.agnitas.web.UserFormEditAction.java

public List<DynaBean> getUserFromList(HttpServletRequest request)
        throws IllegalAccessException, InstantiationException {
    ApplicationContext aContext = getWebApplicationContext();
    JdbcTemplate aTemplate = new JdbcTemplate((DataSource) aContext.getBean("dataSource"));

    String sqlStatement = "SELECT form_id, formname, description FROM userform_tbl WHERE company_id="
            + AgnUtils.getCompanyID(request) + " ORDER BY formname";

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

    DynaProperty[] properties = new DynaProperty[] { new DynaProperty("formid", Long.class),
            new DynaProperty("formname", String.class), new DynaProperty("description", String.class) };

    if (AgnUtils.isOracleDB()) {
        properties = new DynaProperty[] { new DynaProperty("formid", BigDecimal.class),
                new DynaProperty("formname", String.class), new DynaProperty("description", String.class) };
    }//from w ww.j a va  2 s .  c om

    BasicDynaClass dynaClass = new BasicDynaClass("userform", null, properties);

    List<DynaBean> result = new ArrayList<DynaBean>();
    for (Map row : tmpList) {
        DynaBean newBean = dynaClass.newInstance();
        newBean.set("formid", row.get("FORM_ID"));
        newBean.set("formname", row.get("FORMNAME"));
        newBean.set("description", row.get("DESCRIPTION"));
        result.add(newBean);
    }
    return result;
}

From source file:org.apache.syncope.core.logic.report.AuditReportlet.java

private void doExtractConf(final ContentHandler handler) throws SAXException {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
    jdbcTemplate.setMaxRows(conf.getSize());
    List<Map<String, Object>> rows = jdbcTemplate
            .queryForList("SELECT * FROM SYNCOPEAUDIT ORDER BY EVENT_DATE DESC");

    handler.startElement("", "", "events", null);
    AttributesImpl atts = new AttributesImpl();
    for (Map<String, Object> row : rows) {
        AuditEntry auditEntry = POJOHelper.deserialize(row.get("MESSAGE").toString(), AuditEntry.class);

        atts.clear();/* ww w .  jav a 2 s .com*/
        if (StringUtils.isNotBlank(auditEntry.getWho())) {
            atts.addAttribute("", "", "who", ReportXMLConst.XSD_STRING, auditEntry.getWho());
        }
        handler.startElement("", "", "event", atts);

        atts.clear();
        if (StringUtils.isNotBlank(auditEntry.getLogger().getCategory())) {
            atts.addAttribute("", "", "category", ReportXMLConst.XSD_STRING,
                    auditEntry.getLogger().getCategory());
        }
        if (StringUtils.isNotBlank(auditEntry.getLogger().getSubcategory())) {
            atts.addAttribute("", "", "subcategory", ReportXMLConst.XSD_STRING,
                    auditEntry.getLogger().getSubcategory());
        }
        if (StringUtils.isNotBlank(auditEntry.getLogger().getEvent())) {
            atts.addAttribute("", "", "event", ReportXMLConst.XSD_STRING, auditEntry.getLogger().getEvent());
        }
        if (auditEntry.getLogger().getResult() != null) {
            atts.addAttribute("", "", "result", ReportXMLConst.XSD_STRING,
                    auditEntry.getLogger().getResult().name());
        }
        handler.startElement("", "", "logger", atts);
        handler.endElement("", "", "logger");

        if (auditEntry.getBefore() != null) {
            char[] before = ToStringBuilder
                    .reflectionToString(auditEntry.getBefore(), ToStringStyle.MULTI_LINE_STYLE).toCharArray();
            handler.startElement("", "", "before", null);
            handler.characters(before, 0, before.length);
            handler.endElement("", "", "before");
        }

        if (auditEntry.getInput() != null) {
            handler.startElement("", "", "inputs", null);
            for (Object inputObj : auditEntry.getInput()) {
                char[] input = ToStringBuilder.reflectionToString(inputObj, ToStringStyle.MULTI_LINE_STYLE)
                        .toCharArray();
                handler.startElement("", "", "input", null);
                handler.characters(input, 0, input.length);
                handler.endElement("", "", "input");
            }
            handler.endElement("", "", "inputs");
        }

        if (auditEntry.getOutput() != null) {
            char[] output = ToStringBuilder
                    .reflectionToString(auditEntry.getOutput(), ToStringStyle.MULTI_LINE_STYLE).toCharArray();
            handler.startElement("", "", "output", null);
            handler.characters(output, 0, output.length);
            handler.endElement("", "", "output");
        }

        handler.startElement("", "", "throwable", null);
        char[] throwable = row.get("THROWABLE").toString().toCharArray();
        handler.characters(throwable, 0, throwable.length);
        handler.endElement("", "", "throwable");

        handler.endElement("", "", "event");
    }
    handler.endElement("", "", "events");
}

From source file:org.apache.syncope.core.provisioning.java.job.report.AuditReportlet.java

private void doExtractConf(final ContentHandler handler) throws SAXException {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
    jdbcTemplate.setMaxRows(conf.getSize());
    List<Map<String, Object>> rows = jdbcTemplate
            .queryForList("SELECT * FROM SYNCOPEAUDIT ORDER BY EVENT_DATE DESC");

    handler.startElement("", "", "events", null);
    AttributesImpl atts = new AttributesImpl();
    for (Map<String, Object> row : rows) {
        AuditEntry auditEntry = POJOHelper.deserialize(row.get("MESSAGE").toString(), AuditEntry.class);

        atts.clear();//from   w w w . j a  va  2s .  co  m
        if (StringUtils.isNotBlank(auditEntry.getWho())) {
            atts.addAttribute("", "", "who", ReportXMLConst.XSD_STRING, auditEntry.getWho());
        }
        handler.startElement("", "", "event", atts);

        atts.clear();
        if (StringUtils.isNotBlank(auditEntry.getLogger().getCategory())) {
            atts.addAttribute("", "", "category", ReportXMLConst.XSD_STRING,
                    auditEntry.getLogger().getCategory());
        }
        if (StringUtils.isNotBlank(auditEntry.getLogger().getSubcategory())) {
            atts.addAttribute("", "", "subcategory", ReportXMLConst.XSD_STRING,
                    auditEntry.getLogger().getSubcategory());
        }
        if (StringUtils.isNotBlank(auditEntry.getLogger().getEvent())) {
            atts.addAttribute("", "", "event", ReportXMLConst.XSD_STRING, auditEntry.getLogger().getEvent());
        }
        if (auditEntry.getLogger().getResult() != null) {
            atts.addAttribute("", "", "result", ReportXMLConst.XSD_STRING,
                    auditEntry.getLogger().getResult().name());
        }
        handler.startElement("", "", "logger", atts);
        handler.endElement("", "", "logger");

        if (auditEntry.getBefore() != null) {
            char[] before = ToStringBuilder.reflectionToString(auditEntry.getBefore(), ToStringStyle.JSON_STYLE)
                    .toCharArray();
            handler.startElement("", "", "before", null);
            handler.characters(before, 0, before.length);
            handler.endElement("", "", "before");
        }

        if (auditEntry.getInput() != null) {
            handler.startElement("", "", "inputs", null);
            for (Object inputObj : auditEntry.getInput()) {
                char[] input = ToStringBuilder.reflectionToString(inputObj, ToStringStyle.JSON_STYLE)
                        .toCharArray();
                handler.startElement("", "", "input", null);
                handler.characters(input, 0, input.length);
                handler.endElement("", "", "input");
            }
            handler.endElement("", "", "inputs");
        }

        if (auditEntry.getOutput() != null) {
            char[] output = ToStringBuilder.reflectionToString(auditEntry.getOutput(), ToStringStyle.JSON_STYLE)
                    .toCharArray();
            handler.startElement("", "", "output", null);
            handler.characters(output, 0, output.length);
            handler.endElement("", "", "output");
        }

        handler.startElement("", "", "throwable", null);
        char[] throwable = row.get("THROWABLE").toString().toCharArray();
        handler.characters(throwable, 0, throwable.length);
        handler.endElement("", "", "throwable");

        handler.endElement("", "", "event");
    }
    handler.endElement("", "", "events");
}

From source file:org.apereo.portal.jdbc.DatabaseMetaDataImpl.java

/**
 * Test the database to find the supported timestamp format
 *//* w  w w  . jav  a 2  s  .c o  m*/
private void testTimeStamp(final JdbcTemplate jdbcTemplate) {
    try {
        //Try using {ts }
        final String timeStampTestQuery = "SELECT USER_ID " + "FROM UP_USER "
                + "WHERE LST_CHAN_UPDT_DT={ts '2001-01-01 00:00:00.0'} AND USER_ID = 0";

        jdbcTemplate.queryForList(timeStampTestQuery);
        this.useTSWrapper = true;
    } catch (DataAccessException dae1) {
        final String logMessage1 = "Error running {ts } test.";
        if (LOG.isDebugEnabled()) {
            LOG.debug(logMessage1, dae1);
        }

        //Try using TO_DATE()
        try {
            final String toDateTestQuery = "SELECT USER_ID " + "FROM UP_USER "
                    + "WHERE LST_CHAN_UPDT_DT>TO_DATE('2001 01 01 00:00', 'YYYY MM DD HH24:MI:SS') AND USER_ID=0";

            jdbcTemplate.queryForList(toDateTestQuery);
            this.useToDate = true;
        } catch (DataAccessException dae2) {
            final String logMessage2 = "Error running TO_DATE() test.";
            if (LOG.isDebugEnabled()) {
                LOG.debug(logMessage2, dae2);
            }
        }
    }
}

From source file:org.projectforge.framework.persistence.database.DatabaseUpdateService.java

/**
 *///from  ww w.j  a  v  a2  s  . c o  m
public List<DatabaseUpdateDO> getUpdateHistory() {
    accessCheck(false);
    final JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
    final List<Map<String, Object>> dbResult = jdbc
            .queryForList("select * from t_database_update order by update_date desc");
    final List<DatabaseUpdateDO> result = new ArrayList<DatabaseUpdateDO>();
    for (final Map<String, Object> map : dbResult) {
        final DatabaseUpdateDO entry = new DatabaseUpdateDO();
        entry.setUpdateDate((Date) map.get("update_date"));
        entry.setRegionId((String) map.get("region_id"));
        entry.setVersionString((String) map.get("version"));
        entry.setExecutionResult((String) map.get("execution_result"));
        final PFUserDO executedByUser = TenantRegistryMap.getInstance().getTenantRegistry().getUserGroupCache()
                .getUser((Integer) map.get("executed_by_user_fk"));
        entry.setExecutedBy(executedByUser);
        entry.setDescription((String) map.get("description"));
        result.add(entry);
    }
    return result;
}