A, requirements,
We cannot access the customer’s database through the account password, but we can access the interface provided by the customer to obtain data. Therefore, we can request the interface to parse the returned JSON data, and then we can get the data we need.
-
You can configure required information in a configuration file to enhance flexibility
-
The requested data is updated if it exists in the database and inserted if it does not
-
If a certain data in JSON does not exist, the data is automatically completed, set to null, and stored in the database
-
If the returned data has a timestamp, it needs to be parsed and saved to the database
-
Use HTTP connection pooling to reduce the overhead of frequent disconnection
Second, the train of thought
Kit:
Httpclient: sends HTTP requests
JsonPath: Used to parse strings
HttpClient tool use
Introducing Maven dependencies:
<! -- httpClient -->
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.5</version>
</dependency>
Copy the codeAdd a new HttpClientUtil utility class to use:
public class HttpClientUtil {
private static CloseableHttpClient httpClient = null;
public static void init(a) {
Create a connection pool manager. By default, this implementation will create no more than 2 concurrent connections for a given route, and no more than 20 connections in total
PoolingHttpClientConnectionManager connectionManager = new PoolingHttpClientConnectionManager(60000./ / 1.1
TimeUnit.MILLISECONDS);// By default, the Tomcat server maintains the connection for 60 seconds. If the connection exceeds 60 seconds, the tomcat server closes the connection to the client
connectionManager.setMaxTotal(1000);// Set the connector to support up to 1000 links at a time
connectionManager.setDefaultMaxPerRoute(50);// Set a maximum of 50 links per route. Note that routing refers to IP+PORT or domain name
//2. Create an HttpClient object
httpClient = HttpClients.custom()
.setConnectionManager(connectionManager)
.disableAutomaticRetries()
.build();
}
public static String doGet(String url) {
// Initialize the connection pool
if(httpClient == null)
init();
String httpResponse = null;
// Create a Get request
HttpGet httpGet = new HttpGet(url);
// Response model
CloseableHttpResponse response = null;
try {
// Configuration information
RequestConfig requestConfig = RequestConfig.custom()
// Set connection timeout in milliseconds
.setConnectTimeout(5000)
// Set the request timeout in milliseconds
.setConnectionRequestTimeout(5000)
// Timeout of socket read and write (milliseconds)
.setSocketTimeout(5000)
// Sets whether redirection is allowed (default: true)
.setRedirectsEnabled(true).build();
// Apply the above configuration information to the Get request
httpGet.setConfig(requestConfig);
// The client executes (sends) the Get request
response = httpClient.execute(httpGet);
// Get the response entity from the response model
HttpEntity responseEntity = response.getEntity();
// Get the request body
httpResponse = EntityUtils.toString(responseEntity,"UTF-8");
} catch (ParseException | IOException e) {
e.printStackTrace();
} finally {
// Reclaim the connection
if (null! = response) {try {
EntityUtils.consume(response.getEntity());
} catch(IOException e) { e.printStackTrace(); }}}return httpResponse;
}
public static String doPost(String url) throws IOException {
if(httpClient == null)
init();
String httpResponse = null;
// Get the Http client (you need a browser; Note: Actually HttpClient is not the same as browser.
CloseableHttpClient httpClient = HttpClientBuilder.create().build();
// Create a Post request
HttpPost httpPost = new HttpPost(url);
// Response model
CloseableHttpResponse response = null;
HttpEntity responseEntity = null;
try {
// The client executes (sends) the Post request
response = httpClient.execute(httpPost);
// Get the response entity from the response model
responseEntity = response.getEntity();
if(responseEntity ! =null) {
// Get the request body
httpResponse = EntityUtils.toString(responseEntity,"UTF-8"); }}catch (ParseException | IOException e) {
e.printStackTrace();
} finally {
// Reclaim the connection
if (null! = response) {try {
EntityUtils.consume(response.getEntity());
} catch(IOException e) { e.printStackTrace(); }}}return httpResponse;
}
public static String doPostWithEntity(String url,String json) throws IOException {
if(httpClient == null)
init();
String httpResponse = null;
// Get the Http client (you need a browser; Note: Actually HttpClient is not the same as browser.
CloseableHttpClient httpClient = HttpClientBuilder.create().build();
// Create a Post request
HttpPost httpPost = new HttpPost(url);
// Response model
CloseableHttpResponse response = null;
HttpEntity responseEntity = null;
try {
StringEntity requestEntity = new StringEntity(json,"utf-8");
requestEntity.setContentEncoding("UTF-8");
httpPost.setHeader("Content-type"."application/json");
httpPost.setEntity(requestEntity);
// The client executes (sends) the Post request
response = httpClient.execute(httpPost);
// Get the response entity from the response model
responseEntity = response.getEntity();
if(responseEntity ! =null) {
// Get the request body
httpResponse = EntityUtils.toString(responseEntity,"UTF-8"); }}catch (ParseException | IOException e) {
e.printStackTrace();
} finally {
// Reclaim the connection
if (null! = response) {try {
EntityUtils.consume(response.getEntity());
} catch(IOException e) { e.printStackTrace(); }}}returnhttpResponse; }}Copy the code4. Use JsonPath tool
The introduction of maven:
<! -- jsonPath json extraction tool -->
<dependency>
<groupId>com.jayway.jsonpath</groupId>
<artifactId>json-path</artifactId>
<version>2.4.0</version>
</dependency>
Copy the codeJsonToSqlUtill: JsonToSqlUtill: JsonToSqlUtill: JsonToSqlUtill
@Component
public class JsonToSqlUtill {
@Value("${JsonToSqlUtill.field}")
private Set<Object> field;
private static Set<Object> s_field;
@Value("${JsonToSqlUtill.timeField}")
private Set<Object> timeField;
private static Set<Object> s_timeField;
@Value("${JsonToSqlUtill.timeFieldType}")
private boolean timeFieldType;
private static boolean s_timeFieldType;
@Value("${JsonToSqlUtill.tableName}")
private String tableName;
private static String s_tableName;
@Value("${JsonToSqlUtill.dataPath}")
private String dataPath;
private static String s_dataPath;
@Value("${JsonToSqlUtill.url}")
private String url;
private static String s_url;
@PostConstruct
public void getField(a){
s_field = this.field;
}
@PostConstruct
public void getTimeField(a){
s_timeField = this.timeField;
}
@PostConstruct
public void getTimeFieldType(a){
s_timeFieldType = this.timeFieldType;
}
@PostConstruct
public void getTableName(a){
s_tableName = this.tableName;
}
@PostConstruct
public void getDataPath(a){
s_dataPath = this.dataPath;
}
@PostConstruct
public void getUrl(a){
s_url = this.url;
}
public static String getSqlWithJson(String json) throws IOException {
// Get the data returned by httpClient
String httpResponse = doPostWithEntity(s_url,json);
return getSql(httpResponse);
}
public static String getSqlWithGet(a) throws IOException {
// Get the data returned by httpClient
String httpResponse = doGet(s_url);
return getSql(httpResponse);
}
public static String getSqlWithPost(a) throws IOException {
// Get the data returned by httpClient
String httpResponse = doPost(s_url);
return getSql(httpResponse);
}
private static String getSql(String httpResponse) throws IOException {
List<Object> list = new ArrayList<>();
// Get the data needed in json
List<HashMap> data = JsonPath.read( httpResponse,"$."+ s_dataPath +"[*]");
//ON DUPLICATE KEY UPDATE (SQL: UPDATE if database exists, insert if database does not exist
StringBuilder updateFields = new StringBuilder();
for (Object key : s_field){
// Add the fields to the list to make it easier for the following fields to be nullated
list.add(key.toString());
updateFields.append(key).append("=").append("values(").append(key).append("),");
}
// Remove the last comma
updateFields.delete(updateFields.length()-1,updateFields.length()).append(";");
// Get the field to insert in values
// index is used to determine if a field in the map has a null value
int index = 0;
StringBuilder values = new StringBuilder();
for (Map map : data){
// Set the loop to 0 each time
index = 0;
values.append("(");
for (Object key : map.keySet()){
// If the index is not correct, the missing segment is automatically filled with null values until the index is equal
while(list.indexOf(key) ! = index){ values.append("'").append("null").append("'").append(",");
index++;
}
// If the value is time, format conversion is required
if (s_timeField.contains(key)){
if (s_timeFieldType){
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String date = df.format(new Date(Long.parseLong((map.get(key).toString()))));
// Write time, skip subsequent execution steps
values.append("'").append(date).append("'").append(",");
}else {
values.append("'").append(map.get(key).toString()).append("'").append(",");
}
index++;
continue;
}
// Add the value to values
values.append("'").append(map.get(key)).append("'").append(",");
// Go to the next index
index++;
}
// Finally fill the missing data segment with null values
while(list.size() ! = index){// If time is missing, take the default time
if (s_timeField.contains(list.get(index))){
// Write time, skip subsequent execution steps
values.append("'").append("0000-00 00 -").append("'").append(",");
index++;
continue;
}
values.append("'").append("null").append("'").append(",");
index++;
}
// Remove the comma from the last field
values.delete(values.length()-1,values.length());
values.append("),");
}
// Remove the generated comma at the end
values.delete(values.length()-1,values.length());
// The final splice
StringBuilder finallySql = new StringBuilder();
finallySql.append("INSERT INTO ").append(s_tableName).append("(").append(s_field.toString().substring(1,s_field.toString().length()-1)).append(")").
append("VALUES").append(values).append("ON DUPLICATE KEY UPDATE ").append(updateFields);
returnString.valueOf(finallySql); }}Copy the codeUse: Start with the configuration file (yML format used here)
JsonToSqlUtill:
field: id,name,description,pictureUrl,url,createDate # Data field names in return order
timeField: createDate,updateDate # format is the field of time (English, separated)
timeFieldType: true # Whether to use timestamp format
tableName: data SQL > alter table name
Example: {"store": {" data": [{"category": "reference", "price": 8.95}, path: store.data
dataPath: data.data
url: https://xxxxxxxxxx #url
Copy the codeOnce the files are configured, there are three methods that you can use directly
GetSqlWithGet () : Make a Get request directly using the URL of the configuration file and return a complete SQL
GetSqlWithPost () : Makes a Post request directly using the URL of the configuration file and returns a complete SQL
GetSqlWithJson (JSON) : Request with a JSON string and return a complete SQL
// Request through Get
String Sql = getSqlWithGet();
System.out.println(Sql);
// Request through Post
String Sql = getSqlWithPost();
System.out.println(Sql);
// Request via Post+Json parameters
String Sql = getSqlWithJson(json);
System.out.println(Sql);
Copy the codeAs shown in the figure:
Create the corresponding data table in the database, and finally use their own way to execute this SECTION of SQL.
Delete the primary key id of the database, store it as a list, and then parse the ID with the JSON obtained from HTTP as a list. Use removeAll between the two lists to obtain the id that has been deleted