往SQLserver中批量插入记录

实现从本地读取文本文件，然后插入SQLSERVER数据库

我的SQL server版本是SQLSERVER2012
不多说，直接看程序

package piliangcharu;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;

@SuppressWarnings("unused")
public class inserLINEITEM {
	final static String cfn = "com.microsoft.sqlserver.jdbc.SQLServerDriver";//数据库驱动
	final static String url = "jdbc:sqlserver://IP:端口;DatabaseName=数据库名称";//自行填充
	final static int totalRows=6001215;//文本文件总行数
	final static int insertedOnce=2;//每条insert语句插入的记录数，在SQL server里 最大值为1000.作者建议值1000，这里之所以用2，是因为作者在做性能测试。
	
	public static void main(String[] args) throws FileNotFoundException, InterruptedException {

		// 建立连接
		System.out.println("running");
		Connection con = null;
		PreparedStatement statement = null;
		int res;
		try {
			Class.forName(cfn);
			con = DriverManager.getConnection(url, "用户名", "密码");
		} catch (Exception e) {
			e.printStackTrace();
		}
		//while (true) {
			// 打开文件
			File file = new File("E:/sugon/download/lineitem.tbl");//文件路径
			if (file.isFile() && file.exists()) {
				try {
					InputStreamReader inputStreamReader = new InputStreamReader(new FileInputStream(file), "utf-8");
					BufferedReader bufferReader = new BufferedReader(inputStreamReader);
					String lineStr = null;

					// 定义字段
					int key;
					int partkey;
					int suppkey;
					int linenumber;
					double quantity;
					double exeprice;
					double discount;
					double tax;
					String retflag;
					String linestatus;
					String shipdate;
					String commitdate;
					String recepitdate;
					String shipstruct;
					String shipmode;
					String commit;

					String sql1, sql2, sql3, sql;
					sql1 = "INSERT INTO LINEITEM(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE";
					sql2 = ",L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT)VALUES";
//6001215
					// 开始生成SQL语句,每次插入insertedOnce 行
					try {
						for (int j = 0; j < totalRows / insertedOnce; j++) {
							sql3 = "";
							for (int i = 0; i < insertedOnce; i++) {
								Thread.sleep(500);// 休眠0.5秒（这里可删除，我之所以加上它是因为我要做性能测试）
								lineStr = bufferReader.readLine();
								String[] arr = lineStr.split("\\|");	//当前文本文件中的字段分隔符是|，如果你是其他分隔符请自行替换
//给各字段赋值
								key = Integer.parseInt(arr[0]);
								partkey = Integer.parseInt(arr[1]);
								suppkey = Integer.parseInt(arr[2]);
								linenumber = Integer.parseInt(arr[3]);
								quantity = Double.parseDouble(arr[4]);
								exeprice = Double.parseDouble(arr[5]);
								discount = Double.parseDouble(arr[6]);
								tax = Double.parseDouble(arr[7]);
								retflag = arr[8];
								linestatus = arr[9];
								shipdate = arr[10];
								commitdate = arr[11];
								recepitdate = arr[12];
								shipstruct = arr[13];
								shipmode = arr[14];
								commit = arr[15];
								// System.out.println(i);
								if (i != 0)
									sql3 += ",(" + key + "," + partkey + "," + suppkey + "," + linenumber + ","
											+ quantity + "," + exeprice + "," + discount + "," + tax + ",'" + retflag
											+ "','" + linestatus + "','" + shipdate + "','" + commitdate + "','"
											+ recepitdate + "','" + shipstruct + "','" + shipmode + "','" + commit
											+ "')\n";
								else
									sql3 += "(" + key + "," + partkey + "," + suppkey + "," + linenumber + ","
											+ quantity + "," + exeprice + "," + discount + "," + tax + ",'" + retflag
											+ "','" + linestatus + "','" + shipdate + "','" + commitdate + "','"
											+ recepitdate + "','" + shipstruct + "','" + shipmode + "','" + commit
											+ "')\n";

							} // end for i 内循环
							sql = sql1 + sql2 + sql3;// 生成SQL完成
//						System.out.println(sql);
//						
//						break;

							// 开始插入
							try {
								statement = con.prepareStatement(sql);
							} catch (SQLException e) {
								e.printStackTrace();
							}
							try {
								res = statement.executeUpdate();
							} catch (SQLException e) {
								e.printStackTrace();
							}
							System.out.println("2 rows inserted");
						} // end for j外循环
					} finally {
						System.out.println("insert over");
					}

					bufferReader.close();
					inputStreamReader.close();
					System.out.println("over");
				} catch (IOException e) {
					System.out.println("file read error!");
					e.printStackTrace();
				}
				System.out.println("file catch unsupported encoding!");
			} else

			{
				System.out.println("file is not a file or file is not existing!");
				return;
			}//end if
		//}//end while
	}	//end main
}//end class

此外程序还要有一个jar包 sqljdbc4-2.0.jar
其实最后面应该关闭数据库连接的，当初写的时候疏漏了。