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驱动包