Example usage for org.springframework.jdbc.core JdbcTemplate 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.agnitas.dao.impl.RecipientDaoImpl.java

/**
 * Holds value of property applicationContext.
 *//*www. j av a2 s.co  m*/

public void deleteAllNoBindings(int companyID, String toBeDeletedTable) {
    JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
    String delete = "delete from customer_" + companyID + "_tbl " + "where customer_id not in ("
            + "select customer_id from customer_" + companyID + "_binding_tbl" + ") "
            + "and customer_id in (select * from " + toBeDeletedTable + ")";

    tmpl.update(delete);
    tmpl.execute("drop table " + toBeDeletedTable);
}

---

From source file:org.agnitas.dao.impl.RecipientDaoImpl.java

public String createTmpTableByMailinglistID(int companyID, int mailinglistID) {
    String tableName = "tmp_" + String.valueOf(System.currentTimeMillis()) + "_delete_tbl";

    JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource"));
    String sql = "create table " + tableName + " as (" + "select customer_id from customer_" + companyID
            + "_tbl where customer_id in (" + "select customer_id from customer_" + companyID
            + "_binding_tbl where mailinglist_id = " + mailinglistID + ")" + ")";
    tmpl.execute(String.format(sql, mailinglistID));
    return tableName;
}

---

From source file:org.apache.syncope.core.persistence.jpa.content.XMLContentLoader.java

private void createIndexes(final String domain, final DataSource dataSource) throws IOException {
    LOG.debug("[{}] Creating indexes", domain);

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    Properties indexes = PropertiesLoaderUtils.loadProperties(indexesXML.getResource());
    for (String idx : indexes.stringPropertyNames()) {
        LOG.debug("[{}] Creating index {}", domain, indexes.get(idx).toString());

        try {//w  w  w  . j  av a  2 s  . co m
            jdbcTemplate.execute(indexes.get(idx).toString());
        } catch (DataAccessException e) {
            LOG.error("[{}] Could not create index", domain, e);
        }
    }

    LOG.debug("Indexes created");
}

---

From source file:org.apache.syncope.core.persistence.jpa.content.XMLContentLoader.java

private void createViews(final String domain, final DataSource dataSource) throws IOException {
    LOG.debug("[{}] Creating views", domain);

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    Properties views = PropertiesLoaderUtils.loadProperties(viewsXML.getResource());
    for (String idx : views.stringPropertyNames()) {
        LOG.debug("[{}] Creating view {}", domain, views.get(idx).toString());

        try {//from  w w  w  .  ja v a  2  s. c  o  m
            jdbcTemplate.execute(views.get(idx).toString().replaceAll("\\n", " "));
        } catch (DataAccessException e) {
            LOG.error("[{}] Could not create view", domain, e);
        }
    }

    LOG.debug("Views created");
}

---

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

@Test
public void reconcileFromDB() {
    UserTO userTO = null;//from  w  w  w .j a  va2  s . c om
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    try {
        ExecTO execution = execProvisioningTask(taskService, "83f7e85d-9774-43fe-adba-ccd856312994", 50, false);
        assertEquals(PropagationTaskExecStatus.SUCCESS,
                PropagationTaskExecStatus.valueOf(execution.getStatus()));

        userTO = userService.read("testuser1");
        assertNotNull(userTO);
        assertEquals("reconciled@syncope.apache.org", 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, "83f7e85d-9774-43fe-adba-ccd856312994", 50, false);
        assertEquals(PropagationTaskExecStatus.SUCCESS,
                PropagationTaskExecStatus.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());
        }
    }
}

---

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

