上一篇我们讲了如何将商品采集到excel,现在我们通过脚本程序一键将excel商品导入到我们自己的网站。
一 商品信息准备
读取excel里的商品信息
ReadExcelUtils excelReader = new ReadExcelUtils(EXCEL_PATH);
Map<Integer, Map<Integer, Object>> map = excelReader.readExcelContent();
System.out.println("获得Excel表格的内容:"+map.size());
for (int i = fromIndex; i <= map.size(); i++) {
initDB(conn, lastId, Objects.requireNonNull(map.get(i)));
}
其中ReadExcelUtils 是我封装的一个读取excel的工具类
这样我们就把excel的数据,读到map里了。看下效果
这个是excel的数据
这个是读取结果。可以进行下一步,将数据导入我们的数据库。
二 将读取的数据插入到数据库
这里使用的JDBC连接远程服务器的数据库,然后就是开始操作我们的数据库里的各种表了
1 插入商品基本信息-表名oc_product
SQL 、插入代码 如下
public class Product {
String sql ="INSERT INTO oc_product (product_id, model, sku, upc, ean, jan, isbn, mpn, location, quantity, stock_status_id, image, manufacturer_id, shipping, price, points, tax_class_id, date_available, weight, weight_class_id, length, width, height, length_class_id, subtract, minimum, sort_order, status, viewed, date_added, date_modified)" +
" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
public static String TABLE_NAME = "oc_product";
String folder;
public Product(int product_id, String model, String sku, float price,String folder) {
this.product_id = product_id;
this.model = model;
this.sku = sku;
this.price = price;
this.folder = folder;
this.image = FileUtils.getMainImageFromFolder(folder);
}
public int product_id;//
public String model;//
public String sku;
public String upc="";
public String ean="";
public String jan="";
public String isbn="";
public String mpn="";
public String location="";
public int quantity = 10000;
public int stock_status_id = 6;
public String image="";
public int manufacturer_id;
public int shipping = 1;
public float price;//
public int points;
public int tax_class_id;
public Date date_available =new Date(System.currentTimeMillis());
public float weight;
public int weight_class_id = 1;
public float length;
public float width;
public float height;
public int length_class_id = 1;
public int subtract = 1;
public int minimum =1;
public int sort_order =1 ;
public int status =1;
public int viewed ;
public Timestamp date_added = new Timestamp(System.currentTimeMillis());
public Timestamp date_modified = new Timestamp(System.currentTimeMillis());
public void insert(Connection conn){
try {
PreparedStatement pstm =conn.prepareStatement(sql);
pstm.setInt(1,product_id);
pstm.setString(2,model);
pstm.setString(3,sku);
pstm.setString(4,upc);
pstm.setString(5,ean);
pstm.setString(6,jan);
pstm.setString(7,isbn);
pstm.setString(8,mpn);
pstm.setString(9,location);
pstm.setInt(10,quantity);
pstm.setInt(11,stock_status_id);
pstm.setString(12,image);
pstm.setInt(13,manufacturer_id);
pstm.setInt(14,shipping);
pstm.setFloat(15,price);
pstm.setInt(16,points);
pstm.setInt(17,tax_class_id);
pstm.setDate(18,date_available);
pstm.setFloat(19,weight);
pstm.setInt(20,weight_class_id);
pstm.setFloat(21,length);
pstm.setFloat(22,width);
pstm.setFloat(23,height);
pstm.setInt(24,length_class_id);
pstm.setInt(25,subtract);
pstm.setInt(26,minimum);
pstm.setInt(27,sort_order);
pstm.setInt(28,status);
pstm.setInt(29,viewed);
pstm.setTimestamp(30,date_added);
pstm.setTimestamp(31,date_modified);
pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
这里一些字段是固定的,变化的字段从excel读出来放到具体的位置。
2 商品主图、附图-表oc_product_image
public class ProductImage {
private static final String SQL = "INSERT INTO oc_product_image (product_image_id, product_id, image, sort_order) VALUES (?,?,?,?)";
public int product_id;
public String folder;
public ProductImage(int product_id, String folder) {
this.product_id = product_id;
this.folder = folder;
}
public void insert(Connection conn){
List<String> images = FileUtils.getSubMainImagesFromFolder(folder);
if (images == null || images.size() == 0){
return;
}
for (int i = 0; i < images.size(); i++) {
String image = images.get(i);
try {
PreparedStatement pstm = conn.prepareStatement(SQL);
pstm.setInt(1, DBUtils.getLastProjectImageId(conn) + 1);
pstm.setInt(2, product_id);
pstm.setString(3, image);
pstm.setInt(4, i);
pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3 产品描述-表oc_product_description
public class ProductDesc {
private static final String SQL = "INSERT INTO oc_product_description (product_id, language_id, name, description, tag, meta_title, meta_description, meta_keyword) VALUES (?,?,?,?,?,?,?,?);";
public int product_id;
public int language_id;// 1 eng 2 cn
public String name;
public String description;
public String tag;
public String meta_title;
public String meta_description;
public String meta_keyword;
public ProductDesc(String folder,int product_id, int language_id, String name, String description, String tag, String meta_description) {
this.product_id = product_id;
this.language_id = language_id;
this.name = name;
this.description = description;
this.tag = tag;
this.meta_title = name;
this.meta_description = meta_description;
this.meta_keyword = name;
this.description = FileUtils.generateDesc(folder);
}
public void insert(Connection conn){
try {
PreparedStatement pstm =conn.prepareStatement(SQL);
pstm.setInt(1,product_id);
pstm.setInt(2,language_id);
pstm.setString(3,name);
pstm.setString(4,description);
pstm.setString(5,tag);
pstm.setString(6,meta_title);
pstm.setString(7,meta_description);
pstm.setString(8,meta_keyword);
pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
4 产品分类-表oc_product_to_category
public class Category {
public static String TABLE_NAME = "oc_product_to_category";
public static final String SQL = "INSERT INTO oc_product_to_category (product_id, category_id) VALUES (?,?);";
public int product_id;
public int category_id = 59;
public Category(int product_id, int category_id) {
this.product_id = product_id;
this.category_id = category_id;
}
public void insert(Connection conn){
try {
PreparedStatement pstm =conn.prepareStatement(SQL);
pstm.setInt(1,product_id);
pstm.setInt(2,category_id);
pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5 选项(选项这个操作稍微复杂些,涉及4张表,我们下节分享)
感兴趣的同学可以去我的自建站看下网站效果