将excel数据批量插入到数据库表中

项目实例结构：

 

1.model实体类

 

package com.model;

public class Test {
	private int num1;
	private String num2;
	private String num3;
	private String num4;
	
	
	public int getNum1() {
		return num1;
	}
	public void setNum1(int num1) {
		this.num1 = num1;
	}
	public String getNum2() {
		return num2;
	}
	public void setNum2(String num2) {
		this.num2 = num2;
	}
	public String getNum3() {
		return num3;
	}
	public void setNum3(String num3) {
		this.num3 = num3;
	}
	public String getNum4() {
		return num4;
	}
	public void setNum4(String num4) {
		this.num4 = num4;
	}

}

2.dao及daoimpl

 

 

package com.dao;

import com.model.Test;

public interface TestDao {
	public void add(Test test)throws Exception;

}

 

 

 

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;

import com.model.Test;
import com.util.JdbcUtils;


public class TestDaoImpl implements TestDao{

	public void add(Test test) throws Exception {
		Connection conn=null;
		try {
			conn=JdbcUtils.getConnection();
			String sql="insert into test(num2,num3,num4) values (?,?,?)";
			PreparedStatement pst=conn.prepareStatement(sql);
			pst.setString(1,test.getNum2());
			pst.setString(2,test.getNum3());
			pst.setString(3,test.getNum4());
			pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("添加数据失败！");
		}finally{
			conn.close();
		}
		
	}

}

 

 

 

3.util工具类

 

package com.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;


public class JdbcUtils {
	private static String driverClass;

	private static String url;

	private static String user;

	private static String password;

	static {
		try {

			Properties props = new Properties();

			// 加载属性文件，注意属性文件的路径写法
			InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream(
			    "com/util/db.properties");

			props.load(is);

			driverClass = props.getProperty("driverClass");
			url = props.getProperty("url");
			user = props.getProperty("user");
			password = props.getProperty("password");

			Class.forName(driverClass);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
//获得Connection对象所消耗资源会占到整个jdbc操作的85%以上
//批处理除外
//尽量减少获得Connection对象	
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, password);
	}

	public static void main(String[] args) throws Exception {
//		Properties props = new Properties();
//		FileInputStream fis = new FileInputStream("d:/db.properties");
//		props.load(fis);
//		System.out.println(props.getProperty("driverClass"));
//		System.out.println(props.getProperty("user"));
		System.out.println(getConnection());
	}

}

 

 

 

4.数据库配置文件db.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
user=root
password=root

 

5.测试类AddToData.java

 

package com.service;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.dao.TestDao;
import com.dao.TestDaoImpl;
import com.model.Test;
import com.sun.org.apache.commons.logging.Log;
import com.sun.org.apache.commons.logging.LogFactory;

public class AddToData {
	// 记录类的输出信息
	//static Log log = LogFactory.getLog(AddToData.class);
	
	// 获取Excel文档的路径
	public static String filePath = "E://upload2/excel.xls";
	
	public static void main(String[] args) throws Exception {
		try {
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(
					filePath));
			// 在Excel文档中，第一张工作表的缺省索引是0
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFSheet sheet = wookbook.getSheet("Sheet1");
			// 获取到Excel文件中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();
			// 遍历行
			for (int i = 1; i < rows; i++) {
				// 读取左上端单元格
				HSSFRow row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					// 获取到Excel文件中的所有的列
					int cells = row.getPhysicalNumberOfCells();
					String value = "";
					// 遍历列
					for (int j = 0; j < cells; j++) {
						// 获取到列的值
						HSSFCell cell = row.getCell(j);
						if (cell != null) {
							switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_FORMULA:
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								value += cell.getNumericCellValue() + ",";
								break;
							case HSSFCell.CELL_TYPE_STRING:
								value += cell.getStringCellValue() + ",";
								break;
							default:
								value += "0";
								break;
							}
						}
					}
					
					// 将数据插入到mysql数据库中
					String[] val = value.split(",");
					Test entity = new Test();
					entity.setNum2(val[0]);
					entity.setNum3(val[1]);
					entity.setNum4(val[2]);
					TestDao method = new TestDaoImpl();
					method.add(entity);
				}
			}
			System.out.println("插入成功！");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}

}

 

 

 

5.excel.xls的数据结构

 

 

 

通过执行AddToData.java即可将excel数据插入到数据库表中。