整体架构
目的
输出结果
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200826140520175.png#pic_center
根据上图
读取dist_relation 表中 path 0-49953-928-5233-51887-52656
关联lk_user 表中 role_id
对path 截取 反向遍历
分别判断 52656 的第一个 role_id= 3 的uid 设为 cloud_uid
第二个 role_id =3 的uid 设为 two_cloud_uid
第一个 role_id =4 的uid 设为 star_uid
第二个 role_id =4 的uid 设为 two_star_uid
将结果输出到 vini_user_relation
创建三张数据表
dist_relation
lk_user
vini_user_relation (插入的结果表)
测试数据
SELECT id,uid,uid_dist_role_id AS uid_role_id,path FROM dist_relation WHERE uid IN('52656','1058')
id uid uid_role_id path
4 1058 4 0-1058
7902 52656 3 0-49953-928-5233-51887-52656
SELECT uid,role_id FROM lk_user WHERE uid IN (51887,5233,928,49953,0) ORDER BY FIND_IN_SET(uid , '(51887,5233,928,49953,0)')
uid role_id
51887 3
5233 3
928 4
49953 4
引入jar包
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12-beta-3</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.9</version>
<scope>compile</scope>
</dependency>
</dependencies>
配置文件
db.properties 连接数据库配置
# 报表开发库
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhot/ssm?useSSL=true
jdbc.username=root
jdbc.password=root
SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--mybatis的主配置文件-->
<configuration>
<properties resource="db.properties"></properties>
<!--配置环境-->
<environments default="development">
<!--配置MySQL的环境-->
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<!--配置数据源(连接池)-->
<dataSource type="POOLED">
<!--配置连接数据库的4个基本信息-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--指定映射配置文件的位置,映射配置文件-->
<mappers>
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
UserMapper.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="com.sumei.mapper.UserMapper">
<!--配置根据uid 查询数据-->
<select id="findUserById" parameterType="int" resultType="com.sumei.pojo.User">
select * from dist_relation where uid = #{uid}
</select>
<!--查询所有数据-->
<select id="findAll" resultType="com.sumei.pojo.User">
select id,uid,uid_dist_role_id as uid_role_id,path from dist_relation -- WHERE uid in('52656','1058')
</select>
<select id="findUserAll" parameterType="String" resultType="com.sumei.pojo.Users">
select uid,role_id from lk_user where uid in (51887,5233,928,49953,0) ORDER BY FIND_IN_SET(uid , '(51887,5233,928,49953,0)')
</select>
<!--#{} 拼接字符串会自动添加 '' 号,使用${_parameter} 不会添加-->
<select id="findUserId" parameterType="String" resultType="com.sumei.pojo.UserIDs">
select uid,role_id from lk_user where uid in ${_parameter}
</select>
<!-- 单条数据插入测试, 批量插入很慢-->
<insert id="inserts" parameterType="com.sumei.pojo.Relation_stat">
insert into VINI_BI.vini_user_relation (uid, role, cloud_uid,
star_uid, two_cloud_uid, two_star_uid
)values (#{uid,jdbcType=VARCHAR}, #{role,jdbcType=INTEGER},
#{cloud_uid,jdbcType=VARCHAR},#{star_uid,jdbcType=VARCHAR},
#{two_cloud_uid,jdbcType=VARCHAR}, #{two_star_uid,jdbcType=VARCHAR}, #{ctime,jdbcType=VARCHAR})
</insert>
<insert id="insert" >
INSERT INTO VINI_BI.vini_user_relation (uid, role, cloud_uid,star_uid, two_cloud_uid, two_star_uid )VALUES ("0", 1,"0","0","0", "0" )
</insert>
<!--批量插入数据,很快-->
<insert id="insertss" parameterType="java.util.List" useGeneratedKeys="false">
insert into vini_user_relation (uid, role, cloud_uid,
star_uid, two_cloud_uid, two_star_uid,ctime
)values
<foreach collection="list" item="item" index="index" separator =",">
(
#{item.uid,jdbcType=VARCHAR}, #{item.role,jdbcType=INTEGER},
#{item.cloud_uid,jdbcType=VARCHAR},#{item.star_uid,jdbcType=VARCHAR},
#{item.two_cloud_uid,jdbcType=VARCHAR}, #{item.two_star_uid,jdbcType=VARCHAR}
, #{item.ctime,jdbcType=VARCHAR}
)
</foreach>
</insert>
</mapper>
mapper
package com.sumei.mapper;
import com.sumei.pojo.Relation_stat;
import com.sumei.pojo.User;
import com.sumei.pojo.UserIDs;
import com.sumei.pojo.Users;
import java.util.List;
public interface UserMapper {
//根据id值查询数据
public User findUserById(int uid) throws Exception;
//查询所有用户数据
List<User> findAll() throws Exception;
List<UserIDs> findUserId(String uids) throws Exception;
List<Users> findUserAll() throws Exception;
public int inserts(Relation_stat relation_stat);
public int insertss(List<Relation_stat> relation_stat);
}
pojo
package com.sumei.pojo;
/**
* @program: kettle_ava
* @description:
* @author: wenglei
* @create: 2020-08-14 09:32
**/
public class Relation_stat {
private String uid ;
private int role;
private String cloud_uid;
private String star_uid;
private String two_cloud_uid;
private String two_star_uid;
private String ctime;
public Relation_stat() {
}
public Relation_stat(String uid, int role, String cloud_uid, String star_uid, String two_cloud_uid, String two_star_uid, String ctime) {
this.uid = uid;
this.role = role;
this.cloud_uid = cloud_uid;
this.star_uid = star_uid;
this.two_cloud_uid = two_cloud_uid;
this.two_star_uid = two_star_uid;
this.ctime = ctime;
}
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid;
}
public int getRole() {
return role;
}
public void setRole(int role) {
this.role = role;
}
public String getCloud_uid() {
return cloud_uid;
}
public void setCloud_uid(String cloud_uid) {
this.cloud_uid = cloud_uid;
}
public String getStar_uid() {
return star_uid;
}
public void setStar_uid(String star_uid) {
this.star_uid = star_uid;
}
public String getTwo_cloud_uid() {
return two_cloud_uid;
}
public void setTwo_cloud_uid(String two_cloud_uid) {
this.two_cloud_uid = two_cloud_uid;
}
public String getTwo_star_uid() {
return two_star_uid;
}
public void setTwo_star_uid(String two_star_uid) {
this.two_star_uid = two_star_uid;
}
public String getCtime() {
return ctime;
}
public void setCtime(String ctime) {
this.ctime = ctime;
}
@Override
public String toString() {
return "Relation_stat{" +
"uid='" + uid + '\'' +
", role=" + role +
", cloud_uid='" + cloud_uid + '\'' +
", star_uid='" + star_uid + '\'' +
", two_cloud_uid='" + two_cloud_uid + '\'' +
", two_star_uid='" + two_star_uid + '\'' +
", ctime='" + ctime + '\'' +
'}';
}
}
package com.sumei.pojo;
/**
* @program: kettle_ava
* @description:
* @author: wenglei
* @create: 2020-08-13 16:39
**/
public class User {
private int id;
private String uid;
private int uid_role_id;
private String path;
public User() {
}
public User(int id, String uid, int uid_role_id, String path) {
this.id = id;
this.uid = uid;
this.uid_role_id = uid_role_id;
this.path = path;
}
public void setId(int id) {
this.id = id;
}
public void setUid(String uid) {
this.uid = uid;
}
public void setUid_role_id(int uid_role_id) {
this.uid_role_id = uid_role_id;
}
public void setPath(String path) {
this.path = path;
}
public int getId() {
return id;
}
public String getUid() {
return uid;
}
public int getUid_role_id() {
return uid_role_id;
}
public String getPath() {
return path;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", uid='" + uid + '\'' +
", uid_role_id=" + uid_role_id +
", path='" + path + '\'' +
'}';
}
}
package com.sumei.pojo;
import javax.swing.*;
/**
* @program: kettle_ava
* @description:
* @author: wenglei
* @create: 2020-08-19 14:23
**/
public class UserIDs {
private String uid;
private int role_id;
public UserIDs() {
}
public UserIDs(String uid, int role_id) {
this.uid = uid;
this.role_id = role_id;
}
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid;
}
public int getRole_id() {
return role_id;
}
public void setRole_id(int role_id) {
this.role_id = role_id;
}
}
package com.sumei.pojo;
/**
* @program: kettle_ava
* @description:
* @author: wenglei
* @create: 2020-08-19 10:58
**/
public class Users {
private String uid;
private int role_id;
public Users() {
}
public Users(String uid, int role_id) {
this.uid = uid;
this.role_id = role_id;
}
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid;
}
public int getRole_id() {
return role_id;
}
public void setRole_id(int role_id) {
this.role_id = role_id;
}
@Override
public String toString() {
return "Users{" +
"uid='" + uid + '\'' +
", role_id=" + role_id +
'}';
}
}
dao
package com.sumei.dao;
import com.sumei.mapper.UserMapper;
import com.sumei.pojo.Relation_stat;
import com.sumei.pojo.User;
import com.sumei.pojo.UserIDs;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @program: kettle_ava
* @description:
* @author: wenglei
* @create: 2020-08-21 09:26
**/
public class Main {
public static void main(String[] args) {
try {
//定义resource,这个resource就是全局配置文件的名字,文件是放在resources包下的,
//注意这里的resource和resources包是没啥联系的
String resource = "SqlMapConfig.xml";
//创建输入流,将全局配置文件变为一个输入流
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建sqlSession工厂,通过工厂的build方法
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
String format = df.format(new Date());
System.out.println(format);// new Date()为获取当前系统时间
//通过工厂打开session
SqlSession sqlSession = sqlSessionFactory.openSession();
//通过sqlsession来获得映射文件
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
ArrayList<Relation_stat> list = new ArrayList<Relation_stat>();
int count =0;
List<User> all = userMapper.findAll();
for (User user : all) {
Relation_stat relation_stat = new Relation_stat();
//为了看到效果,我们将这个用户的信息打印出来
//System.out.println(user);
String path = user.getPath();
int uid_role_id = user.getUid_role_id();
String uid = user.getUid();
//调用方法,对路径进行截取
String[] paths = path.split("-");
//如果用户身份是会员,需要判断,用户上级云店,上级星链
String s = "";
if( paths.length > 2){
for (int i = paths.length - 1; i >= 0; i--) {
if(i != paths.length - 1){
if (i == paths.length - 2) {
s = "(" + paths[i] + ",";
}else if (i == 0) {
s = s + paths[i] + ")";
} else {
s = s + paths[i] + ",";
}
}
}
} else{
s = "0";
}
//如果 s 等于零,说明该用户没有上级,直接输出数据
if(s == "0"){
relation_stat.setUid(uid);
relation_stat.setRole(uid_role_id);
relation_stat.setCloud_uid("0");
relation_stat.setStar_uid("0");
relation_stat.setTwo_cloud_uid("0");
relation_stat.setTwo_star_uid("0");
relation_stat.setCtime(format);
}else {
//FIND_IN_SET 让sql按照 s 路径进行查询数据
String uids = s + " ORDER BY FIND_IN_SET(uid ,\'" + s + "\' )";
// System.out.println(uids);
List<UserIDs> userId = userMapper.findUserId(uids);
String cloud_uid = "0";
String star_uid = "0";
String two_cloud_uid = "0";
String two_star_uid = "0";
for (UserIDs users : userId) {
//获取用户上级的uid与 角色级别 role_id
String uid1 = users.getUid();
int role_id = users.getRole_id();
if (uid_role_id == 2) {
if (cloud_uid == "0" && role_id == 3) {
cloud_uid = uid1;
} else if (star_uid == "0" && role_id > 3) {
star_uid = uid1;
} else if (star_uid != "0" && role_id > 3) {
two_star_uid = uid1;
} else if (cloud_uid != "0" && role_id == 3) {
two_cloud_uid = uid1;
}
} else if (uid_role_id == 3) {
if (cloud_uid == "0" && role_id == 3) {
cloud_uid = uid1;
} else if (star_uid == "0" && role_id > 3) {
star_uid = uid1;
} else if (star_uid != "0" && role_id > 3) {
two_star_uid = uid1;
} else if (cloud_uid != "0" && role_id == 3) {
two_cloud_uid = uid1;
}
} else if (uid_role_id > 3) {
if (star_uid == "0" && role_id > 3) {
star_uid = uid1;
} else if (star_uid != "0" && role_id > 3) {
two_star_uid = uid1;
}
}
relation_stat.setUid(uid);
relation_stat.setRole(uid_role_id);
relation_stat.setCloud_uid(cloud_uid);
relation_stat.setStar_uid(star_uid);
relation_stat.setTwo_cloud_uid(two_cloud_uid);
relation_stat.setTwo_star_uid(two_star_uid);
relation_stat.setCtime(format);
}
}
System.out.println("uid: "+relation_stat.getUid()+" Role: "+relation_stat.getRole()
+" Star_uid: "+relation_stat.getStar_uid()+" Cloud_uid: " +relation_stat.getCloud_uid()
+" Two_cloud_uid: "+relation_stat.getTwo_cloud_uid()+" Two_star_uid: "+relation_stat.getTwo_star_uid());
list.add(relation_stat);
++count;
}
//可能会出现异常,处理异常
try {
//批量插入数据
int i = userMapper.insertss(list);
if (i == count){
sqlSession.commit();
System.out.println(df.format(new Date()));// new Date()为获取当前系统时间
System.out.println("执行成功,插入数据:"+count+" 条");
}
}catch (Exception e){
e.printStackTrace();
System.out.println("执行失败!!");
}
}catch (Exception e){
e.printStackTrace();
}
}
}