Java Code Examples for org.springframework.jdbc.core.JdbcTemplate#batchUpdate()

private static void testDiffError(JdbcTemplate jdbcTemplate) {
  String[] sqls = {
      "delete from test_0.types_bak where id = 2125",
      "delete from test_0.types_bak where id = 2122",
      "insert into `test_0`.`types_bak` (`double`,`varchar`,`char`,`tinyint`,`id`,`text`,`decimal`,`bigint`,`timestamp`) values (0.6055158,'D5v','k',26,2125,'/>$Kf',19265911.19,1366022492355224397,'2017-12-01 22:30:24.0')",
      "insert into `test_0`.`types_bak` (`double`,`varchar`,`char`,`tinyint`,`id`,`text`,`decimal`,`bigint`,`timestamp`) values (0.6055158,'D5v','k',26,2125,'/>$Kf',19265911.19,1366022492355224397,'2017-12-01 22:30:24.0')",
      "insert into `test_0`.`types_bak` (`double`,`varchar`,`char`,`tinyint`,`id`,`text`,`decimal`,`bigint`,`timestamp`) values (0.47148514,'v[e|','6P{N(hb=8C6!t5oAfLv2',161,2122,'Qria3&&V',19265911.19,3128612873388751949,'2005-06-07 08:46:12.0')",
      "insert into `test_0`.`not_exists` (`double`) VALUES (1)",
      };
  try {
    jdbcTemplate.batchUpdate(sqls);
  } catch (DataAccessException e) {
    int[] updateCounts = ((BatchUpdateException) e.getCause()).getUpdateCounts();
    System.out.println(Arrays.toString(updateCounts));
    e.printStackTrace();
  }
}
 

public void insertBatch1(final List<Employee> employees){

		jdbcTemplate = new JdbcTemplate(dataSource);
		  String sql = "INSERT INTO EMPLOYEE " +
			"(ID, NAME, AGE) VALUES (?, ?, ?)";
		 
		  jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
		 
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Employee employee = employees.get(i);
				ps.setLong(1, employee.getId());
				ps.setString(2, employee.getName());
				ps.setInt(3, employee.getAge() );
			}
		 
			public int getBatchSize() {
				return employees.size();
			}
		  });
		}
 

private void insert(JdbcTemplate jdbcTemplate, ConfigMeta meta, List<String> ips) {
    String sql = "INSERT IGNORE INTO file_delete(group_id, data_id, profile, ip) VALUES(?, ?, ?, INET_ATON(?))";
    List<Object[]> params = Lists.newLinkedList();
    for (String ip : ips) {
        Object[] param = {
                meta.getGroup(),
                meta.getDataId(),
                meta.getProfile(),
                ip
        };
        params.add(param);
    }
    jdbcTemplate.batchUpdate(sql, params);
}
 

public <T> int batchUpdate(String sql, List<T> entities, Function<T, Object[]> function) {
    int result = 0;
    List<Object[]> paramList = Lists.newLinkedList();
    for (T entity : entities) {
        Object[] params = function.apply(entity);
        paramList.add(params);
    }
    for (JdbcTemplate jdbcTemplate : jdbcTemplates.values()) {
        int[] resultArray = jdbcTemplate.batchUpdate(sql, paramList);
        for (int count : resultArray) {
            result += count;
        }
    }
    return result;
}
 

private void cleanAndSetUp() {
	wholeJdbcTemplate.batchUpdate(new String[] {
			"Create Table If Not Exists `order` (  `order_id` int(11) NOT NULL AUTO_INCREMENT,  `user_id` int(11) NOT NULL,  `money` bigint(20) NOT NULL,  `create_time` datetime NOT NULL,  PRIMARY KEY (`order_id`)) DEFAULT CHARSET=utf8",
			"TRUNCATE `order`",
			"Create Table If Not Exists `wallet` (  `user_id` int(11) NOT NULL,  `total_amount` bigint(20) NOT NULL,  `freeze_amount` bigint(20) NOT NULL,  PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8",
			"TRUNCATE `wallet`",
			"Create Table If Not Exists  `accounting` (  `accounting_id` int(11) NOT NULL AUTO_INCREMENT,  `p_app_id` varchar(32) NOT NULL,  `p_bus_code` varchar(128) NOT NULL,  `p_trx_id` varchar(64) NOT NULL,  `user_id` int(11) NOT NULL,  `amount` bigint(20) NOT NULL,  `create_time` datetime NOT NULL,  PRIMARY KEY (`accounting_id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8",
			"TRUNCATE `accounting`",
			"Create Table If Not Exists `point` (  `user_id` int(11) NOT NULL,  `point` bigint(20) NOT NULL,  PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8",
			"TRUNCATE `point`",
			"Create Table If Not Exists `express` (  `p_app_id` varchar(32) NOT NULL,  `p_bus_code` varchar(128) NOT NULL,  `p_trx_id` varchar(64) NOT NULL,  `user_id` int(11) NOT NULL,  PRIMARY KEY (`p_app_id`,`p_bus_code`,`p_trx_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8",
			"TRUNCATE `express`",
			"CREATE TABLE if not exists `coupon` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `coupon` int(11) NOT NULL, PRIMARY KEY (`user_id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8",
			"TRUNCATE `coupon`",
			"TRUNCATE `executed_trans`", "TRUNCATE `idempotent`",
			"INSERT INTO `wallet` (`user_id`, `total_amount`, `freeze_amount`) VALUES ('1', '10000', '0')",
			"INSERT INTO `point` (`user_id`, `point`) VALUES ('1', '0')",
			"INSERT INTO `coupon` (`user_id`, `coupon`) VALUES ('1', '10000');",
			
               "TRUNCATE `fescar_lock`",
               "TRUNCATE `undo_log`"
			});

	if(dbForLog != null || suiteDatabase != null){
	    
	    DataSource dataSource = null;
	    if(dbForLog != null) {
	        dataSource = dbForLog.getDataSource();
	    } else {
	        dataSource = suiteDatabase.getDataSource();
	    }
	    
		JdbcTemplate transLogJdbcTemplate = new JdbcTemplate(dataSource);
		transLogJdbcTemplate
		.batchUpdate(new String[] { "TRUNCATE `trans_log_unfinished`", "TRUNCATE `trans_log_detail`", });
	}

}
 

