org.springframework.jdbc.core.JdbcTemplate Java Examples

public int insertMetaBeanById(String tableName, MicroMetaBean microMetaBean) {
	//JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
	JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
	final MicroMetaBean insertBean=microMetaBean;
	String timeName=getTimeName();
	String sql = "insert into " + tableName +"(id,meta_content,meta_key,meta_name,meta_type,remark,create_time,update_time) values(?,?,?,?,?,?,"+timeName+","+timeName+") ";
	List paramList=new ArrayList();
	paramList.add(insertBean.getId());
	paramList.add(insertBean.getMeta_content());
	paramList.add(insertBean.getMeta_key());
	paramList.add(insertBean.getMeta_name());
	paramList.add(insertBean.getMeta_type());
	paramList.add(insertBean.getRemark());
	logger.debug(sql);
	logger.debug(paramList.toArray());
	Integer retStatus=jdbcTemplate.update(sql,paramList.toArray());

	return retStatus;
}
 

@Before
public void setup() {
	this.properties = new HashMap<>();
	this.properties.put("spring.datasource.url", DATASOURCE_URL);
	this.properties.put("spring.datasource.username", DATASOURCE_USER_NAME);
	this.properties.put("spring.datasource.password", DATASOURCE_USER_PASSWORD);
	this.properties.put("spring.datasource.driverClassName",
			DATASOURCE_DRIVER_CLASS_NAME);
	this.properties.put("spring.application.name", TASK_NAME);

	JdbcTemplate template = new JdbcTemplate(this.dataSource);
	template.execute("DROP ALL OBJECTS");

	DataSourceInitializer initializer = new DataSourceInitializer();

	initializer.setDataSource(this.dataSource);
	ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
	databasePopulator.addScript(
			new ClassPathResource("/org/springframework/cloud/task/schema-h2.sql"));
	initializer.setDatabasePopulator(databasePopulator);

	initializer.afterPropertiesSet();
}
 

@EtCps(cancelMethod="reverseEntry", idempotentType=BusinessProvider.IDENPOTENT_TYPE_FRAMEWORK,cfgClass=AccountingRequestCfg.class)
public AccountingResponse accounting(AccountingRequest param) {
	JdbcTemplate jdbcTemplate = getJdbcTemplate(param);
	
	TransactionId trxId = MetaDataFilter.getMetaData(EasytransConstant.CallHeadKeys.PARENT_TRX_ID_KEY);
	
	int update = jdbcTemplate.update("INSERT INTO `accounting` (`accounting_id`, `p_app_id`, `p_bus_code`, `p_trx_id`, `user_id`, `amount`, `create_time`) VALUES (NULL, ?, ?, ?, ?, ?, ?);",
			trxId.getAppId(),
			trxId.getBusCode(),
			trxId.getTrxId(),
			param.getUserId(),
			param.getAmount(),
			new Date());
	
	if(update != 1){
		throw new RuntimeException("unkonw Exception!");
	}
	return new AccountingResponse();
}
 

@Test
public void spring_with_parent() throws Exception {
  final MockSpan parent = mockTracer.buildSpan("parent").start();
  try (Scope ignored = mockTracer.activateSpan(parent)) {
    BasicDataSource dataSource = getDataSource(false);

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

    dataSource.close();
  }
  parent.finish();

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

  checkSameTrace(spans);
  checkNoEmptyTags(spans);
}
 

public void printDataSourceInfo(JdbcTemplate jdbcTemplate) throws SQLException {

        DataSource dataSource = jdbcTemplate.getDataSource();

        Connection connection;
        if (dataSource != null) {
            connection = dataSource.getConnection();
        } else {
            log.error("获取 DataSource 失败");
            return;
        }

        if (connection != null) {
            log.info("DB URL: {}", connection.getMetaData().getURL());
        } else {
            log.error("获取 Connection 失败");
        }
    }
 

