com.datastax.insight.agent.dao.InsightDAO Maven / Gradle / Ivy
package com.datastax.insight.agent.dao;
import com.datastax.insight.agent.entity.Asset;
import com.datastax.insight.agent.entity.Project;
import com.datastax.insight.agent.entity.Subject;
import com.datastax.insight.core.entity.Cache;
import com.datastax.insight.agent.constants.FlowTypeConstants;
import com.datastax.insight.agent.constants.MIDKey;
import com.datastax.insight.agent.entity.Component;
import com.datastax.insight.agent.entity.SettingItem;
import com.datastax.insight.core.entity.DBSource;
import com.datastax.insight.core.entity.Datum;
import com.datastax.insight.core.entity.Model;
import com.datastax.util.db.ConnUtil;
import com.datastax.util.lang.StringUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
public class InsightDAO {
protected Connection conn;
private static final String URL = "jdbc:mysql://${db.address}/${db.schema}?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true";
public InsightDAO() {
// String host = ConfigManager.getSection("jdbc.url");
// conn = ConnUtil.createConnection("com.mysql.jdbc.Driver",
// host,
// ConfigManager.getSection("jdbc.username"), ConfigManager.getSection("jdbc.password"));
String host = URL.replace("${db.address}", Cache.getCache("address").toString())
.replace("${db.schema}", Cache.getCache("schema").toString());
conn = ConnUtil.createConnection("com.mysql.jdbc.Driver",
host,
Cache.getCache("username").toString(),
Cache.getCache("password").toString());
}
public List getSparkComponents() {
QueryRunner qRunner = new QueryRunner();
String sql = "select * from d_component where platform='spark'";
List cList = null;
try {
cList = qRunner.query(conn, sql, new BeanListHandler<>(Component.class));
} catch (SQLException ex) {
System.out.println("获取Spark组件列表时出错：" + ex.getMessage());
}
return cList;
}
public List getAllComponents() {
QueryRunner qRunner = new QueryRunner();
String sql = "select * from d_component";
List cList = null;
try {
cList = qRunner.query(conn, sql, new BeanListHandler<>(Component.class));
/*List idList = new ArrayList<>();
for (Component c : cList) {
idList.add(String.valueOf(c.getId()));
}
sql = "select parameter_json from component where id in (" + StringUtil.listToString(idList, ",") + ")";
PreparedStatement pstm = conn.prepareStatement(sql);
ResultSet rs = pstm.executeQuery();
int index=0;
if (rs.next()) {
InputStream inputStream = rs.getAsciiStream(1);
String json= StringUtil.streamToString(inputStream);
cList.get(index).setParameter_json(json);
index++;
}*/
} catch (SQLException ex) {
System.out.println("获取组件列表时出错：" + ex.getMessage());
}
return cList;
}
public List getDBSources() {
QueryRunner qRunner = new QueryRunner();
String sql = "select ds.id as id,ds.url as url,ds.user as user,ds.password as password,dt.driver_class as driver " +
" from d_dbsource ds,m_dbtype dt where ds.type=dt.id";
//String sql="select * from dbsource";
List cList = null;
try {
cList = qRunner.query(conn, sql, new BeanListHandler<>(DBSource.class));
} catch (SQLException ex) {
System.out.println("获取数据源列表时出错：" + ex.getMessage());
}
return cList;
}
public List getModels() {
QueryRunner qRunner = new QueryRunner();
String sql = "select id, model_name as name,usage_description as description,model_path as path from d_model_manage";
List cList = null;
try {
cList = qRunner.query(conn, sql, new BeanListHandler<>(Model.class));
} catch (SQLException ex) {
System.out.println("获取通用模型列表时出错：" + ex.getMessage());
}
return cList;
}
public Model getModel(String modelId) {
QueryRunner qRunner = new QueryRunner();
String sql = null;
/*
if(modelId.startsWith("C-")) {
sql = "select id, name, description, file_name as fileName, model_class as modelClass, path from d_model where id = ?";
} else if(modelId.startsWith("P-")) {
sql = "select id, name, description, file_name as fileName, model_class as modelClass, path from d_model_manage where id = ?";
}*/
sql = "select id, model_name as name,usage_description as description,model_path as path, model_class as modelClass from d_model_manage where id = ?";
Long id = Long.parseLong(modelId.substring(2));
Model model = null;
try {
model = qRunner.query(conn, sql, id, new BeanHandler<>(Model.class));
} catch (SQLException ex) {
System.out.println("获取通用模型列表时出错：" + ex.getMessage());
}
return model;
}
public List getDatum() {
QueryRunner qRunner = new QueryRunner();
String sql = "select id, name, description, node_type as nodeType, parent_id as parentId, file_name as fileName, path from d_data_set";
List datumList = null;
try {
datumList = qRunner.query(conn, sql, new BeanListHandler<>(Datum.class));
} catch (SQLException ex) {
System.out.println("获取数据源列表时出错：" + ex.getMessage());
}
return datumList;
}
public String getDAGJson(long projectId, long flowId) {
String sql;
/* if(projectId == 0L) {
sql = "select dag_json from d_example_flow where id=?";
} else {
sql = "select dag_json from d_flow where id=?";
}*/
sql = "select dag_json from d_dag_flow where id=?";
try {
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setLong(1, flowId);
ResultSet rs = pstm.executeQuery();
if (rs.next()) {
InputStream inputStream = rs.getAsciiStream(1);
return StringUtil.streamToString(inputStream);
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public void saveRestConfig(String name, Long componentId) {
//
String sql = "INSERT INTO restservice(`name`, `cid`) VALUES (?, ?);";
QueryRunner qRunner = new QueryRunner();
try {
qRunner.update(conn, sql, name, componentId);
} catch (SQLException e) {
e.printStackTrace();
}
}
public int saveModel(Long flowId, Long batchId, Long flowVersionId, String modelName, String modelClass, String path) {
int result = -1;
try {
Long projectId = getProjectId(flowId);
String flowCodeById = getFlowCodeById(flowId);
QueryRunner runner = new QueryRunner();
/* if (projectId == 0L) {
String sql4update = "update d_model_manage set name=?, model_class=?, path=?,batch_id=? where project_id=? and flow_id=?";
result = runner.update(conn, sql4update, new Object[]{modelName, modelClass, path,batchId, projectId, flowId});
System.out.println("model is updated with success, path: " + path);
return result;
}*/
String sql = "select count(*) from d_model_manage where flow_id=? and flow_type=?";
long count = runner.query(conn, sql, new ScalarHandler(), new Object[]{flowId, FlowTypeConstants.VISUALIZATION});
String identity = getModelIdentity(System.currentTimeMillis());
Long uId = -1L;
Object userId = Cache.getCache("userId");
if (userId != null) {
try {
uId = Long.parseLong(userId.toString());
} catch (Exception e) {
e.printStackTrace();
}
}
Project project = getProjectById(projectId);
Long subject_id=-1L;
String projectName="";
String subjectName="";
String subjectCode="";
String assetCode="";
String assetTypeCode="";
if (project!=null){
projectName=project.getName();
if (project.getSubject_id()!=null){
Subject subject = getSubjectById(project.getSubject_id());
subject_id=project.getSubject_id();
if (subject!=null){
subjectName=subject.getName();
subjectCode=subject.getCode();
}
}
if (project.getAsset_id()!=null){
Asset asset = getAssetById(project.getAsset_id());
if (asset!=null){
assetCode=asset.getAsset_code();
if (asset.getAsset_type_id()!=null){
assetTypeCode= getAssetTypeCodeById(asset.getAsset_type_id());
}
}
}
}
if (count > 0) {
String sql4update = "update d_model_manage set model_name=?, model_class=?, model_path=? ,identity=? ,update_time=? ,model_release=?,model_code=? where flow_id=? and flow_type=?";
result = runner.update(conn, sql4update, new Object[]{modelName, modelClass, path, identity,new Date(),0,flowCodeById, flowId, FlowTypeConstants.VISUALIZATION});
System.out.println("model is updated with success, path: " + path);
} else {
Long maxId = getId(MIDKey.M_MODEL_MANAGER);
String sql4insert = "insert into d_model_manage (id, project_id, flow_id, model_name, model_class, " +
"model_path,flow_type,identity,project_name,subject_id,subject_name" +
",subject_code,asset_code,asset_type_code,create_time,status,on_offline,model_tpye,model_release,model_code) " +
"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
result = runner.update(conn, sql4insert, new Object[]{maxId, projectId,
flowId, modelName, modelClass, path,
FlowTypeConstants.VISUALIZATION, identity,projectName,subject_id
,subjectName,subjectCode,assetCode,assetTypeCode,new Date(),"NOT_RELEASE","OFF_LINE","INTERNAL",0,flowCodeById});
System.out.println("model is saved with success, path: " + path);
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public String getFlowCodeById(Long id){
QueryRunner qRunner = new QueryRunner();
String sql = null;
sql = "SELECT `code` from d_dag_flow WHERE id=?";
String code = "";
try {
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setLong(1, id);
ResultSet rs = pstm.executeQuery();
if (rs.next()) {
InputStream inputStream = rs.getAsciiStream(1);
code= StringUtil.streamToString(inputStream);
}
} catch (SQLException ex) {
System.out.println("获取assettype时出错：" + ex.getMessage());
}
return code;
}
public Project getProjectById(Long id){
QueryRunner qRunner = new QueryRunner();
String sql = null;
/*
if(modelId.startsWith("C-")) {
sql = "select id, name, description, file_name as fileName, model_class as modelClass, path from d_model where id = ?";
} else if(modelId.startsWith("P-")) {
sql = "select id, name, description, file_name as fileName, model_class as modelClass, path from d_model_manage where id = ?";
}*/
sql = "select name,asset_id,subject_id from d_project where id = ?";
Project project = null;
try {
project = qRunner.query(conn, sql, id, new BeanHandler<>(Project.class));
} catch (SQLException ex) {
System.out.println("获取poject时出错：" + ex.getMessage());
}
return project;
}
public Subject getSubjectById(Long id){
QueryRunner qRunner = new QueryRunner();
String sql = null;
sql = "select code, name from d_subject where id = ?";
Subject subject = null;
try {
subject = qRunner.query(conn, sql, id, new BeanHandler<>(Subject.class));
} catch (SQLException ex) {
System.out.println("获取subject列表时出错：" + ex.getMessage());
}
return subject;
}
public Asset getAssetById(Long id){
QueryRunner qRunner = new QueryRunner();
String sql = null;
sql="select asset_code,asset_type_id from d_asset where id = ?";
Asset asset = null;
try {
asset = qRunner.query(conn, sql, id, new BeanHandler<>(Asset.class));
} catch (SQLException ex) {
System.out.println("获取asset时出错：" + ex.getMessage());
}
return asset;
}
public String getAssetTypeCodeById(Long id){
QueryRunner qRunner = new QueryRunner();
String sql = null;
sql = "select asset_type_code from d_asset_type where id = ?";
String asset = "";
try {
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setLong(1, id);
ResultSet rs = pstm.executeQuery();
if (rs.next()) {
InputStream inputStream = rs.getAsciiStream(1);
asset= StringUtil.streamToString(inputStream);
}
} catch (SQLException ex) {
System.out.println("获取assettype时出错：" + ex.getMessage());
}
return asset;
}
private String getModelIdentity(Long time) {
DateTimeFormatter ftf = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
StringBuilder identity = new StringBuilder(ftf.format(LocalDateTime.ofInstant(Instant.ofEpochMilli(time), ZoneId.systemDefault())));
Random random = new Random();
int value = random.nextInt(99);
if (value >= 10) {
identity.append(value);
} else {
identity.append("0" + value);
}
return identity.toString();
}
private long getId(String key) throws SQLException {
Long id = null;
String sqlId = "select pk_value from m_id where pk_name=?";
QueryRunner runner = new QueryRunner();
Long maxId = runner.query(conn, sqlId, new ScalarHandler(), new Object[]{key});
if (maxId == null) {
maxId = 0L;
id = maxId;
maxId++;
String sql = "insert into m_id(pk_name,pk_value) values(?,?)";
runner.update(conn, sql, new Object[]{key, maxId});
} else {
id = maxId;
maxId++;
String sql = "update m_id set pk_value=? where pk_name=?";
runner.update(conn, sql, new Object[]{maxId, key});
}
//更新数据表
return id;
}
public int saveModelMetrics(Long flowId, String metrics) {
int result = -1;
try {
/*Long projectId = getProjectId(flowId);
QueryRunner runner = new QueryRunner();
if (projectId == 0L) {
String sql4update = "update d_model_manage set metrics=? where project_id=? and flow_id=?";
result = runner.update(conn, sql4update, new Object[]{metrics, projectId, flowId});
System.out.println("metrics is updated with success");
return result;
}*/
Long projectId = getProjectId(flowId);
QueryRunner runner = new QueryRunner();
/*String sqlFlowVersion = "select id from d_flow_version where flow_id =? order by id desc limit 1";
Long flowVersionId = runner.query(conn, sqlFlowVersion, new ScalarHandler(), new Object[]{flowId});
*/
String sql = "select count(*) from d_model_manage where flow_id=? and flow_type=?";
Long count = runner.query(conn, sql, new ScalarHandler(),
new Object[]{flowId, FlowTypeConstants.VISUALIZATION});
if (count > 0) {
String sql4update = "update d_model_manage set model_metrics=? where flow_id=? and flow_type=?";
result = runner.update(conn, sql4update, new Object[]{metrics, flowId, FlowTypeConstants.VISUALIZATION});
System.out.println("metrics is updated with success");
} else {
Long maxId = getId(MIDKey.M_MODEL_MANAGER);
Long uId = -1L;
Object userId = Cache.getCache("userId");
if (userId != null) {
try {
uId = Long.parseLong(userId.toString());
} catch (Exception e) {
e.printStackTrace();
}
}
//accesscode
//accesstypecode
//modeltype INTERNAL
//subject_code subject_name subject_id
//project_name project_id
//createtime updatetime
//status NOT_RELEASE
//create_by
//model_code
//on_offline OFF_LINE
Project project = getProjectById(projectId);
Long subject_id=-1L;
String projectName="";
String subjectName="";
String subjectCode="";
String assetCode="";
String assetTypeCode="";
if (project!=null){
projectName=project.getName();
if (project.getSubject_id()!=null){
Subject subject = getSubjectById(project.getSubject_id());
subject_id=project.getSubject_id();
if (subject!=null){
subjectName=subject.getName();
subjectCode=subject.getCode();
}
}
if (project.getAsset_id()!=null){
Asset asset = getAssetById(project.getAsset_id());
if (asset!=null){
assetCode=asset.getAsset_code();
if (asset.getAsset_type_id()!=null){
assetTypeCode= getAssetTypeCodeById(asset.getAsset_type_id());
}
}
}
}
// String sql4insert = "insert into d_model_manage (id, project_id, flow_id, model_metrics,flow_type) values(?,?,?,?,?)";
String sql4insert = "insert into d_model_manage (id, project_id, flow_id, flow_type" +
",project_name,subject_id,subject_name" +
",subject_code,asset_code,asset_type_code,create_time,status,on_offline,model_tpye,model_metrics,create_by) " +
"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
result = runner.update(conn, sql4insert, new Object[]{maxId, projectId,
flowId,
FlowTypeConstants.VISUALIZATION,projectName,subject_id
,subjectName,subjectCode,assetCode,assetTypeCode,new Date(),"NOT_RELEASE","OFF_LINE","INTERNAL",metrics,userId});
// result = runner.update(conn, sql4insert, new Object[]{maxId, projectId, flowId, metrics, FlowTypeConstants.VISUALIZATION});
System.out.println("metrics is saved with success");
}
/*if(projectModelList.size() > 0) {
String sql4update = "update d_model_manage set metrics=? where project_id=? and flow_id=?";
result = runner.update(conn, sql4update, new Object[]{metrics, projectId, flowId});
System.out.println("metrics is updated with success");
} else {
String sql4id = "select max(id) from d_model_manage";
Long maxId = runner.query(conn, sql4id, new ScalarHandler());
String sql4insert = "insert into d_model_manage (id, project_id, flow_id, metrics) values(?,?,?,?)";
result = runner.update(conn, sql4insert, new Object[]{ maxId == null ? 0 : maxId + 1, projectId, flowId, metrics});
System.out.println("metrics is saved with success");
}*/
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
/**
* 保存debugString组件的运行结果到数据表
*
* @param flowId
* @param debugJson
* @return
*/
public int saveModelDebugJson(Long flowId, String debugJson) {
int result = -1;
try {
Long projectId = getProjectId(flowId);
QueryRunner runner = new QueryRunner();
String sql = "select count(*) from d_model_manage where flow_id=? and flow_type=?";
Long count = runner.query(conn, sql, new ScalarHandler(),
new Object[]{flowId, FlowTypeConstants.VISUALIZATION});
if (count > 0) {
String sql4update = "update d_model_manage set debug_json=? where flow_id=? and flow_type=?";
result = runner.update(conn, sql4update, new Object[]{debugJson, flowId, FlowTypeConstants.VISUALIZATION});
System.out.println("debugJson is updated with success");
} else {
Long maxId = getId(MIDKey.M_MODEL_MANAGER);
String sql4insert = "insert into d_model_manage (id, project_id, flow_id, debug_json) values(?,?,?,?)";
result = runner.update(conn, sql4insert, new Object[]{maxId, projectId, flowId, debugJson});
System.out.println("debugJson is saved with success");
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
/**
* 根据流程id查找项目id 如果查不到 则认为流程为示例流程
*
* @param flowId
* @return
*/
public Long getProjectId(Long flowId) {
Long result = -1L;
String sql = "select project_id from d_dag_flow where id=?";
QueryRunner runner = new QueryRunner();
try {
result = runner.query(conn, sql, new ScalarHandler(), new Object[]{flowId});
/*if (result == null)
result = 0L;*/
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public int saveFlowResult(Long flowId, String resultPath) {
int result = -1;
try {
QueryRunner runner = new QueryRunner();
String sql4id = "select max(id) from d_flow_result";
Long maxId = runner.query(conn, sql4id, new ScalarHandler());
String sql4insert = "insert into d_flow_result (id, flow_id, result_path) values(?,?,?)";
result = runner.update(conn, sql4insert, new Object[]{maxId == null ? 0 : maxId + 1, flowId, resultPath});
System.out.println("flow result is saved with success");
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public int updateFlowStatus(Long flowId, Long batchId, int status) {
int result = -1;
try {
String sql = "update d_flow_log set status=?, complete_time=? where flow_id=? and batch_id=?";
QueryRunner runner = new QueryRunner();
result = runner.update(conn, sql, new Object[]{status, new Timestamp(System.currentTimeMillis()), flowId, batchId});
System.out.println("flow_log is updated with success");
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public Map getSystemSettings(Long userId) {
Map result = new HashMap<>();
/* private Long settingId;
private String code;
private String value;*/
try {
String sql = "select code ,value from m_ccb_setting_item ";
QueryRunner runner = new QueryRunner();
List settings = runner.query(conn, sql, new BeanListHandler<>(SettingItem.class));
if (settings != null) {
settings.stream().forEach(s -> result.put(s.getCode(), s.getValue()));
}
/*if (userId != 0L) {
sql = "select a.* from m_setting_item a, m_setting b where a.setting_id = b.id and b.active = true and b.user_id = " + userId;
settings = runner.query(conn, sql, new BeanListHandler<>(SettingItem.class));
if (settings != null) {
settings.stream().forEach(s -> {
if (s.getValue() != null && !Strings.isNullOrEmpty(s.getValue())) {
result.put(s.getCode(), s.getValue());
}
});
}
}*/
System.out.println("fetch setting_item with success");
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public Long getFlowVersionId(Long flowId) {
Long flowVersionId = 0l;
try {
String sql = "select flow_version_id from d_flow where id =?";
QueryRunner runner = new QueryRunner();
flowVersionId = runner.query(conn, sql, new ScalarHandler(), new Object[]{flowId});
} catch (SQLException e) {
e.printStackTrace();
}
return flowVersionId;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy