package com.zs.job.migration.info.service.jobhandler;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.handler.annotation.XxlJob;
import com.xxl.job.core.log.XxlJobLogger;
import com.zs.job.migration.info.service.DataMapBiz;
import com.zs.job.migration.info.service.PathMapBiz;
import com.zs.job.migration.info.util.DataSourceUtil;
import com.zs.service.flag.api.service.InfoService;
import com.zs.service.info.api.service.column.ColumnService;
import com.zs.service.info.api.service.info.InfoDetailService;
import com.zs.service.info.api.service.subChannel.SubChannelService;
import com.zs.service.info.api.service.text.InfoDetailTextService;
import com.zs.service.mgr.info.api.service.InfoMgrService;
import com.zs.utils.service.snowflake.ISnowflakeWorker;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.dubbo.config.annotation.DubboReference;
import org.modelmapper.ModelMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Semaphore;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
@Slf4j
@Component
public class ShopDataImportXxlJob {
private static Logger logger = LoggerFactory.getLogger(ShopDataImportXxlJob.class);
private final String imageUrl = "https://image.21cp.com/";
@Value("${datasource.url}")
private String oldUrl;
@Value("${datasource.username}")
private String oldUsername;
@Value("${datasource.password}")
private String oldPassword;
@Value("${new.datasource.url}")
private String newUrl;
@Value("${new.datasource.username}")
private String newUsername;
@Value("${new.datasource.password}")
private String newPassword;
@Resource
private ISnowflakeWorker snowflakeIdWorker;
@Resource
private ModelMapper modelMapper;
@Resource
private DataMapBiz dataMapBiz;
@Resource
private PathMapBiz pathMapBiz;
/**
* 方法功能说明 企业相册数据导入
*
* @Params
* @Return
* @author 张聪
*/
@XxlJob("shopUserPhotosJobHandler")
public ReturnT<String> shopUserPhotosJobHandler(String param) throws Exception {
int state = 0;
if (!StringUtils.isBlank(param)) {
state = Integer.parseInt(param);
}
XxlJobLogger.log("XXL-JOB, shopUserPhotosJobHandler start-----");
DataSource dataSource = DataSourceUtil.getDataSource("zs_main", oldUrl, oldUsername, oldPassword);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
DataSource dataSourceBanner = DataSourceUtil.getDataSource("zs_shop", newUrl, newUsername, newPassword);
JdbcTemplate jdbcTemplateBanner = new JdbcTemplate(dataSourceBanner);
AtomicInteger count = new AtomicInteger(0);
if (state == 1) {
long onceCount = 100L;
int threadTotal = 50;
int acount = jdbcTemplate.queryForObject("select count(1) from (SELECT g.ENTERPRICE_UUID,g.`NAME`,GROUP_CONCAT(g.DOWNLOAD_URL) FROM(SELECT p.`NAME`,p.ENTERPRICE_UUID ,a.DOWNLOAD_URL FROM zs_fl_photos p LEFT JOIN zs_fl_attachment a ON p.UUID = a.RELEVANCE_UUID AND p.PHOTO_TYPE = a.RELEVANCE_TYPE WHERE a.ATTACHMENT_TYPE = '0' AND p.ENTERPRICE_UUID is NOT NULL ) g GROUP BY g.`NAME` )h", Integer.class);
XxlJobLogger.log("shopUserPhotosJobHandler++++++++++++zonggong+++++++++"+acount);
long startCount = acount / onceCount;
final Semaphore semaphore = new Semaphore(threadTotal);
ExecutorService exec = Executors.newCachedThreadPool();
for (int i = 0; i <= startCount; i++) {
long endCount = i + 1;
long startIndex = i * onceCount;
exec.execute(() -> {
//进行一次批量操作
try {
semaphore.acquire();
String selectSql = " SELECT g.ENTERPRICE_UUID,g.`NAME` TITLE ,GROUP_CONCAT(g.DOWNLOAD_URL) imageUrl FROM(SELECT p.`NAME`,p.ENTERPRICE_UUID ,a.DOWNLOAD_URL FROM zs_fl_photos p LEFT JOIN zs_fl_attachment a ON p.UUID = a.RELEVANCE_UUID AND p.PHOTO_TYPE = a.RELEVANCE_TYPE " +
" WHERE a.ATTACHMENT_TYPE = '0' AND p.ENTERPRICE_UUID is NOT NULL) g GROUP BY g.`NAME` " +
" LIMIT "+startIndex +","+onceCount;
List<Map<String, Object>> list = jdbcTemplate.queryForList(selectSql);
Vector<Map<String, Object>> vector = new Vector<Map<String, Object>>();
vector.addAll(list);
List<Object[]> args = new ArrayList<Object[]>();
for (Map<String, Object> map : vector) {
long sid = snowflakeIdWorker.nextId();
String userId = map.get("ENTERPRICE_UUID").toString();
Long userSid = dataMapBiz.uuidMapSid(userId);
String photoName = map.get("TITLE").toString();
String imageUrl = map.get("imageUrl").toString();
String[] split = imageUrl.split(",");
List<String> imageList = Arrays.stream(split).collect(Collectors.toList());
// List<String> imageList = JSONArray.parseArray(imageUrl, String.class);
List<String> collect = imageList.stream().map(f -> {
try {
String imageUrlNew = pathMapBiz.oldPathMapNewPath("uc", "shop", f.toString(), "", "");
return imageUrlNew;
} catch (Exception e) {
e.printStackTrace();
}
return f;
}).collect(Collectors.toList());
int imageType = 0;
args.add(new Object[]{sid,userSid,photoName, JSON.toJSONString(collect)});
}
jdbcTemplateBanner.batchUpdate("insert into shop_user_photo (sid,user_sid,photo_name,photo_img_url)" +
" values (?,?,?,?)",args);
int size = args.size();
count.addAndGet(size);
semaphore.release();
} catch (Exception e) {
log.error("exception", e);
}
});
}
exec.shutdown();
try {
while (true) {
if (exec.isTerminated()) {
XxlJobLogger.log("-------------shopUserPhotosJobHandler-------complete-------------");
XxlJobLogger.log(String.valueOf(count.get()));
break;
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
return ReturnT.SUCCESS;
}
/**
* 方法功能说明 banner图 导入
*
* @Params
* @Return
* @author 张聪
*/
@XxlJob("shopBannerJobHandler")
public ReturnT<String> shopBannerJobHandler(String param) throws Exception {
int state = 0;
if (!StringUtils.isBlank(param)) {
state = Integer.parseInt(param);
}
XxlJobLogger.log("XXL-JOB, 栏目 shopBannerJobHandler start-----");
DataSource dataSource = DataSourceUtil.getDataSource("zs_main", oldUrl, oldUsername, oldPassword);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
DataSource dataSourceBanner = DataSourceUtil.getDataSource("zs_shop", newUrl, newUsername, newPassword);
JdbcTemplate jdbcTemplateBanner = new JdbcTemplate(dataSourceBanner);
AtomicInteger count = new AtomicInteger(0);
if (state == 1) {
long onceCount = 1000L;
int threadTotal = 100;
int acount = jdbcTemplate.queryForObject("SELECT count(1) FROM (SELECT r.USER_ID,b.TITLE,b.IMAGE FROM zs_shop_banner b LEFT JOIN zs_uup_user_to_role r ON r.CORP_ID = b.CORP_ID WHERE b.IS_DELETE = '0' AND r.ROLE_ID = '001' )g", Integer.class);
XxlJobLogger.log("shopIntroductioJobHandler++zonggong+++++++++++++++++++"+acount);
long startCount = acount / onceCount;
final Semaphore semaphore = new Semaphore(threadTotal);
ExecutorService exec = Executors.newCachedThreadPool();
for (int i = 0; i <= startCount; i++) {
long endCount = i + 1;
long startIndex = i * onceCount;
exec.execute(() -> {
//进行一次批量操作
try {
semaphore.acquire();
String selectSql = " SELECT b.CORP_ID,b.TITLE,b.IMAGE FROM zs_shop_banner b LEFT JOIN zs_uup_user_to_role r ON r.CORP_ID = b.CORP_ID WHERE b.IS_DELETE = '0' AND " +
" r.ROLE_ID = '001' LIMIT "+startIndex +","+onceCount;
List<Map<String, Object>> list = jdbcTemplate.queryForList(selectSql);
Vector<Map<String, Object>> vector = new Vector<Map<String, Object>>();
vector.addAll(list);
List<Object[]> args = new ArrayList<Object[]>();
for (Map<String, Object> map : vector) {
long sid = snowflakeIdWorker.nextId();
String userId = map.get("CORP_ID").toString();
Long userSid = dataMapBiz.uuidMapSid(userId);
String advertisingName = map.get("TITLE").toString();
String imageUrl = map.get("IMAGE").toString();
String imageUrlNew = pathMapBiz.oldPathMapNewPath("uc", "shop", imageUrl, "", "");
int imageType = 0;
args.add(new Object[]{sid,userSid,"zs",imageType,advertisingName,imageUrlNew});
}
jdbcTemplateBanner.batchUpdate("insert into shop_user_advertising (sid,user_sid,user_group_type,image_type,advertising_name,advertising_img_url)" +
" values (?,?,?,?,?,?)",args);
int size = args.size();
count.addAndGet(size);
semaphore.release();
} catch (Exception e) {
log.error("exception", e);
}
});
}
exec.shutdown();
try {
while (true) {
if (exec.isTerminated()) {
XxlJobLogger.log("-------------shopBannerJobHandler----complete----------------");
XxlJobLogger.log(String.valueOf(count.get()));
break;
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
return ReturnT.SUCCESS;
}
/**
* 方法功能说明 商铺企业简介导入
*
* @Params
* @Return
* @author 张聪
*/
@XxlJob("shopIntroductioJobHandler")
public ReturnT<String> shopIntroductioJobHandler(String param) throws Exception {
int state = 0;
if (!StringUtils.isBlank(param)) {
state = Integer.parseInt(param);
}
XxlJobLogger.log("XXL-JOB, 栏目企业简介 shopIntroductioJobHandler start-----");
DataSource dataSource = DataSourceUtil.getDataSource("zs_main", oldUrl, oldUsername, oldPassword);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
DataSource dataSourceBanner = DataSourceUtil.getDataSource("zs_shop", newUrl, newUsername, newPassword);
JdbcTemplate jdbcTemplateBanner = new JdbcTemplate(dataSourceBanner);
AtomicInteger count = new AtomicInteger(0);
if (state == 1) {
//阈值调大数据库连接容易lost
long onceCount = 100L;
int threadTotal = 50;
int acount = jdbcTemplate.queryForObject("SELECT count(1) FROM zs_shop_config b LEFT JOIN zs_uup_user_to_role r ON r.CORP_ID = b.CORP_ID WHERE b.IS_DELETE = '0' AND r.ROLE_ID = '001' AND ISNULL( b.ENTERPRICE_PROFILE ) = 0 AND LENGTH( trim( b.ENTERPRICE_PROFILE ) ) > 0", Integer.class);
// int acount = 10;
XxlJobLogger.log("shopIntroductioJobHandler++++++++zonggong+++++++++++++"+acount);
long startCount = acount / onceCount;
final Semaphore semaphore = new Semaphore(threadTotal);
ExecutorService exec = Executors.newCachedThreadPool();
for (int i = 0; i <= startCount; i++) {
long endCount = i + 1;
long startIndex = i * onceCount;
exec.execute(() -> {
//进行一次批量操作
try {
semaphore.acquire();
XxlJobLogger.log(startIndex+"+++++++++++++++++");
//ORDER BY r.USER_ID 不能指定orderBy 否则会很慢
String selectSql = "SELECT b.CORP_ID,b.ENTERPRICE_PROFILE FROM zs_shop_config b LEFT JOIN zs_uup_user_to_role r ON r.CORP_ID = b.CORP_ID WHERE b.IS_DELETE = '0' AND r.ROLE_ID = '001' AND ISNULL( b.ENTERPRICE_PROFILE ) = 0 AND LENGTH( trim( b.ENTERPRICE_PROFILE ) ) > 0 " +
" LIMIT "+startIndex +","+onceCount;
List<Map<String, Object>> list = jdbcTemplate.queryForList(selectSql);
if(list!=null&&list.size()>0){
Vector<Map<String, Object>> vector = new Vector<Map<String, Object>>();
vector.addAll(list);
List<Object[]> args = new ArrayList<Object[]>();
List<Object[]> argsText = new ArrayList<Object[]>();
for (Map<String, Object> map : vector) {
long sid = snowflakeIdWorker.nextId();
String userId = map.get("CORP_ID").toString();
Long userSid = dataMapBiz.uuidMapSid(userId);
String content = map.get("ENTERPRICE_PROFILE").toString();
args.add(new Object[]{sid,userSid});
argsText.add(new Object[]{sid,content});
}
jdbcTemplateBanner.batchUpdate("insert into shop_user_introduction (sid,user_sid)" +
" values (?,?)",args);
jdbcTemplateBanner.batchUpdate("insert into shop_user_introduction_text (shop_user_introduction_sid,content)" +
" values (?,?)",argsText);
int size = args.size();
count.addAndGet(size);
}else{
XxlJobLogger.log("sql shibai"+selectSql);
}
XxlJobLogger.log(count.get()+"____________________________________________");
semaphore.release();
} catch (Exception e) {
log.error("exception", e);
}
});
}
exec.shutdown();
try {
while (true) {
if (exec.isTerminated()) {
XxlJobLogger.log("-------------shopIntroductioJobHandler插入完成个数为--------------------");
XxlJobLogger.log(String.valueOf(count.get()));
break;
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
return ReturnT.SUCCESS;
}
}
多线程导数据
最新推荐文章于 2024-10-16 10:13:45 发布