ObjectDB fast queries

Hi,

See below an answer by Gemini 3 Pro Preview, which seems to make sense.

In addition, you can also try simpler queries comparing entities rather than ids, i.e.

Instead of: d.pollenDivisionEntity.idValue = :divisionId
try: d.pollenDivisionEntity = :divisionEntity
You will have to send entities as parameters, which you can get as hollow objects (by ids) from EntityManager.getReference.

One  more point. Moving  byte[] picture and byte[] icon to separate Blob entities may make a big difference in queries.

Gemini 3 Pro Preview Answer:

The problem you are facing ("Duplicate IDs" or mapping conflicts) is because you are mapping the same database column (e.g., `pollenDivisionEntityId`) twice: once for the object reference (`@ManyToOne`) and once for the `Long` field.

You do **not** need the `Long` fields to get fast performance. You are "fighting the framework" by trying to manually manage Foreign Keys.

Here is the better, standard, and high-performance way to do this in ObjectDB (and JPA in general).

### 1. The Entity Optimization
**Changes made:**
1.  Removed the `Long ...Id` fields completely (removes duplicate mapping errors).
2.  Removed `syncIds`, `@PrePersist`, and `@PreUpdate` (removes overhead).
3.  Moved `@Index` to the `@ManyToOne` fields.
4.  **Crucial:** Changed `FetchType.EAGER` to `FetchType.LAZY`. **EAGER** loading is likely why you thought "Joins are slow." It forces the database to load every related object every time you query a definition.

package com.manage.jpa.pollen;

import com.manage.jpa.GenericEntity;
import com.objectdb.Index;
import lombok.Getter;
import lombok.Setter;
import org.springframework.cache.annotation.Cacheable;

import javax.persistence.*;

@Entity
@Getter
@Setter
@Cacheable
public class PollenDefinitionEntity extends GenericEntity {

    // 1. Index the relationship directly.
    // 2. Use LAZY loading. This makes the query fast because it won't 
    //    load the entire Division object unless you specifically ask for it later.
    @ManyToOne(fetch = FetchType.LAZY) 
    @JoinColumn(name = "pollenDivisionEntityId", referencedColumnName = "idValue", nullable = false)
    @Index // <--- ObjectDB indexes the Reference (pointer), effectively indexing the ID
    private PollenDivisionEntity pollenDivisionEntity;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "pollenFamilyEntityId", referencedColumnName = "idValue", nullable = false)
    @Index
    private PollenFamilyEntity pollenFamilyEntity;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "pollenGenusEntityId", referencedColumnName = "idValue", nullable = true)
    @Index
    private PollenGenusEntity pollenGenusEntity;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "pollenSpeciesEntityId", referencedColumnName = "idValue", nullable = true)
    @Index
    private PollenSpeciesEntity pollenSpeciesEntity;

    @Lob
    @Basic(fetch = FetchType.LAZY)
    private byte[] picture;

    @Lob
    @Basic(fetch = FetchType.LAZY)
    private byte[] icon;

    @Column(nullable = false)
    private int referenceCounter;

    // No need for syncIds, onPrePersist, or the Long fields anymore.
    
    @Override
    public String toString() {
        // Safe toString that handles Lazy Loading avoids NullPointerExceptions
        return "PollenDefinitionEntity{" +
                "id=" + getId() + 
                '}';
    }
}

### 2. The Repository Optimization
In JPA/ObjectDB, when you write `d.entity.id` in a query, the database is smart. It **does not** perform a slow SQL-style JOIN and it does not load the full object. It simply looks at the "Foreign Key" (reference ID) stored in the `PollenDefinitionEntity` table.

package com.manage.jpa.pollen;

import com.manage.jpa.repo.BaseRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.Optional;

@Repository
public interface PollenDefinitionRepository extends BaseRepository<PollenDefinitionEntity, Long, Long> {

    // Use .idValue to query against the ID without loading the object
    @Query(" SELECT d FROM PollenDefinitionEntity d WHERE " +
            "(d.pollenDivisionEntity.idValue = :divisionId) AND " +
            "(d.pollenFamilyEntity.idValue   = :familyId) AND " +
            "( (:genusId   IS NULL AND d.pollenGenusEntity   IS NULL) OR d.pollenGenusEntity.idValue   = :genusId ) AND" +
            "( (:speciesId IS NULL AND d.pollenSpeciesEntity IS NULL) OR d.pollenSpeciesEntity.idValue = :speciesId )")
    Optional<PollenDefinitionEntity> findByIds(
            @Param("divisionId") Long divisionId,
            @Param("familyId")   Long familyId,
            @Param("genusId")    Long genusId,
            @Param("speciesId")  Long speciesId
    );
}

### Why is this fast?

1.  **Index on Relationship:** By putting `@Index` on `pollenDivisionEntity`, ObjectDB creates an index on the object references (IDs).
2.  **Path Navigation Optimization:** When ObjectDB sees `d.pollenDivisionEntity.idValue`, it knows you only want to check the ID. It does **not** instantiate the Division object. It just compares the ID stored in the column.
3.  **No Join Required:** This behaves exactly like your manual ID check, but without the messy code and duplicate mapping errors.
4.  **FetchType.LAZY:** Your previous code had `EAGER`. EAGER tells the database: "Whenever you find a Definition, immediately go find the Division, Family, Genus, and Species too." *That* is what makes queries slow. Changing to `LAZY` ensures only the Definition is loaded.