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

public boolean revokeAccess(RdsRevokeAccessQuery rdsRevokeAccessQuery) throws Exception{
    String user = rdsRevokeAccessQuery.getUser();
    String address = rdsRevokeAccessQuery.getAddress();
    String userRole = getGkUserName(user, rdsRevokeAccessQuery.getRole());

    try{
        JdbcTemplate conn = connect(address, gkUserCredentialsProvider.getGatekeeperSecret(rdsRevokeAccessQuery));
        logger.info("Deleting User " + user + " if they already exist on DB " + address);
        conn.execute("GRANT USAGE ON *.* to " + userRole);
        conn.execute("DROP USER '" + userRole + "'");

        logger.info("Deleted existing " + userRole + " on database " + address);
        return true;
    }catch (Exception ex){
        logger.error("An exception was thrown Trying to revoke access to " + userRole + " from " + address, ex);
        return false;
    }
}
 

@Before
public void setup() {

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

@Test
public void spring_with_ignored_statement() throws Exception {
  BasicDataSource dataSource = getDataSource(false, Arrays.asList(
      "CREATE TABLE ignored (id INTEGER, TEST VARCHAR)",
      "INSERT INTO ignored (id, \\\"TEST\\\") VALUES (1, 'value')"
  ));

  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  jdbcTemplate.execute("CREATE TABLE ignored (id INTEGER, TEST VARCHAR)");
  jdbcTemplate.execute("INSERT INTO ignored (id, \"TEST\") VALUES (1, 'value')");
  jdbcTemplate.execute("CREATE TABLE not_ignored (id INTEGER)");

  dataSource.close();

  List<MockSpan> finishedSpans = mockTracer.finishedSpans();
  assertEquals(DB_CONNECTION_SPAN_COUNT + 1, finishedSpans.size());
  checkNoEmptyTags(finishedSpans);
}
 

private void updateCurrentChunk() {
    if (logger.isDebugEnabled()) {
        logger.debug("Updating HILO chunk...");
    }

    long t = System.currentTimeMillis();
    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(conn, true));

        jdbcTemplate.update(SQL_LOCK);
        currentChunk = jdbcTemplate.queryForObject(SQL_SELECT, Integer.class);
        if (logger.isDebugEnabled())
            logger.debug("Current chunk: " + currentChunk);
        jdbcTemplate.execute(SQL_UPDATE);
        jdbcTemplate.execute("commit");

        if (logger.isDebugEnabled()) {
            logger.debug("Updating HILO chunk done in " + (System.currentTimeMillis() - t) + " ms");
        }
        currentId = 0;
    } catch (SQLException e) {
        logger.error("Unable to update current chunk", e);
        throw new IllegalStateException("Unable to update current chunk");
    }
}
 

public static void verifyTestEnvironment(DataSource dataSource) {
    if (dataSource == null) {
        Assert.fail("Could not locate the data source.");
    }
    JdbcTemplate template = new JdbcTemplate(dataSource);
    template.execute(new ConnectionCallback() {
        public Object doInConnection(Connection connection) throws SQLException {
            ResultSet resultSet = connection.getMetaData().getTables(null, null, TEST_TABLE_NAME, null);
            if (!resultSet.next()) {
                LOG.error("No table named '"+TEST_TABLE_NAME+"' was found in the configured database.  " +
                        "You are attempting to run tests against a non-test database!!!");
                LOG.error("The test environment will not start up properly!!!");
                Assert.fail("No table named '"+TEST_TABLE_NAME+"' was found in the configured database.  " +
                		"You are attempting to run tests against a non-test database!!!");
            }
            return null;
        }
    });
}
 

public OrderCreateTable(CamelContext camelContext) {
    DataSource ds = camelContext.getRegistry().lookupByNameAndType("myDataSource", DataSource.class);
    JdbcTemplate jdbc = new JdbcTemplate(ds);

    try {
        jdbc.execute("drop table riders_order");
    } catch (Exception e) {
        // ignore as the table may not already exists
    }
    jdbc.execute("create table riders_order "
        + "( customer_id varchar(10), ref_no varchar(10), part_id varchar(10), amount varchar(10) )");
}
 

