Java Code Examples for org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate

The following are top voted examples for showing how to use org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate. These examples are extracted from open source projects. You can vote up the examples you like and your votes will be used in our system to generate more good examples.
Example 1
Project: stats-alfresco-on-database   File: LocalDaoImpl.java   Source Code and License 9 votes vote down vote up
@Override
@Transactional
public void insertStatsDirLocalSize(Map<Long, Long> dirLocalSize) throws SaodException {
	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	List<MapSqlParameterSource> batchArgs = new ArrayList<>();

	for (Entry<Long, Long> e : dirLocalSize.entrySet()) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("node_id", e.getKey());
		parameters.addValue("local_size", e.getValue());
		batchArgs.add(parameters);
	}

	String query = sqlQueries.getQuery("insert_stats_dir_local_size.sql");
	jdbcNamesTpl.batchUpdate(query, batchArgs.toArray(new MapSqlParameterSource[dirLocalSize.size()]));
}
 
Example 2
Project: premier-wherehows   File: AdvSearchDAO.java   Source Code and License 7 votes vote down vote up
public static List<String> getTableNames(String scopes)
{
       //Logger.debug("Entering AdvSearch.java:getTableNames()");
	List<String> tables = null;
	if (StringUtils.isNotBlank(scopes))
	{
		String[] scopeArray = scopes.split(",");
		List<String> scopeList = Arrays.asList(scopeArray);
		Map<String, List> param = Collections.singletonMap("scopes", scopeList);
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new
				NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
		tables = namedParameterJdbcTemplate.queryForList(
				GET_DATASET_TABLE_NAMES_BY_SCOPE, param, String.class);
	}
	else
	{
		tables = getJdbcTemplate().queryForList(GET_DATASET_TABLE_NAMES, String.class);
	}

	return tables;
}
 
Example 3
Project: premier-wherehows   File: LineageDAOLite.java   Source Code and License 7 votes vote down vote up
private static void assignData(LineageNodeLite node) {
    List<Map<String, Object>> rows = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", node.urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    rows = namedParameterJdbcTemplate.queryForList(GET_DATA_ATTR, parameters);

    for (Map<String, Object> row : rows) {
        // node only knows id, level, and urn, assign all other attributes
        JsonNode prop = Json.parse((String) row.get("properties"));
        node.description = (prop.has("description")) ? prop.get("description").asText() : "null";
        node.source = (String) row.get("source");
        node.storage_type = (String) row.get("dataset_type"); // what the js calls storage_type, the sql calls dataset_type
        node.dataset_type = (String) row.get("dataset_type");

        // check wh_property for a user specified color, use some generic defaults if nothing found
        //node.color = getColor(node.urn, node.node_type);

        //node.abstracted_path = getPostfix(node.urn);

        // set things to show up in tooltip
        node._sort_list.add("abstracted_path");
        node._sort_list.add("storage_type");
    }
}
 
Example 4
Project: stats-alfresco-on-database   File: LocalDaoImpl.java   Source Code and License 7 votes vote down vote up
@Override
@Transactional
public List<Long> selectparentFolders(List<Long> nodesid) throws SaodException {
	if (nodesid == null || nodesid.size() == 0) {
		return Collections.emptyList();
	}

	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	MapSqlParameterSource parameters = new MapSqlParameterSource();
	parameters.addValue("ids", nodesid);

	String query = sqlQueries.getQuery("select_parents_folders.sql");
	final SqlRowSet queryForRowSet = jdbcNamesTpl.queryForRowSet(query, parameters);

	final List<Long> ids = new ArrayList<>();
	while (queryForRowSet.next()) {
		ids.add(queryForRowSet.getLong(1));
	}

	return ids;
}
 
Example 5
Project: premier-wherehows   File: AdvSearchDAO.java   Source Code and License 6 votes vote down vote up
public static List<String> getFlowNames(String applications)
{
       //ogger.debug("Entering AdvSearch.java:getFlowNames()");
	List<String> flowNames = null;
	if (StringUtils.isNotBlank(applications))
	{
		String[] appArray = applications.split(",");
		List<String> appList = Arrays.asList(appArray);
		Map<String, List> param = Collections.singletonMap("apps", appList);
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new
				NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
		flowNames = namedParameterJdbcTemplate.queryForList(
				GET_FLOW_NAMES_BY_APP, param, String.class);
	}
	else
	{
		flowNames = getJdbcTemplate().queryForList(GET_FLOW_NAMES, String.class);
	}

	return flowNames;
}
 
Example 6
Project: premier-wherehows   File: LineageDAOLite.java   Source Code and License 6 votes vote down vote up
private static void assignApp(LineageNodeLite node) {
    List<Map<String, Object>> rows = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", node.urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    rows = namedParameterJdbcTemplate.queryForList(GET_DATA_ATTR, parameters);

    for (Map<String, Object> row : rows) {
        // node only knows id, level, and urn, assign all other attributes

        // stored in dict_dataset, so has those fields
        JsonNode prop = Json.parse((String) row.get("properties"));

        // properties is a JsonNode, extract what we want out of it
        node.description = (prop.has("description")) ? prop.get("description").asText() : "null";
        node.app_code = (prop.has("app_code")) ? prop.get("app_code").asText() : "null";

        // check wh_property for a user specified color, use some generic defaults if nothing found
        //node.color = getColor(node.urn, node.node_type);

        // set things to show up in tooltip
        node._sort_list.add("app_code");
        node._sort_list.add("description");
    }
}
 
Example 7
Project: premier-wherehows   File: LineageDAOLite.java   Source Code and License 6 votes vote down vote up
private static void assignDB(LineageNodeLite node) {
    List<Map<String, Object>> rows = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", node.urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    rows = namedParameterJdbcTemplate.queryForList(GET_DATA_ATTR, parameters);
    // node only knows id, level, and urn, assign all other attributes

    for (Map<String, Object> row : rows) {
        JsonNode prop = Json.parse((String) row.get("properties"));
        node.description = (prop.has("description")) ? prop.get("description").asText() : "null";
        node.jdbc_url = (prop.has("jdbc_url")) ? prop.get("jdbc_url").asText() : "null";
        node.db_code = (prop.has("db_code")) ? prop.get("db_code").asText() : "null";

        // check wh_property for a user specified color, use some generic defaults if nothing found
        //node.color = getColor(node.urn, node.node_type);

        // set things to show up in tooltip
        node._sort_list.add("db_code");
        //node._sort_list.add("last_modified");
    }
}
 
