Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Play Framework: Error getting sequence nextval using H2 in-memory database

Tags:

jdbc

playframework-2.0

h2

ebean

As the title suggests, I get an error running Play 2.0.1 Tests using a FakeApplication w/ H2 in memory.

I set up a basic unit test:

public class ModelTest {
    @Test
    public void checkThatIndustriesExist() {
        running(fakeApplication(inMemoryDatabase()), new Runnable() {
            public void run() {
                Industry industry = new Industry();
                industry.name = "Some name";
                industry.shortname = "some-name";
                industry.save();
                assertThat(Industry.find.all()).hasSize(1);
            }
        });
    }

Which yields the following exception:

[info] test.ModelTest
[error] Test test.ModelTest.checkThatIndustriesExist failed: Error getting sequence nextval
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.getMoreIds(SequenceIdGenerator.java:213)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.loadMoreIds(SequenceIdGenerator.java:163)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.nextId(SequenceIdGenerator.java:118)
[error]     at com.avaje.ebeaninternal.server.deploy.BeanDescriptor.nextId(BeanDescriptor.java:1218)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.setIdGenValue(DefaultPersister.java:1304)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:403)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.saveEnhanced(DefaultPersister.java:345)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.saveRecurse(DefaultPersister.java:315)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.save(DefaultPersister.java:282)
[error]     at com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1577)
[error]     at com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1567)
[error]     at com.avaje.ebean.Ebean.save(Ebean.java:538)
[error]     at play.db.ebean.Model.save(Model.java:76)
[error]     at test.ModelTest$1.run(ModelTest.java:24)
[error]     at play.test.Helpers.running(Helpers.java:277)
[error]     at test.ModelTest.checkThatIndustriesExist(ModelTest.java:21)
[error]     ...
[error] Caused by: org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT INDUSTRY_SEQ.NEXTVAL UNION[*] SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL "; erwartet "identifier"
[error] Syntax error in SQL statement "SELECT INDUSTRY_SEQ.NEXTVAL UNION[*] SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL "; expected "identifier"; SQL statement:
[error] select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval [42001-158]
[error]     at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
[error]     at org.h2.message.DbException.get(DbException.java:169)
[error]     at org.h2.message.DbException.getSyntaxError(DbException.java:194)
[error]     at org.h2.command.Parser.readColumnIdentifier(Parser.java:2777)
[error]     at org.h2.command.Parser.readTermObjectDot(Parser.java:2336)
[error]     at org.h2.command.Parser.readTerm(Parser.java:2453)
[error]     at org.h2.command.Parser.readFactor(Parser.java:2035)
[error]     at org.h2.command.Parser.readSum(Parser.java:2022)
[error]     at org.h2.command.Parser.readConcat(Parser.java:1995)
[error]     at org.h2.command.Parser.readCondition(Parser.java:1860)
[error]     at org.h2.command.Parser.readAnd(Parser.java:1841)
[error]     at org.h2.command.Parser.readExpression(Parser.java:1833)
[error]     at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1746)
[error]     at org.h2.command.Parser.parseSelectSimple(Parser.java:1778)
[error]     at org.h2.command.Parser.parseSelectSub(Parser.java:1673)
[error]     at org.h2.command.Parser.parseSelectUnion(Parser.java:1518)
[error]     at org.h2.command.Parser.parseSelect(Parser.java:1506)
[error]     at org.h2.command.Parser.parsePrepared(Parser.java:405)
[error]     at org.h2.command.Parser.parse(Parser.java:279)
[error]     at org.h2.command.Parser.parse(Parser.java:251)
[error]     at org.h2.command.Parser.prepareCommand(Parser.java:217)
[error]     at org.h2.engine.Session.prepareLocal(Session.java:415)
[error]     at org.h2.engine.Session.prepareCommand(Session.java:364)
[error]     at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1119)
[error]     at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
[error]     at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
[error]     at com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:820)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.getMoreIds(SequenceIdGenerator.java:193)
[error]     ... 80 more

My model looks like this:

@Entity
@Table(name = "industry")
public class Industry extends Model {
    @Id public Long id;
    public String name;
    public String shortname;

    // called in the view to trigger lazy-loading
    public String getName() {
        return name;
    }

    public static Finder<Long, Industry> find = new Finder<Long, Industry>(Long.class, Industry.class);
}

... and finally the relevant part from my initial evolution:

create table industry (
    id        bigint not null,
    name      varchar(255),
    shortname varchar(255),
    constraint pk_industry primary key (id)
}

