org.springframework.jdbc.core.ColumnMapRowMapper#org.springframework.jdbc.core.simple.SimpleJdbcInsert源码实例Demo

/**
 * 插入源初始化
 * 
 * @param tableName
 * @param paramJson
 * @return
 */
private SimpleJdbcInsert insertInit(String tableName, JSONObject paramJson) {
	// 1. 参数验证
	paramValidate(tableName, paramJson);
	
	// 2. 创建JdbcInsert实例
	SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
	simpleJdbcInsert.setTableName(tableName); // 设置表名
	simpleJdbcInsert.setGeneratedKeyName(DbConstant.PRIMARY_KEY);	// 设置主键名，添加成功后返回主键的值
	
	// 3. 设置ColumnNames
	List<String> keys = MapUtils.keyList(paramJson);
	List<String> columnNames = ListUtils.toList(getMetaData(tableName).getColumnNames());
	List<String> insertColumn = ListUtils.keepSameValue(keys, (List<String>) dialect.getWrapper().wrap(columnNames));
	simpleJdbcInsert.setColumnNames(insertColumn);
	
	// 4. 返回结果
	return simpleJdbcInsert;
}
 

/**
 * 向表中批量插入数据，主键默认为id时使用。
 * 
 * @param tableName 表名
 * @param paramJsons 参数
 */
@Transactional
public void insertBatch(String tableName, JSONObject[] paramJsons) {
	// 1. 参数验证
	paramValidate(tableName, paramJsons);
	
	// 2. 插入源初始化
	tableName = dialect.getWrapper().wrap(tableName);
	paramJsons = dialect.getWrapper().wrap(paramJsons);
	SimpleJdbcInsert simpleJdbcInsert = insertInit(tableName, paramJsons[0]);
	
	// 3. 执行
       int updateRowsNumber = simpleJdbcInsert.executeBatch(paramJsons).length;
       
       // 4. 确认插入条数
       if (updateRowsNumber != paramJsons.length) {
       	throw new DbException(ResultPrompt.INSERT_BATCH_ERROR);
       }
}
 

/**
 * Alternative to RideRepositoryImpl#createRide(com.pluralsight.model.Ride)
 */
public Ride createRideSimpleJDBC(Ride ride) {
	final SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate);

	insert.setGeneratedKeyName("id");

	final Map<String, Object> data = new HashMap<>();
	data.put("name", ride.getName());
	data.put("duration", ride.getDuration());

	final List<String> columns = new ArrayList<>();
	columns.add("name");
	columns.add("duration");

	insert.setTableName("ride");
	insert.setColumnNames(columns);

	final Number id = insert.executeAndReturnKey(data);
	return getRide(id);
}
 

public void migrate(Context context) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true));
    SimpleJdbcInsert insertOp = new SimpleJdbcInsert(jdbcTemplate).withTableName("m_prj_ticket_relation");

    List<Map<String, Object>> rows = jdbcTemplate.queryForList("SELECT * FROM m_tracker_related_bug");
    rows.forEach(row -> {
        Map<String, Object> parameters = new HashMap<>(6);
        parameters.put("ticketId", row.get("bugid"));
        parameters.put("ticketType", "Project-Bug");
        parameters.put("type", "Project-Bug");
        parameters.put("typeId", row.get("relatedid"));
        parameters.put("rel", row.get("relatetype"));
        parameters.put("comment", row.get("comment"));
        insertOp.execute(parameters);
    });
    jdbcTemplate.execute("DROP TABLE `m_tracker_related_bug`;");
}
 

/**
 * 向表中插入一条数据，主键默认为id时使用。
 * 
 * @param tableName 表名
 * @param paramJson 参数
 * @return 返回主键值
 */
@Transactional
public Long insert(String tableName, JSONObject paramJson) {
	// 1. 移除空对象
	MapUtils.removeEmpty(paramJson);
	
	// 2. 插入源初始化
	tableName = dialect.getWrapper().wrap(tableName);
	paramJson = dialect.getWrapper().wrap(paramJson);
	SimpleJdbcInsert simpleJdbcInsert = insertInit(tableName, paramJson);
	
	// 3. 执行
	return simpleJdbcInsert.executeAndReturnKey(paramJson).longValue();
}
 

/**
 * 向表中插入一条数据
 * 
 * @param tableName 表名
 * @param paramJson 参数
 */
