jdbc SQL Server 返回的响应不完整。连接已关闭。
各位同行大家好
现有一张Excel表，想通过JXL包读取数据后，再将结果集写入数据库，但在提交事务报了这个错，请各位给些意见，谢谢
------------------------------------------------------
com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server 返回的响应不完整。连接已关闭。
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1654)
at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:4844)
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6150)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:1284)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:1209)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1173)
at ExcelReadPre.writeToDb(ExcelReadPre.java:118)
at ExcelReadPre.testRun(ExcelReadPre.java:29)
at ExcelReadPre.main(ExcelReadPre.java:15)
______________________________________________________________________________________________________________
代码如下
import jxl.Cell;
import jxl.Sheet;
import java.io.File;
import java.io.InputStream;
import java.sql.*;
import java.util.Vector;
import java.io.FileInputStream;
import jxl.Workbook;
public class ExcelReadPre {
public static void main(String[] args) {
ExcelReadPre cr = new ExcelReadPre();
long start = System.currentTimeMillis();// 记录开始时间
cr.testRun();// 开始执行
long end = System.currentTimeMillis();
System.out.println("============耗：====" + ((end - start) / 1000.0));
}
public void testRun() {
String path = "J:\\Loader\\excel";
try {
File fileDir = new File(path);
File files[] = fileDir.listFiles();
for (int i = 0; i < files.length; i++) {
System.out.println("P第" + (i + 1) + "个文件：====="
+ files[i].getAbsolutePath());
this.writeToDb(files[i].getAbsolutePath());
}
if (this.con != null && !con.isClosed()) {
this.con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 执行指定的Sql
*/
private String url = "jdbc:sqlserver://localhost:1433;database=test";
private Connection con = null;
PreparedStatement pst = null;
public void getCOnn() {
// System.out.println("=========="+sql);
try {
if (con == null || con.isClosed()) {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url, "sa", "fuckguoqinglin");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 导入一指定文件数据 批处理SQL
*
* @param filePath
*/
public void writeToDb(String filePath) {
String sql = "insert into [dbo].[5lpriceticket] values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
this.getCOnn();
// 关闭事务自动提交
con.setAutoCommit(true);
pst = con.prepareStatement(sql);
Vector<Cell[]> v = readExcel(filePath);
int i = 0;
for (Cell[] cells : v) {
int j = 1;
pst.setString(j++, cells[0].getContents());
pst.setString(j++, cells[1].getContents());
pst.setString(j++, cells[2].getContents());
pst.setString(j++, cells[3].getContents());
pst.setString(j++, cells[4].getContents());
pst.setString(j++, cells[5].getContents());
pst.setString(j++, cells[6].getContents());
pst.setString(j++, cells[7].getContents());
pst.setString(j++, cells[8].getContents());
pst.setString(j++, cells[9].getContents());
pst.setString(j++, cells[10].getContents());
pst.setString(j++, cells[11].getContents());
pst.setString(j++, cells[12].getContents());
pst.setString(j++, cells[13].getContents());
pst.setString(j++, cells[14].getContents());
pst.setString(j++, cells[15].getContents());
pst.setString(j++, cells[16].getContents());
pst.setString(j++, cells[17].getContents());
pst.setString(j++, cells[18].getContents());
pst.setString(j++, cells[19].getContents());
pst.setString(j++, cells[20].getContents());
pst.setString(j++, cells[21].getContents());
pst.setString(j++, cells[22].getContents());
pst.setString(j++, cells[23].getContents());
pst.setString(j++, cells[24].getContents());
pst.addBatch();
i++;
if (i > 6000) {
// 防止一次数量过大，导致内存泄漏
pst.executeBatch();
con.commit();
pst.clearBatch();
i = 0;
continue;
}
// 把一个SQL命令加入命令列表
}
// 执行批量更新
pst.executeBatch();
// 语句执行完毕，提交本事务
con.commit();
pst.clearBatch();
pst.close();
// System.gc();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*
* 方法名：readExcel 参数：filePath为Execl文件的绝对路径 用途：读取Execl数据,放在数组中返回 返回类型：Vector
*/
public static Vector<Cell[]> readExcel(String filePath) {
Vector<Cell[]> v = new Vector<Cell[]>();
Workbook rwb = null;
try {
InputStream is = new FileInputStream(filePath);
rwb = Workbook.getWorkbook(is);
// 获取第一张Sheet表
Sheet rs = rwb.getSheet(0);
for (int i = 1; i < rs.getRows(); i++) {
v.add(rs.getRow(i));// 放入一行记录
}
is.close();
rwb.close();
System.gc();
} catch (Exception e) {
e.printStackTrace();
}
return v;
}
}
[解决办法]
1.// 关闭事务自动提交
con.setAutoCommit(true); ==> con.setAutoCommit(false);
2. 最后的pst.executeBatch();前要pst.addBatch();
3.con.commit();可以放到最后一句，用一次就行