public static final boolean dropSequenceIfExists(String sequenceName, final DataSource dataSource) {
	if(checkDbVendorIsOracle(dataSource)) {
		final JdbcTemplate template = new JdbcTemplate(dataSource);
		try {
	    	int foundSequences = template.queryForObject("SELECT COUNT(*) FROM all_sequences WHERE sequence_name = ?", Integer.class, sequenceName.toUpperCase());
	    	if (foundSequences > 0) {
	    		template.execute("DROP SEQUENCE " + sequenceName);
	    		return true;
	    	} else {
	    		return false;
	    	}
		} catch (Exception e) {
			return false;
		}
	} else {
		return false;
	}
}
 

public static void main(String[] args) {
    Options options = new Options();
    options.addOption("t", "to", true, "Providers xml to migrate to.");
    CommandLineParser parser = new DefaultParser();

    CommandLine commandLine = null;
    try {
        commandLine = parser.parse(options, args);
    } catch (ParseException e) {
        log.error("Unable to parse command line.", e);

        new HelpFormatter().printHelp("persistence-toolkit [args]", options);

        System.exit(PARSE_EXCEPTION);
    }

    NiFiRegistryProperties fromProperties = NiFiRegistry.initializeProperties(NiFiRegistry.getMasterKeyProvider());

    DataSource dataSource = new DataSourceFactory(fromProperties).getDataSource();
    DatabaseMetadataService fromMetadataService = new DatabaseMetadataService(new JdbcTemplate(dataSource));
    FlowPersistenceProvider fromPersistenceProvider = createFlowPersistenceProvider(fromProperties, dataSource);
    FlowPersistenceProvider toPersistenceProvider = createFlowPersistenceProvider(createToProperties(commandLine, fromProperties), dataSource);

    new FlowPersistenceProviderMigrator().doMigrate(fromMetadataService, fromPersistenceProvider, toPersistenceProvider);
}
 

protected void doApply(List<Record> records) {
    Map<List<String>, List<Record>> buckets = MigrateMap.makeComputingMap(new Function<List<String>, List<Record>>() {

        public List<Record> apply(List<String> names) {
            return Lists.newArrayList();
        }
    });

    // 根据目标库的不同，划分为多个bucket
    for (Record record : records) {
        buckets.get(Arrays.asList(record.getSchemaName(), record.getTableName())).add(record);
    }

    JdbcTemplate jdbcTemplate = new JdbcTemplate(context.getTargetDs());
    for (final List<Record> batchRecords : buckets.values()) {
        TableSqlUnit sqlUnit = getSqlUnit(batchRecords.get(0));
        if (context.isBatchApply()) {
            applierByBatch(jdbcTemplate, batchRecords, sqlUnit);
        } else {
            applyOneByOne(jdbcTemplate, batchRecords, sqlUnit);
        }
    }
}
 

private ScheduledPartitionParams getDocmanParams(Timestamp jobStartTimeStamp, boolean inclusiveOfStart) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(sourceDataSource);
    String sql = MessageFormat.format("SELECT MAX({0}) AS max_id, MIN({0}) AS min_id, MAX({1}) AS max_time_stamp " +
                    ",MIN({1}) AS min_time_stamp FROM {2} ",
            column,timeStamp,table);

    Timestamp jobEndTimeStamp = getEndTimeStamp(jobStartTimeStamp);
    if(inclusiveOfStart) {
        sql = getDocmanStartTimeInclusiveSqlString(sql, jobStartTimeStamp, jobEndTimeStamp);
    }else if(!inclusiveOfStart){
        sql = getDocmanStartTimeExclusiveSqlString(sql, jobStartTimeStamp, jobEndTimeStamp);
    }else{
        throw new RuntimeException("cannot determine parameters");
    }
    logger.info ("This docman job SQL: " + sql);
    return (ScheduledPartitionParams) jdbcTemplate.queryForObject(
            sql, new PartitionParamsRowMapper());
}
 

@Test
public void test_不设i() {
    JdbcTemplate jt = new JdbcTemplate(createGroupDataSource("ds0:rw, ds1:r, ds2:r, ds3:r"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds1", "select 1 from dual"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(2);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds2", "select 1 from dual"));
}
 

