Java 类org.springframework.jdbc.core.namedparam.SqlParameterSource 实例源码

public E selectOneWhere(String sqlCondition, SqlParameterSource parameterSource) {
    //sql
    String sql = "SELECT * FROM " + tableName + " WHERE " + sqlCondition;

    List<E> dataList = getSqlFactory().createSQL().useSql(sql)
            .parameter(parameterSource)
            .queryList(new BeanPropertyRowMapper<>(entityClass));


    if (dataList.size() == 0) {
        return null;
    } else if (dataList.size() == 1) {
        return dataList.get(0);
    } else {
        log.error(tableName + "#findOneWhere()返回多条数据");
        throw new RuntimeException(tableName + "#findOneWhere()返回多条数据");
    }
}

private SqlParameterSource getSqlParameterByModel(User user) {

        // Unable to handle List<String> or Array
        // BeanPropertySqlParameterSource

        MapSqlParameterSource paramSource = new MapSqlParameterSource();
        paramSource.addValue("id", user.getId());
        paramSource.addValue("name", user.getName());
        paramSource.addValue("email", user.getEmail());
        paramSource.addValue("login", user.getLogin());
        paramSource.addValue("address", user.getAddress());
        paramSource.addValue("password", user.getPassword());
        paramSource.addValue("newsletter", user.isNewsletter());

        // join String
        paramSource.addValue("framework", convertListToDelimitedString(user.getFramework()));
        paramSource.addValue("sex", user.getSex());
        paramSource.addValue("number", user.getNumber());
        paramSource.addValue("country", user.getCountry());
        paramSource.addValue("skill", convertListToDelimitedString(user.getSkill()));

        return paramSource;
    }

private String queryToCSV(final SqlParameterSource queryParams, final String sql) {
    final int bufferSize = 16384;

    try (final StringWriter writer = new StringWriter(bufferSize);
            final CSVWriter csvWriter = new CSVWriter(writer, ';')) {

        jdbcTemplate.query(sql, queryParams, resultSet -> {
            try {
                csvWriter.writeAll(resultSet, true);
                csvWriter.flush();

                return null;

            } catch (IOException ioe) {
                throw new RuntimeException(ioe);
            }
        });

        return writer.getBuffer().toString();
    } catch (final IOException e) {
        throw new RuntimeException(e);
    }
}

@Override
@Transactional
public TaskExecution merge (TaskExecution aTaskExecution) {
  TaskExecution current = jdbc.queryForObject("select * from task_execution where id = :id for update", Collections.singletonMap("id", aTaskExecution.getId()),this::jobTaskRowMappper);
  SimpleTaskExecution merged = SimpleTaskExecution.createForUpdate(aTaskExecution);  
  if(current.getStatus().isTerminated() && aTaskExecution.getStatus() == TaskStatus.STARTED) {
    merged = SimpleTaskExecution.createForUpdate(current);
    merged.setStartTime(aTaskExecution.getStartTime());
  }
  else if (aTaskExecution.getStatus().isTerminated() && current.getStatus() == TaskStatus.STARTED) {
    merged.setStartTime(current.getStartTime());      
  }
  SqlParameterSource sqlParameterSource = createSqlParameterSource(merged);
  jdbc.update("update task_execution set serialized_execution=:serializedExecution,status=:status,start_time=:startTime,end_time=:endTime where id = :id ", sqlParameterSource);
  return merged;
}

/**
 * Put a key/value pair in the database
 *
 * @param key
 * @param value
 */
public void put(String key, String value) {
    String sql = String.format("select rap_insert_%s(:key, :content);", tableName);
    PGobject valueJson = new PGobject();
    valueJson.setType("jsonb");
    try {
        valueJson.setValue(value);
    } catch (SQLException e) {
        log.error(ExceptionToString.format(e));
        valueJson = null;
    }
    if (valueJson != null) {
        SqlParameterSource paramSource = new MapSqlParameterSource("key", key).addValue("content", valueJson);
        RowMapper<Boolean> rowMapper = new RowMapper<Boolean>() {
            @Override
            public Boolean mapRow(ResultSet rs, int rowNum) throws SQLException {
                return true;
            }
        };
        namedJdbcTemplate.query(sql, paramSource, rowMapper);
    }
}

