文件采集到mysql_上传文件实现数据采集以及批量导入数据库实例

因为项目要求，需要读取本地文件，实现数据采集，然后批量导入数据库。

友情提示：下面代码中关于数据库的操作的代码有的是封装好的，比如save()方法，findBatchNum()方法并没有在本篇博客中贴出来，但是方法的作用会以注释形式声明。

数据采集(bean)

import java.io.BufferedReader;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStreamReader;

public class User {

private String name;

private String address;

private String phone;

private String company;

private Integer idCard;

private Integer blood;

private Long id;

public static User parse(String str){

User u = new User();

String[] s = str.split("\\|");

u.setName(s[0].equals("未采集")?"":s[0]);

u.setAddress(s[1].equals("未采集")?"":s[1]);

u.setPhone(s[2].equals("未采集")?"":s[2]);

u.setCompany(s[3].equals("未采集")?"":s[3]);

u.setIdCard(s[4].equals("未采集")?0:Integer.parseInt(s[4]));

u.setBlood(s[5].equals("未采集")?0:Integer.parseInt(s[5]));

return u ;

}

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public String getPhone() {

return phone;

}

public void setPhone(String phone) {

this.phone = phone;

}

public String getCompany() {

return company;

}

public void setCompany(String company) {

this.company = company;

}

public Integer getIdCard() {

return idCard;

}

public void setIdCard(Integer idCard) {

this.idCard = idCard;

}

public Integer getBlood() {

return blood;

}

public void setBlood(Integer blood) {

this.blood = blood;

}

@Override

public String toString() {

// TODO Auto-generated method stub

return "name"+name+"address"+address+"phone"+phone+"company"+company+"idCard"+idCard+"blood"+blood;

}

//测试

public static void main(String[] args) throws IOException {

File file = new File("C:/test/23333.txt");

FileInputStream fis = new FileInputStream(file);

InputStreamReader isr = new InputStreamReader(fis,"utf-8");

BufferedReader br = new BufferedReader(isr);

String b=null;

while((b=br.readLine())!=null){

System.out.println(User.parse(b).toString());

}

br.close();

}

}

记录数据导入相关信息(bean)

import java.util.Date;

public class ImportRecord {

/**

* 批次号(无后缀文件名称)

*/

private String batchNum;

/**

* 每次导入的总记录数

*/

private int totalRecord;

/**

* 导入失败为0 ，导入成功为1

*/

private int status;

/**

* 导入时间

*/

private Date createTime;

/**

* 导入完成时间

*/

private Date finishTime;

private Long id;

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getBatchNum() {

return batchNum;

}

public void setBatchNum(String batchNum) {

this.batchNum = batchNum;

}

public int getTotalRecord() {

return totalRecord;

}

public void setTotalRecord(int totalRecord) {

this.totalRecord = totalRecord;

}

public int getStatus() {

return status;

}

public void setStatus(int status) {

this.status = status;

}

public Date getCreateTime() {

return createTime;

}

public void setCreateTime(Date createTime) {

this.createTime = createTime;

}

public Date getFinishTime() {

return finishTime;

}

public void setFinishTime(Date finishTime) {

this.finishTime = finishTime;

}

}

文件上传以及数据的导入(UserService.java)

public void batchSave(MultipartFile dataFile,HttpServletRequest request){

BufferedReader br=null;

InputStreamReader isr=null;

FileInputStream fis=null;

try {

CommonsMultipartFile cf = (CommonsMultipartFile) dataFile;

MultipartHttpServletRequest multiRequest=(MultipartHttpServletRequest) request;

DiskFileItem fi = (DiskFileItem) cf.getFileItem();

File file = fi.getStoreLocation();

fis = new FileInputStream(file);

isr = new InputStreamReader(fis, "UTF-8");

br = new BufferedReader(isr);

String b= null;

List u=new ArrayList();//把每一天记录保存到集合中，以便批量保存到数据库中

while ((b= br.readLine()) != null) {

User user = User.parse(b);

u.add(user);

}

//logger.info("读取条数:"+u.size());

Iterator iter = multiRequest.getFileNames();

MultipartFile f = multiRequest.getFile(iter.next());

String fileName = f.getOriginalFilename();

String userName= fileName.substring(0,fileName.lastIndexOf(".")); //获取无后缀文件名称

//以文件名作为条件从数据库查询

ImportRecord importRecord = importRecordService.findBatchNum(userName);

if (importRecord != null) {//如果结果不为空，则不能导入，防止重复导入

throw new RuntimeException();

}else{

ImportRecord record=new ImportRecord();

record.setBatchNum(batchNum);

record.setTotalRecord(customers.size());

record.setStatus(0);//没导入之前的状态为0

record.setCreateTime(new Date()); //导入开始时间

//批量导入

                            dao.batchSaveCustomer(customers);

record.setFinishTime(new Date()); //导入结束时间

record.setStatue(1);//导入成功的状态为1

importRecordService.save(record);//保存到数据库中

}

} catch (IOException e) {

e.printStackTrace();

} finally{

try{

if(br!=null)br.close();

if(isr!=null)isr.close();

if(fis!=null)fis.close();

}catch(Exception ex){

ex.printStackTrace();

}

}

}

把数据批量导入到数据库中(UserDao.java)

public void batchSaveUser(List list) {

Session session = getSession().getSessionFactory().openSession();

session.beginTransaction().begin();

for (int i = 0; i < list.size(); i++) {

getSession().save(list.get(i));

// 批插入的对象立即写入数据库并释放内存

if (i % 50 == 0 || i == list.size()-1) {

session.flush();

session.clear();

}

}

session.getTransaction().commit(); // 提交事物

session.close();

}

controller层

@RequestMapping

public String add(@RequestParam("dataFile")MultipartFile dataFile,HttpServletRequest request,Model model){

try{

customerService.batchSave(dataFile,request);

}catch(RuntimeException e){

model.addAttribute("remind", "批次已存在");

e.printStackTrace();

}catch(Exception ex){

ex.printStackTrace();

}

return "user/user-list";

}

页面(jsp)

function handler1(tp){

if(tp=='ok'){

$("#form1").submit();

}

}

• 测试管理
• >
• 导入

文件:

${remind}

导入