public List<Map<String, Object>> queryLimitObjJoinByCondition(String sql,int limit) {

		String tempType=calcuDbType();
		if(tempType!=null && tempType.equals("mysql")){
		//if(dbType!=null && dbType.equals("mysql")){
			sql=sql+" limit "+limit;
		}else{
			sql="select * from ("+sql+") where rownum <="+limit;
		}	
		JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
		logger.debug(sql);
		List<Map<String, Object>> retList0 = jdbcTemplate.queryForList(sql);
		//add 201807 ning
		//List<Map<String, Object>> retList=changeOutKeyCase4List(retList0);	
		
		//add 201902 ning
		List<Map<String, Object>> retList=ignoreKeyCase((List)retList0);
		
		return retList;
	}
 

public void save ( String opUrl, Association association )
{
	cleanupExpired ( ) ;
	
	try
	{
		JdbcTemplate jdbcTemplate = getJdbcTemplate ( ) ;

		int cnt = jdbcTemplate.update ( _sqlInsert,
										new Object[]
											{
											 	opUrl,
												association.getHandle ( ),
												association.getType ( ),
												association.getMacKey ( ) == null ? null :
												    new String (
																Base64.encodeBase64 ( association.getMacKey ( ).getEncoded ( ) ) ),
												association.getExpiry ( ) } ) ;
	}
	catch ( Exception e )
	{
		_log.error ( "Error saving association to table: " + _tableName, e ) ;
	}
}
 

@Override
public List<Employee> getAll() {
	String query = "select id, name, role from Employee";
	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	List<Employee> empList = new ArrayList<Employee>();

	List<Map<String,Object>> empRows = jdbcTemplate.queryForList(query);
	
	for(Map<String,Object> empRow : empRows){
		Employee emp = new Employee();
		emp.setId(Integer.parseInt(String.valueOf(empRow.get("id"))));
		emp.setName(String.valueOf(empRow.get("name")));
		emp.setRole(String.valueOf(empRow.get("role")));
		empList.add(emp);
	}
	return empList;
}
 

DatabaseCreator(String url, String username, String password) {
    String databaseUrlPattern = getDatabaseUrlPattern(url);
    Pattern jdbcBaseUrlWithDbNamePattern = Pattern.compile(databaseUrlPattern);
    Matcher matcher = jdbcBaseUrlWithDbNamePattern.matcher(url);

    if (!matcher.matches()) {
        throw new IllegalArgumentException(url + " does not match " + databaseUrlPattern);
    }

    String jdbcBaseUrl = matcher.group("jdbcBaseUrl");
    String databaseName = matcher.group("databaseName");
    databaseExistsQuery = String.format("SELECT count(*) FROM sys.databases WHERE name='%s'", databaseName);
    createDatabaseQuery = String.format("CREATE DATABASE %s", databaseName);
    this.template = new JdbcTemplate(
            new SimpleDriverDataSource(getDriver(jdbcBaseUrl), jdbcBaseUrl, username, password));
}
 

@Override
public String findByDocId(String productName, String version, String component, String locale,
		JdbcTemplate jdbcTemplate) {
	// TODO Auto-generated method stub
	String sql = "select v.messages::text from vip_msg v where v.product = ? and v.version = ? and v.component= ? and v.locale = ?";

	logger.debug(((DruidDataSource) (jdbcTemplate.getDataSource())).getName());
	logger.debug(sql);
	String[] params = { productName, version, component, locale };
	logger.debug(String.join(", ", params));
	String resultjson = null;

	try {
		resultjson = jdbcTemplate.queryForObject(sql, params, String.class);

	} catch (EmptyResultDataAccessException empty) {
		
		logger.error(empty.getMessage(), empty);
	}

	if (resultjson != null) {
		resultjson = "{ \"component\" : \"" + component + "\", \"messages\" : " + resultjson + ", \"locale\" : \""
				+ locale + "\" }";
	}
	return resultjson;
}
 

