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.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.Products;
import com.uas.search.model.Property;
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.HashSet;
import java.util.List;
import java.util.Set;
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 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, true);
        }
        // Product索引
        if (PRODUCTS_PRIVATE_TABLE_NAME.equals(tableName)) {
            data = queryVProducts(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, boolean needProperties) {
        List<Component> components = 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());
        String propertyValueSql = "select * from product$propertyvalue left join product$property on product$propertyvalue.pv_propertyid = product$property.pt_id where pv_componentid = ";
        if (!CollectionUtils.isEmpty(components) && needProperties) {
            for (Component component : components) {
                    component.setProperties(new HashSet<>(jdbcTemplate.query(propertyValueSql + component.getId(), new PropertyValue())));
            }
        }
        return (List<T>) components;
    }

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

    /**
     * 获取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;
    }
}