@Test
void shouldHonorTimezone() {
    TimeZone timezone = TimeZone.getTimeZone("America/Los_Angeles");

    Instant lockUntil = Instant.parse("2020-04-10T17:30:00Z");
    ClockProvider.setClock(Clock.fixed(lockUntil.minusSeconds(10), timezone.toZoneId()));

    TimeZone originalTimezone = TimeZone.getDefault();


    DataSource datasource = getDatasource();

    TimeZone.setDefault(timezone);

    try {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
        jdbcTemplate.execute("CREATE TABLE shedlock_tz(name VARCHAR(64), lock_until TIMESTAMP WITH TIME ZONE, locked_at TIMESTAMP WITH TIME ZONE, locked_by  VARCHAR(255), PRIMARY KEY (name))");

        JdbcTemplateLockProvider provider = new JdbcTemplateLockProvider(builder()
            .withJdbcTemplate(new JdbcTemplate(datasource))
            .withTableName("shedlock_tz")
            .withTimeZone(timezone)
            .build());


        provider.lock(new LockConfiguration("timezone_test", lockUntil));
        new JdbcTemplate(datasource).query("SELECT * FROM shedlock_tz where name='timezone_test'", rs -> {
            Timestamp timestamp = rs.getTimestamp("lock_until");
            assertThat(timestamp.getTimezoneOffset()).isEqualTo(7 * 60);
            assertThat(timestamp.toInstant()).isEqualTo(lockUntil);
        });
    } finally {
        TimeZone.setDefault(originalTimezone);
    }
}
 

private void testConnection(DataSource dataSource) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String version = (String) jdbcTemplate.execute(new ConnectionCallback() {

        public Object doInConnection(Connection con) throws SQLException, DataAccessException {
            DatabaseMetaData metaData = con.getMetaData();
            return metaData.getDatabaseProductName() + "-" + metaData.getDatabaseProductVersion();
        }
    });

    System.out.println(version);
    Assert.assertNotNull(version);
}
 

@Test
public void reconcileFromDB() {
    UserTO userTO = null;
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    try {
        ExecTO execution = execProvisioningTask(
                taskService, TaskType.PULL, "83f7e85d-9774-43fe-adba-ccd856312994", MAX_WAIT_SECONDS, false);
        assertEquals(ExecStatus.SUCCESS, ExecStatus.valueOf(execution.getStatus()));

        userTO = userService.read("testuser1");
        assertNotNull(userTO);
        assertEquals("[email protected]", userTO.getPlainAttr("userId").get().getValues().get(0));
        assertEquals("suspended", userTO.getStatus());

        // enable user on external resource
        jdbcTemplate.execute("UPDATE TEST SET status=TRUE WHERE id='testuser1'");

        // re-execute the same PullTask: now user must be active
        execution = execProvisioningTask(
                taskService, TaskType.PULL, "83f7e85d-9774-43fe-adba-ccd856312994", MAX_WAIT_SECONDS, false);
        assertEquals(ExecStatus.SUCCESS, ExecStatus.valueOf(execution.getStatus()));

        userTO = userService.read("testuser1");
        assertNotNull(userTO);
        assertEquals("active", userTO.getStatus());
    } finally {
        jdbcTemplate.execute("UPDATE TEST SET status=FALSE WHERE id='testuser1'");
        if (userTO != null) {
            userService.delete(userTO.getKey());
        }
    }
}
 

@Before
public void setupDatabase() throws Exception {
    DataSource ds = context.getRegistry().lookupByNameAndType("myDataSource", DataSource.class);
    jdbc = new JdbcTemplate(ds);

    jdbc.execute("create table partner_metric "
        + "( partner_id varchar(10), time_occurred varchar(20), status_code varchar(3), perf_time varchar(10) )");
}
 

@Override
public void execute(JdbcTemplate template) {

    // version 16 was used for 4.3.beta1
    if (template.queryForInt("select count(*) from version where version = 16") == 0) {
        LOG.info("Updating database schema to version 16.");
        template.execute("insert into version values (16)");
    }

    if (template.queryForInt("select count(*) from version where version = 17") == 0) {
        LOG.info("Updating database schema to version 17.");
        template.execute("insert into version values (17)");

        for (String format : Arrays.asList("avi", "mpg", "mpeg", "mp4", "m4v", "mkv", "mov", "wmv", "ogv")) {
            template.update("delete from transcoding where source_format=? and target_format=?", new Object[] {format, "flv"});
            template.execute("insert into transcoding values(null,'" + format + " > flv' ,'" + format + "' ,'flv','ffmpeg -ss %o -i %s -async 1 -b %bk -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -',null,null,true,true)");
            template.execute("insert into player_transcoding select p.id as player_id, t.id as transaction_id from player p, transcoding t where t.name = '" + format + " > flv'");
        }
        LOG.info("Created video transcoding configuration.");
    }

    if (!columnExists(template, "email", "user")) {
        LOG.info("Database column 'user.email' not found.  Creating it.");
        template.execute("alter table user add email varchar");
        LOG.info("Database column 'user.email' was added successfully.");
    }

}
 