@Test
public void parameterPropagation() {
	SqlOperation operation = new SqlOperation() {};
	DataSource ds = new DriverManagerDataSource();
	operation.setDataSource(ds);
	operation.setFetchSize(10);
	operation.setMaxRows(20);
	JdbcTemplate jt = operation.getJdbcTemplate();
	assertEquals(ds, jt.getDataSource());
	assertEquals(10, jt.getFetchSize());
	assertEquals(20, jt.getMaxRows());
}
 

@Test
public void testRemoveDataSourceAndFallbackDataSourceThenReAddNewDataSource() throws Exception {
    DataSource dataSource = applicationContext.getBean("dataSource", DataSource.class);
    assertThat(dataSource, notNullValue());
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    List<Map<String, Object>> result;
    result = jdbcTemplate.queryForList(SELECT_DB);
    assertThat((String)result.get(0).get("DATABASE()"), is("A"));
    result = jdbcTemplate.queryForList(SELECT_DB);
    assertThat((String)result.get(0).get("DATABASE()"), is("B"));

    zkConfigClient.delete().deletingChildrenIfNeeded().forPath("/database/mydb/a");
    Thread.sleep(500);

    result = jdbcTemplate.queryForList(SELECT_DB);
    assertThat((String) result.get(0).get("DATABASE()"), is("C"));
    result = jdbcTemplate.queryForList(SELECT_DB);
    assertThat((String) result.get(0).get("DATABASE()"), is("B"));

    String dConfig = "{\n" +
            "    \"driverClassName\" : \"org.h2.Driver\",\n" +
            "    \"jdbcUrl\" : \"jdbc:h2:mem:d;MODE=MySQL;DB_CLOSE_DELAY=-1\"\n"+
            "}";
    zkConfigClient.create().creatingParentsIfNeeded().forPath("/database/mydb/a", dConfig.getBytes());
    Thread.sleep(500);

    result = jdbcTemplate.queryForList(SELECT_DB);
    assertThat((String) result.get(0).get("DATABASE()"), is("D"));
    result = jdbcTemplate.queryForList(SELECT_DB);
    assertThat((String) result.get(0).get("DATABASE()"), is("B"));
}
 

/**
 * hive metadata read JDBC template. Query timeout is set to control long running read queries.
 *
 * @param connectorContext connector config.
 * @param hiveDataSource hive data source
 * @return hive JDBC Template
 */
@Bean
public JdbcTemplate hiveReadJdbcTemplate(
    final ConnectorContext connectorContext,
    @Qualifier("hiveDataSource") final DataSource hiveDataSource) {
    final JdbcTemplate result = new JdbcTemplate(hiveDataSource);
    result.setQueryTimeout(getDataStoreReadTimeout(connectorContext) / 1000);
    return result;
}
 

@BeforeEach
void before() {
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  jdbcTemplate.execute("DELETE FROM TASK");
  jdbcTemplate.execute("DELETE FROM WORKBASKET");
  jdbcTemplate.execute("DELETE FROM CUSTOMDB.TEST");
}
 

/**
 * 以流方式获得blog,image等大数据
 * 
 * @param id
 *            字段主键
 * @param tableName
 *            表名
 * @param ColumnName
 *            字段名
 * @param jdbcTemplate
 */
public static String getBlob(String id, String tableName, final String columnName, JdbcTemplate jdbcTemplate) {
	String ls_sql = "select " + columnName + " from " + tableName + " where id='" + id + "'";

	// 查询并获得输入流
	jdbcTemplate.query(ls_sql, new RowCallbackHandler() {
		
		public void processRow(ResultSet rs) throws SQLException {
			inStream = rs.getBinaryStream(columnName);
		}
	});

	// 读取流数据并转换成16进制字符串
	if (inStream != null) {
		StringBuffer readInBuffer = new StringBuffer();
		readInBuffer.append("0x");
		byte[] b = new byte[4096];
		try {

			for (; (inStream.read(b)) != -1;) {
				readInBuffer.append(byte2HexStr(b));
			}
		} catch (IOException e) {

			e.printStackTrace();
		}
		String ls_return = readInBuffer.toString().trim();
		if ("0x".equals(ls_return)) {
			ls_return = ls_return + "00";
		}
		return ls_return;
	} else {
		return "0x00";
	}
}
 

