Skip to main content

Database

Structural Migration with Liquibase​

To prevent accidental irreversible database modifications, we use Liquibase to prepare changes when developing that can be confirmed in the review process.

Gradle Commands​

We offer two Gradle commands with Liquibase:

  • liquibaseClearChecksums: Use this whenever Liquibase detects inconsistencies between the database changelog and the XML changelog
  • liquibaseDiffChangeLog: Generates a new changelog from the current database state. The command appears to not work for all operating systems, and you might have to add a changelog manually.

Liquibase Changelog​

The changelog manifest lies in src/main/resources/config/liquibase/master.xml, which imports all single changelog files. To create a new change, you have to do the following:

  1. Get the current time in the format YYYYMMDDHHmmss.
  2. Create a new file in /changelog named <formatted-time>_changelog.xml and include this file at the bottom of the master.xml as every other file.
  3. Add your changelog in your newly created file. Take other changes and the Liquibase documentation as inspiration.

MySQL and PostgreSQL Compatibility​

We support both PostgreSQL and MySQL databases. In case you introduce database changes, carefully check the results of the continuous integration tests run against the different databases to make sure the migration works on both of them.

Only in special cases where you cannot avoid different SQL statements depending on the database type, you can use preconditions as part of your changeset to apply it only to one database type.

Pattern for database changes with preconditions depending on the database type
<databaseChangelog>
    <!-- … -->

    <changeSet id="00000000000000m" author="you">
        <!-- Only runs for MySQL -->
        <preConditions onFail="CONTINUE">
            <dbms type="mysql"/>
        </preConditions>

        <!-- your <sql> and other Liquibase changes here -->
    </changeSet>

    <changeSet id="00000000000000p" author="you">
        <!-- Only runs for PostgreSQL -->
        <preConditions onFail="CONTINUE">
            <dbms type="postgresql"/>
        </preConditions>

        <!-- your <sql> and other Liquibase changes here -->
    </changeSet>

    <!-- … -->
</databaseChangelog>

DATETIME Data Type​

The recommended default data type for storing values that involve date and time, such as creation dates and timestamps, is datetime(3). Liquibase will translate this to datetime(3) for MySQL and timestamp(3) for PostgreSQL. This choice ensures consistent support for milliseconds across both MySQL and PostgreSQL databases.

Development Best Practices​

  • All executed entries are saved in the table databasechangelog. If you delete something, it gets executed again.
  • Test your changes locally first, and only commit changes you are confident that work.
  • Before deploying any database changes to a test server, ask for official permission from the project lead. If the changes don't get approved, manual rollbacks can be necessary, which are avoidable.
  • Make sure to add your name to the changeSet in your file as well as your formatted time as the id. Refer to other changes for further help.

Data Migration with Java​

Java Migration Changelog​

The changelog can be found in src/main/java/config/migration/MigrationRegistry.java. To create a new change, follow these steps:

  1. Get the current time in the format YYYYMMDD_HHmmss.
  2. Create a new file in /entries named MigrationEntry<formatted-time>.java containing a class extending MigrationEntry.
  3. Implement the required methods in your class and follow the JavaDoc.
  4. Add the class to the migrationEntryMap attribute in the MigrationRegistry.java together with the next Integer key inside the constructor.

Development Best Practices for Java Migrations​

  • All executed entries are saved in the table migration_changelog. If you delete entries, they get executed again.
  • Test your changes locally first, and only commit changes you are confident that work.
  • Before deploying any database changes to a test server, ask for official permission from the project lead. If the changes don't get approved, manual rollbacks can be necessary, which are avoidable.
  • If queries fail due to the authorization object being null, call SecurityUtils.setAuthorizationObject() beforehand to set a dummy object.

General Guidelines​

Weekly Database Meeting​

We hold a weekly, in-person, cross-functional meeting involving database, infrastructure, and operations teams. Attendance is required whenever changes to the database, configurations, or related operations are proposed. Please prepare for the meeting and enter your PR and a short description of the changes in the meeting agenda.

Approval Process​

  • Mandatory Review: Every change affecting the database or configuration must be explicitly discussed and approved during the weekly meeting. Final approval is granted by Stephan Krusche or Patrick Bassner.
  • Deployment Restrictions: Database-affecting deployments cannot proceed without prior approval from designated reviewers to ensure accountability and prevent unauthorized changes. For test server deployments, approval is required from Benjamin Schmitz.

Values That Reference a Stored File​

A column that points at an uploaded file stores the filename only, never a path fragment and never a REST path. The request path and the location on disk are both derived from the owning entity instead, so that renaming an endpoint never becomes a schema change and the storage layer never parses a URL. See File Storage, Stored Values and REST Paths Are Independent.

A migration written in order to rename a REST path is a sign that this rule has been broken somewhere; fix the coupling rather than migrating the data.

