Setup Spring JPA Hibernate & FileMaker.md

Integrating FileMaker with Spring JPA/Hibernate HowTo

The FileMakerDialect.class was cretaed by John Keates, Forked from: https://github.com/johnkeates/hibernate-filemaker-dialect

This is just an implementation note.

Add FileMaker JDBC dirver to the classpath: (provided by FileMaker Inc. http://www.filemaker.com

I do not know any Maven source.

fmjdbc.jar

Add the following dependencies to your Maven project:

<!-- hikari -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>

<!-- needed for Hikari Configuration -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-configuration-processor</artifactId>
    <optional>true</optional>
</dependency>

setup the following properties in application.properties

# Hibernate did not recognize itsels which database is FileMaker
# so help it providing the driver to be used
spring.datasource.driver-class-name=com.filemaker.jdbc.Driver

# add "useSSL=false"
# no need to ask for UTF encoding, since FM always stores Unicode UTF8
spring.datasource.url=jdbc:filemaker://host/database?useSSL=false

# access credentials as usual
spring.datasource.username=**username**
spring.datasource.password=**password**

# add the custom Hibernate Dialect to the classpath
# and tell Spring to use it with FileMaker driver
spring.jpa.properties.hibernate.dialect = nl.keates.filemaker.hibernate.dialect.FileMakerDialect

# FM jdbc driver does not allow schema modifications, so disable updates
spring.jpa.hibernate.ddl-auto = none

# FM jdbc driver does not allow "SELECT 1" default connectivity test, so setup this
spring.datasource.hikari.connection-test-query=SELECT p.* FROM FileMaker_Tables p

# setup other properties as usual, like the following
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE

NOTE: FileMaker SQL support is very poor, not supporting in any way reverse engeneering the database schema (maybe because FileMaker databases do not have 'schema') and constrains (can't see the defined relationships).

When generatinig entities from the database connection, do not forget to set all the @Id columns and create the required associations, because Hibernate can''t look inside.

Error: Couldn't determine Dialect for "filemaker"

Hey man, this properties work fine?I have difficulties to connect filemaker with jdbc. I also use spring boot. Can you help-me?

Tiis is just an implementation note, you should configure your own configuration according to your project, but the following exampe is ok for all my integrations (with little adjustements)...

spring:
  datasource:
    driver-class-name: com.filemaker.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:filemaker://localhost/db_name
    username: username
    password: userpassword
    hikari:
      max-lifetime: 1800000
      connection-timeout: 30000
      connection-test-query: SELECT count(*) FROM FileMaker_Tables
      pool-name: Hikari
      auto-commit: false
      data-source-properties:
        cachePrepStmts: true
        prepStmtCacheSize: 500
        prepStmtCacheSqlLimit: 1024
        useServerPrepStmts: false
  jpa:
    show-sql: true # overwrite or set to false in production
    properties:
      hibernate:
        javax:
          cache:
            missing_cache_strategy: create
        dialect: nl.keates.filemaker.hibernate.dialect.FileMakerDialect
        ddl-auto: none
        id:
          new_generator_mappings: true
        connection:
          provider_disables_autocommit: true
        cache:
          use_second_level_cache: true
          use_query_cache: true
        generate_statistics: false
        format_sql: false
        use_sql_comments: false

Hope this helps, sometimes is a bit tricky but then amazingly solid !
And remember to use latest jdbc driver available (currently 20.1)

Error: Couldn't determine Dialect for "filemaker"

This means that Hibernate couldn't determine by itself the dialect from the driver (as expected for this case). You should tell to spring which driver to use and to Hibernate which dialect to talk with...

spring.datasource.driver-class-name = com.filemaker.jdbc.Driver
spring.jpa.properties.hibernate.dialect = nl.keates.filemaker.hibernate.dialect.FileMakerDialect

(see all relevant configuration above in early coments)

Let me know what.

Is there an updated dialect for hibernate 6?