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

@Override
public int addStrs(String productName, String version, String component, String locale,
		Map<String, String> messages, JdbcTemplate jdbcTemplate) {
	// TODO Auto-generated method stub
	String sqlhead =  "update vip_msg set messages = ";   
	String sqltail= " where product = ? and version = ? and component= ? and locale = ?"; 
	int i=0;
	String updateStr =null;
	for(Entry<String, String> entry: messages.entrySet()) {
		if(i<1) { 
			updateStr = "jsonb_set(messages, '{"+entry.getKey()+"}', '\""+entry.getValue()+"\"'::jsonb,true)";
		}else {
			updateStr = "jsonb_set("+updateStr+", '{"+entry.getKey()+"}', '\""+entry.getValue()+"\"'::jsonb,true)";
		}
		
		i++;
		
	}


	String sql = sqlhead + updateStr + sqltail;
	logger.debug(((DruidDataSource)(jdbcTemplate.getDataSource())).getName());
	logger.debug(sql);
	logger.debug(String.join(", ", productName, version, component, locale));
	int result = jdbcTemplate.update(sql, productName, version, component,locale);
	return result;
}
 

public void insertTestBinariesForTika( String tableName) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(sourceDataSource);
    int docCount = 1000;
    byte[] bytes = null;
    try {
        bytes = IOUtils.toByteArray(getClass().getClassLoader().getResourceAsStream("tika/testdocs/docexample.doc"));
    } catch (IOException ex) {
        logger.fatal(ex);
    }

    String sql = "INSERT INTO  " + tableName
            + "( srcColumnFieldName"
            + ", srcTableName"
            + ", primaryKeyFieldName"
            + ", primaryKeyFieldValue"
            + ", updateTime"
            + ", sometext"
            + ") VALUES (?,?,?,?,?,?)";
    for (int ii = 0; ii < docCount; ii++) {
        jdbcTemplate.update(sql, "fictionalColumnFieldName", "fictionalTableName", "fictionalPrimaryKeyFieldName", ii, new Timestamp(today), bytes);
        today = TestUtils.nextDay();
    }
}
 

@Before
public void setup() throws Exception {
	if (!initialized) {
		registerApp(ApplicationType.task, "timestamp", "1.2.0.RELEASE");
		initialize();
		createJobExecution(JOB_NAME, BatchStatus.STARTED);
		createJobExecution(JOB_NAME + "_1", BatchStatus.STOPPED);


		jdbcTemplate = new JdbcTemplate(this.dataSource);
		jdbcTemplate.afterPropertiesSet();
		jdbcTemplate.update(
				"INSERT into task_deployment(id, object_version, task_deployment_id, task_definition_name, platform_name, created_on) " +
						"values (?,?,?,?,?,?)",
				1, 1, "2", JOB_NAME + "_1", "default", new Date());

		documentation.dontDocument(() -> this.mockMvc.perform(
				post("/tasks/definitions")
						.param("name", "DOCJOB_1")
						.param("definition", "timestamp --format='YYYY MM DD'"))
				.andExpect(status().isOk()));

		initialized = true;
	}
}
 

/**
 * Delete all rows from the specified tables.
 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
 * @param tableNames the names of the tables to delete from
 * @return the total number of rows deleted from all specified tables
 */
public static int deleteFromTables(JdbcTemplate jdbcTemplate, String... tableNames) {
	int totalRowCount = 0;
	for (String tableName : tableNames) {
		int rowCount = jdbcTemplate.update("DELETE FROM " + tableName);
		totalRowCount += rowCount;
		if (logger.isInfoEnabled()) {
			logger.info("Deleted " + rowCount + " rows from table " + tableName);
		}
	}
	return totalRowCount;
}
 

public int delObjByBizId(String tableName, Object bizId,String bizCol) {
	//String tableName=changeTableNameCase(otableName);
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	String sql = "delete from " + tableName + " where "+bizCol+"=?";
	Object[] paramArray=new Object[1];
	paramArray[0]=bizId;
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	Integer retStatus=jdbcTemplate.update(sql,paramArray);
	return retStatus;
}
 

public int delMetaBeanById(String tableName, String id) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	String sql = "delete from " + tableName + " where id=?";
	String[] paramArray=new String[1];
	paramArray[0]=id;
	logger.debug(sql);
	logger.debug(paramArray);
	Integer retStatus=jdbcTemplate.update(sql,paramArray);
	return retStatus;
}
 

/**
 * execute update, also supports specified data sources
 * 
 * @param sql
 * @param tableName
 * @return
 */
