用Java代码将excel中的数据导入到数据库中

实体类 package net.importPractice.entity; public class CustomerInfo { private int custId; private String custName; private String custCard; private String custMajor; private String custUnit; private String custPhone; private String custAddress; private String custLsjzs; private String custRegistNo; public int getCustId() {   return custId; } public void setCustId(int custId) {   this.custId = custId; } public String getCustName() {   return custName; } public void setCustName(String custName) {   this.custName = custName; } public String getCustCard() {   return custCard; } public void setCustCard(String custCard) {   this.custCard = custCard; } public String getCustMajor() {   return custMajor; } public void setCustMajor(String custMajor) {   this.custMajor = custMajor; } public String getCustUnit() {   return custUnit; } public void setCustUnit(String custUnit) {   this.custUnit = custUnit; } public String getCustPhone() {   return custPhone; } public void setCustPhone(String custPhone) {   this.custPhone = custPhone; } public String getCustAddress() {   return custAddress; } public void setCustAddress(String custAddress) {   this.custAddress = custAddress; } public String getCustLsjzs() {   return custLsjzs; } public void setCustLsjzs(String custLsjzs) {   this.custLsjzs = custLsjzs; } public String getCustRegistNo() {   return custRegistNo; } public void setCustRegistNo(String custRegistNo) {   this.custRegistNo = custRegistNo; }  } 连接数据库 package net.importPractice.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class BaseDao {    //com.microsoft.sqlserver.jdbc.SQLServerDriver"    private static final String driver="oracle.jdbc.driver.OracleDriver";    //jdbc:sqlserver://localhost:1433;databaseName=qrcodeBase;    private static final String url="jdbcracle:thin192.168.0.200:1521:ORCL";    private static final String uname="ys";    private static final String upass="12345678";    public Connection conn=null;    public PreparedStatement pre=null;    public ResultSet re=null;    /**     * 连接数据库     */    public Connection getConn()    {     try      {     Class.forName(driver);     }      catch (ClassNotFoundException e)      {     e.printStackTrace();     }     try      {     conn=DriverManager.getConnection(url,uname,upass);    }      catch (SQLException e)      {     e.printStackTrace();    }       return conn;    }    /**     * 增删改的共同方法     */    public int into(String sql,String []a)    {    //System.out.println(a[0]+a[1]+a[2]+a[3]);     int r=0;     conn=this.getConn();     try      {     pre=conn.prepareStatement(sql);     if(a!=null)     {      for(int i=0;i<a.length;i++)      {       pre.setString(i+1, a[i]);      }     }     r=pre.executeUpdate();     }      catch (SQLException e)      {     e.printStackTrace();     }          return r;    }    /**     * 查询的共同方法     */    public ResultSet select(String sql,String []a)    {     conn=this.getConn();     try      {     pre=conn.prepareStatement(sql);     if(a!=null)     {      for(int i=0;i<a.length;i++)      {       pre.setString(i+1, a[i]);      }     }     re=pre.executeQuery();     }      catch (SQLException e)      {     e.printStackTrace();     }     return re;    }    /**     * 关闭数据库     */    public void closeAll()    {    try     {     if(re!=null)     {      re.close();     }     if(pre!=null)     {      pre.close();     }     if(conn!=null && !conn.isClosed())     {      conn.close();     }    }     catch (SQLException e)     {     e.printStackTrace();    }    } } 将excel中的数据导入 package net.importPractice.entity; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.List; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; public class ReadExcel { public List<CustomerInfo> readExcelDemo(){ //实例一个实体类 CustomerInfo cust = null; //声明一个list，用于存储从Excel中取出的数据 List<CustomerInfo> custList = new ArrayList<CustomerInfo>(); try {   //取出Excel文件   File file = new File("E://jxjy03.xls");   Workbook book = Workbook.getWorkbook(file);   //获得第一个sheet   Sheet sheet = book.getSheet(0);   //一共有多少行多少列数据   int rows = sheet.getRows();   int columns = sheet.getColumns(); //  System.out.println(rows+"行"+columns+"列");   boolean hasText = false;   for(int i=1;i<rows;i++)   {    //过滤掉没有文本内容的行    for(int j=0;j<columns;j++)    {     if(sheet.getCell(j,i).getContents() != "")     {      hasText = true;      break;     }    }    if(hasText)    {     cust = new CustomerInfo();     cust.setCustId(i);     cust.setCustName(sheet.getCell(0,i).getContents());     cust.setCustCard(sheet.getCell(1,i).getContents());     cust.setCustMajor(sheet.getCell(2,i).getContents());     cust.setCustUnit(sheet.getCell(3,i).getContents());     cust.setCustPhone(sheet.getCell(4,i).getContents());     cust.setCustAddress(sheet.getCell(5,i).getContents());     cust.setCustLsjzs(sheet.getCell(6,i).getContents());     cust.setCustRegistNo(sheet.getCell(7,i).getContents());     cust.setCustClassNo("");     custList.add(cust);      }    } }catch(IOException e) {   e.printStackTrace(); }catch(BiffException e) {   e.printStackTrace(); }catch(Exception e) {   e.printStackTrace(); } return custList; } } 提供的操作数据库的方法 package net.importPractice.entity; import java.sql.SQLException; import net.importPractice.util.BaseDao; public class ImportLibary extends BaseDao{ /**   * 将从Excel中的数据插入到数据库中，相当于给数据库插入一条数据   * @param cust 实体类   * @throws SQLException 捕获的异常   */ public void batchInsert(CustomerInfo cust,int count) throws SQLException {   String sql = "INSERT INTO CUSTOMER(CUST_ID,CUST_NAME,CUST_CARD,CUST_MAJOR,CUST_UNIT,CUST_PHONE,"     + "CUST_ADDRESS,CUST_LSJZS,CUST_REGISTNO)"     + "VALUES(?,?,?,?,?,?,?,?,?)";   String [] a = new String[]{cust.getCustId()+"",cust.getCustName(),cust.getCustCard(),     cust.getCustMajor(),cust.getCustUnit(),cust.getCustPhone(),cust.getCustAddress(),     cust.getCustLsjzs(),cust.getCustRegistNo()};   this.into(sql, a); } /**   * 多条件查询   * 根据姓名，省份证号码，所属专业这三个条件，如果着三个条件都在数据库中存在，则将这条数据不插入数据库中   * @param cust 查询参数:实体（实体中可能是任何一个参数）   * @return 取出存储的个数，存储在r中   * @throws SQLException    */ public int findByType(CustomerInfo cust) throws SQLException {   int r = 0;   String sql = "SELECT COUNT(*) FROM CUSTOMER WHERE CUST_NAME='"+cust.getCustName()+"' AND CUST_CARD='"+cust.getCustCard()+"' AND CUST_MAJOR='"+cust.getCustMajor()+"'";   re = this.select(sql, null);   while(re.next())   {    r = re.getInt(1);   }   return r; } } 测试 package net.importPractice.test; import java.sql.SQLException; import java.util.List; import net.importPractice.entity.CustomerInfo; import net.importPractice.entity.ImportLibary; import net.importPractice.entity.ReadExcel; public class Test { public static void main(String[] args) throws SQLException {      ImportLibary il = new ImportLibary();   ReadExcel re = new ReadExcel();   List<CustomerInfo> list = re.readExcelDemo();   for(int i=0;i<list.size();i++)   {    /**     * 根据r的值来判断是否增加一条新数据     */    int r = il.findByType((CustomerInfo)list.get(i));    if(r != 1)    {     //先增加后更改，只更改一个班级编号的字段     il.batchInsert((CustomerInfo)list.get(i));     System.out.println(list.get(i).getCustName().toString()+"的数据成功存储在数据库中");    }    else    {     System.out.println(list.get(i).getCustName().toString()+"的数据已经存储在数据库中");     continue;    }   } } }