S-HR之代码创建临时表并插入数据

...

private String tempTab1 = null;

 

//临时表EcirrWithPPTempTable

public String getTempTable() {
String tempTable = null;
try {
tempTable = "EcirrWithPPTempTable";
String createTableSQL = "create table "
+ tempTable
+ " (" +
"fempName varchar(50),"+//员工姓名
"fempNumber varchar(50),"+//员工编号
"fempEnterDateBefore varchar(50),"+//变更前入职时间
"fempEnterDateAfter varchar(50),"+//变更后入职时间
"fempJoinGroupDateBefore varchar(50),"+//变更前工龄计算起始时间
"fempJoinGroupDateAfter varchar(50),"+//变更后工龄计算起始时间
"flastUpdateTime varchar(50),"+//变更时间
"flastUpdateUserName varchar(50)"//变更人
+ " )";
TempTablePool tablePool = TempTablePool.getInstance(this.ctx);
tempTable = tablePool.createTempTable(createTableSQL);
} catch (Exception e) {
e.printStackTrace();
}
return tempTable;
}

 

private void getAllInfo(String tempTab12,Map<String, Object> map) {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String longnumber = null;
if (null == map.get("admin")) {
longnumber = "000001";
} else {
longnumber = map.get("admin").toString();
}
try {
StringBuffer sql = new StringBuffer();
sql.append(" SELECT person.FNAME_l2 empName,person.FNUMBER empNumber,ppHis.FENTERDATE empEnterDateBefore,pp.FENTERDATE empEnterDateAfter,ppHis.FJOINGROUPDATE empJoinGroupDateBefore,pp.FJOINGROUPDATE empJoinGroupDateAfter,pp.FLASTUPDATETIME lastUpdateTime,lastU.FNAME_l2 lastUpdateUserName ").append("\r\n");;
sql.append(" FROM T_HR_PERSONPOSITIONHIS pp ").append("\r\n");;
sql.append(" left join T_BD_Person person on pp.FPERSONID=person.FID ").append("\r\n");;
sql.append(" left join T_ORG_POSITION position on position.FID =pp.FPRIMARYPOSITIONID").append("\r\n");;
sql.append(" left join T_ORG_Admin org on org.FID = position.FADMINORGUNITID ").append("\r\n");;
sql.append(" left join T_ORG_ORGFUNCTION fun on fun.FID=org.forgfunctionid ").append("\r\n");;
sql.append(" left join T_PM_User lastU on lastU.FID =pp.FLASTUPDATEUSERID ").append("\r\n");;
sql.append(" left join T_HR_PERSONPOSITIONHIS ppHis on ppHis.FLEFFDT=pp.FEFFDT-1 and ppHis.FPERSONID=pp.FPERSONID ").append("\r\n");;
sql.append(" where org.flongnumber like '"+ longnumber + "%' and FIsSealUp='0' ").append("\r\n");;
// 开始日期
if (null != map.get("startDate")) {
sql.append(" and to_char(pp.FLASTUPDATETIME,'yyyy-MM-dd')>='"+ map.get("startDate") + "' ").append("\r\n");
}
// 结束日期
if (null != map.get("endDate")) {
sql.append(" and to_char(pp.FLASTUPDATETIME,'yyyy-MM-dd') <='"+ map.get("endDate") + "' ").append("\r\n");
}
IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());
while (rs.next()) {
Map<String, String> maps = new HashMap<String, String>();
maps.put("empName", rs.getString("empName"));
maps.put("empNumber", rs.getString("empNumber"));
maps.put("empEnterDateBefore",rs.getDate("empEnterDateBefore")==null?"":sdf.format(rs.getDate("empEnterDateBefore")));
maps.put("empEnterDateAfter", rs.getDate("empEnterDateAfter")==null?"":sdf.format(rs.getDate("empEnterDateAfter")));
maps.put("empJoinGroupDateBefore", rs.getDate("empJoinGroupDateBefore")==null?"":sdf.format(rs.getDate("empJoinGroupDateBefore")));
maps.put("empJoinGroupDateAfter", rs.getDate("empJoinGroupDateAfter")==null?"":sdf.format(rs.getDate("empJoinGroupDateAfter")));
maps.put("lastUpdateTime", rs.getDate("lastUpdateTime")==null?"":sdf.format(rs.getDate("lastUpdateTime")));
maps.put("lastUpdateUserName", rs.getString("lastUpdateUserName"));
list.add(maps);
}
//执行插入数据
insertAllInfo(tempTab1, list);
} catch (BOSException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

 

批量执行插入数据

//执行插入数据到tempTable1中
private void insertAllInfo(String tempTab1, List<Map<String, String>> list) {
try {
if (list.size() > 0) {
// 插入临时表
StringBuffer insertSQL = new StringBuffer();
insertSQL.append("INSERT INTO " + tempTab1);
insertSQL.append("(fempName,fempNumber,fempEnterDateBefore,fempEnterDateAfter,fempJoinGroupDateBefore,fempJoinGroupDateAfter,flastUpdateTime,flastUpdateUserName)").append(" VALUES(?,?,?,?,?,?,?,?)");
List<Object> intsertList = new ArrayList<Object>();
// 从map中取出数据放到临时表中
for (Map<String, String> map : list) {
//String number=map.get("longnumber");
Object[] obj = {
map.get("empName"),
map.get("empNumber"),
map.get("empEnterDateBefore"),
map.get("empEnterDateAfter"),
map.get("empJoinGroupDateBefore"),
map.get("empJoinGroupDateAfter"),
map.get("lastUpdateTime"),
map.get("lastUpdateUserName")
};
intsertList.add(obj);
}
DbUtil.executeBatch(ctx, insertSQL.toString(), intsertList);
}
} catch (Exception e) {
e.printStackTrace();
}
}