List of usage examples for org.springframework.jdbc.core.namedparam MapSqlParameterSource MapSqlParameterSource
public MapSqlParameterSource(@Nullable Map<String, ?> values)
From source file:org.openlmis.performancetesting.dao.LineItemDAO.java
public List<Product> getLineItems(Facility facility, Program program, Boolean isFullSupply) { String getLineItemQuery = "SELECT p.code, p.fullSupply " + "FROM facility_approved_products fap " + "INNER JOIN facilities f ON f.typeId = fap.facilityTypeId " + "INNER JOIN program_products pp ON pp.id = fap.programProductId " + "INNER JOIN products p ON p.id = pp.productId " + "INNER JOIN product_categories pc ON pc.id = p.categoryId " + "WHERE pp.programId = :programId " + "AND f.id = :facilityId " + "AND p.fullSupply = :fullSupply " + "AND p.active = TRUE AND pp.active = TRUE " + "ORDER BY pc.displayOrder, pc.name, p.displayOrder NULLS LAST, p.code"; Map<String, Object> params = new HashMap<>(); params.put("programId", program.getId()); params.put("facilityId", facility.getId()); params.put("fullSupply", isFullSupply); SqlParameterSource namedParameters = new MapSqlParameterSource(params); List<Product> products = new ArrayList<>(); List<Map<String, Object>> values = template.queryForList(getLineItemQuery, namedParameters); for (Map<String, Object> record : values) { Product product = new Product(); product.setCode((String) record.get("code")); product.setFullSupply((Boolean) record.get("fullSupply")); products.add(product);//w ww .j a v a 2 s . co m } return products; }
From source file:pl.edu.agh.samm.db.impl.MeasurementValueDAO.java
@Override public List<MeasurementValue> getMeasurementValues(String instanceUri, Date startTime, Date endTime) { Map<String, Object> params = new HashMap<String, Object>(); params.put("instanceUri", instanceUri); String query = SQL_QUERY_MEASUREMENT; if (startTime != null && endTime != null) { query = SQL_QUERY_MEASUREMENT_BETWEEN_DATE; params.put("start", startTime); params.put("end", endTime); }/*from w w w . jav a 2 s . co m*/ SqlParameterSource sps = new MapSqlParameterSource(params); return getSimpleJdbcTemplate().query(query, new MeasurementValueRowMapper(), sps); }
From source file:tomekkup.helenos.dao.AccountDao.java
private SqlParameterSource prepareParameterSource(QxAccount user) { return new MapSqlParameterSource(user.toParametersMap()); }
From source file:com.github.ferstl.spring.jdbc.oracle.OracleNamedParameterJdbcTemplateTest.java
@Test public void endingWithSpace() throws SQLException { Map<String, Object> map = new HashMap<>(3); map.put("ten", 10); map.put("twenty", 20); String sql = "SELECT 1 FROM dual WHERE 10 = :ten or 20 = :twenty "; PreparedStatementCreator preparedStatementCreator = this.namedJdbcTemplate.getPreparedStatementCreator(sql, new MapSqlParameterSource(map)); Connection connection = mock(Connection.class); PreparedStatement preparedStatement = mock(PreparedStatement.class); OraclePreparedStatement oracleStatement = mock(OraclePreparedStatement.class); when(connection.prepareStatement(sql)).thenReturn(preparedStatement); when(preparedStatement.unwrap(OraclePreparedStatement.class)).thenReturn(oracleStatement); preparedStatementCreator.createPreparedStatement(connection); verify(oracleStatement).setObjectAtName("ten", 10); verify(oracleStatement).setObjectAtName("twenty", 20); }
From source file:ru.mystamps.web.dao.impl.JdbcSeriesDao.java
@Override public Integer add(AddSeriesDbDto series) { Map<String, Object> params = new HashMap<>(); params.put("category_id", series.getCategoryId()); params.put("country_id", series.getCountryId()); params.put("quantity", series.getQuantity()); params.put("perforated", series.getPerforated()); params.put("release_day", series.getReleaseDay()); params.put("release_month", series.getReleaseMonth()); params.put("release_year", series.getReleaseYear()); params.put("michel_price", series.getMichelPrice()); params.put("michel_currency", series.getMichelCurrency()); params.put("scott_price", series.getScottPrice()); params.put("scott_currency", series.getScottCurrency()); params.put("yvert_price", series.getYvertPrice()); params.put("yvert_currency", series.getYvertCurrency()); params.put("gibbons_price", series.getGibbonsPrice()); params.put("gibbons_currency", series.getGibbonsCurrency()); params.put("comment", series.getComment()); params.put("created_at", series.getCreatedAt()); params.put("created_by", series.getCreatedBy()); params.put("updated_at", series.getUpdatedAt()); params.put("updated_by", series.getUpdatedBy()); KeyHolder holder = new GeneratedKeyHolder(); int affected = jdbcTemplate.update(createSeriesSql, new MapSqlParameterSource(params), holder); Validate.validState(affected == 1, "Unexpected number of affected rows after creation of series: %d", affected);// w ww. java 2s .c o m return Integer.valueOf(holder.getKey().intValue()); }
From source file:in.sc.dao.ListGenerator.java
public int createList(HashMap inputMap) { StringBuilder sql = new StringBuilder(); KeyHolder holder = new GeneratedKeyHolder(); int cl_id = 0; try {// w ww .ja v a2 s .co m sql.append( " INSERT INTO CAT_LISTS(cat_id,list_type,list_url,list_name,min_price,max_price,brand_ids,whereclause,orderclause)VALUES "); sql.append( " (:cat_id,:list_type,:list_url,:list_name,:minprice,:maxprice,:brand,:whereclause,:orderclause) "); namedParameterJdbcTemplate = getTemplate(); namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource(inputMap), holder); cl_id = holder.getKey().intValue(); inputMap.put(ProductHelper.to, 20); // ArrayList<ProductBean> data = (ArrayList) pHelper.getProductLists1(inputMap); // pHelper.insertListProductMap(data, cl_id); } catch (Exception e) { e.printStackTrace(); } finally { } return cl_id; }
From source file:com.github.ferstl.spring.jdbc.oracle.OracleNamedParameterJdbcTemplateTest.java
@Test public void repetition() throws SQLException { Map<String, Object> map = new HashMap<>(3); map.put("ten", 10); String sql = "SELECT 1 FROM dual WHERE 10 = :ten or 0 < :ten "; PreparedStatementCreator preparedStatementCreator = this.namedJdbcTemplate.getPreparedStatementCreator(sql, new MapSqlParameterSource(map)); Connection connection = mock(Connection.class); PreparedStatement preparedStatement = mock(PreparedStatement.class); OraclePreparedStatement oracleStatement = mock(OraclePreparedStatement.class); when(connection.prepareStatement(sql)).thenReturn(preparedStatement); when(preparedStatement.unwrap(OraclePreparedStatement.class)).thenReturn(oracleStatement); preparedStatementCreator.createPreparedStatement(connection); verify(oracleStatement).setObjectAtName("ten", 10); }
From source file:com.ebay.pulsar.analytics.dao.RDBMS.java
public int insert(final String sql, Map<String, ?> parameter, KeyHolder keyHolder) { SqlParameterSource sqlParameter = new MapSqlParameterSource(parameter); return this.namedParameterJdbcTemplate.update(sql, sqlParameter, keyHolder); }
From source file:pl.edu.agh.samm.db.impl.MeasurementValueDAO.java
@Override public List<MeasurementValue> getHistoricalMeasurementValues(String resourceURI, String capabilityURI) { Map<String, Object> params = new HashMap<String, Object>(); params.put("instanceUri", resourceURI); params.put("capabilityUri", capabilityURI); SqlParameterSource sps = new MapSqlParameterSource(params); return getSimpleJdbcTemplate().query(SQL_QUERY_MEASUREMENT_BY_CAPABILITY, new MeasurementValueRowMapper(), sps);//from ww w . j a va2 s . com }
From source file:com.qualogy.qafe.business.integration.rdb.SQLQueryDAO.java
private Object execute(RDBDatasource ds, SQLQuery stmt, Map<String, AdaptedToService> paramsIn, Set outputMapping, Filters filters, DataIdentifier dataId) { Long oldChecksum = null;//from w ww . ja v a 2s .co m if (isConcurrentModificationEnabled()) { oldChecksum = ConflictDetectionUtil.removeChecksum(paramsIn); } String[] inputKeys = null; Map<String, Object> paramIns = new HashMap<String, Object>(); if (paramsIn != null) { paramIns = narrow(paramsIn); inputKeys = (String[]) paramIns.keySet().toArray(new String[paramIns.size()]); } MapSqlParameterSource namedParameters = new MapSqlParameterSource(paramIns); Object result = null; isCountOnly = DataStore.findValue(dataId, DataStore.KEY_WORD_COUNT) != null; String sql = QueryToStringCreator.toString(stmt, namedParameters, inputKeys, outputMapping); Map values = namedParameters.getValues(); if ((values != null) && (values.size() > 0)) { Map replacementMap = new HashMap<String, Object>(); for (String key : inputKeys) { if (values.containsKey(key.toUpperCase())) { replacementMap.put(key, values.get(key.toUpperCase())); } if (values.containsKey(key.toLowerCase())) { replacementMap.put(key, values.get(key.toLowerCase())); } } namedParameters.addValues(replacementMap); } logger.info("Executing SQL: " + sql); SimpleJdbcTemplate template = new SimpleJdbcTemplate(ds.getDataSource()); try { Connection conn = ds.getDataSource().getConnection(); dialect = getDatabaseDialect(conn); DataSourceUtils.releaseConnection(conn, ds.getDataSource()); } catch (SQLException e) { ExceptionHelper.printStackTrace(e); } if (stmt instanceof Select) { result = handleSelect(sql, namedParameters, (Select) stmt, template, filters); if (!isCountOnly && isConcurrentModificationEnabled()) { ConflictDetectionUtil.addChecksums((List<Map<String, Object>>) result, sql); } } else if (stmt instanceof Insert) { result = handleInsert(sql, namedParameters, (Insert) stmt, template); if (isConcurrentModificationEnabled()) { DataStore.store(dataId, DataStore.KEY_SERVICE_MODIFY); } } else if (stmt instanceof SQLOnly) { result = handleQueryTag(sql, namedParameters, template); } else { if (isConcurrentModificationEnabled()) { ConflictDetectionUtil.validateChecksum(template, sql, namedParameters.getValues(), oldChecksum); } template.update(sql, namedParameters); if (isConcurrentModificationEnabled()) { DataStore.store(dataId, DataStore.KEY_SERVICE_MODIFY); } } return result; }