package com.yeshi.buwan.service.imp;
|
|
import com.yeshi.buwan.dao.CategoryVideoDao;
|
import com.yeshi.buwan.dao.VideoInfoDao;
|
import com.yeshi.buwan.domain.VideoDetailInfo;
|
import com.yeshi.buwan.dto.mq.VideoDataChangeMQMsg;
|
import com.yeshi.buwan.service.imp.juhe.IqiyiService;
|
import com.yeshi.buwan.util.StringUtil;
|
import com.yeshi.buwan.util.TimeUtil;
|
import com.yeshi.buwan.util.mq.rabbit.RabbitmqManager;
|
import net.sf.json.JSONObject;
|
import org.hibernate.HibernateException;
|
import org.hibernate.Session;
|
import org.springframework.orm.hibernate4.HibernateCallback;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import java.math.BigInteger;
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.List;
|
|
//清除数据库中的死数据
|
@Service
|
public class ClearService {
|
|
|
private final String[] videoTables = new String[]{"wk_category_video", "wk_resource_video", "wk_video_attention",
|
"wk_video_banquan_video", "wk_video_banquan_webvideo", "wk_video_collection", "wk_video_comment",
|
"wk_video_comment2", "wk_video_getscore_collect", "wk_video_homead", "wk_video_homevideo",
|
"wk_video_hotstar_video", "wk_video_iqiyi", "wk_video_letv", "wk_video_play_statistics",
|
"wk_video_pptv", "wk_video_sohu", "wk_video_special_video", "wk_video_youku", "wk_video_youku_dj"};
|
|
@Resource
|
private VideoInfoDao videoInfoDao;
|
|
@Resource
|
private CategoryVideoDao categoryVideoDao;
|
|
@Resource
|
private RabbitmqManager rabbitmqManager;
|
|
|
/**
|
* @return void
|
* @author hxh
|
* @description 删除老旧数据
|
* @date 16:49 2022/3/18
|
* @param: typeId
|
* @param: leftNumber 留下的数量
|
**/
|
public void clearOldVideo(long typeId, int leftNumber) {
|
Long minCreateTime = categoryVideoDao.getHibernateTemplate().execute(new HibernateCallback<Long>() {
|
@Override
|
public Long doInHibernate(Session session) throws HibernateException {
|
Object result = session.createSQLQuery("SELECT v.`createtime` FROM wk_category_video cv LEFT JOIN wk_video_video v ON v.`id`=cv.`videoid` WHERE cv.`videotypeid`=? ORDER BY cv.`id` DESC LIMIT ?,1").setParameter(0, typeId).setParameter(1, leftNumber).uniqueResult();
|
if (result == null) {
|
return null;
|
}
|
return Long.parseLong(result.toString());
|
}
|
});
|
if (minCreateTime == null) {
|
return;
|
}
|
clearVideos(new long[]{typeId}, TimeUtil.getGernalTime(minCreateTime, "yyyy-MM-dd"));
|
}
|
|
|
@SuppressWarnings("rawtypes")
|
public void clearVideos(long[] ls, String date) {
|
final List<JSONObject> list = new ArrayList<>();
|
|
for (long id : ls) {
|
JSONObject json = new JSONObject();
|
json.put("videotype", id);
|
json.put("time", TimeUtil.convertGernalTime(date, "yyyy-MM-dd"));
|
list.add(json);
|
}
|
|
videoInfoDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
for (JSONObject item : list) {
|
Object result = session
|
.createSQLQuery(String.format(
|
"SELECT count(*) FROM wk_category_video cv LEFT JOIN wk_video_video v ON v.`id`=cv.`videoid` WHERE cv.`videotypeid`=%s AND v.`createtime`<%s",
|
item.getLong("videotype"), item.getLong("time")))
|
.uniqueResult();
|
int totalCount = Integer.parseInt(result.toString());
|
if (totalCount <= 0) {
|
return null;
|
}
|
int BETCH_SIZE = 1000;
|
int page = totalCount / BETCH_SIZE + 1;
|
for (int n = 0; n < page; n++) {
|
List list = session
|
.createSQLQuery(String.format(
|
"SELECT cv.id,cv.videoid FROM wk_category_video cv LEFT JOIN wk_video_video v ON v.`id`=cv.`videoid` WHERE cv.`videotypeid`=%s AND v.`createtime`<%s",
|
item.getLong("videotype"), item.getLong("time")))
|
.setFirstResult(0).setMaxResults(BETCH_SIZE).list();
|
session.getTransaction().begin();
|
if (list != null) {
|
int pagecount = 50;
|
int pages = list.size() % pagecount == 0 ? list.size() / pagecount
|
: list.size() / pagecount + 1;
|
for (int i = 0; i < pages; i++) {
|
String ors = "";
|
String cvors = "";
|
|
List<String> videoIds = new ArrayList<>();
|
for (int p = 0; p < pagecount; p++) {
|
int pos = i * pagecount + p;
|
pos = pos >= list.size() ? list.size() - 1 : pos;
|
ors += String.format("id=%s or ", ((Object[]) list.get(pos))[1] + "");
|
cvors += String.format("id=%s or ", ((Object[]) list.get(pos))[0] + "");
|
videoIds.add(((Object[]) list.get(pos))[1] + "");
|
}
|
if (ors.endsWith("or "))
|
ors = ors.substring(0, ors.length() - 3);
|
|
if (cvors.endsWith("or "))
|
cvors = cvors.substring(0, cvors.length() - 3);
|
|
session.createSQLQuery("delete from wk_video_video where " + ors).executeUpdate();
|
session.createSQLQuery("delete from wk_category_video where " + cvors).executeUpdate();
|
if (videoIds.size() > 0) {
|
rabbitmqManager.addVideoDataChanged(new VideoDataChangeMQMsg(VideoDataChangeMQMsg.TYPE_VIDEO_INFO, org.yeshi.utils.StringUtil.concat(videoIds, ","), VideoDataChangeMQMsg.ACTION_DELETE));
|
}
|
|
}
|
}
|
session.flush();
|
session.getTransaction().commit();
|
}
|
}
|
return null;
|
}
|
});
|
|
}
|
|
// 清楚依赖VideoInfo的数据
|
@SuppressWarnings("rawtypes")
|
public void clearDependVideo() {
|
for (String table : videoTables) {
|
clearDependVideoByTable(table);
|
}
|
}
|
|
|
public void clearDependVideoByTable(String table) {
|
final String sql = String.format(
|
"SELECT vv.id FROM `%s` vv LEFT JOIN wk_video_video v ON v.`id`=vv.`videoid` WHERE v.`id` IS NULL",
|
table);
|
final String countSql = String.format(
|
"SELECT count(*) FROM `%s` vv LEFT JOIN wk_video_video v ON v.`id`=vv.`videoid` WHERE v.`id` IS NULL",
|
table);
|
|
Object obj = videoInfoDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
return session.createSQLQuery(countSql).uniqueResult();
|
}
|
});
|
int count = Integer.parseInt(obj.toString());
|
int pageSize = 1000;
|
int page = count / pageSize + 1;
|
|
for (int p = 0; p < page; p++) {
|
obj = videoInfoDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
return session.createSQLQuery(sql).setFirstResult(0).setMaxResults(pageSize).list();
|
}
|
});
|
|
final List list = (List) obj;
|
|
if (list != null && list.size() > 0) {
|
String idsStr = "";
|
for (int i = 0; i < list.size(); i++) {
|
idsStr += " or id=" + list.get(i);
|
}
|
if (idsStr.startsWith(" or"))
|
idsStr = idsStr.substring(3);
|
final String tempTable = table;
|
final String tempIds = idsStr;
|
try {
|
videoInfoDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
session.getTransaction().begin();
|
if (!StringUtil.isNullOrEmpty(tempIds.trim())) {
|
session.createSQLQuery(
|
String.format("delete from %s where %s", tempTable, tempIds + ""))
|
.executeUpdate();
|
}
|
session.flush();
|
session.getTransaction().commit();
|
return null;
|
}
|
});
|
} catch (Exception e) {
|
|
}
|
|
}
|
}
|
}
|
|
/**
|
* 清理SQL数据库依赖视频
|
*
|
* @param videoId
|
*/
|
public void clearDependVideo(final String videoId) {
|
clearDependVideo(Arrays.asList(new String[]{videoId}));
|
}
|
|
|
public void clearDependVideo(final List<String> videoIds) {
|
videoInfoDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
session.getTransaction().begin();
|
List<String> ors = new ArrayList<>();
|
for (String videoId : videoIds) {
|
ors.add("videoid=" + videoId);
|
}
|
|
for (String table : videoTables)
|
session.createSQLQuery(
|
String.format("delete from %s where %s", table, org.yeshi.utils.StringUtil.concat(ors, " or ")))
|
.executeUpdate();
|
session.flush();
|
session.getTransaction().commit();
|
return null;
|
}
|
});
|
}
|
|
/**
|
* 清除无效的爱奇艺视频
|
*/
|
@SuppressWarnings("unchecked")
|
public void clearInvalidIqiyiVideo() {
|
IqiyiService iqiyiService = com.yeshi.buwan.util.BeanUtil.getBean(IqiyiService.class);
|
|
// 查询出
|
// SELECT DISTINCT(i.videoid) FROM `wk_video_iqiyi` i LEFT JOIN
|
// wk_video_video v ON v.id=i.videoid WHERE v.show=1;
|
// 查询出所有的iqiyi视频
|
|
for (int i = 1; i < 100; i++) {
|
final int p = i;
|
List<BigInteger> videoList = (List<BigInteger>) videoInfoDao
|
.excute(new HibernateCallback<List<BigInteger>>() {
|
|
@Override
|
public List<BigInteger> doInHibernate(Session session) throws HibernateException {
|
return session
|
.createSQLQuery(
|
"SELECT DISTINCT(i.videoid) FROM `wk_video_iqiyi` i LEFT JOIN wk_video_video v ON v.id=i.videoid WHERE v.show=1")
|
.setFirstResult(p * 10000).setMaxResults(10000).list();
|
}
|
});
|
for (BigInteger videoId : videoList) {
|
List<VideoDetailInfo> list = iqiyiService.getVideoDetailList(videoId.toString(), 1, 2000);
|
if (list == null) {
|
iqiyiService.deleteIqiyiVideoByVideoId(videoId.toString());
|
}
|
}
|
}
|
}
|
}
|
