时间戳

首页 > 解决方案 > SpringBoot-使用PostgreSQL的多个参数的单值

postgresql - SpringBoot-使用PostgreSQL的多个参数的单值

问题描述

PostgreSQL 查询

select date,count(*) from table1 
where (DATE_PART('day', current_date::timestamp - table1.date::timestamp )) 
<(SELECT value from keyvaluepair where priority='Important')
and priority = 'Important' group by date
order by date

我将从哪里调用 api

http://localhost:8080/get/getDates?priority=Important

我会通过吗？如图所示在两个地方

select date,count(*) from table1 
    where (DATE_PART('day', current_date::timestamp - table1.date::timestamp)) 
    <(SELECT value from keyvaluepair where priority=?)
    and priority = ? group by date
    order by date

我收到这样的错误

No value specified for parameter 2.; nested exception is org.postgresql.util.PSQLException: No value specified for parameter 2.
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback;

在java中，我只是得到这个查询数据的日期和计数。

public Map<String,Object> getDates(String priority) {
        Map<String,Object> map = new Map<String,Object>();
        List<Object> count = new ArrayList<Object>();
        List<Object> date = new ArrayList<Object>();
        try {

            jdbcTemplate.query(query, new Object[] { priority }, new RowMapper<Object>() {

                @Override
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    date.add(rs.getString("date"));
                    count.add(rs.getInt("count"));
                    return null;
                }

            });
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return map;
    }

如何在查询中赋予一次优先级并使用两次？

标签： postgresqlspring-boot

解决方案

select t1.date,count(*) from table1 t1 
where (DATE_PART('day', current_date::timestamp - t1.date::timestamp )) 
<(SELECT value from keyvaluepair k where k.priority=t1.priority)
and t1.priority = 'Important' group by t1.date
order by t1.date

查询中的这种更改帮助我解决了我遇到的错误，如图所示 No value specified for parameter 2.; 嵌套异常是 org.postgresql.util.PSQLException: No value specified for parameter 2. org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; 其中表的两个列名相同，我必须为所有参数传递一次值，如下面的链接所示

http://localhost:8080/get/getDates?priority=Important

select t1.date,count(*) from table1 t1 
    where (DATE_PART('day', current_date::timestamp - t1.date::timestamp )) 
    <(SELECT value from keyvaluepair k where k.priority=t1.priority)
    and t1.priority = ? group by t1.date
    order by t1.date

在此链接中，重要的单个值用于查询它用于相同列名的两个表参数的位置。

推荐阅读