@Override
public StorageBasedLockProvider getLockProvider() {
    return new JdbcTemplateLockProvider(JdbcTemplateLockProvider.Configuration
        .builder()
        .withJdbcTemplate(new JdbcTemplate(getDatasource()))
        .usingDbTime()
        .build()
    );
}
 

/**
 * get execute result from database
 * @param filterChain
 * @param reqest
 * @return
 */
public IdempotentPo getIdempotentPo(EasyTransFilterChain filterChain, Map<String,Object> header, EasyTransRequest<?, ?> reqest){
	BusinessIdentifer businessType = ReflectUtil.getBusinessIdentifer(reqest.getClass());
	Object trxIdObj = header.get(EasytransConstant.CallHeadKeys.PARENT_TRX_ID_KEY);
	TransactionId transactionId = (TransactionId) trxIdObj;
	Integer callSeq = Integer.parseInt(header.get(EasytransConstant.CallHeadKeys.CALL_SEQ).toString());
	JdbcTemplate jdbcTemplate = getJdbcTemplate(filterChain, reqest);
	 List<IdempotentPo> listQuery = jdbcTemplate.query(
			selectSql, 
			new Object[]{
					stringCodecer.findId(APP_ID, transactionId.getAppId()),
					stringCodecer.findId(BUSINESS_CODE,transactionId.getBusCode()),
					transactionId.getTrxId(),
					stringCodecer.findId(APP_ID, businessType.appId()),
					stringCodecer.findId(BUSINESS_CODE,businessType.busCode()),
					callSeq,
					stringCodecer.findId(APP_ID,appId)},
			beanPropertyRowMapper
			);
	 
	 if(listQuery.size() == 1){
		 return listQuery.get(0);
	 }else if (listQuery.size() == 0){
		 return null;
	 }else{
		 throw new RuntimeException("Unkonw Error!" + listQuery);
	 }
}
 

@Override
@Before
public void setUp() throws Exception {
    new JdbcTemplate(dataSource).execute("CREATE TABLE COM_AUDIT_TRAIL ( "
            + "AUD_USER      VARCHAR(100)  NOT NULL, "
            + "AUD_CLIENT_IP VARCHAR(15)    NOT NULL, "
            + "AUD_SERVER_IP VARCHAR(15)    NOT NULL, "
            + "AUD_RESOURCE  VARCHAR(100)  NOT NULL, "
            + "AUD_ACTION    VARCHAR(100)  NOT NULL, "
            + "APPLIC_CD     VARCHAR(5)    NOT NULL, "
            + "AUD_DATE      TIMESTAMP      NOT NULL)");
}
 

