Java tutorial
package siddur.solidtrust.autoscout; import java.math.BigDecimal; import java.math.BigInteger; import java.util.ArrayList; 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.springframework.data.domain.Page; import org.springframework.data.domain.PageImpl; import org.springframework.data.domain.Pageable; import org.springframework.stereotype.Service; import siddur.solidtrust.marktplaats.Fast100; import siddur.solidtrust.marktplaats.Sorter; import siddur.solidtrust.marktplaats.MarktplaatsService.CarItemToSort; @Service public class AutoscoutService { private static final Logger log4j = Logger.getLogger(AutoscoutService.class); @PersistenceContext private EntityManager em; private String selectSQL = "select m.id, m.licensePlate, m.brand, m.model, m.build, m.color, m.price, m.enginesize, m.fuel, m.ownerType, m.mileage, m.dateScraped, m.dateRemoved, m.dateRegisted, datediff(m.dateRegisted, m.dateScraped)as days "; public List<AutoscoutNl> findEntityByLicensePlate(String lp) { String jqpl = "from AutoscoutNl m where m.licensePlate='" + lp + "'"; return em.createQuery(jqpl, AutoscoutNl.class).getResultList(); } /* * Sold cars. * Delete the repetitions with same license plate and registed date */ public Page<ScoutCar> timeOnSale(Pageable pageable, String brand, String model, String build) { String baseJpql = "from AutoscoutNl m where m.dateRegisted is not null and m.repetition is null"; if (!StringUtils.isEmpty(brand)) { baseJpql += " and m.brand = '" + brand + "'"; } if (!StringUtils.isEmpty(model)) { baseJpql += " and m.model = '" + model + "'"; } if (!StringUtils.isEmpty(build)) { baseJpql += " and m.build = '" + build + "'"; } long count = em.createQuery("select count(m) " + baseJpql, Long.class).getSingleResult(); String jpql = selectSQL + baseJpql; log4j.info(jpql); List<Object[]> list = em.createQuery(jpql, Object[].class) .setFirstResult(pageable.getPageSize() * pageable.getPageNumber()) .setMaxResults(pageable.getPageSize()).getResultList(); List<ScoutCar> results = ScoutCar.toScoutCarList(list, true); Page<ScoutCar> page = new PageImpl<ScoutCar>(results, pageable, (int) count); return page; } /* * Sold cars. * Delete the repetitions with same license plate and registed date * * 2015-10-09 * select avg(datediff(m.dateRegisted, m.dateScraped)) from AutoscoutNl m where m.dateRegisted is not null and m.repetition is null * 35.6594 * * select count(1) from AutoscoutNl m where m.dateRegisted is not null and m.repetition is null * 251391 * * select count(1) from AutoscoutNl m * 513173 */ public int averageTimeOnSale(String brand, String model, String build) { String baseJpql = "select avg(datediff(m.dateRegisted, m.dateScraped)) from AutoscoutNl m where m.dateRegisted is not null and m.repetition is null"; if (!StringUtils.isEmpty(brand)) { baseJpql += " and m.brand = '" + brand + "'"; } if (!StringUtils.isEmpty(model)) { baseJpql += " and m.model = '" + model + "'"; } if (!StringUtils.isEmpty(build)) { baseJpql += " and m.build = '" + build + "'"; } BigDecimal avg = (BigDecimal) em.createNativeQuery(baseJpql).getSingleResult(); if (avg == null) { return 0; } return avg.intValue(); } public List<Fast100> top100(int maxCount, String brand, Integer buildStart, Integer buildEnd, Integer priceStart, Integer priceEnd) { String brandCondition = ""; if (!StringUtils.isEmpty(brand)) { brandCondition = " and m.brand = '" + brand + "'"; } String buildCondition = ""; if (buildEnd != null && buildStart != null) { buildCondition = " and m.build between '" + buildStart + "' and '" + buildEnd + "'"; } else { buildCondition = " and m.build != '0'"; } String priceCondition = ""; if (priceEnd != null && priceStart != null) { priceCondition = " and price between " + priceStart + " and " + priceEnd; } String jqpl = "select m.brand, m.model, m.build, m.enginesize, m.fuel, m.arrangement, count(1) as count, avg(datediff(m.dateRegisted, m.dateScraped)) as avg, avg(m.price) as price" + " from AutoscoutNl m" + " where m.repetition is null and m.dateRegisted is not null and m.enginesize != 0" + brandCondition + buildCondition + " group by m.brand, m.model, m.build, m.enginesize, m.fuel, 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() + 1); //plus 1 f.setAvgPrice(((BigDecimal) objs[i++]).intValue()); } return results; } public Sorter top100Sort(Fast100 f) { String baseJpql = "from AutoscoutNl m where m.dateRegisted is not null" + " and m.repetition is null" + " and m.brand = '" + f.getBrand() + "' and m.model = '" + f.getModel() + "' and m.build = '" + f.getBuild() + "' and m.enginesize = " + f.getEngineSize() + " and m.fuel = '" + f.getFuelType() + "' and m.arrangement = '" + f.getArrangement() + "'"; String selectSql = "select m.ownerType, m.color, m.price, m.mileage, datediff(m.dateRegisted, m.dateScraped)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() + 1); //plus 1 } Sorter sorter = new Sorter(results); sorter.sort(); return sorter; } public Page<ScoutCar> top100Detail(Pageable pageable, Fast100 f, String type, int index, String name) { String baseJpql = "from AutoscoutNl m where m.dateRegisted is not null" + " and m.repetition is null" + " and m.brand = '" + f.getBrand() + "' and m.model = '" + f.getModel() + "' and m.build = '" + f.getBuild() + "' and m.enginesize = " + f.getEngineSize() + " and m.fuel = '" + f.getFuelType() + "' and m.arrangement = '" + f.getArrangement() + "'"; if (type.equals("color")) { baseJpql += " and m.color='" + name + "'"; } else { Integer[] range = type.equals("price") ? Sorter.PRICE_RANGE : Sorter.MILEAGE_RANGE; if (index == 0) { baseJpql += " and m." + type + " < " + range[index]; } else if (index == range.length) { baseJpql += " and m." + type + " >= " + range[index - 1]; } else { baseJpql += " and m." + type + " < " + range[index]; baseJpql += " and m." + type + " >= " + range[index - 1]; } } String jpql = selectSQL + baseJpql; log4j.info(jpql); List<Object[]> list = em.createQuery(jpql, Object[].class) .setFirstResult(pageable.getPageSize() * pageable.getPageNumber()) .setMaxResults(pageable.getPageSize()).getResultList(); List<ScoutCar> results = ScoutCar.toScoutCarList(list, true); long count = em.createQuery("select count(m) " + baseJpql, Long.class).getSingleResult(); Page<ScoutCar> page = new PageImpl<ScoutCar>(results, pageable, (int) count); return page; } }