关于excel数据批量导入数据库不成功的问题求助

门口塘 2019-06-20 01:07:11

想通过按网页按钮触发固定文件路径下excel表内数据存入数据库，现在是从excel数据可以正常读取。可是数据无法存入数据库，也没有报错。求各位帮忙看下。代码如下

package cn.itcast.crm.domain;

import java.io.Serializable;
import java.sql.Timestamp;
/*
* 用户（员工）管理
*/
public class CrmUser implements Serializable {
private static final long serialVersionUID=1L;
private String userId;
private String logonName;
private String logonPwd;
private String userName;
private String gender;
private Timestamp onDutyDate;
//多对一：多个用户属于一个职位
private CrmPost crmPost;
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getLogonName() {
return logonName;
}
public void setLogonName(String logonName) {
this.logonName = logonName;
}
public String getLogonPwd() {
return logonPwd;
}
public void setLogonPwd(String logonPwd) {
this.logonPwd = logonPwd;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Timestamp getOnDutyDate() {
return onDutyDate;
}
public void setOnDutyDate(Timestamp onDutyDate) {
this.onDutyDate = onDutyDate;
}
public CrmPost getCrmPost() {
return crmPost;
}
public void setCrmPost(CrmPost crmPost) {
this.crmPost = crmPost;
}
/*public CrmUser(String userId, String logonName, String logonPwd, String userName, String gender) {
super();
this.userId = userId;
this.logonName = logonName;
this.logonPwd = logonPwd;
this.userName = userName;
this.gender = gender;
}
public CrmUser() {
super();
// TODO Auto-generated constructor stub
}*/

}

package cn.itcast.crm.dao;

import java.util.List;

/*
* 抽取出的dao接口方法，专门用于继承
*/
public interface BaseDao<T> {
//添加
public void save(T t);
//更新
public void update(T t);
//保存或更新（代理主键：如果没有IOD将执行save，如果有IOD将执行update）
public void saveOrUpdate(T t);
//删除
public void delete(T t);
//通过id查询
public T findById(java.io.Serializable id);
//查询所有
public List<T> findAll();
/**
* 带有条件查询所有
* @param condition条件语句（where之后的语句）
* 格式：and属性关键字？
* 例如：and name like ？
* @param params条件
*/
public List<T> findAll(String condition,Object ...params);
//查询分页数据
public List<T> findAllWithPage(String conditionHQL,Object[] params,int startIndex,int pageSize);
//查询总记录数
public int getTotalRecord();
}

package cn.itcast.crm.dao.impl;
import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.util.List;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import cn.itcast.crm.dao.BaseDao;
import cn.itcast.crm.page.PageHibernateCallback;
/**
* dao接口的实现类，用于继承
*/
@SuppressWarnings("unchecked")
public class BaseDaoImpl<T> extends HibernateDaoSupport implements BaseDao<T> {
//通过泛型信息获取类信息
private Class<T> beanClass;
public BaseDaoImpl() {
// this ,在运行时表示的【当前运行类】。在编译时表示就是【当前类】
// 1 获得当前运行类的父类，父类具有泛型信息，
ParameterizedType parameterizedType = (ParameterizedType) this.getClass().getGenericSuperclass();
// 2 获得实际参数
beanClass = (Class<T>) parameterizedType.getActualTypeArguments()[0];
}
//保存
public void save(T t) {
this.getHibernateTemplate().save(t);
}
//更新
public void update(T t) {
this.getHibernateTemplate().update(t);
}
//保存或更新
public void saveOrUpdate(T t) {
this.getHibernateTemplate().saveOrUpdate(t);
};
//删除
public void delete(T t) {
this.getHibernateTemplate().delete(t);
}
//通过id查询
public T findById(Serializable id) {
return this.getHibernateTemplate().get(beanClass, id);
}
//查询所有
public List<T> findAll() {
// class.getName() 获得类的全限定类名，例如：java.lang.String
// hibernate hql 完成写法 “from cn.itcast.crm...CrmUser”
return this.getHibernateTemplate().find("from " + beanClass.getName());
}
//通过参数查询所有
public List<T> findAll(String condition, Object... params) {
String hql = "from " + beanClass.getName() + " where 1=1 " + condition;
return this.getHibernateTemplate().find(hql , params);
}
//分页查询
public List<T> findAllWithPage(String conditionHQL, Object[] params, int startIndex, int pageSize) {
String hql = "from " + beanClass.getName()+" as c where 1=1" + conditionHQL;
return this.getHibernateTemplate().execute(new PageHibernateCallback<T>(hql,params,startIndex,pageSize));
}
//获取总记录数
public int getTotalRecord() {
List<Long> allData = this.getHibernateTemplate().find("select count(u) from "+beanClass.getName()+" u");
return allData.get(0).intValue();
}
}

package cn.itcast.crm.dao;

import java.util.List;

import cn.itcast.crm.domain.CrmUser;

public interface CrmUserDao extends BaseDao<CrmUser> {
//通过登录名和密码查询用户
public CrmUser find(CrmUser crmUser);
public void deleteCrmUser(String id);
public void testExcelToDb(String file);
}

package cn.itcast.crm.dao.impl;

import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import cn.itcast.crm.dao.CrmUserDao;
import cn.itcast.crm.domain.CrmUser;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class CrmUserDaoImpl extends BaseDaoImpl<CrmUser> implements CrmUserDao {
//登录时查询用户
@Override
public CrmUser find(CrmUser crmUser) {
//定义HQL
String hql="from CrmUser where logonName=? and logonPwd=?";
//将传递的参数放入params数组中
Object[] params= {crmUser.getLogonName(),crmUser.getLogonPwd()};
//查询数据
List<CrmUser>allUser=this.getHibernateTemplate().find(hql, params);
//返回结果
return allUser.size()==0?null:allUser.get(0);
}

@Override
public void deleteCrmUser(String id) {
// TODO Auto-generated method stub
this.getHibernateTemplate().delete(findById(id));
}

/*
* 查询指定目录中电子表格中所有的数据
* @param file文件完整路径
* @return
*/
public void testExcelToDb(String file){
//List<CrmUser> list=new ArrayList<CrmUser>();
try {
Workbook rwb=Workbook.getWorkbook(new File(file));
Sheet rs=rwb.getSheet(0);
int clos=rs.getColumns();//得到所有的列
int rows=rs.getRows();//得到所有的行
System.out.println("clos:"+clos+" rows:"+rows);
for(int i=1;i<rows;i++) {
for(int j=0;j<clos;j++) {
//第一个是列数，第二个是行数
String userId=rs.getCell(j++, i).getContents();//默认最左边编号也算一列所以这里得j++
String logonName=rs.getCell(j++, i).getContents();
String logonPwd=rs.getCell(j++, i).getContents();
String userName=rs.getCell(j++, i).getContents();
String gender=rs.getCell(j++, i).getContents();
System.out.println("userId:"+userId+" logonName:"+logonName+
" logonPwd:"+logonPwd+" userName:"+userName+" gender:"+gender);
CrmUser crmUser=new CrmUser();
crmUser.setUserId(userId);
crmUser.setLogonName(logonName);
crmUser.setLogonPwd(logonPwd);
crmUser.setUserName(userName);
crmUser.setGender(gender);
this.getHibernateTemplate().save(crmUser);
//list.add(crmUser);
}
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

package cn.itcast.crm.service;

import java.util.List;

import cn.itcast.crm.domain.CrmUser;

public interface CrmUserService {
//登录
public CrmUser login(CrmUser crmUser);
/*
* 查询所有
*/
public List<CrmUser> findAllCrmUser();
/*
* 通过id查询用户
*/
public CrmUser findById(String id);
/*
* 保存或更新用户
*/
public void saveOrUpdateCrmUser(CrmUser crmUser);
public void deleteUser(String userId);
public void exceltoDb(String file);
}

package cn.itcast.crm.service.impl;

import java.util.List;

import cn.itcast.crm.dao.CrmUserDao;
import cn.itcast.crm.domain.CrmUser;
import cn.itcast.crm.service.CrmUserService;
import cn.itcast.crm.util.StringUtils;

public class CrmUserServiceImpl implements CrmUserService {
//定义crmUserDao属性及其setter方法
private CrmUserDao crmUserDao;

public void setCrmUserDao(CrmUserDao crmUserDao) {
this.crmUserDao = crmUserDao;
}
/*
*用户登录
*/

@Override
public CrmUser login(CrmUser crmUser) {
//密码需要加密
String logonPwd=StringUtils.getMD5Value(crmUser.getLogonPwd());
System.out.println(logonPwd);
crmUser.setLogonPwd(logonPwd);
return this.crmUserDao.find(crmUser);
}

@Override
public void deleteUser(String userId) {
// TODO Auto-generated method stub
this.crmUserDao.deleteCrmUser(userId);
}

@Override
public List<CrmUser> findAllCrmUser() {
// TODO Auto-generated method stub
return this.crmUserDao.findAll();
}

@Override
/*
* 根据id查询用户
*/
public CrmUser findById(String id) {
// TODO Auto-generated method stub
return this.crmUserDao.findById(id);
}

@Override
/*
* 保存或更新用户
*/
public void saveOrUpdateCrmUser(CrmUser crmUser) {
// TODO Auto-generated method stub
//密码需要加密
String logonPwd=StringUtils.getMD5Value(crmUser.getLogonPwd());
crmUser.setLogonPwd(logonPwd);
this.crmUserDao.saveOrUpdate(crmUser);
}

@Override
public void exceltoDb(String file) {
this.crmUserDao.testExcelToDb(file);
}

}

控制台可以正常输出excel读取数据，如下图：