@Transactional
public void insertNotReturn(String tableName, JSONObject paramJson) {
	// 1. 移除空对象
	MapUtils.removeEmpty(paramJson);
	
	// 2. 插入源初始化
	tableName = dialect.getWrapper().wrap(tableName);
	paramJson = dialect.getWrapper().wrap(paramJson);
	SimpleJdbcInsert simpleJdbcInsert = insertInit(tableName, paramJson);
	
	// 3. 执行
	simpleJdbcInsert.execute(paramJson);
}
 

/**
 * Creates the employee in the database and returns the id of the created employee.
 * The method uses SimpleJdbcInsert instead of JdbcTemplate.
 * @param employee the employee to be created
 * @return the id of the created employee
 */
public long simpleSave(Employee employee) {
  SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
          .withTableName("employees")
          .usingGeneratedKeyColumns("id");

  return simpleJdbcInsert.executeAndReturnKey(employee.toMap()).longValue();
}
 

public void insertTopicMessage(int newTopicsMessageCount, long topicNum, Instant startTime, long seqStart) {
    if (newTopicsMessageCount <= 0) {
        // no messages to create, abort and db logic
        return;
    }

    createTopic(topicNum);

    long nextSequenceNum = seqStart == -1 ? getNextAvailableSequenceNumber(topicNum) : seqStart;
    log.info("Inserting {} topic messages starting from sequence number {} and time {}", newTopicsMessageCount,
            nextSequenceNum, startTime);

    List<SqlParameterSource> parameterSources = new ArrayList<>();
    SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate.getDataSource())
            .withTableName("topic_message");

    for (int i = 1; i <= newTopicsMessageCount; i++) {
        long sequenceNum = nextSequenceNum + i;
        Instant consensusInstant = startTime.plusNanos(sequenceNum);
        TopicMessage topicMessage = TopicMessage.builder()
                .consensusTimestamp(consensusInstant)
                .sequenceNumber(sequenceNum)
                .message(BYTES)
                .runningHash(BYTES)
                .realmNum(0)
                .build();
        parameterSources.add(new BeanPropertySqlParameterSource(topicMessage));

        if (i % BATCH_SIZE == 0) {
            simpleJdbcInsert.executeBatch(parameterSources.toArray(new SqlParameterSource[] {}));
            parameterSources.clear();
        }
    }

    if (!parameterSources.isEmpty()) {
        simpleJdbcInsert.executeBatch(parameterSources.toArray(new SqlParameterSource[] {}));
    }

    log.debug("Successfully inserted {} topic messages", newTopicsMessageCount);
}
 

@Autowired
public JdbcPetRepositoryImpl(DataSource dataSource, OwnerRepository ownerRepository,
        VisitRepository visitRepository) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    this.insertPet = new SimpleJdbcInsert(dataSource).withTableName("pets").usingGeneratedKeyColumns("id");

    this.ownerRepository = ownerRepository;
    this.visitRepository = visitRepository;
}
 

/**
 * Inserts a spitter using SimpleJdbcInsert. 
 * Involves no direct SQL and is able to return the ID of the newly created Spitter.
 * @param spitter a Spitter to insert into the databse
 * @return the ID of the newly inserted Spitter
 */
private long insertSpitterAndReturnId(Spitter spitter) {
	SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("Spitter");
	jdbcInsert.setGeneratedKeyName("id");
	Map<String, Object> args = new HashMap<String, Object>();
	args.put("username", spitter.getUsername());
	args.put("password", spitter.getPassword());
	args.put("fullname", spitter.getFullName());
	args.put("email", spitter.getEmail());
	args.put("updateByEmail", spitter.isUpdateByEmail());
	long spitterId = jdbcInsert.executeAndReturnKey(args).longValue();
	return spitterId;
}
 

private long insertSpittleAndReturnId(Spittle spittle) {
		SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("Spittle");
		jdbcInsert.setGeneratedKeyName("id");
		Map<String, Object> args = new HashMap<String, Object>();
		args.put("spitter", spittle.getSpitter().getId());
		args.put("message", spittle.getMessage());
		args.put("postedTime", spittle.getPostedTime());
		long spittleId = jdbcInsert.executeAndReturnKey(args).longValue();
		return spittleId;
}
 