@Override
public void execute(JdbcTemplate template) {

    if (template.queryForInt("select count(*) from version where version = 22") == 0) {
        LOG.info("Updating database schema to version 22.");
        template.execute("insert into version values (22)");

        template.execute("insert into transcoding2(name, source_formats, target_format, step1, default_active) values('mkv video', " +
                "'avi mpg mpeg mp4 m4v mkv mov wmv ogv divx m2ts', 'mkv', " +
                "'ffmpeg -ss %o -i %s -c:v libx264 -preset superfast -b:v %bk -c:a libvorbis -f matroska -threads 0 -', 'true')");

        template.execute("insert into player_transcoding2(player_id, transcoding_id) " +
                "select distinct p.id, t.id from player p, transcoding2 t where t.name='mkv video'");
        LOG.info("Added mkv transcoding.");
    }

    if (!columnExists(template, "song_notification", "user_settings")) {
        LOG.info("Database column 'user_settings.song_notification' not found.  Creating it.");
        template.execute("alter table user_settings add song_notification boolean default true not null");
        LOG.info("Database column 'user_settings.song_notification' was added successfully.");
    }

    // Added in 5.0.beta2
    if (template.queryForInt("select count(*) from version where version = 23") == 0) {
        LOG.info("Updating database schema to version 23.");
        template.execute("insert into version values (23)");
        template.execute("update transcoding2 set step1='ffmpeg -i %s -map 0:0 -b:a %bk -v 0 -f mp3 -' where name='mp3 audio'");
    }
}
 

@Before
public void setupDatabase() throws Exception {
    DataSource ds = context.getRegistry().lookupByNameAndType("myDataSource", DataSource.class);
    jdbc = new JdbcTemplate(ds);

    jdbc.execute("create table partner_metric "
            + "( partner_id varchar(10), time_occurred varchar(20), status_code varchar(3), perf_time varchar(10) )");
}
 

public void execute(JdbcTemplate template) {

        if (template.queryForInt("select count(*) from version where version = 9") == 0) {
            LOG.info("Updating database schema to version 9.");
            template.execute("insert into version values (9)");
        }

        if (!columnExists(template, "client_side_playlist", "player")) {
            LOG.info("Database column 'player.client_side_playlist' not found.  Creating it.");
            template.execute("alter table player add client_side_playlist boolean default false not null");
            LOG.info("Database column 'player.client_side_playlist' was added successfully.");
        }
    }
 

@Override
public void deleteDatabase() {
    JdbcTemplate template = createJdbcTemplate();
    try {
        template.execute("drop table if exists " + getObjectsTableName());
    } finally {
        close(template);
    }
}
 

@Before
public void setupDatabase() throws Exception {
    DataSource ds = context.getRegistry().lookupByNameAndType("myDataSource", DataSource.class);
    jdbc = new JdbcTemplate(ds);

    jdbc.execute("create table partner_metric "
            + "( partner_id varchar(10), time_occurred varchar(20), status_code varchar(3), perf_time varchar(10) )");
    jdbc.execute("insert into partner_metric (partner_id, time_occurred, status_code, perf_time) values ('123', '20170515183457', '200', '1503')");
}
 

@Before
public void setupDatabase() throws Exception {
    DataSource ds = context.getRegistry().lookupByNameAndType("myDataSource", DataSource.class);
    jdbc = new JdbcTemplate(ds);

    jdbc.execute("create table partner_metric "
        + "( partner_id varchar(10), time_occurred varchar(20), status_code varchar(3), perf_time varchar(10) )");
}
 

