Translate SQL into Hibernate HQL

I’m using Spring boot along with Hibernate. I’ve only recently started using Java so I’m not quite good at it.

I have a OneToMany unidirectional relationship with a join table.

RssUrl Table

CREATE TABLE `rss_url` (
    `id` BIGINT(19) NOT NULL,
    `created_at` DATETIME(6) NOT NULL,
    `updated_at` DATETIME(6) NULL DEFAULT NULL,
    `url` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

User Table

CREATE TABLE `user` (
    `id` BIGINT(19) NOT NULL,
    `created_at` DATETIME(6) NOT NULL,
    `updated_at` DATETIME(6) NULL DEFAULT NULL,
    `email` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `is_active` BIT(1) NULL DEFAULT NULL,
    `password` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `email_unique_constraint_1873213` (`email`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

Join table

CREATE TABLE `user_rssurl` (
    `user_id` BIGINT(19) NOT NULL,
    `rss_url_id` BIGINT(19) NOT NULL,
    UNIQUE INDEX `UK_paw8syp4ru29oqh7430u2c7vl` (`rss_url_id`) USING BTREE,
    INDEX `FK7gp98smro2y75g1026ut00jsf` (`user_id`) USING BTREE,
    CONSTRAINT `FK7gp98smro2y75g1026ut00jsf` FOREIGN KEY (`user_id`) REFERENCES `javadb`.`user` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `FKpxci6mn3aayjdpkdigcpekrsy` FOREIGN KEY (`rss_url_id`) REFERENCES `javadb`.`rss_url` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

I’m trying to translate this SQL into HQL

SELECT COUNT(u.id)
FROM `user` u
LEFT JOIN user_rssurl urs ON urs.user_id = u.id
LEFT JOIN rss_url ru ON urs.rss_url_id = ru.id
WHERE u.id = ?
AND urs.rss_url_id = ?

It’s basically checking if a url is already associated with a user. I’m sure it’s not the most efficient sql out there but it gets the job done(I’m more than happy to hear how I can improve it, but that’s besides the point).

So far I got this going for me, which is really not a lot tbh.

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT COUNT(u.id) FROM User u LEFT JOIN user_rssurl urs on urs.user_id = u.id LEFT JOIN RssUrl ru on urs.rss_url_id = ru.id WHERE u.id = ?1 AND urs.rss_url_id = ?2")
    Integer findInRelation(Long uid, Long rssUrlId);
}

I’ve also considered alternative methods, such as letting the database error out and catching the exception but that’ just seems wrong.

This question has 2 parts:

• What is the correct HQL for the above query?

• Is there an easier way to do this? I come from a PHP background. Using ORMs like Doctrine made things a lot easier. One could in fact fetch related entities with considerably less hassle. I find it hard to believe that Hibernate doesn’t have a better way to do this.

I’d love to google it myself but quite frankly I don’t know what to google exactly.

Answer

In actual, java jpa is not friendly with join table query; in there, I can give you two methed only for refer:

1. you can split this query into three base query to complete you question, i know this method is not good;
2. you can define an entity as a join entity, then use @OneToOne or @ManyToOne anaotations to reflect the relation;
3. I aslo has the 3 suggestion, not use jpa but use mybatis, in mybatis, you can direct use your sql lile what you write when query with many table;