java_mybatis_maven

整体架构

在这里插入图片描述

目的

输出结果
在这里插入图片描述
![在这里插入图片描述](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();
        }

    }
}

最终实现效果

在这里插入图片描述

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值