Refine search
/** * Public setter for the data source for injection purposes. * * @param dataSource {@link javax.sql.DataSource} to use for querying against */ public void setDataSource(DataSource dataSource) { if (namedParameterJdbcTemplate == null) { this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource); } }
@Override public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) throws DataAccessException { return query(sql, new MapSqlParameterSource(paramMap), rowMapper); }
@Override public int update(String sql, Map<String, ?> paramMap) throws DataAccessException { return update(sql, new MapSqlParameterSource(paramMap)); }
@Override public void query(String sql, SqlParameterSource paramSource, RowCallbackHandler rch) throws DataAccessException { getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rch); }
@Override public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) { if (batchArgs.length == 0) { return new int[0]; } ParsedSql parsedSql = getParsedSql(sql); PreparedStatementCreatorFactory pscf = getPreparedStatementCreatorFactory(parsedSql, batchArgs[0]); return getJdbcOperations().batchUpdate( pscf.getSql(), new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Object[] values = NamedParameterUtils.buildValueArray(parsedSql, batchArgs[i], null); pscf.newPreparedStatementSetter(values).setValues(ps); } @Override public int getBatchSize() { return batchArgs.length; } }); }
@Test public void testBatchUpdateWithEmptyMap() throws Exception { @SuppressWarnings("unchecked") final Map<String, Integer>[] ids = new Map[0]; namedParameterTemplate = new NamedParameterJdbcTemplate(new JdbcTemplate(dataSource, false)); int[] actualRowsAffected = namedParameterTemplate.batchUpdate( "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = :id", ids); assertTrue("executed 0 updates", actualRowsAffected.length == 0); }
public List<AbstractDTO> getAdditionalCodesByQuery(final String query, final String productnumber) { String _query = prepareAdditionalCodeQueryWithLimit(MAX_RESULTS); Map<String, Object> parameters = Maps.newHashMap(); parameters.put("query", "%" + query + "%"); parameters.put("productnumber", productnumber); SqlParameterSource nParameters = new MapSqlParameterSource(parameters); List<AbstractDTO> codes = jdbcTemplate.query(_query, nParameters, new BeanPropertyRowMapper(AdditionalCodeDTO.class)); return codes; }
public Map<Date, List<PalletBalanceRowDto>> getCurrentState(Date dateTo) { StringBuilder query = new StringBuilder(); query.append("select r.typeofpallet as typeOfPallet, count(distinct p.number) as palletsCount, current_date AS day "); query.append(" from materialflowresources_resource r "); query.append(" join basic_palletnumber p on r.palletnumber_id = p.id "); query.append("group by r.typeofpallet"); List<PalletBalanceRowDto> results = jdbcTemplate.query(query.toString(), new BeanPropertyRowMapper<>( PalletBalanceRowDto.class)); Map<Date, List<PalletBalanceRowDto>> map = Maps.newHashMap(); map.put(dateTo, results); return map; }
private void setAcceptationInProgress(final List<Entity> documents, final boolean acceptationInProgress) { String sql = "UPDATE materialflowresources_document SET acceptationinprogress = :acceptationinprogress WHERE id IN (:ids);"; List<Long> ids = documents.stream().map(document -> document.getId()).collect(Collectors.toList()); Map<String, Object> parameters = Maps.newHashMap(); parameters.put("acceptationinprogress", acceptationInProgress); parameters.put("ids", ids); SqlParameterSource namedParameters = new MapSqlParameterSource(parameters); LOG.info("DOCUMENT SET ACCEPTATION IN PROGRESS = " + acceptationInProgress + " ids =" + ids.stream().map(Object::toString).collect(Collectors.joining(", "))); jdbcTemplate.update(sql, namedParameters); }
private boolean getAcceptationInProgress(final Long documentId) { String sql = "SELECT acceptationinprogress FROM materialflowresources_document WHERE id = :id;"; Map<String, Object> parameters = Maps.newHashMap(); parameters.put("id", documentId); return jdbcTemplate.queryForObject(sql, parameters, Boolean.class); }
private boolean getAcceptationInProgress(final Long documentId) { String sql = "SELECT acceptationinprogress FROM materialflowresources_document WHERE id = :id;"; Map<String, Object> parameters = Maps.newHashMap(); parameters.put("id", documentId); SqlParameterSource namedParameters = new MapSqlParameterSource(parameters); return jdbcTemplate.queryForObject(sql, parameters, Boolean.class); }
public void delete(final Long id) { validator.validateBeforeDelete(id); Map<String, Object> params = Maps.newHashMap(); params.put("id", id); StringBuilder queryBuilder = new StringBuilder(); queryBuilder.append("DELETE FROM materialflowresources_position WHERE id = :id "); String queryForDocumentId = "SELECT document_id, product_id, resource_id, quantity FROM materialflowresources_position WHERE id = :id"; Map<String, Object> result = jdbcTemplate.queryForMap(queryForDocumentId, params); params.putAll(result); reservationsService.deleteReservationFromDocumentPosition(params); jdbcTemplate.update(queryBuilder.toString(), params); }
private void deleteOldLogs() { String sqlForViewed = "DELETE FROM basic_viewedactivity " + "WHERE log_id IN (SELECT id FROM basic_log WHERE createtime < (now() - interval '1 month'))"; String sql = "DELETE FROM basic_log WHERE createtime < (now() - interval '1 month');"; Map<String, Object> params = Maps.newHashMap(); jdbcTemplate.update(sqlForViewed, params); jdbcTemplate.update(sql, params); }
@Test public void testQueryForIntWithParamMap() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getInt(1)).willReturn(22); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("id", 3); int i = template.queryForObject("SELECT AGE FROM CUSTMR WHERE ID = :id", params, Integer.class).intValue(); assertEquals("Return of an int", 22, i); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = ?"); verify(preparedStatement).setObject(1, 3); }
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(localJdbcTemplate); MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("ids", transIdList); query = namedTemplate.query(selectTransDetailsByIds, paramSource,new BeanPropertyRowMapper<DataBaseTransactionLogDetail>(DataBaseTransactionLogDetail.class));
public List<ProductDTO> getAllProducts(final String sidx, final String sord) { // TODO sort String _query = "SELECT product.id, product.number AS code, product.number, product.name, product.ean, product.globaltypeofmaterial, product.category " + "FROM basic_product product WHERE product.active = true;"; List<ProductDTO> products = jdbcTemplate.query(_query, new MapSqlParameterSource(Collections.EMPTY_MAP), new BeanPropertyRowMapper(ProductDTO.class)); return products; }
@Test public void testQueryForListWithParamMapAndEmptyResult() throws Exception { given(resultSet.next()).willReturn(false); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("id", 3); List<Map<String, Object>> li = template.queryForList( "SELECT AGE FROM CUSTMR WHERE ID < :id", params); assertEquals("All rows returned", 0, li.size()); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID < ?"); verify(preparedStatement).setObject(1, 3); }
@Override public List<Employee> employeesWithSalaryGreaterThan(Integer minSalary) { logger.info("Looking for employeesWithSalaryGreaterThan using JDBCTemplate"); String query = "SELECT * " + " FROM employee" + " WHERE salary > :salary"; MapSqlParameterSource params = new MapSqlParameterSource() .addValue("salary", minSalary); // using BeanPropertyRowMapper is easier, but with much worse performance than custom RowMapper return jdbcTemplate.query(query, params, BeanPropertyRowMapper.newInstance(Employee.class)); }
@Override public void write(List<? extends PlayerSummary> summaries) { for (PlayerSummary summary : summaries) { MapSqlParameterSource args = new MapSqlParameterSource().addValue("id", summary.getId()).addValue("year", summary.getYear()).addValue("completes", summary.getCompletes()).addValue("attempts", summary.getAttempts()).addValue("passingYards", summary.getPassingYards()).addValue("passingTd", summary.getPassingTd()).addValue("interceptions", summary.getInterceptions()).addValue("rushes", summary.getRushes()).addValue("rushYards", summary.getRushYards()).addValue("receptions", summary.getReceptions()).addValue("receptionYards", summary.getReceptionYards()).addValue( "totalTd", summary.getTotalTd()); namedParameterJdbcTemplate.update(INSERT_SUMMARY, args); } }
@Test public void testBatchUpdateWithSqlParameterSourcePlusTypeInfo() throws Exception { SqlParameterSource[] ids = new SqlParameterSource[2]; ids[0] = new MapSqlParameterSource().addValue("id", 100, Types.NUMERIC); ids[1] = new MapSqlParameterSource().addValue("id", 200, Types.NUMERIC); final int[] rowsAffected = new int[] {1, 2}; given(preparedStatement.executeBatch()).willReturn(rowsAffected); given(connection.getMetaData()).willReturn(databaseMetaData); namedParameterTemplate = new NamedParameterJdbcTemplate(new JdbcTemplate(dataSource, false)); int[] actualRowsAffected = namedParameterTemplate.batchUpdate( "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = :id", ids); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]); assertEquals(rowsAffected[1], actualRowsAffected[1]); verify(connection).prepareStatement("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"); verify(preparedStatement).setObject(1, 100, Types.NUMERIC); verify(preparedStatement).setObject(1, 200, Types.NUMERIC); verify(preparedStatement, times(2)).addBatch(); verify(preparedStatement, atLeastOnce()).close(); verify(connection, atLeastOnce()).close(); }