Example usage for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate query

List of usage examples for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate query

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.namedparam.*
  5. NamedParameterJdbcTemplate
  6. query

Introduction

In this page you can find the example usage for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate query.

Prototype

@Override
    public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper)
            throws DataAccessException

Source Link

Usage

From source file:org.surfnet.cruncher.repository.StatisticsRepositoryImpl.java

@Override
public VersStatistic getVersStats(LocalDate startDate, LocalDate endDate, String spEntityId) {
    final VersStatistic result = new VersStatistic();

    NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(cruncherJdbcTemplate);
    String query = "select idpentityid, sum(entrycount) as loginCount from aggregated_log_logins " + "where "
            + "entryday >= :startDate AND " + "entryday <= :endDate AND " + "spentityid = :spEntityId "
            + "group by idpentityid " + "order by idpentityid";

    Map<String, Object> parameterMap = new HashMap<String, Object>();
    parameterMap.put("startDate", new Date(startDate.toDateMidnight().getMillis()));
    parameterMap.put("endDate", new Date(endDate.toDateMidnight().getMillis()));
    parameterMap.put("spEntityId", spEntityId);

    namedJdbcTemplate.query(query, parameterMap, new RowMapper<VersStatistic>() {

        @Override//from  w ww  .ja  v  a2  s  .c om
        public VersStatistic mapRow(ResultSet rs, int row) throws SQLException {
            String idpEntityId = rs.getString("idpEntityId");
            result.addInstitutionLoginCount(idpEntityId, rs.getLong("loginCount"));

            // no rowbased result
            return null;
        }
    });

    return result;
}

From source file:info.raack.appliancelabeler.data.JDBCDatabase.java

@Override
public Map<UserAppliance, ApplianceStateTransition> getLatestApplianceStatesForUserAppliances(
        List<UserAppliance> apps, final ApplianceEnergyConsumptionDetectionAlgorithm algorithm) {

    List<ApplianceStateTransition> latestTransitions = new ArrayList<ApplianceStateTransition>();

    // query will not execute properly if there are no appliance ids in the "in" list
    if (apps.size() > 0) {
        List<Integer> applianceIds = new ArrayList<Integer>();
        for (UserAppliance userAppliance : apps) {
            applianceIds.add(userAppliance.getId());
        }//from   w  w  w. j ava  2  s  .  c om

        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("user_appliance_ids", applianceIds);
        parameters.addValue("detection_algorithm", algorithm.getId());

        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);

        ApplianceStateTransitionMapper applianceStateTransitionMapper = new ApplianceStateTransitionMapper(
                algorithm);
        template.query(
                "select ast.id as ast_id, ast.start_on as ast_start_on, ast.time as ast_time, ua.id as ua_id, ua.name as ua_name, ua.algorithm_generated as ua_algorithm_generated from appliance_state_transitions ast, (select user_appliance_id, max(time) as maxdatetime from appliance_state_transitions where user_appliance_id in (:user_appliance_ids) and detection_algorithm = :detection_algorithm group by user_appliance_id) groupedast, user_appliances ua where ua.id = ast.user_appliance_id and ast.user_appliance_id = groupedast.user_appliance_id and ast.time = groupedast.maxdatetime order by ast_time",
                parameters, applianceStateTransitionMapper);
    }

    Map<UserAppliance, ApplianceStateTransition> latestApplianceStateTransitions = new HashMap<UserAppliance, ApplianceStateTransition>();

    for (ApplianceStateTransition transition : latestTransitions) {
        latestApplianceStateTransitions.put(transition.getUserAppliance(), transition);
    }

    return latestApplianceStateTransitions;
}

From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java

@Override
public void loadOriginalImage(JochreImageInternal jochreImage) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT image_image FROM ocr_image WHERE image_id=:image_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("image_id", jochreImage.getId());

    LOG.debug(sql);/*from   w  w w . ja  v a 2s. c  om*/
    logParameters(paramSource);

    byte[] pixels = (byte[]) jt.query(sql, paramSource, new ResultSetExtractor() {
        @Override
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                byte[] pixels = rs.getBytes("image_image");

                return pixels;
            } else {
                return null;
            }
        }

    });

    ByteArrayInputStream is = new ByteArrayInputStream(pixels);
    BufferedImage image;
    try {
        image = ImageIO.read(is);
        is.close();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
    jochreImage.setOriginalImageDB(image);
}

