java-exceeds the maximum number of open cursors __java

In Java, every time you open a statement object JDBC will open a cursor in the database to perform the operation, after the business must immediately shut down, otherwise once multiple open and do not shut down will cause ORA-01000: exceeded the maximum number of open cursors error, If you need to loop to establish a statement object to the final unified commit, you can do the activity in the loop immediately shut down, the business in the uncommitted state after the shutdown, until the execution of the Connection.commit event before the activity is submitted.

Oracle database open_cursors Default value is 300, the normal situation is sufficient, ORA-01000 error is basically a program does not close the cursor, more in the Java program using connection pool to connect the database, Open the statement without closing the connection back to the connection pool or loop open the statement object without closing, please carefully check your program code

Like this code:

for (int j = 0; J < Count; J +) {
						DataObject d = dataobjects.get (j);
						String sql = D.getsql ();
						statement = conn.preparestatement (SQL);
						list<datacell> datacells = D.getdataarray ();
						if (d.getsheettype () = = Sheettype.ext) {
							Batchexecextsql (d, statement, datacells);
						} else {
							statement = conn . preparestatement (SQL);
							for (int i = 0; i < datacells.size (); i++) {
								Datacell Datacell = Datacells.get (i);
								Object val = Datacell.getvalue ();
								Setsqlvalue (statement, Datacell.getdatatype (), Val, i + 1);
							}
							Statement.execute ();}}
				Finally {
					if (statement!= null) {
//						Statement.clearbatch ();
						Statement.close ();
					}

becomes a table multiline data insert, the statement in the For loop is a new object each time, and the object produces a cursor in the database, and finanlly finally closes only one of the statement. The rest can only be handed over to the JVM. The garbage collection mechanism is complete. If the above code changes to:

PreparedStatement statement = null;
					try {Connection conn = session.connection ();
					Session.setflushmode (flushmode.always); Concurrent testing is expected to be elevated by adding transaction isolation levels (partly), and later changing the transaction elements of Read operations valid//Conn.settransactionisolation (Transactiondefinition.isolation_read
					_committed);
					int count = Dataobjects.size ();
						for (int j = 0; J < Count; J +) {DataObject d = dataobjects.get (j);
						String sql = D.getsql ();
						statement = conn.preparestatement (SQL);
						list<datacell> datacells = D.getdataarray ();
						if (d.getsheettype () = = Sheettype.ext) {batchexecextsql (d, statement, DataCells);
								else {for (int i = 0; i < datacells.size (); i++) {Datacell Datacell = Datacells.get (i);
								Object val = Datacell.getvalue ();
							Setsqlvalue (statement, Datacell.getdatatype (), Val, i + 1);
							} statement.execute ();
							if (statement!= null) {statement.close (); finally {if (STAT)}}}}Ement!= null) {Statement.clearbatch ();
					Statement.close ();                                                                                                    }
				}

This does not occur, and the problem occurs only in the case of concurrent operations or multiple operations in a transaction, typically a small amount of Java-reclaimed, and database cursors are sufficient.

The following code, though a batch, does not conform to the specification of the Java JDBC operation, also causes problems with cursors that are not sufficient.

public void Batchexecsqlwitharray (final String insertsql, final list<systemlog> dataobjects) throws SQLException {this.gethibernatetemplate (). Execute (new Hibernatecallback () {@SuppressWarnings ("deprecation") public Object DoI
				NHibernate (Session session) throws Hibernateexception, SQLException {preparedstatement statement = null;
					try {Connection conn = session.connection ();
					int count = Dataobjects.size (); for (int j = 0; J < Count; J +) {Systemlog log = Dataobjects.get (j);statement = conn.preparestatement (insertsql);UUID UUIDs = Uuid.randomuuid ();
						String uuid = uuids.tostring (). ReplaceAll ("-", "");
						Statement.setstring (1, UUID);
						Statement.setstring (2, log.getipadress ());
						Statement.setint (3, Log.getlogtype (). GetId ());
						Statement.setstring (4, Log.getlogdate ());
						Statement.setstring (5, Log.getmemo ());
						Statement.setstring (6, Log.getmessage ());
						Statement.setint (7, Log.getmodule (). GetId ());
						Statement.setint (8, Log.getrelease ());
						Statement.setstring (9, Log.getuserid ());
					Statement.addbatch ();
					} dataobjects.clear ();
				Statement.executebatch ();
						Finally {if (statement!= null) {Statement.clearbatch ();
					Statement.close ();
			} return null;
	}
		}); }

statement = Conn.preparestatement (insertsql); In the For loop it is also a wrong way to spell out that there is only one object outside the unload.

Database-related close connection resources are more common, discovered during the development process ...