Excel向数据库插入数据（执行一次只需连接一次）-batch简单使用

由于前端时间向数据库插入excel中的数据时，每插入一条数据，就得连接一次数据库；后来发现这种做法不好，如果excel中有很多条数据，就得连接很多次数据库，这样就很浪费资源而且不安全，有时数据库也会报连接次数过多的异常，所以这次换了中处理方法，batch的简单用法。

1.上图就是本次所要上传数据库的数据的excel表，所需jar包：jxl.jar，ojdbc6.jar，使用mysql就mysql-connector-java-5.1.22-bin.jar；

2.创建实体类；

package com.excel.model;

import java.util.Date;

public class PHSample {
private String  id;                  
private String  sample_num;          
private String  customer_name;       
private String  customer_gender;     
private String    test_time;           
private String  height;              
private String  weight;              
private String  bmi;                 
private String  bmi_value;           
private String  pulmonary;           
private String  pulmonary_value;     
private String  steps;               
private String  steps_value;         
private String  grip;                
private String  grip_value;          
private String  flexion;             
private String  flexion_value;       
private String  reaction;            
private String  reaction_value;      
private String  singlestand;         
private String  singlestand_value;   
private String  verticaljump;        
private String  verticaljump_value;  
private String  pushup;              
private String  pushup_value;        
private String  abdominalcurl;       
private String  abdominalcurl_value;
private String  totalscore;          
private String  general;

/*
 * 部门
 */
private String department;
public String getDepartment() {
    return department;
}
public void setDepartment(String department) {
    this.department = department;
}
public PHSample(String customer_name, String customer_gender, String test_time,
        String height, String weight, String bmi, String bmi_value,
        String pulmonary, String pulmonary_value, String steps,
        String steps_value, String grip, String grip_value, String flexion,
        String flexion_value, String reaction, String reaction_value,
        String singlestand, String singlestand_value, String verticaljump,
        String verticaljump_value, String pushup, String pushup_value,
        String abdominalcurl, String abdominalcurl_value, String totalscore,
        String general,String department) {
    this.customer_name = customer_name;
    this.customer_gender = customer_gender;
    this.test_time = test_time;
    this.height = height;
    this.weight = weight;
    this.bmi = bmi;
    this.bmi_value = bmi_value;
    this.pulmonary = pulmonary;
    this.pulmonary_value = pulmonary_value;
    this.steps = steps;
    this.steps_value = steps_value;
    this.grip = grip;
    this.grip_value = grip_value;
    this.flexion = flexion;
    this.flexion_value = flexion_value;
    this.reaction = reaction;
    this.reaction_value = reaction_value;
    this.singlestand = singlestand;
    this.singlestand_value = singlestand_value;
    this.verticaljump = verticaljump;
    this.verticaljump_value = verticaljump_value;
    this.pushup = pushup;
    this.pushup_value = pushup_value;
    this.abdominalcurl = abdominalcurl;
    this.abdominalcurl_value = abdominalcurl_value;
    this.totalscore = totalscore;
    this.general = general;
    this.department = department;
}
public String getId() {
    return id;
}
public void setId(String id) {
    this.id = id;
}
public String getSample_num() {
    return sample_num;
}
public void setSample_num(String sample_num) {
    this.sample_num = sample_num;
}
public String getCustomer_name() {
    return customer_name;
}
public void setCustomer_name(String customer_name) {
    this.customer_name = customer_name;
}
public String getCustomer_gender() {
    return customer_gender;
}
public void setCustomer_gender(String customer_gender) {
    this.customer_gender = customer_gender;
}
public String getTest_time() {
    return test_time;
}
public void setTest_time(String test_time) {
    this.test_time = test_time;
}
public String getHeight() {
    return height;
}
public void setHeight(String height) {
    this.height = height;
}
public String getWeight() {
    return weight;
}
public void setWeight(String weight) {
    this.weight = weight;
}
public String getBmi() {
    return bmi;
}
public void setBmi(String bmi) {
    this.bmi = bmi;
}
public String getBmi_value() {
    return bmi_value;
}
public void setBmi_value(String bmi_value) {
    this.bmi_value = bmi_value;
}
public String getPulmonary() {
    return pulmonary;
}
public void setPulmonary(String pulmonary) {
    this.pulmonary = pulmonary;
}
public String getPulmonary_value() {
    return pulmonary_value;
}
public void setPulmonary_value(String pulmonary_value) {
    this.pulmonary_value = pulmonary_value;
}
public String getSteps() {
    return steps;
}
public void setSteps(String steps) {
    this.steps = steps;
}
public String getSteps_value() {
    return steps_value;
}
public void setSteps_value(String steps_value) {
    this.steps_value = steps_value;
}
public String getGrip() {
    return grip;
}
public void setGrip(String grip) {
    this.grip = grip;
}
public String getGrip_value() {
    return grip_value;
}
public void setGrip_value(String grip_value) {
    this.grip_value = grip_value;
}
public String getFlexion() {
    return flexion;
}
public void setFlexion(String flexion) {
    this.flexion = flexion;
}
public String getFlexion_value() {
    return flexion_value;
}
public void setFlexion_value(String flexion_value) {
    this.flexion_value = flexion_value;
}
public String getReaction() {
    return reaction;
}
public void setReaction(String reaction) {
    this.reaction = reaction;
}
public String getReaction_value() {
    return reaction_value;
}
public void setReaction_value(String reaction_value) {
    this.reaction_value = reaction_value;
}
public String getSinglestand() {
    return singlestand;
}
public void setSinglestand(String singlestand) {
    this.singlestand = singlestand;
}
public String getSinglestand_value() {
    return singlestand_value;
}
public void setSinglestand_value(String singlestand_value) {
    this.singlestand_value = singlestand_value;
}
public String getVerticaljump() {
    return verticaljump;
}
public void setVerticaljump(String verticaljump) {
    this.verticaljump = verticaljump;
}
public String getVerticaljump_value() {
    return verticaljump_value;
}
public void setVerticaljump_value(String verticaljump_value) {
    this.verticaljump_value = verticaljump_value;
}
public String getPushup() {
    return pushup;
}
public void setPushup(String pushup) {
    this.pushup = pushup;
}
public String getPushup_value() {
    return pushup_value;
}
public void setPushup_value(String pushup_value) {
    this.pushup_value = pushup_value;
}
public String getAbdominalcurl() {
    return abdominalcurl;
}
public void setAbdominalcurl(String abdominalcurl) {
    this.abdominalcurl = abdominalcurl;
}
public String getAbdominalcurl_value() {
    return abdominalcurl_value;
}
public void setAbdominalcurl_value(String abdominalcurl_value) {
    this.abdominalcurl_value = abdominalcurl_value;
}
public String getTotalscore() {
    return totalscore;
}
public void setTotalscore(String totalscore) {
    this.totalscore = totalscore;
}
public String getGeneral() {
    return general;
}
public void setGeneral(String general) {
    this.general = general;
}

}
3，创建dao