public int[] updateObjBatch(String[] sql) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	logger.debug(sql);
	int[] retStatus=jdbcTemplate.batchUpdate(sql);
	return retStatus;
}
 

public int[] updateObjBatch(String sql,List<Object[]> paramList) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	logger.debug(sql);
	logger.debug(paramList.toArray());
	int[] retStatus=jdbcTemplate.batchUpdate(sql,paramList);
	return retStatus;
}
 

@Test
public void batch() throws SQLException {
  BasicDataSource dataSource = getDataSource(false);

  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  jdbcTemplate.execute("CREATE TABLE batch (id INTEGER)");

  final List<Integer> ids = Arrays.asList(1, 2, 3, 4, 5);
  jdbcTemplate.batchUpdate("INSERT INTO batch (id) VALUES (?)",
      new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
          preparedStatement.setInt(1, ids.get(i));
        }

        @Override
        public int getBatchSize() {
          return ids.size();
        }
      }
  );

  dataSource.close();

  List<MockSpan> spans = mockTracer.finishedSpans();
  assertEquals(DB_CONNECTION_SPAN_COUNT + 2, spans.size());

  for (MockSpan span : spans.subList(DB_CONNECTION_SPAN_COUNT, spans.size() - 1)) {
    assertEquals(Tags.SPAN_KIND_CLIENT, span.tags().get(Tags.SPAN_KIND.getKey()));
    assertEquals(JdbcTracingUtils.COMPONENT_NAME, span.tags().get(Tags.COMPONENT.getKey()));
    assertThat(span.tags().get(Tags.DB_STATEMENT.getKey()).toString()).isNotEmpty();
    assertEquals("h2", span.tags().get(Tags.DB_TYPE.getKey()));
    assertEquals("spring", span.tags().get(Tags.DB_INSTANCE.getKey()));
    assertEquals("localhost:-1", span.tags().get("peer.address"));
    assertEquals(0, span.generatedErrors().size());
  }

  assertNull(mockTracer.activeSpan());
  checkNoEmptyTags(spans);
}
 

private void updateTmTextUnits(JdbcTemplate jdbcTemplate, final List<TmTextUnit> tmTextUnits) {
   int[] updated = jdbcTemplate.batchUpdate("update tm_text_unit set word_count = ? where id = ?",
        new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setInt(1, wordCountService.getEnglishWordCount(tmTextUnits.get(i).content));
                    ps.setLong(2, tmTextUnits.get(i).id);
                }

                public int getBatchSize() {
                    return tmTextUnits.size();
                }
            });
   
   logger.info("TmTextUnit update count: {}", updated.length);
}
 

public int[] updateObjBatch(String[] sql) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	logger.debug(sql);
	int[] retStatus=jdbcTemplate.batchUpdate(sql);
	return retStatus;
}
 

public int[] updateObjBatch(String sql,List<Object[]> paramList) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	logger.debug(sql);
	logger.debug(paramList.toArray());
	int[] retStatus=jdbcTemplate.batchUpdate(sql,paramList);
	return retStatus;
}
 

@Override
     public int[] call() throws Exception {
     	JdbcTemplate jdbcTemplate = createJdbcTemplate(shard.getTargetDataSource(), ShardJdbcTemplate.this);
         String interceptedSql = shardDataSource.getSqlInterceptor().intercept(sql, shard.getTableContext());
         
         return jdbcTemplate.batchUpdate(interceptedSql, new BatchPreparedStatementSetter() {
	
	@Override
	public void setValues(PreparedStatement ps, int statementIndex) throws SQLException {
		Object[] args = argsList.get(statementIndex);
		
		for (int i = 0; i < args.length; i++) {
			Object arg = args[i];
			if (arg instanceof SqlParameterValue) {
				SqlParameterValue paramValue = (SqlParameterValue) arg;
				StatementCreatorUtils.setParameterValue(ps, i + 1, paramValue, paramValue.getValue());
			}
			else {
				StatementCreatorUtils.setParameterValue(ps, i + 1, SqlTypeValue.TYPE_UNKNOWN, arg);
			}
		}
	}
	
	@Override
	public int getBatchSize() {
		return argsList.size();
	}
});
     }
 

public void batchUpdate(String sql, BatchPreparedStatementSetter params) throws DataAccessException {
    for (JdbcTemplate jdbcTemplate : jdbcTemplates.values()) {
        jdbcTemplate.batchUpdate(sql, params);
    }
}
 

public void batchUpdate(String sql, List<Object[]> batchArgs) {
    for (JdbcTemplate jdbcTemplate : jdbcTemplates.values()) {
        jdbcTemplate.batchUpdate(sql, batchArgs);
    }
}
 

public void insertBatch2(final String sql){
	jdbcTemplate = new JdbcTemplate(dataSource); 
	jdbcTemplate.batchUpdate(new String[]{sql});
}