@Before
public void setup() {
	this.properties = new HashMap<>();
	this.properties.put("spring.datasource.url", DATASOURCE_URL);
	this.properties.put("spring.datasource.username", DATASOURCE_USER_NAME);
	this.properties.put("spring.datasource.password", DATASOURCE_USER_PASSWORD);
	this.properties.put("spring.datasource.driverClassName",
			DATASOURCE_DRIVER_CLASS_NAME);
	this.properties.put("spring.application.name", TASK_NAME);
	this.properties.put("spring.cloud.task.initialize-enabled", "false");

	JdbcTemplate template = new JdbcTemplate(this.dataSource);
	template.execute("DROP TABLE IF EXISTS TASK_TASK_BATCH");
	template.execute("DROP TABLE IF EXISTS TASK_SEQ");
	template.execute("DROP TABLE IF EXISTS TASK_EXECUTION_PARAMS");
	template.execute("DROP TABLE IF EXISTS TASK_EXECUTION");
	template.execute("DROP TABLE IF EXISTS TASK_LOCK");
	template.execute("DROP TABLE IF EXISTS BATCH_STEP_EXECUTION_SEQ");
	template.execute("DROP TABLE IF EXISTS BATCH_STEP_EXECUTION_CONTEXT");
	template.execute("DROP TABLE IF EXISTS BATCH_STEP_EXECUTION");
	template.execute("DROP TABLE IF EXISTS BATCH_JOB_SEQ");
	template.execute("DROP TABLE IF EXISTS BATCH_JOB_EXECUTION_SEQ");
	template.execute("DROP TABLE IF EXISTS BATCH_JOB_EXECUTION_PARAMS");
	template.execute("DROP TABLE IF EXISTS BATCH_JOB_EXECUTION_CONTEXT");
	template.execute("DROP TABLE IF EXISTS BATCH_JOB_EXECUTION");
	template.execute("DROP TABLE IF EXISTS BATCH_JOB_INSTANCE");
	template.execute("DROP SEQUENCE IF EXISTS TASK_SEQ");

	DataSourceInitializer initializer = new DataSourceInitializer();

	initializer.setDataSource(this.dataSource);
	ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
	databasePopulator.addScript(
			new ClassPathResource("/org/springframework/cloud/task/schema-h2.sql"));
	initializer.setDatabasePopulator(databasePopulator);
	initializer.afterPropertiesSet();
}
 

/**
 * Selects all the encrypted xml documents from krns_maint_doc_t, decrypts them, runs the rules to upgrade them,
 * encrypt them and update krns_maint_doc_t with the upgraded xml.
 *
 * @param settingsMap - the settings
 * @throws Exception
 */
public void runFileConversion(HashMap settingsMap, final String runMode, final String fromRange,
        final String toRange, final boolean hasRangeParameters) throws Exception {

    final EncryptionService encryptService = new EncryptionService((String) settingsMap.get("encryption.key"));

    String docSQL = "SELECT DOC_HDR_ID, DOC_CNTNT FROM krns_maint_doc_t ";

    // If user entered range add the sql parameters and filter results because DOC_HDR_ID is a varchar field.
    if (hasRangeParameters) {
        docSQL = docSQL.concat(" WHERE DOC_HDR_ID >= '" + fromRange + "' AND DOC_HDR_ID <= '" + toRange + "'");
    }
    System.out.println("SQL to run:"  + docSQL);

    jdbcTemplate = new JdbcTemplate(getDataSource(settingsMap));
    jdbcTemplate.query(docSQL, new RowCallbackHandler() {

        public void processRow(ResultSet rs) throws SQLException {
            // Check that all docId's is in range
            if (hasRangeParameters) {
                int docId = Integer.parseInt(rs.getString(1));
                if (docId >= Integer.parseInt(fromRange) && docId <= Integer.parseInt(toRange)) {
                    processDocumentRow(rs.getString(1), rs.getString(2), encryptService, runMode);
                }
            } else {
                processDocumentRow(rs.getString(1), rs.getString(2), encryptService, runMode);
            }
        }
    });

    System.out.println(totalDocs + " maintenance documents upgraded.");

}
 