Example 8
Project: premier-wherehows   File: LineageDAOLite.java   Source Code and License 6 votes vote down vote up
private static void assignGeneral(LineageNodeLite node) {
    List<Map<String, Object>> rows = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", node.urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    rows = namedParameterJdbcTemplate.queryForList(GET_DATA_ATTR, parameters);


    for (Map<String, Object> row : rows) {
        node.name = (String) row.get("name");
        node.schema = (String) row.get("schema");

        // check wh_property for a user specified color, use some generic defaults if nothing found
        node.color = getNodeColor(node.urn, node.node_type);

        // set things to show up in tooltip
        node._sort_list.add("urn");
        node._sort_list.add("name");
    }
}
 
Example 9
Project: spring-data-jdbc   File: JdbcQueryLookupStrategy.java   Source Code and License 6 votes vote down vote up
/**
 * Creates a {@link QueryLookupStrategy} for the given {@link EntityManager}
 * and {@link Key}.
 *
 * @param em                        must not be {@literal null}.
 * @param key                       may be {@literal null}.
 * @param extractor                 must not be {@literal null}.
 * @param evaluationContextProvider must not be {@literal null}.
 * @return
 */
public static QueryLookupStrategy create(Key key, EvaluationContextProvider evaluationContextProvider,
                                         SqlGenerator generator, NamedParameterJdbcTemplate template, RowMapper rowMapper,
                                         TableDescription tableDescription) {

    Assert.notNull(evaluationContextProvider, "EvaluationContextProvider must not be null!");

    switch (key != null ? key : Key.CREATE_IF_NOT_FOUND) {
        case CREATE:
            return new CreateQueryLookupStrategy(generator, template, rowMapper, tableDescription);
        case USE_DECLARED_QUERY:
            return new DeclaredQueryLookupStrategy(generator, template, rowMapper, tableDescription);
        case CREATE_IF_NOT_FOUND:
            return new CreateIfNotFoundQueryLookupStrategy(generator, template, rowMapper, tableDescription);
        default:
            throw new IllegalArgumentException(String.format("Unsupported query lookup strategy %s!", key));
    }
}
 
Example 10
Project: spring-data-jdbc   File: JdbcRepositoryFactory.java   Source Code and License 6 votes vote down vote up
@Override
protected Object getTargetRepository(RepositoryInformation metadata) {
    entityClass = metadata.getDomainType();
    @SuppressWarnings("rawtypes")
    ReflectionJdbcRepository repository = getTargetRepositoryViaReflection(metadata, entityClass);
    repository.setDataSource(datasource);
    repository.afterPropertiesSet();
    this.repository = repository;

    generator = SqlGeneratorFactory.getInstance().getGenerator(datasource);
    template = new NamedParameterJdbcTemplate((JdbcOperations) extractRepositoryField(repository, FIELD_JDBC_OPS));
    rowMapper = extractRepositoryField(repository, FIELD_ROWMAPPER);
    tableDescription = extractRepositoryField(repository, FIELD_TABLE_DESCRIPTION);

    return repository;
}
 
Example 11
Project: wherehowsX   File: AdvSearchDAO.java   Source Code and License 6 votes vote down vote up
public static List<String> getTableNames(String scopes)
{
	List<String> tables = null;
	if (StringUtils.isNotBlank(scopes))
	{
		String[] scopeArray = scopes.split(",");
		List<String> scopeList = Arrays.asList(scopeArray);
		Map<String, List> param = Collections.singletonMap("scopes", scopeList);
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new
				NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
		tables = namedParameterJdbcTemplate.queryForList(
				GET_DATASET_TABLE_NAMES_BY_SCOPE, param, String.class);
	}
	else
	{
		tables = getJdbcTemplate().queryForList(GET_DATASET_TABLE_NAMES, String.class);
	}

	return tables;
}
 
Example 12
Project: wherehowsX   File: AdvSearchDAO.java   Source Code and License 6 votes vote down vote up
public static List<String> getFlowNames(String applications)
{
	List<String> flowNames = null;
	if (StringUtils.isNotBlank(applications))
	{
		String[] appArray = applications.split(",");
		List<String> appList = Arrays.asList(appArray);
		Map<String, List> param = Collections.singletonMap("apps", appList);
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new
				NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
		flowNames = namedParameterJdbcTemplate.queryForList(
				GET_FLOW_NAMES_BY_APP, param, String.class);
	}
	else
	{
		flowNames = getJdbcTemplate().queryForList(GET_FLOW_NAMES, String.class);
	}

	return flowNames;
}
 
Example 13
Project: common-dao   File: QueryInterceptor.java   Source Code and License 6 votes vote down vote up
public void intercept(final NamedParameterJdbcTemplate jdbcTemplate) throws Exception {
    JdbcTemplate innerJdbcTemplate = (JdbcTemplate) jdbcTemplate.getJdbcOperations();
    this.jdbcTemplate = innerJdbcTemplate;
    DataSource dataSource = (innerJdbcTemplate.getDataSource());

    Enhancer enhancer = new Enhancer();
    enhancer.setSuperclass(JdbcTemplate.class);
    Callback noOpCb = NoOp.INSTANCE;
    Callback[] callbacks = new Callback[]{noOpCb, this};
    enhancer.setCallbacks(callbacks);
    enhancer.setCallbackFilter(this);
    JdbcTemplate proxy = (JdbcTemplate) enhancer.create(new Class[]{DataSource.class}, new Object[]{dataSource});

    Field jdbcTemplateField = jdbcTemplate.getClass().getDeclaredField("classicJdbcTemplate");
    jdbcTemplateField.setAccessible(true);
    jdbcTemplateField.set(jdbcTemplate, proxy);
}
 
Example 14
Project: common-dao   File: DaoSupport.java   Source Code and License 6 votes vote down vote up
@Autowired
public DaoSupport(NamedParameterJdbcTemplate jdbcTemplate,String basePackage) {
    selectSupport = new SelectSupport(jdbcTemplate);
    updateSupport = new UpdateSupport(jdbcTemplate);
    deleteSupport = new DeleteSupport(jdbcTemplate);
    insertSupport = new InsertSupport(jdbcTemplate);

    try {
        this.queryInterceptor = new QueryInterceptor(jdbcTemplate);
    } catch (Exception e) {
        e.printStackTrace();
        logger.error("hook jdbcTemplate query methid fail,", e);
    }
    this.basePackage = basePackage;
    if (StringUtil.isEmpty(basePackage)) {
        this.basePackage = "*";
    }
    List<Class<?>> entityClassList = new EntityScanner().scan(basePackage);
    for (Class<?> clazz : entityClassList) {
        getEntity(clazz);
    }
}
 
Example 15
Project: spring-configuration-support   File: ConfigurationDaoImpl.java   Source Code and License 6 votes vote down vote up
/**
 * Init dao.
 */