public List<RaptureFolderInfo> getChildren(String prefix) {
    if (!prefix.startsWith("/")) {
        prefix = "/" + prefix;
    }
    String sql = String.format("SELECT SUB, N FROM %s WHERE ID=:prefix;", folderTable);
    SqlParameterSource paramSource = new MapSqlParameterSource("prefix", prefix);
    RowMapper<RaptureFolderInfo> rowMapper = new RowMapper<RaptureFolderInfo>() {
        @Override
        public RaptureFolderInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            String value = rs.getString(1);
            String nature = rs.getObject(2).toString();
            RaptureFolderInfo info = new RaptureFolderInfo();
            info.setName(value);
            info.setFolder(nature.equals("folder"));
            return info;

        }
    };
    return namedJdbcTemplate.query(sql, paramSource, rowMapper);
}

public static boolean tableExists(NamedParameterJdbcTemplate namedParameterJdbcTemplate, String tableName) throws PostgresException {
    String sql = "SELECT EXISTS (\n"
            + "    SELECT 1 \n"
            + "    FROM   pg_catalog.pg_class c\n"
            + "    JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
            + "    WHERE  n.nspname = 'public'\n"
            + "    AND    c.relname = :table_name\n"
            + ")";

    SqlParameterSource paramSource = new MapSqlParameterSource("table_name", tableName);
    try {
        return namedParameterJdbcTemplate.queryForObject(sql, paramSource, Boolean.class);
    } catch (DataAccessException e) {
        throw new PostgresException(String.format("Error while checking if table [%s] exists", tableName), e);
    }
}

private void insertValue(NamedParameterJdbcTemplate jdbcTemplate, int i) throws SQLException {
    String value = String.format("{\"id\": "
            + "\"%s\", \"max\": 2, \"message\": \"what upz\", \"lastSeen\": 12345, \"progress\": 1}", i);
    PGobject valueJson = new PGobject();
    valueJson.setType("jsonb");
    valueJson.setValue(value);
    SqlParameterSource params = new MapSqlParameterSource().addValue("keyIn", "key" + i).addValue("contentIn", valueJson);
    jdbcTemplate.update("INSERT INTO activity\n"
            + "VALUES(:keyIn, :contentIn, now());\n", params);
}

/**
 * Exemple de requ�te conditionnelle param�tr�e
 */
public List<User> findUsersByExample(final User example) {
    StringBuilder sb = new StringBuilder("select * from users where 1=1 ");

    if (example.getId() != 0) {
        sb.append(" and id = :idParam ");
    }
    if (!StringUtils.isEmpty(example.getName())) {
        sb.append(" and name = :userName ");
    }
    if (!StringUtils.isEmpty(example.getMail())) {
        sb.append(" and email = :userEmail ");
    }

    SqlParameterSource params = new MapSqlParameterSource()
            .addValue("idParam", example.getId())
            .addValue("userName", example.getName())
            .addValue("userEmail", example.getMail());

    return namedParameterJdbcTemplate.query(sb.toString(), params, new UserRowMapper());
}

@Override
public Object add(Object object) {
    SqlModel<Object> sqlModel = sqlBuilder.insertSelectiveSql(object);
    checkSqlModel(sqlModel);

    SqlParameterSource paramSource =  new BeanPropertySqlParameterSource(object);
    KeyHolder generatedKeyHolder =  new  GeneratedKeyHolder();
    namedPjdbcTemplate.update(sqlModel.getSql(), paramSource, generatedKeyHolder);
    Number num = generatedKeyHolder.getKey();

    String[] primaryKeys = sqlModel.getPrimaryKeys();
    if(primaryKeys != null && primaryKeys.length > 0){
        BeanWrapper beanWrapper = PropertyAccessorFactory.forBeanPropertyAccess(object);
        beanWrapper.setPropertyValue(primaryKeys[0], num);
    }

    return object;
}

