java.sql.SQLException: ORA-01000: 超出打开游标的最大数的原因和解决方案

java.sql.SQLException: ORA-01000: 超出打开游标的最大数的原因和解决方案

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
//oracle连接
public class UserConn {
 
	public static String url="jdbc:oracle:thin:@192.168.49.92:1521:orcl";
	public static String user="ur";
	public static String pwd="ur";
	static {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static Connection getConn(){
		Connection conn=null;
			try {
				conn=DriverManager.getConnection(url,user,pwd);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		return conn;
	}
}

 

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class UserImpl implements UserInterface {
  Connection conn = null;

  public UserImpl() {
    conn = UserConn.getConn();
  }

  public void add(User user) {
    String sql = "insert into register values(?,?,?,?,?,?) ";
    // String userid="4028e4e937167cc80137169";

    String userid = "4028e4e937167cc80137169";
    String strTable = "0123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz";
    int len = strTable.length();
    for (int i = 0; i < 9; i++) {
      int intR = (int) Math.floor(Math.random() * len);
      char c = strTable.charAt(intR);
      userid += c;
    }
    // userid+=UUID.randomUUID().toString();
    System.out.println("id:" + userid);
    PreparedStatement pstm;
    try {
      pstm = conn.prepareStatement(sql);

      pstm.setString(1, userid);
      pstm.setString(2, user.getAddress());
      pstm.setString(3, user.getSex());
      pstm.setDate(4, new Date(user.getBirthday().getTime()));
      pstm.setString(5, user.getName());
      pstm.setInt(6, user.getAge());
      pstm.executeUpdate();
      pstm.close();     //问题所在，没有关闭pstm
      System.out.println("增加成功");
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }

}

 

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
//测试
public class TestUser {

	public static void main(String[] args){
	  UserInterface userdao=new UserImpl();
	  User user=new User();
	  //user.setId("1");
	  user.setAddress("aa");
	  user.setSex("aa");
		
	  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	  try {
		Date birDate = dateFormat.parse("2010-11-11");
		user.setBirthday(birDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}	  
		user.setName("aa");
		user.setAge(20);
		long startTime = System.currentTimeMillis();
		for (int i = 0; i < 500000; i++) {
		  userdao.add(user);
        }
        long endTime = System.currentTimeMillis();
        long totalTime = endTime - startTime;
        System.out.println("add totalTime:"+totalTime);
	  
	}

}

 

这段代码是已经能运行正确的，但是如果不写pstm.close()时会报超出游标最大数的异常，这是因为在循环里面每次都
pstm = conn.prepareStatement(sql);而没有释放，这样每个都占用了一个服务器的游标资源，所以最后抛异常。
附件为oracle驱动包