protected void initDao() throws Exception {
	this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getDataSource());

	this.updateQuery = "UPDATE " + this.tableName
	                   + " SET " + this.tableValueColumn + " = :config_value "
	                   + " WHERE " + this.tableKeyColumn + " = :config_key "
	                   + " AND " + this.tableEnvColumn + " = :config_env";

	this.listQuery = "SELECT "
	                 + this.tableEnvColumn + ", "
	                 + this.tableKeyColumn + ", "
	                 + this.tableValueColumn
	                 + " FROM " + this.tableName
	                 + " WHERE " + this.tableEnvColumn + " = :config_env"
	                 + " OR " + this.tableEnvColumn + " = :default_env_key";

	this.getQuery = "SELECT "
	                + this.tableEnvColumn + ", "
	                + this.tableKeyColumn + ", "
	                + this.tableValueColumn
	                + " FROM " + this.tableName
	                + " WHERE " + this.tableKeyColumn + " = :config_key"
	                + " AND " + this.tableEnvColumn + " = :config_env";

}
 
Example 16
Project: Equella   File: SqlTaxonomyDataSource.java   Source Code and License 6 votes vote down vote up
SqlTaxonomyDataSource(DataSource dataSource, Map<String, String> as)
{
	this.dataSource = dataSource;
	this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

	queryGetTerm = as.get(GET_TERM.toString());
	queryGetChildTerms = as.get(GET_CHILD_TERMS.toString());
	queryGetDataForTerm = as.get(GET_DATA_FOR_TERM.toString());
	querySearchTermsLeaves = as.get(SEARCH_TERMS_LEAVES.toString());
	queryCountTermsLeaves = as.get(COUNT_TERMS_LEAVES.toString());
	querySearchTermsTopLevel = as.get(SEARCH_TERMS_TOPLEVEL.toString());
	queryCountTermsTopLevel = as.get(COUNT_TERMS_TOPLEVEL.toString());
	querySearchTermsAny = as.get(SEARCH_TERMS_ANY.toString());
	queryCountTermsAny = as.get(COUNT_TERMS_ANY.toString());
	queryGetAllDataForTerm = as.get(GET_ALL_DATA_FOR_TERM.toString());
}
 
Example 17
Project: wayf-cloud   File: WayfGuiceModule.java   Source Code and License 6 votes vote down vote up
@Provides
@Singleton
public NamedParameterJdbcTemplate getJdbcTemplate(
        @Named("jdbc.driver") String driver,
        @Named("jdbc.username") String username,
        @Named("jdbc.password") String password,
        @Named("jdbc.url") String url,
        @Named("jdbc.maxActive") Integer maxActive,
        @Named("jdbc.maxIdle") Integer maxIdle,
        @Named("jdbc.initialSize") Integer initialSize,
        @Named("jdbc.validationQuery") String validationQuery) {
    BasicDataSource dataSource = new BasicDataSource();

    dataSource.setDriverClassName(driver);
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    dataSource.setUrl(url);
    dataSource.setMaxActive(maxActive);
    dataSource.setMaxIdle(maxIdle);
    dataSource.setInitialSize(initialSize);
    dataSource.setValidationQuery(validationQuery);

    return new NamedParameterJdbcTemplate(dataSource);
}
 
Example 18
Project: OperatieBRP   File: AdministratieveHandelingJpaRepository.java   Source Code and License 6 votes vote down vote up
@Override
public List<TeLeverenHandelingDTO> geefHandelingenVoorAdmhndPublicatie() {
    //selecteer alle handelingen met oudste tsreg en nog niet geleverd en die handelingen die al in levering staan
    final String basicSelect =
            "SELECT admhnd.id as id, admhnd.tsreg as tsreg, admhnd.statuslev as status, afad.pers pers "
                    + "FROM kern.admhnd admhnd INNER JOIN kern.his_persafgeleidadministrati afad "
                    + "ON afad.admhnd = admhnd.id WHERE ";
    final String sql = String.format("( %s admhnd.statuslev = :televerenstatus ORDER BY admhnd.tsreg ASC LIMIT :maxAantal ) "
            + "UNION ALL "
            + "(%s admhnd.statuslev = :statuslev )", basicSelect, basicSelect);

    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);
    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("maxAantal", maxHandelingenVoorPublicatie);
    parameters.put("televerenstatus", StatusLeveringAdministratieveHandeling.TE_LEVEREN.getId());
    parameters.put("statuslev", StatusLeveringAdministratieveHandeling.IN_LEVERING.getId());
    return jdbcTemplate.query(sql, parameters, (resultSet, i) -> {
        final TeLeverenHandelingDTO teLeverenHandeling = new TeLeverenHandelingDTO();
        teLeverenHandeling.setAdmhndId(resultSet.getLong("id"));
        final java.sql.Date tsreg = resultSet.getDate("tsreg");
        teLeverenHandeling
                .setTijdstipRegistratie(DatumUtil.vanLongNaarZonedDateTime(tsreg.getTime()));
        final int status = resultSet.getInt("status");
        if (status != 0) {
            teLeverenHandeling.setStatus(StatusLeveringAdministratieveHandeling.parseId(status));
        }
        final long pers = resultSet.getLong("pers");
        if (pers != 0) {
            teLeverenHandeling.setBijgehoudenPersoon(pers);
        }
        return teLeverenHandeling;
    });
}
 
Example 19
Project: OperatieBRP   File: AdministratieveHandelingJpaRepository.java   Source Code and License 6 votes vote down vote up
@Override
public int zetHandelingenStatusInLevering(final Set<Long> ids) {
    if (ids.isEmpty()) {
        return 0;
    }
    final String sql = "UPDATE kern.admhnd SET statuslev = 3 WHERE id IN (:ids) AND statuslev = 1";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);

    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("ids", ids);
    return jdbcTemplate.update(sql, parameters);
}
 
