springboot+mybatis+mysql+elasticsearch+elasticsearch-sql

ZhiHao1 commented 5 years ago

返回对象集合时resulttype写什么

ZhiHao1 commented 5 years ago

`` <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

`` 返回对象可以拿到了，，可是嵌套的数组呢，有实践过的吗

shi-yuan commented 5 years ago

可以的：

{
  "mappings": {
    "test4": {
      "properties": {
        "arr": {
          "properties": {
            "a": {
              "type": "text",
              "fields": {
                "keyword": {
                  "ignore_above": 256,
                  "type": "keyword"
                }
              }
            },
            "b": {
              "type": "text",
              "fields": {
                "keyword": {
                  "ignore_above": 256,
                  "type": "keyword"
                }
              }
            },
            "c": {
              "type": "text",
              "fields": {
                "keyword": {
                  "ignore_above": 256,
                  "type": "keyword"
                }
              }
            }
          }
        },
        "name": {
          "analyzer": "dic_ansj",
          "type": "text"
        }
      }
    }
  }
}

<mapper namespace="test">
    <resultMap id="baseResult" type="ResultBean">
        <result column="name" property="name"/>
        <result column="_id" property="id"/>
        <collection property="arr" ofType="ArrBean">
            <result property="a" column="a"/>
            <result property="b" column="b"/>
            <result property="c" column="c"/>
        </collection>
    </resultMap>
    <select id="test2" parameterType="ParamBean" resultMap="baseResult">
        SELECT _id,name,arr.a.keyword a,arr.b.keyword b,arr.c.keyword c FROM test4
    </select>
</mapper>

ParamBean pb = new ParamBean();
pb.setName("test");
Object obj = testDao.test2(pb);
System.out.println(obj);

[ResultBean[name='本田Urban EV谍照表示其车型由概念车的三门版', id='1', arr=[]],
ResultBean[name='2222222222222222222', id='2', arr=[]], 
ResultBean[name='333333333333333', id='3', arr=[]], 
ResultBean[name='4444444444', id='4', arr=[]], 
ResultBean[name='555555', id='5', arr=[]], 
ResultBean[name='666666666', id='6', arr=[]], 
ResultBean[name='7777777777', id='7', arr=[]], 
ResultBean[name='88888888', id='8', arr=[]], 
ResultBean[name='999999999999', id='9', arr=[]], 
ResultBean[name='null', id='10', arr=[ArrBean[a='1111111111111', b='2222222222222', c='3333333333333']]]]

ZhiHao1 commented 5 years ago

按照你写的，我把select * 改成各个字段了，每个嵌套数组的属性值只查出来一组 "hobby": { "properties": { "v": { "type": "float" }, "n": { "type": "text", "fields": { "keyword": { "ignore_above": 256, "type": "keyword" } } } } } hobby下有两组，现在只能查出一组 "hobby": [{ "n": "book", "v": 21.5 }, { "n": "read", "v": 40.4 } ]

whzhangxw commented 5 years ago

@ZhiHao1 springboot+mybatis+elasticsearch-sql整合的配置文件能不能参考一下，Thks

ZhiHao1 commented 5 years ago

@whzhangxw ,我同时需要es和MySQL，所以定义了多数据源，以下是es的数据源配置 `` package cn.net.polyinfo.learning.config;

import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.ElasticSearchDruidDataSourceFactory; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource; import java.util.Properties;

import static com.alibaba.druid.pool.DruidDataSourceFactory.PROP_CONNECTIONPROPERTIES;

@Configuration @MapperScan(basePackages = "cn.net.polyinfo.learning.modules.esDao", sqlSessionTemplateRef = "esSqlSessionTemplate") public class ESDataSourceConfig {

@Value("${elasticsearchDB.url}")
private String url;

@Bean(name = "esDataSource")
public DataSource dataSource() {
    Properties properties = new Properties();
    properties.put("url", "jdbc:elasticsearch://" + url);
    properties.put(PROP_CONNECTIONPROPERTIES, "client.transport.ignore_cluster_name=true");

    DruidDataSource druidDataSource = null;
    try {
        druidDataSource = (DruidDataSource) ElasticSearchDruidDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return druidDataSource;
}

@Bean(name = "esSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("esDataSource") DataSource druidDataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(druidDataSource);
    bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/ElasticSearchDB/*.xml"));
    return bean.getObject();
}

// @Bean(name = "mysqlTransactionManager") // public DataSourceTransactionManager transactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) { // return new DataSourceTransactionManager(dataSource); // }

@Bean(name = "esSqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("esSqlSessionFactory") SqlSessionFactory sqlSessionFactory)
        throws Exception {
    return new SqlSessionTemplate(sqlSessionFactory);
}

} `` 然后其他类的配置和一般springboot+mybatis配置一样。我并没有给dao接口写实现类，而是service直接调的dao接口。我这边的问题是查询的时候，json下数组里有多组数据的话，只查出了一条，看代码执行sql的时mybatis。。。。突然有个想法，如果像 shi-yuan 一样也写一个dao实现类，通过 #768中dao实现类执行，会不会就是利用项目操作的sql，而并非mybatis，，整合的初衷只是为了解耦。

shi-yuan commented 5 years ago

上面实现确实是有问题的，这个需要自定义TypeHandler了，可以参考下哈：

<result column="arr" property="arr" javaType="ArrBean" typeHandler="CustomTypeHandler"/>

import com.alibaba.fastjson.JSON;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.Objects;

public class CustomTypeHandler<T> extends BaseTypeHandler<Collection<T>> {

    private Class<T> type;

    public CustomTypeHandler(Class<T> type) {
        this.type = Objects.requireNonNull(type);
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Collection<T> parameter, JdbcType jdbcType) throws SQLException {
        // TODO:
    }

    @Override
    public Collection<T> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return parse(rs.getObject(columnName));
    }

    @Override
    public Collection<T> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        // TODO:
        return null;
    }

    @Override
    public Collection<T> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        // TODO:
        return null;
    }

    private List<T> parse(Object obj) {
        return JSON.parseArray(JSON.toJSONString(obj), type);
    }
}

NLPchina / elasticsearch-sql

springboot+mybatis+mysql+elasticsearch+elasticsearch-sql #969