Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package data.services; import data.dao.CarCompletionGroupDao; import data.dao.CarCompletionOptionDao; import data.dao.CarDao; import data.dao.CarOptionValueDao; import data.dao.CarPropertyDao; import data.dao.CarPropertyGroupDao; import data.dao.ColorDao; import data.dao.FeatureDao; import data.dao.MarkDao; import data.dao.ModelDao; import data.dao.PropertyNameDao; import data.dao.SubModelDao; import data.entity.Car; import data.entity.CarCompletionGroup; import data.entity.CarCompletionOption; import data.entity.CarOptionValue; import data.entity.CarProperty; import data.entity.CarPropertyGroup; import data.entity.Color; import data.entity.Feature; import data.entity.Mark; import data.entity.Model; import data.entity.PropertyName; import data.entity.SubModel; import data.services.parent.PrimService; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Objects; import java.util.Properties; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.context.annotation.ScopedProxyMode; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import support.StringAdapter; /** * * @author bezdatiuzer */ @Service @Transactional @Scope(value = "request", proxyMode = ScopedProxyMode.TARGET_CLASS) public class ParseBaseService extends PrimService { @Autowired private ModelDao modelDao; @Autowired private MarkDao markDao; @Autowired private CarDao carDao; @Autowired private SubModelDao subModelDao; @Autowired private FeatureDao featureDao; @Autowired private PropertyNameDao propertyNameDao; @Autowired private CarPropertyDao carPropertyDao; @Autowired private CarPropertyGroupDao carPropertyGroupDao; @Autowired private CarOptionValueDao carOptionValueDao; @Autowired private CarCompletionOptionDao CCODao; @Autowired private CarCompletionGroupDao CCGDao; @Autowired private ColorDao colorDao; @Autowired private CarService carService; @Autowired private CarCompletionGroupService CCGService; private Connection conn; private final String QUTO_MARK_TABLE = "car_brand"; private final String QUTO_MODEL_TABLE = "car_model"; private final String QUTO_CAR_MODIFICATION_TABLE = "car_modification"; private final String QUTO_CAR_MODEL_SUB_TABLE = "car_model_sub"; private final String QUTO_FEATURE_TABLE = "car_feature"; private final String QUTO_PROPERTY_TABLE = "car_property"; private final String QUTO_CCG_TABLE = "car_completion_group"; private final String QUTO_CCO_TABLE = "car_completion_option"; private final String QUTO_COLOR_TABLE = "car_color"; private final String QUTO_CAR_TO_FEATURE_LINK_TABLE = "car_modification_feature_link"; /* private String featureTable = "feature"; private String featureTable = "feature"; private String featureTable = "feature"; private String featureTable = "feature"; private String featureTable = "feature";*/ public void updateDataFromQuto() throws SQLException, ClassNotFoundException, Exception { updateMarks(); updateModels(); updateCarModelSubs(); updateFeatures(); updateProperties(); updateColors(); updateOptionGroups(); updateOptions(); updateCars(); updateCarFeatureLinks(); updatePropertyNames(); updateCarOptionValues(); conn.close(); } private void updateMarks() throws SQLException, ClassNotFoundException { List<Mark> marksForSaveList = new ArrayList(); List<Mark> marksForUpdateList = new ArrayList(); List<Mark> markList = markDao.getAllAsc(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, Mark> ourOldIdMarkMap = new HashMap(); for (Mark m : markList) { ourOldIdMarkMap.put(m.getQutoId(), m); } ResultSet resSet = getFromQutoBase(getSelectAll(QUTO_MARK_TABLE)); while (resSet.next()) { Long qutoId = resSet.getLong("id"); actualQutoIdList.add(qutoId); String pop = StringAdapter.getString(resSet.getString("is_popular")).trim(); String title = StringAdapter.getString(resSet.getString("title")).trim(); String titleRus = StringAdapter.getString(resSet.getString("title_rus")).trim(); String url = StringAdapter.getString(resSet.getString("url")).trim(); String desc = StringAdapter.getString(resSet.getString("description")).trim(); Long mediaId = resSet.getLong("media_id"); if (!ourOldIdMarkMap.keySet().contains(qutoId)) { Mark mark = new Mark(); mark.setPop(pop); mark.setTitle(title); mark.setTitleRus(titleRus); mark.setDescription(desc); mark.setMediaId(mediaId); mark.setUrl(url); if (validate(mark, "TroubleQutoId=" + mark.getQutoId() + "; ")) { marksForSaveList.add(mark); } } else { Mark mark = ourOldIdMarkMap.get(qutoId); if (!pop.equals(mark.getPop()) || !title.equals(mark.getTitle()) || !titleRus.equals(mark.getTitleRus()) || !url.equals(mark.getUrl()) || !mediaId.equals(mark.getMediaId()) || !desc.equals(mark.getDescription())) { mark.setQutoId(qutoId); mark.setPop(pop); mark.setTitle(title); mark.setTitleRus(titleRus); mark.setDescription(desc); mark.setMediaId(mediaId); mark.setUrl(url); if (validate(mark, "TroubleQutoId=" + mark.getQutoId() + "; ")) { marksForUpdateList.add(mark); } } } } int s = 0; int u = 0; int d = 0; for (Mark m : marksForSaveList) { markDao.save(m); s++; } for (Mark m : marksForUpdateList) { markDao.update(m); u++; } for (Long qutoId : ourOldIdMarkMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; markDao.delete(ourOldIdMarkMap.get(qutoId)); } } addError(": " + s + " ? ?, " + u + " , " + d + " ."); } //to do - ? ? quto ?, private void updateModels() throws SQLException, ClassNotFoundException { List<Model> modelsForSaveList = new ArrayList(); List<Model> modelsForUpdateList = new ArrayList(); List<Model> modelList = modelDao.getAllAsc(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, Model> ourOldIdModelMap = new HashMap(); for (Model m : modelList) { ourOldIdModelMap.put(m.getQutoId(), m); } ResultSet resSet = getFromQutoBase(getSelectAll(QUTO_MODEL_TABLE)); while (resSet.next()) { Long qutoId = resSet.getLong("id"); actualQutoIdList.add(qutoId); Long markQutoId = resSet.getLong("car_brand_id"); String pop = StringAdapter.getString(resSet.getString("is_popular")).trim(); String title = StringAdapter.getString(resSet.getString("title")).trim(); String titleRus = StringAdapter.getString(resSet.getString("title_rus")).trim(); String url = StringAdapter.getString(resSet.getString("url")).trim(); String desc = StringAdapter.getString(resSet.getString("description")).trim(); Long mediaId = resSet.getLong("media_id"); Mark mark = new Mark(); mark.setQutoId(markQutoId); List<Mark> supMarkList = markDao.find(mark); if (!supMarkList.isEmpty() && qutoId != 0) { mark = supMarkList.get(0); if (!ourOldIdModelMap.keySet().contains(qutoId)) { Model model = new Model(); model.setQutoId(qutoId); model.setMark(mark); model.setPop(pop); model.setTitle(title); model.setTitleRus(titleRus); model.setDescription(desc); model.setMediaId(mediaId); model.setUrl(url); if (validate(model, "TroubleQutoId=" + model.getQutoId() + "; ")) { modelsForSaveList.add(model); } } else { Model model = ourOldIdModelMap.get(qutoId); if (!pop.equals(model.getPop()) || !title.equals(model.getTitle()) || !titleRus.equals(model.getTitleRus()) || !url.equals(model.getUrl()) || !mediaId.equals(model.getMediaId()) || !desc.equals(model.getDescription())) { model.setQutoId(qutoId); model.setMark(mark); model.setPop(pop); model.setTitle(title); model.setTitleRus(titleRus); model.setDescription(desc); model.setMediaId(mediaId); model.setUrl(url); if (validate(model, "TroubleQutoId=" + model.getQutoId() + "; ")) { modelsForUpdateList.add(model); } } } } } int s = 0; int u = 0; int d = 0; for (Model m : modelsForSaveList) { modelDao.save(m); s++; } for (Model m : modelsForUpdateList) { modelDao.update(m); u++; } for (Long qutoId : ourOldIdModelMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; modelDao.delete(ourOldIdModelMap.get(qutoId)); } } addError(": " + s + " ? ?, " + u + " , " + d + " ."); } private void updateCars() throws SQLException, ClassNotFoundException, Exception { List<Car> carsForSaveList = new ArrayList(); List<Car> carsForUpdateList = new ArrayList(); List<Car> carList = carDao.getAllAsc(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, Car> ourOldIdCarMap = new HashMap(); for (Car car : carList) { ourOldIdCarMap.put(car.getCmqId(), car); } ResultSet resSet = getFromQutoBase( "SELECT c.*,cmc.title,cmsg.car_model_sub_id cms_id,cmsg.car_model_generation_id cmg_id,cmc.title completion_title,cmg.car_model_id model_id FROM car_modification c LEFT JOIN car_modification_completion cmc ON c.car_modification_completion_id=cmc.id LEFT JOIN car_model_sub_generation cmsg ON c.car_model_sub_generation_id=cmsg.id LEFT JOIN car_model_generation cmg ON cmsg.car_model_generation_id=cmg.id WHERE c.usage='ad_archive_catalog'"); while (resSet.next()) { Long cmqId = resSet.getLong("id"); actualQutoIdList.add(cmqId); Long modelQutoId = resSet.getLong("model_id"); String completionTitle = StringAdapter.getString(resSet.getString("completion_title")).trim(); Long cmsId = resSet.getLong("cms_id"); Long cmgId = resSet.getLong("cmg_id"); String title = StringAdapter.getString(resSet.getString("title")).trim(); Long cmsgId = resSet.getLong("car_model_sub_generation_id"); Long mediaId = resSet.getLong("media_id"); String url = StringAdapter.getString(resSet.getString("url")).trim(); Double price = resSet.getDouble("dt_price_min"); Model model = new Model(); model.setQutoId(modelQutoId); List<Model> supModelList = modelDao.find(model); if (!supModelList.isEmpty() && modelQutoId != 0) { model = supModelList.get(0); if (!ourOldIdCarMap.keySet().contains(cmqId)) { Car car = new Car(); car.setCmqId(cmqId); car.setModel(model); ; car.setTitle(title); car.setMediaId(mediaId); car.setUrl(url); car.setCmPrice(price); car.setCmgqId(cmgId); car.setCmsgqId(cmsgId); car.setCmsqId(cmsId); car.setCompletionTitle(completionTitle); if (validate(car, " , TroubleQutoId=" + car.getCmqId() + "; ")) { carsForSaveList.add(car); } } else { Car car = ourOldIdCarMap.get(cmqId); if (!cmgId.equals(car.getCmgqId()) || !title.equals(car.getTitle()) || !price.equals(car.getCmPrice()) || !url.equals(car.getUrl()) || !mediaId.equals(car.getMediaId()) || !completionTitle.equals(car.getCompletionTitle()) || !cmsgId.equals(car.getCmsgqId()) || !cmsId.equals(car.getCmsqId())) { car.setCmqId(cmqId); car.setModel(model); ; car.setTitle(title); car.setMediaId(mediaId); car.setUrl(url); car.setCmPrice(price); car.setCmgqId(cmgId); car.setCmsgqId(cmsgId); car.setCmsqId(cmsId); car.setCompletionTitle(completionTitle); ; if (validate(car, " , TroubleQutoId=" + car.getCmqId() + "; ")) { carsForUpdateList.add(car); } } } } } int s = 0; int u = 0; int d = 0; for (Car car : carsForSaveList) { carService.create(car); s++; } for (Car car : carsForUpdateList) { carDao.update(car); u++; } for (Long qutoId : ourOldIdCarMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; carService.delete(ourOldIdCarMap.get(qutoId)); } } addError(" : " + s + " ? ?, " + u + " , " + d + " ."); } private void updateCarModelSubs() throws SQLException, ClassNotFoundException { List<SubModel> subModelsForSaveList = new ArrayList(); List<SubModel> subModelsForUpdateList = new ArrayList(); List<SubModel> subModelList = subModelDao.getAllAsc(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, SubModel> ourOldIdSubModelMap = new HashMap(); for (SubModel m : subModelList) { ourOldIdSubModelMap.put(m.getQutoModelSubId(), m); } ResultSet resSet = getFromQutoBase( "SELECT cms.*,cb.title body,cc.title car_class,ct.title type FROM car_model_sub cms LEFT JOIN car_body cb ON cms.car_body_id=cb.id LEFT JOIN car_class cc ON cms.car_class_id=cc.id LEFT JOIN car_type ct ON cms.car_type_id=ct.id where cms.dt_usage='ad_archive_catalog'"); while (resSet.next()) { Long qutoId = resSet.getLong("id"); actualQutoIdList.add(qutoId); Integer dc = resSet.getInt("doors_count"); String title = StringAdapter.getString(resSet.getString("title")).trim(); String url = StringAdapter.getString(resSet.getString("url")).trim(); String body = StringAdapter.getString(resSet.getString("body")).trim(); String carClass = StringAdapter.getString(resSet.getString("car_class")).trim(); String type = StringAdapter.getString(resSet.getString("type")).trim(); if (!ourOldIdSubModelMap.keySet().contains(qutoId)) { SubModel model = new SubModel(); model.setQutoModelSubId(qutoId); model.setBody(body); model.setTitle(title); model.setCarClass(carClass); model.setDoorsCount(dc); model.setType(type); model.setUrl(url); if (validate(model, "TroubleQutoId=" + model.getQutoModelSubId() + "; ")) { subModelsForSaveList.add(model); } } else { SubModel model = ourOldIdSubModelMap.get(qutoId); if (!Objects.equals(dc, model.getDoorsCount()) || !title.equals(model.getTitle()) || !body.equals(model.getBody()) || !url.equals(model .getUrl())/*||!type.equals(model.getType())||!carClass.equals(model.getCarClass())*/) { model.setQutoModelSubId(qutoId); model.setBody(body); model.setTitle(title); model.setDoorsCount(dc); /*model.setCarClass(carClass); model.setType(type);*/ model.setUrl(url); if (validate(model, "TroubleQutoId=" + model.getQutoModelSubId() + "; ")) { subModelsForUpdateList.add(model); } } } } int s = 0; int u = 0; int d = 0; for (SubModel m : subModelsForSaveList) { subModelDao.save(m); s++; } for (SubModel m : subModelsForUpdateList) { subModelDao.update(m); u++; } for (Long qutoId : ourOldIdSubModelMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; subModelDao.delete(ourOldIdSubModelMap.get(qutoId)); } } addError(": " + s + " ? ?, " + u + " , " + d + " ."); } private void updateFeatures() throws SQLException, ClassNotFoundException { List<Feature> featuresForSaveList = new ArrayList(); List<Feature> featuresForUpdateList = new ArrayList(); List<Feature> featureList = featureDao.getAllAsc(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, Feature> ourOldIdFeatureMap = new HashMap(); for (Feature m : featureList) { ourOldIdFeatureMap.put(m.getOldId(), m); } ResultSet resSet = getFromQutoBase(getSelectAll(QUTO_FEATURE_TABLE)); while (resSet.next()) { Long qutoId = resSet.getLong("id"); actualQutoIdList.add(qutoId); String desc = StringAdapter.getString(resSet.getString("description")).trim(); String title = StringAdapter.getString(resSet.getString("title")).trim(); Long mediaId = resSet.getLong("media_id"); Long cmgId = resSet.getLong("car_model_generation_id"); Long ccoId = resSet.getLong("car_completion_option_id"); if (!ourOldIdFeatureMap.keySet().contains(qutoId)) { Feature f = new Feature(); f.setOldId(qutoId); f.setDescription(desc); f.setTitle(title); f.setCcoId(ccoId); f.setCmgId(cmgId); f.setMediaId(mediaId); if (validate(f, "TroubleQutoId=" + f.getOldId() + "; ")) { featuresForSaveList.add(f); } } else { Feature f = ourOldIdFeatureMap.get(qutoId); if (!title.equals(f.getTitle()) || !cmgId.equals(f.getCmgId()) || !ccoId.equals(f.getCcoId()) || !mediaId.equals(f.getMediaId()) || !desc.equals(f.getDescription())) { f.setOldId(qutoId); f.setDescription(desc); f.setTitle(title); f.setCcoId(ccoId); f.setCmgId(cmgId); f.setMediaId(mediaId); if (validate(f, "TroubleQutoId=" + f.getOldId() + "; ")) { featuresForUpdateList.add(f); } } } } int s = 0; int u = 0; int d = 0; for (Feature f : featuresForSaveList) { featureDao.save(f); s++; } for (Feature f : featuresForUpdateList) { featureDao.update(f); u++; } for (Long qutoId : ourOldIdFeatureMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; featureDao.delete(ourOldIdFeatureMap.get(qutoId)); } } addError("??: " + s + " ? ?, " + u + " , " + d + " ."); } private void updateProperties() throws SQLException, ClassNotFoundException { List<CarProperty> propertiesForSaveList = new ArrayList(); List<CarProperty> propertiesForUpdateList = new ArrayList(); List<CarProperty> propertyList = carPropertyDao.getAllAsc(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, CarProperty> ourOldIdPropertyMap = new HashMap(); for (CarProperty cp : propertyList) { ourOldIdPropertyMap.put(cp.getOldId(), cp); } ResultSet resSet = getFromQutoBase(getSelectAll(QUTO_PROPERTY_TABLE)); while (resSet.next()) { Long qutoId = resSet.getLong("id"); actualQutoIdList.add(qutoId); String title = StringAdapter.getString(resSet.getString("title")).trim(); Long groupId = resSet.getLong("car_property_group_id"); CarPropertyGroup supcpg = new CarPropertyGroup(); supcpg.setOldId(groupId); List<CarPropertyGroup> supCpgList = carPropertyGroupDao.find(supcpg); if (!supCpgList.isEmpty() && groupId != 0) { supcpg = supCpgList.get(0); if (!ourOldIdPropertyMap.keySet().contains(qutoId)) { CarProperty cp = new CarProperty(); cp.setOldId(qutoId); cp.setCPG(supcpg); cp.setTitle(title); if (validate(cp, "TroubleQutoId=" + cp.getOldId() + "; ")) { propertiesForSaveList.add(cp); } } else { CarProperty cp = ourOldIdPropertyMap.get(qutoId); if (!title.equals(cp.getTitle())) { cp.setOldId(qutoId); cp.setCPG(supcpg); cp.setTitle(title); if (validate(cp, "TroubleQutoId=" + cp.getOldId() + "; ")) { propertiesForUpdateList.add(cp); } } } } } int s = 0; int u = 0; int d = 0; for (CarProperty cp : propertiesForSaveList) { carPropertyDao.save(cp); s++; } for (CarProperty cp : propertiesForUpdateList) { carPropertyDao.update(cp); u++; } for (Long qutoId : ourOldIdPropertyMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; carPropertyDao.delete(ourOldIdPropertyMap.get(qutoId)); } } addError("?: " + s + " ? ?, " + u + " , " + d + " ."); } private void updateOptionGroups() throws SQLException, ClassNotFoundException, Exception { List<CarCompletionGroup> ccgList = CCGDao.getAllAsc(); List<CarCompletionGroup> ccgsForSaveList = new ArrayList(); List<CarCompletionGroup> ccgsForUpdateList = new ArrayList(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, CarCompletionGroup> ourOldIdCCGMap = new HashMap(); for (CarCompletionGroup ccg : ccgList) { ourOldIdCCGMap.put(ccg.getOldId(), ccg); } ResultSet resSet = getFromQutoBase(getSelectAll(QUTO_CCG_TABLE)); while (resSet.next()) { Long qutoId = resSet.getLong("id"); actualQutoIdList.add(qutoId); Integer level = resSet.getInt("level"); String title = StringAdapter.getString(resSet.getString("title")).trim(); Long parentId = resSet.getLong("parent_id"); Boolean isHidden = resSet.getBoolean("is_hidden"); Boolean isOptReq = resSet.getBoolean("is_option_require"); Boolean isPublish = resSet.getBoolean("is_publish"); Long sort = resSet.getLong("sort"); CarCompletionGroup ccg = ourOldIdCCGMap.get(qutoId); if (ccg == null) { ccg = new CarCompletionGroup(); ccg.setIsHidden(isHidden); ccg.setIsOptReq(isOptReq); ccg.setIsPublish(isPublish); ccg.setLevel(level); ccg.setOldId(qutoId); ccg.setParentId(parentId); ccg.setSort(sort); ccg.setTitle(title); if (validate(ccg, " ?: quto_id=" + qutoId + "; ")) { ccgsForSaveList.add(ccg); } } else { if (!Objects.equals(ccg.getLevel(), level) || !Objects.equals(ccg.getOldId(), qutoId) || !Objects.equals(ccg.getParentId(), parentId) || !Objects.equals(ccg.getSort(), sort) || !ccg.getTitle().equals(title) || !ccg.isIsHidden().equals(isHidden) || !ccg.isIsOptReq().equals(isOptReq) || !ccg.isIsPublish().equals(isPublish)) { ccg.setIsHidden(isHidden); ccg.setIsOptReq(isOptReq); ccg.setIsPublish(isPublish); ccg.setLevel(level); ccg.setParentId(parentId); ccg.setSort(sort); ccg.setTitle(title); if (validate(ccg, " : quto_id=" + qutoId + "; ")) { ccgsForUpdateList.add(ccg); } } } } int s = 0; int u = 0; int d = 0; for (CarCompletionGroup ccg : ccgsForSaveList) { CCGDao.save(ccg); s++; } for (CarCompletionGroup ccg : ccgsForUpdateList) { CCGDao.update(ccg); u++; } for (Long qutoId : ourOldIdCCGMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; CCGService.delete(ourOldIdCCGMap.get(qutoId)); } } addError(" : " + s + " ? ?, " + u + " , " + d + " ."); } private void updateOptions() throws SQLException, ClassNotFoundException { List<CarCompletionOption> ccosForSaveList = new ArrayList(); List<CarCompletionOption> ccosForUpdateList = new ArrayList(); List<CarCompletionOption> ccoList = CCODao.getAllAsc(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, CarCompletionOption> ourOldIdCCOMap = new HashMap(); for (CarCompletionOption cco : ccoList) { ourOldIdCCOMap.put(cco.getOldId(), cco); } ResultSet resSet = getFromQutoBase(getSelectAll(QUTO_CCO_TABLE)); while (resSet.next()) { Long qutoId = resSet.getLong("id"); actualQutoIdList.add(qutoId); String titleFull = StringAdapter.getString(resSet.getString("title_full")).trim(); String title = StringAdapter.getString(resSet.getString("title")).trim(); if (title.equals("")) { title = " ?"; } Long ccgId = resSet.getLong("car_completion_group_id"); CarCompletionGroup ccg = new CarCompletionGroup(); ccg.setOldId(ccgId); List<CarCompletionGroup> supCcgList = CCGDao.find(ccg); if (!supCcgList.isEmpty() && ccgId != 0) { ccg = supCcgList.get(0); if (!ourOldIdCCOMap.keySet().contains(qutoId)) { CarCompletionOption cco = new CarCompletionOption(); cco.setOldId(qutoId); cco.setTitleFull(titleFull); cco.setTitle(title); cco.setCCG(ccg); if (validate(cco, "TroubleQutoId=" + cco.getOldId() + "; ")) { ccosForSaveList.add(cco); } } else { CarCompletionOption cco = ourOldIdCCOMap.get(qutoId); if (!title.equals(cco.getTitle()) || !titleFull.equals(cco.getTitleFull())) { cco.setOldId(qutoId); cco.setTitleFull(titleFull); cco.setTitle(title); cco.setCCG(ccg); if (validate(cco, "TroubleQutoId=" + cco.getOldId() + "; ")) { ccosForUpdateList.add(cco); } } } } } int s = 0; int u = 0; int d = 0; for (CarCompletionOption cco : ccosForSaveList) { CCODao.save(cco); s++; } for (CarCompletionOption cco : ccosForUpdateList) { CCODao.update(cco); u++; } for (Long qutoId : ourOldIdCCOMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; CCODao.delete(ourOldIdCCOMap.get(qutoId)); } } addError(": " + s + " ? ?, " + u + " , " + d + " ."); } private void updateColors() throws SQLException, ClassNotFoundException { List<Color> colorsForSaveList = new ArrayList(); List<Color> colorsForUpdateList = new ArrayList(); List<Color> colorList = colorDao.getAllAsc(); List<Long> actualQutoIdList = new ArrayList(); HashMap<Long, Color> ourOldIdFeatureMap = new HashMap(); for (Color c : colorList) { ourOldIdFeatureMap.put(c.getOldId(), c); } ResultSet resSet = getFromQutoBase(getSelectAll(QUTO_COLOR_TABLE)); while (resSet.next()) { Long qutoId = resSet.getLong("id"); actualQutoIdList.add(qutoId); String title = StringAdapter.getString(resSet.getString("title")).trim(); if (!ourOldIdFeatureMap.keySet().contains(qutoId)) { Color c = new Color(); c.setOldId(qutoId); c.setTitle(title); if (validate(c, "TroubleQutoId=" + c.getOldId() + "; ")) { colorsForSaveList.add(c); } } else { Color c = ourOldIdFeatureMap.get(qutoId); if (!title.equals(c.getTitle())) { c.setOldId(qutoId); c.setTitle(title); if (validate(c, "TroubleQutoId=" + c.getOldId() + "; ")) { colorsForUpdateList.add(c); } } } } int s = 0; int u = 0; int d = 0; for (Color f : colorsForSaveList) { colorDao.save(f); s++; } for (Color f : colorsForUpdateList) { colorDao.update(f); u++; } for (Long qutoId : ourOldIdFeatureMap.keySet()) { if (!actualQutoIdList.contains(qutoId)) { d++; colorDao.delete(ourOldIdFeatureMap.get(qutoId)); } } addError(": " + s + " ? ?, " + u + " , " + d + " ."); } private void updateCarFeatureLinks() throws SQLException, ClassNotFoundException { List<Feature> fullFList = featureDao.getAllAsc(); HashMap<Long, Feature> ourOldIdFeatureMap = new HashMap(); for (Feature f : fullFList) { ourOldIdFeatureMap.put(f.getOldId(), f); } List<Car> carList = carDao.getAllAsc(); HashMap<Long, Car> ourOldIdCarMap = new HashMap(); for (Car car : carList) { ourOldIdCarMap.put(car.getCmqId(), car); } ResultSet resSet = getFromQutoBase(getSelectAll(QUTO_CAR_TO_FEATURE_LINK_TABLE)); HashMap<Long, ArrayList<Feature>> newFeatureInfo = new HashMap(); while (resSet.next()) { Long carOldId = resSet.getLong("car_modification_id"); //Car car = ourOldIdCarMap.get(carOldId); ArrayList fList = newFeatureInfo.get(carOldId); if (fList == null) { fList = new ArrayList(); } Long featureOldId = resSet.getLong("car_feature_id"); Feature f = ourOldIdFeatureMap.get(featureOldId); fList.add(f); newFeatureInfo.put(carOldId, fList); } for (Long carOldId : ourOldIdCarMap.keySet()) { Car car = ourOldIdCarMap.get(carOldId); ArrayList<Feature> newFList = newFeatureInfo.get(carOldId); if (newFList == null) { newFList = new ArrayList(); } car.setFeatures(newFList); carDao.update(car); } addError("?? ."); } private void updatePropertyNames() throws SQLException, ClassNotFoundException, Exception { List<Car> carList = carDao.getAllAsc(); HashMap<Long, Car> ourOldIdCarMap = new HashMap(); for (Car car : carList) { ourOldIdCarMap.put(car.getCmqId(), car); } List<CarProperty> fullCPList = carPropertyDao.getAllAsc(); HashMap<Long, CarProperty> ourOldIdCpMap = new HashMap(); for (CarProperty cp : fullCPList) { ourOldIdCpMap.put(cp.getOldId(), cp); } HashMap<Long, HashMap<Long, PropertyName>> newInfoCarMap = new HashMap(); List<PropertyName> pnListForSave = new ArrayList(); List<PropertyName> pnListForUpdate = new ArrayList(); List<PropertyName> pnListForDelete = new ArrayList(); ResultSet resSet = getFromQutoBase( "SELECT link.*,cpv.* FROM car_modification_property_value_link link LEFT JOIN car_property_value cpv ON link.car_property_value_id=cpv.id LEFT JOIN car_modification cm ON link.car_modification_id=cm.id WHERE cm.usage='ad_archive_catalog'"); //HashMap<Long,ArrayList<Feature>> newFeatureInfo = new HashMap(); while (resSet.next()) { Long carOldId = resSet.getLong("car_modification_id"); HashMap<Long, PropertyName> newInfoPNMap = newInfoCarMap.get(carOldId); if (newInfoPNMap == null) { newInfoPNMap = new HashMap(); } PropertyName newPn = new PropertyName(); Long oldcpId = resSet.getLong("car_property_id"); String strVal = StringAdapter.getString(resSet.getString("value_string")).trim(); Double numVal = resSet.getDouble("value_number"); Long oldPnId = resSet.getLong("id"); String pnVal = strVal; if (pnVal.equals("")) { pnVal = StringAdapter.getString(numVal).replace(".", ","); } newPn.setOldValueId(oldPnId); newPn.setPropertyNameValue(pnVal); newPn.setParamValue(numVal); newInfoPNMap.put(oldcpId, newPn); newInfoCarMap.put(carOldId, newInfoPNMap); } for (Long carOldId : ourOldIdCarMap.keySet()) { try { Car car = ourOldIdCarMap.get(carOldId); List<PropertyName> oldPnList = car.getPropertyNames(); HashMap<Long, PropertyName> oldPnMap = new HashMap(); if (oldPnList == null) { oldPnList = new ArrayList(); } HashMap<Long, PropertyName> newInfoPNMap = newInfoCarMap.get(carOldId); //int existingPnSize = 0; for (PropertyName pn : oldPnList) { Long oldcpId = pn.getCarProperty().getOldId(); oldPnMap.put(oldcpId, pn); /*PropertyName newPn = newInfoPNMap.get(oldcpId); if(newPn!=null){ pn.setOldValueId(newPn.getOldValueId()); pn.setParamValue(newPn.getParamValue()); pn.setPropertyNameValue(newPn.getPropertyNameValue()); propertyNameDao.update(pn); existingPnSize++; }else{ propertyNameDao.delete(pn); }*/ } if (oldPnMap.size() != oldPnList.size()) { //throw new Exception("? :"+car.getCarId()+" ? ?!"); addError("? :" + car.getCarId() + " ? ?!"); } for (Long oldcpId : newInfoPNMap.keySet()) { PropertyName newPn = newInfoPNMap.get(oldcpId); PropertyName oldPn = oldPnMap.get(oldcpId); if (oldPn == null) { oldPn = newPn; oldPn.setCar(car); oldPn.setAudial((long) 0); oldPn.setVisual((long) 0); oldPn.setKinestet((long) 0); oldPn.setCarProperty(ourOldIdCpMap.get(oldcpId)); oldPn.setPercentValue((long) 0); if (validate(oldPn, " ? ? ?: auto_quto_id=" + car.getCmqId() + ", pnv_quto_id=" + newPn.getOldValueId() + ", cp_quto_id=" + oldcpId + "; ")) { pnListForSave.add(oldPn); } } else { oldPn.setParamValue(newPn.getParamValue()); oldPn.setPropertyNameValue(newPn.getPropertyNameValue()); oldPn.setOldValueId(newPn.getOldValueId()); if (validate(oldPn, " ? ? : auto_quto_id=" + car.getCmqId() + ", pnv_quto_id=" + newPn.getOldValueId() + ", cp_quto_id=" + oldcpId + "; ")) { pnListForUpdate.add(oldPn); } } } for (Long oldcpId : oldPnMap.keySet()) { if (newInfoPNMap.get(oldcpId) == null) { pnListForDelete.add(oldPnMap.get(oldcpId)); } } } catch (Exception e) { throw new Exception( " ? ? quto_car_id:" + carOldId + "; " + e); } } int s = 0; int u = 0; int d = 0; for (PropertyName pn : pnListForSave) { propertyNameDao.save(pn); s++; } for (PropertyName pn : pnListForUpdate) { propertyNameDao.update(pn); u++; } for (PropertyName pn : pnListForDelete) { propertyNameDao.delete(pn); d++; } addError("? : " + s + " ?, " + u + " , " + d + " ."); } private void updateCarOptionValues() throws SQLException, ClassNotFoundException { List<Car> carList = carDao.getAllAsc(); HashMap<Long, Car> ourOldIdCarMap = new HashMap(); HashMap<Long, CarOptionValue> fullOldIdCovMap = new HashMap(); for (Car car : carList) { ourOldIdCarMap.put(car.getCmqId(), car); for (CarOptionValue cov : car.getCarOptionValues()) { fullOldIdCovMap.put(cov.getOldId(), cov); } } List<CarCompletionOption> fullCCOList = CCODao.getAllAsc(); HashMap<Long, CarCompletionOption> ourOldIdCCOMap = new HashMap(); for (CarCompletionOption cco : fullCCOList) { ourOldIdCCOMap.put(cco.getOldId(), cco); } HashSet<Long> fullOldCovIdInfoSet = new HashSet(); List<CarOptionValue> covListForSave = new ArrayList(); List<CarOptionValue> covListForUpdate = new ArrayList(); List<CarOptionValue> covListForDelete = new ArrayList(); ResultSet resSet = getFromQutoBase( "SELECT cmco.* FROM car_modification_completion_option cmco LEFT JOIN car_modification cm ON cmco.car_modification_id=cm.id WHERE cm.usage='ad_archive_catalog'"); while (resSet.next()) { Long carOldId = resSet.getLong("car_modification_id"); Long covOldId = resSet.getLong("id"); fullOldCovIdInfoSet.add(covOldId); Double price = resSet.getDouble("price"); String title = StringAdapter.getString(resSet.getString("title")).trim(); String desc = StringAdapter.getString(resSet.getString("description")).trim(); Long sort = resSet.getLong("sort"); Boolean isPack = resSet.getBoolean("is_package"); Long ccoOldId = resSet.getLong("car_completion_option_id"); Long imageId = resSet.getLong("car_modification_completion_option_image_id"); CarOptionValue cov = fullOldIdCovMap.get(covOldId); CarCompletionOption cco = ourOldIdCCOMap.get(ccoOldId); if (cco == null) { addError("? ? Quto:" + ccoOldId + "; "); } else { if (cov == null) { cov = new CarOptionValue(); cov.setAudial(0); cov.setVisual(0); cov.setKinestet(0); cov.setCCO(cco); cov.setCcoOldId(ccoOldId); cov.setDescription(desc); cov.setIsPack(isPack); cov.setOldId(covOldId); cov.setTitle(title); cov.setSort(sort); cov.setCar(ourOldIdCarMap.get(carOldId)); cov.setPrice(price); cov.setCovImageId(imageId); if (validate(cov, " ? ?: auto_quto_id=" + carOldId + ", cov_quto_id=" + covOldId + ", cco_quto_id=" + ccoOldId + "; ")) { covListForSave.add(cov); } } else { if (!Objects.equals(cov.getCCO().getOldId(), ccoOldId) || !desc.equals(cov.getDescription()) || isPack.compareTo(cov.isIsPack()) != 0 || !title.equals(cov.getTitle()) || !Objects.equals(cov.getSort(), sort) || price.compareTo(cov.getPrice()) != 0 || !Objects.equals(cov.getCovImageId(), imageId) || !Objects.equals(cov.getCar().getCmqId(), carOldId)) { cov.setCCO(cco); cov.setCcoOldId(ccoOldId); cov.setDescription(desc); cov.setIsPack(isPack); cov.setOldId(covOldId); cov.setTitle(title); cov.setSort(sort); cov.setCar(ourOldIdCarMap.get(carOldId)); cov.setPrice(price); cov.setCovImageId(imageId); if (validate(cov, " ? : auto_quto_id=" + carOldId + ", cov_quto_id=" + covOldId + ", cco_quto_id=" + ccoOldId + "; ")) { covListForUpdate.add(cov); } } } } } for (Long oldCovId : fullOldIdCovMap.keySet()) { if (!fullOldCovIdInfoSet.contains(oldCovId)) { covListForDelete.add(fullOldIdCovMap.get(oldCovId)); } } int s = 0; int u = 0; int d = 0; for (CarOptionValue cov : covListForSave) { carOptionValueDao.save(cov); s++; } for (CarOptionValue cov : covListForUpdate) { carOptionValueDao.update(cov); u++; } for (CarOptionValue cov : covListForDelete) { carOptionValueDao.delete(cov); d++; } addError(" : " + s + " ?, " + u + " , " + d + " ."); } private ResultSet getFromQutoBase(String query) throws SQLException, ClassNotFoundException { Statement st = getConnection().createStatement(); ResultSet queryResult = st.executeQuery(query); return queryResult; } private String getSelectAll(String tableName) { return "select * from " + tableName; } private String trim(String str) { return (str != null ? str.trim() : str); } private Connection getConnection() throws ClassNotFoundException, SQLException { if (conn == null) { String dbDriver = "com.mysql.jdbc.Driver"; String dbName = "bufer_base"; String dbUser = "root"; String dbPass = "qwerty123!"; String dbEncoding = "UTF-8"; Class.forName(dbDriver); String url = "jdbc:mysql://localhost:3306/" + dbName + "?autoReconnect=true"; Properties properties = new Properties(); properties.setProperty("user", dbUser); properties.setProperty("password", dbPass); properties.setProperty("useUnicode", "true"); properties.setProperty("characterEncoding", dbEncoding); conn = DriverManager.getConnection(url, properties); } return conn; } /** * ?? ? * * @param oldId ? * @param oldName ? * @return */ /*public boolean existOldId(Long oldId, String oldName) { return (getNewId(oldId, oldName) != null); }*/ /** * ? * * @param oldId ? * @param oldName ? - * @param newCl ?? ?? * @return id null */ /*public Long getNewId(Long oldId, String oldName) { DataLink link = dataLinkDao.getByParams(oldId, oldName); if (link != null) { return link.getNewId(); } return null; }*/ }