SQL Server大批量数据插入

数据库连接及相关操作

public class DataBase {/*** 驱动*/private static final String DRIVER = PropertiesUtil.getString("spring.datasource.driver-class-name");/*** 数据库地址*/private static final String URL = PropertiesUtil.getString("spring.datasource.url");/*** 数据库用户名*/private static final String NAME = PropertiesUtil.getString("spring.datasource.username");/*** 数据库密码*/private static final String PASSWORD = PropertiesUtil.getString("spring.datasource.password");/*** 数据库连接，定义为全局变量，方便调用*/private Connection con;//导入驱动，静态代码块的作用为只运行一次,异常无法向上抛出，只能及时处理static {try {Class.forName(DRIVER);} catch (ClassNotFoundException e) {//打印异常相关信息e.printStackTrace();}}/*** 无参构造方法，连接数据库*/public DataBase() {try {con = DriverManager.getConnection(URL, NAME, PASSWORD);} catch (SQLException throwable) {throwable.printStackTrace();}}/*** 数据查找，返回查找的内容，向上抛异常** @param sql* @param object* @return* @throws SQLException*/public ResultSet executeSQL(String sql, Object... object) throws SQLException {PreparedStatement ps = con.prepareStatement(sql);for (int i = 0; i < object.length; i++) {//ps传入参数的下标是从1开始ps.setObject(i + 1, object[i]);}//返回结果集return ps.executeQuery();}/*** 关闭数据库连接** @throws SQLException*/public void close() throws SQLException {con.close();}/*** 设置 AutoCommit 模式为 false** @throws SQLException*/public void setAutoCommit(boolean flag) throws SQLException {// 设置 AutoCommit 模式con.setAutoCommit(flag);}/*** 事务提交** @throws SQLException*/public void commit() throws SQLException {// 手动提交事务con.commit();}/*** 事务回滚** @throws SQLException*/public void rollback() throws SQLException {con.rollback();}
}

批量插入数据

public class BulkCopyUtil {/*** 批量插入数据** @param tableName 表名* @param list      数据集合* @throws SQLException*/public static <T> void insertBatch(String tableName, List<T> list) throws Exception {// 查询出空值用于构建 CachedRowSetImpl 对象以省去列映射的步骤DataBase dataBase = new DataBase();// 从源表中获取数据作为 ResultSetResultSet resultSet = dataBase.executeSQL("select * from " + tableName + " where 1=0");CachedRowSetImpl crs = new CachedRowSetImpl();crs.populate(resultSet);ResultSetMetaData metaData = resultSet.getMetaData();int columnCount = metaData.getColumnCount();String[] dbFieldNames = new String[columnCount];for (int i = 1; i <= columnCount; i++) {dbFieldNames[i-1] = metaData.getColumnName(i);}// 循环批量插入for (T t : list) {if (ObjectUtil.isNotEmpty(t)) {// 移动指针到“插入行”，插入行是一个虚拟行crs.moveToInsertRow();// 向 CachedRowSet 对象插入一条数据populate(crs, t, dbFieldNames);// 插入虚拟行crs.insertRow();// 移动指针到当前行crs.moveToCurrentRow();}}// 进行批量插入bulkCopyHelp(dataBase, crs, tableName, list.size());}/*** 批量插入数据** @param dataBase  数据库连接相关操作* @param crs       CachedRowSet* @param tableName 表名* @param size      拷贝列表大小*/public static void bulkCopyHelp(DataBase dataBase, CachedRowSetImpl crs, String tableName, int size) throwsSQLException {// 数据库地址String url = PropertiesUtil.getString("spring.datasource.url");// 数据库用户名String name = PropertiesUtil.getString("spring.datasource.username");// 数据库密码String password = PropertiesUtil.getString("spring.datasource.password");// 数据库连接字符串String urlStr = url + ";user=" + name + ";password=" + password;// 使用 KeepIdentity 选项和 BatchSize 设置大容量复制对象SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();copyOptions.setKeepIdentity(true);copyOptions.setBatchSize(size);// 开启数据库事务copyOptions.setUseInternalTransaction(true);// 设置超时时间copyOptions.setBulkCopyTimeout(60000);SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(urlStr);bulkCopy.setBulkCopyOptions(copyOptions);// 设置拷贝目标表名bulkCopy.setDestinationTableName(tableName);// 将 crs 写入目标try {// 设置 AutoCommit 模式为 falsedataBase.setAutoCommit(false);// 执行数据库操作bulkCopy.writeToServer(crs);// 手动提交事务dataBase.commit();} catch (SQLException e) {e.printStackTrace();// 回滚dataBase.rollback();}crs.close();bulkCopy.close();}/*** 这里主要是通过表的列名，通过反射，拿到待插入对象的属性值** @param crs* @param record* @param dbFieldNames* @param <T>* @throws Exception*/private static <T> void populate(CachedRowSetImpl crs, T record, String[] dbFieldNames) throws Exception {Class<?> clazz = record.getClass();for (String fieldName : dbFieldNames) {StringBuilder getMethodName = new StringBuilder("get");if (fieldName.contains("_")) {String[] singleWords = fieldName.split("_");for (String singleWord : singleWords) {getMethodName.append(upperFirstChar(singleWord));}} else {getMethodName.append(upperFirstChar(fieldName));}Method method = clazz.getMethod(getMethodName.toString(), null);Object value = method.invoke(record, null);updateCrs(crs, fieldName, value);}}/*** 根据数据值的类型，将值设置到rowset里--这里value是否为空，都要做crs.update操作，否则会出bug** @param crs* @param dbFieldName* @param value* @throws SQLException*/private static void updateCrs(CachedRowSetImpl crs, String dbFieldName, Object value) throws SQLException {if (value instanceof String) {crs.updateString(dbFieldName, (String) value);} else if (value instanceof Integer) {crs.updateInt(dbFieldName, (int) value);} else if (value instanceof Double) {crs.updateDouble(dbFieldName, (double) value);} else if (value instanceof Long) {crs.updateLong(dbFieldName, (long) value);} else if (value instanceof Float) {crs.updateFloat(dbFieldName, (float) value);} else if (value instanceof Timestamp) {crs.updateTimestamp(dbFieldName, (Timestamp) value);} else if (value instanceof java.util.Date) {crs.updateDate(dbFieldName, new java.sql.Date(((java.util.Date) value).getTime()));} else {crs.updateObject(dbFieldName, value);}}/*** 首字母大写* @param camelCaseStr* @return*/private static String upperFirstChar(String camelCaseStr) {return camelCaseStr.substring(0, 1).toUpperCase() + camelCaseStr.substring(1);}
}

备注（数据库连接取值）

# 配置数据源
spring:datasource:driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriverurl: jdbc:sqlserver://ip:port;DatabaseName=collectionusername: sapassword: 123456