数据库执行用的是spring的jdbcTemplete,我在批量插入数据的时候id需要从数据库中查出一个自增的序列值,在循环取这个序列的时候就会抛异常,但也不是一开始就抛,执行到一半的时候就会出来。代码如下:(如果换成其它方式取id就不会有异常)
//批量插入数据(我执行的时候大概会一次插入500条数据)
public void batchInsertRows(String sql,final List<TestBean1> dataSet) throws Exception{
BatchPreparedStatementSetter setter=new BatchPreparedStatementSetter(){
public int getBatchSize(){
return dataSet.size();
}
public void setValues(PreparedStatement ps,int i){
CheckAlarmBean obj = dataSet.get(i);
try{
ps.setString(1,getNextId());
ps.setString(2,obj.getWELL_ID());
}catch(Exception e){
e.printStackTrace();
}
}
};
dbmanager.batchUpdate(sql,setter);
} private int getNextId(){
String sql="Select SEQUENCE_CQ.Nextval As hao from dual";
return dbmanager.queryForInt(sql);//dbmanager 是我配的数据源,异常就从这句抛出来的
}
异常如下:
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))
Caused by: java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:335)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:361)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:442)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:321)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:154)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:283)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:271)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:255)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:236)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:329)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:405)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:413)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:421)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:426)
at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:435)
//批量插入数据(我执行的时候大概会一次插入500条数据)
public void batchInsertRows(String sql,final List<TestBean1> dataSet) throws Exception{
BatchPreparedStatementSetter setter=new BatchPreparedStatementSetter(){
public int getBatchSize(){
return dataSet.size();
}
public void setValues(PreparedStatement ps,int i){
CheckAlarmBean obj = dataSet.get(i);
try{
ps.setString(1,getNextId());
ps.setString(2,obj.getWELL_ID());
}catch(Exception e){
e.printStackTrace();
}
}
};
dbmanager.batchUpdate(sql,setter);
} private int getNextId(){
String sql="Select SEQUENCE_CQ.Nextval As hao from dual";
return dbmanager.queryForInt(sql);//dbmanager 是我配的数据源,异常就从这句抛出来的
}
异常如下:
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))
Caused by: java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:335)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:361)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:442)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:321)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:154)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:283)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:271)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:255)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:236)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:329)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:405)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:413)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:421)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:426)
at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:435)
另外,你的id完全可以不用这么写。把你的sql拿出来看看。
oracle可以这么弄:
insert into tbl values(SEQUENCE_CQ.Nextval,?);
这样你只需要set一个 ps.setString(1,obj.getWELL_ID());
就好了,完全用不着取出来再去设置,麻烦不说,还浪费资源(要查一次数据库)。
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; 就像自己写jdbc连接没有close掉
还有如果出现这样的情况,比如在一个查询过程中,对查询出来的每一项又得拿到另外一个表中去循环查询别的值,这样的时候数据量大的话也会报这样的错,要怎么避免呢?
取不到连接肯定是你没有配置好,要不别人怎么可以呢?
你说的查询出的每一项又拿出去查,为什么查的时候不使用多表联查呢?这个都可以从数据库级别来做。
ps:spring我不懂。