数据库连接及相关操作
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();}}public ResultSet executeSQL(String sql, Object... object) throws SQLException {PreparedStatement ps = con.prepareStatement(sql);for (int i = 0; i < object.length; i++) {ps.setObject(i + 1, object[i]);}return ps.executeQuery();}public void close() throws SQLException {con.close();}public void setAutoCommit(boolean flag) throws SQLException {con.setAutoCommit(flag);}public void commit() throws SQLException {con.commit();}public void rollback() throws SQLException {con.rollback();}
}
批量插入数据
public class BulkCopyUtil {public static <T> void insertBatch(String tableName, List<T> list) throws Exception {DataBase dataBase = new DataBase();ResultSet 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();populate(crs, t, dbFieldNames);crs.insertRow();crs.moveToCurrentRow();}}bulkCopyHelp(dataBase, crs, tableName, list.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;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);try {dataBase.setAutoCommit(false);bulkCopy.writeToServer(crs);dataBase.commit();} catch (SQLException e) {e.printStackTrace();dataBase.rollback();}crs.close();bulkCopy.close();}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);}}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);}}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