org.springframework.jdbc.core.simple.SimpleJdbcInsert Java Examples

/**
 * 向表中批量插入数据，主键默认为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`;");
}
 

/**
 * 插入源初始化
 * 
 * @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;
}
 

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

public JdbcSchedulerExecutionRepository(final JdbcTemplate jdbcTemplate,
                                        final ServerSchedulerJdbcConfigurationProperties properties) throws Exception {
    this.jdbcTemplate = jdbcTemplate;
    this.simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
            .withSchemaName(properties.getDatabaseSchema())
            .withTableName(properties.getExecutionTable())
            .usingGeneratedKeyColumns(SchedulerExecutionDomain.ID);
    this.tableName = properties.getExecutionTable();
    this.rowMapper = new SchedulerExecutionRowMapper();
    this.byStatePagingQueryProvider = this.getPagingQueryProvider("S.state = ?");
    this.findAllPagingQueryProvider = this.getPagingQueryProvider(null);
}
 

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

private void enableLock(String lockKey) {
	SimpleJdbcInsert taskLockInsert = new SimpleJdbcInsert(this.dataSource)
			.withTableName("TASK_LOCK");
	Map<String, Object> taskLockParams = new HashMap<>();
	taskLockParams.put("LOCK_KEY",
			UUID.nameUUIDFromBytes(lockKey.getBytes()).toString());
	taskLockParams.put("REGION", "DEFAULT");
	taskLockParams.put("CLIENT_ID", "aClientID");
	taskLockParams.put("CREATED_DATE", new Date());
	taskLockInsert.execute(taskLockParams);
}
 

private static void insertConstructor(JdbcTemplate jdbc) {
    separator("insertConstructor");
    SimpleJdbcInsert insertActor = new SimpleJdbcInsert(jdbc).withSchemaName("region").withTableName("jc_region").usingGeneratedKeyColumns("region_id");

    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("region_name", "new region name");
    Number number = insertActor.executeAndReturnKey(parameters);
    log.debug("Inserted region id: {}", number.longValue());
}
 

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

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

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

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 void setDataSource(final DataSource dataSource) {
    jdbcTemplate = new JdbcTemplate(dataSource);
    final CustomSQLErrorCodeTranslator customSQLErrorCodeTranslator = new CustomSQLErrorCodeTranslator();
    jdbcTemplate.setExceptionTranslator(customSQLErrorCodeTranslator);

    namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    simpleJdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("EMPLOYEE");
    
    // Commented as the database is H2, change the database and create procedure READ_EMPLOYEE before calling getEmployeeUsingSimpleJdbcCall 
    //simpleJdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("READ_EMPLOYEE");
}
 

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

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

/**
 * 向表中插入一条数据，主键默认为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();
}
 

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

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

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

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