Example usage for org.springframework.jdbc.core JdbcTemplate execute

List of usage examples for org.springframework.jdbc.core JdbcTemplate execute

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.*
  5. JdbcTemplate
  6. execute

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate execute.

Prototype

@Override
    public void execute(final String sql) throws DataAccessException

Source Link

Usage

From source file:org.apache.syncope.fit.core.reference.SyncTaskITCase.java

@Test
public void issueSYNCOPE230() {
    // 1. read SyncTask for resource-db-sync (table TESTSYNC on external H2)
    execProvisioningTask(10L, 50, false);

    // 3. read e-mail address for user created by the SyncTask first execution
    UserTO userTO = readUser("issuesyncope230");
    assertNotNull(userTO);//w w  w .  j  a va  2s.  c  om
    String email = userTO.getPlainAttrMap().get("email").getValues().iterator().next();
    assertNotNull(email);

    // 4. update TESTSYNC on external H2 by changing e-mail address
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    jdbcTemplate.execute("UPDATE TESTSYNC SET email='updatedSYNCOPE230@syncope.apache.org'");

    // 5. re-execute the SyncTask
    execProvisioningTask(10L, 50, false);

    // 6. verify that the e-mail was updated
    userTO = readUser("issuesyncope230");
    assertNotNull(userTO);
    email = userTO.getPlainAttrMap().get("email").getValues().iterator().next();
    assertNotNull(email);
    assertEquals("updatedSYNCOPE230@syncope.apache.org", email);
}

From source file:org.apache.syncope.fit.core.reference.SyncTaskITCase.java

@Test
public void issueSYNCOPE313DB() throws Exception {
    // 1. create user in DB
    UserTO user = UserITCase.getUniqueSampleTO("syncope313-db@syncope.apache.org");
    user.setPassword("security123");
    user.getResources().add(RESOURCE_NAME_TESTDB);
    user = createUser(user);//  w ww .  j  av a 2 s  .c  om
    assertNotNull(user);
    assertFalse(user.getResources().isEmpty());

    // 2. Check that the DB resource has the correct password
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    String value = jdbcTemplate.queryForObject("SELECT PASSWORD FROM test WHERE ID=?", String.class,
            user.getUsername());
    assertEquals(Encryptor.getInstance().encode("security123", CipherAlgorithm.SHA1), value.toUpperCase());

    // 3. Update the password in the DB
    String newPassword = Encryptor.getInstance().encode("new-security", CipherAlgorithm.SHA1);
    jdbcTemplate
            .execute("UPDATE test set PASSWORD='" + newPassword + "' where ID='" + user.getUsername() + "'");

    // 4. Sync the user from the resource
    SyncTaskTO syncTask = new SyncTaskTO();
    syncTask.setDestinationRealm(SyncopeConstants.ROOT_REALM);
    syncTask.setName("DB Sync Task");
    syncTask.setPerformCreate(true);
    syncTask.setPerformUpdate(true);
    syncTask.setFullReconciliation(true);
    syncTask.setResource(RESOURCE_NAME_TESTDB);
    syncTask.getActionsClassNames().add(DBPasswordSyncActions.class.getName());
    Response taskResponse = taskService.create(syncTask);

    SyncTaskTO actual = getObject(taskResponse.getLocation(), TaskService.class, SyncTaskTO.class);
    assertNotNull(actual);

    syncTask = taskService.read(actual.getKey());
    assertNotNull(syncTask);
    assertEquals(actual.getKey(), syncTask.getKey());
    assertEquals(actual.getJobClassName(), syncTask.getJobClassName());

    TaskExecTO execution = execProvisioningTask(syncTask.getKey(), 50, false);
    final String status = execution.getStatus();
    assertNotNull(status);
    assertTrue(PropagationTaskExecStatus.valueOf(status).isSuccessful());

    // 5. Test the sync'd user
    UserTO updatedUser = userService.read(user.getKey());
    assertEquals(newPassword, updatedUser.getPassword());

    // 6. Delete SyncTask + user
    taskService.delete(syncTask.getKey());
    deleteUser(user.getKey());
}

From source file:org.apache.syncope.fit.core.SyncTaskITCase.java

