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.atricore.idbus.idojos.dbsessionstore.test.JdbcSessionStoreTest.java

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

---

From source file:org.cloudfoundry.identity.uaa.db.mysql.V1_5_4__NormalizeTableAndColumnNames.java

@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    logger.info("[V1_5_4] Running SQL: " + colQuery);
    List<DatabaseInformation1_5_3.ColumnInfo> columns = jdbcTemplate.query(colQuery,
            new DatabaseInformation1_5_3.ColumnMapper());
    for (DatabaseInformation1_5_3.ColumnInfo column : columns) {
        if (processColumn(column)) {
            String sql = column.sql.replaceAll("2001-01-01 .*", "'2001-01-01 01:01:01.000001'");
            logger.info("Renaming column: [" + sql + "]");
            jdbcTemplate.execute(sql);
        }/*from ww w .j a va  2 s.c  om*/
    }
}

---

From source file:org.firewaterframework.test.TestRouteMapper.java

@BeforeClass
public static void init() {
    appContext = new ClassPathXmlApplicationContext("rest-base.xml");
    BasicDataSource ds = (BasicDataSource) appContext.getBean("dataSource");
    JdbcTemplate template = new JdbcTemplate(ds);
    String sql = "create table user(id int auto_increment primary key, first_name varchar(255), last_name varchar(255),"
            + "city varchar(255), state char(2), email varchar(255), password varchar(255), zip int)";
    template.execute(sql);

    sql = "create table user_role( user_id int, role_id int )";
    template.execute(sql);/*ww w  .j av  a2 s . c  o m*/

    sql = "create table role( id int, name varchar(64) )";
    template.execute(sql);

    sql = "create table pet(id int auto_increment primary key, name varchar(255), species_id int, owner_id int)";
    template.execute(sql);

    sql = "create table species(id int auto_increment primary key, name varchar(255))";
    template.execute(sql);

    sql = "insert into user( id, first_name, last_name, city, state, email, password, zip ) "
            + "values( 0, 'joe', 'who', 'new york', 'NY', 'joe@who.com', 'yahoo', 10012 ),"
            + "( 1, 'willie', 'who', 'new york', 'NY', 'willie@who.com', 'yahoo', 10012 ),"
            + "( 2, 'joe', 'wonka', 'new york', 'NY', 'joe@wonka.com', 'yahoo', 10033 ),"
            + "( 3, 'jane', 'who', 'San Francisco', 'CA', 'jane@who.com', 'ziper', 28218 ),"
            + "( 4, 'jim', 'morrison', 'new york', 'NY', 'whoajee@who.com', 'nutz', 10012 ),"
            + "( 5, 'eddie', 'van halen', 'los angeles', 'CA', 'zorker@who.com', 'yahoo', 90210 )";
    template.update(sql);

    sql = "insert into user_role( user_id, role_id ) "
            + "values( 0, 0 ), ( 1, 0 ), ( 2, 1 ), ( 3, 1 ), ( 4, 0 ), ( 5, 0 )";
    template.update(sql);

    sql = "insert into role( id, name ) values " + "(0, 'User'),(1, 'Admin')";
    template.update(sql);

    sql = "insert into pet( id, name, species_id, owner_id ) " + "values( 0, 'trixie', 1, 1 ),"
            + "( 1, 'wixie', 3, 1 )," + "( 2, 'jimmy', 1, 3 )," + "( 3, 'flopsy', 2, 4 ),"
            + "( 4, 'mixie', 2, 1 )";
    template.update(sql);

    sql = "insert into species( id, name ) " + "values( 0, 'cat' )," + "( 1, 'dog' )," + "( 2, 'birdy' ),"
            + "( 3, 'fish' )";
    template.update(sql);
}

---

From source file:org.josso.gateway.assertion.service.store.db.test.DataSourceAssertionStoreTest.java

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

---

From source file:org.josso.gateway.assertion.service.store.db.test.DataSourceAssertionStoreTest.java

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

---

From source file:org.jsecurity.samples.spring.BootstrapDataPopulator.java