@Override
public void execute(JdbcTemplate template) {

    if (template.queryForInt("select count(*) from version where version = 25") == 0) {
        LOG.info("Updating database schema to version 25.");
        template.execute("insert into version values (25)");
    }

    if (!rowExists(template, "table_name='PODCAST_EPISODE' and column_name='URL' and ordinal_position=1",
                   "information_schema.system_indexinfo")) {
        template.execute("create index idx_podcast_episode_url on podcast_episode(url)");
        LOG.info("Created index for podcast_episode.url");
    }

    if (!columnExists(template, "default_album_list", "user_settings")) {
        LOG.info("Database column 'user_settings.default_album_list' not found.  Creating it.");
        template.execute("alter table user_settings add default_album_list varchar default '" +
                         AlbumListType.RANDOM.getId() + "' not null");
        LOG.info("Database column 'user_settings.default_album_list' was added successfully.");
    }

    if (!columnExists(template, "queue_following_songs", "user_settings")) {
        LOG.info("Database column 'user_settings.queue_following_songs' not found.  Creating it.");
        template.execute("alter table user_settings add queue_following_songs boolean default true not null");
        LOG.info("Database column 'user_settings.queue_following_songs' was added successfully.");
    }

    if (!columnExists(template, "image_url", "podcast_channel")) {
        LOG.info("Database column 'podcast_channel.image_url' not found.  Creating it.");
        template.execute("alter table podcast_channel add image_url varchar");
        LOG.info("Database column 'podcast_channel.image_url' was added successfully.");
    }

    if (!columnExists(template, "show_side_bar", "user_settings")) {
        LOG.info("Database column 'user_settings.show_side_bar' not found.  Creating it.");
        template.execute("alter table user_settings add show_side_bar boolean default true not null");
        LOG.info("Database column 'user_settings.show_side_bar' was added successfully.");
    }

    if (!columnExists(template, "folder_id", "artist")) {
        LOG.info("Database column 'artist.folder_id' not found.  Creating it.");
        template.execute("alter table artist add folder_id int");
        LOG.info("Database column 'artist.folder_id' was added successfully.");
    }
}
 

/**
 * @throws SQLException
 * @param jdbcTemplate2
 * @param sysAdviceMessDao
 * @param multiDatasource
 * @param sysZhcxTab
 * 方法名： startConfixTableConfig
 * 功 能： TODO(这里用一句话描述这个方法的作用)
 * 参 数：
 * 返 回： void
 * 作 者 ： Administrator
 * @throws
 */
public static void startConfixOracleTableConfig(SysDbmsTabsInfo sysZhcxTab, Map<String, DataSource> multiDatasource, SysDbmsAdviMessInfoDao sysAdviceMessDao, JdbcTemplate jdbcTemplate2) throws SQLException {
	logger.info("startConfixTableConfig", ZhcxAdviceService.class);
	// 表配置比较建议修正 (表修改，表配置修改)
	// 表修改需要人工确认，所以当前不会生成表修改的类型
	// 表配置修改包括： 修改注释翻译对照的mess和 更新配置表中的数据两块大小

	StringBuffer sBuffer = new StringBuffer();
	sBuffer.append(" select t.owner,t.table_name,t.num_rows,t.blocks*8*1024 as table_space ,tc.comments  from all_tables t ");
	sBuffer.append(" inner join all_tab_comments tc on t.owner = tc.owner and t.table_name = tc.table_name ");
	sBuffer.append(" where t.owner||'.'||t.table_name = :tablename");
	Map<String, String> map = new HashMap<>();
	String tableName = sysZhcxTab.getTabsName();
	if (tableName.contains("@")) {
		tableName = sysZhcxTab.getTabsName().substring(0, sysZhcxTab.getTabsName().indexOf("@"));
	}
	map.put("tablename", tableName);
	SysDbmsAdviMessInfo advice = new SysDbmsAdviMessInfo(UUID.randomUUID().toString(), "表配置修改", sysZhcxTab.getTabsDesc(), tableName, sysZhcxTab.getJdbcUuid());
	NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(multiDatasource.get(sysZhcxTab.getJdbcUuid()));
	List<Map<String, Object>> list = template.queryForList(sBuffer.toString(), map);
	if (list != null && list.size() > 0) {
		Map<String, Object> resultmap = list.get(0);
		// 表数据量更新
		if (sysZhcxTab.getTabsRows() == null || sysZhcxTab.getTabsSpace() == null || sysZhcxTab.getTabsRows() != resultmap.get("num_rows") || sysZhcxTab.getTabsSpace() != resultmap.get("table_space")) {
			String executeSql = "";
			if (jdbcTemplate2.getDataSource().getConnection().getMetaData().getDatabaseProductName().equals("ORACLE")) {
				executeSql = "update sys_dbms_tabs_info t set  t.tabs_rows = " + resultmap.get("num_rows") + ",t.tabs_space = " + resultmap.get("tabs_space") + ",t.update_time = sysdate where t.uuid='" + sysZhcxTab.getUuid() + "'";
			} else if (jdbcTemplate2.getDataSource().getConnection().getMetaData().getDatabaseProductName().equals("MySQL")) {
				executeSql = "update sys_dbms_tabs_info t set  t.tabs_rows = " + resultmap.get("num_rows") + ",t.tabs_space = " + resultmap.get("tabs_space") + ",t.update_time = CURRENT_TIMESTAMP() where t.uuid='" + sysZhcxTab.getUuid() + "'";
			}
			advice.setExecuteSql(executeSql + ";");
			logger.debug(executeSql, ZhcxAdviceService.class);
			jdbcTemplate2.execute(executeSql);
			advice.setDeleteFlag(1);
			sysAdviceMessDao.save(advice);

		}
		// 表注释和翻译
		if (sysZhcxTab.getTabsDesc() != null && resultmap.get("comments") != null) {
			StringBuilder sBuilder = new StringBuilder();
			if (sysZhcxTab.getTabsDesc() == null || "".equals(sysZhcxTab.getTabsDesc())) {
				sBuilder.append("-- 由于配置中的信息没有，建议执行以下语句进行统一：\n");
				sBuilder.append("update sys_dbms_tabs_info t set t.tabs_desc ='" + resultmap.get("comments") + "' ,t.update_time = sysdate where t.uuid='" + sysZhcxTab.getUuid() + "'; \n");
			} else if (resultmap.get("comments") == null || "".equals(resultmap.get("comments"))) {
				sBuilder.append("-- 由于表中注释信息没有，建议执行以下语句进行统一：\n");
				sBuilder.append("comment on table " + tableName + "  is '" + sysZhcxTab.getTabsDesc() + "';\n");
			} else if (!sysZhcxTab.getTabsDesc().equals(resultmap.get("comments"))) {
				sBuilder.append("-- 由于表中注释信息和配置中的信息不一致，建议执行以下语句进行统一：\n");
				sBuilder.append("-- 建议 一 根据表信息 更新配置表中的信息.\n");
				sBuilder.append("--  update sys_dbms_tabs_info t set t.tabs_desc ='" + resultmap.get("comments") + "' ,t.update_time = sysdate where t.uuid='" + sysZhcxTab.getUuid() + "'; \n");
				sBuilder.append("-- 建议 二 根据配置表中的信息更新表信息 .\n");
				sBuilder.append("comment on table " + tableName + "  is '" + sysZhcxTab.getTabsDesc() + "';\n");
			} else {
				return;
			}
			advice.setMessage(sBuilder.toString());
			advice.setDeleteFlag(0);
			sysAdviceMessDao.save(advice);
		}
	}

}
 

