Java tutorial
package siddur.solidtrust.marktplaats; import java.math.BigDecimal; import java.math.BigInteger; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.apache.commons.lang3.StringUtils; import org.apache.log4j.Logger; import org.apache.lucene.analysis.Analyzer; import org.apache.lucene.analysis.standard.StandardAnalyzer; import org.apache.lucene.index.Term; import org.apache.lucene.queryParser.QueryParser; import org.apache.lucene.search.Query; import org.apache.lucene.search.TermQuery; import org.apache.lucene.util.Version; import org.hibernate.search.jpa.FullTextEntityManager; import org.hibernate.search.jpa.FullTextQuery; import org.hibernate.search.jpa.Search; import org.hibernate.search.query.dsl.BooleanJunction; import org.hibernate.search.query.dsl.QueryBuilder; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageImpl; import org.springframework.data.domain.Pageable; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.ui.Model; import siddur.solidtrust.priceAnalysis.NewMarktplaats; import siddur.solidtrust.priceAnalysis.SortedMarktplaats; import siddur.solidtrust.util.DateUtil; import siddur.solidtrust.util.LuceneUtil; @Service public class MarktplaatsService { private static final Logger log4j = Logger.getLogger(MarktplaatsService.class); @PersistenceContext private EntityManager em; private String marktplaatsCarSelectSQL = "select m.id, m.licensePlate, m.brand, m.model, m.type, m.build, m.color, m.price, m.enginesize, m.fuel, m.ownerType, m.mileage, m.adDate, m.dateRemoved, m.dateRegisted, datediff(m.dateRegisted, m.adDate)as days "; private String marktplaatsCarMileageSelectSQL = "select m.id, m.licensePlate, m.brand, m.model, m.build, m.fuel, m.mileage "; public List<NewMarktplaats> findEntityByLicensePlate(String lp) { String jqpl = "from NewMarktplaats m where m.licensePlate='" + lp + "'"; return em.createQuery(jqpl, NewMarktplaats.class).getResultList(); } /* * Used for Days for sale (sold), delete the repetitions with same license plate and registed date */ public Page<MarktplaatsCar> timeOnSale(Pageable pageable, String brand, String model, String build, String fuelType) { String baseJpql = "from SortedMarktplaats s, NewMarktplaats m where s.id = m.id and m.dateRegisted is not null and m.repetition is null"; if (!StringUtils.isEmpty(brand)) { baseJpql += " and s.brand = '" + brand + "'"; } if (!StringUtils.isEmpty(model)) { baseJpql += " and s.model = '" + model + "'"; } if (!StringUtils.isEmpty(build)) { baseJpql += " and s.build = '" + build + "'"; } if (!StringUtils.isEmpty(fuelType)) { baseJpql += " and s.fuelType = '" + fuelType + "'"; } String countSql = "select count(m) " + baseJpql; int count = em.createQuery(countSql, Long.class).getSingleResult().intValue(); String jpql = marktplaatsCarSelectSQL + baseJpql; log4j.info(jpql); List<Object[]> list = em.createQuery(jpql, Object[].class) .setFirstResult(pageable.getPageSize() * pageable.getPageNumber()) .setMaxResults(pageable.getPageSize()).getResultList(); List<MarktplaatsCar> results = toMarktplaatsList(list); Page<MarktplaatsCar> page = new PageImpl<MarktplaatsCar>(results, pageable, count); return page; } /* * Used for Days for sale (sold), delete the repetitions with same license plate and registed date * * 2015-10-09 * select avg(datediff(m.dateRegisted, m.adDate)) from NewMarktplaats m where m.dateRegisted is not null and m.repetition is null; * 33.6601 * * select count(1) from NewMarktplaats m ; * 1280339 * * select count(1) from NewMarktplaats m where m.dateRegisted is not null and m.repetition is null; * 437297 * */ public int averageTimeOnSale(String brand, String model, String build, String fuelType) { String baseJpql = "select avg(datediff(m.dateRegisted, m.adDate)) from SortedMarktplaats s, NewMarktplaats m where s.id = m.id and m.dateRegisted is not null and m.repetition is null"; if (!StringUtils.isEmpty(brand)) { baseJpql += " and s.brand = '" + brand + "'"; } if (!StringUtils.isEmpty(model)) { baseJpql += " and s.model = '" + model + "'"; } if (!StringUtils.isEmpty(build)) { baseJpql += " and s.build = '" + build + "'"; } if (!StringUtils.isEmpty(fuelType)) { baseJpql += " and s.fuelType = '" + fuelType + "'"; } BigDecimal avg = (BigDecimal) em.createNativeQuery(baseJpql).getSingleResult(); return avg.intValue(); } /* * Used in stadagen */ @Transactional(readOnly = true) public Page<MarktplaatsCar> timeOnSaleByLucene(Pageable pageable, String brand, String model, String build) throws Exception { List<Integer> items = searchIds(brand, model, build, null); String jpql = marktplaatsCarSelectSQL + " from NewMarktplaats m where m.id in :ids and m.dateRegisted is not null"; String totalJpql = "select count(m) from NewMarktplaats m where m.id in :ids and m.dateRegisted is not null"; log4j.info(jpql); List<Object[]> list = em.createQuery(jpql, Object[].class).setParameter("ids", items) .setFirstResult(pageable.getPageSize() * pageable.getPageNumber()) .setMaxResults(pageable.getPageSize()).getResultList(); long total = em.createQuery(totalJpql, Long.class).setParameter("ids", items).getSingleResult(); List<MarktplaatsCar> results = toMarktplaatsList(list); Page<MarktplaatsCar> page = new PageImpl<MarktplaatsCar>(results, pageable, total); return page; } @Deprecated //replace by averageTimeOnSaleByLucene2 @Transactional(readOnly = true) public int averageTimeOnSaleByLucene(String brand, String model, String build) throws Exception { List<Integer> items = searchIds(brand, model, build, null); if (items.isEmpty()) { return 0; } String baseJpql = "select avg(datediff(m.dateRegisted, m.adDate)) from NewMarktplaats m where m.id in :ids and m.dateRegisted is not null"; BigDecimal avg = (BigDecimal) em.createNativeQuery(baseJpql).setParameter("ids", items).getSingleResult(); return avg == null ? 0 : avg.intValue(); } @Transactional(readOnly = true) public int averageTimeOnSaleByLucene2(String brand, String model, String build) throws Exception { List<SortedMarktplaats> items = search(brand, model, build, null); if (items.isEmpty()) { return 0; } log4j.info("found " + items.size()); int perDay = 1000 * 60 * 60 * 24; List<SortedMarktplaats> newList = new ArrayList<>(); for (SortedMarktplaats sm : items) { if (sm.getDateRegisted() != null) { newList.add(sm); } } log4j.info("after filter, found " + newList.size()); int total = 0; for (SortedMarktplaats i : newList) { Date d1 = DateUtil.string2Date1(i.getDateRegisted()); Date d2 = DateUtil.string2Date1(i.getAdDate()); total += (int) ((d1.getTime() - d2.getTime()) / perDay); } return total / newList.size(); // double avg = items.stream().filter(i -> i.getDateRegisted() != null) // .mapToInt(i -> { // Date d1 = DateUtil.string2Date1(i.getDateRegisted()); // Date d2 = DateUtil.string2Date1(i.getAdDate()); // return (int)((d1.getTime() - d2.getTime()) / perDay); // }).average().getAsDouble(); // return (int)avg; } @Transactional(readOnly = true) public void mileageByLucene(Model _model, Pageable pageable, String brand, String model, String build, String fuelType) throws Exception { List<Integer> items = searchIds(brand, model, build, fuelType); if (items.isEmpty()) { return; } String suffix = " from NewMarktplaats m where m.id in :ids and m.mileage > 10"; String jpql = marktplaatsCarMileageSelectSQL + suffix; String totalJpql = "select count(m), avg(m.mileage)" + suffix; List<Object[]> list = em.createQuery(jpql, Object[].class).setParameter("ids", items) .setFirstResult(pageable.getOffset()).setMaxResults(pageable.getPageSize()).getResultList(); Object[] count = em.createQuery(totalJpql, Object[].class).setParameter("ids", items).getSingleResult(); List<MarktplaatsCar> results = toMarktplaatsListForMileage(list); Page<MarktplaatsCar> page = new PageImpl<MarktplaatsCar>(results, pageable, (long) count[0]); _model.addAttribute("page", page); _model.addAttribute("mileage", ((Double) count[1]).intValue()); } //replace by mileageByLuceneForAPI2 @Deprecated @Transactional(readOnly = true) public Double mileageByLuceneForAPI(String brand, String model, String build, String fuelType) throws Exception { List<Integer> items = searchIds(brand, model, build, fuelType); if (items.isEmpty()) { return null; } log4j.info("found " + items.size()); String suffix = " from NewMarktplaats m where m.id in :ids and m.mileage > 10"; String totalJpql = "select avg(m.mileage)" + suffix; Double avg = em.createQuery(totalJpql, Double.class).setParameter("ids", items).getSingleResult(); return avg; } @Transactional(readOnly = true) public Integer mileageByLuceneForAPI2(String brand, String model, String build, String fuelType) throws Exception { List<SortedMarktplaats> items = search(brand, model, build, fuelType); if (items.isEmpty()) { return null; } log4j.info("found " + items.size()); List<Integer> ints = new ArrayList<>(); for (SortedMarktplaats sm : items) { if (sm.getMileage() > 10) { ints.add(sm.getMileage()); } } int total = 0; for (Integer i : ints) { total += i; } return total / ints.size(); // Double avg = items.stream() // .filter(i -> i.getMileage() > 10) // .mapToInt(i -> i.getMileage()) // .average() // .getAsDouble(); // return avg; } //replaced by search @Deprecated private List<Integer> searchIds(String brand, String model, String build, String fuelType) throws Exception { FullTextEntityManager fullTextEntityManager = Search.getFullTextEntityManager(em); QueryBuilder qb = fullTextEntityManager.getSearchFactory().buildQueryBuilder() .forEntity(SortedMarktplaats.class).get(); Analyzer analyzer = new StandardAnalyzer(Version.LUCENE_36, LuceneUtil.getStopwords()); String s = "0;0;0;0"; String[] boostArray = null; boostArray = s.split(";"); @SuppressWarnings("rawtypes") BooleanJunction bj = qb.bool(); LuceneUtil.setBoost(0, boostArray, new TermQuery(new Term("build", build)), bj); LuceneUtil.setBoost(1, boostArray, new TermQuery(new Term("brand", brand)), bj); if (!StringUtils.isEmpty(model)) { QueryParser parser = new QueryParser(Version.LUCENE_36, "model", analyzer); LuceneUtil.setBoost(2, boostArray, parser.parse(QueryParser.escape(model)), bj); } if (!StringUtils.isEmpty(fuelType)) { QueryParser parser = new QueryParser(Version.LUCENE_36, "fuelType", analyzer); LuceneUtil.setBoost(3, boostArray, parser.parse(QueryParser.escape(fuelType)), bj); } Query query = bj.createQuery(); FullTextQuery persistenceQuery = fullTextEntityManager.createFullTextQuery(query, SortedMarktplaats.class); persistenceQuery.setProjection(FullTextQuery.THIS); @SuppressWarnings("unchecked") List<Object[]> list = persistenceQuery.getResultList(); List<Integer> items = new ArrayList<Integer>(list.size()); for (Object[] array : list) { SortedMarktplaats item = (SortedMarktplaats) array[0]; items.add(item.getId()); } log4j.info(items); return items; } private List<SortedMarktplaats> search(String brand, String model, String build, String fuelType) throws Exception { FullTextEntityManager fullTextEntityManager = Search.getFullTextEntityManager(em); QueryBuilder qb = fullTextEntityManager.getSearchFactory().buildQueryBuilder() .forEntity(SortedMarktplaats.class).get(); Analyzer analyzer = new StandardAnalyzer(Version.LUCENE_36, LuceneUtil.getStopwords()); String s = "0;0;0;0"; String[] boostArray = null; boostArray = s.split(";"); @SuppressWarnings("rawtypes") BooleanJunction bj = qb.bool(); LuceneUtil.setBoost(0, boostArray, new TermQuery(new Term("build", build)), bj); LuceneUtil.setBoost(1, boostArray, new TermQuery(new Term("brand", brand)), bj); if (!StringUtils.isEmpty(model)) { QueryParser parser = new QueryParser(Version.LUCENE_36, "model", analyzer); LuceneUtil.setBoost(2, boostArray, parser.parse(QueryParser.escape(model)), bj); } if (!StringUtils.isEmpty(fuelType)) { QueryParser parser = new QueryParser(Version.LUCENE_36, "fuelType", analyzer); LuceneUtil.setBoost(3, boostArray, parser.parse(QueryParser.escape(fuelType)), bj); } Query query = bj.createQuery(); FullTextQuery persistenceQuery = fullTextEntityManager.createFullTextQuery(query, SortedMarktplaats.class); persistenceQuery.setProjection(FullTextQuery.THIS).setMaxResults(500); @SuppressWarnings("unchecked") List<Object[]> list = persistenceQuery.getResultList(); List<SortedMarktplaats> items = new ArrayList<SortedMarktplaats>(list.size()); for (Object[] array : list) { SortedMarktplaats item = (SortedMarktplaats) array[0]; items.add(item); } return items; } public List<Fast100> top100(int maxCount, String brand, Integer buildStart, Integer buildEnd, Integer priceStart, Integer priceEnd) { return top100(maxCount, brand, buildStart, buildEnd, priceStart, priceEnd, false); } /* * Don't count records with same license plate and register date. */ public List<Fast100> top100(int maxCount, String brand, Integer buildStart, Integer buildEnd, Integer priceStart, Integer priceEnd, boolean cache) { if (cache == false) { boolean useCache = false; if (maxCount == 100 && brand == null && buildStart == null && buildEnd == null && priceStart == null && priceEnd == null) { useCache = true; } if (useCache) { List<Fast100> top100 = em.createQuery("from Fast100", Fast100.class).getResultList(); return top100; } } String brandCondition = ""; if (!StringUtils.isEmpty(brand)) { brandCondition = " and s.brand = '" + brand + "'"; } String buildCondition = ""; if (buildEnd != null && buildStart != null) { buildCondition = " and s.build between '" + buildStart + "' and '" + buildEnd + "'"; } String priceCondition = ""; if (priceEnd != null && priceStart != null) { priceCondition = " and price between " + priceStart + " and " + priceEnd; } //exclude price = 99999 999999 9999999 String jqpl = "select s.brand, s.model, s.build, s.engineSize, s.fuelType, m.arrangement, count(1) as count, avg(datediff(m.dateRegisted, m.adDate)) as avg, avg(s.price) as price" + " from SortedMarktplaats s, NewMarktplaats m" + " where m.repetition is null and m.dateRegisted is not null and m.price != 99999 and m.price != 999999 and m.price != 9999999 and s.id = m.id" + brandCondition + buildCondition + " group by s.brand, s.model, s.build, s.engineSize, s.fuelType, m.arrangement" + " having count > " + maxCount + priceCondition + " order by avg asc limit 100"; @SuppressWarnings("unchecked") List<Object[]> list = em.createNativeQuery(jqpl).getResultList(); List<Fast100> results = new ArrayList<Fast100>(list.size()); for (Object[] objs : list) { Fast100 f = new Fast100(); results.add(f); int i = 0; f.setBrand((String) objs[i++]); f.setModel((String) objs[i++]); f.setBuild((String) objs[i++]); f.setEngineSize((Integer) objs[i++]); f.setFuelType((String) objs[i++]); f.setArrangement((String) objs[i++]); f.setCount(((BigInteger) objs[i++]).intValue()); f.setAvg(((BigDecimal) objs[i++]).intValue()); f.setAvgPrice(((BigDecimal) objs[i++]).intValue()); } return results; } /* * Don't count records with same license plate and register date. */ public Sorter top100Sort(Fast100 f) { String baseJpql = "from SortedMarktplaats s, NewMarktplaats m where s.id = m.id" + " and m.repetition is null" + " and m.dateRegisted is not null" + " and m.price != 99999 and m.price != 999999 and m.price != 9999999" + " and s.brand = '" + f.getBrand() + "' and s.model = '" + f.getModel() + "' and s.build = '" + f.getBuild() + "' and s.engineSize = " + f.getEngineSize() + " and s.fuelType = '" + f.getFuelType() + "' and m.arrangement = '" + f.getArrangement() + "'"; String selectSql = "select m.ownerType, m.color, m.price, m.mileage, datediff(m.dateRegisted, m.adDate)as days "; String jpql = selectSql + baseJpql; log4j.info(jpql); @SuppressWarnings("unchecked") List<Object[]> list = em.createNativeQuery(jpql).getResultList(); List<CarItemToSort> results = new ArrayList<CarItemToSort>(list.size()); for (Object[] objs : list) { CarItemToSort car = new CarItemToSort(); results.add(car); int i = 0; car.setOwnerType((Integer) objs[i++]); car.setColor((String) objs[i++]); car.setPrice((Integer) objs[i++]); car.setMileage((Integer) objs[i++]); car.setDays(((BigInteger) objs[i++]).intValue()); } Sorter sorter = new Sorter(results); sorter.sort(); return sorter; } /* * Don't count records with same license plate and register date. */ public Page<MarktplaatsCar> top100Detail(Pageable pageable, Fast100 f, String type, int index, String name) { String baseJpql = "from SortedMarktplaats s, NewMarktplaats m where s.id = m.id" + " and m.repetition is null" + " and m.dateRegisted is not null" + " and m.price != 99999 and m.price != 999999 and m.price != 9999999" + " and s.brand = '" + f.getBrand() + "' and s.model = '" + f.getModel() + "' and s.build = '" + f.getBuild() + "' and s.engineSize = " + f.getEngineSize() + " and s.fuelType = '" + f.getFuelType() + "' and m.arrangement = '" + f.getArrangement() + "'"; if (type.equals("color")) { baseJpql += " and s.color='" + name + "'"; } else { Integer[] range = type.equals("price") ? Sorter.PRICE_RANGE : Sorter.MILEAGE_RANGE; if (index == 0) { baseJpql += " and s." + type + " < " + range[index]; } else if (index == range.length) { baseJpql += " and s." + type + " >= " + range[index - 1]; } else { baseJpql += " and s." + type + " < " + range[index]; baseJpql += " and s." + type + " >= " + range[index - 1]; } } String jpql = marktplaatsCarSelectSQL + baseJpql; log4j.info(jpql); List<Object[]> list = em.createQuery(jpql, Object[].class) .setFirstResult(pageable.getPageSize() * pageable.getPageNumber()) .setMaxResults(pageable.getPageSize()).getResultList(); List<MarktplaatsCar> results = toMarktplaatsList(list); long count = em.createQuery("select count(m) " + baseJpql, Long.class).getSingleResult(); Page<MarktplaatsCar> page = new PageImpl<MarktplaatsCar>(results, pageable, (int) count); return page; } private static List<MarktplaatsCar> toMarktplaatsList(List<Object[]> list) { List<MarktplaatsCar> results = new ArrayList<MarktplaatsService.MarktplaatsCar>(list.size()); for (Object[] objs : list) { MarktplaatsCar car = new MarktplaatsCar(); results.add(car); int i = 0; car.id = (Integer) objs[i++]; car.licensePlate = (String) objs[i++]; car.brand = (String) objs[i++]; car.model = (String) objs[i++]; car.type = (String) objs[i++]; car.build = (String) objs[i++]; car.color = (String) objs[i++]; car.price = (Integer) objs[i++]; car.engineSize = (Integer) objs[i++]; car.fuelType = (String) objs[i++]; car.ownerType = (Integer) objs[i++]; car.mileage = (Integer) objs[i++]; car.adDate = (String) objs[i++]; car.dateRemoved = (String) objs[i++]; car.dateRegisted = (String) objs[i++]; car.days = (Integer) objs[i++]; } return results; } private static List<MarktplaatsCar> toMarktplaatsListForMileage(List<Object[]> list) { List<MarktplaatsCar> results = new ArrayList<MarktplaatsService.MarktplaatsCar>(list.size()); for (Object[] objs : list) { MarktplaatsCar car = new MarktplaatsCar(); results.add(car); int i = 0; car.id = (Integer) objs[i++]; car.licensePlate = (String) objs[i++]; car.brand = (String) objs[i++]; car.model = (String) objs[i++]; car.build = (String) objs[i++]; car.fuelType = (String) objs[i++]; car.mileage = (Integer) objs[i++]; } return results; } public static class CarItemToSort { private int price; private int mileage; private int ownerType; private String color; private int days; public int getPrice() { return price; } public void setPrice(int price) { this.price = price; } public int getMileage() { return mileage; } public void setMileage(int mileage) { this.mileage = mileage; } public int getOwnerType() { return ownerType; } public void setOwnerType(int ownerType) { this.ownerType = ownerType; } public String getColor() { return color; } public void setColor(String color) { this.color = color; } public int getDays() { return days; } public void setDays(int days) { this.days = days; } } public static class MarktplaatsCar { private int id; private String licensePlate; private String brand; private String model; private String type; private String build; private String color; private int engineSize; private String fuelType; private int ownerType; private int price; private int mileage; private String adDate; private String dateRemoved; private String dateRegisted; private int days; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getLicensePlate() { return licensePlate; } public void setLicensePlate(String licensePlate) { this.licensePlate = licensePlate; } public String getBrand() { return brand; } public void setBrand(String brand) { this.brand = brand; } public String getModel() { return model; } public void setModel(String model) { this.model = model; } public String getColor() { return color; } public void setColor(String color) { this.color = color; } public int getPrice() { return price; } public void setPrice(int price) { this.price = price; } public int getMileage() { return mileage; } public void setMileage(int mileage) { this.mileage = mileage; } public String getAdDate() { return adDate; } public void setAdDate(String adDate) { this.adDate = adDate; } public String getDateRemoved() { return dateRemoved; } public void setDateRemoved(String dateRemoved) { this.dateRemoved = dateRemoved; } public String getDateRegisted() { return dateRegisted; } public void setDateRegisted(String dateRegisted) { this.dateRegisted = dateRegisted; } public int getDays() { return days; } public void setDays(int days) { this.days = days; } public String getType() { return type; } public void setType(String type) { this.type = type; } public String getBuild() { return build; } public void setBuild(String build) { this.build = build; } public int getEngineSize() { return engineSize; } public void setEngineSize(int engineSize) { this.engineSize = engineSize; } public String getFuelType() { return fuelType; } public void setFuelType(String fuelType) { this.fuelType = fuelType; } public int getOwnerType() { return ownerType; } public void setOwnerType(int ownerType) { this.ownerType = ownerType; } } }