〇、场景
1、需求：调用聚水潭Sku API接口拉取数据，并存入到本地数据库。
2、技术：Spring Boot + MyBatis-Plus + MySQL
一、SDK调用说明
1、聚水潭开放平台SDK集成了API的请求封装、摘要签名，以及简单的响应解释等功能，使用SDK可以轻松完成API的调用，API结果的获取。
2、SDK只能用于API接口的调用，不能用于授权接口的调用。
通过该链接地址访问SDK详细说明，并下载SDK。链接: link，或访问聚水潭官方文档。
二、Sku商品信息API
| 环境 | Http请求地址 |
|---|---|
| 正式环境 | https://openapi.jushuitan.com/open/sku/query |
| 测试环境 | https://dev-api.jushuitan.com/open/sku/query |
三、将下载后的SDK Jar包添加至项目中
打开 File 菜单，点击 Project Structure，点击 Modules，点击 Dependencies，点击 + 号，选择
JARs or Directories...，找到下载SDK Jar包，点击OK添加成功
四、上代码
1、pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.4.1</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>2.0</version>
</dependency>
<dependency>
<groupId>io.swagger</groupId>
<artifactId>swagger-annotations</artifactId>
<version>1.5.13</version>
</dependency>
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
2、MySQL数据表
-- 聚水潭Access_Token
CREATE TABLE access_token (
id BIGINT(20) PRIMARY KEY COMMENT 'id',
expires_in VARCHAR(20) comment 'access_token有效期',
access_token VARCHAR(255) comment '访问令牌',
scope VARCHAR(20) comment '固定值all',
refresh_token VARCHAR(255) comment '更新令牌',
expiration_time VARCHAR(20) comment '过期时间'
);
-- 聚水潭商品
CREATE TABLE sku (
id BIGINT(20) PRIMARY KEY COMMENT 'id',
sku_id VARCHAR(30) comment '商品编码',
i_id VARCHAR(30) comment '款式编码',
sku_name VARCHAR(100) comment '商品名称',
short_name VARCHAR(100) comment '商品简称',
properties_value VARCHAR(50) comment '颜色规格',
supplier_sku_id VARCHAR(30) comment '供应商商品编码',
supplier_i_id VARCHAR(30) comment '供应商商品款号',
item_type VARCHAR(20) comment '商品属性',
modified VARCHAR(20) comment '修改时间'
);
-- 日志
CREATE TABLE log (
id BIGINT(20) PRIMARY KEY COMMENT 'id',
requestTime VARCHAR(20) comment '请求时间',
msg VARCHAR(255) comment '日志信息'
);
3、使用MyBatis-Plus生成entity、mapper、service文件，这里entity和mapper文件就不展示出来了。
- AccessTokenSerivce.java
import com.xjmy.jushuitan.entity.AccessToken;
import com.xjmy.jushuitan.mapper.AccessTokenMapper;
import net.sf.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.text.SimpleDateFormat;
import java.util.Date;
@Service
public class AccessTokenService {
@Autowired
AccessTokenMapper accessTokenMapper;
/**
* 插入数据
* @param jsonObject JSON数据
*/
public void insertData(JSONObject jsonObject) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
AccessToken accessToken = new AccessToken();
accessToken.setAccessToken(jsonObject.getString("access_token")); //访问令牌
accessToken.setRefreshToken(jsonObject.getString("refresh_token")); //更新令牌
accessToken.setScope(jsonObject.getString("scope")); //固定值all
accessToken.setExpiresIn(jsonObject.getString("expires_in")); //access_token有效期
Long expirationTime = System.currentTimeMillis() / 1000 + Long.valueOf(jsonObject.getString("expires_in")) - 3600;
//过期时间设置提前设置1小时
Date date = new Date(expirationTime * 1000);
accessToken.setExpirationTime(sdf.format(date)); //过期时间
//保存到数据库
accessTokenMapper.insert(accessToken);
System.out.println("access_token数据保存成功!");
}
/**
* 查询AccessToken
* @return
*/
public String selectAccessToken() {
AccessToken accessToken = accessTokenMapper.selectOne(null);
return accessToken.getAccessToken();
}
/**
* 查询RefreshToken
* @return
*/
public String selectRefreshToken() {
AccessToken accessToken = accessTokenMapper.selectOne(null);
return accessToken.getRefreshToken();
}
/**
* 删除AccessToken
*/
public void deleteAccessToken() {
accessTokenMapper.delete(null);
}
/**
* 查询一条数据
*/
public AccessToken selectOne() {
AccessToken accessTokenObj = accessTokenMapper.selectOne(null);
return accessTokenObj;
}
}
- ApiService.java
import com.jushuitan.api.*;
import com.jushuitan.api.util.AuthUtil;
import com.xjmy.jushuitan.utils.OtherUtils;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* @Description: 聚水潭API
*/
@Service
public class ApiService {
@Autowired
AccessTokenService accessTokenService;
@Autowired
LogService logService;
/**
* @param url 具体API URL地址
* @param biz 参数
*/
public String requestApi(String url, String biz) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String result = "";
// 实例化client
ApiClient client = new DefaultApiClient();
// 设置超时时间
((DefaultApiClient)client).setReadTimeout(3000);
((DefaultApiClient)client).setConnectTimeout(2000);
// 替换为POP分配给应用的app_key,app_secret,accessToken等
String appKey = "";
String appSecret = "";
ApiResponse response = null; //API响应结果
// 执行接口调用
try {
// 构建请求对象
ApiRequest request = new ApiRequest.Builder(url, appKey, appSecret).biz(biz).build();
// 查询数据库中accessToken
String accessToken = accessTokenService.selectAccessToken();
if (!StringUtils.isEmpty(accessToken)) {
response = client.execute(request, accessToken);
JSONObject responseJson = JSONObject.fromObject(response.getBody());
// 判断该accessToken是否失效，失效则调用refreshToken()刷新accessToken
if (responseJson.getInt("code") == 100) {
// 获取存在数据库中的 refreshToken
String refreshToken = accessTokenService.selectRefreshToken();
// 构建获取AccessToken请求对象
AuthRequest authRequest = new AuthRequest.Builder(appKey,appSecret).code(OtherUtils.get6UUID()).refreshToken(refreshToken).build();
// 刷新AccessToken
String accessTokenInfo = AuthUtil.refreshToken(authRequest);
JSONObject accessTokenJson = JSONObject.fromObject(accessTokenInfo);
if ("0".equals(accessTokenJson.getString("code"))) {
String data = accessTokenJson.getString("data");
JSONObject dataJson = JSONObject.fromObject(data);
accessToken = dataJson.getString("access_token");
// 清空access_token表数据
accessTokenService.deleteAccessToken();
// 重新新增数据
accessTokenService.insertData(dataJson);
}
response = client.execute(request, accessToken);
}
} else {
AuthRequest authRequest = new AuthRequest.Builder(appKey,appSecret).code(OtherUtils.get6UUID()).build();
String initAccessToken = AuthUtil.getInitToken(authRequest);
JSONObject initAccessTokenJson = JSONObject.fromObject(initAccessToken);
if ("0".equals(initAccessTokenJson.getString("code"))) {
String data = initAccessTokenJson.getString("data");
JSONObject dataJson = JSONObject.fromObject(data);
accessToken = dataJson.getString("access_token");
// 重新新增数据
accessTokenService.insertData(dataJson);
}
response = client.execute(request, accessToken);
}
result = response.getBody();
} catch (Exception e) {
String requestTime = sdf.format(new Date());
String msg = e.getMessage();
logService.insertData(requestTime,msg);
}
return result;
}
}
- SkuService.java
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.xjmy.jushuitan.entity.Sku;
import com.xjmy.jushuitan.mapper.SkuMapper;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
@Slf4j
@Service
public class SkuService {
@Autowired
SkuMapper skuMapper;
@Autowired
ApiService apiService;
@Autowired
LogService logService;
/**
* 普通SKU商品API同步
* @param modified_begin 起始修改时间
* @param modified_end 截止修改时间
*/
public void skuApiThread(String modified_begin,String modified_end) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String url = "https://openapi.jushuitan.com/open/sku/query";// 正式地址
Integer page_index = 1;
Integer page_size = 50;
//JSON参数
JSONObject paramsJson = new JSONObject();
paramsJson.put("page_index",page_index); //页数
paramsJson.put("page_size",page_size);//每页多少条数据
paramsJson.put("modified_begin",modified_begin);//修改开始时间
paramsJson.put("modified_end",modified_end);//修改结束时间
String biz = paramsJson.toString();
try {
// 获取API接口返回报文
String apiResult = apiService.requestApi(url,biz);
if (!StringUtils.isEmpty(apiResult)) {
// 将报文字符串转成JSON
JSONObject resultJson = JSONObject.fromObject(apiResult);
log.info("同步Sku商品信息任务启动，执行时间为：{}", LocalDateTime.now());
// 判断该API返回报文结果是否是调用API成功
if ("执行成功".equals(resultJson.getString("msg")) && resultJson.getInt("code") == 0){
// 解析第一层data
String dataStr = resultJson.getString("data");
JSONObject dataJson = JSONObject.fromObject(dataStr);
// 解析第二层datas
String datasStr = dataJson.getString("datas");
JSONArray dataArrays = JSONArray.fromObject(datasStr);
for (int i = 0;i < dataArrays.size();i++){
JSONObject skuJson = dataArrays.getJSONObject(i);
System.out.println("sku商品"+i+"=== sku_id:"+skuJson.getString("sku_id")+" sku_name:"+skuJson.getString("name"));
// 查询该商品id是否存在
QueryWrapper<Sku> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("sku_id",skuJson.getString("sku_id"));
Sku sku = skuMapper.selectOne(queryWrapper);
if (sku != null) {
// 执行更新
updateSkuData(sku,skuJson);
} else {
// 执行新增
insertSkuData(skuJson);
}
}
} else {
log.info("同步Sku商品信息任务失败，错误信息：{}", resultJson.getString("msg"));
String requestTime = sdf.format(new Date());
String msg = "同步Sku商品信息任务失败，错误信息："+ resultJson.getString("msg");
logService.insertData(requestTime,msg);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 新增普通SKU数据
* @param skuJson JSON数据
*/
public void insertSkuData(JSONObject skuJson) {
Sku newSku = new Sku();
newSku.setSkuId(skuJson.getString("sku_id"));
newSku.setIId(skuJson.getString("i_id"));
newSku.setSkuName(skuJson.getString("name"));
newSku.setShortName(skuJson.getString("short_name"));
newSku.setPropertiesValue(skuJson.getString("properties_value"));
newSku.setSupplierSkuId(skuJson.getString("supplier_sku_id"));
newSku.setSupplierIId(skuJson.getString("supplier_i_id"));
newSku.setItemType(skuJson.getString("item_type"));
newSku.setModified(skuJson.getString("modified"));
skuMapper.insert(newSku);
}
/**
* 修改普通SKU数据
* @param sku 商品SKU
* @param skuJson JSON数据
*/
public void updateSkuData(Sku sku,JSONObject skuJson) {
UpdateWrapper<Sku> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("sku_id",sku.getSkuId());
sku.setSkuName(skuJson.getString("name"));
sku.setShortName(skuJson.getString("short_name"));
sku.setPropertiesValue(skuJson.getString("properties_value"));
sku.setSupplierSkuId(skuJson.getString("supplier_sku_id"));
sku.setSupplierIId(skuJson.getString("supplier_i_id"));
sku.setItemType(skuJson.getString("item_type"));
sku.setModified(skuJson.getString("modified"));
skuMapper.update(sku, updateWrapper);
}
- Test.java
/**
- 测试SKU商品API
*/
@Test
void testSkuApi() {
String startDate = "2024-03-04 00:00:00";
String endDate = "2024-03-04 23:59:00";
// 普通商品
skuService.skuApiThread(startDate, endDate);
}
- 返回结果
sku商品0=== sku_id:SX-ZT-2000-1 sku_name:猪蹄
sku商品1=== sku_id:SX-WHR-2000-1 sku_name:五花肉
sku商品2=== sku_id:SX-TZG-2000-1 sku_name:筒子骨
sku商品3=== sku_id:SX-JPLP-2000-1 sku_name:精品肋排
sku商品4=== sku_id:SX-RGLP-2000-1 sku_name:带软骨肋排
sku商品5=== sku_id:SX-LPK-2000-1 sku_name:肋排块
本人也是初次调用聚水潭API，代码并不完美，提供参考。
扫一扫
2625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