Example 20
Project: OperatieBRP   File: PersoonCacheJpaRepositoryImpl.java   Source Code and License 6 votes vote down vote up
@Override
public void updateAfnemerindicatieBlob(final long persoonId, final Long lockVersiePersoon, final Long afnemerindicatieLockVersie) throws BlobException {
    final List<PersoonAfnemerindicatie> afnemerindicatiesNaToevoegen = afnemerindicatieRepository.haalAfnemerindicatiesOp(persoonId);
    final AfnemerindicatiesBlob afnemerindicatiesBlob = Blobber.maakBlob(afnemerindicatiesNaToevoegen);
    LOGGER.info("Blobify persoon:{}", persoonId);
    final byte[] afnemerindicatiesBlobBytes = Blobber.toJsonBytes(afnemerindicatiesBlob);
    //lockversieafnemerindicatiege = null uit initiele vulling of nog geen afnemerindicatie aanwezig
    final String sql
            = "UPDATE kern.perscache pc "
            + "SET lockversieafnemerindicatiege = CASE WHEN lockversieafnemerindicatiege IS NOT NULL THEN lockversieafnemerindicatiege + 1 ELSE  1 END, "
            + "afnemerindicatiegegevens = :afnemerindicatiegegevens "
            + "WHERE (pc.lockversieafnemerindicatiege = :lockversieAfnemerindicatie OR pc.lockversieafnemerindicatiege is null) "
            + "AND pc.pers = :persoonId "
            + "AND EXISTS "
            + "(SELECT 1 FROM kern.pers p WHERE p.id = pc.pers AND p.lockversie = :persoonLock )";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);
    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("afnemerindicatiegegevens", afnemerindicatiesBlobBytes);
    parameters.put("lockversieAfnemerindicatie", afnemerindicatieLockVersie);
    parameters.put("persoonId", persoonId);
    parameters.put("persoonLock", lockVersiePersoon);
    final int rowsUpdated = jdbcTemplate.update(sql, parameters);
    if (rowsUpdated != 1) {
        throw new OptimisticLockException("PersoonCache is ondertussen gewijzigd.");
    }
}
 
Example 21
Project: bdf2   File: JdbcDao.java   Source Code and License 6 votes vote down vote up
@SuppressWarnings({ "unchecked", "rawtypes" })
protected void pagingQuery(Page<?> page,String sql,String dataSourceName,Map<String,Object> parameters,RowMapper<?> mapper){
	NamedParameterJdbcTemplate namedjdbcTemplate=this.getNamedParameterJdbcTemplate(dataSourceName);			
	String querySql=this.getDialect(this.getJdbcTemplate(dataSourceName)).getPaginationSql(sql, page.getPageNo(), page.getPageSize());
	String countSql="select count(*) from ("+sql+") sub_table_alias_";
	if(parameters!=null){
		if(mapper==null){
			page.setEntities((List)namedjdbcTemplate.queryForList(querySql,parameters));
		}else{
			page.setEntities((List)namedjdbcTemplate.query(querySql,parameters,mapper));
		}
		page.setEntityCount(namedjdbcTemplate.queryForObject(countSql,parameters, Integer.class));								
	}else{
		JdbcTemplate jdbcTemplate=this.getJdbcTemplate(dataSourceName);
		if(mapper==null){
			page.setEntities((List)jdbcTemplate.queryForList(querySql));				
		}else{
			page.setEntities((List)jdbcTemplate.query(querySql,mapper));								
		}
		page.setEntityCount(jdbcTemplate.queryForObject(countSql, Integer.class));
	}
}
 
Example 22
Project: bdf2   File: JdbcDao.java   Source Code and License 6 votes vote down vote up
@SuppressWarnings({ "unchecked", "rawtypes" })
protected void pagingQuery(Page<?> page,String sql,String dataSourceName,Map<String,Object> parameters,RowMapper<?> mapper){
	NamedParameterJdbcTemplate namedjdbcTemplate=this.getNamedParameterJdbcTemplate(dataSourceName);			
	String querySql=this.getDialect(this.getJdbcTemplate(dataSourceName)).getPaginationSql(sql, page.getPageNo(), page.getPageSize());
	String countSql="select count(*) from ("+sql+") sub_table_alias_";
	if(parameters!=null){
		if(mapper==null){
			page.setEntities((List)namedjdbcTemplate.queryForList(querySql,parameters));
		}else{
			page.setEntities((List)namedjdbcTemplate.query(querySql,parameters,mapper));
		}
		page.setEntityCount(namedjdbcTemplate.queryForObject(countSql,parameters, Integer.class));								
	}else{
		JdbcTemplate jdbcTemplate=this.getJdbcTemplate(dataSourceName);
		if(mapper==null){
			page.setEntities((List)jdbcTemplate.queryForList(querySql));				
		}else{
			page.setEntities((List)jdbcTemplate.query(querySql,mapper));								
		}
		page.setEntityCount(jdbcTemplate.queryForObject(countSql, Integer.class));
	}
}
 
Example 23
Project: piper   File: JdbcJobRepositoryTests.java   Source Code and License 6 votes vote down vote up
@Test
public void test1 () {
  JdbcTaskExecutionRepository taskRepository = new JdbcTaskExecutionRepository();
  taskRepository.setJdbcOperations(new NamedParameterJdbcTemplate(dataSource));
  taskRepository.setObjectMapper(createObjectMapper());
  
  JdbcJobRepository jobRepository = new JdbcJobRepository();
  jobRepository.setJdbcOperations(new NamedParameterJdbcTemplate(dataSource));
  jobRepository.setJobTaskRepository(taskRepository);
  
  int pageTotal = jobRepository.findAll(1).getNumber();
  
  SimpleJob job = new SimpleJob();
  job.setPipelineId("demo:1234");
  job.setId("1");
  job.setCreateTime(new Date());
  job.setStatus(JobStatus.CREATED);
  jobRepository.create(job);
  
  Page<Job> all = jobRepository.findAll(1);
  Assert.assertEquals(pageTotal+1,all.getSize());
  
  Job one = jobRepository.findOne("1");
  Assert.assertNotNull(one);
}
 
Example 24
Project: jdbc   File: JdbcSinkIntegrationTests.java   Source Code and License 6 votes vote down vote up
@Test
public void testInsertion() {
	NamedParameterJdbcOperations namedParameterJdbcOperations = new NamedParameterJdbcTemplate(jdbcOperations);
	Map<String, Object> mapA = new HashMap<>();
	mapA.put("a", "hello1");
	mapA.put("b", 42);
	Map<String, Object> mapB = new HashMap<>();
	mapB.put("a", "hello2");
	mapB.put("b", null);
	Map<String, Object> mapC = new HashMap<>();
	mapC.put("a", "hello3");
	channels.input().send(MessageBuilder.withPayload(mapA).build());
	channels.input().send(MessageBuilder.withPayload(mapB).build());
	channels.input().send(MessageBuilder.withPayload(mapC).build());
	Assert.assertThat(namedParameterJdbcOperations.queryForObject(
			"select count(*) from messages where a = :a and b = :b", mapA, Integer.class), is(1));
	Assert.assertThat(namedParameterJdbcOperations.queryForObject(
			"select count(*) from messages where a = :a and b IS NULL", mapB, Integer.class), is(1));
	Assert.assertThat(namedParameterJdbcOperations.queryForObject(
			"select count(*) from messages where a = :a and b IS NULL", mapC, Integer.class), is(1));
}
 