public static int getUpdateResultMap(String sql, String tableName, String dbConfigKey) {

    JdbcTemplate jdbcTemplate = getJdbcTemplate(tableName, dbConfigKey);
    int result = jdbcTemplate.update(sql);
    return result;

}
 

@Override
public int delStrBykey(String productName, String version, String component, String locale, String key,
		JdbcTemplate jdbcTemplate) {
	// TODO Auto-generated method stub
	
	logger.debug(((DruidDataSource)(jdbcTemplate.getDataSource())).getName());
	
	String sql=  String.format("update vip_msg  set messages = messages #- '{%s}'::text[] where product = ? and version = ? and component= ? and locale = ?",key);   
	logger.debug(sql);
	logger.debug(String.join(", ", productName, version, component, locale));
	 return  jdbcTemplate.update(sql, productName, version, component,locale);
	
}
 

protected static void clearData(JdbcTemplate tddlJTX, String sql, Object[] args) {
    if (args == null) {
        args = new Object[] {};
    }
    // 确保数据清除成功
    try {
        tddlJTX.update(sql, args);
    } catch (Exception e) {
        tddlJTX.update(sql, args);
    }
}
 

/**
 * 1. Storage service is ready . A seller add 100 storage to a sku: C00321
 *
 * @param args the input arguments
 */
public static void main(String[] args) {
    ClassPathXmlApplicationContext storageContext = new ClassPathXmlApplicationContext(
        new String[]{"spring/dubbo-storage-service.xml"});
    storageContext.getBean("service");
    JdbcTemplate storageJdbcTemplate = (JdbcTemplate) storageContext.getBean("jdbcTemplate");
    storageJdbcTemplate.update("delete from storage_tbl where commodity_code = 'C00321'");
    storageJdbcTemplate.update("insert into storage_tbl(commodity_code, count) values ('C00321', 100)");
    new ApplicationKeeper(storageContext).keep();
}
 

private void insertTestExecutionDataIntoRepo(JdbcTemplate template, long id, String taskName) {
	final String INSERT_STATEMENT = "INSERT INTO task_execution (task_execution_id, "
			+ "start_time, end_time, task_name, " + "exit_code,exit_message,last_updated) "
			+ "VALUES (?,?,?,?,?,?,?)";
	Object[] param = new Object[] { id, new Date(id), new Date(), taskName, 0, null, new Date() };
	template.update(INSERT_STATEMENT, param);
}
 

public int insertObj(String sql) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	logger.debug(sql);
	Integer retStatus=jdbcTemplate.update(sql);
	return retStatus;
}
 

public int delObjByBizId(String tableName, Object bizId,String bizCol) {
	//String tableName=changeTableNameCase(otableName);
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	String sql = "delete from " + tableName + " where "+bizCol+"=?";
	Object[] paramArray=new Object[1];
	paramArray[0]=bizId;
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	Integer retStatus=jdbcTemplate.update(sql,paramArray);
	return retStatus;
}
 

public void insertDataIntoBasicTable( String tableName,boolean includeText, int startId,int endId,boolean sameDay){
    if(!sameDay)today = TestUtils.nextDay();
    JdbcTemplate jdbcTemplate = new JdbcTemplate(sourceDataSource);
    String sql = "INSERT INTO  " + tableName
            + "( srcColumnFieldName"
            + ", srcTableName"
            + ", primaryKeyFieldName"
            + ", primaryKeyFieldValue"
            + ", updateTime"
            + ", someText"
            + ", anotherTime"
            + ") VALUES (?,?,?,?,?,?,?)";


    String biolarkTs = "";
    for(int i=0;i<biolarkText.length;i++){
        biolarkTs = biolarkTs +" " +biolarkText[i];
    }
    for (long i = startId; i <= endId; i++) {

        if(includeText) {
            jdbcTemplate.update(sql, "fictionalColumnFieldName", "fictionalTableName",
                    "fictionalPrimaryKeyFieldName", i, new Timestamp(today), biolarkTs, new Timestamp(today));
        }else{
            jdbcTemplate.update(sql, "fictionalColumnFieldName", "fictionalTableName",
                    "fictionalPrimaryKeyFieldName", i, new Timestamp(today), null, new Timestamp(today));
        }
    }
}
 

public void delete(Integer id) {
    queryDAO.deleteByDatabaseConnectionId(id);

    String sql = "DELETE FROM database_connections WHERE id = ?";

    jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.update(sql, new Object[] { id });
}
 

