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

梦里梦外; 2022-01-06 14:21 202阅读 0赞

...

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();  
\}  
\}

转载于:https://www.cnblogs.com/luojiabao/p/11064262.html