Database Review Guidelines​

  • Third Normal Form (3NF): Database schema changes should adhere strictly to the third normal form to avoid redundancy and ensure data integrity.
  • Cross-Compatibility: Ensure compatibility with both MySQL and PostgreSQL databases.
  • Foreign Keys: Deletions involving foreign keys must be verified thoroughly to maintain referential integrity.
  • Advisor/Maintainer Approval: The database schema must always be reviewed and approved by an advisor or maintainer prior to implementation.
  • Joins Limitation: Limit the number of LEFT JOIN operations to a maximum of 5, unless proven that the resulting dataset remains small and performant.
  • Indexed Columns: WHERE clauses must leverage appropriate indices to optimize query performance.
  • Nullable Fields: Use nullable fields sparingly and only when explicitly necessary.
  • Optimized Data Types: Minimize the size of VARCHAR and DATETIME fields (e.g., DATETIME(3) precision). For enumeration fields, use actual ENUM types in MySQL and TEXT types in PostgreSQL.
  • Atomic Changesets: Implement small, atomic database changesets that are easy to roll back in case of issues.
  • Redundancy and Cleanup: Identify and avoid unnecessary redundancy; implement periodic cleanup services to manage and remove obsolete data.
  • Rollback Procedures: Ensure robust and sensible rollback procedures are always available and tested. Rollbacks can be performed and tested using the corresponding rollback Gradle tasks provided by Liquibase. Information about this can be found in the Liquibase documentation.
  • Delete Policy: Adopt a rename-first approach before deleting database objects, enabling safer rollback.
  • Major Migrations: Major database migrations should only occur during X.X.0 releases.
  • Hibernate Fetching Strategies: Carefully handle Hibernate fetching strategies (ManyToOne, OneToMany, etc.), always preferring lazy loading over eager loading.
  • Use of DTOs: Prefer the use of Data Transfer Objects (DTOs) in queries to manage database fetch size and improve performance.

Retrieving and Building Objects​

The cost of retrieving and building an object's relationships far exceeds the cost of selecting the object. This is especially true for relationships where it would trigger the loading of every child through the relationship hierarchy. The solution to this issue is lazy fetching (lazy loading). Lazy fetching allows the fetching of a relationship to be deferred until it is accessed. This is important not only to avoid the database access, but also to avoid the cost of building the objects if they are not needed.

In JPA lazy fetching can be set on any relationship using the fetch attribute. The fetch can be set to either LAZY or EAGER as defined in the FetchType enum. The default fetch type is LAZY for all relationships except for OneToOne and ManyToOne, but in general it is a good idea to make every relationship LAZY. The EAGER default for OneToOne and ManyToOne is for implementation reasons (more easier to implement), not because it is a good idea.

Eager fetching is not allowed​

No @OneToOne, @OneToMany or @ManyToMany may fetch eagerly. The ArchUnit rule ArchitectureTest.testNoEagerFetching fails the build for any that does; the associations that are still eager are listed in FIELDS_ALLOWED_TO_FETCH_EAGERLY, which may only shrink.

The rule judges the fetch type that actually applies, not what is written down. @OneToOne defaults to EAGER, so it has to spell out fetch = FetchType.LAZY; @OneToMany and @ManyToMany are lazy already and need not repeat it, though writing it out does no harm.

@ManyToOne is out of scope. Hibernate cannot make a to-one association lazy without bytecode enhancement or a proxy, and a proxied @ManyToOne does not work with entity hierarchies - which most of ours are. Its eager default is a fact to design around, not something to declare.

An eager association is loaded for every caller, including the overwhelming majority that never read it, and the cost never appears where it was written. Course.athenaConfig was two booleans behind an eager one-to-one: the server issued 155,848 queries for it during a single 2000-student exam, and an exam does not care whether Athena is switched on.

Read a configuration through its own repository​

Do not add a lazy association to an @EntityGraph or a JOIN FETCH just so that some code further down can read it off the entity. That couples every query that happens to be on the path to a decision made somewhere else, and it does not even work reliably: where the owning entity is reached through an eager @ManyToOne chain - Exercise to ExerciseGroup to Exam to Course, for instance - Hibernate resolves that chain by secondary select and the fetch plan no longer applies, so the association stays uninitialized however you spell the query.

Give the configuration its own repository and read it where the decision is made. CourseConfigurationRepository and CourseAthenaConfigRepository are the pattern: a findByCourseId, plus a projection of the fields a hot path actually reads.

Which side holds the key​

A configuration that must not outlive what it configures holds the parent's key, not the other way round. A foreign key constrains the row that holds it: with the key on the parent, the database can require that parent's configuration to exist, but nothing can require the configuration's parent to exist. Deleting the parent by any route other than the application's own cascade then leaves the configuration behind, and the schema has no way to refuse it.

Moving the key has a consequence that decides how the parent maps it. JPA makes whichever side holds the column the owner, so the parent becomes the inverse side of the @OneToOne - and an inverse one-to-one cannot be proxied: Hibernate issues a select on every parent read to find out whether the row exists, whether or not the caller looks at it. optional = false does not change this, and no fetch removes it from a query that reaches the parent polymorphically, because the subclass attribute is not on the polymorphic type. Mapping ProgrammingExercise.buildConfig that way cost one select per exercise on the course dashboard and on git authorization, neither of which reads it.

