How to get nextVal of sequence that works for H2 as well as MariaDB-CodePudding

I use MariaDB as my production database and a H2-Database for testing purposes.

My Problem: I have a query to get the next value of a sequence which looks like this:

Query query = this.entitymanager.createNativeQuery("select nextval(seq_stayId)");

This works fine on MariaDB but it does not work on the H2-Database. However, if I change the query to the following:

Query query = this.entitymanager.createNativeQuery("select nextval('seq_stayId')");

it works on the H2-Database but not on MariaDB. I know that MariaDB does not like these String literals but how can I find a solution, that works on both databases?

I would be very glad if you could help me out. Thank you in advance!

CodePudding user response：

select next value for seq_stayId is supported by both H2 and MariaDB.

NEXT VALUE FOR sequenceName is a part of the SQL Standard, you really should use it instead of various vendor-specific expressions, especially when you need to execute the same SQL in different database systems.

CodePudding user response：

you can create multiple profile properties in spring boot and manage values in properties file

1: application-mariadb.properties

next.value.native.query=select nextval(seq_stayId)

2: application-h2db.properties

next.value.native.query=select nextval('seq_stayId')

then you can bind this property using @Value annotation in your required class

@Component
    class YouClassName{
        @Value("next.value.native.query")
        private String nextValueQuery;

        public void whatEverIsYourMethodName() {
            Query query = this.entitymanager.createNativeQuery(nextValueQuery);
        }

    }

Then you can active your DB profile based on your required env. Also if you have DB specific to existing dev/prod/test or any other env then you can use that property directly in existing env properties files