@Override
public int[] batchUpdate(Collection<?> objects) {
    SqlParameterSource[] batchArgs = new BeanPropertySqlParameterSource[objects.size()];
    String sql = null;
    int index = 0;
    for (Object object : objects) {
        if(index == 0 ){
            //XXX: thinking 共享第一实体的sql
            SqlModel<Object> sqlModel = sqlBuilder.updateSql(object);
            sql = sqlModel.getSql();
        }
        batchArgs[index] = new BeanPropertySqlParameterSource(object);

        index ++;
    }
    int[] rows = namedPjdbcTemplate.batchUpdate(sql, batchArgs);
    return rows;
}

@Override
public List<Object> matchInParameterValuesWithInsertColumns(SqlParameterSource parameterSource) {
    /**
     * 优先使用 EntityPropertySqlParameterSource
     */
    if(parameterSource instanceof EntityPropertySqlParameterSource){
        List<Object> values = new ArrayList<Object>();
        for (String column : this.getTableColumns()) {
            if (parameterSource.hasValue(column)) {
                values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, column));
            }else{
                values.add(null);
            }
        }
        return values;
    }


    return super.matchInParameterValuesWithInsertColumns(parameterSource);
}

@Test
public void testInsert() {
    GeneratedAlwaysRecord record = new GeneratedAlwaysRecord();
    record.setId(100);
    record.setFirstName("Bob");
    record.setLastName("Jones");

    InsertStatementProvider<GeneratedAlwaysRecord> insertStatement = insert(record)
            .into(generatedAlways)
            .map(id).toProperty("id")
            .map(firstName).toProperty("firstName")
            .map(lastName).toProperty("lastName")
            .build()
            .render(RenderingStrategy.SPRING_NAMED_PARAMETER);

    SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(insertStatement.getRecord());
    KeyHolder keyHolder = new GeneratedKeyHolder();

    int rows = template.update(insertStatement.getInsertStatement(), parameterSource, keyHolder);
    String generatedKey = (String) keyHolder.getKeys().get("FULL_NAME");

    assertThat(rows).isEqualTo(1);
    assertThat(generatedKey).isEqualTo("Bob Jones");

}

@Override
public Consumer createConsumer(Processor processor) throws Exception {
    SqlProcessingStrategy proStrategy = new ElsqlSqlProcessingStrategy(elSql);
    SqlPrepareStatementStrategy preStategy = new ElsqlSqlPrepareStatementStrategy();

    final SqlParameterSource param = new EmptySqlParameterSource();
    final String sql = elSql.getSql(elsqlName, new SpringSqlParams(param));
    LOG.debug("ElsqlConsumer @{} using sql: {}", elsqlName, sql);

    ElsqlConsumer consumer = new ElsqlConsumer(this, processor, namedJdbcTemplate, sql, param, preStategy, proStrategy);
    consumer.setMaxMessagesPerPoll(getMaxMessagesPerPoll());
    consumer.setOnConsume(getOnConsume());
    consumer.setOnConsumeFailed(getOnConsumeFailed());
    consumer.setOnConsumeBatchComplete(getOnConsumeBatchComplete());
    consumer.setBreakBatchOnConsumeFail(isBreakBatchOnConsumeFail());
    consumer.setExpectedUpdateCount(getExpectedUpdateCount());
    consumer.setUseIterator(isUseIterator());
    consumer.setRouteEmptyResultSet(isRouteEmptyResultSet());
    configureConsumer(consumer);
    return consumer;
}

@Override
public int commit(DefaultSqlEndpoint defaultSqlEndpoint, Exchange exchange, Object data, NamedParameterJdbcTemplate jdbcTemplate,
                  SqlParameterSource parameterSource, String query) throws Exception {

    final SqlParameterSource param = new ElsqlSqlMapSource(exchange, data);
    final String sql = elSql.getSql(query, new SpringSqlParams(param));
    LOG.debug("commit @{} using sql: {}", query, sql);

    return jdbcTemplate.execute(sql, param, new PreparedStatementCallback<Integer>() {
        @Override
        public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.execute();

            int updateCount = ps.getUpdateCount();
            if (LOG.isTraceEnabled()) {
                LOG.trace("Update count {}", updateCount);
            }
            return updateCount;
        }
    });
}

