Java Code Examples for org.springframework.jdbc.core.JdbcTemplate#queryForList()

/**
 */
public List<DatabaseUpdateDO> getUpdateHistory()
{
  accessCheck(false);
  final JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
  final List<Map<String, Object>> dbResult = jdbc.queryForList("select * from t_database_update order by update_date desc");
  final List<DatabaseUpdateDO> result = new ArrayList<DatabaseUpdateDO>();
  for (final Map<String, Object> map : dbResult) {
    final DatabaseUpdateDO entry = new DatabaseUpdateDO();
    entry.setUpdateDate((Date) map.get("update_date"));
    entry.setRegionId((String) map.get("region_id"));
    entry.setVersionString((String) map.get("version"));
    entry.setExecutionResult((String) map.get("execution_result"));
    final PFUserDO executedByUser = Registry.instance().getUserGroupCache().getUser((Integer) map.get("executed_by_user_fk"));
    entry.setExecutedBy(executedByUser);
    entry.setDescription((String) map.get("description"));
    result.add(entry);
  }
  return result;
}
 

public List<Map<String, Object>> queryObjJoinDataByPageCondition(String innerSql, int start,int end, Object[] paramArray) {

		/*		JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
		.getDbSource(dbName);*/
		JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
		String sql = "";
		String tempType=calcuDbType();
		if(tempType!=null && tempType.equals("mysql")){
		//if(dbType!=null && dbType.equals("mysql")){
			int pageRows=end-start;
			String limit=start+","+pageRows;
			sql=innerSql+ " limit "+limit;
		}else{
			String startLimit=" WHERE NHPAGE_RN >= "+start;
			String endLimit=" WHERE ROWNUM < "+end;
			sql="SELECT * FROM ( SELECT NHPAGE_TEMP.*, ROWNUM NHPAGE_RN FROM ("+ innerSql +" ) NHPAGE_TEMP "+endLimit+" ) "+ startLimit;
		}
		logger.debug(sql);
		logger.debug(Arrays.toString(paramArray));
		List<Map<String, Object>> retList0 = jdbcTemplate.queryForList(sql,paramArray);
		//add 201807 ning
		//List<Map<String, Object>> retList=changeOutKeyCase4List(retList0);
		//add 201902 ning
		List<Map<String, Object>> retList=ignoreKeyCase((List)retList0);		
		return retList;
	}
 

public List<Map<String, Object>> queryObjJoinByCondition(String sql,Object[] paramArray) {

		/*		JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
		.getDbSource(dbName);*/
		JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
		logger.debug(sql);
		logger.debug(Arrays.toString(paramArray));
		List<Map<String, Object>> retList0 = jdbcTemplate.queryForList(sql,paramArray);
		//add 201807 ning
		//List<Map<String, Object>> retList=changeOutKeyCase4List(retList0);
		
		//add 201902 ning
		List<Map<String, Object>> retList=ignoreKeyCase((List)retList0);
		
		return retList;
	}
 

public static <T> List<T> findList(final String dbKey, String sql, Class<T> clazz,Object... param) {
	List<T> list;
	JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
	
	if (ArrayUtils.isEmpty(param)) {
		list = jdbcTemplate.queryForList(sql,clazz);
	} else {
		list = jdbcTemplate.queryForList(sql,clazz,param);
	}
	return list;
}
 

private List<Map<String, Object>> getResultRows(Integer id, String variables) {
    Query query = queryDAO.find(id);
    DatabaseConnection databaseConnection = databaseConnectionDAO.find(query.getDatabaseConnectionId());

    List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
    try {
        BasicDataSource dataSource = dataSourceFactory.getBasicDataSource(
                databaseConnection.getUrl(),
                databaseConnection.getUsername(),
                databaseConnection.getPassword()
        );

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        if (variables != null) {
            List<Object> arguments = new ArrayList<Object>();
            String[] extractedVariables = variables.split(",");

            for (String variable : extractedVariables) {
                try {
                    arguments.add(Long.parseLong(variable));
                } catch(NumberFormatException noLong) {
                    try {
                        arguments.add(Double.parseDouble(variable));
                    } catch(NumberFormatException noDouble) {
                        arguments.add(variable);
                    }
                }
            }

            rows = jdbcTemplate.queryForList(query.getContent(), arguments.toArray());
        } else {
            rows = jdbcTemplate.queryForList(query.getContent());
        }
    } catch (Exception e) {
        logger.debug(e);
    }

    return rows;
}
 

/**
 * 检查数据
 *
 * @param mediaSourceInfo
 * @param sql
 * @return
 */
public static List<Map<String, Object>> executeSql(MediaSourceInfo mediaSourceInfo, String sql) {

    try {
        DataSource dataSource = DataSourceFactory.getDataSource(mediaSourceInfo);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    } catch (Exception e) {
        throw new RuntimeException("JDBC执行错误", e);
    } finally {
        DataSourceFactory.invalidate(mediaSourceInfo, () -> null);
    }

}
 

public List<Map<String, Object>> queryObjByCondition(String tableName, String condition,String cols, String orders,Object[] paramArray,int[] typeArray) {
	//String tableName=changeTableNameCase(otableName);
	/*		JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
	.getDbSource(dbName);*/
	JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
	String sql = "select "+cols+" from " + tableName + " where "+condition+" order by "+orders;
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	List<Map<String, Object>> retList0 = jdbcTemplate.queryForList(sql,paramArray,typeArray);
	//add 201807 ning
	//List<Map<String, Object>> retList=changeOutKeyCase4List(retList0);	
	//add 201902 ning
	List<Map<String, Object>> retList=ignoreKeyCase((List)retList0);
	return retList;
}
 