create sequence industry_seq start with 1000;

Everything works fine running on my PostgreSQL DB, and from my point of view the code is not any different from the Play2.0 Computer Database Sample.

I am happy for any help - thanks!

Regards, Alex

like image 874
aix Avatar asked Jun 03 '12 10:06

aix

3 Answers

My ids look like this, and I don't get that error...

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long id;
like image 28
Christopher Davies Avatar answered Sep 22 '22 08:09

Christopher Davies

Solved the problem, here's how:

  • before calling inMemoryDatabase() I provided my own config
  • the name of the database was 'test' and not 'default' as is used by calling inMemoryDatabase()
  • since I did not keep evolutions in conf/evolutions/test but only in conf/evolutions/default the schema hasn't been created
  • I had to clean the app, as the 'default' config wasn't applied after the change to inMemoryDatabase()
like image 55
aix Avatar answered Sep 20 '22 08:09

aix

Just using your code and a new Play 2.0.1 project I could not reproduce. Here is my code and config with imports. Since the classes are not the problem my guess would be that you switched off evolutions with evolutionplugin=disabled and your database evolution does never get applied, hence the "identifier expected" in your exception.

Edit disabling database evolutions, as stated above, yields the same exception that you encountered, see at the bottom.

Edit-2 the author of this blogpost describes how to apply the evolutions by hand. Doesn't look too sexy, but I you should only need to implement that base-class once per project. If someone knows something more elegant within the framework, do tell.

The Ebean Evolution (automatically generated):

create table industry (   
  id              bigint not null,  
  name            varchar(255),   shortname                
  varchar(255),   constraint pk_industry primary key (id)) ;
  create sequence industry_seq;
;

My models.Industry 

package models;

import play.db.ebean.Model;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "industry")
public class Industry extends Model {
    @Id
    public Long id;
    public String name;
    public String shortname;

    // called in the view to trigger lazy-loading
    public String getName() {
        return name;
    }

    public static Finder<Long, Industry> find 
           = new Model.Finder<Long, Industry>(Long.class, Industry.class);
}

My Unit-Test

package models;

import org.junit.Test;

import java.util.List;
import java.util.Map;

import static org.fest.assertions.Assertions.assertThat;
import static play.test.Helpers.fakeApplication;
import static play.test.Helpers.inMemoryDatabase;
import static play.test.Helpers.running;

public class ModelTest {

    @Test
    public void checkThatIndustriesExist() {
        running(fakeApplication(inMemoryDatabase()), new Runnable() {
            public void run() {
                Industry industry = new Industry();
                industry.name = "Some name";
                industry.shortname = "some-name";
                industry.save();
                assertThat(Industry.find.all()).hasSize(1);
            }
        });
    }

}

My appication.conf

application.secret="8aXG0?h`kxccxs6JM?WdB@<v`kwouvQr2<y5Y>9jk3XU1yHV`Yr>18xRKHv8PTdv"
application.langs="en"
ebean.default="models.*"
logger.root=ERROR
logger.play=INFO
logger.application=DEBUG

Test output:

[stackoverflow-10869508] $ test
[info] Updating {file:/Users/martin/workspace/stackoverflow-10869508/}stackoverflow-10869508...
[info] Done updating.                                                                  
[info] Compiling 4 Scala sources and 3 Java sources to /Users/martin/workspace/stackoverflow-10869508/target/scala-2.9.1/classes...
[info] Compiling 1 Java source to /Users/martin/workspace/stackoverflow-10869508/target/scala-2.9.1/test-classes...
[info] models.ModelTest
[info] + models.ModelTest.checkThatIndustriesExist
[info] 
[info] 
[info] Total for test models.ModelTest
[info] Finished in 2.289 seconds
[info] 1 tests, 0 failures, 0 errors
[info] Passed: : Total 1, Failed 0, Errors 0, Passed 1, Skipped 0

Test output disabling evolutions:

