Akka Jaworek
Reputation: 2120
Bidirectional OneToMany relation causing maximum open cursors exceeded
I have 3 tablels Foo, Bar and Foo_x_Bar_Status, in my service im trying to create new record for Foo_x_Bar_Status. Im fetching managed object of Foo and managed object of Bar, then im creating new object of FooBarStatus and set previously fetched Foo and Bar objects on it. Then im trying to persist my newly created FooBarStatus wirh entityManager.merge() (it needs to be merge as i need to return FooBarStatus managed object without searching for it again), however this merge fails with message: java.sql.SQLException: - ORA-01000: maximum open cursors exceeded, how can i solve this issue?
P.S. i know i can just remove collections from Foo and Bar and access is via dedicated service, however it is essential for me to have this bidirectional relation
Foo:
@Entity(name = "Foo")
@Table(name = "FOO")
public class Foo implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "ID")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FOO_SEQ")
private Long id;
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.PERSIST, mappedBy = "foo", orphanRemoval = true)
private Set<FooBarStatus> fooBarStatus;
}
Bar:
@Entity(name = "Bar")
@Table(name = "BAR")
public class BAR implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "ID")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "BAR_SEQ")
private Long id;
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.PERSIST, mappedBy = "bar", orphanRemoval = true)
private Set<FooBarStatus> fooBarStatus;
}
FooBarStatus:
@Entity(name = "FooBarStatus")
@Table(name = "Foo_x_Bar_Status")
public class FooBarStatus implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "ID")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FOO_BAR_STATUS_SEQ")
private Long id;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "FOO_ID", nullable = false)
private Foo foo;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "BAR_ID", nullable = false)
private Bar bar;
Service
@Service
public class FooBarStatusServiceImpl implements FooBarStatus {
@Autowired
private FooRepository fooRepository;
@Autowired
private BarRepository barRepository;
@Autowired
private FooBarStatusReposiotry fooBarStatusReposiotry;
@Override
public FooBarStatus createFooBarStatus(Foo foo, Bar bar) {
//make foo nd bar managed
foo = fooRepository.createFoo(foo);
bar = barRepository.createBar(bar);
FooBarStatus fooBarStatus = new FooBarStatus();
fooBarStatus.setFoo(foo);
fooBarStatus.setBar(bar);
return fooBarStatusReposiotry.createFooBarStatus(fooBarStatus);
}
}
FooBarStatusReposioty:
@Repository
public class FooBarStatusReposiotyImpl implements FooBarStatusReposioty {
@PersistenceContext
public EntityManager entityManager;
@Override
public FooBarStatus createFooBarStatus(FooarStatus fooBarStatus){
return entityManager.merge(fooBarStatus);
}
}
Stacktrace:
2018-11-28 16:43:02 ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet] with root cause
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
at sun.reflect.GeneratedMethodAccessor154.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy119.executeQuery(Unknown Source)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:434)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:186)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:121)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
at org.hibernate.loader.collection.plan.AbstractLoadPlanBasedCollectionInitializer.initialize(AbstractLoadPlanBasedCollectionInitializer.java:88)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:688)
at org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:75)
at org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1991)
at org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:570)
at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:252)
at org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:566)
at org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:135)
at org.hibernate.collection.internal.PersistentSet.hashCode(PersistentSet.java:430)
at com.iwork.server.defautmodel.User.hashCode(User.java:20)
at com.iwork.server.defautmodel.UserNotification.hashCode(UserNotification.java:11)
at java.util.HashMap.hash(HashMap.java:339)
at java.util.HashMap.put(HashMap.java:612)
at java.util.HashSet.add(HashSet.java:220)
at java.util.AbstractCollection.addAll(AbstractCollection.java:344)
at org.hibernate.collection.internal.PersistentSet.endRead(PersistentSet.java:327)
at org.hibernate.engine.loading.internal.CollectionLoadContext.endLoadingCollection(CollectionLoadContext.java:234)
at org.hibernate.engine.loading.internal.CollectionLoadContext.endLoadingCollections(CollectionLoadContext.java:221)
at org.hibernate.engine.loading.internal.CollectionLoadContext.endLoadingCollections(CollectionLoadContext.java:194)
at org.hibernate.loader.plan.exec.process.internal.CollectionReferenceInitializerImpl.endLoading(CollectionReferenceInitializerImpl.java:154)
at org.hibernate.loader.plan.exec.process.internal.AbstractRowReader.finishLoadingCollections(AbstractRowReader.java:249)
at org.hibernate.loader.plan.exec.process.internal.AbstractRowReader.finishUp(AbstractRowReader.java:212)
at org.hibernate.loader.plan.exec.process.internal.ResultSetProcessorImpl.extractResults(ResultSetProcessorImpl.java:133)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:122)
at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
at org.hibernate.loader.collection.plan.AbstractLoadPlanBasedCollectionInitializer.initialize(AbstractLoadPlanBasedCollectionInitializer.java:88)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:688)
at org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:75)
at org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1991)
at org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:570)
at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:252)
at org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:566)
at org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:135)
at org.hibernate.collection.internal.PersistentSet.hashCode(PersistentSet.java:430)
at com.application.server.defautmodel.Bar.hashCode(Bar.java:20)
at com.application.server.defautmodel.Foo.hashCode(Foo.java:11)
at java.util.HashMap.hash(HashMap.java:339)
at java.util.HashMap.put(HashMap.java:612)
at java.util.HashSet.add(HashSet.java:220)
at java.util.AbstractCollection.addAll(AbstractCollection.java:344)
the stacktrace is much longer but it seems like its looping.
Foo.java:11 and Bar.java:20 are pointing to @Data annotation from Lombok
Upvotes: 0
Views: 750
Answers (2)
Akka Jaworek
Reputation: 2120
As suggested by @Chris in comment, root cause was that usage of @Data annotation generated a HashCode calculation method and since Foo, Bar and FooBarStatus had lazy initialized attributes with bidirectional relation, hash calculation attempted to fetch data and lopped back infinitely. Since all Foo, Bar and FooBarStatus are unique by id's it is not necerssary to include joined attribtes in hash calculation. Therefore as per https://projectlombok.org/features/EqualsAndHashCode i have added @EqualsAndHashCode.Exclude to bidirectional lazy initialized attributes which excluded the from hash calculation. Example below:
@Entity(name = "Foo")
@Table(name = "FOO")
public class Foo implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "ID")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "FOO_SEQ")
private Long id;
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.PERSIST, mappedBy = "foo", orphanRemoval = true)
@EqualsAndHashCode.Exclude
private Set<FooBarStatus> fooBarStatus;
}
Upvotes: 1
Alan Jhone
Reputation: 1
This error is caused by Connections, Statements, and ResultSets left open. Example,
Connection with = getConnection();
PreparedStatement ps = con.prepareStatement
ResultSet rs = ps.executeQuery ();
So, you just close the
rs.close ();
ps.close ();
con.close ();
option is to use a try {} catch (e) {} finally {} and close in the finally
Upvotes: 0
Related Questions
- Spring Boot JPA - OneToMany relationship causes infinite loop
- JPA hibernate one to many infinite loop bidirectional relation problem
- wiered behavior of OneToMany relationship while using spring boot and spring data jpa
- Spring Data JPA OneToMany Relationship weird error
- problem with a bidirectional oneToMany relationship in Java + spring
- Unidirectional OneToMany relationship induces UniqueConstraint
- JPA Unidirectional @OnetoMany fails
- JPA/Hibernate: bidirectional OneToMany/ManyToOne relation only works unidirectional
- JPA - OneToMany, ManyToOne, OneToOne problem
- Bidirectional OneToMany/ManyToOne mapping in OpenJPA JPQL problem