I am new to working with databases and I have a Spring DATA JPA training project with HSQLDB that has 2 entities with child collections and many to many relationships. One entity is called a Menu, and it contains a Dish list, and vice versa, a Dish contains a set of Menus to which it belongs. When I try to insert a collection of dishes into a table, the Hibernate sends a separate call for each entity. Is there any way to optimize for Hibernate to send one complex query to save the entire child collection? UPD. Sorry for the mistake, actually I'm saving the Menu that contains the Dish collection, and not just saving the Dish collection.
Hibernate:
call next value for global_seq
Hibernate:
/* insert com.atanava.restaurants.model.Menu
*/ insert
into
menus
(date, restaurant_id, id)
values
(?, ?, ?)
Hibernate:
/* insert collection
row com.atanava.restaurants.model.Menu.dishes */ insert
into
dishes_menus
(menu_id, dish_id)
values
(?, ?)
Hibernate:
/* insert collection
row com.atanava.restaurants.model.Menu.dishes */ insert
into
dishes_menus
(menu_id, dish_id)
values
(?, ?)
Hibernate:
/* insert collection
row com.atanava.restaurants.model.Menu.dishes */ insert
into
dishes_menus
(menu_id, dish_id)
values
(?, ?)
Hibernate:
/* insert collection
row com.atanava.restaurants.model.Menu.dishes */ insert
into
dishes_menus
(menu_id, dish_id)
values
(?, ?)
Hibernate:
/* insert collection
row com.atanava.restaurants.model.Menu.dishes */ insert
into
dishes_menus
(menu_id, dish_id)
values
(?, ?)
Menu class:
@NamedQueries({
@NamedQuery(name = Menu.GET, query = "SELECT m FROM Menu m WHERE m.id=:id AND m.restaurant.id=:restaurantId"),
@NamedQuery(name = Menu.BY_RESTAURANT, query = "SELECT m FROM Menu m WHERE m.restaurant.id=:restaurantId ORDER BY m.date DESC"),
@NamedQuery(name = Menu.BY_DATE, query = "SELECT m FROM Menu m WHERE m.date=:date"),
@NamedQuery(name = Menu.BY_REST_AND_DATE, query = "SELECT m FROM Menu m WHERE m.restaurant.id=:restaurantId AND m.date=:date"),
@NamedQuery(name = Menu.DELETE, query = "DELETE FROM Menu m WHERE m.id=:id AND m.restaurant.id=:restaurantId"),
})
@Entity
@Table(name = "menus", uniqueConstraints = {@UniqueConstraint(columnNames = {"restaurant_id", "date"},
name = "restaurant_id_date_idx")})
public class Menu extends AbstractBaseEntity {
public static final String GET = "Menu.get";
public static final String BY_RESTAURANT = "Menu.getAllByRestaurant";
public static final String BY_DATE = "Menu.getAllByDate";
public static final String BY_REST_AND_DATE = "Menu.getByRestAndDate";
public static final String DELETE = "Menu.delete";
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "restaurant_id", nullable = false)
@OnDelete(action = OnDeleteAction.CASCADE)
@NotNull
private Restaurant restaurant;
@ManyToMany(fetch = FetchType.EAGER)
@Fetch(FetchMode.SUBSELECT)
@JoinTable(name = "dishes_menus",
joinColumns = @JoinColumn(name = "menu_id"),
inverseJoinColumns = @JoinColumn(name = "dish_id"))
private List<Dish> dishes;
@Column(name = "date", columnDefinition = "date default current_date", nullable = false)
@NotNull
private LocalDate date;
//constructors, getters, setters
}
Dish class:
@NamedQueries({
@NamedQuery(name = Dish.BY_RESTAURANT, query = "SELECT d FROM Dish d WHERE d.restaurant.id=:restaurantId ORDER BY d.name"),
})
@Entity
@Table(name = "dishes", uniqueConstraints = {@UniqueConstraint(columnNames = {"restaurant_id", "name"},
name = "unique_restaurant_id_dish_name_idx")})
public class Dish extends AbstractNamedEntity {
public static final String BY_RESTAURANT = "Dish.getAllByRestaurant";
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "restaurant_id", nullable = false)
@OnDelete(action = OnDeleteAction.CASCADE)
@NotNull
private Restaurant restaurant;
@Column(name = "price", nullable = false)
@NotNull
private Integer price;
@Column(name = "active", nullable = false, columnDefinition = "bool default true")
private boolean active = true;
@ManyToMany(fetch = FetchType.LAZY, mappedBy = "dishes")
Set<Menu> menus;
//constructors, getters, setters
}
Menu repository where I haven't overridden save method yet: I save menu with original method crudMenuRepository.save(menu)
@Transactional(readOnly = true)
public interface CrudMenuRepository extends JpaRepository<Menu, Integer> {
@Transactional
@Modifying
@Query(name = Menu.DELETE)
int delete(@Param("id") int id, @Param("restaurantId") int restaurantId);
@Query(name = Menu.GET)
Menu get(@Param("id") int id, @Param("restaurantId") int restaurantId);
@Query(name = Menu.BY_REST_AND_DATE)
Menu getByRestAndDate(@Param("restaurantId") int restaurantId, @Param("date") LocalDate date);
@Query(name = Menu.BY_RESTAURANT)
List<Menu> getAllByRestaurant(@Param("restaurantId") int restaurantId);
@Query(name = Menu.BY_DATE)
List<Menu> getAllByDate(@Param("date") LocalDate date);
}
Snippet of initDB.sql file:
CREATE TABLE dishes
(
id INTEGER GENERATED BY DEFAULT AS SEQUENCE GLOBAL_SEQ PRIMARY KEY,
restaurant_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
price INTEGER NOT NULL,
active BOOLEAN DEFAULT TRUE NOT NULL,
FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX unique_restaurant_id_dish_name_idx on dishes (restaurant_id, name);
CREATE TABLE menus
(
id INTEGER GENERATED BY DEFAULT AS SEQUENCE GLOBAL_SEQ PRIMARY KEY,
restaurant_id INTEGER NOT NULL,
date DATE DEFAULT CURRENT_DATE NOT NULL,
CONSTRAINT restaurant_id_date_idx UNIQUE (restaurant_id, date),
FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
);
CREATE TABLE dishes_menus
(
dish_id INTEGER NOT NULL,
menu_id INTEGER NOT NULL,
CONSTRAINT dish_id_menu_id_idx UNIQUE (dish_id, menu_id),
FOREIGN KEY (dish_id) REFERENCES dishes (id) ON DELETE CASCADE,
FOREIGN KEY (menu_id) REFERENCES menus (id) ON DELETE CASCADE
);
UPD2 Here is my spring-db.xml config:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">
<jdbc:initialize-database data-source="dataSource" enabled="${database.init}">
<jdbc:script location="${jdbc.initLocation}"/>
<jdbc:script encoding="utf-8" location="classpath:db/populateDB.sql"/>
</jdbc:initialize-database>
<context:property-placeholder location="classpath:db/hsqldb.properties" system-properties-mode="OVERRIDE"/>
<!--no pooling-->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
p:driverClassName="org.hsqldb.jdbcDriver"
p:url="${database.url}"
p:username="${database.username}"
p:password="${database.password}"/>
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
p:dataSource-ref="dataSource"
p:packagesToScan="com.atanava.**.model">
<!--p:persistenceUnitName="persistenceUnit">-->
<property name="jpaPropertyMap">
<map>
<entry key="#{T(org.hibernate.cfg.AvailableSettings).FORMAT_SQL}" value="${hibernate.format_sql}"/>
<entry key="#{T(org.hibernate.cfg.AvailableSettings).USE_SQL_COMMENTS}" value="${hibernate.use_sql_comments}"/>
<entry key="#{T(org.hibernate.cfg.AvailableSettings).JPA_PROXY_COMPLIANCE}" value="false"/>
<!--<entry key="#{T(org.hibernate.cfg.AvailableSettings).HBM2DDL_AUTO}" value="${hibernate.hbm2ddl.auto}"/>-->
</map>
</property>
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" p:showSql="${jpa.showSql}">
</bean>
</property>
</bean>
<tx:annotation-driven/>
<!-- Transaction manager for a single JPA EntityManagerFactory (alternative to JTA) -->
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"
p:entityManagerFactory-ref="entityManagerFactory"/>
<context:component-scan base-package="com.atanava.**.repository**"/>
<jpa:repositories base-package="com.atanava.**.repository**"/>
</beans>
And here is a persistence.xml file that I created:
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.2"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd">
<persistence-unit name="menu-persistence-unit">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<class>com.atanava.restaurants.model.Menu</class>
<class>com.atanava.restaurants.model.Dish</class>
<properties>
<property name="hibernate.jdbc.batch_size" value="20"/>
</properties>
</persistence-unit>
</persistence>