Example 25
Project: stats-alfresco-on-database   File: LocalDaoImpl.java   Source Code and License 6 votes vote down vote up
@Override
@Transactional
public void insertStatsDirNoSize(List<Long> parentsid) throws SaodException {
	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	String query = sqlQueries.getQuery("insert_stats_dir_local_size.sql");

	for (Long id : parentsid) {
		if (loadRow(id) != null) {
			continue;
		}

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("node_id", id);
		parameters.addValue("local_size", 0);
		jdbcNamesTpl.update(query, parameters);
	}
}
 
Example 26
Project: stats-alfresco-on-database   File: LocalDaoImpl.java   Source Code and License 6 votes vote down vote up
@Override
@Transactional
public void upadteDirSumSizeZero(List<Long> parentsid) throws SaodException {
	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	List<MapSqlParameterSource> batchArgs = new ArrayList<>();

	for (Long id : parentsid) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("node_id", id);
		parameters.addValue("sum_size", 0);
		batchArgs.add(parameters);
	}

	String query = sqlQueries.getQuery("update_stats_dir_sum_size.sql");
	jdbcNamesTpl.batchUpdate(query, batchArgs.toArray(new MapSqlParameterSource[parentsid.size()]));
}
 
Example 27
Project: stats-alfresco-on-database   File: LocalDaoImpl.java   Source Code and License 6 votes vote down vote up
@Override
@Transactional
public void updateParentNodeId(Map<Long, Long> nodeids) throws SaodException {
	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	List<MapSqlParameterSource> batchArgs = new ArrayList<>();

	for (Entry<Long, Long> e : nodeids.entrySet()) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("node_id", e.getKey());
		parameters.addValue("parent_node_id", e.getValue());
		batchArgs.add(parameters);
	}

	String query = sqlQueries.getQuery("update_parent_node_id.sql");
	jdbcNamesTpl.batchUpdate(query, batchArgs.toArray(new MapSqlParameterSource[nodeids.size()]));
}
 
Example 28
Project: stats-alfresco-on-database   File: AlfrescoDaoImpl.java   Source Code and License 6 votes vote down vote up
@Override
@Transactional
public Map<Long, Long> selectParentNodeId(List<Long> child_id) throws SaodException {
	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	final String query = sqlQueries.getQuery("select_parent_node_id.sql");
	final Map<Long, Long> libelle = new HashMap<>();

	for (int i = 0; i < child_id.size(); i += MAX_NUM_EXP_LIST) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("ids", child_id.subList(i, Math.min(i + MAX_NUM_EXP_LIST, child_id.size())));

		final SqlRowSet queryForRowSet = jdbcNamesTpl.queryForRowSet(query, parameters);
		while (queryForRowSet.next()) {
			libelle.put(queryForRowSet.getLong(1), queryForRowSet.getLong(2));
		}

	}

	return libelle;
}
 
Example 29
Project: osmp   File: JdbcTemplateManager.java   Source Code and License 6 votes vote down vote up
public void addDataSource(DataSource ds,String catalog,DBType dbType){
    if(ds == null || catalog == null || dbType == null) return;
    BaseTemplate jdbcTemplate = null;
    
    if(dbType.equals(DBType.SQLSERVER)){
        jdbcTemplate = new MSSQLTemplate();
    }else if(dbType.equals(DBType.MYSQL)){
        jdbcTemplate = new MysqlTemplate();
    }else{
        jdbcTemplate = new OracleTemplate();
    }
    jdbcTemplate.setOwnJdbcTemplate(new NamedParameterJdbcTemplate(ds));
    jdbcTemplate.setTransactionManager(new DataSourceTransactionManager(ds));
    //dsTransactionManager.setGlobalRollbackOnParticipationFailure(false); //指定主事务决定回滚
    
    templates.put(catalog.toUpperCase(), jdbcTemplate);
    
}
 
Example 30
Project: Rapture   File: PostgresHelper.java   Source Code and License 6 votes vote down vote up
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);
    }
}
 
Example 31
Project: Rapture   File: PgIndexCreationTest.java   Source Code and License 6 votes vote down vote up
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);
}
 
Example 32
Project: spring-cloud-stream-app-starters   File: JdbcSinkIntegrationTests.java   Source Code and License 6 votes vote down vote up
@Test
public void testInsertion() {
	NamedParameterJdbcOperations namedParameterJdbcOperations = new NamedParameterJdbcTemplate(jdbcOperations);
	Map<String, Object> mapA = new HashMap<>();
	mapA.put("a", "hello1");
	mapA.put("b", 42);
	Map<String, Object> mapB = new HashMap<>();
	mapB.put("a", "hello2");
	mapB.put("b", null);
	Map<String, Object> mapC = new HashMap<>();
	mapC.put("a", "hello3");
	channels.input().send(MessageBuilder.withPayload(mapA).build());
	channels.input().send(MessageBuilder.withPayload(mapB).build());
	channels.input().send(MessageBuilder.withPayload(mapC).build());
	Assert.assertThat(namedParameterJdbcOperations.queryForObject(
			"select count(*) from messages where a = :a and b = :b", mapA, Integer.class), is(1));
	Assert.assertThat(namedParameterJdbcOperations.queryForObject(
			"select count(*) from messages where a = :a and b IS NULL", mapB, Integer.class), is(1));
	Assert.assertThat(namedParameterJdbcOperations.queryForObject(
			"select count(*) from messages where a = :a and b IS NULL", mapC, Integer.class), is(1));
}
 
Example 33
Project: Camel   File: ElsqlSqlProcessingStrategy.java   Source Code and License 6 votes vote down vote up
@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;
        }
    });
}
 
Example 34
Project: Camel   File: ElsqlSqlProcessingStrategy.java   Source Code and License 6 votes vote down vote up
@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;
        }
    });
}
 
Example 35
Project: bamboobsc   File: SysLoginLogSupport.java   Source Code and License 6 votes vote down vote up
public static void log(String userId) {
	if ( StringUtils.isBlank(userId) ) {
		log.warn("null userId");
		return;
	}
	NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate)AppContext.getBean("namedParameterJdbcTemplate");
	Map<String, Object> paramMap = new HashMap<String, Object>();
	paramMap.put("oid", SimpleUtils.getUUIDStr());
	paramMap.put("user", userId);
	paramMap.put("cuserid", "SYS");
	paramMap.put("cdate", new Date());
	try {
		namedParameterJdbcTemplate.update("insert into tb_sys_login_log(OID, USER, CUSERID, CDATE) values(:oid, :user, :cuserid, :cdate)", paramMap);
	} catch (Exception e) {
		e.printStackTrace();
		log.error( e.getMessage().toString() );
	}		
}
 