package com.excel.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBhelperOracle {
 Connection con=null;
 ResultSet res=null;
// PreparedStatement pre=null;
 PreparedStatement pre=null;
 
 //连接数据库
 public void DBbase(){
     try {
        String driver="oracle.jdbc.driver.OracleDriver";
//        String url="jdbc:oracle:thin:@192.168.224.87:1523:orcl";
//        String userName="gb";
//        String passWord="gb123$";
        
        String url="jdbc:oracle:thin:@192.168.31.35:1521:orcl";
        String userName="gb_hospital";
        String passWord="gb_hospital123$";
            
        Class.forName(driver);
        con=DriverManager.getConnection(url,userName,passWord);
    } catch (Exception e) {
        e.printStackTrace();
    }
 }
 
 //查询
 public ResultSet Search(String sql,String args[]){
     DBbase();
     try {
        pre=con.prepareStatement(sql);
        if(args!=null){
            for(int i=0;i<args.length;i++){
                pre.setString(i+1, args[i]);
            }
            
        }
        res=pre.executeQuery();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return res;
 }
 
 //批量增删改（连接一次数据库）
 public int Adu(String sql,String args[]){
     int falg=0;
     DBbase();
     try {
        pre=con.prepareStatement(sql);
        if(args!=null){
            for(int i=0;i<args.length;i++){
                pre.setString(i+1, args[i]);
            }
            pre.addBatch();//批量添加
        }
        pre.executeBatch();//批量执行
        falg=1;
    } catch (Exception e) {
        e.printStackTrace();
    }
    return falg;
 }
 
}
4.创建service

package com.excel.service;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;

import com.excel.model.PHSample;

public class PHSampleService {
   /**
     * 查询指定目录中电子表格中所有的数据
     * @param file 文件完整路径
     * @return
     */
    public static List<PHSample> getAllByExcel(String file){
        
        List<PHSample> PHSample=new ArrayList<PHSample>();
        try {
            Workbook wb=Workbook.getWorkbook(new File(file));
            Sheet sheet=wb.getSheet("Sheet1");
            int cols=sheet.getColumns();//得到总的列数
            int rows=sheet.getRows();//得到总的行数
            
            System.out.println("列数:"+cols+" 行数:"+rows);
            for(int i=1;i<rows;i++){
                for (int j = 1; j < cols; j++) {
                    //第一个是列数，第二个是行数
                    String testTime=sheet.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
                    String department=sheet.getCell(j++, i).getContents();
                    String name=sheet.getCell(j++,i).getContents();
                    String sex=sheet.getCell(j++,i).getContents();
                    j++;
                    String height=sheet.getCell(j++,i).getContents();
                    String weight=sheet.getCell(j++,i).getContents();
                    String bmi=sheet.getCell(j,i).getContents();
                    String bmi_value=sheet.getCell(j++,i+1).getContents();
                    String pulmonary=sheet.getCell(j,i).getContents();
                    String pulmonary_value=sheet.getCell(j++,i+1).getContents();
                    String steps=sheet.getCell(j,i).getContents();
                    String steps_value=sheet.getCell(j++,i+1).getContents();
                    String grip=sheet.getCell(j,i).getContents();
                    String grip_value=sheet.getCell(j++,i+1).getContents();
                    String flexion=sheet.getCell(j,i).getContents();
                    String flexion_value=sheet.getCell(j++,i+1).getContents();
                    String reaction=sheet.getCell(j,i).getContents();
                    String reaction_value=sheet.getCell(j++,i+1).getContents();
                    String singlestand=sheet.getCell(j,i).getContents();
                    String singlestand_value=sheet.getCell(j++,i+1).getContents();
                    String verticaljump=sheet.getCell(j,i).getContents();
                    String verticaljump_value=sheet.getCell(j++,i+1).getContents();
                    String pushup=sheet.getCell(j,i).getContents();
                    String pushup_value=sheet.getCell(j++,i+1).getContents();
                    String abdominalcurl=sheet.getCell(j,i).getContents();
                    String abdominalcurl_value=sheet.getCell(j++,i+1).getContents();
                    String totalscore=sheet.getCell(j++,i).getContents();
                    String general=sheet.getCell(j++,i).getContents();
                    
//                    System.out.println("testTime:"+testTime+" name:"+name+" sex:"+sex+" height:"+height
//                            +" weight:"+weight+" bmi:"+bmi+" bmi_value:"+bmi_value);
                    PHSample.add(new PHSample(
                             name,  sex, testTime,
                             height,  weight,  bmi,  bmi_value,
                             pulmonary,  pulmonary_value,  steps,
                             steps_value,  grip,  grip_value,  flexion,
                             flexion_value,  reaction,  reaction_value,
                             singlestand,  singlestand_value,  verticaljump,
                             verticaljump_value,  pushup,  pushup_value,
                             abdominalcurl,  abdominalcurl_value,  totalscore,
                             general,department
                            ));
                }
                i++;//一次循环读两行，目的是跳过下一行
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return PHSample;
    }
    
}
5.创建control

package com.excel.control;

import java.util.List;

import com.excel.dao.DBhelperOracle;
import com.excel.model.PHSample;
import com.excel.service.PHSampleService;

public class PHSampleControl {
public static void main(String[] args) {
    List<PHSample> phSample=PHSampleService.getAllByExcel("C://Users//lidelin//Desktop//华大基因体测汇总16.1.27临时.xls");//查询数据库中所有的数据
    DBhelperOracle dB=new DBhelperOracle();
    
    for (PHSample ph:phSample) {
        String sql="insert into T_PH_SAMPLE2016_Summary(customer_name,  customer_gender, test_time,"+
                             "height,  weight,  bmi,  bmi_value,"+
                             "pulmonary,  pulmonary_value,  steps,"+
                             "steps_value,  grip,  grip_value,  flexion,"+
                             "flexion_value,  reaction,  reaction_value,"+
                             "singlestand,  singlestand_value,  verticaljump,"+
                             "verticaljump_value,  pushup,  pushup_value,"+
                             "abdominalcurl,  abdominalcurl_value,  totalscore,"+
                             "general,department) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String[] str={
                ph.getCustomer_name(),
                ph.getCustomer_gender(),
                ph.getTest_time(),
                ph.getHeight(),
                ph.getWeight(),
                ph.getBmi(),
                ph.getBmi_value(),
                ph.getPulmonary(),
                ph.getPulmonary_value(),
                ph.getSteps(),
                ph.getSteps_value(),
                ph.getGrip(),
                ph.getGrip_value(),
                ph.getFlexion(),
                ph.getFlexion_value(),
                ph.getReaction(),
                ph.getReaction_value(),
                ph.getSinglestand(),
                ph.getSinglestand_value(),
                ph.getVerticaljump(),
                ph.getVerticaljump_value(),
                ph.getPushup(),
                ph.getPushup_value(),
                ph.getAbdominalcurl(),
                ph.getAbdominalcurl_value(),
                ph.getTotalscore(),
                ph.getGeneral(),
                ph.getDepartment()
                      };
        dB.Adu(sql, str);
    }
}
}

上述经笔者验证可以执行！

因为笔者技术有限，难免有漏洞，还望朋友们多提宝贵意见！