/** * Adds an item to a project, specified by ID * @param projectId {@code Long} ID of a project * @param biologicalItemId {@code Long} ID of an item to add */ @Transactional(propagation = Propagation.MANDATORY) public void addProjectItem(long projectId, long biologicalItemId) { Project project = loadProject(projectId); Assert.notNull(project, MessageHelper.getMessage(MessagesConstants.ERROR_PROJECT_NOT_FOUND, projectId)); long newId = daoHelper.createId(projectItemSequenceName); Number countNumber = getJdbcTemplate().queryForObject(loadProjectItemsMaxNumberQuery, new SingleColumnRowMapper<>(), projectId); Integer count = 1; if (countNumber != null) { count = countNumber.intValue() + 1; } MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue(ProjectItemParameters.PROJECT_ITEM_ID.name(), newId); params.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId); params.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), biologicalItemId); params.addValue(ProjectItemParameters.ORDINAL_NUMBER.name(), count); params.addValue(ProjectItemParameters.HIDDEN.name(), false); getNamedParameterJdbcTemplate().update(addProjectItemQuery, params); }
public <T> List<T> queryForList(String sql,Map<String,?> parameters, final int maxrows){ final RowMapper<T> mapper=new SingleColumnRowMapper<T>(); return this.namedParameterJdbcTemplate.query(sql, parameters, new ResultSetExtractor<List<T>>(){ @Override public List<T> extractData(ResultSet rs) throws SQLException, DataAccessException { List<T> data = new ArrayList<T>(); if(rs==null) return data; int rowNum = 0; while((maxrows>0 && rowNum<maxrows || maxrows<0) && rs.next()){ data.add(mapper.mapRow(rs, rowNum++)); } return data; } }); }
/** * !!! Only supports MySQL */ @SuppressWarnings("unchecked") public static List<String> findSchemas(JdbcTemplate jdbcTemplate, final String schemaPattern) { try { if (StringUtils.isEmpty(schemaPattern)) { return jdbcTemplate.query("show databases", new SingleColumnRowMapper(String.class)); } return jdbcTemplate.query("show databases like ?", new Object[] { schemaPattern }, new SingleColumnRowMapper(String.class)); } catch (Exception e) { logger.error(e.getMessage(), e); return new ArrayList<String>(); } }
private <S> RowMapper<S> getRowMapper(JdbcTemplateQueryMethod queryMethod, Class<S> resultType) { if (queryMethod.isSingleColumn()) { return SingleColumnRowMapper.newInstance(resultType); } else { return getEntityRowMapper(queryMethod, resultType); } }
/** * Adds multiple items to a project, specified by ID * @param projectId {@code Long} ID of a project * @param items a {@code List<BiologicalDataItem>} of items to add */ @Transactional(propagation = Propagation.MANDATORY) public void addProjectItems(long projectId, List<ProjectItem> items) { Project project = loadProject(projectId); Assert.notNull(project, MessageHelper.getMessage(MessagesConstants.ERROR_PROJECT_NOT_FOUND, projectId)); List<Long> newIds = daoHelper.createIds(projectItemSequenceName, items.size()); Number countNumber = getJdbcTemplate().queryForObject(loadProjectItemsMaxNumberQuery, new SingleColumnRowMapper<>(), projectId); Integer count = 1; if (countNumber != null) { count = countNumber.intValue() + 1; } ArrayList<MapSqlParameterSource> params = new ArrayList<>(items.size()); for (int i = 0; i < items.size(); i++) { MapSqlParameterSource param = new MapSqlParameterSource(); param.addValue(ProjectItemParameters.PROJECT_ITEM_ID.name(), newIds.get(i)); param.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId); param.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), getBioDataItemId(items.get(i).getBioDataItem())); param.addValue(ProjectItemParameters.ORDINAL_NUMBER.name(), count); param.addValue(ProjectItemParameters.HIDDEN.name(), items.get(i).getHidden() != null && items.get(i).getHidden()); params.add(param); } getNamedParameterJdbcTemplate().batchUpdate(addProjectItemQuery, params.toArray(new MapSqlParameterSource[items.size()])); }
/** * Removes an item from a project, specified by ID * @param projectId {@code Long} ID of a project * @param biologicalItemId {@code Long} ID of an item to remove */ @Transactional(propagation = Propagation.MANDATORY) public void deleteProjectItem(long projectId, long biologicalItemId) { List<Long> currentItemIds = getJdbcTemplate().query(loadProjectItemsIdsQuery, new SingleColumnRowMapper<>(), projectId); currentItemIds.remove(biologicalItemId); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId); params.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), biologicalItemId); getNamedParameterJdbcTemplate().update(deleteProjectItemQuery, params); ArrayList<MapSqlParameterSource> updateParams = new ArrayList<>(); for (int i = 1; i <= currentItemIds.size(); i++) { MapSqlParameterSource param = new MapSqlParameterSource(); param.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId); param.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), currentItemIds.get(i - 1)); param.addValue(ProjectItemParameters.ORDINAL_NUMBER.name(), i); updateParams.add(param); } getNamedParameterJdbcTemplate().batchUpdate(updateProjectItemOrderingNumberQuery, updateParams.toArray( new MapSqlParameterSource[updateParams.size()])); }
/** * Tests if project item from a database is hidden * @param projectId {@code Long} ID of a project * @param biologicalItemId {@code Long} ID of an item to test * @return {@code true} if project item is hidden, {@code false} if not */ @Transactional(propagation = Propagation.MANDATORY) public Boolean isProjectItemHidden(long projectId, long biologicalItemId) { MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId); params.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), biologicalItemId); return getNamedParameterJdbcTemplate().queryForObject(isProjectItemHiddenQuery, params, new SingleColumnRowMapper<>()); }
/** * Checks whether BAM file with a given name is already registered in the system. * @param name BAM file name * @return true, is file is already registered otherwise false */ @Transactional(propagation = Propagation.MANDATORY) public Boolean hasThisName(String name) { List<String> names = getJdbcTemplate().query(searchByNameBamFileQuery, new SingleColumnRowMapper<>(), name); return names.isEmpty(); }
/** * Tests if project item from a database is hidden * @param projectId {@code Long} ID of a project * @param biologicalItemId {@code Long} ID of an item to test * @return {@code true} if project item is hidden, {@code false} if not */ @Transactional(propagation = Propagation.SUPPORTS) public Boolean isProjectItemHidden(long projectId, long biologicalItemId) { MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId); params.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), biologicalItemId); return getNamedParameterJdbcTemplate().queryForObject(isProjectItemHiddenQuery, params, new SingleColumnRowMapper<>()); }
/** * Checks whether BAM file with a given name is already registered in the system. * @param name BAM file name * @return true, is file is already registered otherwise false */ @Transactional(propagation = Propagation.SUPPORTS) public Boolean hasThisName(String name) { List<String> names = getJdbcTemplate().query(searchByNameBamFileQuery, new SingleColumnRowMapper<>(), name); return names.isEmpty(); }
@Override public List<String> getLogEntriesAsGeoJson(LatLngBounds bounds) { String sql = "select ST_AsGeoJSON(location) geojson " + " from logs.client_location_log " + " where logs.client_location_log.location && st_makeenvelope(?, ?, ?, ?) " + " order by stamp desc limit 5000 "; return jdbcTemplate.query(sql, new SingleColumnRowMapper<>(String.class), bounds.getSouthwest().getLng(), bounds.getSouthwest().getLat(), bounds.getNortheast().getLng(), bounds.getNortheast().getLat() ); }
private ItemReader<Long> intervalActivityIdReader(Date cutOffDate, Class<?> activityClass, int chunkSize) { try { JdbcPagingItemReader<Long> reader = new JdbcPagingItemReader<>(); final SqlPagingQueryProviderFactoryBean sqlPagingQueryProviderFactoryBean = new SqlPagingQueryProviderFactoryBean(); sqlPagingQueryProviderFactoryBean.setDataSource(dataSource); sqlPagingQueryProviderFactoryBean.setSelectClause("select id"); sqlPagingQueryProviderFactoryBean.setFromClause("from interval_activities"); sqlPagingQueryProviderFactoryBean.setWhereClause("where dtype = '" + activityClass.getSimpleName() + "' and aggregates_computed = 0 and start_date <= :cutOffDate"); sqlPagingQueryProviderFactoryBean.setSortKey("id"); reader.setQueryProvider(sqlPagingQueryProviderFactoryBean.getObject()); reader.setDataSource(dataSource); reader.setPageSize(chunkSize); reader.setRowMapper(SingleColumnRowMapper.newInstance(Long.class)); reader.setParameterValues(Collections.singletonMap("cutOffDate", cutOffDate)); reader.afterPropertiesSet(); reader.setSaveState(true); logger.info("Reading nonaggregated {} entities with startDate <= {} in chunks of {}", activityClass.getSimpleName(), cutOffDate, chunkSize); return reader; } catch (Exception e) { throw YonaException.unexpected(e); } }
@Override public <E> List<E> querySingleColumn(QueryExample example, Class<E> elementType) { Validate.notNull(example); Validate.notEmpty(example.getFields(), "fields cannot be null"); Transaction transaction = TransactionFactory.createQueryTransaction(config(), example, new SingleColumnRowMapper<E>(elementType)); return transaction.execute(); }
@Override public List<Long> listMessage(LocalDateTime dtm) { QMailQueue a = new QMailQueue("a"); SQLQuery query = queryDslJdbcOperations.newSqlQuery(); query.from(a); query.where(a.scheduledAt.goe(dtm)); query.where(a.deletedFlg.eq(DeletedFlag.NOT_DELETED.code())); return queryDslJdbcOperations.query(query, new SingleColumnRowMapper<Long>(Long.class), a.mailId); }
@Override public Set<Long> getFinishedExecStateIds() { getFinishedExecStateIdsJDBCTemplate.setMaxRows(1000000); getFinishedExecStateIdsJDBCTemplate.setFetchSize(1000000); List<Long> result = doSelectWithTemplate(getFinishedExecStateIdsJDBCTemplate, SELECT_FINISHED_STEPS_IDS, new SingleColumnRowMapper<>(Long.class)); return new HashSet<>(result); }
public RowMapper extract( final Method method ){ final com.stehno.sjdbcx.annotation.RowMapper mapper = AnnotationUtils.getAnnotation( method, com.stehno.sjdbcx.annotation.RowMapper.class ); if( mapper == null ){ Class mappedType = method.getReturnType(); if( Collection.class.isAssignableFrom( mappedType ) ){ mappedType = (Class)((ParameterizedType)method.getGenericReturnType()).getActualTypeArguments()[0]; } else if( mappedType.isArray() ){ throw new UnsupportedOperationException( "Auto-mapping for array return types is not yet supported" ); } else if( mappedType.isPrimitive() ){ if( mappedType == int.class || mappedType == long.class ){ return new SingleColumnRowMapper(); } else if( mappedType == boolean.class ){ return new RowMapper<Boolean>() { @Override public Boolean mapRow( final ResultSet resultSet, final int i ) throws SQLException{ return resultSet.getBoolean(1); } }; } } return new BeanPropertyRowMapper(mappedType); } else { final String extractKey = mapper.value(); if( StringUtils.isEmpty( extractKey ) ){ return resolve( (Class<RowMapper>)mapper.type() ); } else { return resolve( extractKey ); } } }
@Override public <T> T queryForObject(String sql, SqlParameterSource paramSource, Class<T> requiredType) throws DataAccessException { return queryForObject(sql, paramSource, new SingleColumnRowMapper<T>(requiredType)); }
@Override public <T> T queryForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType) throws DataAccessException { return queryForObject(sql, paramMap, new SingleColumnRowMapper<T>(requiredType)); }
@Override public <T> List<T> queryForList(String sql, SqlParameterSource paramSource, Class<T> elementType) throws DataAccessException { return query(sql, paramSource, new SingleColumnRowMapper<T>(elementType)); }
public String getName(int id){ List<String> result = jdbcTemplate.query("select name from student where id = ?", new SingleColumnRowMapper<String>(), id); return result.isEmpty()?null: result.get(0); }
@Override public boolean existsVehicleState(int assetId) { String sql = "SELECT 1 FROM ref.vehicle_state WHERE asset_id = ?"; List<Object> results = jdbcTemplate.query(sql, new SingleColumnRowMapper<>(), assetId); return (!results.isEmpty()); }
@Override public boolean existsPossibleSubroute(int assetId, SubrouteKey subroute) { String sql = "SELECT 1 FROM ref.vehicle_state_possible_subroutes WHERE asset_id = ? AND route = ? and direction = ?"; List<Object> results = jdbcTemplate.query(sql, new SingleColumnRowMapper<>(), assetId, subroute.getRoute(), subroute.getDirection()); return (!results.isEmpty()); }
@Override public boolean existsPossibleRoute(int assetId, String route) { String sql = "SELECT 1 FROM ref.vehicle_state_possible_routes WHERE asset_id = ? AND route = ?"; List<Object> results = jdbcTemplate.query(sql, new SingleColumnRowMapper<>(), assetId, route); return (!results.isEmpty()); }
@Override public List<String> getGpsEnabledRoutesWithinDistance(List<LatLng> line, int distanceInMeters) { PGgeometry geom = new PGgeometry(Mappers.toLineString(line, Mappers.WGS84_SRID)); return jdbcTemplate.query("SELECT DISTINCT route FROM subroute AS subroute WHERE ST_DWithin(ST_Transform(?, 32161), ?)", new SingleColumnRowMapper<>(String.class), geom, distanceInMeters); }
@Override public boolean vehicleExists(int assetId) { List<Integer> result = jdbcTemplate.query("select 1 from ref.vehicle where asset_id = ?", new SingleColumnRowMapper<>(Integer.class), assetId); return !result.isEmpty(); }
@Override public RowMapper<Object> getSingleColumnRowMapper(Class<Object> clzz) { return new SingleColumnRowMapper<Object>(clzz); }
public <T> T queryForObject(String sql, SqlParameterSource paramSource, Class<T> requiredType) throws DataAccessException { return queryForObject(sql, paramSource, new SingleColumnRowMapper<T>(requiredType)); }
public <T> T queryForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType) throws DataAccessException { return queryForObject(sql, paramMap, new SingleColumnRowMapper<T>(requiredType)); }
public <T> List<T> queryForList(String sql, SqlParameterSource paramSource, Class<T> elementType) throws DataAccessException { return query(sql, paramSource, new SingleColumnRowMapper<T>(elementType)); }
private int queryForInt(String sql, PreparedStatementSetter setter) throws DataAccessException { Integer number = queryForObject(sql, setter, new SingleColumnRowMapper<Integer>(Integer.class)); return (number != null ? number.intValue() : 0); }
