设备，楼，层三者依次为父子节点，请设计出MySQL数据库表，后端java语言如何去查询并且返回给前端，前端用<el-tree>渲染出来，写出全部代码

好的，我会根据你的要求回答你的问题。首先，我会设计MySQL数据库的表：

CREATE TABLE `device` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '设备ID',
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '设备名称',
`parent_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '父设备ID',
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `device_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `device` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='设备表';

CREATE TABLE `building` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '楼ID',
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '楼名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='楼表';

CREATE TABLE `floor` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '层ID',
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '层名称',
`building_id` int(10) UNSIGNED NOT NULL COMMENT '所属楼ID',
PRIMARY KEY (`id`),
KEY `building_id` (`building_id`),
CONSTRAINT `floor_ibfk_1` FOREIGN KEY (`building_id`) REFERENCES `building` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='层表';

这样我们就有了设备表，楼表和层表，其中设备表中的parent_id是指父设备的ID，如果这个设备是顶级设备，则parent_id设置为0。另外，我们通过外键来建立设备、楼和层的关系，方便后面的查询。

接下来，我会给出后端Java代码：

/**
* 获取所有设备、楼、层节点
*
* @return 节点列表
*/
@GetMapping("/nodes")
public List<TreeNode> getNodes() {
List<Device> devices = deviceService.getAllDevices();
List<Building> buildings = buildingService.getAllBuildings();
List<Floor> floors = floorService.getAllFloors();

// 构造设备节点
List<TreeNode> deviceNodes = new ArrayList<>();
for (Device device : devices) {
TreeNode node = new TreeNode();
node.setId(device.getId());
node.setLabel(device.getName());
node.setParentId(device.getParentId());
node.setType(TreeNode.TYPE_DEVICE);
deviceNodes.add(node);
}

// 构造楼节点
List<TreeNode> buildingNodes = new ArrayList<>();
for (Building building : buildings) {
TreeNode node = new TreeNode();
node.setId(building.getId());
node.setLabel(building.getName());
node.setType(TreeNode.TYPE_BUILDING);
buildingNodes.add(node);
}

// 构造层节点
List<TreeNode> floorNodes = new ArrayList<>();
for (Floor floor : floors) {
TreeNode node = new TreeNode();
node.setId(floor.getId());
node.setLabel(floor.getName());
node.setParentId(floor.getBuildingId());
node.setType(TreeNode.TYPE_FLOOR);
floorNodes.add(node);
}

// 构造树结构
List<TreeNode> treeNodes = new ArrayList<>();
treeNodes.addAll(deviceNodes);
treeNodes.addAll(buildingNodes);
treeNodes.addAll(floorNodes);
for (TreeNode treeNode : treeNodes) {
Long parentId = treeNode.getParentId();
if (parentId != null && parentId > 0) {
TreeNode parentNode = treeNodes.stream()
.filter(node -> node.getId().equals(parentId))
.findFirst().orElse(null);
treeNode.setParent(parentNode);
if (parentNode != null) {
parentNode.addChild(treeNode);
}
}
}

// 返回根节点
return treeNodes.stream()
.filter(node -> node.getParent() == null)
.collect(Collectors.toList());
}

对于前端部分，我们可以使用Vue.js和element-ui框架来实现。我们可以使用<el-tree>组件来展示树形结构，具体代码如下：

<template>
<el-tree :data="nodes" :expand-on-click-node="false"></el-tree>
</template>

<script>
import axios from 'axios'

export default {
data() {
return {
nodes: []
}
},
mounted() {
axios.get('/api/nodes').then(res => {
this.nodes = res.data
})
}
}
</script>

这样就可以将后端返回的树形结构渲染出来了。

完整代码如下：

Java：

public class Device {
private Long id;
private String name;
private Long parentId;
// 省略getter和setter
}

public class Building {
private Long id;
private String name;
// 省略getter和setter
}

public class Floor {
private Long id;
private String name;
private Long buildingId;
// 省略getter和setter
}

public class TreeNode {
public static final String TYPE_DEVICE = "device";
public static final String TYPE_BUILDING = "building";
public static final String TYPE_FLOOR = "floor";

private Long id;
private String label;
private Long parentId;
private String type;
private TreeNode parent;
private List<TreeNode> children = new ArrayList<>();

// 省略getter和setter

public void addChild(TreeNode child) {
children.add(child);
}
}

@RestController
@RequestMapping("/api")
public class TreeController {
@Autowired
private DeviceService deviceService;
@Autowired
private BuildingService buildingService;
@Autowired
private FloorService floorService;

/**
* 获取所有设备、楼、层节点
*
* @return 节点列表
*/
@GetMapping("/nodes")
public List<TreeNode> getNodes() {
List<Device> devices = deviceService.getAllDevices();
List<Building> buildings = buildingService.getAllBuildings();
List<Floor> floors = floorService.getAllFloors();

// 构造设备节点
List<TreeNode> deviceNodes = new ArrayList<>();
for (Device device : devices) {
TreeNode node = new TreeNode();
node.setId(device.getId());
node.setLabel(device.getName());
node.setParentId(device.getParentId());
node.setType(TreeNode.TYPE_DEVICE);
deviceNodes.add(node);
}

// 构造楼节点
List<TreeNode> buildingNodes = new ArrayList<>();
for (Building building : buildings) {
TreeNode node = new TreeNode();
node.setId(building.getId());
node.setLabel(building.getName());
node.setType(TreeNode.TYPE_BUILDING);
buildingNodes.add(node);
}

// 构造层节点
List<TreeNode> floorNodes = new ArrayList<>();
for (Floor floor : floors) {
TreeNode node = new TreeNode();
node.setId(floor.getId());
node.setLabel(floor.getName());
node.setParentId(floor.getBuildingId());
node.setType(TreeNode.TYPE_FLOOR);
floorNodes.add(node);
}

// 构造树结构
List<TreeNode> treeNodes = new ArrayList<>();
treeNodes.addAll(deviceNodes);
treeNodes.addAll(buildingNodes);
treeNodes.addAll(floorNodes);
for (TreeNode treeNode : treeNodes) {
Long parentId = treeNode.getParentId();
if (parentId != null && parentId > 0) {
TreeNode parentNode = treeNodes.stream()
.filter(node -> node.getId().equals(parentId))
.findFirst().orElse(null);
treeNode.setParent(parentNode);
if (parentNode != null) {
parentNode.addChild(treeNode);
}
}
}

// 返回根节点
return treeNodes.stream()
.filter(node -> node.getParent() == null)
.collect(Collectors.toList());
}
}

Vue.js：

<template>
<el-tree :data="nodes" :expand-on-click-node="false"></el-tree>
</template>

<script>
import axios from 'axios'

export default {
data() {
return {
nodes: []
}
},
mounted() {
axios.get('/api/nodes').then(res => {
this.nodes = res.data
})
}
}
</script>

注：这是一个示例，实际使用时需要根据具体需求进行适当调整。