@Component
public class QueryDao {
private static final Logger LOGGER = LoggerFactory.getLogger(StatisticDao.class);
public List<BaseNode> queryNodeByPage(BaseNode baseNode, LocalDateTime lastTime, long pageNum, int pageSize) {
long skip = pageNum * pageSize;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("MATCH (n:").append(baseNode.typeGetter());
if (StringUtils.hasText(baseNode.uniqueValueGetter()))
stringBuilder.append("{").append(baseNode.uniqueFieldNameGetter()).append(": '").append(baseNode.uniqueValueGetter()).append("'}");
stringBuilder.append(") ");
if (null != lastTime)
stringBuilder.append("WHERE n.updateTime is null OR n.updateTime <= '").append(lastTime.format(Constant.NORMAL_TIME_FORMATTER)).append("' ");
stringBuilder.append("RETURN n SKIP ").append(skip).append(" LIMIT ").append(pageSize);
return Lists.newArrayList(CyberQueryExecuteUtil.executeEntityQuery(stringBuilder.toString(), BaseNode.class));
}
public List<BaseNode> queryNodeByUniqueValue(BaseNode entity) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("MATCH (n:").append(entity.typeGetter()).append("{").append(entity.uniqueFieldNameGetter()).append(": '");
if (entity instanceof CarNode) {
String[] carArray = entity.uniqueValueGetter().split(Constant.carNoTypeSplit);
stringBuilder.append(carArray[0]).append("'");
if (1 < carArray.length) {
stringBuilder.append(",vehicleCodeType:'");
if (2 > carArray[1].length()) stringBuilder.append("0");
stringBuilder.append(carArray[1]).append("'");
}
} else stringBuilder.append(entity.uniqueValueGetter()).append("'");
stringBuilder.append("}) RETURN n");
return Lists.newArrayList(CyberQueryExecuteUtil.executeEntityQuery(stringBuilder.toString(), BaseNode.class));
}
public boolean isNodeExistAndFreshLastWeek(BaseNode entity) {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("MATCH (n:").append(entity.typeGetter());
stringBuilder.append("{").append(entity.uniqueFieldNameGetter()).append(": '").append(entity.uniqueValueGetter()).append("'");
if (entity instanceof CarNode) {
CarNode carNode = (CarNode) entity;
if (StringUtils.hasText(carNode.getVehicleCodeType()))
stringBuilder.append(",vehicleCodeType:'").append(carNode.getVehicleCodeType()).append("'");
}
stringBuilder.append("}) WHERE n.updateTime >= '")
.append(LocalDateTime.now().minusDays(7L).format(Constant.NORMAL_TIME_FORMATTER)).append("' RETURN count(n)");
Long count = CyberQueryExecuteUtil.executeCountQuery(stringBuilder.toString());
return 1L <= count;
}
public List<BaseRelation> queryEndNodeByStartNodeAndRelationType(BaseNode entity, BaseRelation relationship, BaseNode baseNode) {
StringBuilder stringBuilder = createBaseCQL(entity, relationship, baseNode);
stringBuilder.append("RETURN n,m,r");
return Lists.newArrayList(CyberQueryExecuteUtil.executeEntityQuery(stringBuilder.toString(), relationship.getClass()));
}
public List<BaseRelation> queryEndNodeByStartNodeAndRelationType(BaseNode entity, BaseRelation relationship, BaseNode baseNode, LocalDateTime startTime, LocalDateTime endTime) {
StringBuilder stringBuilder = createBaseCQL(entity, relationship, baseNode);
stringBuilder.append("WHERE 1=1 ");
if (null != startTime)
stringBuilder.append("AND r.startTime > '").append(startTime.format(Constant.NORMAL_TIME_FORMATTER)).append("' ");
if (null != endTime)
stringBuilder.append("AND r.endTime < '").append(endTime.format(Constant.NORMAL_TIME_FORMATTER)).append("' ");
stringBuilder.append("RETURN n,m,r");
return Lists.newArrayList(CyberQueryExecuteUtil.executeEntityQuery(stringBuilder.toString(), relationship.getClass()));
}
public List<BaseRelation> queryRelationByStartNode(BaseNode entity, BaseRelation relationship, BaseNode baseNode) {
StringBuilder stringBuilder = createBaseCQL(entity, relationship, baseNode);
stringBuilder.append("WHERE r.joinCount IS NOT NULL ");
if (null != relationship.getStartTime())
stringBuilder.append("AND r.startTime > '").append(relationship.getStartTime().format(Constant.NORMAL_TIME_FORMATTER)).append("' ");
if (null != relationship.getEndTime())
stringBuilder.append("AND r.endTime < '").append(relationship.getEndTime().format(Constant.NORMAL_TIME_FORMATTER)).append("' ");
stringBuilder.append("RETURN n,m,r ");
stringBuilder.append("ORDER BY r.joinCount DESC, r.confidence DESC LIMIT 10");
return Lists.newArrayList(CyberQueryExecuteUtil.executeEntityQuery(stringBuilder.toString(), relationship.getClass()));
}
public List<Map<String, Object>> statisticFollowHistory(BaseNode entity, BaseNode baseNode, boolean last3Day) {
StringBuilder stringBuilder = createBaseCQL(entity, FollowRelation.builder().build(), baseNode);
if (last3Day)
stringBuilder.append("WHERE r.endTime >= '")
.append(LocalDate.now().minusDays(3L).atStartOfDay().format(Constant.NORMAL_TIME_FORMATTER))
.append("' ");
stringBuilder.append("WITH n,m,");
stringBuilder.append("SUM(r.joinCount)");
//stringBuilder.append(last3Day ? "SUM(r.joinCount)" : "COUNT(r)");
stringBuilder.append(" AS r_count RETURN n,m,r_count ORDER BY r_count DESC LIMIT 5");
return CyberQueryExecuteUtil.executeOriginResultQuery(stringBuilder.toString());
}
public List<FollowRelation> queryLatestByStartNode(BaseNode entity, BaseNode baseNode) {
StringBuilder stringBuilder = createBaseCQL(entity, FollowRelation.builder().build(), baseNode);
stringBuilder.append("RETURN n,m,r ORDER BY r.endTime DESC LIMIT 5");
return Lists.newArrayList(CyberQueryExecuteUtil.executeEntityQuery(stringBuilder.toString(), FollowRelation.class));
}
private StringBuilder createBaseCQL(BaseNode entity, BaseRelation relationship, BaseNode baseNode) {
StringBuilder stringBuilder = new StringBuilder("MATCH ");
stringBuilder.append("(n:").append(entity.typeGetter()).append("{").append(entity.uniqueFieldNameGetter()).append(": '").append(entity.uniqueValueGetter()).append("'");
if (entity instanceof CarNode) {
CarNode carNode = (CarNode) entity;
if (StringUtils.hasText(carNode.getVehicleCodeType()))
stringBuilder.append(",vehicleCodeType:'").append(carNode.getVehicleCodeType()).append("'");
}
stringBuilder.append("})");
stringBuilder.append("-");
stringBuilder.append("[r:").append(relationship.getType()).append("]");
stringBuilder.append("-");
stringBuilder.append("(m:").append(baseNode.typeGetter()).append(") ");
return stringBuilder;
}
}
/**
* @author Ricardo.H.Wu
* @time 2020/8/3 11:04
*/
@Data
public abstract class BaseNode {
@Id
@GeneratedValue
private Long id;
@Convert(Neo4jLocalDateTimeConvert.class)
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime updateTime = LocalDateTime.now();
public abstract String typeGetter();
public abstract String uniqueFieldNameGetter();
public abstract String uniqueValueGetter();
public abstract void uniqueValueSetter(String uniqueValue);
public DtoNeo4jNode convertToResponseEntity() {
return DtoNeo4jNode.builder()
.label(typeGetter())
.id(uniqueValueGetter())
.data(this)
.build();
}
}
@Data
public abstract class BaseRelation {
@Id
@GeneratedValue
private Long id;
@StartNode
private BaseNode startNode;
@EndNode
private BaseNode endNode;
@Convert(Neo4jLocalDateTimeConvert.class)
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime startTime;
@Convert(Neo4jLocalDateTimeConvert.class)
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime endTime;
@Convert(Neo4jLocalDateTimeConvert.class)
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime updateTime = LocalDateTime.now();
public abstract String getType();
public abstract DtoNeo4jEdge convertToResponseEntity();
}