关于生成POS端sqlLiteDB文件 实现全量同步

 // 零售商商品列表
        List<RetailerSkuCmmdtyEntity> cmmdtyList = null;
        // 商品规格表
        List<CmmdtyPropertyEntity> properties = null;
        // 获取商品
                cmmdtyList = retailerSkuCmmdtyBusiness.queryCmmdtysByMerCustNo(merchantCustNo);
                // 获取商品规格
                properties = cmmdtyPropertyBusiness.queryCmmdtyPropertiesByMerCustNo(merchantCustNo);
  Map<String, Object> param = new HashMap<String, Object>();
param.put("cmmdtyList", cmmdtyList);
param.put("properties ", properties );
createPosCmmdtyDbFile(merchantCustNo, storeCode, param);

private String createPosCmmdtyDbFile(String merchantCustNo, String storeCode, Map<String, Object> param) {
        String uploadPath = StringUtils.EMPTY;
        int status = FAIL;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String filePath = System.getProperty("user.dir") + File.separator + "pos";
        String date = df.format(new Date());
        createDir(filePath);
        String dbFileName = "product_" + storeCode + "_" + date + ".db";
        LOGGER.debug("Start零售商:{},店铺:{},DB文件本地存储路径:{}", merchantCustNo, storeCode, filePath + File.separator
                + dbFileName);
        Connection conn = SqliteConnectionUtils.getConnection("jdbc:sqlite:" + File.separator + filePath
                + File.separator + dbFileName);
        try {
            Statement stat = conn.createStatement();
            // 建表
            status = createTables(stat);
            if (status == SUCCESS) {
                // 添加商品信息
                status = insertProductInfo(conn, param);
            }


            if (status == SUCCESS) {
                // 添加商品属性信息
                status = insertPropertyInfo(conn, param);
            }
            if (status == SUCCESS) {
                // 添加商品属性值信息
                status = insertPropertyValueInfo(conn, param);
            }

            // 生成DB文件成功则上传DB文件
            if (status == SUCCESS) {
                OssFileUploadUtil oss = new OssFileUploadUtil();
                uploadPath = oss.uploadOssFile(filePath, dbFileName);
            }
        } catch (SQLException e) {
            LOGGER.error("PosCmmdtyBusiness.createPosCmmdty error,", e);
        } finally {
            LOGGER.debug("End零售商:{},店铺:{},DB文件OSS存储路径:{}", merchantCustNo, storeCode, uploadPath);
            SqliteConnectionUtils.closeConnection(conn);
            deleteFile(filePath + File.separator + dbFileName);
        }
        return uploadPath;
    }

sqlLite 工具类