public void afterPropertiesSet() throws Exception {
    //because we're using an in-memory hsqldb for the sample app, a new one will be created each time the
    //app starts, so create the tables and insert the 2 sample users on bootstrap:

    JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
    jdbcTemplate.execute(CREATE_TABLES);

    //password is 'user1' SHA hashed and base64 encoded:
    //The first argument to the hash constructor is the actual value to be hased.  The 2nd is the
    //salt.  In this simple demo scenario, the username and the password are the same, but to clarify the
    //distinction, you would see this in practice:
    //new Sha256Hash( <password>, <username> )
    String query = "insert into users values ('user1', '" + new Sha256Hash("user1", "user1").toBase64() + "' )";
    jdbcTemplate.execute(query);/*w  w  w.  jav a2 s.  co m*/
    log.debug("Created user1.");

    //password is 'user2' SHA hashed and base64 encoded:
    query = "insert into users values ( 'user2', '" + new Sha256Hash("user2", "user2").toBase64() + "' )";
    jdbcTemplate.execute(query);
    log.debug("Created user2.");

    query = "insert into roles values ( 'role1' )";
    jdbcTemplate.execute(query);
    log.debug("Created role1");

    query = "insert into roles values ( 'role2' )";
    jdbcTemplate.execute(query);
    log.debug("Created role2");

    query = "insert into roles_permissions values ( 'role1', 'permission1')";
    jdbcTemplate.execute(query);
    log.debug("Created permission 1 for role 1");

    query = "insert into roles_permissions values ( 'role1', 'permission2')";
    jdbcTemplate.execute(query);
    log.debug("Created permission 2 for role 1");

    query = "insert into roles_permissions values ( 'role2', 'permission1')";
    jdbcTemplate.execute(query);
    log.debug("Created permission 1 for role 2");

    query = "insert into user_roles values ( 'user1', 'role1' )";
    jdbcTemplate.execute(query);
    query = "insert into user_roles values ( 'user1', 'role2' )";
    jdbcTemplate.execute(query);
    log.debug("Assigned user1 roles role1 and role2");

    query = "insert into user_roles values ( 'user2', 'role2' )";
    jdbcTemplate.execute(query);
    log.debug("Assigned user2 role role2");
}

---

From source file:org.kuali.kra.infrastructure.TestUtilities.java

License:asdf

public static void verifyTestEnvironment(DataSource dataSource) {
    if (dataSource == null) {
        Assert.fail("Could not locate the EDEN data source.");
    }//  w ww .java  2 s. c o m
    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;
        }
    });
}

---

From source file:org.kuali.kra.infrastructure.TestUtilities.java

License:asdf

public static void clearTables(final PlatformTransactionManager transactionManager, final DataSource dataSource,
        final String edenSchemaName, final List<String> dontClear) {
    LOG.info("Clearing tables for schema " + edenSchemaName);
    if (dataSource == null) {
        Assert.fail("Null data source given");
    }/*from  ww w  . jav a  2  s  . c  o m*/
    if (edenSchemaName == null || edenSchemaName.equals("")) {
        Assert.fail("Empty eden schema name given");
    }
    new TransactionTemplate(transactionManager).execute(new TransactionCallback() {
        public Object doInTransaction(TransactionStatus status) {
            verifyTestEnvironment(dataSource);
            JdbcTemplate template = new JdbcTemplate(dataSource);
            return template.execute(new StatementCallback() {
                public Object doInStatement(Statement statement) throws SQLException {
                    List<String> reEnableConstraints = new ArrayList<String>();
                    ResultSet resultSet = statement.getConnection().getMetaData().getTables(null,
                            edenSchemaName, null, new String[] { "TABLE" });
                    while (resultSet.next()) {
                        String tableName = resultSet.getString("TABLE_NAME");
                        if (tableName.startsWith("EN_") && !dontClear.contains(tableName)) {
                            ResultSet keyResultSet = statement.getConnection().getMetaData()
                                    .getExportedKeys(null, edenSchemaName, tableName);
                            while (keyResultSet.next()) {
                                String fkName = keyResultSet.getString("FK_NAME");
                                String fkTableName = keyResultSet.getString("FKTABLE_NAME");
                                statement.addBatch(
                                        "ALTER TABLE " + fkTableName + " DISABLE CONSTRAINT " + fkName);
                                reEnableConstraints
                                        .add("ALTER TABLE " + fkTableName + " ENABLE CONSTRAINT " + fkName);
                            }
                            keyResultSet.close();
                            statement.addBatch("DELETE FROM " + tableName.toUpperCase());
                        }
                    }
                    for (String constraint : reEnableConstraints) {
                        statement.addBatch(constraint);
                    }
                    statement.executeBatch();
                    resultSet.close();
                    return null;
                }
            });
        }
    });
    LOG.info("Tables successfully cleared for schema " + edenSchemaName);
}

