[spring] spring boot에서 SQLite를 JPA로 사용하기

 

1. 개요

spring data jpa를 이용하는 Spring boot 프로젝트에서 SQLite를 사용하는 방법에 대해 작성합니다.

2. SQLite란?

공식사이트 : https://sqlite.org/index.html

DB-engines(H2 vs SQLite) : https://db-engines.com/en/system/H2%3BSQLite

SQLite는 서버로 띄우지 않고 애플리케이션에 포함되어 사용할 수 있는 DB입니다.

DB를 생성하면 파일 하나로 생성됩니다.

Spring boot에서는 비슷한 역할을 하는 H2 DB를 쉽게 사용할 수 있어서 SQLite를 잘 사용하지 않습니다.

하지만 SQLite는 DB-engines overall ranking이 10위권 안에 들 정도로 다른 생태계에서는 SQLite를 많이 사용하고 잘 지원합니다.

3. 사용방법

3-1. SQLite-jdbc를 추가

sqlite-jdbc maven repository - https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc

 

// Gradle
dependencies {
	runtimeOnly 'org.xerial:sqlite-jdbc:3.34.0'
}

 

<!-- Maven -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.34.0</version>
</dependency>

3-2. Dialect 추가

JPA의 구현체인 hibernate에는 SQLite를 위한 Dialect가 없습니다.

hibernate는 Dialect를 이용해서 다양한 DB의 sql 문법의 차이를 개발자가 신경쓰지 않도록 변환해줍니다.

그래서 SQLite를 위한 Dialect를 별도로 추가해야합니다.

 

package com.example.demo.config;

import java.sql.Types;

import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.type.StringType;

public class SQLDialect extends Dialect {
    public SQLDialect() {
        registerColumnType(Types.BIT, "integer");
        registerColumnType(Types.TINYINT, "tinyint");
        registerColumnType(Types.SMALLINT, "smallint");
        registerColumnType(Types.INTEGER, "integer");
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.FLOAT, "float");
        registerColumnType(Types.REAL, "real");
        registerColumnType(Types.DOUBLE, "double");
        registerColumnType(Types.NUMERIC, "numeric");
        registerColumnType(Types.DECIMAL, "decimal");
        registerColumnType(Types.CHAR, "char");
        registerColumnType(Types.VARCHAR, "varchar");
        registerColumnType(Types.LONGVARCHAR, "longvarchar");
        registerColumnType(Types.DATE, "date");
        registerColumnType(Types.TIME, "time");
        registerColumnType(Types.TIMESTAMP, "timestamp");
        registerColumnType(Types.BINARY, "blob");
        registerColumnType(Types.VARBINARY, "blob");
        registerColumnType(Types.LONGVARBINARY, "blob");
        // registerColumnType(Types.NULL, "null");
        registerColumnType(Types.BLOB, "blob");
        registerColumnType(Types.CLOB, "clob");
        registerColumnType(Types.BOOLEAN, "integer");

        registerFunction("concat", new VarArgsSQLFunction(StringType.INSTANCE, "", "||", ""));
        registerFunction("mod", new SQLFunctionTemplate(StringType.INSTANCE, "?1 % ?2"));
        registerFunction("substr", new StandardSQLFunction("substr", StringType.INSTANCE));
        registerFunction("substring", new StandardSQLFunction("substr", StringType.INSTANCE));
    }

    public boolean supportsIdentityColumns() {
        return true;
    }

    public boolean hasDataTypeInIdentityColumn() {
        return false; // As specify in NHibernate dialect
    }

    public String getIdentityColumnString() {
        // return "integer primary key autoincrement";
        return "integer";
    }

    public String getIdentitySelectString() {
        return "select last_insert_rowid()";
    }

    public boolean supportsLimit() {
        return true;
    }

    protected String getLimitString(String query, boolean hasOffset) {
        return new StringBuffer(query.length() + 20).append(query).append(hasOffset ? " limit ? offset ?" : " limit ?")
                .toString();
    }

    public boolean supportsTemporaryTables() {
        return true;
    }

    public String getCreateTemporaryTableString() {
        return "create temporary table if not exists";
    }

    public boolean dropTemporaryTableAfterUse() {
        return false;
    }

    public boolean supportsCurrentTimestampSelection() {
        return true;
    }

    public boolean isCurrentTimestampSelectStringCallable() {
        return false;
    }

    public String getCurrentTimestampSelectString() {
        return "select current_timestamp";
    }

    public boolean supportsUnionAll() {
        return true;
    }

    public boolean hasAlterTable() {
        return false; // As specify in NHibernate dialect
    }

    public boolean dropConstraints() {
        return false;
    }

    public String getAddColumnString() {
        return "add column";
    }

    public String getForUpdateString() {
        return "";
    }

    public boolean supportsOuterJoinForUpdate() {
        return false;
    }

    public String getDropForeignKeyString() {
        throw new UnsupportedOperationException("No drop foreign key syntax supported by SQLiteDialect");
    }

    public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKey, String referencedTable,
            String[] primaryKey, boolean referencesPrimaryKey) {
        throw new UnsupportedOperationException("No add foreign key syntax supported by SQLiteDialect");
    }

    public String getAddPrimaryKeyConstraintString(String constraintName) {
        throw new UnsupportedOperationException("No add primary key syntax supported by SQLiteDialect");
    }

    public boolean supportsIfExistsBeforeTableName() {
        return true;
    }

    public boolean supportsCascadeDelete() {
        return false;
    }
}

 

3-3. DB 설정 추가

DB 사용에 필요한 정보를 application.properties에 추가합니다.

 

# SQLite Dialect 지정
spring.jpa.database-platform=com.shinhan.dictionary.config.SQLDialect 

spring.jpa.hibernate.ddl-auto=update

spring.datasource.url=jdbc:sqlite:mydb.db
spring.datasource.driver-class-name=org.sqlite.JDBC

 

hibernate.ddl-auto은 서버와 DB 간의 데이터(테이블 형태 등) 동기화 방법에 대해 정의합니다.

최초 테이블 생성을 위해 update를 사용하였지만, 운영 서버에서는 none 또는 validate를 이용하는것을 권장합니다.

 

 

 

reference

• https://www.baeldung.com/spring-boot-sqlite
• https://deep-jin.tistory.com/entry/Spring-Boot%EC%99%80-sqlite3-%EC%97%B0%EB%8F%99%ED%95%98%EA%B8%B0-Hibernate