1)准备工作-jar包
a、mysql-connector-java-5.1.20-bin.jar
b、ojdbc14.jar
c、poi-3.10.1-20140818.jar
d、poi-excelant-3.10.1-20140818.jar
e、poi-ooxml-3.10.1-20140818.jar
f、poi-ooxml-schemas-3.10.1-20140818.jar
g、poi-scratchpad-3.10.1-20140818.jar
2)实施-创建普通Java工程,编写代码
// 输入参数判断
// 1)hostFileUrl设备文件路径(从设备管理列表导出的excel文件)
// 2)DB_URL
// 3)Username
// 4)Password
if (args == null || args.length < 4) {
System.out.println("传递的参数不足4个【hostFilePath,Db_url,Username,Password】,(含空格用双引号引起)参数之间用空格分开,路径不能含中文。");
return;
}
// 获取JDBC连接
Connection connection = getConnection(args);
Statement statement = null;
// 解析数据
HSSFWorkbook hwb = null;
try {
File file = new File(args[0]);
hwb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet e = hwb.getSheet("sheet名");
statement = connection.createStatement();
for (int i = 1; i <= e.getLastRowNum(); ++i) {
HSSFRow row = e.getRow(i);
Map<Integer, String> map = new HashMap<Integer, String>();
for (int j = 0; j < row.getLastCellNum(); ++j) {
HSSFCell cell = row.getCell(j);
cell.setCellType(1);
map.put(Integer.valueOf(j), cell.getStringCellValue());
}
// 查询主机是否存在
if(StringUtils.isNullOrEmpty(map.get(0))){
continue;
}
ResultSet resultSet = statement.executeQuery("select * from `hosts` where `name`='" + map.get(1) + "'");
if(!resultSet.next()){
continue;
}
// 插入数据库连接
StringBuffer sqlStr = new StringBuffer();
strBuffer.append("INSERT INTO table values(...)");
statement.addBatch(strBuffer.toString());
}
e = hwb.getSheet("sheet名");
for (int i = 1; i <= e.getLastRowNum(); ++i) {
HSSFRow row = e.getRow(i);
Map<Integer, String> map = new HashMap<Integer, String>();
for (int j = 0; j < row.getLastCellNum(); ++j) {
HSSFCell cell = row.getCell(j);
String value = "";
if(cell != null){
cell.setCellType(1);
value = cell.getStringCellValue();
}
map.put(Integer.valueOf(j), value);
}
// 查询主机是否存在
if(StringUtils.isNullOrEmpty(map.get(0))){
continue;
}
ResultSet resultSet = statement.executeQuery("select * from `hosts` where `name`='" + map.get(1) + "'");
if(!resultSet.next()){
continue;
}
// 插入数据库连接
StringBuffer strBuffer = new StringBuffer();
strBuffer.append("INSERT INTO table values(...)");
statement.addBatch(strBuffer.toString());
}
statement.executeBatch();
System.out.println("数据插入成功.");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
System.out.println("关闭连接.");
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static Connection getConnection(String[] args) {
// 获取参数
String url = args[1];
String user = args[2];
String password = args[3];
// 设置驱动名称
String driverName = "oracle.jdbc.driver.OracleDriver";
if (url.contains("jdbc:mysql")) {
driverName = "com.mysql.jdbc.Driver";
}
// 加载驱动,建立连接
Connection con = null;
try {
Class.forName(driverName);
con = DriverManager.getConnection(url, user, password);
System.out.println("db连接成功.");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
3)打成jar包,在命令行运行