代码之家 › 专栏 › 技术社区 › ip696

JPA/Hibernate5按序列名称获取序列nextval

sequence jpa hibernate java

ip696 · 技术社区 · 6 年前

sequence nextval JPA 或 Hibernate 5 通过 sequence name

我有以下步骤 TEST_SEQ 在里面 Oracle DB和 ANOTHER_NAME_SEQ Postgresql 分贝。

我需要一个签名如下的方法

public Long getSequenceByName(String sequenceName){}

nextval

我有几个主意,但不合适。

1) 将每个DB的本机查询存储在properties中,并按以下方式编写方法:

@Value("${query}")//"SELECT {name}.NEXTVAL FROM DUAL"
private StringQuery;

public Long getSequenceByName(String sequenceName){
    uery q = em.createNativeQuery(StringQuery.replace("{name}", sequenceName));
    return (java.math.BigDecimal) q.getSingleResult();
  }

但我需要用占位符存储查询字符串,并将占位符替换为序列名称,为每个数据库存储查询。

2) 创建只有一个字段的实体 @Id . 插入实体和getId(序列值)。

但如果在不同的数据库中是不同的序列名-???

3) 使用 this

编辑:

我尝试以下解决方案:

@Component
public class SequenseRepository {

    @PersistenceContext
    private EntityManager em;

    @Transactional
    public Long getID(final String sequenceName) {
        final List<Long> ids = new ArrayList<>(1);

        Session session = em.unwrap(Session.class);
        session.doWork(connection -> {
            DialectResolver dialectResolver = new StandardDialectResolver();
            Dialect dialect =  dialectResolver.resolveDialect((DialectResolutionInfo) connection.getMetaData());
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                preparedStatement = connection.prepareStatement( dialect.getSequenceNextValString(sequenceName));
                resultSet = preparedStatement.executeQuery();
                resultSet.next();
                ids.add(resultSet.getLong(1));
            }catch (SQLException e) {
                throw e;
            } finally {
                if(preparedStatement != null) {
                    preparedStatement.close();
                }
                if(resultSet != null) {
                    resultSet.close();
                }
            }
        });

        return ids.get(0);
    }
}

我得到了例外:

java.lang.ClassCastException: oracle.jdbc.driver.OracleDatabaseMetaData cannot be cast to org.hibernate.engine.jdbc.dialect.spi.DialectResolutionInfo&#xd;

1 回复 | 直到 6 年前

ip696 6 年前

由于这篇文章,我找到了解决办法 enter link description here

    public interface SequenceRepository {
    int getNext(String sequenceName);
   }

以及每个数据库的实现:

@Profile("oracle")
@Component("oracleSequenceRepository")
public class OracleSequenceRepository implements SequenceRepository{

    private final DataSource dataSource;

    @Autowired
    public OracleSequenceRepository(@Qualifier("dataSource") DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Transactional(readOnly = true)
    @Override
    public int getNext(String sequenceName) {
        AbstractSequenceMaxValueIncrementer incr = new OracleSequenceMaxValueIncrementer(this.dataSource, sequenceName);
        return incr.nextIntValue();
    }
}

和

@Profile("postgre")
@Component("postgresSequenceRepository")
public class PostgreSequenceRepository implements SequenceRepository{

    private final DataSource dataSource;

    @Autowired
    public PostgreSequenceRepository(@Qualifier("dataSource") DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Transactional(readOnly = true)
    @Override
    public int getNext(String sequenceName) {
        AbstractSequenceMaxValueIncrementer incr = new PostgresSequenceMaxValueIncrementer(this.dataSource, sequenceName);
        return incr.nextIntValue();
    }
}