Example 36
Project: FinanceAnalytics   File: DbConnector.java   Source Code and License 6 votes vote down vote up
/**
 * Creates an instance.
 * 
 * @param name the configuration name, not null
 * @param dialect the database dialect, not null
 * @param dataSource the data source, not null
 * @param jdbcTemplate the JDBC template, not null
 * @param hibernateTemplate the Hibernate template, may be null
 * @param transactionTemplate the transaction template, not null
 */
public DbConnector(
    String name, DbDialect dialect, DataSource dataSource,
    NamedParameterJdbcTemplate jdbcTemplate, HibernateTemplate hibernateTemplate, TransactionTemplate transactionTemplate) {
  ArgumentChecker.notNull(name, "name");
  ArgumentChecker.notNull(dialect, "dialect");
  ArgumentChecker.notNull(dataSource, "dataSource");
  ArgumentChecker.notNull(jdbcTemplate, "JDBC template");
  ArgumentChecker.notNull(transactionTemplate, "transactionTemplate");
  _name = name;
  _dataSource = dataSource;
  _dialect = dialect;
  _jdbcTemplate = jdbcTemplate;
  _hibernateTemplate = hibernateTemplate;
  _transactionTemplate = transactionTemplate;
  _clock = new DbClock(this);  // late initialization
}
 
Example 37
Project: nics-common   File: NicsDaoTest.java   Source Code and License 6 votes vote down vote up
private void removeTestFormData() {
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
	
	int formId = -1;
	try {
		formId = template.queryForObject("select formid from form where formid=:formid", 
				new MapSqlParameterSource("formid", 9999), Integer.class);
	} catch(Exception e) {
		System.out.println("Failed to get formId, not removing form!");
		return;
	}
	System.out.println("\n!!!Retrieve test form ID result: " + formId);
			
	
	int formResult = template.update("delete from form where formid=:formid",
			new MapSqlParameterSource("formid", formId));
	System.out.println("\n!!!Remove test Form result: " + formResult);
}
 
Example 38
Project: WhereHows   File: AdvSearchDAO.java   Source Code and License 6 votes vote down vote up
public static List<String> getTableNames(String scopes)
{
	List<String> tables = null;
	if (StringUtils.isNotBlank(scopes))
	{
		String[] scopeArray = scopes.split(",");
		List<String> scopeList = Arrays.asList(scopeArray);
		Map<String, List> param = Collections.singletonMap("scopes", scopeList);
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new
				NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
		tables = namedParameterJdbcTemplate.queryForList(
				GET_DATASET_TABLE_NAMES_BY_SCOPE, param, String.class);
	}
	else
	{
		tables = getJdbcTemplate().queryForList(GET_DATASET_TABLE_NAMES, String.class);
	}

	return tables;
}
 
Example 39
Project: WhereHows   File: AdvSearchDAO.java   Source Code and License 6 votes vote down vote up
public static List<String> getFlowNames(String applications)
{
	List<String> flowNames = null;
	if (StringUtils.isNotBlank(applications))
	{
		String[] appArray = applications.split(",");
		List<String> appList = Arrays.asList(appArray);
		Map<String, List> param = Collections.singletonMap("apps", appList);
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new
				NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
		flowNames = namedParameterJdbcTemplate.queryForList(
				GET_FLOW_NAMES_BY_APP, param, String.class);
	}
	else
	{
		flowNames = getJdbcTemplate().queryForList(GET_FLOW_NAMES, String.class);
	}

	return flowNames;
}
 
Example 40
Project: smart-sync   File: H2Test.java   Source Code and License 6 votes vote down vote up
private void test() {
	try {
		super.getSrcDs().getConnection().close();
		logger.info("H2 Connection works");
		NamedParameterJdbcTemplate t=new NamedParameterJdbcTemplate(getSrcDs());
		Connection c=super.getSrcDs().getConnection();
		c.prepareStatement("drop table test if exists").execute();
		c.prepareStatement("create table test (s varchar2(10), i integer, r real )").execute();
		c.close();
		logger.info("test table creation ok");
		logger.info("Your H2 installation seems good");
	} catch (SQLException e) {
		throw new SyncException(e);
	}
	
}
 
Example 41
Project: npjt-extra   File: QueryType.java   Source Code and License 6 votes vote down vote up
@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));
}
 
Example 42
Project: premier-wherehows   File: LineageDAOLite.java   Source Code and License 5 votes vote down vote up
private static ImpactDataset assignImpactDataset(String idurn) {
    int level = Integer.parseInt(idurn.substring(0, idurn.indexOf("****")));
    String urn = idurn.substring(idurn.indexOf("****") + 4);
    ImpactDataset impD = new ImpactDataset();

    Map<String, Object> row = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    try {
        row = namedParameterJdbcTemplate.queryForMap(GET_DATA_ATTR, parameters);
    } catch (IncorrectResultSizeDataAccessException irsdae) {
        Logger.error("Incorrect result size ", irsdae);
    } catch (DataAccessException dae) {
        Logger.error("SQL query failed ", dae);
    }

    impD.urn = urn;
    impD.level = level;
    impD.name = (String) row.get("name");
    impD.id = (Long) row.get("id");
    impD.datasetUrl = "#/datasets/" + impD.id;
    JsonNode prop = Json.parse((String) row.get("properties"));
    if (prop.has("valid") && (prop.get("valid").asText()) == "true") {
        impD.isValidDataset = true;
    } else {
        impD.isValidDataset = false;
    }
    return impD;
}
 
Example 43
Project: common-dao   File: CrudTest.java   Source Code and License 5 votes vote down vote up
@Before
public void setUp() throws Exception {
    ComboPooledDataSource dataSource = new ComboPooledDataSource();
    dataSource.setDriverClass("com.mysql.jdbc.Driver");
    dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&zeroDateTimeBehavior=convertToNull&useSSL=false");
    dataSource.setUser("root");
    dataSource.setPassword("3233006");
    jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    daoSupport = new DaoSupport(jdbcTemplate,"top.chenat.commondao");
    daoSupport.setSqlWarningTime(100);
    studentDao = new StudentDao();
    studentDao.setDaoSupport(daoSupport);

}
 
Example 44
Project: ApplicationDetection   File: ActiveRecord.java   Source Code and License 5 votes vote down vote up
public ActiveRecord(NamedParameterJdbcTemplate npJdbcTemplate, String table) {
    Assert.notNull(npJdbcTemplate, "NamedParameterJdbcTemplate不能为空");
    Assert.hasText(table, "表名不能为空");

    this.table = table;
    this.npJdbcTemplate = npJdbcTemplate;
}
 
