Java NamedParameterJdbcTemplate.query方法代碼示例

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
/**
 * 첫번쨰로우의 첫번쨰 컬럼값 리턴. <br/>
 * 값이 없는경우 NULL <br/>
 * 
 * @작성자 : KYJ
 * @작성일 : 2017. 11. 27.
 * @param dataSource
 * @param sql
 * @param paramMap
 * @return
 */
public static String selectScala(DataSource dataSource, final String sql, MapSqlParameterSource paramMap) {
	String r = null;
	try {
		noticeQuery(sql);
		NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

		ResultSetExtractor<String> extr = new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				if (rs.next()) {
					return rs.getString(1);
				}
				return null;
			}
		};
		r = jdbcTemplate.query(sql, paramMap, extr);

	} catch (Exception e) {
		throw e;
	} finally {
		cleanDataSource();
	}
	return r;
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
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;
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@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;
    });
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@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;
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
/**
 * 조회결과를 단순히 boolean으로만 리턴한다. 즉 조회되는 데이터가 있는지 없는지 확인처리만 하기위한 함수.
 *
 * @작성자 : KYJ
 * @작성일 : 2015. 10. 30.
 * @param sql
 * @param paramMap
 * @param u
 * @return
 */
private boolean checkExists(final String sql, final Map<String, Object> paramMap, NamedParameterJdbcTemplate u) {
	if (u.query(sql, paramMap, new ResultSetExtractor<Boolean>() {

		@Override
		public Boolean extractData(ResultSet rs) throws SQLException, DataAccessException {
			if (rs.next())
				return true;
			return false;
		}
	}))
		return true;
	return false;
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@Test(testName="InsertUser", description="Inserts a user with Contacts, UserOrgs entries", dataProvider="userProvider")
public void TestInsertUser(User user, List<Contact> contacts, List<UserOrg> userOrgs) {
			
	UserDAOImpl userDao = new UserDAOImpl();				
	//userDao.reInitWithNewDatasource(dataSource);
	userDao.setDataSource(dataSource);
	userDao.initialize();
	
	boolean userStatus = userDao.registerUser(user, contacts, userOrgs, null);
	
	System.out.println("Register User status: " + userStatus);
	
	JoinRowCallbackHandler<User> handler = new JoinRowCallbackHandler<User>(new UserRowMapper());
	
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
	template.query("select * from public.user where username=:username", 
			new MapSqlParameterSource("username", "[email protected]"), handler);
	User newUser = handler.getSingleResult();
	//User newUser = userDao.getMyUserID(null) //userDao.getUser("[email protected]");
	try {
		System.out.println("newUser: " + ((newUser != null) ? newUser.toJSONObject().toString() : "null"));
	} catch (JSONException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	if(newUser != null) {
		Assert.assertEquals(newUser.getUsername(), "[email protected]");
		Assert.assertEquals(newUser.getFirstname(), "Test1First");
	} else {
		Assert.fail("Test user not found in database");
	}
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
protected List<AuthorityResource> fetchAuthorityResources(){
		if(StringUtils.isBlank(resourceQuery)){
			Resource res = SpringUtils.classpath(AUTHORITY_RESOURCE_SQL_FILE);
			if(res.exists()){
				try {
					List<String> strs = FileUtils.readAsList(res.getInputStream());
					this.resourceQuery = StringUtils.join(strs, " ");
				} catch (IOException e) {
					throw new BaseException("read sql file error: "+ AUTHORITY_RESOURCE_SQL_FILE, e);
				}
			}else{
				this.resourceQuery = "SELECT "
										+ "perm.code as authority, "
										+ "perm.resources_pattern as resources_pattern, "
										+ "perm.sort "
										+ "FROM admin_permission perm "
										+ "WHERE perm.resources_pattern is not null "
										+ (LangUtils.isEmpty(appCodes)?"":"and perm.app_code in ( :appCode ) ")
//										+ "and perm.resources_pattern!='' " //oracle裏是個坑
										+ "order by perm.sort";
			}
		}
		
		Assert.hasText(resourceQuery);
//		ResourceMapping mapping = new ResourceMapping(getDataSource(), resourceQuery);
//		List<AuthorityResource> authorities = mapping.execute();
		ResourceMapping mapping = new ResourceMapping();
		NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(getDataSource());
		Map<String, Object> params = new HashMap<String, Object>();
		if(!LangUtils.isEmpty(appCodes)){
			params.put("appCode", appCodes);
		}
		List<AuthorityResource> authorities = jdbcTemplate.query(resourceQuery, params, mapping);
		
		if(authorities.isEmpty()){
			logger.warn("no authorities fetch, check your application!");
		}
		return authorities;
	}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@Override
public List<LogCollection> getUnfinishedLogs(LogCollection locationId,
		int pageSize, Date createTimeFloor) {
	
	JdbcTemplate localJdbcTemplate = getJdbcTemplate();
	
	List<DataBaseTransactionLogDetail> query;
	
	List<String> transIdList = null;
	if(locationId != null){
		String transIdLocation = EasyTransStaticHelper.getTransId(locationId.getAppId(), locationId.getBusCode(), locationId.getTrxId());
		transIdList = localJdbcTemplate.queryForList("select trans_log_id from trans_log_unfinished where create_time <= ? and trans_log_id > ? ORDER BY trans_log_id LIMIT ?", new Object[]{createTimeFloor,transIdLocation,pageSize},String.class);
	}else{
		transIdList = localJdbcTemplate.queryForList("select trans_log_id from trans_log_unfinished where create_time <= ? ORDER BY trans_log_id LIMIT ?", new Object[]{createTimeFloor,pageSize},String.class);
	}
	
	if(transIdList == null || transIdList.size() ==0){
		return new ArrayList<LogCollection>();
	}
	
	NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(localJdbcTemplate);
	MapSqlParameterSource paramSource = new MapSqlParameterSource();
	paramSource.addValue("ids", transIdList);
	query = namedTemplate.query("select * from trans_log_detail where trans_log_id in (:ids)  order by trans_log_id,log_detail_id;", paramSource,new BeanPropertyRowMapper<DataBaseTransactionLogDetail>(DataBaseTransactionLogDetail.class));
		
	
	List<LogCollection> result = new ArrayList<LogCollection>();
	List<DataBaseTransactionLogDetail> currentDoList = new ArrayList<DataBaseTransactionLogDetail>();
	List<Content> currentContentList = new ArrayList<Content>();
	String currentId = null;
	for(DataBaseTransactionLogDetail detailDo:query){
		if(!detailDo.getTransLogId().equals(currentId)){
			addToResult(result, currentDoList, currentContentList);
			currentContentList.clear();
			currentDoList.clear();
			currentId = detailDo.getTransLogId();
		}
		
		currentDoList.add(detailDo);
		currentContentList.addAll(deserializer(detailDo));
	}
	addToResult(result, currentDoList, currentContentList);
	
	return result;
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
public static void getImpactDatasets(
		List<String> searchUrnList,
		int level,
		List<ImpactDataset> impactDatasets)
{
	if (searchUrnList != null && searchUrnList.size() > 0) {

		if (impactDatasets == null)
		{
			impactDatasets = new ArrayList<ImpactDataset>();
		}

		List<String> pathList = new ArrayList<String>();
		List<String> nextSearchList = new ArrayList<String>();

		for (String urn : searchUrnList)
		{
			LineagePathInfo pathInfo = Lineage.convertFromURN(urn);
			if (pathInfo != null && StringUtils.isNotBlank(pathInfo.filePath))
			{
				if (!pathList.contains(pathInfo.filePath))
				{
					pathList.add(pathInfo.filePath);
				}
			}
		}

		if (pathList != null && pathList.size() > 0)
		{
			Map<String, List> param = Collections.singletonMap("pathlist", pathList);
			NamedParameterJdbcTemplate namedParameterJdbcTemplate = new
					NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
			List<ImpactDataset> impactDatasetList = namedParameterJdbcTemplate.query(
					GET_ONE_LEVEL_IMPACT_DATABASES,
					param,
					new ImpactDatasetRowMapper());

			if (impactDatasetList != null) {
				for (ImpactDataset dataset : impactDatasetList) {
					dataset.level = level;
					if (impactDatasets.stream().filter(o -> o.urn.equals(dataset.urn)).findFirst().isPresent())
					{
						continue;
					}
					impactDatasets.add(dataset);
					nextSearchList.add(dataset.urn);
				}
			}
		}

		if (nextSearchList.size() > 0)
		{
			getImpactDatasets(nextSearchList, level + 1, impactDatasets);
		}
	}
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@SuppressWarnings({ "unchecked", "rawtypes" })
private List<FilaResultat> consultaSql(
		Domini domini,
		Map<String, Object> parametres) {
	long t0 = System.currentTimeMillis();
	try {
		logger.debug("Petició de domini de tipus SQL (" +
				"id=" + domini.getId() + ", " +
				"codi=" + domini.getCodi() + ", " +
				"params=" + parametresToString(parametres) + ")");
		NamedParameterJdbcTemplate jdbcTemplate = getJdbcTemplateFromDomini(domini);
		MapSqlParameterSource parameterSource = new MapSqlParameterSource(parametres) {
			public boolean hasValue(String paramName) {
				return true;
			}
		};
		List<FilaResultat> resultat = jdbcTemplate.query(
				domini.getSql(),
				parameterSource,
				new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
						FilaResultat fr = new FilaResultat();
						ResultSetMetaData rsm = rs.getMetaData();
						for (int i = 1; i <= rsm.getColumnCount(); i++) {
							fr.addColumna(new ParellaCodiValor(
									rsm.getColumnName(i),
									rs.getObject(i)));
						}
						return fr;
					}
				});
		monitorDominiHelper.addAccioOk(
				domini,
				"Consulta SQL",
				IntegracioAccioTipusEnumDto.ENVIAMENT,
				System.currentTimeMillis() - t0,
				toIntegracioParametres(parametres));
		return resultat;
	} catch (Exception ex) {
		monitorDominiHelper.addAccioError(
				domini,
				"Consulta SQL",
				IntegracioAccioTipusEnumDto.ENVIAMENT,
				System.currentTimeMillis() - t0,
				ex.getMessage(),
				ex,
				toIntegracioParametres(parametres));
		
		if(ExceptionUtils.getRootCause(ex) != null && 
				(ExceptionUtils.getRootCause(ex).getClass().getName().contains("Timeout") ||
				 ExceptionUtils.getRootCause(ex).getClass().getName().contains("timeout"))) {
					
				throw new SistemaExternTimeoutException(
						domini.getEntorn().getId(),
						domini.getEntorn().getCodi(), 
						domini.getEntorn().getNom(), 
						null, 
						null, 
						null, 
						domini.getExpedientTipus() == null ? null : domini.getExpedientTipus().getId(), 
						domini.getExpedientTipus() == null ? null : domini.getExpedientTipus().getCodi(), 
						domini.getExpedientTipus() == null ? null : domini.getExpedientTipus().getNom(), 
						"(Domini SQL'" + domini.getCodi() + "')", 
						ex);
		} else {
			throw new SistemaExternException(
					domini.getEntorn().getId(),
					domini.getEntorn().getCodi(), 
					domini.getEntorn().getNom(), 
					null, 
					null, 
					null, 
					domini.getExpedientTipus() == null ? null : domini.getExpedientTipus().getId(), 
					domini.getExpedientTipus() == null ? null : domini.getExpedientTipus().getCodi(), 
					domini.getExpedientTipus() == null ? null : domini.getExpedientTipus().getNom(), 
					"(Domini SQL'" + domini.getCodi() + "')", 
					ex);
		}
	}
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
/**
 * @작성자 : KYJ
 * @작성일 : 2017. 11. 21.
 * @param dataSource
 * @param sql
 * @param paramMap
 * @param rowMapper
 * @param limit
 * @return
 * @throws Exception
 */
public static <T> List<T> selectLimit(DataSource dataSource, final String sql, MapSqlParameterSource paramMap, RowMapper<T> rowMapper,
		final int limit) throws Exception {
	List<T> query = null;
	try {
		noticeQuery(sql);
		NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

		// String _sql = ValueUtil.getVelocityToText(sql, paramMap);
		// LOGGER.debug(_sql);

		ResultSetExtractor<List<T>> extr = new ResultSetExtractor<List<T>>() {

			@Override
			public List<T> extractData(ResultSet rs) throws SQLException, DataAccessException {

				ArrayList<T> arrayList = new ArrayList<T>();
				int rownum = 1;

				while (rs.next()) {
					T mapRow = rowMapper.mapRow(rs, rownum);
					arrayList.add(mapRow);
					rownum++;

					if ((limit != -1) && (limit <= rownum)) {
						break;
					}

				}
				return arrayList;
			}
		};

		query = jdbcTemplate.query(sql, paramMap, extr);
	} catch (Exception e) {
		throw e;
	} finally {
		cleanDataSource();
		// close(dataSource);
	}
	return query;
}