@Override
public int commitBatchComplete(DefaultSqlEndpoint endpoint, NamedParameterJdbcTemplate namedJdbcTemplate,
                        SqlParameterSource parameterSource, String query) throws Exception {

    final SqlParameterSource param = new EmptySqlParameterSource();
    final String sql = elSql.getSql(query, new SpringSqlParams(param));
    LOG.debug("commitBatchComplete @{} using sql: {}", query, sql);

    return namedJdbcTemplate.execute(sql, param, new PreparedStatementCallback<Integer>() {
        @Override
        public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.execute();

            int updateCount = ps.getUpdateCount();
            if (LOG.isTraceEnabled()) {
                LOG.trace("Update count {}", updateCount);
            }
            return updateCount;
        }
    });
}

private SqlParameterSource getSqlParameterByModel(User user) {

        // Unable to handle List<String> or Array
        // BeanPropertySqlParameterSource

        MapSqlParameterSource paramSource = new MapSqlParameterSource();
        paramSource.addValue("id", user.getId());
        paramSource.addValue("name", user.getName());
        paramSource.addValue("email", user.getEmail());
        paramSource.addValue("address", user.getAddress());
        paramSource.addValue("password", user.getPassword());
        paramSource.addValue("newsletter", user.isNewsletter());

        // join String
        paramSource.addValue("framework", convertListToDelimitedString(user.getFramework()));
        paramSource.addValue("sex", user.getSex());
        paramSource.addValue("number", user.getNumber());
        paramSource.addValue("country", user.getCountry());
        paramSource.addValue("skill", convertListToDelimitedString(user.getSkill()));

        return paramSource;
    }

@Override
public int update(TDto dto) throws FieldValidationException {
    MapSqlParameterSource restrictionParams = new MapSqlParameterSource();
    restrictionParams.addValue(HasId.FN_ID, dto.getId());
    if (dto instanceof HasTimestamps) {
        HasTimestamps hasTimestamps = (HasTimestamps) dto;
        long modifiedAt = hasTimestamps.getModifiedAt();
        hasTimestamps.setModifiedAt(new Date().getTime());
        restrictionParams.addValue(HasTimestamps.FN_MODIFIED_AT, modifiedAt);
    }

    SqlParameterSource dtoParams = parameterSourceBuilder.buildParameterSource(dto);

    try {
        return jdbcUpdate.execute(dtoParams, restrictionParams);
    } catch (Throwable t) {
        processDuplicateEntryException(t);
        Throwables.propagate(t);
        return Integer.MIN_VALUE; // never happens actually
    }
}

@SuppressWarnings("unchecked")
private static <T> AffectedRowCountAndKey<T> executeUpdateAndKeepKeys(
        String template, Method method,
        NamedParameterJdbcTemplate jdbc, SqlParameterSource parameters) {

    Class<T> keyClass = (Class<T>) ((ParameterizedType) method.getGenericReturnType()).getActualTypeArguments()[0];

    KeyHolder keyHolder = new GeneratedKeyHolder();

    int result = jdbc.update(template, parameters, keyHolder);
    Map<String, Object> keys = keyHolder.getKeys();
    Object key;
    if (keys.size() > 1) {
        AutoGeneratedKey spec = Objects.requireNonNull(withType(method.getDeclaredAnnotations(), AutoGeneratedKey.class), "more than one key for query " + template + ": annotation @AutoGeneratedKey required");
        key = Objects.requireNonNull(keys.get(spec.value()), "the key with name " + spec.value() + " has returned null for query " + template + ": required a non null key");
    } else if (Number.class.isAssignableFrom(keyClass)) {
        Class<? extends Number> c = (Class<? extends Number>) keyClass;
        return new AffectedRowCountAndKey<>(result, (T) NumberUtils.convertNumberToTargetClass(keyHolder.getKey(), c));
    } else {
        key = keys.values().iterator().next();
    }
    return new AffectedRowCountAndKey<>(result, keyClass.cast(key));
}

