热.冷.云备
假设你是一位女性，你有一位男朋友，于此同时你和另外一位男生暧昧不清，比朋友 好，又不是恋人。你随时可以甩了现任男友，另外一位马上就能补上。这是冷备份。
假设你是一位女性，同时和两位男性在交往，两位都是你男朋友。并且他们还互不干涉，独立运行。这就是双机热备份。
假设你是一位女性，不安于男朋友给你的安全感。在遥远的男友未知的地方，和一位男生保持着联系，你告诉他你没有男朋友，你现在处于纠结期，一旦你和你男朋友分开了，你马上可以把自己感情转移到异地男人那里去。这是异地容灾备份。
假设你是一位女性，有一位男朋友，你又付了钱给一家婚姻介绍所，让他帮你留意好的资源，一旦你和你这位男朋友分开，婚姻介绍所马上给你安排资源，你感情不间断运行，这是云备份。。。。
数据安全大于一切，今天你把自己备份了吗？根据评论区脑洞，再补几个。
假设你是一位女性，你怀疑男朋友对你的忠诚，在某宝购买了一个测试忠诚度的服务。这是灾难演练。友情提醒，在没有备份的情况下，切忌进行灾难演练，说不好会让你数据血本无归。。
假设你是一位女性，你有一位好到不能在好的闺蜜，好到你们可以共享一个男朋友，这是NAS。
假设你是一位女性，你男朋友活太好，你一个人根本hold不住，必须要姐妹帮忙才能稳住他。这是负载均衡，QOS。
假设你是一位女性，和A吃饭和B逛街和C打炮。合起来是一个完整的男朋友。这。。这是超算集群。。。建议主频不高的女性不要这样做。会直接死机的。
**
* 店铺商品导出
*
* @param commodityQueryModel
*/
public int export(ShopCommoditySearch commodityQueryModel, HttpServletResponse res, HttpServletRequest req) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();//excel文件
HSSFSheet sheet = wb.createSheet("导出商品列表");//sheet表
sheet.setColumnWidth(0, 20 * 256);
sheet.setColumnWidth(1, 20 * 256);
sheet.setColumnWidth(2, 20 * 256);
sheet.setColumnWidth(3, 20 * 256);
sheet.setColumnWidth(4, 20 * 256);
sheet.setColumnWidth(5, 20 * 256);
sheet.setColumnWidth(6, 20 * 256);
sheet.setColumnWidth(7, 20 * 256);
sheet.setColumnWidth(8, 20 * 256);
sheet.setColumnWidth(9, 20 * 256);
HSSFCellStyle style = wb.createCellStyle();
// 设置这些样式
style.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
String[] headers = {"商品条码", "商品名称", "单位", "商品规格", "进价", "售价", "库存", "成本", "预售金额", "毛利润"};
//写表头
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//获得商品列表
List<Map> list = hqCommodityMapper.findHqList(commodityQueryModel);
List<CommodityBase> list1 = this.parseMap(list);
for (int i = 0; i < list1.size(); i++) {
CommodityBase commodityBase = list1.get(i);
HSSFRow row1 = sheet.createRow(i + 1);
row1.createCell(0).setCellValue(commodityBase.getBarcode());
row1.createCell(1).setCellValue(commodityBase.getName());
row1.createCell(2).setCellValue(commodityBase.getUnit());
row1.createCell(3).setCellValue(commodityBase.getSpec());
row1.createCell(4).setCellValue(commodityBase.getBuyPrice().toString());
row1.createCell(5).setCellValue(commodityBase.getSellPrice().toString());
row1.createCell(6).setCellValue(commodityBase.getInventoryNum().toString());
row1.createCell(7).setCellValue(commodityBase.getCost().toString());
row1.createCell(8).setCellValue(commodityBase.getAmount().toString());
row1.createCell(9).setCellValue(commodityBase.getProfit().toString());
}
/* 下载导出*/
res.setContentType("application/vnd.ms-excel");
String filename = "导出商品列表.xls";
String agent = req.getHeader("user-agent");
filename = FileUtils.encodeDownloadFilename(filename, agent);
res.setHeader("Content-Disposition", "attachment;filename=" + filename);
ServletOutputStream outputStream = res.getOutputStream();
wb.write(outputStream);
wb.close();
return list.size();
}
<select id="listPermissionByRoleId" resultType="java.util.Map">
SELECT t.id as permission_id , t.name, t.code,t.depend_id, t.module_id, t.edit_flag, t.shop_flag from (
SELECT t1.* from auth_permission t1
WHERE EXISTS (
SELECT t2.permission_id from auth_role_permission t2
LEFT JOIN auth_role t3 on t3.id = t2.role_id
WHERE t1.id = t2.permission_id and t3.id = #{roleId} )
) t
LEFT JOIN auth_module t3 on t.module_id=t3.id
order by t.id asc
</select>
<select id="findListPermissionByHqIdOrShopId" parameterType="map" resultType="java.util.Map">
select p.id as permission_id , up.user_id,p.name as permission_name , p.code
from auth_user_permission as up
left join auth_permission as p on up.permission_id = p.id
where up.user_id = #{userId} and ((up.hq_id = #{hqId} or up.shop_id = #{shopId}) or (up.shop_id is null and up.hq_id is null))
group by p.id,up.user_id, p.name,p.code
order by p.id asc
</select>
<select id="findHqListModulePermissions" resultType="java.util.Map">
select a.mid, a.name, a.type, a.parent_id from (
select
p.id, m0.id as mid, m0.name, 2 as type, m0.parent_id, p.shop_flag
from auth_permission as p
left join auth_module as m0 on p.module_id = m0.id
where p.shop_flag = #{client}
union all
select p.id, m1.id, m1.name, 1, null, p.shop_flag from auth_permission as p
left join auth_module as m0 on p.module_id=m0.id
left join auth_module as m1 on m0.parent_id=m1.id
where m1.id is not null and p.shop_flag = #{client}
group by p.id, m1.id, m1.name, p.shop_flag
) as a
left join auth_permission as p on a.id=p.id
left join auth_user_permission as up on p.id = up.permission_id
where up.user_id = #{userId} and (up.hq_id = #{hqId} or (up.shop_id is null and up.hq_id is null))
group by a.mid, a.name, a.type, a.parent_id
</select>
<select id="findShopListModulePermissions" resultType="java.util.Map">
select a.mid, a.name, a.type, a.parent_id from (
select
p.id, m0.id as mid, m0.name, 2 as type, m0.parent_id, p.shop_flag
from auth_permission as p
left join auth_module as m0 on p.module_id = m0.id
where p.shop_flag = #{client}
union all
select p.id, m1.id, m1.name, 1, null, p.shop_flag from auth_permission as p
left join auth_module as m0 on p.module_id=m0.id
left join auth_module as m1 on m0.parent_id=m1.id
where m1.id is not null and p.shop_flag = #{client}
group by p.id, m1.id, m1.name, p.shop_flag
) as a
left join auth_permission as p on a.id=p.id
left join auth_user_permission as up on p.id = up.permission_id
where up.user_id = #{userId} and (up.shop_id = #{shopId} or (up.shop_id is null and up.hq_id is null))
group by a.mid, a.name, a.type, a.parent_id
</select>
<delete id="deleteUserPermission">
delete auth_user_permission from auth_user_permission
left join auth_permission on auth_permission.id = auth_user_permission.permission_id
<where>
<if test="userId != null">
and auth_user_permission.user_id = #{userId}
</if>
<if test="hqId != null and shopId != null">
and (auth_user_permission.hq_id = #{hqId} or auth_user_permission.shop_id = #{shopId})
</if>
and auth_permission.edit_flag = #{editFlag}
</where>
</delete>
<insert id="batchInsertUserPermissionHq" parameterType="map" keyProperty="id" useGeneratedKeys="true">
insert into auth_user_permission(user_id,hq_id,permission_id)
values
<foreach collection="hqPerIds" index="index" item="item" separator=",">
( #{userId},#{hqId},#{item} )
</foreach>
</insert>
<insert id="batchInsertUserPermissionShop" parameterType="map" keyProperty="id" useGeneratedKeys="true">
insert into auth_user_permission(user_id,shop_id,permission_id)
values
<foreach collection="shopPerIds" index="index" item="item" separator=",">
( #{userId},#{shopId},#{item} )
</foreach>
</insert>
<select id="findEmpPermission" resultType="java.lang.Long">
select p.id from auth_permission p
left join auth_role_permission t2 on p.id = t2.permission_id
left join auth_role t3 on t3.id = t2.role_id
where role_id = #{role} and shop_flag = #{shopFlag}
and p.id not in (
SELECT up.permission_id FROM wetool.auth_user_permission as up where up.user_id = #{userId} and (up.shop_id is null or up.hq_id is null)
)
</select>
<select id="findParentIdByPermissionId" resultType="java.lang.Long">
select ap.depend_id from auth_permission as ap where ap.id = #{permissionId} and ap.depend_id is not null
</select>
<select id="findDefaultPermissionsByEditFlag" resultType="java.lang.Long">
select id from auth_permission where edit_flag = #{editFlag}
</select>
<select id="findShopList" resultType="java.util.Map">
SELECT s.id AS shop_id,a.id AS user_id,s.name AS shop_name,s.contacts_tel,
s.short_name,s.contacts,s.create_time,s.expire_time,s.address,
a.name AS emp_name,s.hq_id,a.head_image_url AS user_url,s.logo_url,
s.province,s.city,s.area,s.invitation_code,se.max_discount_price,
se.inviter_id,IFNULL(ts.total, 0) AS isBoss,q.isChain
FROM
es_shop AS s
LEFT JOIN es_shop_employee AS se ON se.shop_id = s.id
LEFT JOIN auth_user AS a ON se.user_id = a.id
LEFT JOIN
(SELECT
username, id, COUNT(1) AS total
FROM
es_hq
GROUP BY username , id) AS ts ON a.username = ts.username
AND s.hq_id = ts.id
LEFT JOIN
(SELECT
ts.id, COUNT(1) AS isChain
FROM
es_hq AS ts
LEFT JOIN es_shop AS s ON ts.id = s.hq_id
GROUP BY ts.id) AS q ON q.id = s.hq_id
<where>
se.status = 1
<if test="userName != null and userName != ''">
and a.username = #{userName}
</if>
<if test="shopId != null and shopId != ''">
and s.id = #{shopId}
</if>
</where>
</select>
{
"_index": "bix",
"_type": "barcode",
"_id": "7c8a7d2aa19558b9b37675943593f9fb",
"_score": 1,
"_source": {
"country": "中国",
"stat": 2,
"other": """{"id":2292891}""",
"code": "6947503712365",
"address": "上海市奉贤区青村镇金钱公路3488号-4",
"price": 3.9,
"name": "晨光米菲修正液MF6002",
"blacklist": 1,
"ctime": "2018-01-05 16:13:55",
"company": "上海晨光文具股份有限公司",
"id": "7c8a7d2aa19558b9b37675943593f9fb",
"category": 0
}
}
{
"took": 0,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 14874,
"max_score": 1,
"hits": [
{
"_index": "bix",
"_type": "barcode",
"_id": "c3db71689347b47197645b9df4a8e009",
"_score": 1,
"_source": {
"country": "中国",
"stat": 2,
"other": """{"id":483912}""",
"code": "6903252237900",
"address": "天津市经济技术开发区睦宁路218号",
"price": 0,
"name": "康师傅珍品爆椒",
"blacklist": 1,
"ctime": "2018-01-05 16:13:55",
"company": "天津顶益食品有限公司",
"id": "c3db71689347b47197645b9df4a8e009",
"category": 0
}
},
{
"_index": "bix",
"_type": "barcode",
"_id": "7c8a7d2aa19558b9b37675943593f9fb",
"_score": 1,
"_source": {
"country": "中国",
"stat": 2,
"other": """{"id":2292891}""",
"code": "6947503712365",
"address": "上海市奉贤区青村镇金钱公路3488号-4",
"price": 3.9,
"name": "晨光米菲修正液MF6002",
"blacklist": 1,
"ctime": "2018-01-05 16:13:55",
"company": "上海晨光文具股份有限公司",
"id": "7c8a7d2aa19558b9b37675943593f9fb",
"category": 0
}
},
{
"_index": "bix",
"_type": "barcode",
"_id": "d00965c20a5650ab410aabc0c7fa6530",
"_score": 1,
"_source": {
"country": "中国",
"stat": 2,
"other": """{"id":2277180}""",
"code": "6910019010867",
"address": "浙江省丽水市上水南三号",
"blacklist": 1,
"spec": "1.5千克",
"unit": "瓶",
"price": 10.2,
"name": "雕牌超效洗洁精1.5千克/瓶",
"ctime": "2018-01-05 16:13:55",
"company": "纳爱斯集团有限公司",
"id": "d00965c20a5650ab410aabc0c7fa6530",
"category": 0
}
},
{
"_index": "bix",
"_type": "barcode",
"_id": "ca20c8023079123ac8a441628122e313",
"_score": 1,
"_source": {
"country": "中国",
"stat": 2,
"other": """{"id":2141076}""",
"code": "6946766600891",
"address": "河南省郑州市侯寨乡铁三官庙村",
"price": 0,
"name": "天宜园",
"blacklist": 1,
"ctime": "2018-01-05 16:13:55",
"company": "郑州市天宜园食品有限公司二七分公司",
"id": "ca20c8023079123ac8a441628122e313",
"category": 0
}
},
{
"_index": "bix",
"_type": "barcode",
"_id": "f4338e71594427dcf43984e6e890ea2b",
"_score": 1,
"_source": {
"country": "中国",
"stat": 2,
"other": """{"id":712440}""",
"code": "6924011400045",
"address": "河北省保定市云杉路131号",
"blacklist": 1,
"spec": "包",
"price": 5.5,
"name": "女主角(16+4)超薄干网独立卫生巾",
"ctime": "2018-01-05 16:13:55",
"company": "河北义厚成日用品有限公司",
"id": "f4338e71594427dcf43984e6e890ea2b",
"category": 0
}
}
]
}
}
package com.wetool.test.core;
import com.alibaba.fastjson.JSON;
import com.squareup.okhttp.*;
import com.wetool.test.po.Bix;
import com.wetool.test.utils.MyConverter;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DataApp {
private static final OkHttpClient client = new OkHttpClient();
private static final Logger log = Logger.getLogger(DataApp.class);
public static void main(String[] args) {
//查询es
String searchurl = "http://192.168.1.10:9200/bix/barcode/_search";
List<Bix> bixes = searchEs(0,1,searchurl);
//写入mysql
//删除es
String delurl = "http://192.168.1.10:9200/bix/barcode";
deleteEs(bixes,delurl);
}
/**
* 查询需要迁移的数据
* @param from 开始位置
* @param size 结束位置
* @param searchurl 迁移目标
* @return
*/
public static List<Bix> searchEs(int from, int size, String searchurl){
Logger log = Logger.getLogger(DataApp.class);
List<Bix> bixes = new ArrayList<>();
String url = searchurl+"?from="+from+"&size="+size+"";
Request request = new Request.Builder()
.url(url)
.get()
.build();
log.info("Send request url >>>" + url);
try {
Response response = client.newCall(request).execute();
if (response.isSuccessful()) {
String json = response.body().string();
System.out.println(json);
Map map = JSON.parseObject(json, Map.class);
for (Object o : map.keySet()) {
if ("hits".equals(o)) {
Map map1 = JSON.parseObject(map.get(o).toString(), Map.class);
for (Object o1 : map1.keySet()) {
if ("hits".equals(o1)) {
List list = JSON.parseObject(map1.get(o1).toString(), List.class);
for (Object o2 : list) {
Map map2 = JSON.parseObject(o2.toString(), Map.class);
for (Object o3 : map2.keySet()) {
if ("_source".equals(o3)) {
System.out.println("<><><>" + map2.get(o3));
HashMap<String,Object> map3 = JSON.parseObject(map2.get(o3).toString(), HashMap.class);
Bix bix = new Bix();
map3.put("ctime",new MyConverter().convert(map3.get("ctime").toString()));
BeanUtils.populate(bix,map3);
bixes.add(bix);
}
}
}
}
}
}
}
} else {
throw new IOException("Unexpected code " + response);
}
return bixes;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 将es中已迁移的数据删除
* @param list 删除的数据集
* @param delurl 删除目标
*/
public static void deleteEs(List<Bix> list, String delurl){
MediaType type = MediaType.parse("application/json; charset=utf-8");
//构造请求
String ids = "";
int count = 0;
for (Bix bix1 : list) {
/* System.out.println("bix_id:"+bix1.getId());
System.out.println("bix_ctime:"+bix1.getCtime());
System.out.println("bix_price:"+bix1.getPrice());
System.out.println("bix_stat:"+bix1.getStat());*/
if(count == 0){
ids = ids+"{\"match\": {\"_id\":\""+bix1.getId()+"\"}}";
count+=1;
}else {
ids = ids+"\n,{\"match\": {\"_id\":\""+bix1.getId()+"\"}}";
}
}
count = 0;
String url = delurl+"/_delete_by_query";
String body = "{\n" +
" \"query\": {\n" +
" \"bool\": {\n" +
" \"must\": [\n" +
" {\"bool\": {\"should\": [\n" +
ids +
" ]}\n" +
" }\n" +
" ]\n" +
" }\n" +
" }\n" +
"}";
log.info("requestBody >>>"+body);
//发送删除请求
RequestBody requestBody = RequestBody.create(type, body);
Request request = new Request.Builder()
.url(url)
.post(requestBody)
.build();
log.info("deleta es url >>>"+ url);
try {
Response response = client.newCall(request).execute();
if (response.isSuccessful()) {
log.info("response body >>>"+response.body().string());
} else {
throw new IOException("Unexpected code " + response);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
无部署 操作数据迁移
GET _search
{
"query": {
"match_all": {}
}
}
GET /serv/stockd/_search
{ "from": 0,
"size": 10000}
GET /bix/barcode/_search
{
"from": 0,
"size": 1613
}
GET /bix/barcode/_search
{
"from":0,
"size":10000
}
GET /_cat/indices?v
GET /_count?pretty
{"query": {
"match_all": {}
}
}
GET /serv/stock/_search
{
}
GET /serv/stock/e0a50e7b9739415038dd99659d4468ca
PUT serv/stockd/0b702ab977e67473f4d0e6b180f11fc61111
{
"barcode": "6923450656181111",
"ctime": "2018-02-01 10:39:30",
"id": "0b702ab977e67473f4d0e6b180f11fc61111",
"origin": "CK1802013300081111",
"price_purchase": 8111,
"price_sell": 10111,
"product_id": 26111,
"product_name": "口香糖111",
"qty": 1111,
"shop_id": 13111,
"stock0": -9,
"stock1": -10
}
GET leo/test/_search
{
"from": 90,
"size": 200
}
GET /serv/stockd/_search
{
"query": {
"match_all": {}
}
, "sort": [
{
"price_sell": {
"order": "desc"
}
}
]
}
GET /serv/stockd/_search
{
"query": {
"match_all": {}
}
, "sort": [
{
"price_sell": {
"order": "asc"
}
}
],
"from": 1,
"size": 3
}
GET /serv/stockd/_search
{
"query":{
"match_phrase": {
"product_name": "口香糖"
}
}
}
GET /serv/stockd/_search
{
"query":{
"match": {
"product_name": "口香糖"
}
}
}
GET /serv/stockd/_search
{
"query" : {
"bool" : {
"must" : {
"match" : {
"price_sell": 10
}
},
"filter" : {
"range" : {
"product_id" : { "gt" : 1500}
}
}
}
}
}
GET /serv/stockd/_search
{
"query" : {
"match" : {
"product_name" : "草莓"
}
}
}
GET /serv/stockd/_search
{
"query" : {
"match" : {
"product_name" : "卤蛋"
}
},
"highlight": {
"fields" : {
"price_sell" : {}
}
}
}
GET /serv/stockd/_search
{
"query":{"match_all": {}},
"sort": [
{
"price_sell": {
"order": "asc"
}
}
]
}
GET /serv/stockd/_search
{
"query":{"match_all": {}},
"_source":["price_sell","product_name"]
}
GET /serv/stockd/_search
{
"query": {
"constant_score": {
"filter": {
"bool": {
"must": [{ "term": { "price_sell": 10 }}]
}
}
}
},
"aggs": {
"product_list": {
"terms": { "field": "type"}
}
}
}
GET /serv/stockd/_search
{
"query": {
"constant_score": {
"filter": {
"bool": {
"must": [{ "term": { "price_sell": 10 }}]
}
}
}
},
"aggs": {
"product_list": {
"terms": { "field": "type"}
}
}
}
GET /serv/stockd/_search
{
"query": {
"constant_score": {
"filter": {
"bool": {
"must": [{ "term": { "price_sell": 10 }}]
}
}
}
},
"aggs": {
"product_list": {
"terms": { "field": "type"},
"aggs": {
"min_price_pruchase": {
"min": {"field": "price_purchase"}
}
}
}
}
}
GET /serv/stockd/_search
{
"query": {
"constant_score": {
"filter": {
"bool": {
"must": [{ "term": { "price_sell": 10 }}]
}
}
}
},
"aggs": {
"product_list": {
"terms": { "field": "type"},
"aggs": {
"price_sell_list": {
"terms": { "field": "price_sell" }
}
}
}
}
}
GET /serv/stockd/_search
{
"query": {
"constant_score": {
"filter": {
"bool": {
"must": [{ "term": { "price_sell": 10 }}]
}
}
}
},
"aggs": {
"product_list": {
"terms": {
"field": "type"
},
"aggs": {
"stockd": {
"top_hits": {
"size": 1,
"sort": { "price_purchase": "desc" },
"_source": []
}
}
}
}
}
}
GET /serv/stockd/_search
{
"query" : {
"bool" : {
"filter" : {
"range" : {
"product_id" : { "gt" : 1500}
}
}
}
}
}
GET /serv/stockd/_search
{
"aggs" : {
"product_list" : {
"terms" : { "field" : "type" },
"aggs" : {
"avg_price_sell" : {
"avg" : { "field" : "price_sell" }
}
}
}
}
}
POST /leo/test/
{
"barcode": "22",
"ctime": "2018-02-01 10:53:42",
"id": "22",
"origin": "22",
"price_purchase": 2,
"price_sell": 2,
"product_id": 2,
"product_name": "22",
"qty": 1,
"shop_id": 25,
"stock0": 10,
"stock1": 9
}
POST /_bulk
{ "index": { "_index": "leo", "_type": "test", "_id": "1255" }}
{ "title": "sss2255" }
{ "index": { "_index": "leo", "_type": "test", "_id": "1425" }}
{ "title": "xxs22335566" }
GET /serv/stockd/_search
{
"query" : {
"match" : {
"product_name" : "好吃的卤蛋"
}
}
}
GET /serv/stockd/_search
{
"query":{
"match":{
"product_name":"桃子"
}
}
}
GET /serv/stockd/_search
{
"query" : {
"bool" : {
"must" : {
"match" : {
"product_name": "青苹果"
}
},
"filter" : {
"range" : {
"product_id" : { "gt" : 1500}
}
}
}
}
}
GET /serv/stockd/0b702ab977e67473f4d0e6b180f11fc6
GET /serv/stockd/_search?q=hello
GET /serv/_mapping
GET leo/test/_search
POST /leo/test/_bulk
{"index": {"_index": "leo", "_type": "test","_id":555}}
{"count": 5, "desc": "hello world 111"}
{"index": {"_index": "leo", "_type": "test","_id":556}}
{"count": 6, "desc": "hello world 222"}
{"index": {"_index": "leo", "_type": "test","_id":557}}
{"count": 7, "desc": "hello world 333"}
{"index": {"_index": "leo", "_type": "test","_id":558}}
{"count": 8, "desc": "hello world 444"}
GET /leo/test/_search
{
"query": {
"match": {
"desc": "hello"
}
}
}
GET /bix/barcode/_search
POST /_reindex
{
"source": {
"index": "leo"
},
"dest": {
"index": "leo2"
}
}
GET /leo2/test/_search
GET /leo/test/_search
POST /leo2/test/_delete_by_query
{
"query": {
"bool": {
"must": [
{"bool": {"should": [
{"match": {"_id":"AWMK2HKx62BIKjMD3tTB"}}
,{"match": {"_id":"AWMF1cQa62BIKjMD3tS4"}} ]}
}
]
}
}
}
POST /bix/barcode/_delete_by_query
{
"query": {
"bool": {
"must": [
{"bool": {"should": [
{"match": {"_id":"AWEl0tzKecBeOegdoksO"}}
]}
}
]
}
}
}
package com.wetool.test.core;
import com.alibaba.fastjson.JSON;
import com.squareup.okhttp.*;
import com.wetool.test.po.Bix;
import com.wetool.test.utils.MyConverter;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
public class DataApp {
private static final OkHttpClient client = new OkHttpClient();
private static final Logger log = Logger.getLogger(DataApp.class);
public static void main(String[] args) {
//查询es
String searchurl = "http://192.168.1.10:9200/bix/barcode/_search";
List<Bix> bixes = searchEs(2,2,searchurl);
//写入mysql
insertMysql(bixes);
//删除es
/* String delurl = "http://192.168.1.10:9200/bix/barcode";
deleteEs(bixes,delurl);*/
}
/**
* 查询需要迁移的数据
* @param from 开始位置
* @param size 结束位置
* @param searchurl 迁移目标
* @return
*/
public static List<Bix> searchEs(int from, int size, String searchurl){
List<Bix> bixes = new ArrayList<>();
String url = searchurl+"?from="+from+"&size="+size+"";
Request request = new Request.Builder()
.url(url)
.get()
.build();
log.info("Send request url >>>" + url);
try {
Response response = client.newCall(request).execute();
if (response.isSuccessful()) {
String json = response.body().string();
System.out.println(json);
Map map = JSON.parseObject(json, Map.class);
for (Object o : map.keySet()) {
if ("hits".equals(o)) {
Map map1 = JSON.parseObject(map.get(o).toString(), Map.class);
for (Object o1 : map1.keySet()) {
if ("hits".equals(o1)) {
List list = JSON.parseObject(map1.get(o1).toString(), List.class);
for (Object o2 : list) {
Map map2 = JSON.parseObject(o2.toString(), Map.class);
for (Object o3 : map2.keySet()) {
if ("_source".equals(o3)) {
System.out.println("<><><>" + map2.get(o3));
HashMap<String,Object> map3 = JSON.parseObject(map2.get(o3).toString(), HashMap.class);
Bix bix = new Bix();
if(map3.get("ctime") == null || map3.get("ctime").toString().equals("")){
map3.put("ctime",new MyConverter().convert("2018-01-05 16:13:55"));
}else {
map3.put("ctime",new MyConverter().convert(map3.get("ctime").toString()));
}
BeanUtils.populate(bix,map3);
bixes.add(bix);
}
}
}
}
}
}
}
} else {
throw new IOException("Unexpected code " + response);
}
return bixes;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 数据存储于mysql
* @param list
*/
public static void insertMysql(List<Bix> list){
Connection connection = null;
PreparedStatement preparedStatement =null;
//数据源
HikariDataSource dataSource = new HikariDataSource();
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/wetool?characterEncoding=utf-8");
//构建sql
int count = 0;
String values = "";
for (Bix data : list) {
if(count == 0){
values = "('"+data.getId()+"',"+data.getBlacklist()+","+data.getCategory()+",'"+data.getCode()+"','"+data.getCompany()+"','"+data.getCountry()+"','"+data.getEncary()+"',"+data.getHas_datail()+",'"+data.getImages()+"','"+data.getMark()+"','"+data.getMsg()+"','"+data.getName()+"','"+data.getOther()+"',"+data.getPrice()+",'"+data.getRemark()+"','"+data.getSpec()+"',"+data.getStat()+",'"+data.getUnit()+"','"+new MyConverter().DateToStr(data.getCtime())+"')";
count+=1;
}else {
values = values+",('"+data.getId()+"',"+data.getBlacklist()+","+data.getCategory()+",'"+data.getCode()+"','"+data.getCompany()+"','"+data.getCountry()+"','"+data.getEncary()+"',"+data.getHas_datail()+",'"+data.getImages()+"','"+data.getMark()+"','"+data.getMsg()+"','"+data.getName()+"','"+data.getOther()+"',"+data.getPrice()+",'"+data.getRemark()+"','"+data.getSpec()+"',"+data.getStat()+",'"+data.getUnit()+"','"+new MyConverter().DateToStr(data.getCtime())+"')";
}
}
count = 0;
String sql = "insert into bix values"+values;
log.info("insert sql >>>"+sql);
//insert
try
{
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement(sql);
int i = preparedStatement.executeUpdate();
log.info("成功插入"+i+"条数据");
} catch (SQLException e)
{
e.printStackTrace();
}finally
{
if(preparedStatement != null){
try
{
preparedStatement.close();
log.info("关闭资源:preparedStatement");
} catch (SQLException e)
{
e.printStackTrace();
}
}
if(connection != null){
try
{
connection.close();
log.info("关闭资源:connection");
} catch (SQLException e)
{
e.printStackTrace();
}
}
}
}
/**
* 将es中已迁移的数据删除
* @param list 删除的数据集
* @param delurl 删除目标
*/
public static void deleteEs(List<Bix> list, String delurl){
MediaType type = MediaType.parse("application/json; charset=utf-8");
//构建请求
String ids = "";
int count = 0;
for (Bix bix1 : list) {
if(count == 0){
ids = ids+"{\"match\": {\"_id\":\""+bix1.getId()+"\"}}";
count+=1;
}else {
ids = ids+"\n,{\"match\": {\"_id\":\""+bix1.getId()+"\"}}";
}
}
count = 0;
String url = delurl+"/_delete_by_query";
String body = "{\n" +
" \"query\": {\n" +
" \"bool\": {\n" +
" \"must\": [\n" +
" {\"bool\": {\"should\": [\n" +
ids +
" ]}\n" +
" }\n" +
" ]\n" +
" }\n" +
" }\n" +
"}";
//发送删除请求
RequestBody requestBody = RequestBody.create(type, body);
Request request = new Request.Builder()
.url(url)
.post(requestBody)
.build();
log.info("delete es url >>>"+ url);
log.info("requestBody >>>"+body);
try {
Response response = client.newCall(request).execute();
if (response.isSuccessful()) {
log.info("response body >>>"+response.body().string());
} else {
throw new IOException("Unexpected code " + response);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
浙公网安备 33010602011771号