废话不多说,直接上代码结构图
所用到的lib包
Students 实体类
package com.test.model;
public class Students {
private int id;
private String username;
private int age;
private int salary;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
@Override
public String toString() {
return "Students [id=" + id + ", username=" + username + ", age=" + age
+ ", salary=" + salary + "]";
}
public Students(int id, String username, int age, int salary) {
// super();
this.id = id;
this.username = username;
this.age = age;
this.salary = salary;
}
public Students() {
// super();
}
}
Mybatis 配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!-- 配置数据库连接信息 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/testmysql"/>
<property name="username" value="root"/>
<property name="password" value="1234"/>
</dataSource>
</environment>
</environments>
<!-- sql语句的配置文件 -->
<mappers>
<mapper resource="com/test/reader/sqlconfig/students.xml"/>
</mappers>
</configuration>
Students.xml sql语句
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.readerExcel.TestReaderExcelToDB">
<select id="getStudent" resultType="map">
select * from students order by id
</select>
<select id="getStudentById" resultType="map" parameterType="map">
select * from students where id = #{userId}
</select>
<delete id="deleteStuentById" parameterType="map">
delete from students where id = #{userId}
</delete>
<insert id="insertStudent" parameterType="map">
insert into students
(username,age,salary)
values
(
#{username},
#{age},
#{salary}
)
</insert>
</mapper>
package com.test.util;
import java.io.InputStream;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class SqlSessionUtil {
public SqlSession getSqlSession() {
String configFile = "mybatis_Config.xml";
// 使用类加载器 加载mybatis的配置文件,mybatis配置文件中配置了关联映射文件
InputStream inputStream = SqlSessionUtil.class.getClassLoader().getResourceAsStream(configFile);
// 构建sqlSession工厂
SqlSessionFactory sqlsessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlsessionFactory.openSession();
return sqlSession;
}
}
读取Excel的工具类
这个读取Excel2007 的代码有点问题,不知道是方法问题,还是我代码问题,总是最后少一行,昨天找了半天没找到....真是吐血....
ReaderExcelUtils
package com.excelutils;
import java.io.File;
import java.io.FileInputStream;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* 读取Excel
* @author CYX
* @since 2016-6-4 上午1:03:23
*/
public class ReaderExcelUtils {
/**
* 输入Excel文件,解析后返回ArrayList
*
* @param file
* 输入的Excel文件
*
* @return ArrayList<Map>,其中的map以第一行的内容为键值
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public static ArrayList<Map> ReaderExcel(File file){
/*
* workbook : 工作簿,就是整个Excel文档
* sheet : 工作表
* cell : 一个单元格
* row : 一行
*/
if(checkExcel2007(file)){
return importToExcel2007(file);
}
//初始化返回值和字段名数组
ArrayList<Map> arr = new ArrayList<Map>();
String[] title;
Workbook workbook = null;
try{
//读取Excel文件
workbook = Workbook.getWorkbook(file);
//总Sheet数
int sheetNumber = workbook.getNumberOfSheets();
System.out.println("Sheet总数: "+sheetNumber);
for (int i = 0; i < sheetNumber; i++) {
Sheet sheet = workbook.getSheet(i);
//当前页 总记录行数和列数
int rowCount = sheet.getRows(); //获取行数
int columeCount = sheet.getColumns(); //获取列数
System.out.println("总记录数 : "+rowCount);
System.out.println("总列数 : "+columeCount);
//第一行为字段名,所以行数大于一才执行
if(rowCount > 1 && columeCount >0){
//取第一列 为 字段名
title = new String[columeCount];
for (int j = 0; j < columeCount; j++) {
title[j] = sheet.getCell(j,0).getContents().trim();
}
//取当前页所有值放入list中
for (int h = 1; h < rowCount; h++) { //行数
LinkedHashMap dataMap = new LinkedHashMap();
for (int k = 0; k < columeCount; k++) { //列数
dataMap.put(title[k], sheet.getCell(k,h).getContents()); //getContents() 获取单元格的值
}
arr.add(dataMap);
}
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(workbook != null){
workbook.close();
workbook = null;
}
}
return arr;
}
/**
* 输入2007版以上excel文件,解析后返回ArrayList(有个bug,暂时不用,保留)
* @param file
* @return
*/
@SuppressWarnings("rawtypes")
public static ArrayList<Map> importToExcel2007(File file){
ArrayList<Map> arr = new ArrayList<Map>();
String[] title;
//初始化
FileInputStream readFile = null;
XSSFWorkbook workbook = null;
XSSFRow row = null;
XSSFSheet sheet = null;
XSSFCell cell = null;
try{
//读取文件
readFile = new FileInputStream(file);
workbook = new XSSFWorkbook(readFile);
//文档页数
int numOfSheets = workbook.getNumberOfSheets();
System.out.println("文档页数 : "+numOfSheets);
for (int i = 0; i < numOfSheets; i++) {
//获取当前的sheet(工作表)
sheet = workbook.getSheetAt(i);
//获取当前页的行数
int sheetRows = sheet.getLastRowNum();
System.out.println("当前页总行数 : "+sheetRows);
//如果当前页行数大于0,则先取第一行为字段名
if(sheetRows > 0){
row = sheet.getRow(0); //当前页 第一行
int cells = row.getLastCellNum(); //第一行 单元格数量
title = new String[cells];
for (int j = 0; j < cells; j++) {
//列为空,则输入空字符串
if(row.getCell(j) == null){
title[j] = "";
continue;
}
cell = row.getCell(j);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:{
Integer num = new Integer((int) cell.getNumericCellValue());
title[j] = String.valueOf(num);
break;
}
case Cell.CELL_TYPE_STRING:{
title[j] = cell.getRichStringCellValue().toString();
break;
}
default:
title[j] = "";
}
}
//分行解析
for (int j = 0; j < sheetRows; j++) {
//如果是空行,则继续下一条
if(sheet.getRow(j) == null){
continue;
}
//将每行数据放入map中
row = sheet.getRow(j);
arr.add(getCellMap(row,cells,title));
}
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
try {
readFile.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return arr;
}
/**
* 根据文件扩展名判断是否是Excel 2007 以上
* @param file
* @return
*/
private static boolean checkExcel2007(File file){
String extendName = file.getName().substring(file.getName().lastIndexOf("."));
if(extendName.equals(".xlsx")){
return true;
}
return false;
}
/**
* 根据传入的Excel行数据,得到Map数据
* @param row
* @param cells
* @param title
* @return
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
private static Map getCellMap(XSSFRow row , int cells , String[] title){
//初始化
Map data = new HashMap();
XSSFCell cell = null;
//分列
for (int i = 0; i < cells; i++) {
//列为空,则输入空字符串
if(row.getCell(i) == null){
data.put(title[i], "");
continue;
}
cell = row.getCell(i);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:{
if(DateUtil.isCellDateFormatted(cell)){
data.put(title[i], cell.getDateCellValue());
}else{
NumberFormat nf = NumberFormat.getInstance();
nf.setGroupingUsed(false);
data.put(title[i], nf.format(cell.getNumericCellValue()));
}
break;
}
case Cell.CELL_TYPE_STRING:{
data.put(title[i], cell.getRichStringCellValue());
break;
}
case Cell.CELL_TYPE_BOOLEAN:{
data.put(title[i], cell.getBooleanCellValue());
break;
}
default:
data.put(title[i], "");
}
}
return data;
}
}
Java Bean的一些方法 很好用....多看看项目代码...
package com.beanutils;
import java.util.Map;
/**
* JavaBean 工具方法
* @author CYX
* @since 2016-6-4 上午12:16:23
*/
public class MyBeanUtils {
/**
* 将JavaBean中的get属性转换到Map中
*
* <pre>
* 符合拷贝条件的属性必须满足以下几点:
* 1.源JavaBean中具有get方法的属性
* </pre>
*
* @param bean
* 源对象JavaBean
* @return 转换后的Map
* @throws Exception
*/
public static Map describe(Object bean)throws Exception{
return org.apache.commons.beanutils.BeanUtils.describe(bean);
}
}
测试Main方法
package com.test.readerExcel;
import java.io.File;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.ibatis.session.SqlSession;
import com.beanutils.MyBeanUtils;
import com.excelutils.ReaderExcelUtils;
import com.test.model.Students;
import com.test.util.SqlSessionUtil;
public class TestReaderExcelToDB {
private static ReaderExcelUtils reu = new ReaderExcelUtils();
private static SqlSessionUtil su = new SqlSessionUtil();
private static SqlSession session = su.getSqlSession();
private static String className = TestReaderExcelToDB.class.getName() + ".";
public static void main(String[] args) throws Exception {
InsertToDataBase();
}
/**
* 读取Excel文件,将数据存入数据库
*
* @param data
* 数据
* @throws Exception
*/
@SuppressWarnings({ "rawtypes", "static-access" })
public static void InsertToDataBase() throws Exception {
Date date = new Date();
long time = date.getTime();
String excelFileName = "D://Export Excel By MyBatis.xls";
File file = new File(excelFileName);
List<Map> dataListMap = reu.ReaderExcel(file);
Iterator it = dataListMap.iterator();
Students students = new Students();
MyBeanUtils mu = new MyBeanUtils();
try {
while (it.hasNext()) {
Map oneMap = (Map) it.next();
students.setUsername(oneMap.get("姓名").toString());
students.setAge(Integer.parseInt(oneMap.get("年龄").toString()));
students.setSalary(Integer.parseInt(oneMap.get("工资").toString()));
Map beanMap = mu.describe(students);
int result = 0;
try {
result = session.insert(className + "insertStudent",beanMap);
if(result < 1){
System.out.println("插入数据库错误");
}
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}
}
System.out.println("全部插入数据库");
Date date2 = new Date();
long time2 = date2.getTime();
long longs = time2 - time;
System.out.println(longs);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取Map中的key值
*
* @param dataMap
* @return
*/
public static String[] getMapKey(Map dataMap) {
String[] keyArr = new String[dataMap.size()];
if (dataMap.size() < 0 || dataMap == null) {
System.out.println("Map为空");
return null;
}
String keyStr = "";
Set set = dataMap.keySet();
for (Object name : set) {
keyStr += name + ",";
}
keyStr = keyStr.substring(0, keyStr.length() - 1);
keyArr = keyStr.split(",");
return keyArr;
}
}
Excel文件内容
执行之后,数据库就有了....
之前在公司电脑上测试,一千五百条数据的Excel 花了75秒左右...
晚上回家用自己电脑,才2,3秒....公司电脑真是渣....
手贱试了试五千条..才五秒....
哎,真是.....