java - 序列 "testsequence"尚未在此 session 中的 postgresql java 中定义

Question

我正在使用带有 google juice 作为注入(inject)器的 java 应用程序以及作为数据库层的 jdbi。我需要使用 java 代码从数据库中获取序列。

我在数据库中有应用程序的表，未使用哪个序列。但出于其他目的，我需要序列值作为当前、下一个和最后一个。

尽管以下查询在 pg-admin 中有效

select currval('testsequence'); 

select nextval('testsequence'); 

select last_value FROM testsequence;

代码如下。

public long getCurrentSequenceFromDB()
{
    LOGGER.info("SequenceHelper :getCurrentSequenceFromDBSTART");
    try (Handle handle = jdbi.open())
    {
        SequenceDAO squenceDAO = handle.attach(SequenceDAO.class);
        long sequence = squenceDAO.getCurrentSequence();
        if (sequence != 0)
        {
            LOGGER.info("SequenceHelper :getCurrentSequenceFromDBEND");
            return sequence;
        }
        else
        {
            LOGGER.info("SequenceHelper :getCurrentSequenceFromDBsequence sequence is null");
            return 0;
        }

    }
    catch (Exception e)
    {
        LOGGER.error("Error in getting sequence from database", e);
        throw new SignageServiceException(ErrorCodes.UNEXPECTED_ERROR, "Error in getting sequence from database", e);
    }
}

在查询级别为:

@SqlQuery("select currval('public.testsequence')")
public long getCurrentSequence();

我得到的错误是

Caused by: org.postgresql.util.PSQLException: 
ERROR: currval of sequence "testsequence" is not yet defined in this session

Answer 1

doc说

currval

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

所以您需要先在这个 session 中调用 nextval。

否则，如果您想要任何 session 的最后一个值，您可以使用

SELECT last_value FROM sequence_name;