导入数据到mysql数量_一次导入大量数据(记录)到MySQL数据库

本程序是一次从excel文件中导入多条记录(十几万条)到MySQL数据库！

注意事项：不要一条一条的提交，否则到了一定数量会出现以下错误：

The driver was unable to create a connection due to an inability

to establish the client portion of a socket.

This is usually caused by a limit on the number of sockets

imposed by the operating system. This limit is usually

configurable.

For Unix-based platforms, see the manual page for the 'ulimit'

command. Kernel or system reconfiguration may also be required.

For Windows-based platforms, see Microsoft Knowledge Base

Article 196271 (Q196271).

主要是因为提交的太频繁了，线程被阻塞了！不能在进行连接了！

解决办法：把antoCimmit()设置成false

，每提交一定量数据，把autoCimmit()设置成true。然后再设成false。注意每次提交数据不要超过3000条。最好在1000到条2000条。

可以参考下面的程序：

package com.blue411;

import jxl.*;

import java.io.*;

import java.sql.*;

public class ReadXLSToMysql {

public static void main(String[] args) throws

IOException {

String sDBDriver =

"com.mysql.jdbc.Driver";

String dbUrl =

"jdbc:mysql://localhost:3306/test?"

+

"user=root&password=blue&useUnicode=true&characterEncoding=utf8";

Connection conn = null;

Statement stmt = null;

//驱动

try {

Class.forName(sDBDriver);

} catch

(java.lang.ClassNotFoundException e) {

System.err.println("Class

dbconnect not found!" + e.getMessage());

}

//建立连接

try {

conn =

DriverManager.getConnection(dbUrl);

stmt =

conn.createStatement();

} catch (SQLException ex)

{

System.err.println(ex.getMessage()); }

String sql_in = "";

String strtext = "";

jxl.Workbook rwb = null;

String strsub[] = new

String[10];

String sql1 = "";

String sql2 = "insert into

testxls (stdID ,name ,xueyuan ,"

+

"zhuanye ,className ,regyear ,num1 ,num2 ,num3) ";

try {

//

构建Workbook对象 只读Workbook对象

//

直接从本地文件创建Workbook

//

从输入流创建Workbook

InputStream

is = new FileInputStream("E:\\test.xls");

rwb =

Workbook.getWorkbook(is); //

获取第一张Sheet表

Sheet st =

rwb.getSheet(0);

//

获取Sheet表中所包含的总列数

int rsColumns

= st.getColumns();

//

获取Sheet表中所包含的总行数

int rsRows =

st.getRows();

//

获取指这下单元格的对象引用

for (int i =

0; i < rsRows - 1; i++) {

for

(int j = 0; j < rsColumns; j++) {

//

注意在读取时 i和j的位置。i代表行 j代表列。且列在前 行在后

Cell

cell = st.getCell(j, i);

if

(j < rsColumns - 1) {

strtext

= strtext + cell.getContents() + ",";

}

else {

strtext

= strtext + cell.getContents();

}

}

strsub

= strtext.split(",");

//获取

要插入的值

try

{

sql1

= "'" + strsub[0] + "','" + strsub[1] + "'," + "'"

+

strsub[2] + "','" + strsub[3] + "'," + "'"

+

strsub[4] + "'," + strsub[5] + "," + strsub[6]

+

"," + strsub[7] + "," + strsub[8];

System.out.println(sql1);

System.out.println();

strtext

= "";

}

catch (Exception e) {

}

sql_in

= sql2 + "values (" + sql1 + ")";

try

{

stmt.executeUpdate(sql_in); //每一千条记录提交一次

if(i

% 1000 == 0)

{ conn.setAutoCommit(true);

}else{

conn.setAutoCommit(false);

} }

catch (SQLException e) {

e.printStackTrace();

}finally{

try

{ conn.setAutoCommit(true);

}

catch (SQLException e)

{ e.printStackTrace();

} }

}

} catch (Exception e) {

e.printStackTrace();

} finally {

//

操作完成时，关闭对象，翻译占用的内存空间

rwb.close();

}

}

}