[stackoverflow-10869508] $ test
[info] Compiling 1 Java source to /Users/martin/workspace/stackoverflow-10869508/target/scala-2.9.1/test-classes...
[info] models.ModelTest
[error] Test models.ModelTest.checkThatIndustriesExist failed: Error getting sequence nextval
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.getMoreIds(SequenceIdGenerator.java:213)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.loadMoreIds(SequenceIdGenerator.java:163)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.nextId(SequenceIdGenerator.java:118)
[error]     at com.avaje.ebeaninternal.server.deploy.BeanDescriptor.nextId(BeanDescriptor.java:1218)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.setIdGenValue(DefaultPersister.java:1304)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:403)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.saveEnhanced(DefaultPersister.java:345)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.saveRecurse(DefaultPersister.java:315)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.save(DefaultPersister.java:282)
[error]     at com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1577)
[error]     at com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1567)
[error]     at com.avaje.ebean.Ebean.save(Ebean.java:538)
[error]     at play.db.ebean.Model.save(Model.java:76)
[error]     at models.ModelTest$1.run(ModelTest.java:22)
[error]     at play.test.Helpers.running(Helpers.java:277)
[error]     at models.ModelTest.checkThatIndustriesExist(ModelTest.java:17)
[error]     ...
[error] Caused by: org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT INDUSTRY_SEQ.NEXTVAL UNION[*] SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL "; erwartet "identifier"
[error] Syntax error in SQL statement "SELECT INDUSTRY_SEQ.NEXTVAL UNION[*] SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL "; expected "identifier"; SQL statement:
[error] select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval [42001-158]
[error]     at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
[error]     at org.h2.message.DbException.get(DbException.java:169)
[error]     at org.h2.message.DbException.getSyntaxError(DbException.java:194)
[error]     at org.h2.command.Parser.readColumnIdentifier(Parser.java:2777)
[error]     at org.h2.command.Parser.readTermObjectDot(Parser.java:2336)
[error]     at org.h2.command.Parser.readTerm(Parser.java:2453)
[error]     at org.h2.command.Parser.readFactor(Parser.java:2035)
[error]     at org.h2.command.Parser.readSum(Parser.java:2022)
[error]     at org.h2.command.Parser.readConcat(Parser.java:1995)
[error]     at org.h2.command.Parser.readCondition(Parser.java:1860)
[error]     at org.h2.command.Parser.readAnd(Parser.java:1841)
[error]     at org.h2.command.Parser.readExpression(Parser.java:1833)
[error]     at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1746)
[error]     at org.h2.command.Parser.parseSelectSimple(Parser.java:1778)
[error]     at org.h2.command.Parser.parseSelectSub(Parser.java:1673)
[error]     at org.h2.command.Parser.parseSelectUnion(Parser.java:1518)
[error]     at org.h2.command.Parser.parseSelect(Parser.java:1506)
[error]     at org.h2.command.Parser.parsePrepared(Parser.java:405)
[error]     at org.h2.command.Parser.parse(Parser.java:279)
[error]     at org.h2.command.Parser.parse(Parser.java:251)
[error]     at org.h2.command.Parser.prepareCommand(Parser.java:217)
[error]     at org.h2.engine.Session.prepareLocal(Session.java:415)
[error]     at org.h2.engine.Session.prepareCommand(Session.java:364)
[error]     at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1119)
[error]     at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
[error]     at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
[error]     at com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:820)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.getMoreIds(SequenceIdGenerator.java:193)
[error]     ... 80 more
[info] x models.ModelTest.checkThatIndustriesExist
[info] 
[info] 
[info] Total for test models.ModelTest
[info] Finished in 2.028 seconds
[info] 1 tests, 1 failures, 0 errors
[error] Failed: : Total 1, Failed 1, Errors 0, Passed 0, Skipped 0
[error] Failed tests:
[error]     models.ModelTest
[error] {file:/Users/martin/workspace/stackoverflow-10869508/}stackoverflow-10869508/test:test: Tests unsuccessful
[error] Total time: 3 s, completed 03.06.2012 18:34:39
like image 44
msung Avatar answered Sep 22 '22 08:09

msung
Sign in to Comment

Related questions

Postgres Copy with null date and integer fields
Is it possible to refer to column names via bind variables in Oracle?
JDBC connection hanging
Parameterized Oracle SQL query in Java?
Memory leak in JDBC4Connection
Weblogic Stuck Thread on JDBC call
Access Android sqlite database from development PC
transactions in jdbi
Can PostgreSQL COPY read CSV from a remote location?
Creating a database table if it does not exist in Java production code and confirming in JUnit
java.lang.ClassCastException: com.mchange.v2.c3p0.impl.NewProxyConnection
Which is the sybase driver(version) to be used in Weblogic 12c ? Where should I add the downloaded driver?
java.sql.SQLException: database connection closed
Why is JDBC dynamically loaded instead of imported? [duplicate]
JDBC: Insert timestamp in UTC
Auto generate key on JDBC insert in SQL Server
JDBC insert statement is not working
How do I set character encoding for Oracle 10g with JDBC
JDBC driver for Java 7
java.sql.SQLException: No data found

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!