search-parent/mall-search/src/main/java/com/uas/search/service/impl/JdbcServiceImpl.java

package com.uas.search.service.impl;

import static com.uas.search.constant.SearchConstants.PCB_TABLE_NAME;
import static com.uas.search.constant.SearchConstants.PRODUCTS_PRIVATE_TABLE_NAME;
import static com.uas.search.constant.SearchConstants.PRODUCTS_USERS_TABLE_NAME;
import static com.uas.search.constant.SearchConstants.TRADE_GOODS_TABLE_NAME;

import com.uas.search.constant.SearchConstants;
import com.uas.search.dao.GoodsDao;
import com.uas.search.model.Brand;
import com.uas.search.model.Component;
import com.uas.search.model.Kind;
import com.uas.search.model.PCB;
import com.uas.search.model.ProductUsersSimpleInfo;
import com.uas.search.model.Products;
import com.uas.search.model.PropertyValue;
import com.uas.search.model.TradeGoods;
import com.uas.search.model.V_Products;
import com.uas.search.service.JdbcService;
import com.uas.search.util.CollectionUtils;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.stream.Collectors;
import javax.persistence.Column;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 *
 * Created by wangyc on 2018/6/11.
 *
 * @version 2018/6/11 15:47 wangyc
 * @param <T>
 */
@Service
public class JdbcServiceImpl<T> implements JdbcService{

    private final JdbcTemplate jdbcTemplate;

    private final GoodsDao goodsDao;

    @Autowired
    public JdbcServiceImpl(JdbcTemplate jdbcTemplate, GoodsDao goodsDao) {
        this.jdbcTemplate = jdbcTemplate;
        this.goodsDao = goodsDao;
    }

    @Override
    public Long getTotalElements(String tablename) {
        return jdbcTemplate.queryForObject(String.format("select count(1) from %s", tablename), Long.class);
    }

    @Override
    public Long getMaxId(String tableName, String idColumn) {
        return jdbcTemplate.queryForObject(String.format("select max(%s) from %s", idColumn, tableName), Long.class);
    }

    @Override
    public Long getMinId(String tableName, String idColumn) {
        return jdbcTemplate.queryForObject(String.format("select max(%s) from %s", idColumn, tableName), Long.class);
    }

    @Override
    public List<Long> getLimitId(String tablename, String direction, String sortField, Long start, Integer size) {
        List<Long> ids = new ArrayList<>();

        Long startId = jdbcTemplate.queryForObject(String.format("select %s from %s order by %s limit %s,1", sortField,
            tablename, sortField, start), Long.class);
        ids.add(startId);
        try {
            Long endId = jdbcTemplate.queryForObject(String.format("select %s from %s order by %s limit %s,1", sortField,
                tablename, sortField, start + size - 1), Long.class);
            ids.add(endId);
        } catch (EmptyResultDataAccessException e) {
            Long endId = jdbcTemplate.queryForObject(String.format("select max(%s) from %s", sortField,
                tablename, sortField, start + size - 1), Long.class);
            ids.add(endId);
            ids.add(1L);
        }

        return ids;
    }

    @Override
    public String abstractTransform(Class className, String property) throws NoSuchFieldException {
        Field id = className.getDeclaredField("id");
        Annotation annotation = id.getAnnotation(Column.class);
        return ((Column) annotation).name();
    }

    @Override
    public List<T> getData(String tableName, Long startId, Long endId, String sortField, String direct) {
        String sql = String.format("select * from %s where %s between %d and %d order by %s %s", tableName, sortField,
            startId, endId, sortField, direct);
        List<T> data = null;
        // 器件索引
        if (SearchConstants.COMPONENT_TABLE_NAME.equals(tableName)) {
            data = queryComponents(sql, startId, endId, true);
        }
        // Product索引
        if (PRODUCTS_PRIVATE_TABLE_NAME.equals(tableName)) {
            data = queryVProducts(sql);
        }
        if (PRODUCTS_USERS_TABLE_NAME.equals(tableName)) {
            data = queryProductUser(sql);
        }
        // Goods索引
        if (TRADE_GOODS_TABLE_NAME.equals(tableName)) {
            data = queryGoods(sql);
        }

        // PCB索引
        if (PCB_TABLE_NAME.equals(tableName)) {
            data = queryPCB(sql);
        }
        return data;
    }

