JPA关联查询

（1）新建A表：b_stationregiontree.sql（区域表）

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for b_stationregiontree
-- ----------------------------
DROP TABLE IF EXISTS `b_stationregiontree`;
CREATE TABLE `b_stationregiontree`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rela_tree_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `parent_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `inf_value` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `inf_code` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `inf_type` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `inf_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `station_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `inf_mark` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `rela_tree_state` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `station_code` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `station_name`(`station_name`) USING BTREE,
  INDEX `inf_value`(`inf_value`) USING BTREE,
  INDEX `rela_tree_id`(`rela_tree_id`) USING BTREE,
  INDEX `inf_mark`(`inf_mark`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of b_stationregiontree
-- ----------------------------
INSERT INTO `b_stationregiontree` VALUES (353, '734', '1', 'XAY', '', '1', '西安', '西安', '', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (355, '735', '353', '1', '', '1', '候车室', '西安', '1', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (357, '736', '353', '1', 'GaoJiaCeng', '1', '检票口', '西安', '2', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (359, '737', '353', '1', 'ZhanTaiCeng', '1', '站台', '西安', '3', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (361, '738', '353', '1', 'ZhanTaiCeng', '1', '股道', '西安', '4', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (363, '739', '353', '1', 'ChuZhanCeng', '1', '出站口', '西安', '5', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (365, '740', '353', '1', '', '1', '售票区', '西安', '6', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (367, '741', '353', '1', '', '1', '广场', '西安', '7', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (369, '742', '353', '1', '', '1', '综合楼', '西安', '8', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (371, '743', '353', '1', '', '1', '办公区', '西安', '9', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (373, '744', '353', '1', '', '1', '进站口', '西安', '10', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (375, '745', '353', '1', '', '1', '进站通道', '西安', '11', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (377, '746', '353', '1', '', '1', '机房', '西安', '12', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (379, '747', '353', '1', '', '1', '总控室', '西安', '13', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (381, '748', '353', '1', '', '1', '出站通道', '西安', '14', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (383, '749', '353', '1', '', '1', '卫生间', '西安', '15', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (385, '750', '353', '1', '', '1', '服务区', '西安', '16', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (387, '751', '353', '1', '', '1', '夹层', '西安', '17', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (663, '932', '353', '45', '', '1', '候车专区', '西安', '18', '1', NULL);
INSERT INTO `b_stationregiontree` VALUES (1000, '456', '111', NULL, NULL, NULL, NULL, '乌鲁木齐', NULL, NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

 

————————————————————————————————————————————————————

（2）新建B表：b_station_dict.sql（车站表）

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for b_station_dict
-- ----------------------------
DROP TABLE IF EXISTS `b_station_dict`;
CREATE TABLE `b_station_dict`  (
  `s_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_bureau_code` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '路局编码',
  `s_station_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '车站code',
  `s_station_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '车站名称',
  `s_station_spell` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '车站拼音码',
  `s_station_telecode` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '车站编码',
  `s_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '类型',
  `s_bureau_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '路局id',
  `s_station_long_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '车站长code',
  `energy_saving_mode` bit(1) NULL DEFAULT NULL COMMENT '节能模式',
  PRIMARY KEY (`s_id`) USING BTREE,
  UNIQUE INDEX `UK_ano37mnikv1k4npr8n4j43r45`(`s_station_telecode`) USING BTREE,
  INDEX `FK56jpv4a8ntaf9mbb1hsd4hss8`(`s_bureau_id`) USING BTREE,
  CONSTRAINT `b_station_dict_ibfk_1` FOREIGN KEY (`s_bureau_id`) REFERENCES `b_bureau_dict` (`s_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '车站' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of b_station_dict
-- ----------------------------
INSERT INTO `b_station_dict` VALUES ('297eb8627f482f92017f488004b60007', NULL, '1', '长沙北', NULL, '1', '0', '2c9c830366bf43460166bf449aa20000', NULL, NULL);
INSERT INTO `b_station_dict` VALUES ('297eb8627f482f92017f49692d500011', NULL, '456', '456', NULL, '1223', '0', '2c9c830366bf43460166bf449aa20000', NULL, NULL);
INSERT INTO `b_station_dict` VALUES ('2c9a307a7d2e044f017d374e90970006', NULL, '1', '乌鲁木齐', NULL, 'WAR', '0', '2c9c830366bf43460166bf449e9a0011', NULL, NULL);
INSERT INTO `b_station_dict` VALUES ('8a83092878c8f7fd0178c9313e360000', 'Y', '39473', '西安', NULL, 'XAY', '0', '2c9c830366bf43460166bf449e9a0011', '00Y000000000000000000000000000000000000XiAn002085', b'1');

SET FOREIGN_KEY_CHECKS = 1;

 

=============================

A表：b_stationregiontree.sql（区域表）

B表：b_station_dict.sql（车站表）

 

问题：

　　后台通过getStationIds的公共方法，可以得到该登录用户的stationId，现在想要展示A表的树结构，前提是只展示A表中车站名对应的车站id是在已登录用户中的stationId中的那些数据。

也就是说A表可能存储了多个站的数据，现在只展示登录站的数据。

 

分析：

    A表中没有stationId,因为A表和B表可以通过A表的station_name和B表的s_station_name进行关联，这样可以查出A表中每条数据对应的stationId，有了A表数据对应的stationId，就可以

拿着关联得到的stationId与登录的用户对应的车站Id进行对比，只找出那些登录用户的stationId对应的与A表stationId相同的数据。

 

JPAsql：

@Query(value = "select r.* from b_stationregiontree r left join b_station_dict b on r.station_name = b.s_station_name where b.s_id\n" +
        "in (:stationIds)", nativeQuery = true)
Set<StationRegionTree> findByStationIds(@Param("stationIds") Set<String> stationIds);

 

代码分析：

　　两表关联后得到了一张大表，这个大表里面有我们需要的A表stationname对应的stationId，即为：b.s_id；

　　因为我们只需要展示A表的数据，所以写成 r.* ；

　　in (：stationIds)  这里传过来的stationIds就是登录后通过公共方法获得的登录用户的stationIds。这样就可以查出登录用户的stationId对应的

A表中的数据。