Java KeyHolder类代码示例

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
@Override
public Object add(Object object) {
	SqlModel<Object> sqlModel = sqlBuilder.insertSelectiveSql(object);
	checkSqlModel(sqlModel);
	
	SqlParameterSource paramSource =  new BeanPropertySqlParameterSource(object);
	KeyHolder generatedKeyHolder =  new  GeneratedKeyHolder();
	namedPjdbcTemplate.update(sqlModel.getSql(), paramSource, generatedKeyHolder);
	Number num = generatedKeyHolder.getKey();
	
	String[] primaryKeys = sqlModel.getPrimaryKeys();
	if(primaryKeys != null && primaryKeys.length > 0){
		BeanWrapper beanWrapper = PropertyAccessorFactory.forBeanPropertyAccess(object);
		beanWrapper.setPropertyValue(primaryKeys[0], num);
	}
	
	return object;
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
@Test
public void testInsert() {
    GeneratedAlwaysRecord record = new GeneratedAlwaysRecord();
    record.setId(100);
    record.setFirstName("Bob");
    record.setLastName("Jones");
    
    InsertStatementProvider<GeneratedAlwaysRecord> insertStatement = insert(record)
            .into(generatedAlways)
            .map(id).toProperty("id")
            .map(firstName).toProperty("firstName")
            .map(lastName).toProperty("lastName")
            .build()
            .render(RenderingStrategy.SPRING_NAMED_PARAMETER);
    
    SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(insertStatement.getRecord());
    KeyHolder keyHolder = new GeneratedKeyHolder();
    
    int rows = template.update(insertStatement.getInsertStatement(), parameterSource, keyHolder);
    String generatedKey = (String) keyHolder.getKeys().get("FULL_NAME");
    
    assertThat(rows).isEqualTo(1);
    assertThat(generatedKey).isEqualTo("Bob Jones");
    
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
private Integer saveOrderRecord(JdbcTemplate jdbcTemplate, final int userId, final long money) {
	
	final String INSERT_SQL = "INSERT INTO `order` (`order_id`, `user_id`, `money`, `create_time`) VALUES (NULL, ?, ?, ?);";
	KeyHolder keyHolder = new GeneratedKeyHolder();
	jdbcTemplate.update(
	    new PreparedStatementCreator() {
	    	@Override
	        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
	            PreparedStatement ps =
	                connection.prepareStatement(INSERT_SQL, new String[] {"id"});
	            ps.setInt(1, userId);
	            ps.setLong(2, money);
	            ps.setDate(3, new Date(System.currentTimeMillis()));
	            return ps;
	        }
	    },
	    keyHolder);
	
	return keyHolder.getKey().intValue();
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
public static int insertLogLineagePattern(JsonNode logLineagePattern) throws Exception {
  Map<String, Object> params = new HashMap<>();
  params.put("regex", JsonUtil.getJsonValue(logLineagePattern, "regex", String.class));
  params.put("databaseType", JsonUtil.getJsonValue(logLineagePattern, "database_type", String.class));
  params.put("operationType", JsonUtil.getJsonValue(logLineagePattern, "operation_type", String.class));
  params.put("patternType", JsonUtil.getJsonValue(logLineagePattern, "pattern_type", String.class, null));
  params.put("sourceTargetType", JsonUtil.getJsonValue(logLineagePattern, "source_target_type", String.class, null));
  params.put("databaseNameIndex", JsonUtil.getJsonValue(logLineagePattern, "database_name_index", Integer.class, null));
  params.put("datasetIndex", JsonUtil.getJsonValue(logLineagePattern, "dataset_index", Integer.class, null));
  params.put("recordCountIndex", JsonUtil.getJsonValue(logLineagePattern, "record_count_index", Integer.class, null));
  params.put("recordByteIndex", JsonUtil.getJsonValue(logLineagePattern, "record_byte_index", Integer.class, null));
  params.put("insertCountIndex", JsonUtil.getJsonValue(logLineagePattern, "insert_count_index", Integer.class, null));
  params.put("insertByteIndex", JsonUtil.getJsonValue(logLineagePattern, "insert_byte_index", Integer.class, null));
  params.put("deleteCountIndex", JsonUtil.getJsonValue(logLineagePattern, "delete_count_index", Integer.class, null));
  params.put("deleteByteIndex", JsonUtil.getJsonValue(logLineagePattern, "delete_byte_index", Integer.class, null));
  params.put("updateCountIndex", JsonUtil.getJsonValue(logLineagePattern, "update_count_index", Integer.class, null));
  params.put("updateByteIndex", JsonUtil.getJsonValue(logLineagePattern, "update_byte_index", Integer.class, null));
  params.put("comments", JsonUtil.getJsonValue(logLineagePattern, "comments", String.class, null));

  KeyHolder kh = JdbcUtil.insertRow(INSERT_LOG_LINEAGE_PATTERN, params);
  return kh.getKey().intValue();
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
public static int insertJobProperty(EtlJobName etlJobName, Integer refId, String propertyName, String propertyValue,
  boolean isEncrypted)
  throws Exception {
  Map<String, Object> params = new HashMap<>();
  params.put("etlJobName", etlJobName.toString());
  params.put("refId", refId);
  params.put("refIdType", etlJobName.getRefIdType().toString());
  params.put("propertyName", propertyName);
  if (isEncrypted) {
    params.put("propertyValue", encrypt(propertyValue));
    params.put("isEncrypted", "Y");
  } else {
    params.put("propertyValue", propertyValue);
    params.put("isEncrypted", "N");
  }
  KeyHolder kh = JdbcUtil.insertRow(INSERT_JOB_PROPERTY, params);
  return kh.getKey().intValue();
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
public static int insertWherehowsProperty(String propertyName, String propertyValue, boolean isEncrypted,
  String groupName)
  throws Exception {
  Map<String, Object> params = new HashMap<>();
  params.put("propertyName", propertyName);
  params.put("propertyValue", propertyValue);
  if (isEncrypted) {
    params.put("propertyValue", encrypt(propertyValue));
    params.put("isEncrypted", "Y");
  } else {
    params.put("propertyValue", propertyValue);
    params.put("isEncrypted", "N");
  }
  params.put("groupName", groupName);
  KeyHolder kh = JdbcUtil.insertRow(INSERT_WHEREHOWS_PROPERTY, params);
  return kh.getKey().intValue();
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
public void insertSelective(Object record){
    try {
        Entity entity=getEntity(record.getClass());
        final Set<Entity.Column> insertColumn = getNotNullColumn(record, entity);
        final StringBuilder sql = new StringBuilder();
        sql.append(SqlHelper.insertIntoTable(entity.getTableName()));
        sql.append(SqlHelper.insertColumns(record, insertColumn));
        System.out.println(sql.toString());
        KeyHolder holder=new GeneratedKeyHolder();
        jdbcTemplate.update(sql.toString(), getParameterMap(record, insertColumn,false), holder, new String[]{entity.getPrimaryKey().getName()});
        int primaryKeyValue = holder.getKey().intValue();
        entity.getPrimaryKey().getField().set(record, primaryKeyValue);
    } catch (Exception e) {
        e.printStackTrace();
    }
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
@Transactional
public User create(String tenantSchema, final User user) {
	final String sql = MessageFormat.format(TENANT_SQL_TEMPLATE, tenantSchema, "insert into user(name,password) values(?,?)");

	KeyHolder holder = new GeneratedKeyHolder();

	jdbcTemplate.update(new PreparedStatementCreator() {

		@Override
		public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, user.getName());
			ps.setString(2, user.getPassword());
			return ps;
		}
	}, holder);

	int newUserId = holder.getKey().intValue();
	user.setId(newUserId);
	return user;
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
public long getCurrentGithubId() {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    String sql = "SELECT stat_id FROM github_stats WHERE stat_date = current_date()";
    long statId = -1;
    try {
        statId = jdbcTemplate.queryForObject(sql, Long.class);
    } catch (EmptyResultDataAccessException e) {

        jdbcTemplate.update(
                new PreparedStatementCreator() {
                    String INSERT_SQL = "INSERT INTO github_stats (stat_date) VALUES (current_date())";
                    public PreparedStatement createPreparedStatement(Connection cn) throws SQLException {
                        PreparedStatement ps = cn.prepareStatement(INSERT_SQL, new String[] {"stat_id"});
                        return ps;
                    }
                },
                keyHolder);
        statId = keyHolder.getKey().longValue();

    }
    logger.info("Current GitHub Stats ID: " + statId);
    return statId;
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
@Test
public void githubStatRecordKeyReturned() throws Exception {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    long statId = -1L;
    try {
        statId = jdbcTemplate.queryForObject("SELECT stat_id FROM github_stats WHERE stat_date = '2010-10-10'", Long.class);
    } catch (EmptyResultDataAccessException e) {

        jdbcTemplate.update(
                new PreparedStatementCreator() {
                    String INSERT_SQL = "INSERT INTO github_stats (stat_date) VALUES ('2010-10-10')";

                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[]{"stat_id"});
                        return ps;
                    }
                },
                keyHolder);
        statId = keyHolder.getKey().longValue();
    }

    assertThat(statId).isGreaterThan(0);
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
public Idea insert(Idea idea, User currentUser) {
	String sql = "insert into idea (title, description, user_id) values (?,?,?)";
	KeyHolder keyHolder = new GeneratedKeyHolder();
	jdbcTemplate.update(new PreparedStatementCreator() {
		@Override
		public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			PreparedStatement ps = connection.prepareStatement(sql, new String[] { "id" });
			ps.setString(1, idea.getTitle());
			ps.setString(2, idea.getDescription());
			ps.setLong(3, currentUser.getId());
			return ps;
		}
	}, keyHolder);
	idea.setId(keyHolder.getKey().longValue());
	return idea;
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
@Override
@Transactional(readOnly=false)
public long saveGraph(String graphName) {
	Object[] args = new Object[1];
	args[0] = graphName;
	
	Map<String, Object>  params = new HashMap<String, Object>(); 
	params.put("name", graphName);
	MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource(params);
	KeyHolder keyHolder = new GeneratedKeyHolder();

	getNamedParameterJdbcTemplate().update("INSERT INTO " + schema + "waygraphs (name) VALUES (:name)", 
			sqlParameterSource, keyHolder, new String[] {"id"});

	return Long.class.cast(keyHolder.getKey());
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的package包/类
@Override
public int update(
		String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, String[] keyColumnNames)
		throws DataAccessException {

	ParsedSql parsedSql = getParsedSql(sql);
	String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
	Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
	List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
	PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
	if (keyColumnNames != null) {
		pscf.setGeneratedKeysColumnNames(keyColumnNames);
	}
	else {
		pscf.setReturnGeneratedKeys(true);
	}
	return getJdbcOperations().update(pscf.newPreparedStatementCreator(params), generatedKeyHolder);
}
 

import org.springframework.jdbc.support.KeyHolder; //导入依赖的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.support.KeyHolder; //导入依赖的package包/类
public Long insertAndGetKey(final String sql, final Object... args) {
    final String mSql = initSql(sql);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    getJdbcTemplate().update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(mSql, Statement.RETURN_GENERATED_KEYS);
            if (args != null && args.length > 0) {
                for (int index = 0; index < args.length; index++) {
                    ps.setObject((index + 1), args[index]);
                }
            }
            return ps;
        }
    }, keyHolder);
    Long generatedId = keyHolder.getKey().longValue();
    return generatedId;
}