/**
 * Order the action list. Additionally, the ids are filtered.
 *
 * @param cardId
 * @param data
 */
@Transactional(readOnly = false)
public void updateActionListOrder(int cardId, List<Integer> data) {

    // we filter out wrong ids
    List<Integer> filtered = Utils.filter(data,
            queries.findAllCardDataIdsBy(data, cardId, CardType.ACTION_LIST.toString()));
    //

    SqlParameterSource[] params = new SqlParameterSource[filtered.size()];
    for (int i = 0; i < filtered.size(); i++) {
        params[i] = new MapSqlParameterSource("order", i + 1).addValue("id", filtered.get(i)).addValue("cardId",
                cardId);
    }

    jdbc.batchUpdate(queries.updateOrder(), params);
}

@Transactional(readOnly = false)
public void moveLabelListValueToOrder(int valueId, int order) {
    LabelListValue value = findListValueById(valueId);
    if (value.getOrder() == order) {
        return;
    }

    List<LabelListValueWithMetadata> currentValues = findListValuesByLabelId(value.getCardLabelId());
    LabelListValueWithMetadata rval = currentValues.remove(value.getOrder() - 1);
    currentValues.add(order - 1, rval);
    List<SqlParameterSource> vals = new ArrayList<>();
    int currentOrder = 1;
    for (LabelListValue llv : currentValues) {
        if (llv.getOrder() != currentOrder) {
            vals.add(new MapSqlParameterSource("id", llv.getId()).addValue("order", currentOrder));
        }
        currentOrder++;
    }

    jdbc.batchUpdate(queries.updateLabelListValueOrder(), vals.toArray(new SqlParameterSource[vals.size()]));
}

/**
 * update column order in a given board/location. The column ids are filtered.
 *
 * @param columns
 * @param boardId
 * @param location
 */
@Transactional(readOnly = false)
public void updateColumnOrder(List<Integer> columns, int boardId, BoardColumnLocation location) {
    Objects.requireNonNull(columns);
    Objects.requireNonNull(location);

    // keep only the columns that are inside the boardId and have location=board
    List<Integer> filteredColumns = Utils.filter(columns,
            queries.findColumnIdsInBoard(columns, location.toString(), boardId));
    //

    SqlParameterSource[] params = new SqlParameterSource[filteredColumns.size()];
    for (int i = 0; i < filteredColumns.size(); i++) {
        params[i] = new MapSqlParameterSource("order", i + 1).addValue("columnId", filteredColumns.get(i))
                .addValue("boardId", boardId).addValue("location", location.toString());
    }

    jdbc.batchUpdate(queries.updateColumnOrder(), params);
}

@Transactional(readOnly = false)
public List<Integer> moveCardsToColumn(List<Integer> cardIds, int previousColumnId, int columnId, int userId) {

    List<Integer> filteredCardIds = Utils.filter(cardIds, queries.findCardIdsInColumnId(cardIds, previousColumnId));

    List<SqlParameterSource> params = new ArrayList<>(filteredCardIds.size());
    for (int cardId : filteredCardIds) {
        SqlParameterSource p = new MapSqlParameterSource("cardId", cardId)//
                .addValue("previousColumnId", previousColumnId)//
                .addValue("columnId", columnId);
        params.add(p);
    }

    int[] updateResult = jdbc.batchUpdate(queries.moveCardToColumn(),
            params.toArray(new SqlParameterSource[params.size()]));

    List<Integer> updated = new ArrayList<>();
    for (int i = 0; i < updateResult.length; i++) {
        if (updateResult[i] > 0) {
            updated.add(filteredCardIds.get(i));
        }
    }

    return updated;
}

/**
 * 根据主键创建查询条件
 *
 * @param pathBase Querydsl query type
 * @param pk       主键
 * @return 查询条件
 */
