月份:2017年12月


ElasticSearch增删改查日记


  public static void main(String[] args) {
  TransportClient client = null;
  try {
  client = new PreBuiltTransportClient(Settings.EMPTY)
  .addTransportAddress(new
  InetSocketTransportAddress(InetAddress.getByName("localhost"), 9300));
  SearchResponse response = client.prepareSearch()
  .setQuery(QueryBuilders.boolQuery()
  .should(QueryBuilders.rangeQuery("tax_paid").from("452588.45").to("452588.45"))
  .should(QueryBuilders.rangeQuery("cash").from("17573086.76").to("17573086.76"))
  .should(QueryBuilders.wildcardQuery("company_name", "*生物*"))
  )
  .get();
  System.out.println(response.getHits().totalHits);
  for (SearchHit hit : response.getHits().getHits()) {
  System.out.print("index:" + hit.getIndex() + "----");
  System.out.print("type:" + hit.getType() + "----");
  System.out.print("value:" + hit.getSource().get("company_id"));
  System.out.println();
  }
  } catch (UnknownHostException e) {
  e.printStackTrace();
  } finally {
  if (client != null) {
  client.close();
  }
  }
  }

/*
 * 批量操作
 */ 
  public static void main(String[] args) { TransportClient client = null; try {
   String json = "{" + "\"user\":\"kimchy\"," + "\"postDate\":\"2013-01-30\"," +
   "\"message\":\"trying out sadfsdafasdf\"" + "}"; String json2 = "{" +
   "\"user\":\"kimchy\"," + "\"postDate\":\"2013-01-31\"," +
  "\"message\":\"trying out aaaaa\"" + "}"; client = new
   PreBuiltTransportClient(Settings.EMPTY) .addTransportAddress(new
   InetSocketTransportAddress(InetAddress.getByName("localhost"), 9300));
   BulkRequestBuilder bulkRequestBuilder = client.prepareBulk();
   bulkRequestBuilder.add(client.prepareIndex("company", "company_profile", "2")
   .setSource(json)); bulkRequestBuilder.add(client.prepareIndex("company",
   "company_profile", "3").setSource(json2)); BulkResponse responses =
   bulkRequestBuilder.get(); if (responses.hasFailures()) {
   System.out.println(responses.toString()); } } catch (UnknownHostException e)
   { e.printStackTrace(); } finally { if (client != null) { client.close(); } }
   }
  

/*
 * 删除数据
 *
 */
  public static void main(String[] args) {
  TransportClient client = null;
  try {
  client = new PreBuiltTransportClient(Settings.EMPTY)
  .addTransportAddress(new
  InetSocketTransportAddress(InetAddress.getByName("localhost"), 9300));
  DeleteResponse response = client.prepareDelete("company", "company_profile",
  "1").get();
  System.out.println(response.status());
  } catch (UnknownHostException e) {
  e.printStackTrace();
  } finally {
  if (client != null) {
  client.close();
  }
  }
  }

/*
 * 插入数据
*/
 public static void main(String[] args) { TransportClient client = null;
   try { client = new PreBuiltTransportClient(Settings.EMPTY)
  .addTransportAddress(new
   InetSocketTransportAddress(InetAddress.getByName("localhost"), 9300)); String
   json = "{" + "\"user\":\"kimchy\"," + "\"postDate\":\"2013-01-30\"," +
   "\"message\":\"trying out Elasticsearch\"" + "}"; IndexResponse response =
   client.prepareIndex("company", "company_profile").setSource(json,
   XContentType.JSON).get(); String _index = response.getIndex(); String _type =
   response.getType(); String _id = response.getId(); long _version =
   response.getVersion(); System.out.println("index:" + _index + "\ntype:" +
   _type + "\nid:" + _id + "\nversion:" + _version); } catch
   (UnknownHostException e) { e.printStackTrace(); } finally { if (client !=
   null) { client.close(); } } }
 

Java 正则匹配全中文字段


// 按指定模式在字符串查找
String line = "全中文a";
String pattern = "^[\u4e00-\u9fa5]+$";

// 创建 Pattern 对象
Pattern r = Pattern.compile(pattern);

// 现在创建 matcher 对象
Matcher m = r.matcher(line);
System.out.println(m.matches());

BigDecimal计算


加法  a.add(b);

减法 a.subtract(b);

乘法 a.multiply(b);

除法 a.divide(b,需要精确到小数点以后几位,BigDecimal.ROUND_HALF_UP)

postgresql创建简单搜索视图


--创建search_company
CREATE MATERIALIZED VIEW search_company AS SELECT DISTINCT a.company_id,
a.ticker,
a.company_status_id,
a.company_name_cn,
a.company_name_en,
a.company_shortname_cn,
a.company_shortname_en,
b.brand_name,
a.year_founded,
c.name_cn AS company_status_name_cn,
c.name_en AS company_status_name_en
FROM ((company_profile a
LEFT JOIN brand_info b ON (((a.company_id)::text = (b.company_id)::text)))
LEFT JOIN config.dictionary c ON (((a.company_status_id)::text = (c.id)::text)))
WHERE a.company_status_id!='3.3'
ORDER BY a.ticker;

--导入自定义函数(扩展包)
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION zhcfg (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION zhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple;
create extension pg_trgm;
create or replace function record_to_text(anyelement) returns text as $$
select $1::text;
$$ language sql strict immutable;

--创建全文索引
create index idx_search_company_gist on search_company using gist (record_to_text(search_company) gist_trgm_ops);
create index idx_search_company_gin on search_company using gin (record_to_text(search_company) gin_trgm_ops);

--检测索引是否正常
/*+ BitmapScan(search_company idx_search_company_gin) */ select * from search_company where record_to_text(search_company) ~ '经纬' and company_status_id like'3%' limit 99;

同步同一个表中的相关字段


--创建函数

CREATE OR REPLACE FUNCTION updateItemDDL(center_show varchar,item_ddl VARCHAR,table_name varchar,item varchar)
RETURNS
void as $$
UPDATE schema_table set center_show=$1,item_ddl=$2 WHERE "table_name"=$3 AND item=$4 AND "version"='v2';
$$ LANGUAGE 'sql' VOLATILE;

--执行函数

SELECT updateItemDDL(center_show,item_ddl,"table_name",item) from schema_table WHERE "version"='v1' AND item_ddl!='';

查询是否执行成功

SELECT "table_name",item,center_show,item_ddl from schema_table WHERE "version"='v2' AND item_ddl!=''

postgresql某字段存多个id,然后根据id连表查询


--buyer_id即为多id字段,id间通过逗号分隔

SELECT
array_to_json(array_agg(row_to_json(T))) as buyer
FROM
(
SELECT
company_id,
company_name_cn,
company_status_id
FROM
company_profile
WHERE
company_id IN(
SELECT
regexp_split_to_table(buyer_id, ',')
FROM
model_1
)
) T

ElactisSearch


查询

and查询:must
or查询:should
模糊查询:wildcard(主要针对数字)
match(单字段模糊查找)
分词查询:queryString(限汉字,匹配所有字段)
范围查询:range
精确查询:term
in查询:terms

本地启动
bin下 start /b elasticsearch.bat
head start /b grunt server

服务器119启动
bin下 ./elasticsearch -d

java进制转换


//十进制转化为十六进制,结果为C8。

Integer.toHexString(200);

//十六进制转化为十进制,结果140。

Integer.parseInt("8C",16);