Java JdbcTemplate.update方法代码示例

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
public void saveIdempotentPo(EasyTransFilterChain filterChain, IdempotentPo idempotentPo) {
	JdbcTemplate jdbcTemplate = filterChain.getResource(JDBC_TEMPLATE);
	int update = jdbcTemplate.update(
			"INSERT INTO `idempotent` (`src_app_id`, `src_bus_code`, `src_trx_id`, `app_id`, `bus_code`, `call_seq` ,`called_methods`, `md5`, `sync_method_result`, `create_time`, `update_time` , `lock_version`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", 
			idempotentPo.getSrcAppId(),
			idempotentPo.getSrcBusCode(),
			idempotentPo.getSrcTrxId(),
			idempotentPo.getAppId(),
			idempotentPo.getBusCode(),
			idempotentPo.getCallSeq(),
			idempotentPo.getCalledMethods(),
			idempotentPo.getMd5(),
			idempotentPo.getSyncMethodResult(),
			idempotentPo.getCreateTime(),
			idempotentPo.getUpdateTime(),
			idempotentPo.getLockVersion()
			);
	
	if(update != 1){
		throw new RuntimeException("update count exception!" + update);
	}
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
public void updateIdempotentPo(EasyTransFilterChain filterChain, IdempotentPo idempotentPo) {

		JdbcTemplate jdbcTemplate = filterChain.getResource(JDBC_TEMPLATE);
		int update = jdbcTemplate.update(
				"UPDATE `idempotent` SET `called_methods` = ?, `md5` = ?, `sync_method_result` = ?, `create_time` = ?, `update_time`  = ?, `lock_version` = `lock_version` + 1 WHERE `src_app_id` = ? AND `src_bus_code` = ? AND `src_trx_id` = ? AND `app_id` = ? AND `bus_code` = ?  AND `call_seq` = ? AND `lock_version` = ?;", 
				idempotentPo.getCalledMethods(),
				idempotentPo.getMd5(),
				idempotentPo.getSyncMethodResult(),
				idempotentPo.getCreateTime(),
				idempotentPo.getUpdateTime(),
				idempotentPo.getSrcAppId(),
				idempotentPo.getSrcBusCode(),
				idempotentPo.getSrcTrxId(),
				idempotentPo.getAppId(),
				idempotentPo.getBusCode(),
				idempotentPo.getCallSeq(),
				idempotentPo.getLockVersion()
				);
		
		if(update != 1){
			throw new RuntimeException("Optimistic Lock Error Occour Or can not find the specific Record!" + idempotentPo);
		}
	}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
public void addPointForBuying(OrderMessage msg){

		BusinessIdentifer businessIdentifer = ReflectUtil.getBusinessIdentifer(msg.getClass());
		
		JdbcTemplate jdbcTemplate = util.getJdbcTemplate(applicationName,businessIdentifer.busCode(),msg);
		int update = jdbcTemplate.update("update `point` set point = point + ? where user_id = ?;", 
				msg.getAmount(),msg.getUserId());
		
		if(update != 1){
			throw new RuntimeException("can not find specific user id!");
		}
		
		//for unit test
		if(successErrorCount != null){
			currentErrorCount++;
			if(successErrorCount < currentErrorCount){
				currentErrorCount = 0;
			} else {
				throw new UtProgramedException("error in message consume time:" + currentErrorCount);
			}
		}
	}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
@Test
public void testUploadDefaultWorkflowScheme() throws Exception {
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
	try {
		// Delete the default project/workflow mapping, where type = '0'
		jdbcTemplate.update(
				"update nodeassociation SET SOURCE_NODE_ID=? WHERE SOURCE_NODE_ID=? AND SOURCE_NODE_ENTITY=? AND SINK_NODE_ID=? AND SINK_NODE_ENTITY=? AND ASSOCIATION_TYPE=?",
				1, 10074, "Project", 10025, "WorkflowScheme", "ProjectScheme");
		resource.upload(new ClassPathResource("csv/upload/nominal-simple.csv").getInputStream(), ENCODING, subscription,
				UploadMode.PREVIEW);
	} finally {
		jdbcTemplate.update(
				"update nodeassociation SET SOURCE_NODE_ID=? WHERE SOURCE_NODE_ID=? AND SOURCE_NODE_ENTITY=? AND SINK_NODE_ID=? AND SINK_NODE_ENTITY=? AND ASSOCIATION_TYPE=?",
				10074, 1, "Project", 10025, "WorkflowScheme", "ProjectScheme");
	}

	final ImportStatus result = jiraResource.getTask(subscription);
	Assert.assertEquals(UploadMode.PREVIEW, result.getMode());
	Assert.assertFalse(result.isFailed());
	Assert.assertEquals(22, result.getStep());
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
@Test
public void clearLoginFailed() {
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
	Assert.assertEquals("1", jdbcTemplate.queryForObject("SELECT attribute_value FROM cwd_user_attributes WHERE ID=212", String.class));

	try {
		jdbcTemplate.update("update pluginversion SET pluginversion=? WHERE ID = ?", "6.0.1", 10075);
		// Updated counter
		dao.clearLoginFailed(datasource, "fdaugan");
	} finally {
		jdbcTemplate.update("update pluginversion SET pluginversion=? WHERE ID = ?", "4.4.1", 10075);
	}
	Assert.assertEquals("0", jdbcTemplate.queryForObject("SELECT attribute_value FROM cwd_user_attributes WHERE ID=212", String.class));

	// Restore the value
	jdbcTemplate.update("UPDATE cwd_user_attributes SET attribute_value=1, lower_attribute_value=1 WHERE ID=212");
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
public int insertObj(final String sql,final Object[] paramArray, KeyHolder keyHolder, final String idCol) {

		JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
		logger.debug(sql);
		logger.debug(Arrays.toString(paramArray));
		Integer retStatus=jdbcTemplate.update(new PreparedStatementCreator() { 

			public PreparedStatement createPreparedStatement(Connection con) 
			throws SQLException { 
					String[] keyColNames=new String[1];
					keyColNames[0]=idCol;
					PreparedStatement ps=con.prepareStatement(sql,keyColNames); 
					if(paramArray!=null){
						int size=paramArray.length;
						for(int i=0;i<size;i++){
							ps.setObject(i+1, paramArray[i]);
						}
					}
		
					return ps; 
				} 
			}, keyHolder);	
		return retStatus;
	}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
@Test
public void testUploadDefaultWorkflow() throws Exception {
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
	try {
		// Delete the default project/workflow mapping, where type = '0'
		jdbcTemplate.update("update workflowschemeentity SET WORKFLOW=? WHERE ID=?", "jira", 10302);
		resource.upload(new ClassPathResource("csv/upload/nominal-simple.csv").getInputStream(), ENCODING, subscription,
				UploadMode.PREVIEW);
	} finally {
		jdbcTemplate.update("update workflowschemeentity SET WORKFLOW=? WHERE ID=?", "CSN", 10302);
	}

	final ImportStatus result = jiraResource.getTask(subscription);
	Assert.assertEquals(UploadMode.PREVIEW, result.getMode());
	Assert.assertFalse(result.isFailed());
	Assert.assertEquals(22, result.getStep());
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
@Test
public void testZUploadWithInsertNoScriptRunner() throws Exception {
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
	try {
		jdbcTemplate.update("update pluginversion SET pluginkey=? WHERE ID = ?", "N/A", 10170);

		startOperationalServer();

		this.subscription = getSubscription("gStack");
		resource.upload(new ClassPathResource("csv/upload/nominal-complete6.csv").getInputStream(), ENCODING, subscription,
				UploadMode.FULL);
		final ImportStatus result = jiraResource.getTask(subscription);
		Assert.assertEquals(1, result.getChanges().intValue());
		Assert.assertEquals(30, result.getStep());
		Assert.assertTrue(result.getCanSynchronizeJira());
		Assert.assertFalse(result.getScriptRunner());
		Assert.assertFalse(result.getSynchronizedJira());
	} finally {
		jdbcTemplate.update("update pluginversion SET pluginkey=? WHERE ID = ?", "com.onresolve.jira.groovy.groovyrunner", 10170);
	}
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
public void deleteActiveTasks(String processInstanceId) {
    Context.getCommandContext().getTaskEntityManager()
            .deleteTasksByProcessInstanceId(processInstanceId, "退回", false);

    JdbcTemplate jdbcTemplate = ApplicationContextHelper
            .getBean(JdbcTemplate.class);
    List<Map<String, Object>> list = jdbcTemplate
            .queryForList(
                    "select * from ACT_HI_ACTINST where proc_inst_id_=? and end_time_ is null",
                    processInstanceId);
    Date now = new Date();

    for (Map<String, Object> map : list) {
        Date startTime = (Date) map.get("start_time_");
        long duration = now.getTime() - startTime.getTime();
        jdbcTemplate
                .update("update ACT_HI_ACTINST set end_time_=?,duration_=? where id_=?",
                        now, duration, map.get("id_"));
    }
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
/**
 * Update the "pcounter" value for a given sequence project.
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param jira
 *            the JIRA project identifier.
 * @param issues
 *            the issues.
 */
private void reserveProjectCounter(final DataSource dataSource, final int jira, final List<JiraIssueRow> issues) {

	// Compute the maximal "issuenum" value
	int max = 0;
	for (final JiraIssueRow issueRow : issues) {
		if (issueRow.getIssueNum() > max) {
			max = issueRow.getIssueNum();
		}
	}

	// Update pcounter with the maximal issue number value
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	jdbcTemplate.update("UPDATE project SET pcounter = ? WHERE ID = ? AND pcounter <= ?", max + 1, jira, max);
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
public void doCancelPay(WalletPayTccMethodRequest param) {
	JdbcTemplate jdbcTemplate = util.getJdbcTemplate(Constant.APPID,WalletPayTccMethod.METHOD_NAME,param);
	int update = jdbcTemplate.update("update `wallet` set freeze_amount = freeze_amount - ? where user_id = ?;", 
			param.getPayAmount(),param.getUserId());
	if(update != 1){
		throw new RuntimeException("unknow Exception!");
	}
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
public WalletPayTccMethodResult doTryPay(WalletPayCascadeTccMethodRequest param) {
	JdbcTemplate jdbcTemplate = util.getJdbcTemplate(Constant.APPID,WalletPayTccMethod.METHOD_NAME,param);
	int update = jdbcTemplate.update("update `wallet` set freeze_amount = freeze_amount + ? where user_id = ? and (total_amount - freeze_amount) >= ?;", 
			param.getPayAmount(),param.getUserId(),param.getPayAmount());
	
	if(update != 1){
		throw new RuntimeException("can not find specific user id or have not enought money");
	}
	
	//start cascading transaction
	transaction.startEasyTrans(WalletPayCascadeTccMethod.METHOD_NAME, String.valueOf(System.currentTimeMillis()));
	//publish point message with an consumer cascading this transaction 
	OrderMessageForCascadingTest msg = new OrderMessageForCascadingTest();
	msg.setUserId(param.getUserId());
	msg.setAmount(param.getPayAmount());
	transaction.execute(msg);
	
	/**
	 * 
	 */
	ExpressDeliverAfterTransMethodRequest expressDeliverAfterTransMethodRequest = new ExpressDeliverAfterTransMethodRequest();
	expressDeliverAfterTransMethodRequest.setUserId(param.getUserId());
	expressDeliverAfterTransMethodRequest.setPayAmount(param.getPayAmount());
	transaction.execute(expressDeliverAfterTransMethodRequest);
	
	checkUtProgramedException();
	
	
	WalletPayTccMethodResult walletPayTccMethodResult = new WalletPayTccMethodResult();
	walletPayTccMethodResult.setFreezeAmount(param.getPayAmount());
	return walletPayTccMethodResult;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
public void doConfirmPay(WalletPayCascadeTccMethodRequest param) {
	JdbcTemplate jdbcTemplate = util.getJdbcTemplate(Constant.APPID,WalletPayTccMethod.METHOD_NAME,param);
	int update = jdbcTemplate.update("update `wallet` set freeze_amount = freeze_amount - ?, total_amount = total_amount - ? where user_id = ?;", 
			param.getPayAmount(),param.getPayAmount(),param.getUserId());
	
	if(update != 1){
		throw new RuntimeException("unknow Exception!");
	}
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
@Test
public void clearLoginFailedOtherUser() {
	final JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
	Assert.assertEquals("1", jdbcTemplate.queryForObject("SELECT attribute_value FROM cwd_user_attributes WHERE ID=212", String.class));
	try {
		jdbcTemplate.update("update pluginversion SET pluginversion=? WHERE ID = ?", "6.0.1", 10075);
		dao.clearLoginFailed(datasource, "alocquet");
	} finally {
		jdbcTemplate.update("update pluginversion SET pluginversion=? WHERE ID = ?", "4.4.1", 10075);
	}

	// Untouched counter
	Assert.assertEquals("1", jdbcTemplate.queryForObject("SELECT attribute_value FROM cwd_user_attributes WHERE ID=212", String.class));
}
 

import org.springframework.jdbc.core.JdbcTemplate; //导入方法依赖的package包/类
public int delObjByCondition(String tableName, String condition,Object[] paramArray) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	String sql = "delete from " + tableName + " where "+condition;
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	Integer retStatus=jdbcTemplate.update(sql,paramArray);
	return retStatus;
}