Java Code Examples for org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#query()

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());
}