" /> Qu'est-ce que le "N+1 sélectionne problème" dans ORM (Object-Relational Mapping) | 2022 codeprofesseur

Qu'est-ce que le "N+1 sélectionne problème" dans ORM (Object-Relational Mapping)

Mots clés : ormselect-n-plus-1orm

meilleur 4 Réponses Qu'est-ce que le "N+1 sélectionne problème" dans ORM (Object-Relational Mapping)

vote vote

99

SELECT * FROM Cars; 
SELECT * FROM Wheel WHERE CarId = ? 
SELECT * FROM Wheel 
vote vote

88

INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence - Part 1', 1)   INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence - Part 2', 2)   INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence - Part 3', 3)   INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence - Part 4', 4) 
INSERT INTO post_comment (post_id, review, id) VALUES (1, 'Excellent book to understand Java Persistence', 1)   INSERT INTO post_comment (post_id, review, id) VALUES (2, 'Must-read for Java developers', 2)   INSERT INTO post_comment (post_id, review, id) VALUES (3, 'Five Stars', 3)   INSERT INTO post_comment (post_id, review, id) VALUES (4, 'A great reference book', 4) 
List<Tuple> comments = entityManager.createNativeQuery("""     SELECT         pc.id AS id,         pc.review AS review,         pc.post_id AS postId     FROM post_comment pc     """, Tuple.class) .getResultList(); 
for (Tuple comment : comments) {     String review = (String) comment.get("review");     Long postId = ((Number) comment.get("postId")).longValue();       String postTitle = (String) entityManager.createNativeQuery("""         SELECT             p.title         FROM post p         WHERE p.id = :postId         """)     .setParameter("postId", postId)     .getSingleResult();       LOGGER.info(         "The Post '{}' got this review '{}'",         postTitle,         review     ); } 
SELECT     pc.id AS id,     pc.review AS review,     pc.post_id AS postId FROM post_comment pc   SELECT p.title FROM post p WHERE p.id = 1 -- The Post 'High-Performance Java Persistence - Part 1' got this review -- 'Excellent book to understand Java Persistence'      SELECT p.title FROM post p WHERE p.id = 2 -- The Post 'High-Performance Java Persistence - Part 2' got this review -- 'Must-read for Java developers'       SELECT p.title FROM post p WHERE p.id = 3 -- The Post 'High-Performance Java Persistence - Part 3' got this review -- 'Five Stars'       SELECT p.title FROM post p WHERE p.id = 4 -- The Post 'High-Performance Java Persistence - Part 4' got this review -- 'A great reference book' 
List<Tuple> comments = entityManager.createNativeQuery("""     SELECT         pc.id AS id,         pc.review AS review,         p.title AS postTitle     FROM post_comment pc     JOIN post p ON pc.post_id = p.id     """, Tuple.class) .getResultList();   for (Tuple comment : comments) {     String review = (String) comment.get("review");     String postTitle = (String) comment.get("postTitle");       LOGGER.info(         "The Post '{}' got this review '{}'",         postTitle,         review     ); } 
@Entity(name = "Post") @Table(name = "post") public class Post {       @Id     private Long id;       private String title;       //Getters and setters omitted for brevity }   @Entity(name = "PostComment") @Table(name = "post_comment") public class PostComment {       @Id     private Long id;       @ManyToOne     private Post post;       private String review;       //Getters and setters omitted for brevity } 
@ManyToOne private Post post; 
List<PostComment> comments = entityManager .createQuery("""     select pc     from PostComment pc     """, PostComment.class) .getResultList(); 
SELECT      pc.id AS id1_1_,      pc.post_id AS post_id3_1_,      pc.review AS review2_1_  FROM      post_comment pc  SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 1 SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 2 SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 3 SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 4 
List<PostComment> comments = entityManager.createQuery("""     select pc     from PostComment pc     join fetch pc.post p     """, PostComment.class) .getResultList();  for(PostComment comment : comments) {     LOGGER.info(         "The Post '{}' got this review '{}'",          comment.getPost().getTitle(),          comment.getReview()     ); } 
SELECT      pc.id as id1_1_0_,      pc.post_id as post_id3_1_0_,      pc.review as review2_1_0_,      p.id as id1_0_1_,      p.title as title2_0_1_  FROM      post_comment pc  INNER JOIN      post p ON pc.post_id = p.id      -- The Post 'High-Performance Java Persistence - Part 1' got this review  -- 'Excellent book to understand Java Persistence'  -- The Post 'High-Performance Java Persistence - Part 2' got this review  -- 'Must-read for Java developers'  -- The Post 'High-Performance Java Persistence - Part 3' got this review  -- 'Five Stars'  -- The Post 'High-Performance Java Persistence - Part 4' got this review  -- 'A great reference book' 
@ManyToOne(fetch = FetchType.LAZY) private Post post; 
List<PostComment> comments = entityManager .createQuery("""     select pc     from PostComment pc     """, PostComment.class) .getResultList(); 
SELECT      pc.id AS id1_1_,      pc.post_id AS post_id3_1_,      pc.review AS review2_1_  FROM      post_comment pc 
for(PostComment comment : comments) {     LOGGER.info(         "The Post '{}' got this review '{}'",          comment.getPost().getTitle(),          comment.getReview()     ); } 
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 1 -- The Post 'High-Performance Java Persistence - Part 1' got this review  -- 'Excellent book to understand Java Persistence'  SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 2 -- The Post 'High-Performance Java Persistence - Part 2' got this review  -- 'Must-read for Java developers'  SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 3 -- The Post 'High-Performance Java Persistence - Part 3' got this review  -- 'Five Stars'  SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 4 -- The Post 'High-Performance Java Persistence - Part 4' got this review  -- 'A great reference book' 
List<PostComment> comments = entityManager.createQuery("""     select pc     from PostComment pc     join fetch pc.post p     """, PostComment.class) .getResultList();  for(PostComment comment : comments) {     LOGGER.info(         "The Post '{}' got this review '{}'",          comment.getPost().getTitle(),          comment.getReview()     ); } 
<dependency>     <groupId>com.vladmihalcea</groupId>     <artifactId>db-util</artifactId>     <version>${db-util.version}</version> </dependency> 
SQLStatementCountValidator.reset();  List<PostComment> comments = entityManager.createQuery("""     select pc     from PostComment pc     """, PostComment.class) .getResultList();  SQLStatementCountValidator.assertSelectCount(1); 
SELECT      pc.id as id1_1_,      pc.post_id as post_id3_1_,      pc.review as review2_1_  FROM      post_comment pc  SELECT p.id as id1_0_0_, p.title as title2_0_0_ FROM post p WHERE p.id = 1  SELECT p.id as id1_0_0_, p.title as title2_0_0_ FROM post p WHERE p.id = 2   -- SQLStatementCountMismatchException: Expected 1 statement(s) but recorded 3 instead! 
vote vote

80

SELECT  table1.* , table2.* INNER JOIN table2 ON table2.SomeFkId = table1.SomeId 
SELECT table1.*  SELECT table2.* WHERE SomeFkId = # 
class House {     int Id { get; set; }     string Address { get; set; }     Person[] Inhabitants { get; set; } }  class Person {     string Name { get; set; }     int HouseId { get; set; } } 
Id Address      Name HouseId 1  22 Valley St Dave 1 1  22 Valley St John 1 1  22 Valley St Mike 1 
Id Address 1  22 Valley St 
SELECT * FROM Person WHERE HouseId = 1 
Name    HouseId Dave    1 John    1 Mike    1 
vote vote

66

***** Table: Supplier ***** +-----+-------------------+ | ID  |       NAME        | +-----+-------------------+ |  1  |  Supplier Name 1  | |  2  |  Supplier Name 2  | |  3  |  Supplier Name 3  | |  4  |  Supplier Name 4  | +-----+-------------------+  ***** Table: Product ***** +-----+-----------+--------------------+-------+------------+ | ID  |   NAME    |     DESCRIPTION    | PRICE | SUPPLIERID | +-----+-----------+--------------------+-------+------------+ |1    | Product 1 | Name for Product 1 |  2.0  |     1      | |2    | Product 2 | Name for Product 2 | 22.0  |     1      | |3    | Product 3 | Name for Product 3 | 30.0  |     2      | |4    | Product 4 | Name for Product 4 |  7.0  |     3      | +-----+-----------+--------------------+-------+------------+ 
// It takes Select fetch mode as a default Query query = session.createQuery( "from Product p"); List list = query.list(); // Supplier is being accessed displayProductsListWithSupplierName(results);  select ... various field names ... from PRODUCT select ... various field names ... from SUPPLIER where SUPPLIER.id=? select ... various field names ... from SUPPLIER where SUPPLIER.id=? select ... various field names ... from SUPPLIER where SUPPLIER.id=? 

Questions similaires