/**
 * Inserts a spitter using SimpleJdbcInsert. 
 * Involves no direct SQL and is able to return the ID of the newly created Spitter.
 * @param spitter a Spitter to insert into the databse
 * @return the ID of the newly inserted Spitter
 */
private long insertSpitterAndReturnId(Spitter spitter) {
	SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("Spitter");
	jdbcInsert.setGeneratedKeyName("id");
	Map<String, Object> args = new HashMap<String, Object>();
	args.put("username", spitter.getUsername());
	args.put("password", spitter.getPassword());
	args.put("fullname", spitter.getFullName());
	args.put("email", spitter.getEmail());
	args.put("updateByEmail", spitter.isUpdateByEmail());
	long spitterId = jdbcInsert.executeAndReturnKey(args).longValue();
	return spitterId;
}
 

private long insertSpittleAndReturnId(Spittle spittle) {
		SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("Spittle");
		jdbcInsert.setGeneratedKeyName("id");
		Map<String, Object> args = new HashMap<String, Object>();
		args.put("spitter", spittle.getSpitter().getId());
		args.put("message", spittle.getMessage());
		args.put("postedTime", spittle.getPostedTime());
		long spittleId = jdbcInsert.executeAndReturnKey(args).longValue();
		return spittleId;
}
 

@Autowired
public JdbcOrderRepository(JdbcTemplate jdbc) {
  this.orderInserter = new SimpleJdbcInsert(jdbc)
      .withTableName("Taco_Order")
      .usingGeneratedKeyColumns("id");

  this.orderTacoInserter = new SimpleJdbcInsert(jdbc)
      .withTableName("Taco_Order_Tacos");

  this.objectMapper = new ObjectMapper();
}
 

@Test
public void shouldNotLock() {
  new SimpleJdbcInsert(jdbcTemplate)
    .withTableName("locks")
    .usingGeneratedKeyColumns("id")
    .executeAndReturnKey(values("1", "def"));

  final var token = lock.acquire(Collections.singletonList("1"), "locks", 1000);
  assertThat(token).isNull();

  final var acquiredLockMap = jdbcTemplate.queryForObject("SELECT * FROM locks WHERE id = 1", new ColumnMapRowMapper());
  assertThat(acquiredLockMap).containsAllEntriesOf(values("1", "def"));
}
 

@Test
public void shouldRelease() {
  new SimpleJdbcInsert(jdbcTemplate)
    .withTableName("locks")
    .usingGeneratedKeyColumns("id")
    .executeAndReturnKey(values("1", "abc"));

  final var released = lock.release(Collections.singletonList("1"), "locks", "abc");
  assertThat(released).isTrue();
  assertThat(jdbcTemplate.queryForList("SELECT * FROM locks")).isNullOrEmpty();
}
 

@Test
public void shouldNotRelease() {
  new SimpleJdbcInsert(jdbcTemplate)
    .withTableName("locks")
    .usingGeneratedKeyColumns("id")
    .executeAndReturnKey(values("1", "def"));

  lock.release(Collections.singletonList("1"), "locks", "abc");

  final var acquiredLockMap = jdbcTemplate.queryForObject("SELECT * FROM locks WHERE id = 1", new ColumnMapRowMapper());
  assertThat(acquiredLockMap).containsAllEntriesOf(values("1", "def"));
}
 

@Autowired
public JdbcVisitRepositoryImpl(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);

    this.insertVisit = new SimpleJdbcInsert(dataSource)
        .withTableName("visits")
        .usingGeneratedKeyColumns("id");
}
 

@Autowired
public JdbcPetRepositoryImpl(DataSource dataSource, OwnerRepository ownerRepository, VisitRepository visitRepository) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    this.insertPet = new SimpleJdbcInsert(dataSource)
        .withTableName("pets")
        .usingGeneratedKeyColumns("id");

    this.ownerRepository = ownerRepository;
    this.visitRepository = visitRepository;
}
 

@Autowired
public JdbcVisitRepositoryImpl(DataSource dataSource) {
    this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    this.insertVisit = new SimpleJdbcInsert(dataSource)
        .withTableName("visits")
        .usingGeneratedKeyColumns("id");
}
 

@Autowired
public JdbcPetRepositoryImpl(DataSource dataSource, OwnerRepository ownerRepository, VisitRepository visitRepository) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    this.insertPet = new SimpleJdbcInsert(dataSource)
        .withTableName("pets")
        .usingGeneratedKeyColumns("id");

    this.ownerRepository = ownerRepository;
    this.visitRepository = visitRepository;
}
 