public static QueryExample createExampleByPk(RelationalPathBase<?> pathBase, Object pk) {
    int numOfPk = pathBase.getPrimaryKey().getLocalColumns().size();
    Validate.isTrue(numOfPk > 0, "primaryKey not exists");
    QueryExample example = QueryExample.newInstance();
    if (numOfPk == 1) {
        example.equalsTo(pathBase.getPrimaryKey().getLocalColumns()
                .get(0).getMetadata().getName(), pk);
    } else {
        SqlParameterSource source = new BeanPropertySqlParameterSource(pk);
        for (Path<?> path : pathBase.getPrimaryKey().getLocalColumns()) {
            String name = path.getMetadata().getName();
            String humpName = humpName(name);
            example.equalsTo(humpName, source.getValue(name));
        }
    }
    return example;
}

@Test
public void test() {
    ListFieldSetSqlParameterSourceProvider aProvider = new ListFieldSetSqlParameterSourceProvider();
    List<FieldSet> aFieldSetList = new ArrayList<FieldSet>(2);
    aFieldSetList.add(new DefaultFieldSet(new String[]{"Facture", "2222.22", "2014-08-01T12:00:00.000"}, new String[]{"TypeDoc", "Montant", "Date"}));
    aFieldSetList.add(new DefaultFieldSet(new String[]{"Avoir", "-333.00", ""}, new String[]{"TypeDoc", "Montant", "Date"}));
    SqlParameterSource aResult = aProvider.createSqlParameterSource(aFieldSetList);
    assertNotNull(aResult);
    assertEquals("Facture", aResult.getValue("rec0_TypeDoc"));
    assertEquals("2222.22", aResult.getValue("rec0_Montant"));
    assertEquals("2014-08-01T12:00:00.000", aResult.getValue("rec0_Date"));
    assertEquals("Avoir", aResult.getValue("rec1_TypeDoc"));
    assertEquals("-333.00", aResult.getValue("rec1_Montant"));
    assertEquals("", aResult.getValue("rec1_Date"));
    assertFalse(aResult.hasValue("rec2_TypeDoc"));
}

@Test
public void testNoName() {
    ListFieldSetSqlParameterSourceProvider aProvider = new ListFieldSetSqlParameterSourceProvider();
    List<FieldSet> aFieldSetList = new ArrayList<FieldSet>(1);
    aFieldSetList.add(new DefaultFieldSet(new String[]{"Facture", "2222.22", "2014-08-01T12:00:00.000"}));
    aFieldSetList.add(new DefaultFieldSet(new String[]{"Avoir", "-333.00", ""}));
    SqlParameterSource aResult = aProvider.createSqlParameterSource(aFieldSetList);
    assertNotNull(aResult);
    assertEquals("Facture", aResult.getValue("rec0_ucol0"));
    assertEquals("2222.22", aResult.getValue("rec0_ucol1"));
    assertEquals("2014-08-01T12:00:00.000", aResult.getValue("rec0_ucol2"));
    assertEquals("Avoir", aResult.getValue("rec1_ucol0"));
    assertEquals("-333.00", aResult.getValue("rec1_ucol1"));
    assertEquals("", aResult.getValue("rec1_ucol2"));
    assertFalse(aResult.hasValue("rec2_ucol0"));
}

/**
 * Executes a given procedure against the datasource. 
 * @param procedureName The name of the procedure to execute.
 * @param parameters The parameters for the procedure.
 * @return The Map of returned values from the procedure.
 */
public Map<String, ?> executeProcedure(String procedureName, Map<String, ?> parameters)
{
    SimpleJdbcCall call = new SimpleJdbcCall(this.datasource).withSchemaName("lodbot").withProcedureName(procedureName);
    SqlParameterSource callParameters = new MapSqlParameterSource(parameters);
    return call.execute(callParameters);
}

