# ProductSpecInfoDaoImpl.java

    package com.mycim.server.spec.dao.impl;

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.server.spec.dao.ProductSpecInfoDao;
    import com.mycim.server.spec.dao.mapper.ProductSpecInfoRowMapper;
    import com.mycim.valueobject.prp.ProductSpecInfo;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.util.ArrayList;
    import java.util.List;

    /**
     * @author Qiansheng.Wang
     * @since 2020-10-26
     */
    @Repository
    public class ProductSpecInfoDaoImpl implements ProductSpecInfoDao {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public void insertProductSpecInfos(List<ProductSpecInfo> productSpecInfos) {
            StringBuilder sql = new StringBuilder("insert into ");

            sql.append(" product_spec_item_info ");
            sql.append(" (PRODUCT_RRN, PRODUCT_ID, PRODUCT_VERSION, PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ");
            sql.append(" ACTIVE_FLAG, ACTIVE_TIME, ACTIVE_USER, CURRENT_STATUS, LAST_UPDATED_USER, LAST_UPDATED_TIME, ");
            sql.append(" CREATE_USER_ID, CREATE_TIME) ");
            sql.append(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

            List<Object[]> batchArgs = new ArrayList<>();
            for (ProductSpecInfo productSpecInfo : productSpecInfos) {
                batchArgs
                        .add(new Object[]{productSpecInfo.getProductRrn(), productSpecInfo.getProductId(),
                                          productSpecInfo.getProductVersion(), productSpecInfo.getProcessRrn(),
                                          productSpecInfo.getProcessId(), productSpecInfo.getProcessVersion(),
                                          productSpecInfo.getActiveFlag(), productSpecInfo.getActiveTime(),
                                          productSpecInfo.getActiveUser(), productSpecInfo.getCurrentStatus(),
                                          productSpecInfo.getLastUpdatedUser(), productSpecInfo.getLastUpdatedTime(),
                                          productSpecInfo.getCreateUserId(), productSpecInfo.getCreateTime()});
            }

            jdbcTemplate.batchUpdate(sql.toString(), batchArgs);
        }

        @Override
        public void updateProductSpecInfos(List<ProductSpecInfo> productSpecInfos) {
            StringBuilder sql = new StringBuilder("update ");

            sql.append(" PRODUCT_SPEC_ITEM_INFO ");
            sql.append(" SET ACTIVE_FLAG = ?, ACTIVE_TIME = ?, ACTIVE_USER = ?, ");
            sql.append(" CURRENT_STATUS = ?, LAST_UPDATED_USER = ?, LAST_UPDATED_TIME = ? ");
            sql.append(" WHERE PRODUCT_RRN = ? AND PRODUCT_VERSION = ? AND PROCESS_RRN = ? AND PROCESS_VERSION = ? ");

            List<Object[]> batchArgs = new ArrayList<>();
            for (ProductSpecInfo productSpecInfo : productSpecInfos) {
                batchArgs
                        .add(new Object[]{productSpecInfo.getActiveFlag(), productSpecInfo.getActiveTime(),
                                          productSpecInfo.getActiveUser(), productSpecInfo.getCurrentStatus(),
                                          productSpecInfo.getLastUpdatedUser(), productSpecInfo.getLastUpdatedTime(),
                                          productSpecInfo.getProcessRrn(), productSpecInfo.getProcessVersion(),
                                          productSpecInfo.getProductRrn(), productSpecInfo.getProductVersion()});
            }

            jdbcTemplate.batchUpdate(sql.toString(), batchArgs);
        }

        @Override
        public ProductSpecInfo getProductSpecInfoByPrimaryKey(Long productRrn, Integer productVersion, Long processRrn,
                                                              Integer processVersion) {
            StringBuilder sql = buildSelectAllSqlStringBuilder();

            sql.append(" where PRODUCT_RRN = ? and PRODUCT_VERSION = ? and PROCESS_RRN = ? and PROCESS_VERSION = ? ");

            return jdbcTemplate
                    .queryForObjectWithNull(sql.toString(), new ProductSpecInfoRowMapper(), productRrn, productVersion,
                                            processRrn, processVersion);
        }

        @Override
        public ProductSpecInfo getLatestActivatedProductSpecInfo(Long productRrn, Long processRrn) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" PRODUCT_RRN, PRODUCT_ID, PRODUCT_VERSION, PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ");
            sql.append(" ACTIVE_FLAG, ACTIVE_TIME, ACTIVE_USER, CURRENT_STATUS, LAST_UPDATED_USER, LAST_UPDATED_TIME, ");
            sql.append(" CREATE_USER_ID, CREATE_TIME  from (");
            sql.append(this.buildSelectAllSqlStringBuilder());
            sql.append(" WHERE PRODUCT_RRN = ? AND PROCESS_RRN = ? AND ACTIVE_FLAG = ? ");
            sql.append(" ORDER BY PROCESS_VERSION DESC, PRODUCT_VERSION DESC ) ");
            sql.append(" WHERE ROWNUM = 1 ");

            return jdbcTemplate
                    .queryForObjectWithNull(sql.toString(), new ProductSpecInfoRowMapper(), productRrn, processRrn, true);
        }

        @Override
        public List<ProductSpecInfo> getProductSpecInfosByProcess(Long processRrn, Integer processVersion) {
            StringBuilder sql = buildSelectAllSqlStringBuilder();

            sql.append(" where PROCESS_RRN = ? and PROCESS_VERSION = ? ");
            sql.append(" order by PRODUCT_ID, PRODUCT_VERSION ");

            return jdbcTemplate.query(sql.toString(), new ProductSpecInfoRowMapper(), processRrn, processVersion);
        }

        @Override
        public List<ProductSpecInfo> getAllActivatedProductSpecInfo(Long productRrn, Long processRrn) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" PRODUCT_RRN, PRODUCT_ID, PRODUCT_VERSION, PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ");
            sql.append(" ACTIVE_FLAG, ACTIVE_TIME, ACTIVE_USER, CURRENT_STATUS, LAST_UPDATED_USER, LAST_UPDATED_TIME, ");
            sql.append(" CREATE_USER_ID, CREATE_TIME  from (");
            sql.append(this.buildSelectAllSqlStringBuilder());
            sql.append(" WHERE PRODUCT_RRN = ? AND PROCESS_RRN = ? AND ACTIVE_FLAG = ? ");
            sql.append(" ORDER BY PROCESS_VERSION DESC, PRODUCT_VERSION DESC ) ");

            return jdbcTemplate.query(sql.toString(), new ProductSpecInfoRowMapper(), productRrn, processRrn, true);
        }

        private StringBuilder buildSelectAllSqlStringBuilder() {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" PRODUCT_RRN, PRODUCT_ID, PRODUCT_VERSION, PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ");
            sql.append(" ACTIVE_FLAG, ACTIVE_TIME, ACTIVE_USER, CURRENT_STATUS, LAST_UPDATED_USER, LAST_UPDATED_TIME, ");
            sql.append(" CREATE_USER_ID, CREATE_TIME ");
            sql.append(" from product_spec_item_info ");

            return sql;
        }

        @Override
        public List<Integer> getAllActivatedProcessVersions(Long productRrn, Long processRrn) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" PROCESS_VERSION ");
            sql.append(" from product_spec_item_info ");
            sql.append(" where PRODUCT_RRN = ? and PROCESS_RRN = ? and ACTIVE_FLAG = ? ");

            return jdbcTemplate.query(sql.toString(), new Object[]{productRrn, processRrn, true}, Integer.class);
        }

    }