@SuppressWarnings("SpringJavaAutowiringInspection")
@Autowired
public JDBCDataRepositoryImpl(NamedParameterJdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
    this.insertIntoProject = new SimpleJdbcInsert((JdbcTemplate) jdbcTemplate.getJdbcOperations())
            .withTableName("project")
            .usingGeneratedKeyColumns("pid");
}
 

public JdbcAuditRepository(
    JdbcTemplate jdbcTemplate,
    ObjectMapper jsonMapper )
{
    this.jdbcTemplate = jdbcTemplate;
    this.jsonMapper = jsonMapper;

    this.auditInsert = new SimpleJdbcInsert( jdbcTemplate )
        .withTableName( "audit" )
        .usingGeneratedKeyColumns( "auditid" );
}
 

@Autowired
public void init(DataSource dataSource) {
	this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);

	this.insertOwner = new SimpleJdbcInsert(dataSource)
		.withTableName("owners")
		.usingGeneratedKeyColumns("id");
	this.insertPet = new SimpleJdbcInsert(dataSource)
		.withTableName("pets")
		.usingGeneratedKeyColumns("id");
	this.insertVisit = new SimpleJdbcInsert(dataSource)
		.withTableName("visits")
		.usingGeneratedKeyColumns("id");
}
 

@Autowired
public JdbcVisitRepositoryImpl(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);

    this.insertVisit = new SimpleJdbcInsert(dataSource)
            .withTableName("visits")
            .usingGeneratedKeyColumns("id");
}
 

@Autowired
public JdbcOwnerRepositoryImpl(DataSource dataSource, NamedParameterJdbcTemplate namedParameterJdbcTemplate,
                               VisitRepository visitRepository) {

    this.insertOwner = new SimpleJdbcInsert(dataSource)
            .withTableName("owners")
            .usingGeneratedKeyColumns("id");

    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    this.visitRepository = visitRepository;
}
 

@Autowired
public JdbcPetRepositoryImpl(DataSource dataSource, OwnerRepository ownerRepository, VisitRepository visitRepository) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    this.insertPet = new SimpleJdbcInsert(dataSource)
            .withTableName("pets")
            .usingGeneratedKeyColumns("id");

    this.ownerRepository = ownerRepository;
    this.visitRepository = visitRepository;
}
 

JobConfigurationDAO(final JdbcTemplate jdbcTemplate, final String tableName, final String schema) {
    this.jdbcTemplate = jdbcTemplate;
    this.tableName = tableName;
    this.simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
            .withSchemaName(schema)
            .withTableName(String.format(TABLE_NAME, tableName)).usingGeneratedKeyColumns(
                    JobConfigurationDomain.JOB_CONFIGURATION_ID);
    final JobConfigurationRowMapper jobConfigurationRowMapper = new JobConfigurationRowMapper();
    this.jobConfigurationJdbcWrapperRowMapper = new JobConfigurationJdbcWrapperRowMapper(jobConfigurationRowMapper);
}
 

JobConfigurationParameterDAO(final JdbcTemplate jdbcTemplate, final String tableName, final String schema) {
    this.jdbcTemplate = jdbcTemplate;
    this.tableName = tableName;
    this.simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
            .withSchemaName(schema)
            .withTableName(String.format(TABLE_NAME, tableName))
            .usingGeneratedKeyColumns(JobConfigurationParameterDomain.ID);
    this.dateFormat = new SimpleDateFormat(DomainParameterParser.DATE_FORMAT_WITH_TIMESTAMP);
}
 

private SchedulerConfigurationDAO(final JdbcTemplate jdbcTemplate,
                                  final String tableName,
                                  final SchedulerConfigurationValueDAO schedulerConfigurationValueDAO,
                                  final String schema) throws Exception {
    this.jdbcTemplate = jdbcTemplate;
    this.simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
            .withTableName(tableName)
            .withSchemaName(schema)
            .usingGeneratedKeyColumns(SchedulerConfigurationDomain.ID);
    this.tableName = tableName;
    this.schedulerConfigurationValueDAO = schedulerConfigurationValueDAO;
    this.rowMapper = new SchedulerConfigurationRowMapper();
    this.findAllPagingQueryProvider = this.getPagingQueryProvider(null);
}