读取txt-jdbc导入百万级数据.rar

/** * */ package com.wichina.wxcs.actPreferentialPlan.admin.service; import java.io.File; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.LinkedList; import java.util.List; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.wichina.common.pojo.Page; import com.wichina.common.spring.dao.IBaseHibernateDao; import com.wichina.common.spring.jdbc.IBaseJDBCDao; import com.wichina.common.util.FileUploadUtil; import com.wichina.common.util.TxtUtil; import com.wichina.wxcs.actPreferentialPlan.model.ActPreferentialPlan; /** * @author sz * * create time：2013-1-28 */ @Service(value = "planInfoService") public class PlanInfoService { private static Log log = LogFactory.getLog(PlanInfoService.class); @Autowired protected IBaseHibernateDao baseDao; @Autowired IBaseJDBCDao jdbcBaseDao; /** * 保存信息 * * @return */ public int saveInfo(ActPreferentialPlan bean, File file, String fileName) { if (bean.getListType() != 0) {// 名单类型不是0 if (file != null) {// 有文档就上传 try { String path = uploadTxt(file, fileName); bean.setListFilePath(path); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } bean.setCreateTime(new Date()); baseDao.save(bean); return bean.getId(); } /** * 信息列表 有翻页 * * @param page * @return */ public Page getInfoList(Page page, String title) { List<Object> params = new LinkedList<Object>(); StringBuffer hql = new StringBuffer(); hql.append("from ActPreferentialPlan where 1=1"); if (StringUtils.isNotBlank(title)) { hql.append(" and title like ?"); params.add("%" + title + "%"); } hql.append(" order by createTime desc"); return baseDao.pageByHql(page, hql.toString(), params.toArray()); } /** * 根据ID获取方案信息 * * @param actID * @return */ public ActPreferentialPlan getActPlanInfo(int actID) { String hql = "from ActPreferentialPlan where id = ?"; return (ActPreferentialPlan) baseDao.queryUniqueByHql(hql, actID); } /** * 上传txt文档 * * @param file * @param fileName * @return * @throws IOException */ public String uploadTxt(File file, String fileName) throws IOException { FileUploadUtil fileupload; fileupload = new FileUploadUtil(file, fileName, "actPlan"); String path = fileupload.writeFile();// 保存文件 return path; } /** * 从TXT导入数据到数据库 * * @return * @throws SQLException */ public int importTxtForDB(File txtFile, final int listType, final int planID, final String planTitle, final int rowCount) throws SQLException { String sql = "insert into actPlan_list_manage(listType,mobile,planID,planTitle) values(?,?,?,?)"; final Connection conn = jdbcBaseDao.getConn(); final PreparedStatement pstmt = conn.prepareStatement(sql); conn.setAutoCommit(false); //实现TxtUtil的concreteProcess方法，这个是匿名类，继承了TxtUtil TxtUtil txtUtil = new TxtUtil(txtFile,1) { private List<String> mobileList = new ArrayList<String>();// 一个数组存放txt读取的电话 public void concreteProcess(String str) throws Exception { if (StringUtils.isNotBlank(str)) { this.rowTotal++;// 文本总数增加 mobileList.add(str);// 增加一个电话 } try { if (rowTotal % 500 == 0) {// 每500个一次 for (String mobile : mobileList) { pstmt.setInt(1, listType); pstmt.setString(2, mobile); pstmt.setInt(3, planID); pstmt.setString(4, planTitle); pstmt.addBatch();// 用PreparedStatement的批量处理 } pstmt.executeBatch();// 执行批量插入 mobileList.clear(); } if (flag) {// 如果大于等于总数了，把剩余的数据都插入数据库 for (String mobile : mobileList) { pstmt.setInt(1, listType); pstmt.setString(2, mobile); pstmt.setInt(3, planID); pstmt.setString(4, planTitle); pstmt.addBatch();// 用PreparedStatement的批量处理 } pstmt.executeBatch();// 执行批量插入 mobileList.clear(); } } catch (Exception e) { e.printStackTrace(); throw e; } } };// new TxtUtil(txtFile) end int rowTotal = 0; try { rowTotal = txtUtil.executeRead();//执行读取 conn.commit(); } catch (Exception e) { log.error("txt导入失败"); e.printStackTrace(); conn.rollback(); }finally{ conn.close(); } return rowTotal; } /** * 更新方案,并重新上传附件数据 * * @param obj * @return */ public boolean updatePlanInfo(ActPreferentialPlan bean,File file, String fileName) { if (file != null) {// 有文档就上传 try { String path = uploadTxt(file, fileName); bean.setListFilePath(path); bean.setIsImport(0); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } baseDao.update(bean); return true; } /** * 更新方案 * * @param obj * @return */ public boolean updatePlanInfo(ActPreferentialPlan bean) { baseDao.update(bean); return true; } /** * 根据方案ID获取，导入名单的总数 * @param actId * @return */ public int countList(int actId){ String hql = "select count(*) from ActListManage where planID = ?"; return baseDao.countByHql(hql, actId); } /** * 下架 * @param actId */ public void OffInfo(int actId){ String hql = "update ActPreferentialPlan set isUse=0 where id = ?"; baseDao.executeByHql(hql, actId); } /** * 上架 * @param actId */ public void OnInfo(int actId) { String hql = "update ActPreferentialPlan set isUse=1 where id = ?"; baseDao.executeByHql(hql, actId); } /** * 根据方案ID删除名单 * @param actId */ public void deleteList(int actId){ String hql = "delete from ActListManage where planID = ?"; baseDao.executeByHql(hql, actId); } }