Elasticsearch 简单搜索查询案例

1.MySql表结构/数据

SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for user_lables
-- ----------------------------
DROP TABLE IF EXISTS `user_lables`;
CREATE TABLE `user_lables` (`id` varchar(255) DEFAULT NULL COMMENT '用户唯一标识',`age` varchar(255) DEFAULT NULL COMMENT '用户年龄',`sex` varchar(255) DEFAULT NULL COMMENT '用户性别 1:男,2:女',`tel` varchar(255) DEFAULT NULL COMMENT '联系电话',`is_high` varchar(255) DEFAULT NULL COMMENT '高价值用户 0:否,1:是',`final_by` varchar(255) DEFAULT NULL COMMENT '最后下单时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;-- ----------------------------
-- Records of user_lables
-- ----------------------------
INSERT INTO `user_lables` VALUES ('10001', '23', '1', '130xxxx1111', '1', '2022-09-11 22:10:12');
INSERT INTO `user_lables` VALUES ('10002', '33', '1', '157xxxx4506', '0', '2023-06-13 12:09:11');
INSERT INTO `user_lables` VALUES ('10003', '24', '2', '157xxxx4309', '1', '2023-07-15 22:19:21');
INSERT INTO `user_lables` VALUES ('10004', '16', '1', '151xxxx5516', '1', '2023-06-29 12:29:31');
INSERT INTO `user_lables` VALUES ('10005', '18', '1', '152xxxx4506', '1', '2023-07-17 17:39:41');
INSERT INTO `user_lables` VALUES ('10006', '19', '2', '153xxxx4506', '0', '2023-07-18 19:49:51');
INSERT INTO `user_lables` VALUES ('10007', '20', '2', '157xxxx4506', '1', '2023-07-18 23:59:11');
INSERT INTO `user_lables` VALUES ('10008', '23', '1', '189xxxx4506', '0', '2023-06-14 16:39:51');
INSERT INTO `user_lables` VALUES ('10009', '36', '2', '137xxxx4506', '1', '2023-06-15 13:29:41');
INSERT INTO `user_lables` VALUES ('10010', '45', '2', '130xxxx4506', '1', '2023-06-29 09:19:31');
INSERT INTO `user_lables` VALUES ('10011', '30', '1', '157xxxx4506', '1', '2023-07-14 21:09:21');
INSERT INTO `user_lables` VALUES ('10012', '33', '2', '157xxxx4506', '0', '2023-07-13 22:29:11');
INSERT INTO `user_lables` VALUES ('10013', '29', '2', '157xxxx4516', '0', '2023-07-13 23:23:21');
INSERT INTO `user_lables` VALUES ('10014', '28', '1', '157xxxx5516', '0', '2023-07-13 23:22:21');

2.Java 实体类

2.1 user_lables表实体

public class User_lables {private String id;private String age;private String sex;private String tel;private String is_high;private String final_by;public String getId() {return id;}public void setId(String id) {this.id = id;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getTel() {return tel;}public void setTel(String tel) {this.tel = tel;}public String getIs_high() {return is_high;}public void setIs_high(String is_high) {this.is_high = is_high;}public String getFinal_by() {return final_by;}public void setFinal_by(String final_by) {this.final_by = final_by;}public User_lables(String id, String age, String sex, String tel, String is_high, String final_by) {this.id = id;this.age = age;this.sex = sex;this.tel = tel;this.is_high = is_high;this.final_by = final_by;}public User_lables() {}@Overridepublic String toString() {return "User_lables{" +"id='" + id + '\'' +", age='" + age + '\'' +", sex='" + sex + '\'' +", tel='" + tel + '\'' +", is_high='" + is_high + '\'' +", final_by='" + final_by + '\'' +'}';}

2.2 查询参数实体

public class Es_bean {private String name;private String type;private String value;public String getName() {return name;}public void setName(String name) {this.name = name;}public String getType() {return type;}public void setType(String type) {this.type = type;}public String getValue() {return value;}public void setValue(String value) {this.value = value;}
}

3.整体代码

package com.jinshan.datacenter.estest;/*** @author MR.Liu* @version 1.0* @data 2023-07-20 11:19*/
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.gson.Gson;
import com.jinshan.datacenter.es.Es_bean;
import com.jinshan.datacenter.es.User_lables;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.http.HttpHost;
import org.elasticsearch.action.bulk.BulkRequest;
import org.elasticsearch.action.bulk.BulkResponse;
import org.elasticsearch.action.index.IndexRequest;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestClient;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.common.xcontent.XContentType;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.index.query.RangeQueryBuilder;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.SearchHits;
import org.elasticsearch.search.builder.SearchSourceBuilder;import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;public class MySQLDemo {// MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URLstatic final String JDBC_DRIVER = "com.mysql.jdbc.Driver";static final String DB_URL = "jdbc:mysql://localhost:3306/test";// MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL//static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";//static final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";// 数据库的用户名与密码，需要根据自己的设置static final String USER = "root";static final String PASS = "000000";public static Connection GetConn(){Connection conn = null;try{// 注册 JDBC 驱动Class.forName(JDBC_DRIVER);// 打开链接System.out.println("连接数据库...");conn = DriverManager.getConnection(DB_URL,USER,PASS);// 执行查询System.out.println(" 实例化Statement对象...");}catch(SQLException se){// 处理 JDBC 错误se.printStackTrace();} catch (ClassNotFoundException e) {throw new RuntimeException(e);}return conn;}public static void InsertDoc(RestHighLevelClient client, String indexName , List<User_lables> list) throws IOException {// 批量导入数据BulkRequest request = new BulkRequest();// 添加索引请求到批量请求中for (int i =0;i<list.size();i++){Gson gson = new Gson();String json = gson.toJson(list.get(i));request.add(new IndexRequest(indexName).id(String.valueOf(i)).source(json, XContentType.JSON));}// 发送批量请求try {BulkResponse response = client.bulk(request, RequestOptions.DEFAULT);if (response.hasFailures()) {System.out.println("批量导入数据失败：" + response.buildFailureMessage());} else {System.out.println("批量导入数据成功");}} catch (IOException e) {e.printStackTrace();}}public static void main(String[] args) throws SQLException, IOException {// 创建客户端对象RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("localhost", 9200, "http")));Connection conn = GetConn();Statement stat=conn.createStatement();String sql = "select * from user_lables";ResultSet rs = stat.executeQuery(sql);BeanListHandler<User_lables> bh =new BeanListHandler<User_lables>(User_lables.class);//rs是ResultSet得到的从返回集合List<User_lables> handle = bh.handle(rs);//InsertDoc(client,"user_test1",handle);String data = "{\n" +"  \"selectedTags\": [\n" +"    {\n" +"      \"effect\": \"user\",\n" +"      \"label\": \"男性\",\n" +"      \"name\": \"sex\",\n" +"      \"value\": \"1\",\n" +"      \"type\": \"match\"\n" +"    },\n" +"    {\n" +"      \"effect\": \"user\",\n" +"      \"label\": \"10~50\",\n" +"      \"name\": \"age\",\n" +"      \"value\": \"20-30\",\n" +"      \"type\": \"rangeBoth\"\n" +"    },\n" +"    {\n" +"      \"effect\": \"user\",\n" +"      \"label\": \"高质量用户\",\n" +"      \"name\": \"is_high\",\n" +"      \"value\": \"0\",\n" +"      \"type\": \"match\"\n" +"    }\n" +"  ]\n" +"}";JSONObject object = JSON.parseObject(data);JSONArray array = object.getJSONArray("selectedTags");List<Es_bean> list = array.toJavaList(Es_bean.class);SearchRequest request = new SearchRequest();request.indices("user_test1");request.types("_doc");SearchSourceBuilder builder = new SearchSourceBuilder();request.source(builder);String[] includes = {"id", "age","sex","tel","is_high","final_by"};builder.fetchSource(includes, null);builder.from(0);builder.size(1000);BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();for (int i=0;i<list.size();i++) {String name = list.get(i).getName();String value = list.get(i).getValue();String type = list.get(i).getType();if (type.equals("match")) {boolQueryBuilder.must(QueryBuilders.matchQuery(name, value));}if (type.equals("rangeBoth")) {String[] split = value.split("-");String v1 = split[0];String v2 = split[1];boolQueryBuilder.must(QueryBuilders.rangeQuery(name).lte(v2).gte(v1));}}builder.query(boolQueryBuilder);request.source(builder);RequestOptions options = RequestOptions.DEFAULT;List<User_lables> memberTags = new ArrayList<>();try {SearchResponse search = client.search(request, options);SearchHits hits = search.getHits();Iterator<SearchHit> iterator = hits.iterator();while (iterator.hasNext()) {SearchHit hit = iterator.next();String sourceAsString = hit.getSourceAsString();User_lables memberTag = JSON.parseObject(sourceAsString, User_lables.class);System.out.println(memberTag);memberTags.add(memberTag);}} catch (IOException e) {e.printStackTrace();}conn.close();client.close();}
}