platform-bi-server/src/main/java/com/dao/DataConnectorMapper.java

package com.dao;

import com.model.po.DataConnector;
import com.model.po.DataConnectorList;
import com.model.po.Databases;
import com.model.vo.configVo.GroupInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
@Mapper
@Repository
public interface DataConnectorMapper {

//    @Select("select d.column_name as columnName, d.DATA_TYPE as columnType, b.comments as remarks from " +
//            "(select column_name,DATA_TYPE from user_tab_cols where Table_Name = #{tableName}) d," +
//            "(select * from user_col_comments where Table_Name = #{tableName}) b" +
//            " where d.column_name = b.column_name order by columnName")
//    List<ColumnScreenInfo> getColumn(@Param("tableName") String tableName);

//    @Select("<script>" +
//            "select * from bi_data_connectors where" + "1=1" +
//            "<if test=\"id != null\"> and id = #{id} </if>" +
//            "<if test=\"dataName != null\"> and data_name = #{dataName} </if>" +
//            "<if test=\"dataTag != null\"> and data_tag = #{dataTag} </if>" +
//            "<if test=\"configuration != null\"> and configuration = #{configuration} </if>" +
//            "<if test=\"columnConfig != null\"> and columns_config = #{columnConfig} </if>"  +
//            "<if test=\"usedNumber != null\"> and used_number = #{usedNumber} </if>" +
//            "<if test=\"createBy != null\">and create_by = #{createBy}</if>" +
//            "</script>")
//    List<DataConnector> getAllData(DataConnector dataConnector);

    /*
    查询单个数据源
     */
    @Select("select ID as dataId, DATA_NAME as dataName, DATA_NOTE as note, DATA_TAG as dataTag, COLUMNS_CONFIG as columnConfig, LOAD_OBJECT as loadObject," +
            "CREATE_BY as createBy, USED_NUMBER as userNumber, CREATE_DATE as createDate,DB_CONFIG as dbConfig, BD_group as connectorGroup," +
            "CON_TYPE as type from bi_data_connectors where id = #{id}")
    DataConnector getOneData(int id);

    /*
    查询数据源列表
     */
    @Select("select id as dataId, con_type as type, data_name as dataName, data_tag as dataTag, data_note as note, create_by as createBy, create_date as createDate," +
            " used_number as usedNumber, db_config as dbConfig ,BD_group as connectorGroup from bi_data_connectors")
    List<DataConnectorList> getDataConnectorList();

    /*
    插入数据源配置
     */
    @Insert("INSERT INTO bi_data_connectors(id,data_name,data_note,data_tag,con_type,LOAD_OBJECT,DB_CONFIG,columns_config,used_number,create_by,create_date, table_name, BD_GROUP) " +
            "VALUES (#{dataId},#{dataName}, #{note}, #{dataTag}, #{type}, #{loadObject}, #{dbConfig},#{columnConfig}, #{usedNumber},#{createBy}, to_date(#{createDate},'YYYY-MM-DD hh24:mi:ss'), #{tableName}, #{connectorGroup})")
    @SelectKey(before=true,keyProperty="dataId",resultType=int.class,statement="SELECT BI_DASHBOARDS_squence.nextval from dual",keyColumn = "id")
    void insertDataConnector(DataConnector dataConnector);

    /*
    更新数据源配置
     */
    @Update("<script>" +
            "UPDATE bi_data_connectors set "  +
            "data_name = #{dataName}" +
            "<if test=\"dataTag != null\"> , data_tag = #{dataTag} </if>" +
            "<if test=\"loadObject != null\"> , LOAD_OBJECT = #{loadObject} </if>" +
            "<if test=\"columnConfig != null\"> , columns_config = #{columnConfig} </if>" +
            "<if test=\"dbConfig != null\"> , DB_CONFIG = #{dbConfig} </if>" +
            "<if test=\"usedNumber != null\"> , used_number = #{usedNumber} </if>" +
            "<if test=\"createBy != null\"> , create_by = #{createBy} </if>" +
            "<if test=\"type != null\"> , con_TYPE = #{type} </if>" +
            "<if test=\"note != null\"> , DATA_NOTE = #{note} </if>" +
            "<if test=\"tableName != null\"> , table_name = #{tableName} </if>" +
            "<if test=\"connectorGroup != null\"> , BD_GROUP = #{connectorGroup} </if>"+
            "where id = #{dataId}" +
            "</script>")
    void updateData(DataConnector dataConnector);

    /*
    更新数据源配置的分组
     */
    @Update("update bi_data_connectors set BD_GROUP = #{connectGroup} where id = #{dataId}")
    void updateConfigGroup(@Param("dataId") int dataId, @Param("connectGroup") int connectGroup);