@Test
public void filteredReconciliation() throws IOException {
    String user1OnTestPull = UUID.randomUUID().toString();
    String user2OnTestPull = UUID.randomUUID().toString();

    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    PullTaskTO task = null;/* w w w  .  j  a v a 2  s  . c  om*/
    UserTO userTO = null;
    try {
        // 1. create 2 users on testpull
        jdbcTemplate.execute("INSERT INTO testpull VALUES (" + "'" + user1OnTestPull
                + "', 'user1', 'Doe', 'mail1@apache.org', NULL)");
        jdbcTemplate.execute("INSERT INTO testpull VALUES (" + "'" + user2OnTestPull
                + "', 'user2', 'Rossi', 'mail2@apache.org', NULL)");

        // 2. create new pull task for test-db, with reconciliation filter (surname 'Rossi') 
        ImplementationTO reconFilterBuilder = new ImplementationTO();
        reconFilterBuilder.setKey("TestReconFilterBuilder");
        reconFilterBuilder.setEngine(ImplementationEngine.GROOVY);
        reconFilterBuilder.setType(ImplementationType.RECON_FILTER_BUILDER);
        reconFilterBuilder.setBody(IOUtils.toString(
                getClass().getResourceAsStream("/TestReconFilterBuilder.groovy"), StandardCharsets.UTF_8));
        Response response = implementationService.create(reconFilterBuilder);
        reconFilterBuilder = getObject(response.getLocation(), ImplementationService.class,
                ImplementationTO.class);
        assertNotNull(reconFilterBuilder);

        task = taskService.read("7c2242f4-14af-4ab5-af31-cdae23783655", true);
        task.setPullMode(PullMode.FILTERED_RECONCILIATION);
        task.setReconFilterBuilder(reconFilterBuilder.getKey());
        response = taskService.create(task);
        task = getObject(response.getLocation(), TaskService.class, PullTaskTO.class);
        assertNotNull(task);
        assertEquals(reconFilterBuilder.getKey(), task.getReconFilterBuilder());

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

        // 4. verify that only enabled user was pulled
        userTO = userService.read("user2");
        assertNotNull(userTO);

        try {
            userService.read("user1");
            fail("This should not happen");
        } catch (SyncopeClientException e) {
            assertEquals(ClientExceptionType.NotFound, e.getType());
        }
    } finally {
        jdbcTemplate.execute("DELETE FROM testpull WHERE id = '" + user1OnTestPull + "'");
        jdbcTemplate.execute("DELETE FROM testpull WHERE id = '" + user2OnTestPull + "'");
        if (task != null && !"7c2242f4-14af-4ab5-af31-cdae23783655".equals(task.getKey())) {
            taskService.delete(task.getKey());
        }
        if (userTO != null) {
            userService.delete(userTO.getKey());
        }
    }
}

---

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

@Test
public void syncTokenWithErrors() {
    ResourceTO origResource = resourceService.read(RESOURCE_NAME_DBPULL);
    ConnInstanceTO origConnector = connectorService.read(origResource.getConnector(), null);

    ResourceTO resForTest = SerializationUtils.clone(origResource);
    resForTest.setKey("syncTokenWithErrors");
    resForTest.setConnector(null);//from   w w w.jav  a2s .c o  m
    ConnInstanceTO connForTest = SerializationUtils.clone(origConnector);
    connForTest.setKey(null);
    connForTest.setDisplayName("For syncTokenWithErrors");

    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    try {
        connForTest.getCapabilities().add(ConnectorCapability.SYNC);

        ConnConfProperty changeLogColumn = connForTest.getConf("changeLogColumn").get();
        assertNotNull(changeLogColumn);
        assertTrue(changeLogColumn.getValues().isEmpty());
        changeLogColumn.getValues().add("lastModification");

        Response response = connectorService.create(connForTest);
        if (response.getStatusInfo().getStatusCode() != Response.Status.CREATED.getStatusCode()) {
            throw (RuntimeException) clientFactory.getExceptionMapper().fromResponse(response);
        }
        connForTest = getObject(response.getLocation(), ConnectorService.class, ConnInstanceTO.class);
        assertNotNull(connForTest);

        resForTest.setConnector(connForTest.getKey());
        resForTest = createResource(resForTest);
        assertNotNull(resForTest);

        PullTaskTO pullTask = new PullTaskTO();
        pullTask.setActive(true);
        pullTask.setName("For syncTokenWithErrors");
        pullTask.setResource(resForTest.getKey());
        pullTask.setDestinationRealm(SyncopeConstants.ROOT_REALM);
        pullTask.setPullMode(PullMode.INCREMENTAL);
        pullTask.setPerformCreate(true);
        pullTask.setPerformUpdate(true);
        pullTask.setPerformDelete(true);

        response = taskService.create(pullTask);
        if (response.getStatusInfo().getStatusCode() != Response.Status.CREATED.getStatusCode()) {
            throw (RuntimeException) clientFactory.getExceptionMapper().fromResponse(response);
        }
        pullTask = getObject(response.getLocation(), TaskService.class, PullTaskTO.class);
        assertNotNull(pullTask);

        jdbcTemplate.execute("DELETE FROM testpull");
        jdbcTemplate.execute("INSERT INTO testpull VALUES " + "(1040, 'syncTokenWithErrors1', 'Surname1', "
                + "'syncTokenWithErrors1@syncope.apache.org', '2014-05-23 13:53:24.293')");
        jdbcTemplate.execute("INSERT INTO testpull VALUES " + "(1041, 'syncTokenWithErrors2', 'Surname2', "
                + "'syncTokenWithErrors1@syncope.apache.org', '2015-05-23 13:53:24.293')");

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

        resForTest = resourceService.read(resForTest.getKey());
        assertTrue(
                resForTest.getProvision(AnyTypeKind.USER.name()).get().getSyncToken().contains("2014-05-23"));

        jdbcTemplate.execute("UPDATE testpull "
                + "SET email='syncTokenWithErrors2@syncope.apache.org', lastModification='2016-05-23 13:53:24.293' "
                + "WHERE ID=1041");

        exec = execProvisioningTask(taskService, pullTask.getKey(), 50, false);
        assertEquals(PropagationTaskExecStatus.SUCCESS, PropagationTaskExecStatus.valueOf(exec.getStatus()));

        resForTest = resourceService.read(resForTest.getKey());
        assertTrue(
                resForTest.getProvision(AnyTypeKind.USER.name()).get().getSyncToken().contains("2016-05-23"));
    } finally {
        if (resForTest.getConnector() != null) {
            resourceService.delete(resForTest.getKey());
            connectorService.delete(connForTest.getKey());
        }

        jdbcTemplate.execute("DELETE FROM testpull WHERE ID=1040");
        jdbcTemplate.execute("DELETE FROM testpull WHERE ID=1041");
    }
}

