最近一个项目需要做一个excel导入数据库,但是数据量太大,整理完之后有87个字段,如果一个一个的set进实体实在是太累了,于是便想到了用反射,最后使用spring的template做了一个导入
首先记录一下实现思路
- 读取studentdata的字段并保存到list(就是fieldList,其中filedList 是 list
- 读取excel文件的第一行,并开始和studentdata中的字段开始比对,如果读取到的名称和filedList中的名称相同,那么记录该属性在fieldList的位置(用于set,get),存成map(index)然后保存到fieldList中,同时把属性名称也存成map(name)然后保存到fieldList中
- 读取数据读取到对应的数据后开始封装set方法,并把读取到的值入参,即封装到student实体类中,然后没一行都添加进list集合中
- 前面三步已经把所有的数据封装到实体并存成list,拼接sql:将fileList中的name取出并拼成insert into student_data(exam_id,…..)values(?,….)
- 获取数据集:通过拼接get方法获取数据并保存到dataList中,然后在吧每一行转换为一个数组,在存到一个list中
- 使用jdbcTemplate将sql和数据入参
下面是代码,仅供参考
package com.education.wwjtj.test;
import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import com.education.wwjtj.domain.StudentData;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class StudentDataImportTest extends SpringTestBase{
@Test
public void test1() throws Exception {
List<Map> fieldList = getStudentDataFields(); //通过反射读取StudentData类的字段,但去除id与exam_id这两个
File file = new File("D:\\200405.xls");
loadStudentDataFieldInFile(file, fieldList); //解析文件中的第一行,并封装fieldList,每个fieldList有会有index字段,用于指向文件中的列序号
List<StudentData> list = loadStudentData(file, fieldList); //通过fieldList将文件中的数据封装至StudentData中,不过两个条件,1字段必须是小写,2是所有动态封装的数据类型必须为字符串
Integer examId = 1;
saveStudentDataBatch(examId, list, fieldList); //如果这里采用mybatis,前提是自行编写完xml,或是通过工具生成xml文件
}
//读取studentdata的字段
public List<Map> getStudentDataFields() {
List<Map> list = new ArrayList<Map>();
//设置忽略的字段
String[] exclude_fields = new String[]{"id","exam_id"};
Arrays.sort(exclude_fields);
Class cls = StudentData.class;
Field[] fields = cls.getDeclaredFields();//获取studentdata类中声明的字段
for(Field field:fields) {
int index = Arrays.binarySearch(exclude_fields, field.getName());
if(index<0) {
Map map = new HashMap();
map.put("name", field.getName());
list.add(map);
}
}
return list;
}
//读取第一行
public void loadStudentDataFieldInFile(File file, List<Map> fieldList) throws Exception {
Workbook workbook = Workbook.getWorkbook(file);
Sheet sheet = workbook.getSheet(0);
if(sheet.getRows()<1) {
throw new RuntimeException("文件不得少于1行");
}
Cell[] cells = sheet.getRow(0);
for(int i=0;i<cells.length;i++) {//获取每一列的值
Cell cell = cells[i];
if(cell!=null) {
String content = cell.getContents().trim().toLowerCase();//获取第一行的字段名
int index = findFields(content, fieldList);
if(index>=0) {
Map map = fieldList.get(index);
map.put("index", index);
}
}
}
workbook.close();
}
//比对excel中的字段名和studentdata的字段名是否一样,如果一样的话,获取该字段在fieldList(studentdata)中的位置,也就是index值
public int findFields(String name, List<Map> fieldList) {
int index = -1;//不存在
for(int i=0;i<fieldList.size();i++) {
Map map = fieldList.get(i);
String fieldname = (String) map.get("name");
if(fieldname.equals(name)) {
index = i;
break;
}
}
return index;
}
public List<StudentData> loadStudentData(File file, List<Map> fieldList) throws Exception {
List<StudentData> list = new ArrayList<StudentData>();
Workbook workbook = Workbook.getWorkbook(file);
Sheet sheet = workbook.getSheet(0);
for(int i=1;i<sheet.getRows();i++) {
Cell[] cells = sheet.getRow(i);
StudentData studentData = new StudentData();
for(int j=0;j<fieldList.size();j++) {
Map map = fieldList.get(j);
String fieldname = (String) map.get("name");//studentdata中对应的属性的名称
Integer index = (Integer) map.get("index");//该属性名称在studentdata属性集合中的位置
if(index!=null && index<cells.length) {
Cell cell = cells[index];
if(cell!=null) {
String content = cell.getContents().trim();//对应字段的数据
setStudentDataValue(fieldname, studentData, content);
}
}
}
list.add(studentData);
}
workbook.close();
return list;
}
public void setStudentDataValue(String fieldname, StudentData studentData, String content) throws Exception {
String methodname = "set"+fieldname.substring(0, 1).toUpperCase()+fieldname.substring(1);
Method method = studentData.getClass().getDeclaredMethod(methodname, String.class);
method.invoke(studentData, content);
}
//sql语句:insert into student_data (exam_id,.....)values(?,....)
public String getSql(List<Map> fieldList) {
StringBuffer sql1 = new StringBuffer("(exam_id");
StringBuffer sql2 = new StringBuffer("(?");
for(Map map:fieldList) {
String fieldname = (String) map.get("name");
sql1.append(","+fieldname);
sql2.append(",?");
}
sql1.append(")");
sql2.append(")");
return "insert into student_data "+sql1.toString()+" values "+sql2.toString();
}
public List<Object[]> getJdbcParamterDataList(Integer examId,List<StudentData> list, List<Map> fieldList) throws Exception {
List<Object[]> rows = new ArrayList<Object[]>();
for(StudentData studentData:list) {
List datas = new ArrayList();
datas.add(examId);
for(Map map:fieldList) {
String fieldname = (String) map.get("name");//字段名
//拼接get方法并获取值
String methodname = "get"+fieldname.substring(0, 1).toUpperCase()+fieldname.substring(1);
Method method = studentData.getClass().getDeclaredMethod(methodname);
Object value = method.invoke(studentData, null);
datas.add(value);
}
//将集合转换为数组:datas.toArray()
rows.add(datas.toArray());
}
return rows;
}
public void saveStudentDataBatch(Integer examId,List<StudentData> list, List<Map> fieldList) throws Exception {
JdbcTemplate jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);
jdbcTemplate.update("delete from student_data where exam_id=?", new Object[]{examId}); //这步类似于导入进行覆盖,先清空数据再导入,当然这根据看项目需求,这里默认数据导入时先清空数据再导入
String sql = getSql(fieldList);
List<Object[]> dataList = getJdbcParamterDataList(examId, list, fieldList);
jdbcTemplate.batchUpdate(sql, dataList);
}
}