jgre88888 2012-12-04 14:41
浏览 365
已采纳

插入1w条数据不报错，但是插入2w条数据时候报错，为什么呢？

   我用jdbc做了个定时更新数据库的功能，当插入1w条数据时候没点问题，当插入2w条数据时候，就报连接超时的错误接着，但是程序还是没有挂掉，不知道是什么原因就解？
    [code='java']
    package com.jgre.org;

import java.sql.Connection;

import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;


public class DB {
//设置
static int precount=1000;

public static  Connection getConnect() throws Exception{
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
   //?useUnicode=true&characterEncoding=utf8
   Connection conn= DriverManager.getConnection("jdbc:sqlserver://146.12.62.208:1433;DatabaseName=LHJGXN", "sa", "flying-321");
        return conn;       
  }

public static void free(ResultSet rs,PreparedStatement ps,Connection conn){
try {
  if(rs!=null) rs.close();
  if(ps!=null) ps.close();
  if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void free(PreparedStatement ps,Connection conn){
try {
  if(ps!=null) ps.close();
  if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//插入数据
public synchronized int insertObject(List<AJXX> list){
list=DB.initArray(list);
Connection conn=null;
PreparedStatement ps=null;
try {
conn=DB.getConnect();
//关闭自动提交
conn.setAutoCommit(false);
//清空数据库
ps=conn.prepareStatement("truncate table LHJGXN.dbo.TB_AJXX");
ps.executeUpdate();

long startTime=System.currentTimeMillis();
String sql="insert into LHJGXN.dbo.TB_AJXX( " +
"AH,NH,ZH,SAXH,AY,LARQ,LAR,CBT,SJCBT, " +
"CBR,AJLB,SPCX,SYCX,SFDA,SFYA,AJSJ,ZZRQ," +
"JARQ, JAFS,GDRQ,DTXP,FDSXDQR,KCSXTS,SXDQR," +
"AJZT,XLABZ)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
ps=conn.prepareStatement(sql);
for(int i=0;i<list.size();i++){
System.out.println(i);
AJXX a=list.get(i);
if(a.getAH()!=null&&!a.getAH().equals("")){
                 ps.setString(1, a.getAH());
                 ps.setInt(2, a.getNH());
                 ps.setString(3, a.getZH());
                 ps.setInt(4, a.getSAXH());
                 ps.setInt(5, a.getAY());
                // Date d=new Date();
                 if(tDate(a.getLARQ())==0){
                 ps.setDate(6,  null);
                 }else{
                 ps.setDate(6,  new Date(tDate(a.getLARQ())));
                 }
                 ps.setString(7, a.getLAR());
                 ps.setString(8, a.getCBT());
                 ps.setString(9, a.getSJCBT());
                 ps.setString(10, a.getCBR());
                 ps.setString(11, a.getAJLB());
                 ps.setString(12, a.getSPCX());
                 ps.setString(13, a.getSYCX());
                 ps.setString(14, a.getSFDA());
                 ps.setString(15, a.getSFYA());
                 ps.setString(16, a.getAJSJ());
                 if(tDate(a.getZZRQ())==0){
                ps.setDate(17, null);
                 }else{
                 ps.setDate(17, new Date(tDate(a.getZZRQ())));
                 }
                 if(tDate(a.getJARQ())==0){
                 ps.setDate(18, null);
                 }else{
                ps.setDate(18, new Date(tDate(a.getJARQ())));
                 }
                 ps.setString(19, getJAFS(a.getJAFS(),conn));
                 if(tDate(a.getGDRQ())==0){
                ps.setDate(20, null);
                 }else{
                 ps.setDate(20, new Date(tDate(a.getGDRQ())));
                 }
                 ps.setInt(21, a.getDTXP());
                 ps.setString(22, a.getFDSXDQR());
                 ps.setInt(23, a.getKCSXTS());
                 ps.setString(24, a.getSXDQR());
                 ps.setString(25, a.getAJZT());
                 ps.setString(26, a.getXLABZ());
   // ps.executeUpdate();
    ps.addBatch();
    }
//每precount次就批量处理一次
if((i+1)%precount==0){
ps.executeBatch();
//因为不确定总数所以没有去分很多批去执行，我整体做了个批量处理
   }
}
//提交
//ps.executeBatch();

conn.commit();
ps.clearBatch();
long endTime=System.currentTimeMillis();
System.out.println("插入数据时间为："+(endTime-startTime)+"ms");
return (int)(endTime-startTime);
} catch (Exception e) {
try {
//如果出现问题就回滚
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
PrintLog.writeLog("error:"+e.toString());
}finally{
DB.free(ps, conn);
}
return -1; 
}
//根据案由名称获取案由编号
public static int getAYBH(String AYMC){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
    conn=DB.getConnect();
ps=conn.prepareStatement("select AYBH from LHJGXN.dbo.TB_AY where AYMC=?");
ps.setString(1, AYMC);
rs=ps.executeQuery();
if(rs.next()){
return rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DB.free(rs, ps, conn);
}
return -1;
}
//得到字号
public static String getZH(String ZHMC){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=DB.getConnect();
ps=conn.prepareStatement("select ZH from LHJGXN.dbo.TB_ZHXX where ZHMC=?");
ps.setString(1, ZHMC);
rs=ps.executeQuery();
if(rs.next()){
return rs.getString(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DB.free(rs, ps, conn);
}
return null;
}
//跟根据结案名称获取JAFS、AJLB、SPCX
public static int getInfo(String cmd,String MC,Connection conn){
//Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
String sql=null;
if(cmd.equalsIgnoreCase("JAFS")){
sql="select JAFS from LHJGXN.dbo.TB_JAFS where MC=?";
}else if(cmd.equalsIgnoreCase("AJLB")){
sql="select AJLB from LHJGXN.dbo.TB_JAFS where MC=?";
}else if(cmd.equalsIgnoreCase("SPCX")){
sql="select SPCX from LHJGXN.dbo.TB_JAFS where MC=?";
}
try {
conn=DB.getConnect();
ps=conn.prepareStatement(sql);
ps.setString(1, MC);
rs=ps.executeQuery();
if(rs.next()){
return rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DB.free(rs, ps, conn);
}
return -1;
}
public static long tDate(String d){
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
   try {
   if(d!=null&&!d.equals("")){
java.util.Date date=sdf.parse(d);
return date.getTime();}
} catch (ParseException e) {
e.printStackTrace();
}
return 0;
}
public static String getJAFS(String jafs,Connection conn){
if(jafs!=null&&!jafs.trim().equals("")){
// Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=DB.getConnect();
ps=conn.prepareStatement("select JAFS from LHJGXN.dbo.TB_JAFS where MC=?");
ps.setString(1, jafs);
rs=ps.executeQuery();
if(rs.next()){
return rs.getString(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DB.free(rs,ps, conn);
}
}
return null;
}
public static List<AJXX> initArray(List<AJXX> d){
while(d!=null&d.size()>0&d.size()%precount!=0){
AJXX a=new AJXX();
d.add(a);
}
System.out.println("格式化后的数据长度："+d.size());
return d;
}

}


  

  然后报的错误是这样的
  



   22238

22239

22240

com.microsoft.sqlserver.jdbc.SQLServerException: 到主机  的 TCP/IP 连接失败。 java.net.BindException: Address already in use: connect

    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

    at java.sql.DriverManager.getConnection(DriverManager.java:582)

    at java.sql.DriverManager.getConnection(DriverManager.java:185)

    at com.jgre.org.DB.getConnect(DB.java:22)

    at com.jgre.org.DB.getJAFS(DB.java:226)

    at com.jgre.org.DB.insertObject(DB.java:98)

    at com.jgre.org.PickTask$1.run(TimerUse.java:120)

    at java.util.TimerThread.mainLoop(Timer.java:512)

    at java.util.TimerThread.run(Timer.java:462)

com.microsoft.sqlserver.jdbc.SQLServerException: 该连接已关闭。

    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source)

    at com.jgre.org.DB.insertObject(DB.java:130)

    at com.jgre.org.PickTask$1.run(TimerUse.java:120)

    at java.util.TimerThread.mainLoop(Timer.java:512)

    at java.util.TimerThread.run(Timer.java:462)

com.microsoft.sqlserver.jdbc.SQLServerException: 该连接已关闭。

    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(Unknown Source)

    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setString(Unknown Source)

    at com.jgre.org.DB.insertObject(DB.java:98)

    at com.jgre.org.PickTask$1.run(TimerUse.java:120)

    at java.util.TimerThread.mainLoop(Timer.java:512)

    at java.util.TimerThread.run(Timer.java:462)

2012-12-04 00:54:08 00:54

2012-12-04 00:55:08 00:55

2012-12-04 00:56:08 00:56


    求遇到过类似情况的，麻烦告诉下解决办法，急救，谢谢！！
  • 写回答

4条回答 默认 最新

  • dohkoos 2012-12-04 15:12
    关注

    getJAFS中创建了太多的数据库连接。也许你要说每次你都通过DB.free()已经释放了数据库连接，但实际上conn.close()操作并不能立即释放绑定的端口，而是把端口设置为TIME_WAIT状态。改写你的程序吧！

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题？

悬赏问题

  • ¥15 如何让企业微信机器人实现消息汇总整合
  • ¥50 关于#ui#的问题：做yolov8的ui界面出现的问题
  • ¥15 如何用Python爬取各高校教师公开的教育和工作经历
  • ¥15 TLE9879QXA40 电机驱动
  • ¥20 对于工程问题的非线性数学模型进行线性化
  • ¥15 Mirare PLUS 进行密钥认证？（详解）
  • ¥15 物体双站RCS和其组成阵列后的双站RCS关系验证
  • ¥20 想用ollama做一个自己的AI数据库
  • ¥15 关于qualoth编辑及缝合服装领子的问题解决方案探寻
  • ¥15 请问怎么才能复现这样的图呀