Java Code Examples for org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate#update()

@Override
public int add(Student student) {
	// String sql = "insert into student(sno,sname,ssex) values(?,?,?)";
	// Object[] args = { student.getSno(), student.getName(), student.getSex() };
	// int[] argTypes = { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR };
	// return this.jdbcTemplate.update(sql, args, argTypes);
	String sql = "insert into student(sno,sname,ssex) values(:sno,:name,:sex)";
	NamedParameterJdbcTemplate npjt = new NamedParameterJdbcTemplate(this.jdbcTemplate.getDataSource());
	return npjt.update(sql, new BeanPropertySqlParameterSource(student));
}
 

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

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

@DaoUpdateReturnValueCheck
private int doUpdate (NamedParameterJdbcTemplate jdbc, String q, Map <String, Object> packed) throws SQLException {
	show ("UPD", q, packed);

	Retry <Integer>	r = new Retry <Integer> ("update", this, jdbc) {
		@Override
		public void execute () throws SQLException {
			priv = jdbc.update (q, packed);
		}
	};
	if (retry (r)) {
		return r.priv;
	}
	throw failure (q, r.error);
}
 

/**
 * 支持miniDao语法操作的Update
 *
 * @param dbKey 数据源标识
 * @param sql   执行sql语句，sql支持minidao语法逻辑
 * @param data  sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static int updateByHash(final String dbKey, String sql, HashMap<String, Object> data) {
    int effectCount;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    //根据模板获取sql
    sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
    effectCount = namedParameterJdbcTemplate.update(sql, data);
    return effectCount;
}
 

/**
 * 支持miniDao语法操作的Update
 *
 * @param dbKey 数据源标识
 * @param sql   执行sql语句，sql支持minidao语法逻辑
 * @param data  sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static int updateByHash(final String dbKey, String sql, HashMap<String, Object> data) {
    int effectCount;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    //根据模板获取sql
    sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
    effectCount = namedParameterJdbcTemplate.update(sql, data);
    return effectCount;
}
 

/**
 * Alternative to RideRepositoryImpl#deleteRide(java.lang.Integer)
 */
public void deleteRideNamedParameter(Integer id) {
	final NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

	final Map<String, Object> paramsMap = new HashMap<>();
	paramsMap.put("id", id);

	namedTemplate.update("delete from RIDE where id = :id", paramsMap);
}
 

private static Object executeQuery(NamedParameterJdbcTemplate template, Map<String, Object> args, String query) {
    QueryBlank queryBlank = prepareQuery(query, args);
    query = queryBlank.getQuery();
    args = queryBlank.getArgs();
    if (isSelect(query)) {
        List<Object[]> list = template.query(query, args, (rs, rowNum) -> {
            return retrieveDataSet(rs);
        });
        return list.toArray();
    } else {
        MapSqlParameterSource paramSource = new MapSqlParameterSource(args);
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        template.update(query, paramSource, generatedKeyHolder);
        List<Map<String, Object>> keyList = generatedKeyHolder.getKeyList();
        if (keyList != null) {
            if (keyList.size() > 1)  {
                List<Object> rows = new ArrayList<>(keyList.size());
                for (Map<String, Object> row : keyList) {
                    rows.add(row.values().toArray());
                }
                return rows.toArray();
            } else if (keyList.size() == 1) {
                return keyList.get(0).values().toArray();
            }
        }
    }
    return null;
}
 

