Jdbc_读取excel内容_批量操作 事务_处理大数据量插入数据库

1,包装一个实体类

2,读取excel内容到实体类，用List打包

package excelread;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
import org.apache.poi.ss.usermodel.Cell; 
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row; 
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook; 
import org.apache.poi.xssf.usermodel.XSSFWorkbook; 

public class ExcelRead {
    public static void main(String[] args) {
	Workbook wb=null;
	List<Student> list=new ArrayList<Student>();
	Sheet sheet=null; //Sheet--对应Excel文档中的一个sheet；
	Row row=null; //Row----对应一个sheet中的一行；
	String cellData=null;
	String filePath="D:\\test.xlsx";
	wb=readExcel(filePath);
	if(wb!=null){
	    sheet=wb.getSheetAt(0);
	    //获取最大行数
	    int rownum=sheet.getPhysicalNumberOfRows();
	    //获取第一行
	    row=sheet.getRow(0);
	    //获取最大列数
	    int colnum=row.getPhysicalNumberOfCells();  //23列
	    for(int i=2;i<rownum;i++){ //从第三行开始
	        row=sheet.getRow(i); 
		if(row!=null){
		    Student student=new Student(); //包装的实体类
		    for(int j=0;j<colnum;j++){ 
		    cellData=(String) getCellFormatValue(row.getCell(j)); 
			switch(j){
			    case 0:student.setId(cellData); break;
			    case 1:student.setStudentId(cellData); break;
			    case 2:student.setName(cellData); break;
			    case 3:student.setSex(cellData); break;
			}
		    }
	        list.add(student);
	    }else{
	        break;
	    }
        }
        Jdbc.add(list);
        }
    }
	
    /**
     * 读excel
     * @param filePath 文件路径
     * @return
     */
    public static Workbook readExcel(String filePath){
	Workbook wb=null;  //Workbook--对应Excel文档
	if(filePath==null){
	    return null;
	}
	String extString = filePath.substring(filePath.lastIndexOf(".")); //.xlsx
	InputStream is=null;
	try {
	    is=new FileInputStream(filePath);
	    if(".xls".equals(extString)){
		return wb=new HSSFWorkbook(is); 
	    }else if(".xlsx".equals(extString)){
		return wb=new XSSFWorkbook(is); 
	    }else{
		return wb=null;
	    }
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}finally{
	    try {
		is.close();
	    } catch (IOException e) {
		e.printStackTrace();
	    }
	}
	return wb;
    }
    /*
     * 
     */
    public static  Object getCellFormatValue(Cell cell){  //单元格
	Object cellValue=null;
	if(cell!=null){
	//单元格
	switch(cell.getCellType())
	{
	    case Cell.CELL_TYPE_NUMERIC:{  
	        cell.setCellType(HSSFCell.CELL_TYPE_STRING); //防止科学计数法
	        cellValue=cell.getStringCellValue();
		break;
	    }
	    case Cell.CELL_TYPE_FORMULA:{
		//日期格式
		if(DateUtil.isCellDateFormatted(cell)){
		//转换为YYYY-mm-dd
		cellValue=cell.getDateCellValue();
	    }else{
	        cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
		cellValue=cell.getStringCellValue();
	    }
		break;
	    }
	    case Cell.CELL_TYPE_STRING:{
		cellValue=cell.getRichStringCellValue().getString();
		break;
	    }
	    default:
		cellValue= "NA";
	    }
	}else{
    	    cellValue= "NA";
	}
	return cellValue;
    }
	
}

3，使用Jdbc批量操和作事务处理大数据量插入数据库(注意数据库url加后缀:?rewriteBatchedStatements=true)

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

public class Jdbc {
    private  static String driver=null;
    private  static String url=null;
    private  static String user=null;
    private  static String password=null;
	
    static Connection conn; 
    static PreparedStatement ps; 
    static ResultSet rs; 
    
    /**
     * 配置文件加载
     */
    static {
	Properties p=new Properties();
	try {
	    p.load(new FileInputStream("jdbc.properties"));
	    driver=p.getProperty("driver");
	    url=p.getProperty("url");
	    user=p.getProperty("user");
	    password=p.getProperty("password");
	} catch (IOException e) {
	    e.printStackTrace();
	}
    }
	
    /**
     * 驱动加载
     */
    public static Connection getConnection(){
	try {
    	    Class.forName(driver);
	    conn=DriverManager.getConnection(url, user, password);
	} catch (ClassNotFoundException e) {
    	    e.printStackTrace();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
	return conn;
    }

   /**
    * 使用连接
    */
    public static void add(List<Student> list){
	String sql="insert into studentinfo(id,student_id,name,sex) values(?,?,?,?)";                       
        conn=Jdbc.getConnection();
	try {
    	    conn.setAutoCommit(false); //设置事务为非自动提交
	    long start=System.currentTimeMillis(); 
	    ps=conn.prepareStatement(sql);
	    for (Student student : list) {
		ps.setString(1, student.getId());
		ps.setString(2, student.getStudentId());
		ps.setString(3, student.getName());
		ps.setString(4, student.getSex());
		ps.addBatch();  
	    }
		ps.executeBatch();  //批量操作
		conn.commit(); //提交事务
		long end=System.currentTimeMillis();
		System.out.println("耗时："+(end-start));
    	    } catch (SQLException e) {
		e.printStackTrace();
	    }finally{
		if(ps!=null){
		    try {
			ps.close();
		    } catch (SQLException e) {
			e.printStackTrace();
		    }
		}
		if(conn!=null){
		    try {
			conn.close();
		    } catch (SQLException e) {
			e.printStackTrace();
		    }
		}
	    }
	}

}