From source file:com.joliciel.talismane.terminology.postgres.PostGresTerminologyBase.java

@Override
public List<Context> getContexts(Term term) {
    MONITOR.startTask("getContexts");
    try {//from   w w  w .jav a 2 s.  c o  m
        NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
        String sql = "SELECT " + SELECT_CONTEXT + " FROM context" + " WHERE context_term_id = :context_term_id"
                + " ORDER BY context_id";
        MapSqlParameterSource paramSource = new MapSqlParameterSource();
        paramSource.addValue("context_term_id", ((PostGresTerm) term).getId());

        LOG.trace(sql);
        LogParameters(paramSource);
        @SuppressWarnings("unchecked")
        List<Context> contexts = jt.query(sql, paramSource, new ContextMapper());

        return contexts;
    } finally {
        MONITOR.endTask("getContexts");
    }
}

From source file:com.joliciel.lefff.LefffDaoImpl.java

@SuppressWarnings("unchecked")
@Override/* w w  w .jav a  2 s  . c om*/
public List<Attribute> findAttributes(LefffEntryInternal entry) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_ATTRIBUTE + " FROM lef_attribute, lef_entry_attribute"
            + " WHERE attribute_id = entatt_attribute_id AND entatt_entry_id = :entry_id"
            + " ORDER BY attribute_code, attribute_value";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("entry_id", entry.getId());

    LOG.info(sql);
    LefffDaoImpl.LogParameters(paramSource);
    List<Attribute> attributes = jt.query(sql, paramSource,
            new AttributeMapper(this.getLefffServiceInternal()));

    return attributes;
}

From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java

@Override
public List<GroupOfShapes> findGroupsForMerge() {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_GROUP + " FROM ocr_group WHERE group_id IN"
            + " (SELECT shape_group_id FROM ocr_shape WHERE shape_letter LIKE '%|%')" + " ORDER BY group_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();

    LOG.debug(sql);/*from   w w  w. jav  a 2s .c  o  m*/
    logParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<GroupOfShapes> groups = jt.query(sql, paramSource,
            new GroupOfShapesMapper(this.getGraphicsServiceInternal()));

    return groups;
}

From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java

@Override
public List<Shape> findShapes(GroupOfShapes group) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_SHAPE + " FROM ocr_shape WHERE shape_group_id=:shape_group_id"
            + " ORDER BY shape_index";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("shape_group_id", group.getId());

    LOG.debug(sql);/*from  w  w w.  ja  v  a 2  s. c  om*/
    logParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<Shape> shapes = jt.query(sql, paramSource, new ShapeMapper(this.getGraphicsServiceInternal()));

    return shapes;
}

From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java

@Override
public List<GroupOfShapes> findGroups(RowOfShapes row) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_GROUP + " FROM ocr_group WHERE group_row_id=:group_row_id"
            + " ORDER BY group_index";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("group_row_id", row.getId());

    LOG.debug(sql);/*from  www .j a v  a 2  s.  co m*/
    logParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<GroupOfShapes> groups = jt.query(sql, paramSource,
            new GroupOfShapesMapper(this.getGraphicsServiceInternal()));

    return groups;
}

From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java

public List<JochreImage> findImages(JochrePage jochrePage) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_IMAGE + " FROM ocr_image WHERE image_page_id=:image_page_id"
            + " ORDER BY image_index";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("image_page_id", jochrePage.getId());

    LOG.debug(sql);//from   www  .java2  s.c  o m
    logParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<JochreImage> images = jt.query(sql, paramSource,
            new JochreImageMapper(this.getGraphicsServiceInternal()));

    return images;
}

From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java

public List<RowOfShapes> findRows(Paragraph paragraph) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_ROW + " FROM ocr_row WHERE row_paragraph_id=:row_paragraph_id"
            + " ORDER BY row_index";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("row_paragraph_id", paragraph.getId());

    LOG.debug(sql);/*from  w w w . j  av a  2 s .  c  o m*/
    logParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<RowOfShapes> rows = jt.query(sql, paramSource,
            new RowOfShapesMapper(this.getGraphicsServiceInternal()));

    return rows;
}