Java SQL 数据库返回数据类型 Object 的结果

Question

我正在查询一个 H2 数据库，我得到了一个对象类型的结果列表，但我无法将它们转换为正确的格式:

我正在查询的 VIEW 表:

SELECT * FROM STATEMENTS_DATAPOINTS;
NAME                  DATA_POINTS_ID    DATE        F1      PRECISON    RECALL  
identifiedNeed          1               2017-11-19  0.3     0.5         0.2
identifiedNeed          2               2017-11-12  0.7     0.4         0.15
identifiedNeed          3               2017-11-15  0.5     0.3         0.1 
identifiedNeed          4               2017-11-18  0.6     0.2         0.05
identifiedNeed          5               2017-11-13  0.1     0.2         0.05
identifiedNeed          6               2017-11-14  0.2     0.2         0.05
identifiedNeed          7               2017-11-16  0.4     0.2         0.05
identifiedBackground    8               2017-11-19  0.2     0.4         0.2
identifiedBackground    9               2017-11-12  0.4     0.3         0.15
identifiedBackground    10              2017-11-15  0.5     0.2         0.1
identifiedBackground    11              2017-11-18  0.6     0.1         0.05
identifiedBackground    12              2017-11-13  0.65    0.1         0.05
identifiedBackground    13              2017-11-14  0.77    0.1         0.05
identifiedBackground    14              2017-11-16  0.8     0.1         0.05
identifiedGoal          15              2017-11-19  0.1     0.4         0.2
identifiedGoal          16              2017-11-12  0.4     0.3         0.15
identifiedGoal          17              2017-11-15  0.3     0.2         0.1
identifiedGoal          18              2017-11-13  0.2     0.1         0.05
identifiedGoal          19              2017-11-12  0.5     0.2         0.15
identifiedGoal          20              2017-11-11  0.6     0.3         0.2

(20 rows, 2 ms)

我从 Java 程序内部查询，我得到的结果是:

[[Ljava.lang.Object;@328bbd7, [Ljava.lang.Object;@3a89e146, [Ljava.lang.Object;@340d5ed1, [Ljava.lang.Object;@14d6920b, [Ljava.lang.Object;@52667545, [Ljava.lang.Object;@27103d9e, [Ljava.lang.Object;@79b5147b]

这是表中的 20 个对象。我试过 toString() 和 toArray() 但它们不起作用，我该怎么办？我的目标是将所有这些点放在一个列表中。

带有查询方法的接口(interface)类:

public interface MeasurementRepository extends CrudRepository<Measurement, Long> {

@Query(value = "SELECT \n" +
        "    measurement_point.date\n" +
        "    , measurement_point.f1\n" +
        "    , measurement_point.precison\n" +
        "    , measurement_point.recall\n" +
        "FROM measurement \n" +
        "LEFT JOIN measurement_data_points ON measurement.id = measurement_data_points.measurement_id\n" +
        "LEFT JOIN measurement_point ON measurement_data_points.data_points_id = measurement_point.id WHERE name='identifiedNeed';", nativeQuery = true)
List<Double> findAllMeasurementDataPoints();

我可以将 List 更改为类型 String、Integer、Double、Objects，我仍然得到相同的结果。

使用 JdbcTemplate 创建新类来查询:

public class DatabaseQueries {


@Autowired
JdbcTemplate jdbcTemplate;

public List<MeasurementPoint> findAllNeedDataPoints() {

    List<MeasurementPoint> needStatementDataPoints = new ArrayList<>();

    String query="SELECT  measurement_point.date,measurement_point.f1, measurement_point.precison, measurement_point.recall\n" +
            "FROM measurement \n" +
            "LEFT JOIN measurement_data_points ON measurement.id = measurement_data_points.measurement_id\n" +
            "LEFT JOIN measurement_point ON measurement_data_points.data_points_id = measurement_point.id;";

    List<Map<String, Object>> rows=jdbcTemplate.queryForList(query);
    for(java.util.Map<String, Object> row :rows) {
        MeasurementPoint measurementPoint = new MeasurementPoint();
        measurementPoint.setDate((String)(row.get("DATE")));
        measurementPoint.setF1((Double)(row.get("F1")));
        measurementPoint.setPrecison((Double)(row.get("PRECISON")));
        measurementPoint.setRecall((Double)(row.get("RECALL")));
        needStatementDataPoints.add(measurementPoint);
    }
    return needStatementDataPoints;
}

我遇到了 NullPointerException，它找不到任何值，我错过了什么吗？错误点:

List<Map<String, Object>> rows=jdbcTemplate.queryForList(query);

我现在得到的结果是:

[com.ibm.fk.textanalys.MeasurementPoint@58e52b28, com.ibm.fk.textanalys.MeasurementPoint@fa8c696, com.ibm.fk.textanalys.MeasurementPoint@5b06cf3c, com.ibm.fk.textanalys.MeasurementPoint@3d89bc9e, com.ibm.fk.textanalys.MeasurementPoint@4cc46c6c, com.ibm.fk.textanalys.MeasurementPoint@5919f14, com.ibm.fk.textanalys.MeasurementPoint@6883cfb5, com.ibm.fk.textanalys.MeasurementPoint@189ee377, com.ibm.fk.textanalys.MeasurementPoint@77d64b04, com.ibm.fk.textanalys.MeasurementPoint@7e3603d5, com.ibm.fk.textanalys.MeasurementPoint@8be95c1, com.ibm.fk.textanalys.MeasurementPoint@67d44afa, com.ibm.fk.textanalys.MeasurementPoint@221fb36f, com.ibm.fk.textanalys.MeasurementPoint@6cf92452, com.ibm.fk.textanalys.MeasurementPoint@1015a491, com.ibm.fk.textanalys.MeasurementPoint@55d3aff3, com.ibm.fk.textanalys.MeasurementPoint@a08faed, com.ibm.fk.textanalys.MeasurementPoint@2f76844a, com.ibm.fk.textanalys.MeasurementPoint@127a9c19, com.ibm.fk.textanalys.MeasurementPoint@5a9e0592]

我现在得到的新结果:

[MeasurementPoint{f1=0.3, precison=0.5, recall=0.2, date='2017-11-19'}, MeasurementPoint{f1=0.7, precison=0.4, recall=0.15, date='2017-11-12'}, MeasurementPoint{f1=0.5, precison=0.3, recall=0.1, date='2017-11-15'}, MeasurementPoint{f1=0.6, precison=0.2, recall=0.05, date='2017-11-18'}, MeasurementPoint{f1=0.1, precison=0.2, recall=0.05, date='2017-11-13'}, MeasurementPoint{f1=0.2, precison=0.2, recall=0.05, date='2017-11-14'}, MeasurementPoint{f1=0.4, precison=0.2, recall=0.05, date='2017-11-16'}, MeasurementPoint{f1=0.2, precison=0.4, recall=0.2, date='2017-11-19'}, MeasurementPoint{f1=0.4, precison=0.3, recall=0.15, date='2017-11-12'}, MeasurementPoint{f1=0.5, precison=0.2, recall=0.1, date='2017-11-15'}, MeasurementPoint{f1=0.6, precison=0.1, recall=0.05, date='2017-11-18'}, MeasurementPoint{f1=0.65, precison=0.1, recall=0.05, date='2017-11-13'}, MeasurementPoint{f1=0.77, precison=0.1, recall=0.05, date='2017-11-14'}, MeasurementPoint{f1=0.8, precison=0.1, recall=0.05, date='2017-11-16'}, MeasurementPoint{f1=0.1, precison=0.4, recall=0.2, date='2017-11-19'}, MeasurementPoint{f1=0.4, precison=0.3, recall=0.15, date='2017-11-12'}, MeasurementPoint{f1=0.3, precison=0.2, recall=0.1, date='2017-11-15'}, MeasurementPoint{f1=0.2, precison=0.1, recall=0.05, date='2017-11-13'}, MeasurementPoint{f1=0.5, precison=0.2, recall=0.15, date='2017-11-12'}, MeasurementPoint{f1=0.6, precison=0.3, recall=0.2, date='2017-11-11'}]

Answer 1

public interface MeasurementRepository extends CrudRepository<Measurement, Long> {

    @Query(value = "SELECT date, f1, precison, recall " +
          "FROM measurement " +
          "LEFT JOIN measurement_data_points ON measurement.id = measurement_data_points.measurement_id " +
          "LEFT JOIN measurement_point ON measurement_data_points.data_points_id = measurement_point.id WHERE name='identifiedNeed';", nativeQuery = true)
    List<Measurement> findAllMeasurementDataPoints();
}

但是推荐你使用JdbcTemplate为您查询并将其与自定义映射器一起使用。 jdbcTemplate.query(your_query, (rs, rowNumber) -> { ... your mapping here ... })

其中 (rs, rowNumber) 是 RowMapper<T> 的 lambda 表达式界面。

@Service
public class MeasurementRepository {
    @Autowired
    JdbcTemplate template;

    public List<Measurement> findAllMeasurementDataPoints() {
         template.query(your_query, (rs, rowNumber) -> {
              Measurement measurement = new Measurement();
              ... your mapping here ....
              return measurement;
         })
    }