在Hibernate(springboot)中用左连接和分页编写SQL本机查询 [英] Write sql native query with left join and pagination in hibernate (springboot)

问题描述

我使用的是Spring数据JPA，我想在我的存储库中编写一个SQL查询.

I'm using spring data JPA and I want to write a SQL query in my repository.

我有以下SQL查询(请注意LEFT JOIN):

I have a following SQL query (notice the LEFT JOIN):

SELECT * FROM institution LEFT JOIN 
(select * from building_institutions where building_institutions.building_id = 1) as reserved_institutions
ON reserved_institutions.institutions_user_id = institution.user_id
WHERE reserved_institutions.institutions_user_id is null;

我想在我的InstitutionRepository中执行它，如下所示:

and i want to execute it in my InstitutionRepository which is as follows:

@Repository
public interface InstitutionRepository extends JpaRepository<Institution, Long>, PagingAndSortingRepository<Institution,Long> {
    // doesn't work
    //@Query("SELECT b.institutions as bi FROM Building b left join Institution i WHERE bi.building_id not in :id")
    Page<Institution> findPotentialInstitutionsByBuildingId(@Param("id") Collection<Long> id, Pageable pageable);

    // doesn't work    
    @Query(
            value = "SELECT * FROM kits_nwt.institution LEFT JOIN\n" +
                    "(SELECT * FROM kits_nwt.building_institutions WHERE kits_nwt.building_institutions.building_id = ?1) AS reserved_institutions\n" +
                    "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id\n" +
                    "WHERE reserved_institutions.institutions_user_id IS null ORDER BY ?#{#pageable}",
            nativeQuery = true)
    Page<Institution> findPotentialInstitutionsByBuildingId(Long userId, Pageable pageable);
}

因此，我想让所有不在使用特定ID(将作为参数发送给它的ID)建立的机构.

So, I want to get all institutions which are not in building with certain ID (which I will send as a parameter).

机构:

建筑机构:

我想要的内容(在此查询中，出于演示目的，ID设置为1)

What I want: (in this query, the id is set on 1, for presentation purposes)

我查看了许多SO问题和答案(例如这一个)，但我无法找出解决方案.

I have looked at many SO questions and answers (such as this one) but I haven't been able to figure out the solution.

那么，如何编写此查询，以便获得所需的信息?

So, how do I write this query so that I get what I want?

@KevinAnderson当前，我正在尝试:

@KevinAnderson Currently, I'm trying with:

@Query(
        value = "SELECT username, password, description, location, title, user_id FROM (institution INNER JOIN user ON institution.user_id = user.id) LEFT JOIN\n" +
                "(SELECT * FROM building_institutions WHERE building_institutions.building_id = 1) AS reserved_institutions\n" +
                "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id\n" +
                "WHERE reserved_institutions.institutions_user_id IS null ORDER BY ?#{#pageable}",
        nativeQuery = true)
Page<Institution> findPotentialInstitutionsByBuildingId(Long userId, Pageable pageable);

我得到了这个例外:

MySQLSyntaxErrorException:您的SQL语法有一个错误.请查看与您的MySQL服务器版本相对应的手册，以获取正确的语法，以在第2行的"WHERE)FROM building_institutions WHERE building_institutions.building_id = 1)A"附近使用

MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE) FROM building_institutions WHERE building_institutions.building_id = 1) A' at line 2

@StanislavL在这里:

Edit 2:

@StanislavL here it is:

问题是您的查询使我得到ID为25的机构，因为它同时位于ID为1的建筑物和ID为2的建筑物中.当您执行JOIN时，两个机构表中的机构ID均为2，行均为25和building_institutions表.然后，您的WHERE条件从这两行中删除了一个，而我得到的是机构ID为25的一行，而我不想要什么.

The problem is that your query gets me the institution with ID 25 because it is in both building with ID 1 and building with ID 2. When you do a JOIN, you have 2 rows ON institution ID being 25 in both institution table and building_institutions table. Then, your WHERE condition removes one from those two rows and I get one row where instituiton ID is 25, and I don't want what.

以下是上述图片:

• 我的查询是分页的(我在问题标题中添加了"with pagination")
• 我不是使用@NamedQuery，而是使用@Query
• 我的错误是我没有将countQuery参数写入@Query批注

• My query is with pagination (I added "with pagination" to the question title)
• I'm not using @NamedQuery but @Query
• My mistake was that I didn't write countQuery parameter to the @Query annotation

推荐答案

我解决了...

查询需要如下所示:

@Query(
        value = "SELECT * FROM \n" +
                "(institution INNER JOIN user ON institution.user_id = user.id) \n" +
                "LEFT JOIN \n" +
                "(SELECT * FROM \n" +
                "building_institutions \n" +
                "WHERE building_id = :userId)\n" +
                " AS reserved_institutions \n" +
                "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id \n" +
                " where reserved_institutions.institutions_user_id IS null \n"
                + "ORDER BY ?#{#pageable}"
        ,
        countQuery = "SELECT count(*) FROM \n" +
                "(institution INNER JOIN user ON institution.user_id = user.id) \n" +
                "LEFT JOIN \n" +
                "(SELECT * FROM \n" +
                "building_institutions \n" +
                "WHERE building_id =:userId)\n" +
                " AS reserved_institutions \n" +
                "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id \n" +
                "where reserved_institutions.institutions_user_id IS null \n" +
                "ORDER BY ?#{#pageable}",
        nativeQuery = true)
Page<Institution> findPotentialInstitutionsByBuildingId(@Param("userId") Long userId, Pageable pageable);

当我的查询如下时，我在WHERE附近的第4行出现错误:

I was getting the error at line 4 near WHERE, when my query looked like this:

@Query(
        value = "SELECT username, password, description, location, title, user_id FROM (institution INNER JOIN user ON institution.user_id = user.id) LEFT JOIN\n" +
                "(SELECT * FROM building_institutions WHERE building_institutions.building_id = 1) AS reserved_institutions\n" +
                "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id\n" +
                "WHERE reserved_institutions.institutions_user_id IS null ORDER BY ?#{#pageable}",
        nativeQuery = true)
Page<Institution> findPotentialInstitutionsByBuildingId(Long userId, Pageable pageable);

那是因为我没有将countQuery参数添加到@Query批注中.

and that was because I didn't add the countQuery parameter to the @Query annotation.

非常感谢所有尝试提供帮助的人.

Big thanks to all of you who tried to help.

我希望我可以为别人节省很多时间的痛苦.

I hope that I save someone else many hours of misery.

干杯！ :)

这篇关于在Hibernate(springboot)中用左连接和分页编写SQL本机查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！