private Object executeWithJdbcTemplate(NativeQueryInfo info) {
    NamedParameterJdbcTemplate jdbcTemplate = ApplicationContextProvider.getApplicationContext().getBean(NamedParameterJdbcTemplate.class);

    Map<String, Object> parametroList = new HashMap<>();
    for (NativeQueryParameter parametro : info.getParameterList()) {
        if (parametro.getValue() != null && info.getSql().contains(":" + parametro.getName())) {
            parametroList.put(parametro.getName(), parametro.getValue());
        }
    }

    BeanPropertyRowMapper<?> beanPropertyRowMapper = new BeanPropertyRowMapper<>(info.getAliasToBean());
    if (info.getReturnType().getSimpleName().equals(Void.TYPE.getName())) {
        jdbcTemplate.update(info.getSql(), parametroList);
        return null;
    }

    if (info.isSingleResult()) {
        if (info.isJavaObject()) {
            return jdbcTemplate.queryForObject(info.getSql(), parametroList, info.getAliasToBean());
        }

        if (info.returnTypeIsOptional()) {
            return getOptionalReturn(() -> jdbcTemplate.queryForObject(info.getSql(), parametroList, beanPropertyRowMapper));
        }

        return jdbcTemplate.queryForObject(info.getSql(), parametroList, beanPropertyRowMapper);
    }

    if (info.isJavaObject()) {
        return jdbcTemplate.queryForList(info.getSql(), parametroList, info.getAliasToBean());
    }
    return jdbcTemplate.query(info.getSql(), parametroList, beanPropertyRowMapper);
}
 

/**
 * 支持miniDao语法操作的Update
 *
 * @param dbKey 数据源标识
 * @param sql   执行sql语句，sql支持minidao语法逻辑
 * @param data  sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static int updateByHash(final String dbKey, String sql, HashMap<String, Object> data) {
    int effectCount;
    JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
    //根据模板获取sql
    sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
    effectCount = namedParameterJdbcTemplate.update(sql, data);
    return effectCount;
}
 

@Override
public void saveSysLog(SysLog syslog) {
	StringBuffer sql = new StringBuffer("insert into sys_log ");
	sql.append("(id,username,operation,time,method,params,ip,create_time) ");
	sql.append("values(seq_sys_log.nextval,:username,:operation,:time,:method,");
	sql.append(":params,:ip,:createTime)");

	NamedParameterJdbcTemplate npjt = new NamedParameterJdbcTemplate(this.jdbcTemplate.getDataSource());
	npjt.update(sql.toString(), new BeanPropertySqlParameterSource(syslog));
}
 

/**
 * Inserts a record in the DB
 * @param jdbcTemplate the Spring JDBC template object
 * @param query the query to run
 * @param bean the bean with the data to insert
 * @return the ID of the record updated
 */
public static int runInsert(final NamedParameterJdbcTemplate jdbcTemplate, final String query, final Object bean) {
    SqlParameterSource beanParameters = new BeanPropertySqlParameterSource(bean);
    KeyHolder keyHolder = new GeneratedKeyHolder();

    jdbcTemplate.update(query, beanParameters, keyHolder);
    return keyHolder.getKey().intValue();
}
 

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

/**
 * 删除指定的表, 在删除期间disable外键的检查.
 */
public static void deleteTable(DataSource h2DataSource, String... tableNames) {
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(h2DataSource);

	template.update("SET REFERENTIAL_INTEGRITY FALSE",new HashMap<String, Object>());

	for (String tableName : tableNames) {
		template.update("DELETE FROM " + tableName,new HashMap<String, Object>());
	}

	template.update("SET REFERENTIAL_INTEGRITY TRUE",new HashMap<String, Object>());
}
 

/**
 * 支持miniDao语法操作的Update
 * @param dbKey 数据源标识
 * @param sql 执行sql语句，sql支持minidao语法逻辑
 * @param data sql语法中需要判断的数据及sql拼接注入中需要的数据
 * @return
 */
public static int updateByHash(final String dbKey, String sql, HashMap<String, Object> data){
	int effectCount = 0;
	JdbcTemplate jdbcTemplate = getJdbcTemplate(dbKey);
	//根据模板获取sql
	sql = FreemarkerParseFactory.parseTemplateContent(sql, data);
	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());
	effectCount = namedParameterJdbcTemplate.update(sql, data);
	return effectCount;
}
 

/**
 * Inserts a record in the DB
 * @param jdbcTemplate the Spring JDBC template object
 * @param query the query to run
 * @param bean the bean with the data to insert
 */
public static void runEmptyInsert(final NamedParameterJdbcTemplate jdbcTemplate, final String query, final Object bean) {
    SqlParameterSource beanParameters = new BeanPropertySqlParameterSource(bean);

    jdbcTemplate.update(query, beanParameters);
}