爱奇艺视频更新

admin

2021-07-30 19533a17aa55fafc70d0a385928e785cb50e1ebc

 src/main/java/com/yeshi/buwan/service/imp/ClearService.java

@@ -2,6 +2,7 @@

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.annotation.Resource;
@@ -23,139 +24,173 @@
//清除数据库中的死数据
@Service
public class ClearService {
   @Resource
   private VideoInfoDao videoInfoDao;

   @SuppressWarnings("rawtypes")
   public void clearVideos(long[] ls, String date) {
      final List<JSONObject> list = new ArrayList<JSONObject>();

      for (long id : ls) {
         JSONObject json = new JSONObject();
         json.put("videotype", id);
         json.put("time", TimeUtil.convertGernalTime(date, "yyyy-MM-dd"));
         list.add(json);
      }
    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"};

      videoInfoDao.excute(new HibernateCallback() {
         public Object doInHibernate(Session session) throws HibernateException {
            for (JSONObject item : list) {
               List list = session
                     .createSQLQuery(String.format(
                           "SELECT 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(1000).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 where = "";
                     for (int p = 0; p < pagecount; p++) {
                        int pos = i * pagecount + p;
                        pos = pos >= list.size() ? list.size() - 1 : pos;
                        where += String.format("id=%s or ", list.get(pos) + "");
                     }
                     if (where.endsWith("or "))
                        where = where.substring(0, where.length() - 3);
    @Resource
    private VideoInfoDao videoInfoDao;

                     session.createSQLQuery("delete from wk_video_video where " + where).executeUpdate();
                  }
               }
               session.flush();
               session.getTransaction().commit();
            }
            return null;
         }
      });
    @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);
        }

   // 清楚依赖VideoInfo的数据
   @SuppressWarnings("rawtypes")
   public void clearDependVideo() {
      String[] tables = 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" };
        videoInfoDao.excute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException {
                for (JSONObject item : list) {
                    List list = session
                            .createSQLQuery(String.format(
                                    "SELECT 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(1000).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 where = "";
                            for (int p = 0; p < pagecount; p++) {
                                int pos = i * pagecount + p;
                                pos = pos >= list.size() ? list.size() - 1 : pos;
                                where += String.format("id=%s or ", list.get(pos) + "");
                            }
                            if (where.endsWith("or "))
                                where = where.substring(0, where.length() - 3);

      for (String table : tables) {
         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);
         Object obj = videoInfoDao.excute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException {
               return session.createSQLQuery(sql).setFirstResult(0).setMaxResults(1000).list();
            }
         });
                            session.createSQLQuery("delete from wk_video_video where " + where).executeUpdate();
                        }
                    }
                    session.flush();
                    session.getTransaction().commit();
                }
                return null;
            }
        });

         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) {
    // 清楚依赖VideoInfo的数据
    @SuppressWarnings("rawtypes")
    public void clearDependVideo() {

            }

         }
        for (String table : videoTables) {
            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);
            Object obj = videoInfoDao.excute(new HibernateCallback() {
                public Object doInHibernate(Session session) throws HibernateException {
                    return session.createSQLQuery(sql).setFirstResult(0).setMaxResults(1000).list();
                }
            });

      }
   }
            final List list = (List) obj;

   /**
    * 清除无效的爱奇艺视频
    */
   @SuppressWarnings("unchecked")
   public void clearInvalidIqiyiVideo() {
      IqiyiService iqiyiService = com.yeshi.buwan.util.BeanUtil.getBean(IqiyiService.class);
            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) {

      // 查询出
      // 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());
            if (list == null) {
               iqiyiService.deleteIqiyiVideoByVideoId(videoId.toString());
            }
         }
      }
   }
        }
    }

    /**
     * 清理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());
                }
            }
        }
    }
}