关于生成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;    }