---

From source file:org.kuali.rice.kew.docsearch.dao.impl.DocumentSearchDAOJdbcImpl.java

@Override
public DocumentSearchResults.Builder findDocuments(final DocumentSearchGenerator documentSearchGenerator,
        final DocumentSearchCriteria criteria, final boolean criteriaModified,
        final List<RemotableAttributeField> searchFields) {
    final int maxResultCap = getMaxResultCap(criteria);
    try {//from w  w w  .  j av a2  s  . c  o  m
        final JdbcTemplate template = new JdbcTemplate(dataSource);

        return template.execute(new ConnectionCallback<DocumentSearchResults.Builder>() {
            @Override
            public DocumentSearchResults.Builder doInConnection(final Connection con) throws SQLException {
                final Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                try {
                    final int fetchIterationLimit = getFetchMoreIterationLimit();
                    final int fetchLimit = fetchIterationLimit * maxResultCap;
                    statement.setFetchSize(maxResultCap + 1);
                    statement.setMaxRows(fetchLimit + 1);

                    PerformanceLogger perfLog = new PerformanceLogger();
                    String sql = documentSearchGenerator.generateSearchSql(criteria, searchFields);
                    perfLog.log("Time to generate search sql from documentSearchGenerator class: "
                            + documentSearchGenerator.getClass().getName(), true);
                    LOG.info("Executing document search with statement max rows: " + statement.getMaxRows());
                    LOG.info(
                            "Executing document search with statement fetch size: " + statement.getFetchSize());
                    perfLog = new PerformanceLogger();
                    final ResultSet rs = statement.executeQuery(sql);
                    try {
                        perfLog.log("Time to execute doc search database query.", true);
                        final Statement searchAttributeStatement = con
                                .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        try {
                            return documentSearchGenerator.processResultSet(criteria, criteriaModified,
                                    searchAttributeStatement, rs, maxResultCap, fetchLimit);
                        } finally {
                            try {
                                searchAttributeStatement.close();
                            } catch (SQLException e) {
                                LOG.warn("Could not close search attribute statement.");
                            }
                        }
                    } finally {
                        try {
                            rs.close();
                        } catch (SQLException e) {
                            LOG.warn("Could not close result set.");
                        }
                    }
                } finally {
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        LOG.warn("Could not close statement.");
                    }
                }
            }
        });

    } catch (DataAccessException dae) {
        String errorMsg = "DataAccessException: " + dae.getMessage();
        LOG.error("getList() " + errorMsg, dae);
        throw new RuntimeException(errorMsg, dae);
    } catch (Exception e) {
        String errorMsg = "LookupException: " + e.getMessage();
        LOG.error("getList() " + errorMsg, e);
        throw new RuntimeException(errorMsg, e);
    }
}

---

From source file:org.kuali.rice.kew.test.TestUtilities.java

public static void verifyTestEnvironment(DataSource dataSource) {
    if (dataSource == null) {
        Assert.fail("Could not locate the data source.");
    }//from ww w  .  j  a  v  a  2 s  . c  o  m
    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;
        }
    });
}

---

• «
• 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
• »