public void execute(JdbcTemplate template) {
    if (!tableExists(template, "version")) {

        // Increase data file limit. See http://www.hsqldb.org/doc/guide/ch04.html
        template.execute("set property \"hsqldb.cache_file_scale\" 8");

        LOG.info("Database table 'version' not found.  Creating it.");
        template.execute("create table version (version int not null)");
        template.execute("insert into version values (1)");
        LOG.info("Database table 'version' was created successfully.");
    }

    if (!tableExists(template, "role")) {
        LOG.info("Database table 'role' not found.  Creating it.");
        template.execute("create table role (" +
                         "id int not null," +
                         "name varchar not null," +
                         "primary key (id))");
        template.execute("insert into role values (1, 'admin')");
        template.execute("insert into role values (2, 'download')");
        template.execute("insert into role values (3, 'upload')");
        template.execute("insert into role values (4, 'playlist')");
        template.execute("insert into role values (5, 'coverart')");
        LOG.info("Database table 'role' was created successfully.");
    }

    if (!tableExists(template, "user")) {
        LOG.info("Database table 'user' not found.  Creating it.");
        template.execute("create table user (" +
                         "username varchar not null," +
                         "password varchar not null," +
                         "primary key (username))");
        template.execute("insert into user values ('admin', 'admin')");
        LOG.info("Database table 'user' was created successfully.");
    }

    if (!tableExists(template, "user_role")) {
        LOG.info("Database table 'user_role' not found.  Creating it.");
        template.execute("create table user_role (" +
                         "username varchar not null," +
                         "role_id int not null," +
                         "primary key (username, role_id)," +
                         "foreign key (username) references user(username)," +
                         "foreign key (role_id) references role(id))");
        template.execute("insert into user_role values ('admin', 1)");
        template.execute("insert into user_role values ('admin', 2)");
        template.execute("insert into user_role values ('admin', 3)");
        template.execute("insert into user_role values ('admin', 4)");
        template.execute("insert into user_role values ('admin', 5)");
        LOG.info("Database table 'user_role' was created successfully.");
    }
}