So the parent does not carry the configuration at all - there is no field, mapped or otherwise. Only the configuration knows the relation:

// ProgrammingExerciseBuildConfig - owns the key, so a deleted exercise takes it along
@OneToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "exercise_id", nullable = false, unique = true)
@JsonIgnore
@Parent
private ProgrammingExercise programmingExercise;

The database refuses a configuration that belongs to no exercise, and a read that does not want the configuration does not pay for it. Everything that needs it reads it at that point - ProgrammingExerciseBuildConfigRepository.getProgrammingExerciseBuildConfigElseThrow(exerciseId) - or takes it as a parameter next to the exercise. A shared primary key (@MapsId) would keep the association mapped without the select, but it cannot be combined with the generated id that DomainObject provides.

The API is unaffected, because responses and request bodies are records rather than entities: the configuration is a component of CreateProgrammingExerciseDTO, UpdateProgrammingExerciseDTO, ImportProgrammingExerciseRequestDTO and ProgrammingExerciseResponseDTO, and the endpoints that carry it pass it to the mapper explicitly. An endpoint whose client does not read the configuration leaves it out instead of spending a query on it.

Writing follows the key as well: the parent is saved first, then the configuration that names it.

Turning an existing association lazy​

open-in-view is disabled, so the persistence context is already closed when a response is serialized. An association the query did not fetch reads as absent - which surfaces either as a LazyInitializationException or, where the getter guards with Hibernate.isInitialized, as a silently wrong value.

Making Course.athenaConfig lazy was the second kind: its getters answer false when it was not loaded, so every response whose query did not resolve it would have reported Athena as switched off and quietly removed the feature from the client. Find every place that reads it, give each an explicit read, and pin the result - AthenaConfigWireContractTest does that for the endpoints the webapp reads those two flags from.

A long text column is text, not a LOB​

Never annotate a field @Lob. ArchitectureTest.testNoLobAnnotation fails the build for any that is.

A CLOB on PostgreSQL is a large object: Hibernate writes the value into pg_largeobject and stores the object's id in the column, then reads the column back as that id. The long text columns in Artemis are declared longtext in Liquibase, and tool_activity is declared clob; both become text on PostgreSQL, so the column holds text and the mapping expects an object id. The two agree only as long as the same mapping wrote the row: a row holding the text itself - as MySQL writes it, and as every row written before the move to PostgreSQL is stored - fails the read with Bad value for type long and takes the whole query with it, not just the one column. On IrisMessage.toolActivity that meant one unreadable message made every Iris session load for that user fail.

The large objects are also never reclaimed. Nothing unlinks them when the message is deleted, so they accumulate outside the table and are invisible to a table-size check.

A String, or an attribute converted to one, needs no annotation at all: bound and extracted as text it round-trips on both databases whatever its length, since a length in the mapping only ever shapes generated DDL and Artemis generates none. Exercise.problemStatement is the pattern - a plain @Column over a longtext.

// IrisMessage - a converted list in a clob column, mapped as the text it is
@Nullable
@Convert(converter = IrisMessageToolActivityConverter.class)
@Column(name = "tool_activity")
private List<PyrisActivityDTO> toolActivity;

Where the value is structured rather than free text, @JdbcTypeCode(SqlTypes.JSON) over a json column is the other option - IrisMessage.accessedMemories uses it. It costs the column its equality operator on PostgreSQL, which is why a query fetching such an entity cannot use DISTINCT; IrisChatSessionRepository.findAllWithMessagesByCourseId returns a Set and deduplicates in Java for exactly that reason.

Relationships​

A relationship is a reference from one object to another. In a relational database relationships are defined through foreign keys. The source row contains the primary key of the target row to define the relationship (and sometimes the inverse). A query must be performed to read the target objects of the relationship using the foreign key and primary key information. If there is a relationship to a collection of other objects, a Collection or array type is used to hold the contents of the relationship. In a relational database, collection relations are either defined by the target objects having a foreign key back to the source object's primary key, or by having an intermediate join table to store the relationship (containing both objects' primary keys).

In this section, we depict common entity relationships we use in Artemis and show some code snippets.

Which relationship an entity belongs to, and whether the database guarantees it, is recorded per entity in Entity Ownership. Read it before adding an entity: a nullable owning foreign key is how a row ends up unreachable but undeleted, which also puts it outside every data privacy cleanup.

OneToOne​

A unique reference from one object to another. It is also inverse of itself. Example: one Complaint has a reference to one Result.

// Complaint.java
@OneToOne
@JoinColumn(unique = true)
private Result result;

OneToMany​

A Collection or Map of objects. It is the inverse of a ManyToOne relationship. Example: one Result has a set of Feedback elements. Default to a Set unless presentation order is semantically meaningful — see the ordered collections section below for when (and how) to use a List.

