How to use REGEX on SQLLite3 with JPA?

Question

I found out that in order to filter a column of my SQLLite3 database (a number phone) I need to use REGEX because there is a formation rule and on my database I have some valid phone numbers and invalid ones. I can not update the database, it is read only, and I have a RGEX to validate the numbers.

The problem is that by default SQLite does not provide implementation for the regular expression comparison, so you have to provide one. It's done by implementing the regexp() custom SQL function.

I googled around to know better how to d it but I still have doubts. So far I found

Well I really don't know where to start.

Where should I implement the REGEX operator?
My best guess is inside the SQLLite3 Dialect. Is it right? How? Does it make sense what I did below? I am assuming it is required to use registerFunction. How should I do it?

package com.jumia.db.dialect;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.regex.Pattern;

import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.identity.IdentityColumnSupport;
import org.hibernate.type.StandardBasicTypes;
import org.sqlite.Function;

public class SQLiteDialect extends Dialect {

    public SQLiteDialect() {
        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.BLOB, "blob");
        registerColumnType(Types.CLOB, "clob");
        registerColumnType(Types.BOOLEAN, "integer");
        
        //How to do it?
registerFunction( "REGEXP", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 REGEXP ?2"));
    }
    
    public static void createREGEXP(Connection connection) throws SQLException
    {
        Function.create(connection, "REGEXP", new Function() 
        {
            @Override
            protected void xFunc() throws SQLException 
            {
                String expression = value_text(0);
                String value = value_text(1);
                if (value == null)
                value = "";
                Pattern pattern=Pattern.compile(expression);
                result(pattern.matcher(value).find() ? 1 : 0);
            }
        });
    }
    public boolean dropConstraints() {
        return false;
    }

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

    public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKey, String referencedTable, String[] primaryKey, boolean referencesPrimaryKey) {
        return "";
    }

    public String getAddPrimaryKeyConstraintString(String constraintName) {
        return "";
    }

    public String getDropForeignKeyString() {
        return "";
    }

    public String getForUpdateString() {
        return "";
    }

    public IdentityColumnSupport getIdentityColumnSupport() {
        return new SQLiteIdentityColumnSupport();
    }

    public boolean hasAlterTable() {
        return false;
    }

    public boolean supportsCascadeDelete() {
        return false;
    }

    public boolean supportsIfExistsBeforeTableName() {
        return true;
    }

    public boolean supportsOuterJoinForUpdate() {
        return false;
    }
}

EDIT: On my Repositry I know that I need to create a method like below.

    @Query(value = "SELECT * FROM Customer c WHERE c.phone REGEXP :regex", nativeQuery = true)
    public Page<Customer> findAllValid(Pageable pageable, @Param("regex") String regex);

Although I have registered this Dialect I still get errors like it was not registered.

2022-02-07 09:50:25.681 ERROR 101004 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not prepare statement; nested exception is org.hibernate.exception.GenericJDBCException: could not prepare statement] with root cause

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such function: REGEXP) at org.sqlite.core.DB.newSQLException(DB.java:1030) ~[sqlite-jdbc-3.36.0.3.jar:na] at org.sqlite.core.DB.newSQLException(DB.java:1042) ~[sqlite-jdbc-3.36.0.3.jar:na]

Because if I do that on my BR I will post process it. I don´t want to do that. It means that if I do on BR I will fetch a larger set from DB and then discard part of it. — Daniel Ferreira Castro, Feb 07 '22 at 08:03

score -1 · Answer 1 · answered Feb 07 '22 at 04:20

-1

You should check when you receive user input, not in the database. Depending on a database-specific function or feature is not a good choice.

answered Feb 07 '22 at 04:20

HUTUTU

282
1
5

Not following. Please clarify. :) – Daniel Ferreira Castro Feb 07 '22 at 08:03

How to use REGEX on SQLLite3 with JPA?

1 Answers1