1、所需jar包
commons-collections4-4.4.jar commons-compress-1.18.jar mysql-connector-java-5.1.33-bin.jar poi-4.1.0.jar poi-examples-4.1.0.jar poi-excelant-4.1.0.jar poi-ooxml-4.1.0.jar poi-ooxml-schemas-4.1.0.jar poi-scratchpad-4.1.0.jar resolver.jar xmlbeans-3.1.0.jar
2、excel读取类
package com.njwd.leading; import java.io.FileInputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class LeadingMenu { public static void main(String[] args) { System.out.println("11"); try { // 创建工作簿 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("F:\\sql相关\\正式库初始化sql\\权限路由表更新后.xlsx")); // 读取第一个工作表(这里的下标与list一样的，从0开始取，之后的也是如此) XSSFSheet sheet = xssfWorkbook.getSheetAt(0); List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); int pid = 0; int twoPid = 0; // 获取第一行的数据 for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { XSSFRow row = sheet.getRow(i); boolean flag = true; Map<String, Object> map = new HashMap<String, Object>(); map.put("id", i); for (int j = 0; j < 4; j++) { if (row.getCell(0) == null) { flag=false; break; } map.put("name", row.getCell(0).toString()); if (row.getCell(1) != null) { pid = i; map.put("menu_code", row.getCell(1).toString()); map.put("parent_id", 0); } if (row.getCell(2) != null) { twoPid = i; map.put("menu_code", row.getCell(2).toString()); map.put("parent_id", pid); } if (row.getCell(3) != null) { map.put("menu_code", row.getCell(3).toString()); map.put("parent_id", twoPid); } } if(flag==false){ break; } list.add(map); System.out.println(map); } System.out.println("=====开始插入=========="); MenuDao menuDao =new MenuDao(); menuDao.deleteMenu(); menuDao.addMenu(list); System.out.println("======更新结束========="); // 获取该行第一个单元格的数据 } catch (Exception e) { e.printStackTrace(); } } }3、menuDao的数据库JDBC类
package com.njwd.leading; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.mysql.fabric.xmlrpc.base.Data; import com.njwd.util.DatabaseConnect; public class MenuDao extends DatabaseConnect{ public void addMenu(List<Map<String, Object>> list) throws Exception{ String sql = "insert into wd_sys_menu(id,menu_code,name,parent_id)" +"values (?,?,?,?)"; PreparedStatement ps = conn.prepareStatement(sql); for (Map<String, Object> map : list) { if( map.get("id")!=null && map.get("menu_code")!=null && map.get("name")!=null ){ ps.setInt(1, Integer.parseInt(map.get("id").toString())); ps.setString(2, map.get("menu_code").toString()); ps.setString(3, map.get("name").toString()); ps.setString(4, map.get("parent_id").toString()); ps.addBatch(); } } ps.executeBatch(); ps.clearBatch(); ps.close(); } public void deleteMenu() throws Exception{ String sql = "delete from wd_sys_menu"; PreparedStatement ps = conn.prepareStatement(sql); ps.executeUpdate(); ps.close(); } }4、DatabaseConnect工具类
package com.njwd.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class DatabaseConnect { protected Connection conn ; public DatabaseConnect(){ try { Properties properties =new Properties(); properties.load(DatabaseConnect.class.getClassLoader().getResourceAsStream("jdbc.properties")); String url = properties.getProperty("url"); String user = properties.getProperty("user"); String password = properties.getProperty("password"); System.out.println(url); Class.forName("com.mysql.jdbc.Driver"); // 加载驱动 conn = DriverManager.getConnection(url,user,password); // 获取数据库连接 } catch (Exception e) { e.printStackTrace(); } } public void close(){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