// Result.java
@OneToMany(mappedBy = "result", cascade = CascadeType.ALL, orphanRemoval = true)
@JsonIgnoreProperties(value = "result", allowSetters = true)
private Set<Feedback> feedbacks = new HashSet<>();

ManyToOne​

A reference from one object to another. It is the inverse of an OneToMany relationship. Example: one Feedback has a reference to one Result.

// Feedback.java
@ManyToOne
@JsonIgnoreProperties("feedbacks")
private Result result;

ManyToMany​

A Collection or Map of objects. It is the inverse of itself. Example: one Exercise has a list of LearningGoal elements, one LearningGoal has list of Exercise elements. In other words: many exercises are connected to many learning goals and vice-versa.

// Exercise.java
@ManyToMany(mappedBy = "exercises")
public Set<LearningGoal> learningGoals = new HashSet<>();

// LearningGoal.java
@ManyToMany
@JoinTable(name = "learning_goal_exercise", joinColumns = @JoinColumn(name = "learning_goal_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "exercise_id", referencedColumnName = "id"))
@JsonIgnoreProperties("learningGoals")
private Set<Exercise> exercises = new HashSet<>();

Lazy Relationships​

Lazy relationships in Artemis may require some additional special handling to work correctly:

  • Lazy OneToOne relationships require the additional presence of the @JoinColumn annotation and only work in one direction. They can only lazily load the child of the relationship, not the parent. The parent is the entity whose database table owns the foreign key.

    E.g., You can lazily load ProgrammingExercise::solutionParticipation but not SolutionProgrammingExerciseParticipation::programmingExercise, as the foreign key is part of the exercise table.

  • Lazy ManyToOne relationships require the additional presence of the @JoinColumn annotation.

  • Lazy OneToMany and ManyToMany relationships work without further changes.

Cascade Types​

Entity relationships often depend on the existence of another entity — for example, the Result-Feedback relationship. Without the Result, the Feedback entity doesn't have any meaning of its own. When we delete the Result entity, our Feedback entity should also get deleted. For more information see: jpa cascade types.

CascadeType.ALL​

Propagates all operations mentioned below from the parent object to the child object.

// Result.java
@OneToMany(mappedBy = "result", cascade = CascadeType.ALL, orphanRemoval = true)
@JsonIgnoreProperties(value = "result", allowSetters = true)
private Set<Feedback> feedbacks = new HashSet<>();

CascadeType.PERSIST​

When persisting a parent entity, it also persists the child entities held in its fields. This cascade rule is helpful for relationships where the parent acts as a container to the child entity. If you do not use this, you have to ensure that you persist the child entity first, otherwise an error will be thrown. Example: The code below propagates the persist operation from parent AnswerCounter to child AnswerOption. When an AnswerCounter is persisted, its AnswerOption is persisted as well.

// AnswerCounter.java
@OneToOne(cascade = { CascadeType.PERSIST })
@JoinColumn(unique = true)
private AnswerOption answer;

CascadeType.MERGE​

If you merge the source entity (saved/updated/synchronized) to the database, the merge is cascaded to the target of the association. This rule applies to existing objects only. Use this type to always merge/synchronize the existing data in the table with the data in the object. Example below: whenever we merge a Result to the database, i.e. save the changes on the object, the Assessor object is also merged/saved.

// Result.java
@OneToOne(cascade = CascadeType.MERGE, fetch = FetchType.LAZY)
@JoinColumn(unique = false)
private User assessor;

CascadeType.REMOVE​

If the source entity is removed, the target of the association is also removed. Example below: propagates remove operation from parent Submission to child Result. When a Submission is deleted, the corresponding Result is also deleted.

// Submission.java
@OneToOne(mappedBy = "submission", fetch = FetchType.LAZY, cascade = CascadeType.REMOVE)
@JsonIgnoreProperties({ "submission", "participation" })
@JoinColumn(unique = true)
private Result result;

CascadeType.REFRESH​

If the source entity is refreshed, it cascades the refresh to the target of the association. This is used to refresh the data in the object and its associations. This is useful for cases where there is a change which needs to be synchronized FROM the database.

Saving and Deleting Entities with Cascade and OrphanRemoval​

When using CascadeType.REMOVE and orphanRemoval = true on @OneToMany relationships, special care is needed when saving new child entities and when explicitly deleting children before their parent.

Saving New Child Entities​

When adding a new child entity to a parent's collection with CascadeType.REMOVE and orphanRemoval = true:

  1. Set the child's reference to the parent: child.setParent(parent)
  2. Save the child entity via its repository: childRepository.save(child)
  3. Add to the parent's collection for in-memory consistency: parent.addChild(child)

The foreign key is on the child side, so the parent does NOT need to be saved again after adding a child (assuming the parent is managed in the current persistence context; otherwise merge/save it).

// Example: Adding a submission to a participation
submission.setParticipation(participation);
submission = submissionRepository.save(submission);
participation.addSubmission(submission);  // For in-memory consistency only

Deleting Parent Entities After Explicit Child Deletion​

When you delete child entities explicitly (for example, to run extra cleanup the cascade does not cover) and then delete the parent, reload the parent with its collection before issuing the delete. This guarantees that the cascade processes the post-deletion collection state rather than a stale in-memory or persistence-context view that still references the children you just removed.

// Example: Deleting a participation with its submissions
public void delete(long participationId) {
    // Step 1: Delete children explicitly (for special cleanup like build logs)
    deleteSubmissionsOfParticipation(participationId);

    // Step 2: Reload parent WITH the collection so cascade sees the post-deletion state
    // The collection is now empty because the children were deleted in step 1.
    var participation = participationRepository.findByIdWithSubmissionsElseThrow(participationId);

    // Step 3: Delete the parent — cascade has nothing to do
    participationRepository.delete(participation);
}

This pattern is used in several places in Artemis:

  • ParticipationDeletionService.delete() — reloads participation with submissions
  • ExampleParticipationService.deleteById() — reloads example participation with submissions
  • ExerciseDeletionService — reloads exercise with participations
  • ProgrammingExerciseDeletionService — reloads exercise with participations

Dynamic Fetching​

In Artemis, we use dynamic fetching to load the relationships of an entity on demand. As we do not want to load all relationships of an entity every time we fetch it from the database, we use as described above FetchType.LAZY. In order to load the relationships of an entity on demand, we then use one of 4 methods:

EntityGraph​

The @EntityGraph annotation is the simplest way to specify a graph of relationships to fetch. It should be used when a query is auto-constructed by Spring Data JPA and does not have a custom @Query annotation. Example:

// CourseRepository.java
@EntityGraph(type = LOAD, attributePaths = { "exercises", "exercises.categories", "exercises.teamAssignmentConfig" })
Course findWithEagerExercisesById(long courseId);

JOIN FETCH​

The JOIN FETCH keyword is used in a custom query to specify a graph of relationships to fetch. It should be used when a query is custom and has a custom @Query annotation. You can see the example below. Also, explicitly or implicitly limiting queries in Hibernate can lead to in-memory paging. For more details, see the 'In-memory paging' section.

// ProgrammingExerciseRepository.java
@Query("""
        SELECT pe
        FROM ProgrammingExercise pe
            LEFT JOIN FETCH pe.exerciseGroup eg
            LEFT JOIN FETCH eg.exam e
        WHERE e.endDate > :dateTime
        """)
List<ProgrammingExercise> findAllWithEagerExamByExamEndDateAfterDate(@Param("dateTime") ZonedDateTime dateTime);

DynamicSpecificationRepository​

For repositories that use a lot of different queries with different relationships to fetch, we use the DynamicSpecificationRepository. You can let a repository additionally implement this interface and then use the findAllWithEagerRelationships and then use the getDynamicSpecification(Collection<? extends FetchOptions> fetchOptions) method in combination with a custom enum implementing the FetchOptions interface to dynamically specify which relationships to fetch inside of service methods.

Example: DynamicSpecificationRepository Usage
// ProgrammingExerciseFetchOptions.java
public enum ProgrammingExerciseFetchOptions implements FetchOptions {

    GradingCriteria(Exercise_.GRADING_CRITERIA),
    AuxiliaryRepositories(Exercise_.AUXILIARY_REPOSITORIES),
    // ...

    private final String fetchPath;

    ProgrammingExerciseFetchOptions(String fetchPath) {
        this.fetchPath = fetchPath;
    }

    public String getFetchPath() {
        return fetchPath;
    }
}


// ProgrammingExerciseRepository.java
@NonNull
default ProgrammingExercise findByIdWithDynamicFetchElseThrow(long exerciseId, Collection<ProgrammingExerciseFetchOptions> fetchOptions) throws EntityNotFoundException {
    var specification = getDynamicSpecification(fetchOptions);
    return findOneByIdElseThrow(specification, exerciseId, "Programming Exercise");
}


// ProgrammingExerciseService.java
final Set<ProgrammingExerciseFetchOptions> fetchOptions = withGradingCriteria ? Set.of(GradingCriteria, AuxiliaryRepositories) : Set.of(AuxiliaryRepositories);
var programmingExercise = programmingExerciseRepository.findByIdWithDynamicFetchElseThrow(exerciseId, fetchOptions);

In-Memory Paging​

Since the flag hibernate.query.fail_on_pagination_over_collection_fetch: true is now active, it is crucial to carefully craft database queries that involve FETCH statements with collections and thoroughly test the changes. In-memory paging would cause performance decrements and is, therefore, disabled. Any use of it will lead to runtime errors.

Queries that may result in this error can return Page<> and contain JOIN FETCHes or involve internal limiting in Hibernate, such as findFirst, findLast, or findOne. One solution is to split the original query into multiple queries and a default method. The first query fetches only the IDs of entities whose full dependencies need to be fetched. The second query eagerly fetches all necessary dependencies, and the third query uses counting to build Pages, if they are utilized.

When possible, use the default Spring Data/JPA methods for second (fetching) and third (counting) queries.

Example: Avoiding In-Memory Paging
// Repository interface
default Page<User> searchAllByLoginOrNameInCourseAndReturnPage(Pageable pageable, String loginOrName, long courseId) {
    List<Long> userIds = findUserIdsByLoginOrNameInCourse(loginOrName, courseId, pageable).stream().map(DomainObject::getId).toList();

    if (userIds.isEmpty()) {
        return new PageImpl<>(Collections.emptyList(), pageable, 0);
    }

    List<User> users = findUsersWithAuthoritiesByIds(userIds);
    long total = countUsersByLoginOrNameInCourse(loginOrName, courseId);

    return new PageImpl<>(users, pageable, total);
}

@Query("""
        SELECT DISTINCT userCourseRole.user
        FROM UserCourseRole userCourseRole
        WHERE userCourseRole.course.id = :courseId
            AND userCourseRole.user.deleted = FALSE
            AND (
                userCourseRole.user.login LIKE :#{#loginOrName}%
                OR CONCAT(userCourseRole.user.firstName, ' ', userCourseRole.user.lastName) LIKE %:#{#loginOrName}%
            )
        """)
List<User> findUsersByLoginOrNameInCourse(@Param("loginOrName") String loginOrName, @Param("courseId") long courseId, Pageable pageable);

@Query("""
        SELECT DISTINCT user
        FROM User user
            LEFT JOIN FETCH user.authorities
        WHERE user.id IN :ids
        """)
List<User> findUsersWithAuthoritiesByIds(@Param("ids") List<Long> ids);

@Query("""
        SELECT COUNT(DISTINCT userCourseRole.user)
        FROM UserCourseRole userCourseRole
        WHERE userCourseRole.course.id = :courseId
            AND userCourseRole.user.deleted = FALSE
            AND (
                userCourseRole.user.login LIKE :#{#loginOrName}%
                OR CONCAT(userCourseRole.user.firstName, ' ', userCourseRole.user.lastName) LIKE %:#{#loginOrName}%
            )
        """)
long countUsersByLoginOrNameInCourse(@Param("loginOrName") String loginOrName, @Param("courseId") long courseId);

Best Practices​

Choosing Collection Types​

If you want to create a @OneToMany relationship or @ManyToMany relationship, first think about if it is important for the association to be ordered. If you do not need the association to be ordered, then always go for a Set instead of List. If you are unsure, start with a Set.

Unordered Collection (Set)​

A Set comes with certain advantages such as ensuring that there are no duplicates and null values in your collection. There are also performance arguments to use a Set, especially for @ManyToMany relationships. For more information see this stackoverflow thread.

Example: Unordered Collection with Set
// Course.java
@OneToMany(mappedBy = "course", fetch = FetchType.LAZY)
@JsonIgnoreProperties("course")
private Set<Exercise> exercises = new HashSet<>();

Ordered Collection without Duplicates (TreeSet)​

When you want to order the collection of objects of the relationship, while having no duplicates use a TreeSet. A TreeSet is a sorted set, which means that the elements are ordered using their natural ordering or by a comparator provided at set creation time.

Example: TreeSet for Ordered Collection without Duplicates
// IrisSubSettings.java
@Column(name = "allowed_models")
@Convert(converter = IrisModelListConverter.class)
private TreeSet<String> allowedVariants = new TreeSet<>();

Ordered Collection with Duplicates (List)​

If you genuinely need a List whose database position is meaningful and you cannot derive the order from a domain field (e.g., a timestamp, a displayOrder int, a numbered spotNr), Hibernate offers @OrderColumn. Treat it as a sharp tool: it has caused multiple production incidents in this codebase (issues #12574, #12584). Read the rules below before using it, and prefer Set or @OrderBy on a domain field whenever you can.

Example: Bidirectional @OrderColumn (the canonical pattern)
// MultipleChoiceQuestion.java — owning side declares the relationship
@OneToMany(mappedBy = "question", cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
@OrderColumn(name = "answer_options_order")
private List<AnswerOption> answerOptions = new ArrayList<>();

public void addAnswerOption(AnswerOption answerOption) {
    if (answerOptions == null) {
        answerOptions = new ArrayList<>();
    }
    answerOptions.add(answerOption);
    answerOption.setQuestion(this);
}

@PrePersist
@PreUpdate
private void ensureAnswerOptionBackReferences() {
    if (answerOptions != null) {
        for (AnswerOption option : answerOptions) {
            if (option != null && option.getQuestion() != this) {
                option.setQuestion(this);
            }
        }
    }
}
// AnswerOption.java — child owns the FK column
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "question_id")
@JsonIgnore
private MultipleChoiceQuestion question;

Preferred alternative: Set + explicit displayOrder + @OrderBy​

For most ordered relationships, an explicit integer displayOrder field on the child plus @OrderBy("displayOrder ASC") on the parent is a strict improvement over @OrderColumn:

  • The order is a visible domain field — easy to grep, log, and assert on in tests.
  • @OrderBy resolves at SQL ORDER BY time, so partial fetches just return the matching rows in order with no null padding.
  • Concurrent writes do not race on a single index column — each writer updates its own row's displayOrder.
  • The relationship can be a Set (typically LinkedHashSet), which avoids the MultipleBagFetchException when multiple ordered collections are fetched together.

This is the pattern used by Lecture.lectureUnits (Lecture.java:67-87, Lecture.java:263-275) and is the recommended default. Use @OrderColumn only when you have weighed the rules above and decided that owning a domain-visible order field is not appropriate.

Example: Set + explicit displayOrder
// Lecture.java
@OneToMany(mappedBy = "lecture", cascade = CascadeType.ALL, orphanRemoval = true)
@OrderBy("lectureUnitOrder ASC")
@JsonIgnoreProperties("lecture")
private Set<LectureUnit> lectureUnits = new LinkedHashSet<>();

@PrePersist
@PreUpdate
public void updateLectureUnitOrder() {
    if (Hibernate.isInitialized(lectureUnits)) {
        int order = 0;
        for (LectureUnit unit : lectureUnits) {
            if (unit == null) continue;
            unit.setLectureUnitOrder(order++);
        }
    }
}
// LectureUnit.java
@Column(name = "lecture_unit_order", nullable = false)
private int lectureUnitOrder;

Unordered child collection: Set + constant hashCode()​

When the child collection has no semantically meaningful order (e.g. each row is identified by a tuple of associations, like (dragItem, dropLocation) or (spot, solution)), prefer Set<Child> over List<Child>. A Set:

  • dedupes Cartesian products that appear when callers JOIN FETCH the collection alongside another collection;
  • avoids MultipleBagFetchException if a sibling collection later drops @OrderColumn;
  • matches the conceptual model and reads honestly in code.

However, DomainObject.hashCode() is id-based by default, and id is null for transient entities. Adding a transient child to a HashSet places it in bucket 0; once Hibernate assigns the id on persist, the hashCode changes, the entity is now in the wrong bucket, and set.contains(...) / set.remove(...) silently fail. This violates the equals ⇒ hashCode contract at the worst possible moment.

Fix it by overriding hashCode() on the child to a class constant, mirroring the Feedback pattern. All instances collide into a single bucket; the id-based equals still discriminates them; the Set lookup degrades to a linear scan, which is free for the small collections this pattern targets.

Example: Set + constant hashCode()
// DragAndDropQuestion.java — parent declares the relationship as a Set
@OneToMany(mappedBy = "question", cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
private Set<DragAndDropMapping> correctMappings = new HashSet<>();
// DragAndDropMapping.java — child owns the FK and provides a stable hashCode
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "question_id")
@JsonIgnore
private DragAndDropQuestion question;

/**
 * Stable, constant hashCode that does not change when Hibernate assigns the id on persist.
 * Required because DragAndDropQuestion.correctMappings is a Set: factories and DTO mappers add
 * transient mappings (id == null), and the id-based default would change after persist and
 * silently break HashSet membership. The (id-based) equals contract still distinguishes instances.
 * Mirrors the same pattern on Feedback.
 */
@Override
public int hashCode() {
    return DragAndDropMapping.class.hashCode();
}

Queries That Behave Differently on MySQL and PostgreSQL​

Artemis runs on both MySQL and PostgreSQL. A query can work on one and fail on the other, or, worse, return different rows without any error. Integration tests run against PostgreSQL only, so a MySQL-only difference never shows up locally, and a difference that changes the result rather than raising an error shows up in neither.

Compare Case Explicitly​

MySQL compares with the utf8mb4_unicode_ci collation, which ignores case, so LIKE '%max%' matches Max Mustermann. PostgreSQL compares case sensitively and the same pattern matches nothing. A search written without this in mind therefore returns an empty list rather than an error, and nothing in the log says so.

Lower case both sides rather than relying on a collation. In JPQL, keep it inside the query so that no caller can defeat it:

@Query("""
        SELECT user
        FROM User user
        WHERE LOWER(user.login) LIKE CONCAT(LOWER(CAST(:loginOrName AS string)), '%')
            OR LOWER(CONCAT(user.firstName, ' ', user.lastName)) LIKE CONCAT('%', LOWER(CAST(:loginOrName AS string)), '%')
        """)
Page<User> searchAllByLoginOrName(Pageable pageable, @Param("loginOrName") String loginOrName);

The CAST is what makes the parameter a string for the database rather than only for Java. Without it, a parameter that arrives as null, which an optional filter does on every request that leaves it out, is sent untyped, PostgreSQL reads it as bytea, and the query fails with function lower(bytea) does not exist rather than returning nothing. A parameter that is never null works without the cast, so the failure appears the first time somebody makes the filter optional.

With the Criteria API, wrap the path in lower and lower case the pattern in Java with an explicit locale:

String pattern = "%" + searchTerm.toLowerCase(Locale.ROOT) + "%";
Predicate titleMatches = criteriaBuilder.like(criteriaBuilder.lower(root.get(Exercise_.TITLE)), pattern);

A comparison wrapped in LOWER cannot use a plain index on the column. Where a search runs against a large table, add a functional index for it. The two databases spell it differently, so a changelog needs one changeset per database type:

-- PostgreSQL
CREATE INDEX user_login_lower ON jhi_user (LOWER(login));
-- MySQL, which needs the expression in its own parentheses
CREATE INDEX user_login_lower ON jhi_user ((LOWER(login)));

A pattern with a leading wildcard cannot use an index on either database, so a full-text search is the answer where that becomes the bottleneck.

ORDER BY With SELECT DISTINCT​

PostgreSQL requires every ORDER BY expression of a SELECT DISTINCT to appear in the select list, and rejects the query with for SELECT DISTINCT, ORDER BY expressions must appear in select list. The combination is easy to miss, because a repository method that selects an id can receive the order from a Pageable the caller builds, so neither the query nor the call site shows it.

Duplicates usually come from a join that only filters. Replace it with EXISTS: the DISTINCT is then unnecessary, and the caller's order stays legal.

@Query("""
        SELECT user.id
        FROM User user
        WHERE EXISTS (
                SELECT 1
                FROM UserCourseRole ucr
                WHERE ucr.user.id = user.id AND ucr.course.id = :courseId
            )
        """)
List<Long> findUserIdsInCourse(@Param("courseId") long courseId, Pageable pageable);

Order Pages Totally​

A page window only means something if the order is total. Rows an order cannot tell apart are free to change places between two queries, so one of them can appear on two pages and another on none. Append the id to whatever order the caller asked for rather than replacing it, so that the requested order still decides the page.

Name Every Grouped Column​

Both databases reject a selected column that is neither aggregated nor grouped, but they draw the line in different places: MySQL applies ONLY_FULL_GROUP_BY, which is on by default and recognises some functional dependencies, and PostgreSQL accepts columns that are functionally dependent on a grouped primary key. Name every non-aggregated column of the select list in the GROUP BY and the query means the same thing on both.

Solutions for Known Issues​

LazyInitializationException​

org.hibernate.LazyInitializationException : could not initialize proxy – no Session caused by fetchType.LAZY. You must explicitly load the associated object from the database before trying to access those.

Example: Eagerly Fetch with LEFT JOIN FETCH
// ResultRepository.java
@Query("select r from Result r left join fetch r.feedbacks where r.id = :resultId")
Optional<Result> findByIdWithEagerFeedbacks(@Param("resultId") Long id);

JpaSystemException / "null index column for collection" with @OrderColumn​

JpaSystemException: null index column for collection (or IllegalArgumentException: Illegal null value for list index) is almost always a symptom of one of the rules in the ordered collections section being violated. Before adding workarounds, check:

  1. Is the relationship unidirectional? If the parent has both @OneToMany ... @JoinColumn(name = "...") and @OrderColumn, convert it to bidirectional (mappedBy) and let the child own the FK via its own @ManyToOne + @JoinColumn. This is the root cause of the #12584 ID-regeneration class of bugs and resolves the null-index error in nearly every case.
  2. Is the FK column claimed twice? If the child also has a @ManyToOne to the parent without an explicit @JoinColumn, JPA defaults to the same column the parent's @JoinColumn claims. Pick one owner — the child — and remove the parent's @JoinColumn.
  3. Is the back-reference set on every child before save? Add a @PrePersist/@PreUpdate hook on the parent that asserts child.setParent(this) on every element. See the four quiz-domain entities for the pattern.
  4. Is the collection partially fetched? A JOIN FETCH ... WHERE ... that filters child rows returns a list with null slots. Either fetch the full collection or stop using @OrderColumn and migrate to Set + @OrderBy on a domain field.
  5. Is some code path saving children directly? A childRepository.save(...) or @Modifying @Query that inserts/deletes child rows without going through the parent's collection desyncs the order column. Route through parent.addChild(child) + parentRepository.save(parent).

The historical "save the child without parent reference, then save the parent" recipe was a workaround for the unidirectional-@OrderColumn failure mode and is no longer recommended. Fix the mapping instead.

Null Values in Ordered Collections​

null slots appear in an ordered collection when:

  • The collection was partially fetched with a WHERE clause that filtered some children out (Hibernate keeps the indices stable by padding with null); or
  • A previous save under unidirectional @OrderColumn raced and left an index gap.

Either fetch the full collection before mutating it, or — preferably — replace @OrderColumn with Set + @OrderBy on a domain field (see the alternative pattern above). Defensive Java-side filtering (e.g., removeNullResults) is a smell, not a fix.

Search documentation