Spring Data JDBC and SQLite

Question

i have to code a new project where there is an existing SQLite datasource.
I have basically two questions:

1. Does Spring Data JDBC support SQLite? The listing here does not explicitly state so: https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#requirements
2. As it is not "officially" supported out of the box, is it even recommended to use it (SQLite) via own dialect?

I could do some import/export jobs from sqlite to let´s say postgres for example with no trouble at all.

Answer 1

1. SQLite is not supported out of the box.

2. I'm not familiar with SQLite, but Spring Data JDBC currently doesn't require any fancy SQL features. And after a quick check of the documentation I'm confident that you should be fine with your own dialect. And it is the intended way to use databases with Spring Data JDBC, that aren't supported out of the box.

You could even make the dialect open source and publish it on Maven, so others could benefit from it as well.

Answer 2

I've developed https://github.com/komamitsu/spring-data-sqlite to use SQLite via Spring Data JDBC interface. I think you can easily use it. It would be great if you try it.

Answer 3

This may not be the correct solution, so please correct me in case I did something wrong as I don't understand the concepts entirely. But this worked for me:

import java.util.Optional;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jdbc.core.convert.JdbcCustomConversions;
import org.springframework.data.jdbc.core.mapping.JdbcMappingContext;
import org.springframework.data.jdbc.repository.config.AbstractJdbcConfiguration;
import org.springframework.data.relational.core.mapping.NamingStrategy;

@Configuration
public class SQLiteJdbcDialectProvider extends AbstractJdbcConfiguration {

    @Bean
    @Override
    public JdbcMappingContext jdbcMappingContext(Optional<NamingStrategy> namingStrategy,
            JdbcCustomConversions customConversions) {

        JdbcMappingContext mappingContext = super.jdbcMappingContext(namingStrategy, customConversions);
        mappingContext.setForceQuote(false);

        return mappingContext;
    }

}

I then copied the MySQL Dialect into the SQLite Dialect class:

import org.springframework.data.relational.core.dialect.AbstractDialect;
import org.springframework.data.relational.core.dialect.ArrayColumns;
import org.springframework.data.relational.core.dialect.H2Dialect;
import org.springframework.data.relational.core.dialect.LimitClause;
import org.springframework.data.relational.core.dialect.LockClause;
import org.springframework.data.relational.core.sql.LockOptions;

public class SQLiteDialect extends AbstractDialect {

    public static final SQLiteDialect INSTANCE = new SQLiteDialect();

    
    private static final LimitClause LIMIT_CLAUSE = new LimitClause() {

        @Override
        public String getLimit(long limit) {
            return "LIMIT " + limit;
        }

        @Override
        public String getOffset(long offset) {
            return String.format("LIMIT %d, 18446744073709551615", offset);
        }

        @Override
        public String getLimitOffset(long limit, long offset) {

            return String.format("LIMIT %s, %s", offset, limit);
        }

        @Override
        public Position getClausePosition() {
            return Position.AFTER_ORDER_BY;
        }
    };
    
    @Override
    public LockClause lock() {
        return LOCK_CLAUSE;
    }

    @Override
    public ArrayColumns getArraySupport() {
        return ArrayColumns.Unsupported.INSTANCE;
    }

    private static final LockClause LOCK_CLAUSE = new LockClause() {

        @Override
        public String getLock(LockOptions lockOptions) {
            return "WITH LOCK";
        }

        @Override
        public Position getClausePosition() {
            return Position.AFTER_ORDER_BY;
        }
    };

    @Override
    public LimitClause limit() {
        return LIMIT_CLAUSE;
    }
}

Then I created a new File under:

src\main\resources\META-INF\spring.factories

With the line:

org.springframework.data.jdbc.repository.config.DialectResolver$JdbcDialectProvider=your.package.SQLiteDialectResolver

In the end I got an ArrayIndexOutOfBoundsException while calling queryForList with the JdbcTemplate, so I used the NamedParameterJdbcTemplate.