使用递归查询数据库中员工表中上下级关系

最新推荐文章于 2023-05-16 09:14:18 发布

W厚积薄发

最新推荐文章于 2023-05-16 09:14:18 发布

阅读量2.7k

点赞数

分类专栏： Spring 算法文章标签：递归员工表上下级关系

本文链接：https://blog.csdn.net/Altitude_/article/details/100927266

版权

Spring 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

算法

1 篇文章 0 订阅

订阅专栏

在数据库中存储着这样的一张表：

在查询的时候，username2为空为领导，他没有上级。通过username获取名称，然后通过获取的名称查询username2，来查询谁是名称的下级。例如：通过username获取wx数据，然后根据wx查询username2，获取两条数据，分别是username为：wx1和wx4.。依此类推，将所有数据上下级关系查询出来。

这里使用spring中jdbcTemplate来查询数据

1. 导入jar包

2 创建配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
         http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">
        
        <!-- 开启注解扫描 -->
        <context:component-scan base-package="com.wx"/>
        
        <!-- org\springframework\jdbc\datasource\DriverManagerDataSource.class -->
        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        	<!-- oracle配置 -->
        	<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        	<property name="url" value="jdbc:oracle:thin:@localhost:1521:idts"/>
        	<!-- mysql配置 -->
        	<!-- <property name="driverClassName" value="jdbc:mysql://127.0.0.1/student" />
        	<property name="url" value="com.mysql.jdbc.Driver"></property> -->
        	<property name="username" value="spring"/>
        	<property name="password" value="spring"/>
        </bean>
        <!-- org\springframework\jdbc\core\JdbcTemplate.class -->
        <bean id= "jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        	<property name="dataSource" ref="dataSource"/>
        </bean>
        <!-- 配置事务管理器 -->
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        	<property name="dataSource" ref="dataSource"></property>
        </bean>
        
        <tx:annotation-driven transaction-manager="transactionManager"/>
        
</beans>

3 .编写java代码

package com.wx.dao;

import java.util.List;
import java.util.Map;

import com.wx.domain.Account;

public interface AccountDao {
	//查询所有的信息
	public List<Map<String,Object>> findAllAccount();
	//查询UserName2的信息
	public List<Map<String,Object>> findUserName2(String username2);
}

package com.wx.dao.impl;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.wx.dao.AccountDao;
import com.wx.domain.Account;

@Repository("accountDao")
@Transactional(isolation=Isolation.DEFAULT,propagation=Propagation.REQUIRED)
public class AccountDaoImpl implements AccountDao {

	@Resource(name="jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	

	@Override
	public List<Map<String, Object>> findAllAccount() {
		// TODO Auto-generated method stub
		String sql = "select * from account";
		RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
		return jdbcTemplate.queryForList(sql,new Object[]{});
	}

	@Override
	public List<Map<String,Object>> findUserName2(String username2) {
		// TODO Auto-generated method stub
		String sql = "select * from account where username2=?";
		RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
		List<Map<String,Object>> account = this.jdbcTemplate.queryForList(sql, new Object[]{username2});
		return account;
	}
}

package com.wx.service;

import java.util.List;
import java.util.Map;

import com.wx.domain.Account;

public interface AccountService {
	//查询所有的信息
	public List<Map<String,Object>> findAllAccount();
	//查询UserName2的信息
	public List<Map<String, Object>> findUserName2(String username2);
}

package com.wx.service.impl;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.wx.dao.AccountDao;
import com.wx.domain.Account;
import com.wx.service.AccountService;


@Service("accountService")
public class AccountServiceImpl implements AccountService {

	@Resource(name = "accountDao")
	private AccountDao accountDao;

	@Override
	public List<Map<String, Object>> findAllAccount() {
		// TODO Auto-generated method stub
		return accountDao.findAllAccount();
	}