public <T> ResultPage<T> queryPage(String sql, int page, int perPage, SqlParameterSource paramSource, RowMapper<T> rowMapper, DataSourceType dataSourceType) {
    String rowsSQL = PageUtils.getRowsSQL(sql, page, perPage, dataSourceType);
    List<T> list = namedParameterJdbcTemplate.query(
            rowsSQL,
            paramSource,
            rowMapper);

    //查询数量
    String numberSQL = PageUtils.getNumberSQL(sql);
    Integer number = namedParameterJdbcTemplate.queryForObject(
            numberSQL,
            paramSource,
            Integer.class);
    return new ResultPage<T>(list, number);
}

@Override
public SqlParameterSource[] getParamSource(List<W> segments, Integer graphVersionId) throws SQLException { 
    final Timestamp now = new Timestamp(Calendar.getInstance().getTimeInMillis());
    SqlParameterSource[] argArray = new SqlParameterSource[segments.size()];
    int i = 0;
    for (W segment : segments) {
        MapSqlParameterSource args = getParamSource(segment, now);
        if (graphVersionId != null) {
            args.addValue("graphVersionId", graphVersionId);
        }
        argArray[i] = args;
        i++;
    }       
    return argArray;
}

/**
 * Match the provided column names and values with the list of columns used.
 * @param parameterSource the parameter names and values
 */
public List<Object> matchInParameterValuesWithInsertColumns(SqlParameterSource parameterSource) {
    List<Object> values = new ArrayList<Object>();
    // for parameter source lookups we need to provide caseinsensitive lookup support since the
    // database metadata is not necessarily providing case sensitive column names
    Map<String, String> caseInsensitiveParameterNames =
            SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);
    for (String column : this.tableColumns) {
        if (parameterSource.hasValue(column)) {
            values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, column));
        }
        else {
            String lowerCaseName = column.toLowerCase();
            if (parameterSource.hasValue(lowerCaseName)) {
                values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, lowerCaseName));
            }
            else {
                String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(column);
                if (parameterSource.hasValue(propertyName)) {
                    values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, propertyName));
                }
                else {
                    if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) {
                        values.add(
                                SqlParameterSourceUtils.getTypedValue(parameterSource,
                                        caseInsensitiveParameterNames.get(lowerCaseName)));
                    }
                    else {
                        values.add(null);
                    }
                }
            }
        }
    }
    return values;
}

/**
 * Method that provides execution of a batch insert using the passed in array of {@link SqlParameterSource}
 * @param batch array of SqlParameterSource with parameter names and values to be used in insert
 * @return array of number of rows affected
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
protected int[] doExecuteBatch(SqlParameterSource[] batch) {
    checkCompiled();
    List[] batchValues = new ArrayList[batch.length];
    int i = 0;
    for (SqlParameterSource parameterSource : batch) {
        List<Object> values = matchInParameterValuesWithInsertColumns(parameterSource);
        batchValues[i++] = values;
    }
    return executeBatchInternal(batchValues);
}

public Cab findByYear(int manufactureYear) {
    SqlParameterSource namedParameters = new MapSqlParameterSource("manufactureYear", manufactureYear);
    List<Cab> result = namedParameterJdbcTemplate.query(
            "SELECT * FROM cab WHERE YEAR(manufactureYear) = :manufactureYear", namedParameters,
            cabRowMapper);

    if(result.size() > 0) {
        return result.get(0);
    }

    return new Cab();
}

private SqlParameterSource parameterSource(Map<String, Object> values) {
    Map<String, Integer> sqlTypes = sqlTypes();

    MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource();
    for (Map.Entry<String, Object> entry : values.entrySet()) {
        String paramName = entry.getKey();
        int sqlType = sqlTypes.get(paramName) != null ? sqlTypes.get(paramName) : SqlTypeValue.TYPE_UNKNOWN;
        sqlParameterSource.addValue(paramName, entry.getValue(), sqlType);
    }
    return sqlParameterSource;
}

private void insertPreviousToAfterPairs(List<NameAndGroup> prevKeys, NameAndGroup afterKey) {
    try {
        namedParameterJdbcTemplate.batchUpdate(INSERT_QUERY,
                prevKeys.stream()
                .map(p -> new MapSqlParameterSource()
                    .addValue("schedulerName", schedulerName)
                    .addValue("prevTriggerName", p.getName())
                    .addValue("prevTriggerGroup", p.getGroup())
                    .addValue("afterTriggerName", afterKey.getName())
                    .addValue("afterTriggerGroup", afterKey.getGroup()))
                .toArray(SqlParameterSource[]::new));
    } catch (Exception e) {
        throw new CronyxException("Could not store previous to after trigger relations", e);
    }
}

protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
    ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
    String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
    Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
    List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
    PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
    return pscf.newPreparedStatementCreator(params);
}

public static <T> T queryForSingleValue(final String sql,
                                        final T defaultValue,
                                        final NamedParameterJdbcOperations template,
                                        final SqlParameterSource paramSource,
                                        final Class<T> requiredType) {
    final List<T> listResult = template.queryForList(sql, paramSource, requiredType);
    return Iterables.getFirst(listResult, defaultValue);
}

/**
 * Match the provided column names and values with the list of columns used.
 * @param parameterSource the parameter names and values
 */