Example 45
Project: airsonic   File: AbstractDao.java   Source Code and License 5 votes vote down vote up
protected <T> List<T> namedQueryWithLimit(String sql, RowMapper<T> rowMapper, Map<String, Object> args, int limit) {
    long t = System.nanoTime();
    JdbcTemplate jdbcTemplate = new JdbcTemplate(daoHelper.getDataSource());
    jdbcTemplate.setMaxRows(limit);
    NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    List<T> result = namedTemplate.query(sql, args, rowMapper);
    log(sql, t);
    return result;
}
 
Example 46
Project: airsonic   File: GenericDaoHelper.java   Source Code and License 5 votes vote down vote up
public GenericDaoHelper(
        DataSource dataSource
) {
    this.dataSource = dataSource;
    this.jdbcTemplate = new JdbcTemplate(dataSource);
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
 
Example 47
Project: spring-i18n-support   File: SimpleJdbcMessageSourceDao.java   Source Code and License 5 votes vote down vote up
/**
 * {@inheritDoc}
 *
 * @see org.springframework.dao.support.DaoSupport#initDao()
 */
@Override
protected void initDao() throws Exception {
	Assert.hasText(this.singleMessageQuery);
	Assert.hasText(this.allMessagesQuery);
	Assert.hasText(this.insertMessageQuery);
	Assert.hasText(this.updateMessageQuery);
	Assert.hasText(this.deleteMessageQuery);
	Assert.hasText(this.deleteMessageForLangQuery);

	this.compiledSingleMessageQuery = this.compileQuery(this.singleMessageQuery);
	this.compiledAllMessagesQuery = this.compileQuery(this.allMessagesQuery);
	this.compiledInsertMessageQuery = this.compileQuery(this.insertMessageQuery);
	this.compiledUpdateMessageQuery = this.compileQuery(this.updateMessageQuery);
	this.compiledDeleteMessageQuery = this.compileQuery(this.deleteMessageQuery);
	this.compiledDeleteMessageForLangQuery = this.compileQuery(this.deleteMessageForLangQuery);

	this.insertMessageSqlUpdate = new SqlUpdate(this.getDataSource(), this.compiledInsertMessageQuery, new int[] { Types.VARCHAR, Types.VARCHAR,
			Types.VARCHAR, Types.VARCHAR, });

	this.insertMessageSqlUpdate.compile();

	this.updateMessageSqlUpdate = new SqlUpdate(this.getDataSource(), this.compiledUpdateMessageQuery, new int[] { Types.VARCHAR, Types.VARCHAR,
			Types.VARCHAR, });

	this.updateMessageSqlUpdate.compile();

	this.deleteMessageSqlUpdate = new SqlUpdate(this.getDataSource(), this.compiledDeleteMessageQuery, new int[] { Types.VARCHAR });

	this.deleteMessageSqlUpdate.compile();

	this.deleteMessageForLangSqlUpdate = new SqlUpdate(this.getDataSource(), this.compiledDeleteMessageForLangQuery, new int[] { Types.VARCHAR, Types.VARCHAR });

	this.deleteMessageForLangSqlUpdate.compile();

	this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(this.getDataSource());

}
 
Example 48
Project: spring-cloud-dashboard   File: AbstractRdbmsKeyValueRepository.java   Source Code and License 5 votes vote down vote up
public AbstractRdbmsKeyValueRepository(DataSource dataSource, String tablePrefix, String tableSuffix,
		RowMapper<D> rowMapper, String keyColumn, String valueColumn) {
	Assert.notNull(dataSource);
	Assert.notNull(rowMapper);
	this.jdbcTemplate = new JdbcTemplate(dataSource);
	this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	this.dataSource = dataSource;
	this.orderMap = new LinkedHashMap<>();
	this.orderMap.put(keyColumn, Order.ASCENDING);
	this.tablePrefix = tablePrefix;
	this.tableSuffix = tableSuffix;
	this.rowMapper = rowMapper;
	this.keyColumn = keyColumn;
	this.valueColumn = valueColumn;
	tableName = updatePrefixSuffix("%PREFIX%%SUFFIX% ");
	selectClause = keyColumn + ", " + valueColumn + " ";
	whereClauseByKey = "where " + keyColumn + " = ? ";
	inClauseByKey = "where " + keyColumn + " in ( :" + LIST_OF_NAMES + ") ";
	findAllQuery = "SELECT " + selectClause + "FROM " + tableName;
	findAllWhereClauseByKey = findAllQuery + whereClauseByKey;
	saveRow = "INSERT into " + tableName + "(" + keyColumn + ", " + valueColumn + ")" + "values (?, ?)";
	countAll = "SELECT COUNT(*) FROM " + tableName;
	countByKey = "SELECT COUNT(*) FROM " + tableName + whereClauseByKey;
	findAllWhereInClause = findAllQuery + inClauseByKey;
	deleteFromTableClause = "DELETE FROM " + tableName;
	deleteFromTableByKey = deleteFromTableClause + whereClauseByKey;
}
 
Example 49
Project: OperatieBRP   File: AdministratieveHandelingJpaRepository.java   Source Code and License 5 votes vote down vote up
@Override
@Bedrijfsregel(Regel.R1988)
public int markeerAdministratieveHandelingAlsVerwerkt(final Long administratieveHandelingId) {
    //update alle handelingen die nog niet geleverd zijn, en ook niet al op klaar. Status mag null zijn of in levering
    final String sql
            = "UPDATE kern.admhnd SET statuslev = :statuslevVerwerkt, tslev = :tslev WHERE id = :admhndIdVerwerkt "
            + "AND (tslev IS NULL OR statuslev = :statuslevInLevering)";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);
    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("statuslevVerwerkt", StatusLeveringAdministratieveHandeling.GELEVERD.getId());
    parameters.put("tslev", new Date());
    parameters.put("admhndIdVerwerkt", administratieveHandelingId);
    parameters.put("statuslevInLevering", StatusLeveringAdministratieveHandeling.IN_LEVERING.getId());
    return jdbcTemplate.update(sql, parameters);
}
 
Example 50
Project: OperatieBRP   File: AdministratieveHandelingJpaRepository.java   Source Code and License 5 votes vote down vote up
@Override
public int markeerAdministratieveHandelingAlsFout(final Long administratieveHandelingId) {
    final String sql = "UPDATE kern.admhnd SET statuslev = :statuslevFout WHERE id = :admhndIdFout "
            + "AND (tslev IS NULL OR statuslev = :statuslevInLeveringFoutPad)";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);
    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("admhndIdFout", administratieveHandelingId);
    parameters.put("statuslevFout", StatusLeveringAdministratieveHandeling.FOUT.getId());
    parameters.put("statuslevInLeveringFoutPad", StatusLeveringAdministratieveHandeling.IN_LEVERING.getId());
    return jdbcTemplate.update(sql, parameters);
}
 