	@Override
	public List<Map<String, Object>> findUserName2(String username2) {
		// TODO Auto-generated method stub
		return this.accountDao.findUserName2(username2);
	}

}

package com.wx.test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.wx.domain.Account;
import com.wx.service.AccountService;



public class MyTest {
	ApplicationContext application = new ClassPathXmlApplicationContext("applicationContext.xml");
	AccountService accountService = (AccountService)application.getBean("accountService");
	public static void main(String[] args){
		MyTest myTest = new MyTest();
		System.out.println(myTest.test());
//		myTest.getAccountTree("");
		
	}
	
	
	public List<Map<String, Object>> test(){
		//查询所有数据
		List<Map<String, Object>>  lists = accountService.findAllAccount();
		//遍历每一条数据
		for(int i = 0; i < lists.size(); i++){
			Map<String,Object> map = lists.get(i);
			String username = (String)map.get("USERNAME");
			//查询每条数据的所有叶子节点
			List<Map<String, Object>>  listsByUserName2 = accountService.findUserName2(username);
			//判断每一条数据是否有叶子节点
			if(listsByUserName2.size()>0){
				map.put("children", getAccountTree(username));
			}
		}
		return lists;
	}
	
	public List<Map<String,Object>> getAccountTree(String username){
		List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
		//根据username查询它的子节点
		List<Map<String, Object>>  listsByUserName2 = accountService.findUserName2(username);
		if(listsByUserName2.size() > 0){
			for(int i = 0; i < listsByUserName2.size(); i++){
				Map<String,Object> map = listsByUserName2.get(i);
				List<Map<String,Object>> listsByUserName3 = accountService.findUserName2((String)map.get("USERNAME"));
				if(listsByUserName3.size()>0){
					map.put("children", getAccountTree((String)map.get("USERNAME")));
				}	
			}
			
		}
		return listsByUserName2;
	}
	
	 
	
}

4. 结果





[	{
		ID=sadfasdffcdsafasdfasdfasdfdsafcasdfsdaf, 
		USERNAME=wx4, 
		BALANCE=12222, 
		USERNAME2=wx
	}, 
	{  
		ID=75BD28315C3C4DA48B085D76243B045E, 
		USERNAME=wx, 
		BALANCE=12000, 
		USERNAME2=null, 
		children=[ {
						ID=sadfasdffcdsafasdfasdfasdfdsafcasdfsdaf, 
						USERNAME=wx4, 
						BALANCE=12222, 
						USERNAME2=wx
					}, 
					{
						ID=E3F657DEAD01492A9D5265B60EB88DA6, 
						USERNAME=wx1, 
						BALANCE=9000, 
						USERNAME2=wx, 
						children=[ {
										ID=DA403914A5014D9699EA07789C5F978F, 
										USERNAME=wx2, 
										BALANCE=12000, 
										USERNAME2=wx1, 
										children=[ 
													{
														ID=7470D8ACCB994A56A63431C28E6C21D4, 
														USERNAME=wx3, 
														BALANCE=13000, 
														USERNAME2=wx2
												    }
												 ]
									}]
					}
				]}, 
	{
		ID=7470D8ACCB994A56A63431C28E6C21D4, 
		USERNAME=wx3, 
		BALANCE=13000, 
		USERNAME2=wx2
	}, 
	{
		ID=E3F657DEAD01492A9D5265B60EB88DA6, 
		USERNAME=wx1, 
		BALANCE=9000, 
		USERNAME2=wx, 
		children=[{
					ID=DA403914A5014D9699EA07789C5F978F, 
					USERNAME=wx2, 
					BALANCE=12000, 
					USERNAME2=wx1, 
					children=[{
								ID=7470D8ACCB994A56A63431C28E6C21D4, 
								USERNAME=wx3, 
								BALANCE=13000, 
								USERNAME2=wx2
							}]
				}]
	}, 
	{
		ID=DA403914A5014D9699EA07789C5F978F, 
		USERNAME=wx2, 
		BALANCE=12000, 
		USERNAME2=wx1, 
		children=[{
					ID=7470D8ACCB994A56A63431C28E6C21D4, 
					USERNAME=wx3, 
					BALANCE=13000, 
					USERNAME2=wx2
				}]
	}
]