Java NamedParameterJdbcTemplate.update方法代碼示例

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@Override
public int zetHandelingenStatusInLevering(final Set<Long> ids) {
    if (ids.isEmpty()) {
        return 0;
    }
    final String sql = "UPDATE kern.admhnd SET statuslev = 3 WHERE id IN (:ids) AND statuslev = 1";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);

    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("ids", ids);
    return jdbcTemplate.update(sql, parameters);
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@Override
public void updateAfnemerindicatieBlob(final long persoonId, final Long lockVersiePersoon, final Long afnemerindicatieLockVersie) throws BlobException {
    final List<PersoonAfnemerindicatie> afnemerindicatiesNaToevoegen = afnemerindicatieRepository.haalAfnemerindicatiesOp(persoonId);
    final AfnemerindicatiesBlob afnemerindicatiesBlob = Blobber.maakBlob(afnemerindicatiesNaToevoegen);
    LOGGER.info("Blobify persoon:{}", persoonId);
    final byte[] afnemerindicatiesBlobBytes = Blobber.toJsonBytes(afnemerindicatiesBlob);
    //lockversieafnemerindicatiege = null uit initiele vulling of nog geen afnemerindicatie aanwezig
    final String sql
            = "UPDATE kern.perscache pc "
            + "SET lockversieafnemerindicatiege = CASE WHEN lockversieafnemerindicatiege IS NOT NULL THEN lockversieafnemerindicatiege + 1 ELSE  1 END, "
            + "afnemerindicatiegegevens = :afnemerindicatiegegevens "
            + "WHERE (pc.lockversieafnemerindicatiege = :lockversieAfnemerindicatie OR pc.lockversieafnemerindicatiege is null) "
            + "AND pc.pers = :persoonId "
            + "AND EXISTS "
            + "(SELECT 1 FROM kern.pers p WHERE p.id = pc.pers AND p.lockversie = :persoonLock )";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);
    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("afnemerindicatiegegevens", afnemerindicatiesBlobBytes);
    parameters.put("lockversieAfnemerindicatie", afnemerindicatieLockVersie);
    parameters.put("persoonId", persoonId);
    parameters.put("persoonLock", lockVersiePersoon);
    final int rowsUpdated = jdbcTemplate.update(sql, parameters);
    if (rowsUpdated != 1) {
        throw new OptimisticLockException("PersoonCache is ondertussen gewijzigd.");
    }
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@Override
@Transactional
public void insertStatsDirNoSize(List<Long> parentsid) throws SaodException {
	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	String query = sqlQueries.getQuery("insert_stats_dir_local_size.sql");

	for (Long id : parentsid) {
		if (loadRow(id) != null) {
			continue;
		}

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("node_id", id);
		parameters.addValue("local_size", 0);
		jdbcNamesTpl.update(query, parameters);
	}
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
private void insertValue(NamedParameterJdbcTemplate jdbcTemplate, int i) throws SQLException {
    String value = String.format("{\"id\": "
            + "\"%s\", \"max\": 2, \"message\": \"what upz\", \"lastSeen\": 12345, \"progress\": 1}", i);
    PGobject valueJson = new PGobject();
    valueJson.setType("jsonb");
    valueJson.setValue(value);
    SqlParameterSource params = new MapSqlParameterSource().addValue("keyIn", "key" + i).addValue("contentIn", valueJson);
    jdbcTemplate.update("INSERT INTO activity\n"
            + "VALUES(:keyIn, :contentIn, now());\n", params);
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
public static void log(String userId) {
	if ( StringUtils.isBlank(userId) ) {
		log.warn("null userId");
		return;
	}
	NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate)AppContext.getBean("namedParameterJdbcTemplate");
	Map<String, Object> paramMap = new HashMap<String, Object>();
	paramMap.put("oid", SimpleUtils.getUUIDStr());
	paramMap.put("user", userId);
	paramMap.put("cuserid", "SYS");
	paramMap.put("cdate", new Date());
	try {
		namedParameterJdbcTemplate.update("insert into tb_sys_login_log(OID, USER, CUSERID, CDATE) values(:oid, :user, :cuserid, :cdate)", paramMap);
	} catch (Exception e) {
		e.printStackTrace();
		log.error( e.getMessage().toString() );
	}		
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
public static void log(String userId, String sysId, String executeEventId, boolean permit) {
	if ( StringUtils.isBlank(userId) || StringUtils.isBlank(sysId) || StringUtils.isBlank(executeEventId) ) {
		log.warn("null userId=" + userId + ", sysId=" + sysId + ", executeEventId=" + executeEventId);
		return;
	}		
	if ( executeEventId.indexOf(Constants._COMMON_LOAD_FORM_ACTION) > -1 ) {
		log.warn("Common load form no need event log : " + executeEventId + " , permit = " + permit);
		return;
	}
	NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate)AppContext.getBean("namedParameterJdbcTemplate");
	Map<String, Object> paramMap = new HashMap<String, Object>();
	paramMap.put("oid", SimpleUtils.getUUIDStr());
	paramMap.put("user", userId);		
	paramMap.put("sysId", sysId);
	paramMap.put("executeEvent", ( executeEventId.length()>255 ? executeEventId.substring(0, 255) : executeEventId ) );		
	paramMap.put("isPermit", ( permit ? "Y" : "N" ) );		
	paramMap.put("cuserid", "SYS");
	paramMap.put("cdate", new Date());
	try {
		namedParameterJdbcTemplate.update("insert into tb_sys_event_log(OID, USER, SYS_ID, EXECUTE_EVENT, IS_PERMIT, CUSERID, CDATE) " 
				+ "values(:oid, :user, :sysId, :executeEvent, :isPermit, :cuserid, :cdate)", paramMap);
	} catch (Exception e) {
		e.printStackTrace();
		log.error( e.getMessage().toString() );
	}		
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
private void removeTestFormData() {
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
	
	int formId = -1;
	try {
		formId = template.queryForObject("select formid from form where formid=:formid", 
				new MapSqlParameterSource("formid", 9999), Integer.class);
	} catch(Exception e) {
		System.out.println("Failed to get formId, not removing form!");
		return;
	}
	System.out.println("\n!!!Retrieve test form ID result: " + formId);
			
	
	int formResult = template.update("delete from form where formid=:formid",
			new MapSqlParameterSource("formid", formId));
	System.out.println("\n!!!Remove test Form result: " + formResult);
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@SuppressWarnings("unchecked")
private static <T> AffectedRowCountAndKey<T> executeUpdateAndKeepKeys(
		String template, Method method,
		NamedParameterJdbcTemplate jdbc, SqlParameterSource parameters) {
	
	Class<T> keyClass = (Class<T>) ((ParameterizedType) method.getGenericReturnType()).getActualTypeArguments()[0];
	
	KeyHolder keyHolder = new GeneratedKeyHolder();
	
	int result = jdbc.update(template, parameters, keyHolder);
	Map<String, Object> keys = keyHolder.getKeys();
	Object key;
	if (keys.size() > 1) {
		AutoGeneratedKey spec = Objects.requireNonNull(withType(method.getDeclaredAnnotations(), AutoGeneratedKey.class), "more than one key for query " + template + ": annotation @AutoGeneratedKey required");
		key = Objects.requireNonNull(keys.get(spec.value()), "the key with name " + spec.value() + " has returned null for query " + template + ": required a non null key");
	} else if (Number.class.isAssignableFrom(keyClass)) {
	    Class<? extends Number> c = (Class<? extends Number>) keyClass;
	    return new AffectedRowCountAndKey<>(result, (T) NumberUtils.convertNumberToTargetClass(keyHolder.getKey(), c));
	} else {
		key = keys.values().iterator().next();
	}
	return new AffectedRowCountAndKey<>(result, keyClass.cast(key));
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@Override
@Bedrijfsregel(Regel.R1988)
public int markeerAdministratieveHandelingAlsVerwerkt(final Long administratieveHandelingId) {
    //update alle handelingen die nog niet geleverd zijn, en ook niet al op klaar. Status mag null zijn of in levering
    final String sql
            = "UPDATE kern.admhnd SET statuslev = :statuslevVerwerkt, tslev = :tslev WHERE id = :admhndIdVerwerkt "
            + "AND (tslev IS NULL OR statuslev = :statuslevInLevering)";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);
    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("statuslevVerwerkt", StatusLeveringAdministratieveHandeling.GELEVERD.getId());
    parameters.put("tslev", new Date());
    parameters.put("admhndIdVerwerkt", administratieveHandelingId);
    parameters.put("statuslevInLevering", StatusLeveringAdministratieveHandeling.IN_LEVERING.getId());
    return jdbcTemplate.update(sql, parameters);
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
@Override
public int markeerAdministratieveHandelingAlsFout(final Long administratieveHandelingId) {
    final String sql = "UPDATE kern.admhnd SET statuslev = :statuslevFout WHERE id = :admhndIdFout "
            + "AND (tslev IS NULL OR statuslev = :statuslevInLeveringFoutPad)";
    final NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(masterDataSource);
    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("admhndIdFout", administratieveHandelingId);
    parameters.put("statuslevFout", StatusLeveringAdministratieveHandeling.FOUT.getId());
    parameters.put("statuslevInLeveringFoutPad", StatusLeveringAdministratieveHandeling.IN_LEVERING.getId());
    return jdbcTemplate.update(sql, parameters);
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
private void clean() throws Exception {
	NamedParameterJdbcTemplate namedParameterJdbcTemplate = (NamedParameterJdbcTemplate)
			AppContext.getBean("namedParameterJdbcTemplate");
	Map<String, Object> paramMap = new HashMap<String, Object>();
	namedParameterJdbcTemplate.update("delete from bb_swot_report_mst", paramMap);
	namedParameterJdbcTemplate.update("delete from bb_swot_report_dtl", paramMap);
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
/**
 * Step 1: You INSERT necessary placeholder records
 * MUST be executed inside Transaction
 */
private void step1insertPlaceholders(String schemaName, String summaryTable,
                                     String schoolTable,
                                     NamedParameterJdbcTemplate jdbcTemplate,
                                     TransactionStatus tx,
                                     DateTime snapshotTime) {
    final String sql = "INSERT INTO " + schemaName + "." + summaryTable + " (snapshottime, option_id)\n" +
            "SELECT :snapshotTime::timestamp with time zone snapshottime," +
            "  (unnest(option)->>'id')::integer option_id\n" +
            "FROM " + schemaName + "." + schoolTable;
    log.info("Inserting placeholder for {}", snapshotTime);
    jdbcTemplate.update(sql, ImmutableMap.of("snapshotTime", snapshotTime.toString()));
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
private void removeTestUserData() {
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
	
	long userId = -1;
	try {
		userId = template.queryForObject("select userid from public.user where username=:username", 
				new MapSqlParameterSource("username", "[email protected]"), Long.class);
	} catch(Exception e) {
		System.out.println("Failed to get userId, not removing any other test data");
		return;
	}
	System.out.println("\n!!!Retrieve test user ID result: " + userId);
	
	if(userId < 1) {
		System.out.println("Invalid userId: " + userId + ", not removing any further test data");
		return;
	}
	
	int contactResult = template.update("delete from contact where userid=:userid",
			new MapSqlParameterSource("userid", userId));
	System.out.println("\n!!!Remove test Contacts result: " + contactResult);
	
	int userorgResult = template.update("delete from userorg where userid=:userid",
			new MapSqlParameterSource("userid", userId));
	System.out.println("\n!!!Remove userorgs result: " + userorgResult);
	
	int userResult = template.update("delete from public.user where username=:username", 
			new MapSqlParameterSource("username", "[email protected]"));		
	System.out.println("\n!!!Remove test user result: " + userResult);
	
	// TODO: remove contacts and userorg, etc
	
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
private void updateTbmSysDao(TbmSysDaoDVO t, NamedParameterJdbcTemplate u, boolean existsSchemaDatabase) throws Exception {
	StringBuffer sb = new StringBuffer();

	if (t.getClassName() == null || t.getClassName().isEmpty()) {
		throw new IllegalArgumentException("class Name is null.");
	}

	StringBuffer sqlCheckExistsTbmSysDao;
	sqlCheckExistsTbmSysDao = new StringBuffer();
	sqlCheckExistsTbmSysDao.append("SELECT 1 FROM \n");
	if (existsSchemaDatabase)
		sqlCheckExistsTbmSysDao.append("meerkat.TBM_SYS_DAO \n");
	else
		sqlCheckExistsTbmSysDao.append("TBM_SYS_DAO \n");
	sqlCheckExistsTbmSysDao.append("WHERE PACKAGE_NAME =:packageName AND CLASS_NAME =:className");

	Map<String, Object> hashMap = new LinkedHashMap<String, Object>();
	hashMap.put("packageName", t.getPackageName());
	hashMap.put("className", t.getClassName());
	hashMap.put("location", t.getLocation());
	hashMap.put("classDesc", t.getClassDesc());
	hashMap.put("tableName", t.getTableName());

	if (checkExists(sqlCheckExistsTbmSysDao.toString(), hashMap, u)) {
		sb.append("UPDATE\n");
		if (existsSchemaDatabase)
			sb.append(" meerkat.TBM_SYS_DAO  \n");
		else
			sb.append(" TBM_SYS_DAO  \n");
		sb.append("SET LOCATION = :location,\n");
		sb.append("CLASS_NAME =:className,\n");
		sb.append("TABLE_NAME =:tableName,\n");
		sb.append("CLASS_DESC =:classDesc\n");
		sb.append("WHERE 1=1\n");
		sb.append("AND PACKAGE_NAME =:packageName\n");
		sb.append("AND CLASS_NAME =:className\n");

	} else {
		sb.append("INSERT INTO \n");
		if (existsSchemaDatabase)
			sb.append("meerkat.TBM_SYS_DAO\n");
		else
			sb.append("TBM_SYS_DAO\n");

		sb.append("(PACKAGE_NAME, CLASS_NAME , LOCATION,\n");
		sb.append("CLASS_DESC, TABLE_NAME\n");
		sb.append(") VALUES\n");
		sb.append("(\n");
		sb.append(":packageName, \n");
		sb.append(":className,\n ");
		sb.append(":location, \n");
		sb.append(":classDesc, \n");
		sb.append(":tableName \n");
		sb.append(")\n");
	}

	String velocityToText = ValueUtil.getVelocityToText(sb.toString(), hashMap, false);

	try {
		u.update(velocityToText, hashMap);
	} catch (Exception e) {
		LOGGER.error("### ERROR QUERY ###");
		LOGGER.error(sb.toString());
		LOGGER.error("### ERROR Velocity QUERY ###");
		LOGGER.error(ValueUtil.getVelocityToText(sb.toString(), hashMap, true));
		LOGGER.error("### PARAMETER ###");
		LOGGER.error(hashMap.toString());
		throw e;
		// 최종연산 및 트랜잭션에 실패했다는 메세지를 전달하기 위해 e를 다시 던짐.
	}
}
 

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; //導入方法依賴的package包/類
/**
 * batchupdate를 수행한다.
 *
 * @작성자 : KYJ
 * @작성일 : 2015. 11. 2.
 * @param sql
 * @param params
 * @param u
 * @return
 */
private int update(String sql, Map<String, Object> param, NamedParameterJdbcTemplate u) {
	return u.update(sql, param);
}