Example 51
Project: OperatieBRP   File: GeefDetailsPersoonJpaRepositoryImpl.java   Source Code and License 5 votes vote down vote up
@Override
public List<Long> zoekIdsPersoonMetBsn(final String bsn) {
    final String sql = "SELECT id FROM kern.pers persoon WHERE persoon.bsn = :bsn AND persoon.srt = 1";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);
    final Map<String, Object> parameters = new HashMap<>(1, 1);
    parameters.put("bsn", bsn);
    return jdbcTemplate.queryForList(sql, parameters, Long.class);
}
 
Example 52
Project: OperatieBRP   File: GeefDetailsPersoonJpaRepositoryImpl.java   Source Code and License 5 votes vote down vote up
@Override
public List<Long> zoekIdsPersoonMetAnummer(final String anr) {
    final String sql = "SELECT id FROM kern.pers persoon WHERE persoon.anr = :anr AND persoon.srt = 1";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);
    final Map<String, Object> parameters = new HashMap<>(1, 1);
    parameters.put("anr", anr);
    return jdbcTemplate.queryForList(sql, parameters, Long.class);
}
 
Example 53
Project: OperatieBRP   File: PersoonCacheSelectieJpaRepositoryImpl.java   Source Code and License 5 votes vote down vote up
@Override
public MinMaxPersoonCacheDTO selecteerMinMaxIdVoorSelectie() {
    final String sql
            = "select min(id) as laag, max(id) as hoog from kern.perscache";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(selectieDataSource);
    final Map<String, Object> parameters = new HashMap<>();
    return jdbcTemplate.queryForObject(sql, parameters, (resultSet, i) -> {
        final MinMaxPersoonCacheDTO minMaxPersoonCacheDTO = new MinMaxPersoonCacheDTO();
        minMaxPersoonCacheDTO.setMinId(resultSet.getInt("laag"));
        minMaxPersoonCacheDTO.setMaxId(resultSet.getInt("hoog"));
        return minMaxPersoonCacheDTO;
    });
}
 
Example 54
Project: taboola-cronyx   File: CronyxAutoConfiguration.java   Source Code and License 5 votes vote down vote up
@Bean(name = "afterDAO")
public AfterDAO afterDAO(@Qualifier("mainScheduler") Scheduler scheduler,
                         @Qualifier("namedParameterJdbcTemplate") NamedParameterJdbcTemplate namedParameterJdbcTemplate,
                         @Qualifier("nameAndGroupRowMapper") NameAndGroupRowMapper nameAndGroupRowMapper,
                         @Qualifier("nameAndGroupOrderedPairMapper") NameAndGroupOrderedPairMapper nameAndGroupOrderedPairMapper) throws SchedulerException {
    return new StdAfterDAO(scheduler.getSchedulerName(), namedParameterJdbcTemplate, nameAndGroupRowMapper, nameAndGroupOrderedPairMapper);
}
 
Example 55
Project: taboola-cronyx   File: StdAfterDAO.java   Source Code and License 5 votes vote down vote up
public StdAfterDAO(String schedulerName, NamedParameterJdbcTemplate namedParameterJdbcTemplate, NameAndGroupRowMapper nameAndGroupRowMapper,
                   NameAndGroupOrderedPairMapper nameAndGroupOrderedPairMapper) {
    this.schedulerName = schedulerName;
    this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    this.nameAndGroupRowMapper = nameAndGroupRowMapper;
    this.nameAndGroupOrderedPairMapper = nameAndGroupOrderedPairMapper;
}
 
Example 56
Project: bdf2   File: DataSourceRepository.java   Source Code and License 5 votes vote down vote up
public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate(String dataSourceName) {
	if(this.jdbcTemplateMap.containsKey(dataSourceName)){
		return new NamedParameterJdbcTemplate(jdbcTemplateMap.get(dataSourceName));
	}else{
		return getDefaultNamedParameterJdbcTemplate();			
	}
}
 
Example 57
Project: bdf2   File: JdbcDao.java   Source Code and License 5 votes vote down vote up
public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate(String dataSourceName){
	if(StringUtils.isNotEmpty(dataSourceName)){
		return this.dataSourceRepository.getNamedParameterJdbcTemplate(dataSourceName);
	}else{
		return this.dataSourceRepository.getDefaultNamedParameterJdbcTemplate();
	}
}
 
Example 58
Project: bdf2   File: DataSourceRepository.java   Source Code and License 5 votes vote down vote up
public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate(String dataSourceName) {
	if(this.jdbcTemplateMap.containsKey(dataSourceName)){
		return new NamedParameterJdbcTemplate(jdbcTemplateMap.get(dataSourceName));
	}else{
		return getDefaultNamedParameterJdbcTemplate();			
	}
}
 
Example 59
Project: bdf2   File: JdbcDao.java   Source Code and License 5 votes vote down vote up
public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate(String dataSourceName){
	if(StringUtils.isNotEmpty(dataSourceName)){
		return this.dataSourceRepository.getNamedParameterJdbcTemplate(dataSourceName);
	}else{
		return this.dataSourceRepository.getDefaultNamedParameterJdbcTemplate();
	}
}
 
Example 60
Project: helium   File: PersonesPluginJdbc.java   Source Code and License 5 votes vote down vote up
@SuppressWarnings("unchecked")
public List<String> findRolsAmbCodi(String codi) throws PersonesPluginException {
	List<String> rols = new ArrayList<String>();
	try {
		String query = GlobalProperties.getInstance().getProperty("app.persones.plugin.jdbc.filter.roles");
		if (query != null && !query.isEmpty()) {
			Map<String, Object> parametres = new HashMap<String, Object>();
			parametres.put("codi", codi);
			
			String jndi = GlobalProperties.getInstance().getProperty("app.persones.plugin.jdbc.jndi.parameter");
			Context initContext = new InitialContext();
			DataSource ds = (DataSource)initContext.lookup(jndi);
			namedJdbcTemplate = new NamedParameterJdbcTemplate(ds);
			MapSqlParameterSource parameterSource = new MapSqlParameterSource(parametres) {
				public boolean hasValue(String paramName) {
					return true;
				}
			};
			
			rols = namedJdbcTemplate.query(
					query,
					parameterSource,
					new RowMapper() {
						public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
							return rs.getString(1);
						}
					});
		}
	} catch (Exception ex) {
		throw new PersonesPluginException("No s'ha pogut trobar la persona", ex);
	}
	return rols;
}