@Test
public void reconcileFromDB() {
    UserTO userTO = null;//w  ww.j a  v a 2s  . com
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    try {
        ExecTO execution = execProvisioningTask(taskService, 7L, 50, false);
        assertNotNull(execution.getStatus());
        assertEquals(PropagationTaskExecStatus.SUCCESS,
                PropagationTaskExecStatus.valueOf(execution.getStatus()));

        userTO = readUser("testuser1");
        assertNotNull(userTO);
        assertEquals("reconciled@syncope.apache.org",
                userTO.getPlainAttrMap().get("userId").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 SyncTask: now user must be active
        execution = execProvisioningTask(taskService, 7L, 50, false);
        assertNotNull(execution.getStatus());
        assertEquals(PropagationTaskExecStatus.SUCCESS,
                PropagationTaskExecStatus.valueOf(execution.getStatus()));

        userTO = readUser("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());
        }
    }
}

From source file:org.apache.syncope.fit.core.SyncTaskITCase.java

@Test
public void filteredReconciliation() {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    SyncTaskTO task = null;//from ww w.  j a v a  2s .  com
    UserTO userTO = null;
    try {
        // 1. create 2 users on testsync
        jdbcTemplate.execute("INSERT INTO testsync VALUES (1001, 'user1', 'Doe', 'mail1@apache.org')");
        jdbcTemplate.execute("INSERT INTO testsync VALUES (1002, 'user2', 'Rossi', 'mail2@apache.org')");

        // 2. create new sync task for test-db, with reconciliation filter (surname 'Rossi') 
        task = taskService.read(10L, true);
        task.setSyncMode(SyncMode.FILTERED_RECONCILIATION);
        task.setReconciliationFilterBuilderClassName(TestReconciliationFilterBuilder.class.getName());
        Response response = taskService.create(task);
        task = getObject(response.getLocation(), TaskService.class, SyncTaskTO.class);
        assertNotNull(task);
        assertEquals(TestReconciliationFilterBuilder.class.getName(),
                task.getReconciliationFilterBuilderClassName());

        // 3. exec task
        ExecTO execution = execProvisioningTask(taskService, task.getKey(), 50, false);
        assertNotNull(execution.getStatus());
        assertEquals(PropagationTaskExecStatus.SUCCESS,
                PropagationTaskExecStatus.valueOf(execution.getStatus()));

        // 4. verify that only enabled user was synchronized
        userTO = readUser("user2");
        assertNotNull(userTO);

        try {
            readUser("user1");
            fail();
        } catch (SyncopeClientException e) {
            assertEquals(ClientExceptionType.NotFound, e.getType());
        }
    } finally {
        jdbcTemplate.execute("DELETE FROM testsync WHERE id = 1001");
        jdbcTemplate.execute("DELETE FROM testsync WHERE id = 1002");
        if (task != null && task.getKey() != 7L) {
            taskService.delete(task.getKey());
        }
        if (userTO != null) {
            userService.delete(userTO.getKey());
        }
    }
}

From source file:org.apache.syncope.fit.core.SyncTaskITCase.java

@Test
public void issueSYNCOPE230() {
    // 1. read SyncTask for resource-db-sync (table TESTSYNC on external H2)
    execProvisioningTask(taskService, 10L, 50, false);

    // 3. read e-mail address for user created by the SyncTask first execution
    UserTO userTO = readUser("issuesyncope230");
    assertNotNull(userTO);/*from   w w  w .jav a2  s .c  o m*/
    String email = userTO.getPlainAttrMap().get("email").getValues().iterator().next();
    assertNotNull(email);

    // 4. update TESTSYNC on external H2 by changing e-mail address
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    jdbcTemplate.execute("UPDATE TESTSYNC SET email='updatedSYNCOPE230@syncope.apache.org'");

    // 5. re-execute the SyncTask
    execProvisioningTask(taskService, 10L, 50, false);

    // 6. verify that the e-mail was updated
    userTO = readUser("issuesyncope230");
    assertNotNull(userTO);
    email = userTO.getPlainAttrMap().get("email").getValues().iterator().next();
    assertNotNull(email);
    assertEquals("updatedSYNCOPE230@syncope.apache.org", email);
}

From source file:org.apache.syncope.fit.core.SyncTaskITCase.java

@Test
public void issueSYNCOPE313DB() throws Exception {
    // 1. create user in DB
    UserTO user = UserITCase.getUniqueSampleTO("syncope313-db@syncope.apache.org");
    user.setPassword("security123");
    user.getResources().add(RESOURCE_NAME_TESTDB);
    user = createUser(user).getAny();//from www  .j a va  2s.  co m
    assertNotNull(user);
    assertFalse(user.getResources().isEmpty());

    // 2. Check that the DB resource has the correct password
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    String value = jdbcTemplate.queryForObject("SELECT PASSWORD FROM test WHERE ID=?", String.class,
            user.getUsername());
    assertEquals(Encryptor.getInstance().encode("security123", CipherAlgorithm.SHA1), value.toUpperCase());

    // 3. Update the password in the DB
    String newCleanPassword = "new-security";
    String newPassword = Encryptor.getInstance().encode(newCleanPassword, CipherAlgorithm.SHA1);
    jdbcTemplate
            .execute("UPDATE test set PASSWORD='" + newPassword + "' where ID='" + user.getUsername() + "'");

    // 4. Sync the user from the resource
    SyncTaskTO syncTask = new SyncTaskTO();
    syncTask.setDestinationRealm(SyncopeConstants.ROOT_REALM);
    syncTask.setName("DB Sync Task");
    syncTask.setActive(true);
    syncTask.setPerformCreate(true);
    syncTask.setPerformUpdate(true);
    syncTask.setSyncMode(SyncMode.FULL_RECONCILIATION);
    syncTask.setResource(RESOURCE_NAME_TESTDB);
    syncTask.getActionsClassNames().add(DBPasswordSyncActions.class.getName());
    Response taskResponse = taskService.create(syncTask);

    SyncTaskTO actual = getObject(taskResponse.getLocation(), TaskService.class, SyncTaskTO.class);
    assertNotNull(actual);

    syncTask = taskService.read(actual.getKey(), true);
    assertNotNull(syncTask);
    assertEquals(actual.getKey(), syncTask.getKey());
    assertEquals(actual.getJobDelegateClassName(), syncTask.getJobDelegateClassName());

    ExecTO execution = execProvisioningTask(taskService, syncTask.getKey(), 50, false);
    assertEquals(PropagationTaskExecStatus.SUCCESS, PropagationTaskExecStatus.valueOf(execution.getStatus()));

    // 5. Test the sync'd user
    Pair<Map<String, Set<String>>, UserTO> self = clientFactory.create(user.getUsername(), newCleanPassword)
            .self();
    assertNotNull(self);

    // 6. Delete SyncTask + user
    taskService.delete(syncTask.getKey());
    deleteUser(user.getKey());
}

From source file:org.apereo.portal.jdbc.DatabaseMetaDataImpl.java

/**
 * Test the database to see if it really supports outer joins.
 * @param conn The connection to use./* w  w w.  ja va 2 s  . c o m*/
 */
private void testOuterJoins(final JdbcTemplate jdbcTemplate) {
    if (this.dbmdSupportsOuterJoins) {
        for (final JoinQueryString joinQueryString : joinTests) {
            final String joinTestQuery = "SELECT COUNT(UP_USER.USER_ID) " + "FROM "
                    + joinQueryString.getTestJoin() + " UP_USER.USER_ID=0";

            try {
                transactionTemplate.execute(new TransactionCallbackWithoutResult() {
                    @Override
                    public void doInTransactionWithoutResult(TransactionStatus status) {
                        jdbcTemplate.execute(joinTestQuery);
                    }
                });

                this.joinTest = joinQueryString;
                if (LOG.isDebugEnabled()) {
                    LOG.debug("Using join test: " + this.joinTest.getClass().getName());
                }
                break;
            } catch (Exception e) {
                final String logMessage = "Join test failed: " + joinQueryString.getClass().getName()
                        + " on statement: '" + joinTestQuery + "':";

                if (LOG.isDebugEnabled()) {
                    LOG.debug(logMessage, e);
                }
            }
        }
    }
}

From source file:org.atricore.idbus.idojos.dbidentitystore.test.JDBCIdentityStoreTest.java

private static void createTables(JdbcTemplate template) throws Exception {
    template.execute(getQueryFromFile("sso.sql"));
}

From source file:org.atricore.idbus.idojos.dbidentitystore.test.JDBCIdentityStoreTest.java

private static void insertData(JdbcTemplate template) throws Exception {
    template.execute(getQueryFromFile("sso-data.sql"));
}

From source file:org.atricore.idbus.idojos.dbsessionstore.test.JdbcSessionStoreTest.java

private static void createTables(JdbcTemplate template) throws Exception {
    template.execute(getQueryFromFile("sso-session.sql"));
}