SpringJDBC在处理sql语句中的写法

新增

public int insertBy(Map<String, Object> map, String currentOrgCode) {
		StringBuffer sql = new StringBuffer(
				"INSERT INTO T_RUT_Temporary_Dispatch");
		sql.append("(ID,Org_Code,Emp_Code,Last_Next_Org_Code,New_Next_Org_Code,Start_Time,End_Time,Status,Create_Time,Modify_Time,Version_No)");
		sql.append("VALUES(SYS_GUID(),?,?,?,?,?,?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,?)");
		List<Object> args = new ArrayList<Object>();
		args.add(map.get("newOrgCode"));
		args.add(map.get("empCpde"));
		args.add(map.get("lastOrgCode"));
		args.add(map.get("lastNewOrgCode"));
		args.add(map.get("startTime"));
		args.add(map.get("endTime"));
		String status = RutConstant.RUT_TEMPORARY_DISPATCH_NEW_CODE;
		if ("999999".equals(currentOrgCode)) {
			status = RutConstant.RUT_TEMPORARY_DISPATCH_VALID_CODE;
		}
		args.add(status);
		args.add(map.get("versionNO"));
		return super.getJdbcTemplate().update(sql.toString(), args.toArray());
	}

批量新增

public int batchInsertBy(final List<RutTemporaryDispatch> rutTemporary,final String currentOrgCode) {
		
		String sql ="INSERT INTO T_RUT_Temporary_Dispatch "+
		"(ID,Org_Code,Emp_Code,Last_Next_Org_Code,New_Next_Org_Code,Start_Time,End_Time,Status,Create_Time,Modify_Time,Version_No)" +
		"VALUES(SYS_GUID(),?,?,?,?,?,?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,?)";
		getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				RutTemporaryDispatch rutTemporaryDispatch = rutTemporary.get(index);
				String id = StringUtils.getUUID();
				rutTemporaryDispatch.setId(id);
				int i = 0;
				ps.setString(++i, rutTemporaryDispatch.getOrgCode());
				ps.setString(++i, rutTemporaryDispatch.getEmpCode());
				ps.setString(++i, rutTemporaryDispatch.getLastOrgCode());
				ps.setString(++i, rutTemporaryDispatch.getNewOrgCode());
				ps.setString(++i, rutTemporaryDispatch.getStartTime());
				ps.setString(++i, rutTemporaryDispatch.getEndTime());
				String status = RutConstant.RUT_TEMPORARY_DISPATCH_NEW_CODE;
				if ("999999".equals(currentOrgCode)) {
					status = RutConstant.RUT_TEMPORARY_DISPATCH_VALID_CODE;
				}
				ps.setString(++i, status);
				ps.setLong(++i, rutTemporaryDispatch.getVersionNo());
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return rutTemporary.size();
			}
		});
		return 0;
		
	}

查询

public List<OptionObjectPair> getEffectiveCodes(String takingOrgCode,String newDestOrgCode) {
		String sql = "SELECT R.EFFECTTIVE_CODE, M.NAME EFFECTTIVE_NAME " +
				"  FROM YTRUT.T_RUT_TIME_EFFECTIVE R LEFT JOIN YTMDM.T_MDM_EFFECTIVE_TYPE M ON M.CODE = R.EFFECTTIVE_CODE" +
				" WHERE R.ORIG_ORG_CODE = ? AND R.DEST_ORG_CODE = ? AND R.STATUS = ?";
		return this.getJdbcTemplate().query(sql, new Object[]{takingOrgCode, newDestOrgCode, Constants.STATUS_VALID}, new RowMapper(){
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				OptionObjectPair dto = new OptionObjectPair();
				dto.setKey(rs.getString("EFFECTTIVE_CODE"));
				dto.setValue(rs.getString("EFFECTTIVE_NAME"));
				return dto;
			}
		});
	}



阅读更多
想对作者说点什么? 我来说一句

sql句中join的用法

2011年11月08日 4KB 下载

SQL句中使用的函数

2011年07月28日 502KB 下载

没有更多推荐了,返回首页

不良信息举报

SpringJDBC在处理sql语句中的写法

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