将excel中的内容导入mysql中

本是古典何须时尚 2021-06-24 15:57 358阅读 0赞

package excel;
    
    import java.io.File;
    import java.io.IOException;
    import java.lang.reflect.Type;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.junit.Test;
    
    import bean.t_usersinfo;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.read.biff.BiffException;
    
    public class ExcelToDB { 
        public void doT2() {
            List li1 = new ArrayList();
            List li2 = new ArrayList();
            li1.add("E:/t_usersinfo.xls");
            li2.add(t_usersinfo.class);
            for (int i = 0; i < li1.size(); i++) {
                Controller((String) li1.get(i), (Class) li2.get(i));
            }
        }
    
        /** * 将excel中的内容读取到mysql中 * * @param excelname * excel的文件位置 * @param clazz * 对应的实体类的.class */
        public void Controller(String excelname, Class clazz) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/sshmysql?user=root&password=root&useUnicode=true&characterEncoding=UTF8");
                Statement stmt = conn.createStatement();
                // ##########################将excel中的文件读取进来###########################
                Workbook rwb = Workbook.getWorkbook(new File(excelname));
                Sheet rs = rwb.getSheet("Sheet1");// 或者rwb.getSheet(0)
                int clos = rs.getColumns();// 得到所有的列
                int rows = rs.getRows();// 得到所有的行
                // ##########################将excel中的文件读取进来###########################
                // 获取实体对象的名字，即insert语句中的表名
                String bean = clazz.toString().substring(clazz.toString().indexOf(".") + 1);
                for (int j = 1; j < rows; j++) {
                    List li = new ArrayList();
                    String str1 = "insert into " + bean + "(";
                    String str2 = "values(";
                    for (int k = 0; k < clos; k++) {
                        String cloumn = rs.getCell(k, 0).getContents();
                        String value = rs.getCell(k, j).getContents();
                        // excel中有些列不是insert表中的字段，值需要做更改
                        // 比如：excel中的列名为x，但是数据库中要插入的字段为xx
                        if (cloumn.equals("x")) {
                            // 例子如：doT()
                            String str = "select * from XXX表 where " + cloumn + "=" + value;
                            ResultSet res = stmt.executeQuery(str);
                            // jdbc通过字段名字获取相应的值,xx为你要查询并插入的字段名
                            String value2 = res.getString("xx");
                            // string,int,date等,自己写
                            String type = "字段的类型";
                            str1 = str1 + "xx" + ",";
                            str2 = getValue(str2, type, value2);
                        } else {
                            str1 = str1 + cloumn + ",";
                            // 获取实体属性的类型
                            Type typeT = clazz.getDeclaredField(cloumn).getGenericType();
                            String type = typeT.toString();
                            str2 = getValue(str2, type, value);
                        }
                    }
                    str1 = str1.substring(0, str1.length() - 1) + ")";
                    str2 = str2.substring(0, str2.length() - 1) + ")";
                    String sql = str1 + str2;
                    System.out.println(sql);
                    stmt.executeUpdate(sql);
                }
            } catch (BiffException | IOException | SQLException | ClassNotFoundException | NoSuchFieldException
                    | SecurityException e) {
                e.printStackTrace();
            }
        }
    
        /** * 类型转换，返回动态sql语句 * * @param str * 动态sql语句 * @param Type * 类型，比如int，string，date等 * @param value * 需要动态拼接的值 * @return 动态sql语句 */
        public String getValue(String str, String Type, String value) {
            if (Type.equals("int") || Type.equals("long")) {
                str = str + value + ",";
            } else if (Type.equals("class java.util.Date")) {
                str = str + "date_format('" + value + "', '%Y-%m-%d')" + ",";
            } else {
                str = str + "'" + value + "'" + ",";
            }
            return str;
        }
    
        /** * 例子 */
        @Test
        public void doT() {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/sshmysql?user=root&password=root&useUnicode=true&characterEncoding=UTF8");
                Statement stmt = conn.createStatement();
                String str = "SELECT * from t_usersinfo WHERE T_USERID=1";
                ResultSet res = stmt.executeQuery(str);
                while (res.next()) {
                    System.out.println(res.getString("T_USERADDRESS"));
                }
            } catch (ClassNotFoundException | SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    
    }