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

package com.database1.dao;

import com.model.po.*;
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<ColumnData> 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, " +
            "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 from bi_data_connectors")
    List<DataConnectorList> getDataConnectorList();

    /*
    插入数据源配置
     */
    @Insert("INSERT INTO bi_data_connectors(data_name,data_note,data_tag,con_type,LOAD_OBJECT,DB_CONFIG,columns_config,used_number,create_by,create_date, table_name) " +
            "VALUES (#{dataName}, #{note}, #{dataTag}, #{type}, #{loadObject}, #{dbConfig},#{columnConfig}, #{usedNumber},#{createBy}, to_date(#{createDate},'YYYY-MM-DD hh24:mi:ss'), #{tableName})")
    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>" +
            "where id = #{dataId}" +
            "</script>")
    void updateData(DataConnector dataConnector);

    /*
    删除数据源
     */
    @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(ADDRASS, CREATE_DATE, DATABASE_TYPE, DATA_NAME, bases_NAME, PASS_WORD, PORT, USER_NAME, note)" +
            "values(#{addrass}, to_date(#{createDate},'YYYY-MM-DD hh24:mi:ss'), #{databaseType}, #{dataName}, #{name}, #{passWord}, #{port}, #{userName}, #{note})")
    void inputDataBases(Databases databases);

    /*
    查询单个数据库连接ID
     */
    @Select("select id from BI_DATABASES where  id= #{id}")
    int 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);
}