下面列出了org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
private List<LogCollection> getTransactionLogByIds(JdbcTemplate localJdbcTemplate, List<byte[]> transIdList) {
List<DataBaseTransactionLogDetail> query;
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(localJdbcTemplate);
MapSqlParameterSource paramSource = new MapSqlParameterSource();
paramSource.addValue("ids", transIdList);
query = namedTemplate.query(selectTransDetailsByIds, paramSource,new BeanPropertyRowMapper<DataBaseTransactionLogDetail>(DataBaseTransactionLogDetail.class));
List<LogCollection> result = new ArrayList<LogCollection>();
List<DataBaseTransactionLogDetail> currentDoList = new ArrayList<DataBaseTransactionLogDetail>();
List<Content> currentContentList = new ArrayList<Content>();
byte[] currentId = null;
for(DataBaseTransactionLogDetail detailDo:query){
if(!Arrays.equals(detailDo.getTransLogId(), 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;
}
private QueryResult executeQuery(NamedParameterJdbcTemplate npjt,
String sql,
Map<String, Object> namedParameters,
int resultLimit) {
// Determine max query result
final int maxQueryResult = JdbcQueryServiceHelper.calculateMaxQueryResultLimit(appProperties.getMaximumQueryRecords(), resultLimit);
QueryResult result = npjt.query(sql, namedParameters, new ResultSetExtractor<QueryResult>() {
@Nullable
@Override
public QueryResult extractData(ResultSet rs) {
try {
ResultSetMetaData metadata = rs.getMetaData();
String[] columnNames = getColumnNames(metadata);
List<Column> columns = getColumnList(metadata);
String data = resultSetToJsonString(rs, metadata, maxQueryResult);
return QueryResult.ofData(data, columns);
} catch (Exception e) {
String error = CommonUtils.getSimpleError(e);
return QueryResult.ofError(error);
}
}
});
return result;
}
/**
* @方法名 findAllTypeByUser
* @功能 TODO(这里用一句话描述这个方法的作用)
* @参数 @param username
* @参数 @return
* @返回 List<SysDbmsTabsTypeInfo>
* @author Administrator
* @throws
*/
public List<SysDbmsTabsTypeInfo> findAllTypeByUser(String username) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("select * from sys_dbms_tabs_type_info t ");
stringBuilder.append(" where t.uuid in (");
stringBuilder.append(" select a.type_uuid from sys_dbms_tabs_info a ");
stringBuilder.append(" where a.uuid in ( ");
stringBuilder.append(" select b.tabs_id from sys_roles_tabs_info b ");
stringBuilder.append(" where b.role_id in (");
stringBuilder.append(" select c.roles_id from sys_user_roles_info c");
stringBuilder.append(" where c.user_id in ( ");
stringBuilder.append(" select d.uuid from sys_user_base_info d");
stringBuilder.append(" where d.user_name = '" + username + "'");
stringBuilder.append(" ) and c.checked = 1");
stringBuilder.append(" ) ");
stringBuilder.append(" ) and a.delete_flag = 0");
stringBuilder.append(" ) and t.delete_flag = 0");
stringBuilder.append(" order by t.type_order");
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
List<SysDbmsTabsTypeInfo> list = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsTypeInfo.class));
return list;
}
/**
* @方法名 page1
* @功能 TODO(这里用一句话描述这个方法的作用)
* @参数 @param vo
* @参数 @return
* @返回 Page<SysDbmsTabsColsInfo>
* @author Administrator
* @throws
*/
public List<SysDbmsTabsColsInfo> page1(Pagination<SysDbmsTabsMergeInfo> vo) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append(" SELECT * FROM sys_dbms_tabs_cols_info c");
stringBuilder.append(" WHERE c.uuid not IN (");
stringBuilder.append(" SELECT m.cols_uuid_1 FROM sys_dbms_tabs_merge_info m ");
if (vo.getInfo().getTableUuid1() != null && vo.getInfo().getTableUuid2() != null) {
stringBuilder.append(" WHERE m.table_uuid_1 = '" + vo.getInfo().getTableUuid1() + "'");
stringBuilder.append(" AND m.table_uuid_2 = '" + vo.getInfo().getTableUuid2() + "'");
} else {
stringBuilder.append(" WHERE 1=0");
}
stringBuilder.append(" )");
stringBuilder.append(" AND c.tabs_uuid = '" + vo.getInfo().getTableUuid1() + "'");
stringBuilder.append(" ORDER BY c.cols_order");
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
List<SysDbmsTabsColsInfo> list = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsColsInfo.class));
return list;
}
/**
* @方法名 page2
* @功能 TODO(这里用一句话描述这个方法的作用)
* @参数 @param vo
* @参数 @return
* @返回 List<SysDbmsTabsColsInfo>
* @author Administrator
* @throws
*/
public List<SysDbmsTabsColsInfo> page2(Pagination<SysDbmsTabsMergeInfo> vo) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append(" SELECT * FROM sys_dbms_tabs_cols_info c");
stringBuilder.append(" WHERE c.uuid not IN (");
stringBuilder.append(" SELECT m.cols_uuid_2 FROM sys_dbms_tabs_merge_info m ");
if (vo.getInfo().getTableUuid1() != null && vo.getInfo().getTableUuid2() != null) {
stringBuilder.append(" WHERE m.table_uuid_1 = '" + vo.getInfo().getTableUuid1() + "'");
stringBuilder.append(" AND m.table_uuid_2 = '" + vo.getInfo().getTableUuid2() + "'");
} else {
stringBuilder.append(" WHERE 1=0");
}
stringBuilder.append(" )");
stringBuilder.append(" AND c.tabs_uuid = '" + vo.getInfo().getTableUuid2() + "'");
stringBuilder.append(" ORDER BY c.cols_order");
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
List<SysDbmsTabsColsInfo> list = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsColsInfo.class));
return list;
}
public List<SysDbmsTabsInfo> findAllTableByTypeUuidAndUsername(SysDbmsTabsInfoVo vo) {
// 多条件时循环查询并找出userindex都有的表
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("select distinct * from sys_dbms_tabs_info a ");
stringBuilder.append(" where a.uuid in ( ");
stringBuilder.append(" select b.tabs_id from sys_roles_tabs_info b ");
stringBuilder.append(" where b.role_id in (");
stringBuilder.append(" select c.roles_id from sys_user_roles_info c");
stringBuilder.append(" where c.user_id in ( ");
stringBuilder.append(" select d.uuid from sys_user_base_info d");
stringBuilder.append(" where d.user_name = '" + vo.getUsername() + "'");
stringBuilder.append(" ) and c.checked = 1");
stringBuilder.append(" ) ");
stringBuilder.append(" ) and a.delete_flag = 0");
stringBuilder.append(" and a.type_uuid='" + vo.getInfo().getTypeUuid() + "' ");
stringBuilder.append(" order by a.tabs_order ");
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
List<SysDbmsTabsInfo> tabsList = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsInfo.class));
// 多条件查询
return tabsList;
}
@Override
public List<CandidateSnapshot> findPublishedCandidateSnapshotsWithApps(List<String> groups, Date time) {
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
Map<String, Object> param = Maps.newHashMap();
param.put("groups", groups);
param.put("time", time);
return template.query(FIND_WITHAPP_SQL, param, COUNT_SNAPSHOT_MAPPER);
}
@Override
public List<CandidateSnapshot> getSnapshotInVersion(ConfigMeta meta, Set<Long> versions) {
if (versions.size() == 0) {
return Lists.newArrayList();
}
NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
MapSqlParameterSource parameter = new MapSqlParameterSource();
parameter.addValue("group_id", meta.getGroup());
parameter.addValue("data_id", meta.getDataId());
parameter.addValue("profile", meta.getProfile());
parameter.addValue("versions", versions);
return nameJdbc.query(SELECT_VERSION_AND_OPERATOR, parameter, SNAPSHOT_MAPPER);
}
@Override
public List<CandidateSnapshot> getSnapshotAfterVersion(ConfigMeta meta, Long version) {
if (version < 0) {
return Lists.newArrayList();
}
NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
MapSqlParameterSource parameter = new MapSqlParameterSource();
parameter.addValue("group_id", meta.getGroup());
parameter.addValue("version", version);
parameter.addValue("profile", meta.getProfile());
parameter.addValue("data_id", meta.getDataId());
return nameJdbc.query(SELECT_VERSION_AFTER_OPERATOR, parameter, SNAPSHOT_MAPPER);
}
private static Object executeQuery(NamedParameterJdbcTemplate template, Map<String, Object> args, String query) {
QueryBlank queryBlank = prepareQuery(query, args);
query = queryBlank.getQuery();
args = queryBlank.getArgs();
if (isSelect(query)) {
List<Object[]> list = template.query(query, args, (rs, rowNum) -> {
return retrieveDataSet(rs);
});
return list.toArray();
} else {
MapSqlParameterSource paramSource = new MapSqlParameterSource(args);
GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
template.update(query, paramSource, generatedKeyHolder);
List<Map<String, Object>> keyList = generatedKeyHolder.getKeyList();
if (keyList != null) {
if (keyList.size() > 1) {
List<Object> rows = new ArrayList<>(keyList.size());
for (Map<String, Object> row : keyList) {
rows.add(row.values().toArray());
}
return rows.toArray();
} else if (keyList.size() == 1) {
return keyList.get(0).values().toArray();
}
}
}
return null;
}
private Object executeWithJdbcTemplate(NativeQueryInfo info) {
NamedParameterJdbcTemplate jdbcTemplate = ApplicationContextProvider.getApplicationContext().getBean(NamedParameterJdbcTemplate.class);
Map<String, Object> parametroList = new HashMap<>();
for (NativeQueryParameter parametro : info.getParameterList()) {
if (parametro.getValue() != null && info.getSql().contains(":" + parametro.getName())) {
parametroList.put(parametro.getName(), parametro.getValue());
}
}
BeanPropertyRowMapper<?> beanPropertyRowMapper = new BeanPropertyRowMapper<>(info.getAliasToBean());
if (info.getReturnType().getSimpleName().equals(Void.TYPE.getName())) {
jdbcTemplate.update(info.getSql(), parametroList);
return null;
}
if (info.isSingleResult()) {
if (info.isJavaObject()) {
return jdbcTemplate.queryForObject(info.getSql(), parametroList, info.getAliasToBean());
}
if (info.returnTypeIsOptional()) {
return getOptionalReturn(() -> jdbcTemplate.queryForObject(info.getSql(), parametroList, beanPropertyRowMapper));
}
return jdbcTemplate.queryForObject(info.getSql(), parametroList, beanPropertyRowMapper);
}
if (info.isJavaObject()) {
return jdbcTemplate.queryForList(info.getSql(), parametroList, info.getAliasToBean());
}
return jdbcTemplate.query(info.getSql(), parametroList, beanPropertyRowMapper);
}
public List<Book> findByTitle(String phrase) {
NamedParameterJdbcTemplate named =
new NamedParameterJdbcTemplate(jdbcTemplate);
SqlParameterSource namedParameters
= new MapSqlParameterSource("search_phrase", phrase);
String sql = "SELECT * FROM book WHERE title = :search_phrase";
return named.query(
sql,
namedParameters,
new BeanPropertyRowMapper<>(Book.class));
}
/**
* @方法名 findAllBySysTableInfoAndUsername
* @功能 TODO(这里用一句话描述这个方法的作用)
* @参数 @param sysDbmsTabsInfo
* @参数 @return
* @返回 List<SysDbmsTabsInfo>
* @author Administrator
* @throws
*/
public List<SysDbmsTabsInfo> findAllBySysTableInfoAndUsername(SysDbmsTabsInfo sysDbmsTabsInfo) {
// 多条件时循环查询并找出userindex都有的表
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("select distinct * from sys_dbms_tabs_info a ");
stringBuilder.append(" where a.uuid in ( ");
stringBuilder.append(" select b.tabs_id from sys_roles_tabs_info b ");
stringBuilder.append(" where b.role_id in (");
stringBuilder.append(" select c.roles_id from sys_user_roles_info c");
stringBuilder.append(" where c.user_id in ( ");
stringBuilder.append(" select d.uuid from sys_user_base_info d");
stringBuilder.append(" where d.user_name = '" + sysDbmsTabsInfo.getCreateUser() + "'");
stringBuilder.append(" ) and c.checked = 1");
stringBuilder.append(" ) ");
stringBuilder.append(" ) and a.delete_flag = 0");
if (sysDbmsTabsInfo.getTypeUuid() != null && !"".equals(sysDbmsTabsInfo.getTypeUuid())) {
stringBuilder.append(" and a.type_uuid = '" + sysDbmsTabsInfo.getTypeUuid() + "' ");
}
if (sysDbmsTabsInfo.getJdbcUuid() != null && !"".equals(sysDbmsTabsInfo.getJdbcUuid())) {
stringBuilder.append(" and a.jdbc_uuid = '" + sysDbmsTabsInfo.getJdbcUuid() + "' ");
}
stringBuilder.append(" order by a.tabs_order ");
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
List<SysDbmsTabsInfo> tabsList = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsInfo.class));
// 多条件查询
return tabsList;
}
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, "resourceQuery must has text!");
// 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;
}
/**
* @方法名 findAllTableByUser
* @功能 TODO(这里用一句话描述这个方法的作用)
* @参数 @param vo
* @参数 @return
* @返回 List<SysDbmsTabsInfo>
* @author Administrator
* @throws
*/
public List<SysDbmsTabsInfo> findAllTableByUser(SysDbmsTabsInfoVo vo) {
// 多条件时循环查询并找出userindex都有的表
List<SysDbmsTabsInfo> minusList = null;
for (MulteityParam val : vo.getParamList()) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("select distinct * from sys_dbms_tabs_info a ");
stringBuilder.append(" where a.uuid in ( ");
stringBuilder.append(" select b.tabs_id from sys_roles_tabs_info b ");
stringBuilder.append(" where b.role_id in (");
stringBuilder.append(" select c.roles_id from sys_user_roles_info c");
stringBuilder.append(" where c.user_id in ( ");
stringBuilder.append(" select d.uuid from sys_user_base_info d");
stringBuilder.append(" where d.user_name = '" + vo.getUsername() + "'");
stringBuilder.append(" ) and c.checked = 1");
stringBuilder.append(" ) ");
stringBuilder.append(" ) and a.delete_flag = 0");
stringBuilder.append(" and a.type_uuid='" + vo.getTypeUuid() + "' ");
stringBuilder.append(" and a.uuid in ( ");
stringBuilder.append(" select c.tabs_uuid from sys_dbms_tabs_cols_info c ");
stringBuilder.append(" where c.user_index='" + val.getUserIndex() + "' ");
stringBuilder.append(" and c.delete_flag = 0 ");
stringBuilder.append(" ) ");
stringBuilder.append(" order by a.tabs_order ");
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);
List<SysDbmsTabsInfo> tabsList = template.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SysDbmsTabsInfo.class));
// List<SysDbmsTabsInfo> tabsList = sysDbmsTabsInfoDao.findAllByUserIndexAndTypeUuid(val.getUserIndex(), vo.getTypeUuid());
if (tabsList == null) {
return null;
}
if (minusList == null) {
minusList = tabsList;
} else {
List<SysDbmsTabsInfo> existsList = new ArrayList<>();
// 多个userindex对比找到相同表
for (SysDbmsTabsInfo sysZhcxTab : minusList) {
for (SysDbmsTabsInfo sysZhcxTab2 : tabsList) {
if (sysZhcxTab.getUuid().equals(sysZhcxTab2.getUuid())) {
existsList.add(sysZhcxTab);
}
}
}
minusList = existsList;
if (minusList.size() == 0) {
return null;
}
}
}
// 多条件查询
return minusList;
}
private List<ExperimentToTaskLink> loadExperiments() {
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
return template.query(EXPERIMENT_IDS_QUERY, new ExperimentToTaskLinkRowMapper());
}