    /**
     * 获取Component
     * @param sql sql
     * @param needProperties 是否需要获取属性值
     * @return
     */
    private List<T> queryComponents(String sql, Long startId, Long endId, boolean needProperties) {
        List<Component> components = new ArrayList<>();
        if (needProperties) {
            String propertyValueSql = "select product$component.cmp_id,product$component.cmp_uuid,product$component.cmp_code,product$component.cmp_description,product$component.cmp_search_count,product$component.cmp_visit_count,product$component.searchweight,product$component.cmp_kiid,product$component.cmp_brid,product$kind.ki_id,product$kind.ki_name,product$kind.ki_level,product$kind.ki_isleaf,product$kind.ki_visit_count,product$kind.ki_search_count,product$brand.br_id,product$brand.br_uuid,product$brand.br_name_cn,product$brand.br_name_en,product$brand.br_visit_count,product$brand.br_search_count,product$brand.br_weight,product$propertyvalue.pv_id,product$propertyvalue.pv_propertyid,product$propertyvalue.pv_value,product$propertyvalue.pv_componentid,product$propertyvalue.pv_detno,product$property.pt_id,product$property.pt_label from product$component left join product$propertyvalue on product$propertyvalue.pv_componentid = product$component.cmp_id left join product$property on product$propertyvalue.pv_propertyid = product$property.pt_id left join product$kind on product$component.cmp_kiid = product$kind.ki_id left join product$brand on product$component.cmp_brid = product$brand.br_id where product$component.cmp_id between %d and %d";
            List<Component> componentList = jdbcTemplate.query(String.format(propertyValueSql, startId, endId), new Component());
            Map<Long, List<Component>> componentMap = componentList.stream().collect(Collectors.groupingBy(Component :: getId));
            for (Entry<Long, List<Component>> entery : componentMap.entrySet()) {
                List<Component> componentLists = componentMap.get(entery.getKey());
                Component finalComponent = componentLists.get(0);
                Set<PropertyValue> propertyValues = componentLists.stream().filter(
                    c -> c.getPropertyValue() != null).map(c -> c.getPropertyValue()).collect(Collectors.toSet());
                finalComponent.setProperties(propertyValues);
                finalComponent.setPropertyValue(null);
                components.add(finalComponent);
            }
            return (List<T>)components;
        } else {
            return jdbcTemplate.query(sql.replace("where", "left join product$brand on product$brand.br_id=product$component.cmp_brid left join product$kind on product$kind.ki_id = product$component.cmp_kiid where"), new Component());
        }
    }

    /**
     * 获取V_Products
     * @param sql sql
     * @return
     */
    private List<T> queryVProducts(String sql) {
        return jdbcTemplate.query(sql, new V_Products());
    }

    private List<T> queryProductUser(String sql) {
        return jdbcTemplate.query(sql.replace("where", "left join v$product$private on product$users.pu_prid = v$product$private.pr_id where"), new ProductUsersSimpleInfo());
    }

    /**
     * 获取Goods
     * @param sql sql
     * @return
     */
    private List<T> queryGoods(String sql) {
        return jdbcTemplate.query(sql, new TradeGoods());
    }

    /**
     * 下载pcb
     * @param sql sql
     * @return
     */
    private List<T> queryPCB(String sql) {
        List<PCB> pcbs = jdbcTemplate.query(sql, new PCB());
        if (!CollectionUtils.isEmpty(pcbs)) {
            String kindSql = "select * from product$kind where ki_id = ";
            String brandSql = "select * from product$brand where br_id = ";
            String productSql = "select * from products where pr_id = ";
            for (PCB pcb : pcbs) {
                if (pcb.getKindId() != null) {
                    List<Kind> kinds = jdbcTemplate.query(kindSql + pcb.getKindId(), new Kind());
                    if (!CollectionUtils.isEmpty(kinds)) {
                        pcb.setKind(kinds.get(0));
                    }
                }

                if (pcb.getBrandId() != null) {
                    List<Brand> brands = jdbcTemplate.query(brandSql + pcb.getBrandId(), new Brand());
                    if (!CollectionUtils.isEmpty(brands)) {
                        pcb.setBrand(brands.get(0));
                    }
                }

                if (pcb.getProductId() != null) {
                    List<Products> productses = jdbcTemplate.query(productSql + pcb.getProductId(), new Products());
                    if (!CollectionUtils.isEmpty(productses)) {
                        pcb.setProducts(productses.get(0));
                    }
                }
            }
        }
        return (List<T>) pcbs;
    }
}