首页 > 其他 > 详细

源数据与目标数据的映射(2张无关联的表联查)

时间:2021-07-07 12:40:56      阅读:19      评论:0      收藏:0      [点我收藏+]

 

表1:源数据表

 DROP TABLE IF EXISTS `src_data_source`;
 CREATE TABLE `src_data_source`  (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键id‘,
   `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘‘ COMMENT ‘数据源名称‘,
   `source_type` tinyint(2) NOT NULL DEFAULT 0 COMMENT ‘数据源类型:1.MySQL,5.Oracle,10.达梦‘,
   `jdbc_url` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘‘ COMMENT ‘jdbc-url‘,
   `jdbc_user` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘‘ COMMENT ‘jdbc-user‘,
   `jdbc_pwd` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘‘ COMMENT ‘jdbc-pwd‘,
   `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘‘ COMMENT ‘描述‘,
   `source_status` bit(1) NOT NULL DEFAULT b‘0‘ COMMENT ‘状态:0.false-停用,1.true-启用‘,
   `version` int(11) NULL DEFAULT NULL COMMENT ‘版本(乐观锁保留字段)‘,
   `create_time` datetime NULL DEFAULT NULL COMMENT ‘创建时间‘,
   `update_time` datetime NULL DEFAULT NULL COMMENT ‘修改时间‘,
   `create_user` bigint(20) NULL DEFAULT NULL COMMENT ‘创建人‘,
   `update_user` bigint(20) NULL DEFAULT NULL COMMENT ‘修改人‘,
   PRIMARY KEY (`id`) USING BTREE
 ) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = ‘源数据源‘ ROW_FORMAT = Dynamic;
 ?
 -- ----------------------------
 -- Records of src_data_source
 -- ----------------------------
 INSERT INTO `src_data_source` VALUES (6, ‘腾讯云‘, 1, ‘jdbc:mysql://192.168.1.109:3306/dragon?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT‘, ‘root‘, ‘root‘, ‘wqeqw‘, b‘0‘, NULL, ‘2021-07-01 18:07:58‘, ‘2021-07-06 15:29:22‘, 1, 1);
 INSERT INTO `src_data_source` VALUES (11, ‘delta‘, 1, ‘jdbc:mysql://192.168.1.109:3306/delta?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT‘, ‘root‘, ‘root‘, ‘109‘, b‘0‘, NULL, ‘2021-07-06 15:30:56‘, ‘2021-07-06 15:31:59‘, 1, 1);
 INSERT INTO `src_data_source` VALUES (12, ‘ops‘, 1, ‘jdbc:mysql://192.168.1.109:3306/ops?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT‘, ‘root‘, ‘root‘, ‘109‘, b‘0‘, NULL, ‘2021-07-06 15:32:33‘, NULL, 1, NULL);
 ?
 SET FOREIGN_KEY_CHECKS = 1;

表2 :目标数据表

 DROP TABLE IF EXISTS `target_data_source`;
 CREATE TABLE `target_data_source`  (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键id‘,
   `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT ‘‘ COMMENT ‘数据源名称‘,
   `source_type` tinyint(2) NULL DEFAULT 0 COMMENT ‘数据源类型:1.MySQL,5.Oracle,10.达梦‘,
   `jdbc_url` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT ‘‘ COMMENT ‘jdbc-url‘,
   `jdbc_user` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT ‘‘ COMMENT ‘jdbc-user‘,
   `jdbc_pwd` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT ‘‘ COMMENT ‘jdbc-pwd‘,
   `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT ‘‘ COMMENT ‘描述‘,
   `version` int(11) NULL DEFAULT NULL COMMENT ‘版本(乐观锁保留字段)‘,
   `create_time` datetime NULL DEFAULT NULL COMMENT ‘创建时间‘,
   `update_time` datetime NULL DEFAULT NULL COMMENT ‘修改时间‘,
   `create_user` bigint(20) NULL DEFAULT NULL COMMENT ‘创建人‘,
   `update_user` bigint(20) NULL DEFAULT NULL COMMENT ‘修改人‘,
   PRIMARY KEY (`id`) USING BTREE
 ) ENGINE = InnoDB AUTO_INCREMENT = 1412339668933988354 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = ‘目标数据源‘ ROW_FORMAT = Dynamic;
 ?
 -- ----------------------------
 -- Records of target_data_source
 -- ----------------------------
 INSERT INTO `target_data_source` VALUES (1, ‘aaa‘, 1, ‘aaa‘, ‘aaa‘, ‘aaa‘, ‘aaa‘, NULL, ‘2021-07-06 00:00:00‘, NULL, 1, NULL);
 INSERT INTO `target_data_source` VALUES (1412335586508914692, ‘bbb‘, 1, ‘bbb‘, ‘bbb‘, ‘bbb‘, ‘bbb‘, NULL, ‘2021-07-06 00:00:00‘, NULL, 1, NULL);
 INSERT INTO `target_data_source` VALUES (1412335586508914693, ‘fadsfadf‘, 1, ‘fadfad‘, ‘adfadf‘, ‘adfad‘, ‘fadfadfadf‘, NULL, ‘2021-07-06 00:00:00‘, NULL, 1, NULL);
 INSERT INTO `target_data_source` VALUES (1412339668933988353, ‘hfdsf‘, 1, ‘dasd‘, ‘fasd‘, ‘dasd‘, ‘sdgd‘, NULL, NULL, NULL, NULL, NULL);
 ?
 SET FOREIGN_KEY_CHECKS = 1;

表3 : 关联表

 DROP TABLE IF EXISTS `src_target_source_relation`;
 CREATE TABLE `src_target_source_relation`  (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键id‘,
   `src_id` bigint(20) NOT NULL DEFAULT 0 COMMENT ‘源id‘,
   `target_id` bigint(20) NOT NULL DEFAULT 0 COMMENT ‘目标id‘,
   PRIMARY KEY (`id`) USING BTREE
 ) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = ‘目标数据源‘ ROW_FORMAT = Dynamic;
 ?
 -- ----------------------------
 -- Records of src_target_source_relation
 -- ----------------------------
 INSERT INTO `src_target_source_relation` VALUES (14, 6, 1);
 INSERT INTO `src_target_source_relation` VALUES (15, 6, 1412335586508914692);
 INSERT INTO `src_target_source_relation` VALUES (16, 12, 1412335586508914693);
 INSERT INTO `src_target_source_relation` VALUES (17, 6, 1412339668933988353);
 ?
 SET FOREIGN_KEY_CHECKS = 1;

目标:查询源数据和目标数据的名称关联

 select sds.name srcName, tds.name tdsName from src_data_source sds , target_data_source tds,src_target_source_relation stsr
         where sds.id=stsr.src_id  and stsr.target_id = tds.id

通过源数据表来查询

SrcDataSourceMapper.xml

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <mapper namespace="cn.deltaphone.dragon.modular.ops.mapper.SrcDataSourceMapper">
 ?
     <!-- 通用查询映射结果 -->
     <resultMap id="BaseResultMap" type="cn.deltaphone.dragon.modular.ops.entity.SrcDataSource">
         <id column="id" property="id" />
         <result column="name" property="name" />
         <result column="source_type" property="sourceType" />
         <result column="jdbc_url" property="jdbcUrl" />
         <result column="jdbc_user" property="jdbcUser" />
         <result column="jdbc_pwd" property="jdbcPwd" />
         <result column="description" property="description" />
         <result column="source_status" property="sourceStatus" />
         <result column="version" property="version" />
         <result column="create_time" property="createTime" />
         <result column="update_time" property="updateTime" />
         <result column="create_user" property="createUser" />
         <result column="update_user" property="updateUser" />
     </resultMap>
 ?
     <resultMap id="BaseResultMap2" type="cn.deltaphone.dragon.modular.ops.model.TdsModel">
         <result column="srcName" property="srcName" />
         <result column="tdsName" property="tdsName" />
     </resultMap>
 ?
     <select id="selectAll" resultMap="BaseResultMap2">
        select sds.name srcName, tds.name tdsName from src_data_source sds , target_data_source tds,src_target_source_relation stsr
        where sds.id=stsr.src_id and stsr.target_id = tds.id
     </select>
 ?
 </mapper>

mapper层

 public interface SrcDataSourceMapper extends BaseMapper<SrcDataSource> {
     List<TdsModel> selectAll();
 }

service 层

 @Service
 public class SrcDataSourceService extends ServiceImpl<SrcDataSourceMapper, SrcDataSource> {
     @Autowired
     private SrcDataSourceMapper srcDataSourceMapper;
     
     public List<TdsModel> getList() {
         List<TdsModel> srcList = srcDataSourceMapper.selectAll();
         return srcList;
    }
 }

controller层

 @Controller
 @RequestMapping("/tds")
 public class TdsController extends BaseController {
     @ResponseBody
     @RequestMapping("/list")
     public List<TdsModel> list() {
         List<TdsModel> l = srcDataSourceService.getList();
         return l;
    }
 }
 // 此处对返回结果进行简化,根据前端框架可以自行调整返回结果集 比如layui需要返回msg,code,count,data

model

 @Data
 public class TdsModel {
     private String srcName;
     private String tdsName;
 }

页面显示结果

技术分享图片

 

源数据与目标数据的映射(2张无关联的表联查)

原文:https://www.cnblogs.com/hippo-dolphin/p/14980470.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!