H2 Database: How to have lowercase for tablename?

Question

• I want to keep all my tablenames as lowercase. example person

• I use Liquibase to setup my database and it looks like

  <databaseChangeLog
          xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
           http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
      <changeSet author="harit" id="09222014.0639">
          <comment>Add Person Table</comment>
          <createTable tableName="person">
              <column name="id" type="INT"/>
              <column name="firstName" type="varchar(255)"/>
              <column name="lastName" type="varchar(255)"/>
          </createTable>
      </changeSet>
  </databaseChangeLog>  
  

I use H2 database to run my integration tests and set up in pom.xml as

   <cargo.datasource.datasource.earth>
        cargo.datasource.driver=${h2.driver}|
        cargo.datasource.url=${datasource.url}|
        cargo.datasource.jndi=${datasource.jndi}|
        cargo.datasource.username=${h2.user}|
        cargo.datasource.password=${h2.user}
    </cargo.datasource.datasource.earth>

where datasource.url looks like

<datasource.url>jdbc:h2:${project.build.directory}/earth;DATABASE_TO_UPPER=false</datasource.url>

When I run mvn, I see

INFO 9/22/14 3:32 PM:liquibase: Successfully acquired change log lock
INFO 9/22/14 3:32 PM:liquibase: Dropping Database Objects in schema: UNIT-TESTING-PU.PUBLIC
INFO 9/22/14 3:32 PM:liquibase: Creating database history table with name: PUBLIC.DATABASECHANGELOG
INFO 9/22/14 3:32 PM:liquibase: Creating database history table with name: PUBLIC.DATABASECHANGELOG
INFO 9/22/14 3:32 PM:liquibase: Successfully released change log lock
INFO 9/22/14 3:32 PM:liquibase: Successfully acquired change log lock
INFO 9/22/14 3:32 PM:liquibase: Reading from PUBLIC.DATABASECHANGELOG
INFO 9/22/14 3:32 PM:liquibase: Reading from PUBLIC.DATABASECHANGELOG
INFO 9/22/14 3:32 PM:liquibase: Reading from PUBLIC.DATABASECHANGELOG
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/01.xml::09222014.0639::harit: Reading from PUBLIC.DATABASECHANGELOG
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/01.xml::09222014.0639::harit: Table person created
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/01.xml::09222014.0639::harit: ChangeSet liquibase/2014/01.xml::09222014.0639::harit ran successfully in 1ms
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/01.xml::09222014.0639::harit: Reading from PUBLIC.DATABASECHANGELOG
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/02.xml::09222014.0844::harit: Reading from PUBLIC.DATABASECHANGELOG
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/02.xml::09222014.0844::harit: New row inserted into person
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/02.xml::09222014.0844::harit: New row inserted into person
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/02.xml::09222014.0844::harit: New row inserted into person
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/02.xml::09222014.0844::harit: New row inserted into person
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/02.xml::09222014.0844::harit: New row inserted into person
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/02.xml::09222014.0844::harit: ChangeSet liquibase/2014/02.xml::09222014.0844::harit ran successfully in 3ms
INFO 9/22/14 3:32 PM:liquibase: liquibase/changelog.xml: liquibase/2014/02.xml::09222014.0844::harit: Reading from PUBLIC.DATABASECHANGELOG
INFO 9/22/14 3:32 PM:liquibase: Successfully released change log lock
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 1.929 sec
Running com.learner.integration.HelloIT
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.443 sec
Running com.learner.integration.PersonsIT
[INFO] [talledLocalContainer] 15:32:59,145 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-15) SQL Error: 42102, SQLState: 42S02
[INFO] [talledLocalContainer] 15:32:59,146 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-15) Table "Person" not found; SQL statement:
[INFO] [talledLocalContainer] select person0_.id as id1_0_, person0_.firstName as firstNam2_0_, person0_.lastName as lastName3_0_ from Person person0_ [42102-181]
[INFO] [talledLocalContainer] 15:32:59,153 ERROR [org.jboss.as.ejb3.invocation] (default task-15) JBAS014134: EJB Invocation failed on component PersonManager for method public java.util.List com.learner.business.manager.PersonManager.getPersons(): javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
[INFO] [talledLocalContainer]   at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleExceptionInOurTx(CMTTxInterceptor.java:190) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
[INFO] [talledLocalContainer]   at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:275) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
[INFO] [talledLocalContainer]   at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:340) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
[INFO] [talledLocalContainer]   at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:239) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
[INFO] [talledLocalContainer]   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)
[INFO] [talledLocalContainer]   at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) [wildfly-ejb3-8.0.0.Final.jar:8.0.0.Final]
[INFO] [talledLocalContainer]   at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

As you can see, liquibase created person table and I used DATABASE_TO_UPPER=false for H2 which made it from PERSON to Person

Question
How can I tell H2 that my tables are all lowercase -> person and not Person?

Plebs

Javadocs for h2 state that "Setting this to "false" is experimental" (emphasis mine). – Evangel

I'm not sure, who created the table? Liquibase? If yes, I guess you need to add the tag liquibase. – Mariammarian

@ThomasMueller, I'm also interested in having tables in my db's in lower-case instead of upper. Is there a possibility h2 could have a feature to either ignore case on queries or to create tables with lower case (reverse of present)? When writing SQL my IDE will complete everything in upper. – Gasbag

@BrettRyan not sure if I understand the problem and the reasons why you want to do that, I would probably ask a new question. – Mariammarian

lol, just saw you here and thought I'd cheat :) The issue arises when one uses the auto-complete feature of a sql editor (like in NetBeans). Inserting completed references will put them in the same case as the names in the schema. I have always preferred all lower case identifiers. – Gasbag

Answer 1

Use DATABASE_TO_LOWER=TRUE in h2 connection URL. Example :

url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;

Accentuate

if using with liquibase, consider using ;DATABASE_TO_UPPER=TRUE as it will help liquibase find your change log table – Christenson

Answer 2

Try to use in H2 connection URL setting "database_to_upper=false".

Rumpus