    /*
    删除数据源
     */
    @Delete("<script>" +
            "delete from bi_data_connectors where id in " +
            "("+
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}"+
            "</foreach>" +
            ")"+
            "</script>")
    void deleteData(List<Integer> idList);

    /*
    保存数据库连接
     */
    @Insert("insert into BI_DATABASES(id,ADDRASS, CREATE_DATE, DATABASE_TYPE, DATA_NAME, bases_NAME, PASS_WORD, PORT, USER_NAME, note)" +
            "values(#{id},#{addrass}, to_date(#{createDate},'YYYY-MM-DD hh24:mi:ss'), #{databaseType}, #{dataName}, #{name}, #{passWord}, #{port}, #{userName}, #{note})")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT bi_databases_squence.nextval from dual",keyColumn = "id")
    void inputDataBases(Databases databases);

    /*
    查询单个数据库连接ID
     */
    @Select("select pass_word from BI_DATABASES where  id= #{id}")
    String getBasesById(int id);

    /*
    查询数据库配置列表
     */
    @Select("select id, bases_name as name, note, addrass, data_name as dataName, DATABASE_TYPE as databaseType," +
            " PASS_WORD as passWord, USER_NAME as userName, PORT, CREATE_DATE as createDate from bi_databases")
    List<Databases> getDatabasesList();

    /*
    修改数据库配置列表
     */
    @Update("<script>"+
            "UPDATE BI_DATABASES set "  +
            "BASES_NAME = #{name}" +
            "<if test=\"addrass != null\"> , ADDRASS = #{addrass} </if>" +
            "<if test=\"port != null\"> , PORT = #{port} </if>" +
            "<if test=\"databaseType != null\"> , DATABASE_TYPE = #{databaseType} </if>" +
            "<if test=\"dataName != null\"> , DATA_NAME = #{dataName} </if>" +
            "<if test=\"userName != null\"> , USER_NAME = #{userName} </if>" +
            "<if test=\"passWord != null\"> , PASS_WORD = #{passWord} </if>" +
            "<if test=\"note != null\"> , NOTE = #{note} </if>" +
            "where id = #{id}" +
            "</script>")
    void updatabases(Databases databases);

    /*
    删除数据库配置
     */
    @Delete("<script>" +
            "delete from bi_databases where id in " +
            "("+
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}"+
            "</foreach>" +
            ")"+
            "</script>")
    void deleteDatabases(List<Integer> idList);

    /*
    查询数据源列数据
     */
    @Select("select columns_Config as columnConfig from bi_data_connectors " +
            " where id =#{id}")
    DataConnector getColumnData(int id);

    /*
    创建数据源分组
     */
    @Insert("insert into bi_base_group_by(bb_id, bb_group_name, bb_index, bb_father_id, create_by, create_date)" +
            "values(#{id}, #{groupName}, #{groupIndex}, #{fatherId}, #{createBy}, to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT bi_base_group_by_squence.nextval from dual",keyColumn = "bb_id")
    void setConnectorGroup(GroupInfo group);

    /*
    更新数据源分组
     */
    @Update("<script>"+
            "UPDATE bi_base_group_by set "+
            "bb_group_name = #{groupName}" +
            "<if test=\"groupIndex != null\"> , bb_index = #{groupIndex} </if>" +
            "<if test=\"fatherId != null\"> , bb_father_id = #{fatherId} </if>" +
            "<if test=\"createBy != null\"> , create_by = #{createBy} ,</if>" +
            "updata_date = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') "+
            " where bb_id = #{id}" +
            "</script>")
    void updataConnectorGroup(GroupInfo groupInfo);

    /*
    删除分组
     */
    @Delete("<script>" +
            "delete from bi_base_group_by where bb_id in" +
            "(" +
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}" +
            " </foreach>" +
            ")" +
            "</script>")
    void delConnectorGroup(List<Integer> isList);

    /*
    查询分组是否有子分组
     */
    @Select("select BB_GROUP_NAME from bi_base_group_by where bb_father_id = #{id}")
    List<String> getFatherId(int id);

    /*
    查询是否有数据源正在使用
     */
    @Select("select data_name from bi_data_connectors where bd_group = #{id}")
    List<String> getConName(int id);

    /*
    查询分组
     */
    @Select("select bb_id as id, bb_group_name as groupName, bb_index as groupIndex, bb_father_id as fatherId, create_by as createBy," +
            "create_date as createDate from bi_base_group_by")
    List<GroupInfo> getConnectorGroup();

    /*
    查询数据源是否关联图表
     */
    @Select("select chart_name from bi_charts where bd_data_ID = #{id}")
    List<String> getChartsName(int id);
}