Validation of database integrity with Hibernate validator

Ok, I have made some experiments, writing down a small test class. To make things simple I changed the constraint to “User can host at most 1 game”.

public class DBTest {
    @Test
    public void gamesCountConstraintWorking() {
        DBManager.deleteHostedGames("afrixs");
        boolean ok1 = DBManager.createOneGame("afrixs");
        boolean ok2 = DBManager.createOneGame("afrixs");
        int gamesCount = DBManager.deleteHostedGames("afrixs");
        System.out.println("Sync test: count: "+gamesCount+", ok1: "+ok1+", ok2: "+ok2);
        assertTrue(gamesCount <= 1);
        assertTrue(!(ok1 && ok2));
    }

    @Test
    public void gamesCountConstraintWorkingAsync() throws InterruptedException {
        DBManager.deleteHostedGames("afrixs");
        for (int i = 0; i < 30; i++) {
            CreateOneGameRunnable r1 = new CreateOneGameRunnable(1);
            CreateOneGameRunnable r2 = new CreateOneGameRunnable(2);
            Thread t1 = new Thread(r1);
            Thread t2 = new Thread(r2);
            t1.start();
            t2.start();
            int maxCount = 0;
            while (r1.running || r2.running) {
                int count = DBManager.selectHostedGamesCount("afrixs");
                System.out.println("count: "+count);
                maxCount = Math.max(maxCount, count);
            }
            t1.join();
            t2.join();
            int gamesCount = DBManager.deleteHostedGames("afrixs");
            System.out.println("Async test: count: "+gamesCount+", maxCount: "+maxCount+", ok1: "+r1.ok+", ok2: "+r2.ok);
            assertTrue(maxCount <= 1 && gamesCount <= 1);
            assertTrue(!(r1.ok && r2.ok));
        }
    }

    private class CreateOneGameRunnable implements Runnable {
        public boolean ok;
        public boolean running = true;
        private int number;
        CreateOneGameRunnable(int number) {
            this.number = number;
        }
        @Override
        public void run() {
            System.out.println("Starting "+number);
            ok = DBManager.createOneGame("afrixs");
            System.out.println("Finished "+number);
            running = false;
        }
    }
}

First I tried out @Guillaume’s suggestion to use user.getGames().add(game); along with game.setUser(user); when assigning the relation. gamesCountConstraintWorking test was successful, however, gamesCountConstraintWorkingAsync wasn’t. It means that this attitude was successful in maintaining the session consistency (at the cost of fetching all user games), however, the database integrity wasn’t maintained.

A solution that actually worked for both tests was (as @OrangeDog suggested) to add the constraint directly into database schema. MySQL:

DELIMITER $$

CREATE TRIGGER check_user_games_count
AFTER INSERT
ON Games FOR EACH ROW
BEGIN
    DECLARE gamesCount INT;
    SET gamesCount = (SELECT COUNT(id) FROM Games WHERE user_id = new.user_id);
    IF gamesCount > 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'User may host at most 1 game';
    END IF;
END $$

DELIMITER ;

So my summary is that Hibernate works great as a layer above the database to work with, but if you want to make sure the persisted data look like you want, you need to dive directly into your database schema and perform actions in there. (But that’s only the result of this experiment, maybe someone knows a solution for this using Hibernate)

Note: I tried the tests with BEFORE UPDATE triggers and random delays inside the triggers and the tests were successful as well, it seems like some kind of lock is acquired for the table while inserting, so yes, this is a safe solution. (Note2: BEFORE UPDATE trigger for this needs gamesCount+1 > 1 condition and the constraint could fail (not tested) in the case of inserting multiple rows in one query)