工厂:
/**
* 创建各个 单例csvReader的工厂
* @author wj
* @date 2017-1-7
*
*/
public class CsvReaderAndPersistFactory {
// 定义一个Map用于保存第一次创建对象时的实例
private static final Map<String, Object> objectMap = Maps.newHashMap();
private CsvReaderAndPersistFactory(){}
@SuppressWarnings("rawtypes")
public synchronized static <T extends CsvFileReaderAndPersist> T create(Class<T> c ,String type) throws Exception {
CsvFileReaderAndPersist single = null;
String className = null;
className = c.getName();
try {
if (!objectMap.containsKey(className)) {
Class class1 = Class.forName(className);
// 获得有参构造
Constructor constructor = class1.getDeclaredConstructor(String.class);
// 设置有参构造是可访问的
constructor.setAccessible(true);
// 产生一个实例对象。
single = (CsvFileReaderAndPersist) constructor.newInstance(type);
objectMap.put(className, single);
}
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return (T) objectMap.get(className);
}
}
模板方法:
/**
* 读csv
* @author wj
* @date 2017-1-1
*
*/
public abstract class CsvFileReaderAndPersist<T> {
private static Logger logger = LoggerFactory.getLogger(CsvFileReaderAndPersist.class);
protected String type;
/**
* @param fileName
*/
public void readCsvFileAndPersist(String fileName) {
FileReader fileReader = null;
CSVParser csvFileParser = null;
//创建CSVFormat(header mapping)
// CSVFormat csvFileFormat = CSVFormat.DEFAULT.withHeader(FILE_HEADER);
CSVFormat csvFileFormat = CSVFormat.DEFAULT.withHeader(getHeader()).withSkipHeaderRecord(); //withSkipHeaderRecord 跳过第一行标题栏
try {
//初始化FileReader object
fileReader = new FileReader(fileName);
//初始化 CSVParser object
csvFileParser = new CSVParser(fileReader, csvFileFormat);
//CSV文件records
List<CSVRecord> csvRecords = csvFileParser.getRecords();
// CSV
dataPersist(csvRecords);
logger.info("---------------------成功导入【"+type+"】 "+csvRecords.size()+" 条数据------------");
}
catch (Exception e) {
e.printStackTrace();
logger.error("导入出错:"+e.getMessage());
return ;
} finally {
try {
fileReader.close();
csvFileParser.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
protected abstract String [] getHeader() ;
// protected abstract List<T> dataSetter( List<CSVRecord> records);
protected abstract void dataPersist(List<CSVRecord> records);
public String getVal(Map<String, Object> map, String param) {
return map.get(param) == null ? null : map.get(param).toString();
}
}
单例 + 模板方法:
/**
* 人员
* @author wj
*
*/
public class UserCsvFileReader extends CsvFileReaderAndPersist<Map<String,Object>> {
public static final String pwd = "jsxfdj123456";
private static Logger logger = LoggerFactory.getLogger(UserCsvFileReader.class);
private List<String> nullOrgids = Lists.newArrayList();
private List<String> dupids = Lists.newArrayList();
//CSV文件头
//"ID","JGMC","JGJC","JGNBID","JGLB","JGDM","XZQHDM","JGDZ","YZBM","LXDH","CZHM","LXR","QTSJBM","SSXFJGDM","JGLX","JGXZDM","JGTREE","QZ","SSYM","GIS_X","GIS_Y","GIS_H","XQGLID","GLID","JGMS","JLZT","SJC","BZ","SJBB","CJSJ","BJSJ","YWXTBSID","YWQH","JGCXZT","JGCXSJ","BYZD1","BYZD2","BYZD3","BYZD4","BYZD5","BYZD6","BYZD7","BYZD8","BYZD9","BYZD10","ZP"
protected static final String [] FILE_HEADER = {"ID","YHBH","XM","YHZH","BGXTZH","RYXH","YHLB","YHJBID","ZZMM","MZ","CSRQ","XB","JZID","JXID","ZWID","GWID","SSJG","SSDW","ZGDW","BMBM","YHZT","JZJDJG","JZJDDW","GWQY","SFZT","ZZDH","JTDH","SJHM","YXZH","XNH","YJFJDX","YHQZ","RZFS","JGZSMJ","YHZPSMJ","YHLCZT","SXRQ","SHXSJ","GZFW","JGZQFRQ","JGZID","JRSFZH","BBDJ","XBB","ADDACC","ADDACCNAME","ADDTIME","ADDIP","CHGACC","CHGACCNAME","CHGTIME","CHGIP","DELETED","LSJGID","YJXSBJ","TXLXSBJ","DXFSBJ","FLAG0","FLAG1","SFZW","QJID","QJLX","WJDX","RYZT","SJBB","CJSJ","BJSJ","YWXTBSID","ZHID","SIGNATUREFLAG","XZQH"
};
static JdbcTemplate jdbcTemplate = (JdbcTemplate) ContextUtil
.getApplicationContext().getBean("jdbcTemplate");
private UserCsvFileReader(String type){
super.type = type;
}
@Override
protected String[] getHeader() {
return FILE_HEADER;
}
@Override
protected void dataPersist( List<CSVRecord> csvRecords) {
String sql = "select * from JSXF_ORG_JCJ t ";
final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
Map<String,String> lookup_nbid_orgid = Maps.newHashMapWithExpectedSize(list.size());
Map<String,String> lookup_orgid_orgname = Maps.newHashMapWithExpectedSize(list.size());
for (Map<String, Object> m : list) {
String JGNBID = getVal(m, "JGNBID");
String ORG_ID = getVal(m, "ORG_ID");
String ORG_NAME = getVal(m, "ORG_NAME");
lookup_nbid_orgid.put(JGNBID, ORG_ID);
lookup_orgid_orgname.put(ORG_ID, ORG_NAME);
}
String SQL_CREAT_ALARM = Contants.sql_user;
// String SQL_CREAT_ALARM = " INSERT INTO JSXF_ORG_JCJ2(ORG_ID,JGMC,ORG_NAME,JGNBID,JGDM,JGDZ,YZBM,LXDH,CZHM,LXR,JGXZDM,JGTREE,JGMS,ORG_LONGITUDE,ORG_DIMENSION,GIS_H,GLID,XQGLID,SSYM,ZP,BZ,JGCXZT,YWXTBSID,ORG_CREATE_TIME,SJBB,JGCXSJ) "
// + " VALUES(?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?,?, ?,?,?,? )";
if(!CollectionUtils.isEmpty(csvRecords)){
int count = 0;
for(CSVRecord record : csvRecords){
/* T_BASE_USER 所需要的所有字段 */
String USERID =record.get("YHZH"); // '用户id';
String USERNAME =record.get("XM");// '用户名称';
if(StringUtils.isEmpty(USERNAME)){
//可以记录下个数
continue;
}
String SEX =record.get("XB");// '性别';
// String PWD =record.get("ID");// '密码'; //没有找到密码字段
String EMAIL =record.get("YXZH");// 'EMAIL';
String PHONE =record.get("SJHM");// '联系电话';
String NATION =record.get("MZ");// '民族';
String POLITICAL=record.get("ZZMM");// '整治面貌';
String PHOTO =record.get("YHZPSMJ");// '照片';
String BIRTHDAY = record.get("CSRQ");// '生日';
Date BIRTHDAY_date = DateUtils.parseDate(BIRTHDAY);
String JOINTIME=record.get("ADDTIME"); // '加入单位时间';
Date JOINTIME_date = DateUtils.parseDate(JOINTIME);
String COMPANYID =record.get("SSDW");// '所在单位ID';
String DEPARTMENT =record.get("BMBM");// '所在部门';
// String ADDRESS =record.get("ID");// '家庭住址'; //没有对应字段
// String LUTIME=record.get("ID"); // '最后登陆时间'; //没有对应字段
String LOCKED = record.get("SFZT");// '是否锁定';
String USER_TYPE =record.get("YHLB");// '用户类型(社区人员、单位人员、消防分管人员、管理员)'; // 他们:用户类别: ‘0’干部 ‘1’士官 ‘2’战士 ‘3’民警
// String ROLE_ID =record.get("ID");// '角色ID'; //没有对应字段
String SORTID=record.get("RYXH"); // '排序'; //数值太大,无法转long
String CARD_ID =StringUtils.trim(record.get("JRSFZH"));// '身份证号';
String CREATETIME =record.get("CJSJ");// '创建时间';
Date CREATETIME_date = DateUtils.parseDate(CREATETIME);
String AREA_SELF=record.get("XZQH"); // '所属区域';
// String IMSI =record.get("ID");// '手机端登录参数'; //没有对应字段
// String ORG_ID =record.get("ID");// '中队id'; //没有对应字段
String ORG_ID =getOrgId(lookup_nbid_orgid, record.get("SSJG"));// '中队id'; //可能为null,没有找到对应ORG_ID
String MENNU_ID =record.get("ZWID");// '职务';
String pwd = CryptoUtils.MD5().digestStr(UserCsvFileReader.pwd);
String role_id = getRoleId(lookup_orgid_orgname, ORG_ID);
try{
jdbcTemplate.update(SQL_CREAT_ALARM, USERID, USERNAME,
SEX,EMAIL,PHONE,NATION,POLITICAL,PHOTO,BIRTHDAY_date,JOINTIME_date,COMPANYID,
DEPARTMENT,Integer.valueOf(LOCKED),USER_TYPE,0,CARD_ID,CREATETIME_date,AREA_SELF,ORG_ID,MENNU_ID,pwd,role_id
);
count++;
}catch(org.springframework.dao.DuplicateKeyException e){ //如果冲突 继续插下面的
dupids.add(USERID);
count--;
}
}
logger.info("【用户】 成功导入 "+count+" / "+csvRecords.size()+" 条数据,但是其中 SSJG为:["+StringUtils.join(nullOrgids, ",") +"] 在系统中没有对应orgId,同时对应角色也为null。其中重复用户ID: ["+ StringUtils.join(dupids, ",")+" ]");
}
}
private String getOrgId(Map<String,String> map, String nbid){
String orgId = map.get(nbid);
if( orgId== null){
nullOrgids.add(nbid);
}
return orgId;
}
//copy from SynTest.java line:631
private String getRoleId(Map<String,String> map, String orgid){
if(orgid ==null){
return null;
}
String org_name = map.get(orgid);
String role_id = "manage";// 默认给支队权限
if (org_name.contains("中队")) {
role_id = "comm";
} else if (org_name.contains("大队")) {
role_id = "manageBrigade";
} else if (org_name.contains("总队")) {
role_id = "manageCorps";
}
return role_id;
}
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception{
// new OrgCsvFileReader("机构信息").readCsvFileAndPersist(Contants.ORG_DATA_PATH);
// new UserCsvFileReader("用户信息").readCsvFileAndPersist(Contants.USER_DATA_PATH);
CsvFileReaderAndPersist user = CsvReaderAndPersistFactory.create(UserCsvFileReader.class,"用户信息");
user.readCsvFileAndPersist(Contants.USER_DATA_PATH);
}
}
结合起来,功能:不同类型数据的读取CSV、以及持久化