---

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

@Test
public void issueSYNCOPE230() {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);

    String id = "a54b3794-b231-47be-b24a-11e1a42949f6";

    // 1. populate the external table
    jdbcTemplate.execute("INSERT INTO testpull VALUES" + "('" + id
            + "', 'issuesyncope230', 'Surname', 'syncope230@syncope.apache.org', NULL)");

    // 2. execute PullTask for resource-db-pull (table TESTPULL on external H2)
    execProvisioningTask(taskService, "7c2242f4-14af-4ab5-af31-cdae23783655", 50, false);

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

    // 4. update TESTPULL on external H2 by changing e-mail address
    jdbcTemplate
            .execute("UPDATE TESTPULL SET email='updatedSYNCOPE230@syncope.apache.org' WHERE id='" + id + "'");

    // 5. re-execute the PullTask
    execProvisioningTask(taskService, "7c2242f4-14af-4ab5-af31-cdae23783655", 50, false);

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

---

From source file:org.apache.syncope.fit.core.PullTaskITCase.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).getEntity();
    assertNotNull(user);/*w  w w .  j  a  v  a 2 s.c  o m*/
    assertFalse(user.getResources().isEmpty());

    // 2. Check that the DB resource has the correct password
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    String value = queryForObject(jdbcTemplate, 50, "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. Pull the user from the resource
    ImplementationTO pullActions = new ImplementationTO();
    pullActions.setKey(DBPasswordPullActions.class.getSimpleName());
    pullActions.setEngine(ImplementationEngine.JAVA);
    pullActions.setType(ImplementationType.PULL_ACTIONS);
    pullActions.setBody(DBPasswordPullActions.class.getName());
    Response response = implementationService.create(pullActions);
    pullActions = getObject(response.getLocation(), ImplementationService.class, ImplementationTO.class);
    assertNotNull(pullActions);

    PullTaskTO pullTask = new PullTaskTO();
    pullTask.setDestinationRealm(SyncopeConstants.ROOT_REALM);
    pullTask.setName("DB Pull Task");
    pullTask.setActive(true);
    pullTask.setPerformCreate(true);
    pullTask.setPerformUpdate(true);
    pullTask.setPullMode(PullMode.FULL_RECONCILIATION);
    pullTask.setResource(RESOURCE_NAME_TESTDB);
    pullTask.getActions().add(pullActions.getKey());
    Response taskResponse = taskService.create(pullTask);

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

    pullTask = taskService.read(actual.getKey(), true);
    assertNotNull(pullTask);
    assertEquals(actual.getKey(), pullTask.getKey());
    assertEquals(actual.getJobDelegate(), pullTask.getJobDelegate());

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

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

    // 6. Delete PullTask + user
    taskService.delete(pullTask.getKey());
    deleteUser(user.getKey());
}

---

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

@Test
public void reconcileFromDB() {
    // update sync task
    TaskExecTO execution = execProvisioningTask(7L, 50, false);
    assertNotNull(execution.getStatus());
    assertTrue(PropagationTaskExecStatus.valueOf(execution.getStatus()).isSuccessful());

    UserTO userTO = readUser("testuser1");
    assertNotNull(userTO);//from   w  ww .ja va  2 s  .  c  o m
    assertEquals("reconciled@syncope.apache.org", userTO.getPlainAttrMap().get("userId").getValues().get(0));
    assertEquals("suspended", userTO.getStatus());

    // enable user on external resource
    JdbcTemplate jdbcTemplate = new JdbcTemplate(testDataSource);
    jdbcTemplate.execute("UPDATE TEST SET STATUS=TRUE");

    // re-execute the same SyncTask: now user must be active
    execution = execProvisioningTask(7L, 50, false);
    assertNotNull(execution.getStatus());
    assertTrue(PropagationTaskExecStatus.valueOf(execution.getStatus()).isSuccessful());

    userTO = readUser("testuser1");
    assertNotNull(userTO);
    assertEquals("active", userTO.getStatus());
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• »