public class SqliteConnectionUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(SqliteConnectionUtils.class);

    private SqliteConnectionUtils() {

    }

    static {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            LOGGER.error("SqliteConnectionUtil error,", e);
        }
    }

    /**
     * 功能描述: 获取SQLite数据库连接<br>
     * 〈功能详细描述〉 获取SQLite数据库连接
     *
     * @param connName 连接名称
     * @return Connection
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static Connection getConnection(String connName) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(connName);
        } catch (SQLException e) {
            LOGGER.error("SqliteConnectionUtils.getConnection error,", e);
        }
        return conn;
    }

    /**
     * 功能描述: 关闭SQLite数据库连接<br>
     * 〈功能详细描述〉 关闭SQLite数据库连接
     *
     * @param Connection 数据库连接
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static void closeConnection(Connection conn) {
        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("SqliteConnectionUtils.closeConnection error,", e);
            }
        }
    }
}

private boolean createDir(String filePath) {
        File dir = new File(filePath);
        // 判断目录是否存在
        if (dir.exists()) {
            return false;
        }
        // 创建目标目录
        if (dir.mkdirs()) {
            return true;
        } else {
            return false;
        }
    }

private int createTables(Statement stat) {
        try {
            stat.executeUpdate("drop table if exists Brand;");
            stat.executeUpdate("drop table if exists Category;");
            stat.executeUpdate("drop table if exists Product;");
            stat.executeUpdate("drop table if exists Product_Category_Relation;");
            stat.executeUpdate("drop table if exists Product_Property;");
            stat.executeUpdate("drop table if exists Product_Property_Value;");
            stat.executeUpdate("drop table if exists Sku_Property_Relation;");
            // 建表语句执行
            stat.executeUpdate("create table Brand (brandCode text not null,brandName text,brandImage text,primary key (brandCode));");
            stat.executeUpdate("create table Category(categoryCode text not null,categoryName text,categoryEng text,manageInventory text, primary key (categoryCode));");
            stat.executeUpdate("create table Product(productCode text not null," + "skuProductCode text,"
                    + "spuProductCode text," + "productName text," + "productType text," + "barcode text,"
                    + "brandName text," + "brandCode text," + "unit text," + "productSource text,"
                    + "deliveryTime text," + "imageSeq text," + "imageIndex text," + "imageUrl text,"
                    + "imageSource text," + "retrailPrice text," + "managerPrice text," + "emplPrice text,"
                    + "retailPriceLowest text," + "inventory text," + "salesCount text,"
                    + "primary key (productCode));");
            stat.executeUpdate("create table Product_Category_Relation(id bigint not null,productCode text,categoryCode text,primary key (id));");
            stat.executeUpdate("create table Product_Property(propertyId text not null,propertyCode text,spuProductCode text,propertyName text,showLevel text,primary key (propertyId));");
            stat.executeUpdate("create table Product_Property_Value(valueId text not null,valueName text not null,showLevel text,propertyId text,primary key (valueId));");
            stat.executeUpdate("create table Sku_Property_Relation(id bigint not null,skuProductCode text,propertyId text,valueId text,primary key (id));");
        } catch (SQLException e) {
            LOGGER.error("PosCmmdtyBusiness.createTables error,", e);
            return FAIL;
        }
        return SUCCESS;
    }

创建数据

@SuppressWarnings("unchecked")
    private int insertProductInfo(Connection conn, Map<String, Object> param) {
        // 零售商商品列表获取
        List<RetailerSkuCmmdtyEntity> cmmdtyList = (List<RetailerSkuCmmdtyEntity>) param.get("cmmdtyList");
        // 有商品数据
        if (CollectionUtils.isEmpty(cmmdtyList)) {
            return SUCCESS;
        }
        // 商品价格获取
        List<StoreCmmdtyPriceEntity> priceList = (List<StoreCmmdtyPriceEntity>) param.get("priceList");
        // 商库存格获取
        List<InventoryEntity> inventoryList = (List<InventoryEntity>) param.get("inventoryList");
        PreparedStatement prep = null;
        try {
            String insertSql = "insert into Product (productCode,skuProductCode,spuProductCode,"
                    + "productName,barcode,brandName,brandCode,unit,productSource,deliveryTime,imageSeq,"
                    + "imageIndex,imageUrl,imageSource,retrailPrice,managerPrice,"
                    + "emplPrice,retailPriceLowest,inventory,salesCount) "
                    + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
            prep = conn.prepareStatement(insertSql);
            // 商品信息
            for (RetailerSkuCmmdtyEntity cmmdtyEntity : cmmdtyList) {
                String cmmdtyCode = cmmdtyEntity.getCmmdtyCode();
                // 商品价格获取
                StoreCmmdtyPriceEntity priceEntity = getPriceByCmmdtyCode(cmmdtyCode, priceList);
                // 商品库存获取
                InventoryEntity inventory = getInventoryByCmmdtyCode(cmmdtyCode, inventoryList);
                LOGGER.debug("插入Product数据,商品数据:{},价格数据:{},库存:{}", cmmdtyEntity, priceEntity, inventory);
                prep.setString(1, cmmdtyEntity.getCmmdtyCode());
                prep.setString(2, cmmdtyEntity.getCommonSku());
                prep.setString(3, cmmdtyEntity.getCommonSpu());
                prep.setString(4, cmmdtyEntity.getCmmdtyDisplayName());
                prep.setString(5, cmmdtyEntity.getBarCode());
                prep.setString(6, cmmdtyEntity.getBrandName());
                prep.setString(7, cmmdtyEntity.getBrandId().toString());
                prep.setString(8, cmmdtyEntity.getMeasureUnit());
                prep.setString(9, cmmdtyEntity.getSource());
                prep.setString(10, "");
                prep.setString(11, cmmdtyEntity.getImageCount() == null ? "" : cmmdtyEntity.getImageCount().toString());
                prep.setString(12, "");
                // 苏宁平台商品
                String coverImgUrl;
                if ("0".equals(cmmdtyEntity.getSource())) {
                    coverImgUrl = uimgServer + CommonUtils.getImageUrlFolder(CommonUtils.MAIN_SITE) + "0000000000-"
                            + cmmdtyEntity.getSnCmmdtyCode() + "_1_200x200.jpg";
                    prep.setString(13, coverImgUrl);
                } else {
                    coverImgUrl = uimgServer + CommonUtils.getImageUrlFolder(CommonUtils.SELF_SITE)
                            + cmmdtyEntity.getCmmdtyCode() + "_1_200x200.jpg";
                    prep.setString(13, coverImgUrl);
                }
                prep.setString(14, "");
                prep.setString(15, priceEntity.getRetailPrice() == null ? "" : priceEntity.getRetailPrice().toString());
                prep.setString(16, priceEntity.getStoreMgrPrice() == null ? "" : priceEntity.getStoreMgrPrice()
                        .toString());
                prep.setString(17, priceEntity.getEmplPrice() == null ? "" : priceEntity.getEmplPrice().toString());
                prep.setString(18, priceEntity.getRetailPriceLowest() == null ? "" : priceEntity.getRetailPriceLowest()
                        .toString());
                prep.setString(19, inventory.getQuantity() == null ? "" : inventory.getQuantity().toString());
                prep.setString(20, "");
                prep.addBatch();
            }
            prep.executeBatch();
        } catch (SQLException e) {
            LOGGER.error("PosCmmdtyBusiness.insertProductInfo error,", e);
            return FAIL;
        } finally {
            if (prep != null) {
                try {
                    prep.close();
                } catch (SQLException e) {
                    LOGGER.error("PosCmmdtyBusiness.insertProductInfo error,", e);
                }
            }
        }
        return SUCCESS;
    }