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