public int updateObjByCondition(String tableName, String condition,String setStr,Object[] paramArray) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	//String tableName=changeTableNameCase(otableName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	String timeName=getTimeName();
	if(autoOperTime){
		setStr="update_time="+timeName+","+setStr;
	}
	String sql = "update " + tableName +" set "+setStr+ " where "+condition;
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	Integer retStatus=jdbcTemplate.update(sql,paramArray);
	return retStatus;
}
 

public int delObjByCondition(String tableName, String condition,Object[] paramArray) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	//String tableName=changeTableNameCase(otableName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	String sql = "delete from " + tableName + " where "+condition;
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	Integer retStatus=jdbcTemplate.update(sql,paramArray);
	return retStatus;
}
 

@RequestMapping("/test")
@Transactional
public String index() {
    /*ReentrantReadWriteLock lock = new ReentrantReadWriteLock();
    WriteLock writeLock = lock.writeLock();
    writeLock.lock();
    try {
      Condition c = writeLock.newCondition();
      c.await(10, TimeUnit.MICROSECONDS);
    } catch (InterruptedException e) {
    } finally {
      writeLock.unlock();
    }*/

  /*try {
  Thread.sleep(1);
} catch (InterruptedException e1) {
}*/

  //Connection c = DataSourceUtils.getConnection(dataSource);

//try {
  //DataSource ds = new SingleConnectionDataSource(dataSource, c);
    JdbcTemplate template = new JdbcTemplate(dataSource);
      int id = idGenerator.getAndIncrement();

      template.update("INSERT INTO customer(id, ssn, name) VALUES (?, ?,?)", new Object[] {id, "1234", "ramesh"});

      template.query("select id, name from customer where id = ?", new Object[] {id}, new RowCallbackHandler() {
          @Override
          public void processRow(ResultSet rs) throws SQLException {
              //System.out.println(rs.getInt(1) + ":" + rs.getString(2));
          }
      });

      template.update("UPDATE CUSTOMER SET name = ? WHERE id = ?", new Object[] {"foo", id});

      return "Greetings from Spring Boot!";
//} finally {
//  DataSourceUtils.releaseConnection(c, dataSource);
//}
}
 

public List<Mutant> insertTestDataForDeidentification(String tableName1, String tableName2,
                                                      int mutationLevel,boolean useBigList){
    JdbcTemplate jdbcTemplate = new JdbcTemplate(sourceDataSource);

    File idFile;
    if(useBigList) {
        idFile = new File(getClass().getClassLoader().getResource("identifiers.csv").getFile());
    }else{
        idFile = new File(getClass().getClassLoader().getResource("identifiers_small.csv").getFile());
    }

    List<CSVRecord> records = null;
    try {
        records = CSVParser.parse(idFile, Charset.defaultCharset(), CSVFormat.DEFAULT).getRecords();
    } catch (IOException e) {
        logger.error(e);
    }

    String sql1 = "INSERT INTO  " + tableName1
            + "( primaryKeyFieldValue "
            + ",   NAME"
            + ", ADDRESS"
            + ", POSTCODE"
            + ", DATE_OF_BIRTH"
            + ") VALUES (?,?,?,?,?)";

    String sql2 = "INSERT INTO  " + tableName2
            + "( srcColumnFieldName"
            + ", srcTableName"
            + ", primaryKeyFieldName"
            + ", primaryKeyFieldValue"
            + ", updateTime"
            + ", someText"
            + ", anotherTime"
            + ") VALUES (?,?,?,?,?,?,?)";

    Iterator<CSVRecord> it = records.iterator();
    it.next();

    List<Mutant> mutants = new ArrayList<>();
    while(it.hasNext()){
        CSVRecord r = it.next();


        String[] stringToMutate = convertCsvRecordToStringArrayAddressOnly(r);
        Mutant mutant = stringMutatorService.generateMutantDocument(stringToMutate, mutationLevel);
        mutant.setDocumentid(Long.valueOf(r.get(0)));
        mutants.add(mutant);
        jdbcTemplate.update(sql1, Long.valueOf(r.get(0)),r.get(1),r.get(2),r.get(3), new Timestamp(today));
        jdbcTemplate.update(sql2, "fictionalColumnFieldName", "fictionalTableName",
                "fictionalPrimaryKeyFieldName", Long.valueOf(r.get(0)),
                new Timestamp(today),mutant.getFinalText(),
                new Timestamp(today));
        today = TestUtils.nextDay();
    }
    return mutants;
}
 

private int loadOne(TaskWriterContext context, T record, DbDialect dbDialect, LobCreator lobCreator, JdbcTemplate template) {
    return template.update(getSql(record, context), ps -> {
        fillPreparedStatement(ps, lobCreator, record, dbDialect, context);
    });
}