由于前端时间向数据库插入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);
}
}
}
上述经笔者验证可以执行！
因为笔者技术有限，难免有漏洞，还望朋友们多提宝贵意见！