public static List<Map<String, Object>> findList(final String dbKey, String sql, Object... param) {
    List<Map<String, Object>> list;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);

    if (ArrayUtils.isEmpty(param)) {
        list = jdbcTemplate.queryForList(sql);
    } else {
        list = jdbcTemplate.queryForList(sql, param);
    }
    return list;
}
 

private void assertUsers(JdbcTemplate jdbcTemplate, String... users) {
	List<String> expected = Arrays.asList(users);
	Collections.sort(expected);
	List<String> actual = jdbcTemplate.queryForList("select name from user", String.class);
	Collections.sort(actual);
	assertEquals("Users in database;", expected, actual);
}
 

private void assertUsers(JdbcTemplate jdbcTemplate, String... users) {
	List<String> expected = Arrays.asList(users);
	Collections.sort(expected);
	List<String> actual = jdbcTemplate.queryForList("select name from user", String.class);
	Collections.sort(actual);
	assertEquals("Users in database;", expected, actual);
}
 

public void doExport(File baseDir, JdbcTemplate jdbcTemplate,
        String siteCode) throws Exception {
    File dir = new File(baseDir, siteCode);
    dir.mkdirs();

    List<Map<String, Object>> list = jdbcTemplate
            .queryForList("select id from CMS_CATALOG");

    for (Map<String, Object> map : list) {
        String catalogId = map.get("id").toString();
        new CatalogExporter().doExport(dir, jdbcTemplate, catalogId);
    }
}
 

private void assertUsers(JdbcTemplate jdbcTemplate, String... users) {
	List<String> expected = Arrays.asList(users);
	Collections.sort(expected);
	List<String> actual = jdbcTemplate.queryForList("select name from user", String.class);
	Collections.sort(actual);
	assertEquals("Users in database;", expected, actual);
}
 

public static <T> List<T> findList(final String dbKey, String sql, Class<T> clazz, Object... param) {
    List<T> list;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);

    if (ArrayUtils.isEmpty(param)) {
        list = jdbcTemplate.queryForList(sql, clazz);
    } else {
        list = jdbcTemplate.queryForList(sql, clazz, param);
    }
    return list;
}
 

public void exportArticles(File dir, JdbcTemplate jdbcTemplate,
        String catalogId) throws Exception {
    String sql = "select id from CMS_ARTICLE where CATALOG_ID=?";
    List<Map<String, Object>> list = jdbcTemplate.queryForList(sql,
            catalogId);

    for (Map<String, Object> map : list) {
        String articleId = map.get("id").toString();
        new ArticleExporter().doExport(dir, jdbcTemplate, articleId);
    }
}
 

public static <T> List<T> findList(final String dbKey, String sql, Class<T> clazz, Object... param) {
    List<T> list;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);

    if (ArrayUtils.isEmpty(param)) {
        list = jdbcTemplate.queryForList(sql, clazz);
    } else {
        list = jdbcTemplate.queryForList(sql, clazz, param);
    }
    return list;
}
 

public List<Map<String, Object>> queryObjByCondition(String tableName, String condition,String cols, String orders,Object[] paramArray) {
	//String tableName=changeTableNameCase(otableName);
	/*		JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
	.getDbSource(dbName);*/
	JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
	String sql = "select "+cols+" from " + tableName + " where "+condition+" order by "+orders;
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	List<Map<String, Object>> retList0 = jdbcTemplate.queryForList(sql,paramArray);
	//add 201807 ning
	//List<Map<String, Object>> retList=changeOutKeyCase4List(retList0);	
	//add 201902 ning
	List<Map<String, Object>> retList=ignoreKeyCase((List)retList0);
	return retList;
}
 

public List<MicroMetaBean> queryMetaBeanByCondition(String tableName, String condition,Object[] paramArray,int[] typeArray) {
	List<MicroMetaBean> retBeanList=new ArrayList();
	/*		JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
	.getDbSource(dbName);*/
	JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
	String sql = "select * from " + tableName + " where "+condition;
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	List<Map<String, Object>> retList = jdbcTemplate.queryForList(sql,paramArray,typeArray);
	if(retList==null){
		return retBeanList;
	}
	
	for(Map<String,Object> rowMap:retList){
		MicroMetaBean metaBean = new MicroMetaBean();
		metaBean.setId((String) rowMap.get("id"));
		metaBean.setMeta_content((String) rowMap.get("meta_content"));
		metaBean.setMeta_key((String) rowMap.get("meta_key"));
		metaBean.setMeta_name((String) rowMap.get("meta_name"));
		metaBean.setMeta_type((String) rowMap.get("meta_type"));
		metaBean.setRemark((String) rowMap.get("remark"));
		metaBean.setCreate_time((Date) rowMap.get("create_time"));
		metaBean.setUpdate_time((Date) rowMap.get("update_time"));
		retBeanList.add(metaBean);
	}
	return retBeanList;
}
 

private void assertUsers(JdbcTemplate jdbcTemplate, String... users) {
	List<String> expected = Arrays.asList(users);
	Collections.sort(expected);
	List<String> actual = jdbcTemplate.queryForList("select name from user", String.class);
	Collections.sort(actual);
	assertEquals("Users in database;", expected, actual);
}
 

public static List<Map<String, Object>> findList(final String dbKey, String sql, Object... param) {
	List<Map<String, Object>> list;
	JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
	
	if (ArrayUtils.isEmpty(param)) {
		list = jdbcTemplate.queryForList(sql);
	} else {
		list = jdbcTemplate.queryForList(sql, param);
	}
	return list;
}
 

private void assertUsers(JdbcTemplate jdbcTemplate, String... users) {
	List<String> expected = Arrays.asList(users);
	Collections.sort(expected);
	List<String> actual = jdbcTemplate.queryForList("select name from user", String.class);
	Collections.sort(actual);
	assertEquals("Users in database;", expected, actual);
}