Btukfyl

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

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

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;

}