将excel中的内容导入mysql中

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();
        }
    }

}