public List<Object> matchInParameterValuesWithInsertColumns(SqlParameterSource parameterSource) {
    List<Object> values = new ArrayList<Object>();
    // for parameter source lookups we need to provide caseinsensitive lookup support since the
    // database metadata is not necessarily providing case sensitive column names
    Map<String, String> caseInsensitiveParameterNames =
            SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);
    for (String column : this.tableColumns) {
        if (parameterSource.hasValue(column)) {
            values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, column));
        }
        else {
            String lowerCaseName = column.toLowerCase();
            if (parameterSource.hasValue(lowerCaseName)) {
                values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, lowerCaseName));
            }
            else {
                String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(column);
                if (parameterSource.hasValue(propertyName)) {
                    values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, propertyName));
                }
                else {
                    if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) {
                        values.add(
                                SqlParameterSourceUtils.getTypedValue(parameterSource,
                                        caseInsensitiveParameterNames.get(lowerCaseName)));
                    }
                    else {
                        values.add(null);
                    }
                }
            }
        }
    }
    return values;
}

/**
 * Delegate method that executes a batch insert using the passed-in {@link SqlParameterSource}s.
 * @param batch array of SqlParameterSource with parameter names and values to be used in insert
 * @return array of number of rows affected
 */
protected int[] doExecuteBatch(SqlParameterSource... batch) {
    checkCompiled();
    List<List<Object>> batchValues = new ArrayList<List<Object>>(batch.length);
    for (SqlParameterSource parameterSource : batch) {
        batchValues.add(matchInParameterValuesWithInsertColumns(parameterSource));
    }
    return executeBatchInternal(batchValues);
}

private SqlParameterSource createSqlParameterSource (TaskExecution aTaskExecution) {
  MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource();
  sqlParameterSource.addValue("id", aTaskExecution.getId());
  sqlParameterSource.addValue("parentId", aTaskExecution.getParentId());
  sqlParameterSource.addValue("jobId", aTaskExecution.getJobId());
  sqlParameterSource.addValue("status", aTaskExecution.getStatus().toString());
  sqlParameterSource.addValue("createTime", aTaskExecution.getCreateTime());
  sqlParameterSource.addValue("startTime", aTaskExecution.getStartTime());
  sqlParameterSource.addValue("endTime", aTaskExecution.getEndTime());
  sqlParameterSource.addValue("serializedExecution", writeValueAsJsonString(aTaskExecution));
  sqlParameterSource.addValue("priority", aTaskExecution.getPriority());
  sqlParameterSource.addValue("taskNumber", aTaskExecution.getTaskNumber());
  return sqlParameterSource;
}

public void update(Warehouse warehouse) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put(WarehouseDao.ID_PARAMETER, warehouse.getId());
    params.put(WarehouseDao.ADDRESS_PARAMETER, warehouse.getAddress());
    SqlParameterSource sqlParameterSource = new MapSqlParameterSource(params);
    namedParameterJdbcTemplate.update(PARAMETERIZED_SQL_UPDATE, sqlParameterSource);        
}