@Test
public void createAndDropTable()
{
  logon(ADMIN);
  final Table table = new Table("t_test") //
  .addAttribute(new TableAttribute("name", TableAttributeType.VARCHAR, 5).setPrimaryKey(true))//
  .addAttribute(new TableAttribute("counter", TableAttributeType.INT)) //
  .addAttribute(new TableAttribute("money", TableAttributeType.DECIMAL, 8, 2).setNullable(false)) //
  .addAttribute(new TableAttribute("address_fk", TableAttributeType.INT).setForeignTable("t_address").setForeignAttribute("pk"));
  final StringBuffer buf = new StringBuffer();
  final MyDatabaseUpdateDao databaseUpdateDao = myDatabaseUpdater.getDatabaseUpdateDao();
  databaseUpdateDao.buildCreateTableStatement(buf, table);
  assertEquals("CREATE TABLE t_test (\n" //
      + "  name VARCHAR(5),\n" //
      + "  counter INT,\n" //
      + "  money DECIMAL(8, 2) NOT NULL,\n" //
      + "  address_fk INT,\n" //
      + "  PRIMARY KEY (name),\n" //
      + "  FOREIGN KEY (address_fk) REFERENCES t_address(pk)\n" //
      + ");\n", buf.toString());
  assertTrue(databaseUpdateDao.createTable(table));
  assertTrue(databaseUpdateDao.doesTableExist("t_test"));
  assertTrue(databaseUpdateDao.dropTable("t_test"));
  assertTrue(databaseUpdateDao.dropTable("t_test"));
  assertTrue(databaseUpdateDao.createTable(table));
  final JdbcTemplate jdbc = new JdbcTemplate(dataSource);
  jdbc.execute("INSERT INTO t_test (name, counter, money) VALUES('test', 5, 5.12);");
  assertFalse("Data base is not empty!", databaseUpdateDao.dropTable("t_test"));
  jdbc.execute("DELETE FROM t_test;");
}
 

@AfterClass
public static void tearDown() {
	JdbcTemplate template = new JdbcTemplate(applicationContext.getBean(DataSource.class));
	template.afterPropertiesSet();
	final String TASK_EXECUTION_FORMAT = "DELETE FROM task_execution WHERE task_execution_id = %d";
	final String TASK_BATCH_FORMAT = "DELETE FROM task_task_batch WHERE task_execution_id = %d";

	for (Long id : taskExecutionIds) {
		template.execute(String.format(TASK_BATCH_FORMAT, id));
		template.execute(String.format(TASK_EXECUTION_FORMAT, id));
	}
}
 

@Test
public void testFactoryBeanLifecycle() throws Exception {
	EmbeddedDatabaseFactoryBean bean = new EmbeddedDatabaseFactoryBean();
	ResourceDatabasePopulator populator = new ResourceDatabasePopulator(resource("db-schema.sql"),
		resource("db-test-data.sql"));
	bean.setDatabasePopulator(populator);
	bean.afterPropertiesSet();
	DataSource ds = bean.getObject();
	JdbcTemplate template = new JdbcTemplate(ds);
	assertEquals("Keith", template.queryForObject("select NAME from T_TEST", String.class));
	bean.destroy();
}
 

public List<MicroMetaBean> queryMetaBeanByCondition(String tableName, String condition,Object[] paramArray,int[] typeArray) {
	List<MicroMetaBean> retBeanList=new ArrayList();
	/*		JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
	.getDbSource(dbName);*/
	JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
	String sql = "select * from " + tableName + " where "+condition;
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	List<Map<String, Object>> retList = jdbcTemplate.queryForList(sql,paramArray,typeArray);
	if(retList==null){
		return retBeanList;
	}
	
	for(Map<String,Object> rowMap:retList){
		MicroMetaBean metaBean = new MicroMetaBean();
		metaBean.setId((String) rowMap.get("id"));
		metaBean.setMeta_content((String) rowMap.get("meta_content"));
		metaBean.setMeta_key((String) rowMap.get("meta_key"));
		metaBean.setMeta_name((String) rowMap.get("meta_name"));
		metaBean.setMeta_type((String) rowMap.get("meta_type"));
		metaBean.setRemark((String) rowMap.get("remark"));
		metaBean.setCreate_time((Date) rowMap.get("create_time"));
		metaBean.setUpdate_time((Date) rowMap.get("update_time"));
		retBeanList.add(metaBean);
	}
	return retBeanList;
}
 

@Test
public void testBatchUpdateWithEmptyMap() throws Exception {
	@SuppressWarnings("unchecked")
	final Map<String, Integer>[] ids = new Map[0];
	namedParameterTemplate = new NamedParameterJdbcTemplate(new JdbcTemplate(dataSource, false));

	int[] actualRowsAffected = namedParameterTemplate.batchUpdate(
			"UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = :id", ids);
	assertTrue("executed 0 updates", actualRowsAffected.length == 0);
}