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.opendatakit.persistence.engine.pgres.TaskLockImpl.java

private TaskLockTable doTransaction(TaskLockTable entity, long l)
        throws ODKEntityNotFoundException, ODKTaskLockException {
    boolean first;

    final List<String> stmts = new ArrayList<String>();

    String uri = entity.getUri();

    StringBuilder stringBuilder = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;// w ww .  j  a v  a  2 s  .co m

    stringBuilder.append("'").append(user.getUriUser().replaceAll("'", "''")).append("'");
    String uriUserInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(uri.replaceAll("'", "''")).append("'");
    String uriLockInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getFormId().replaceAll("'", "''")).append("'");
    String formIdInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getTaskType().replaceAll("'", "''")).append("'");
    String taskTypeInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("interval '").append(l).append(" milliseconds'");
    String lifetimeIntervalMilliseconds = stringBuilder.toString();
    stringBuilder.setLength(0);

    stringBuilder.append("LOCK TABLE ").append(tableName).append(" IN ACCESS EXCLUSIVE MODE");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);

    dam.recordPutUsage(TaskLockTable.TABLE_NAME);
    if (!entity.isFromDatabase()) {
        // insert a new record (prospective lock)
        stringBuilder.append("INSERT INTO ");
        stringBuilder.append(tableName);
        stringBuilder.append(" (");
        first = true;
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(dataField.getName());
            stringBuilder.append(K_BQ);
        }
        first = true;
        stringBuilder.append(") VALUES ( ");
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            if (dataField.equals(entity.creationDate) || dataField.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (dataField.equals(entity.creatorUriUser) || dataField.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (dataField.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (dataField.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (dataField.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (dataField.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + dataField.getName());
            }
        }
        stringBuilder.append(")");
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    } else {
        // update existing record (prospective lock)
        stringBuilder.append("UPDATE ");
        stringBuilder.append(tableName);
        stringBuilder.append(" SET ");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (f == entity.primaryKey)
                continue;
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(f.getName());
            stringBuilder.append(K_BQ);
            stringBuilder.append(" = ");
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (f.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (f.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (f.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (f.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        stringBuilder.append(" WHERE ");
        stringBuilder.append(K_BQ);
        stringBuilder.append(entity.primaryKey.getName());
        stringBuilder.append(K_BQ);
        stringBuilder.append(" = ");
        stringBuilder.append(uriLockInline);
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    }
    // delete stale locks (don't care who's)
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ).append(" <= NOW()");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete prospective locks which are not the oldest for that resource and
    // task type
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    stringBuilder.append(" > (SELECT MIN(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete our entry if it collides with another entry with exactly 
    // this time.
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.primaryKey.getName()).append(K_BQ).append(" = ")
            .append(uriLockInline).append(" AND ");
    stringBuilder.append("1 < (SELECT COUNT(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // assert: only the lock that holds the resource for that task type appears
    // in the task lock table
    TaskLockTable relation;
    try {

        JdbcTemplate jdbc = datastore.getJdbcConnection();
        jdbc.execute(new ConnectionCallback<Object>() {

            @Override
            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                boolean oldAutoCommitValue = conn.getAutoCommit();
                int oldTransactionValue = conn.getTransactionIsolation();
                try {
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                    Statement stmt = conn.createStatement();
                    for (String s : stmts) {
                        // for debugging: LogFactory.getLog(TaskLockImpl.class).info(s);
                        stmt.execute(s);
                    }
                    conn.commit();
                } catch (PSQLException e) {
                    e.printStackTrace();
                    conn.rollback();
                } catch (Exception e) {
                    e.printStackTrace();
                    conn.rollback();
                }
                conn.setTransactionIsolation(oldTransactionValue);
                conn.setAutoCommit(oldAutoCommitValue);
                return null;
            }

        });

        relation = TaskLockTable.assertRelation(datastore, user);
    } catch (Exception e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }
    return (TaskLockTable) datastore.getEntity(relation, entity.getUri(), user);
}

From source file:com.tmg.fuse.poc.DatabaseBean.java

public void create() throws Exception {
    JdbcTemplate jdbc = new JdbcTemplate(dataSource);

    // TODO: load sql from classpath (can be tricky in OSGi)
    String sql = "CREATE TABLE account( ID INT not null primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), NAME VARCHAR(100) DEFAULT NULL, XREFID INT DEFAULT NULL, CRMID INT DEFAULT NULL, PSPID INT DEFAULT NULL )";
    String sql2 = "CREATE SEQUENCE seq_xrefId START WITH 10000";
    String sql3 = "INSERT INTO account (NAME, XREFID, CRMID) VALUES ('Jon Walton',1234, 100)";
    String sql4 = "INSERT INTO account (NAME, XREFID, CRMID) VALUES ('Graeme Colman',5678, 200)";

    LOG.info("Creating table account ...");

    try {//from   w  w  w.j  a  v a  2  s.  com
        jdbc.execute("drop table account");
    } catch (Throwable e) {
        // ignore
    }

    jdbc.execute(sql);
    jdbc.execute(sql2);
    jdbc.execute(sql3);
    jdbc.execute(sql4);

    LOG.info("... created table account");
}

From source file:org.jasig.ssp.util.importer.job.listener.StagingTableTruncator.java

@Override
public void beforeStep(StepExecution arg0) {
    try {//from ww  w.  jav a 2  s.  com
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        dataSource.getConnection().setAutoCommit(true);
        ResultSet tables = dataSource.getConnection().getMetaData().getTables(null, null, "stg_%",
                new String[] { "TABLE" });
        String[] exclusions = truncateExclusions == null ? new String[] {} : truncateExclusions.split(",");
        while (tables.next()) {
            stagingTables.add(tables.getString("table_name"));
        }
        for (String table : stagingTables) {
            if (isNotExcluded(exclusions, table)) {
                String sql = "truncate table " + table + ";";
                jdbcTemplate.execute(sql);
                logger.info(sql);
            }
        }
    } catch (Exception e) {
        logger.info(e.getMessage());
    }
    logger.info("DONE TRUNCATE");

}

From source file:net.sourceforge.subsonic.dao.schema.SchemaMadsonic.java

@Override
public void execute(JdbcTemplate template) {

    ////////////////////

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

        // Reset Usersetting: show_now_playing & show_chat
        if (columnExists(template, "show_chat", "user_settings")) {
            template.execute("update user_settings set show_chat = false, show_now_playing = false");
            LOG.info("Database Update 'user_settings.show_chat' was added successfully.");
            LOG.info("Database Update 'user_settings.show_now_playing' was added successfully.");
        }/*from  www .  ja  v  a2 s .  com*/
    }

    ////////////////////

    // Add Statistic Table
    if (!tableExists(template, "statistic_user")) {
        LOG.info("Database table 'statistic_user' not found.  Creating it.");
        template.execute("create table statistic_user (" + "id identity," + "username varchar not null,"
                + "media_file_id int not null," + "played datetime not null,"
                + "foreign key (media_file_id) references media_file(id) on delete cascade,"
                + "foreign key (username) references user(username) on delete cascade)");

        template.execute("create index idx_statistic_user_media_file_id on statistic_user(media_file_id)");
        template.execute("create index idx_statistic_user_username on statistic_user(username)");

        LOG.info("Database table 'statistic_user' was created successfully.");
    }

    ////////////////////

    // Add Hot Recommmed Table
    if (!tableExists(template, "hot_rating")) {
        LOG.info("Database table 'hot_rating' not found.  Creating it.");
        template.execute("create table hot_rating (" + "username varchar not null," + "path varchar not null,"
                + "id int not null," + "primary key (username, path),"
                + "foreign key (username) references user(username) on delete cascade)");
        LOG.info("Database table 'hot_rating' was created successfully.");

    }

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

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

    ////////////////////

    if (template.queryForInt("select count(*) from version where version = 103") == 0) {
        LOG.info("Updating database schema to version 103.");
        template.execute("insert into version values (103)");
        template.execute(
                "create index idx_starred_media_file_media_file_id_username on starred_media_file(media_file_id, username)");
        template.execute("create index idx_starred_media_file_created on starred_media_file(created)");
        LOG.info("Database index 'idx_starred_media_file_media_file_id_username' was added successfully.");
        LOG.info("Database index 'idx_starred_media_file_created' was added successfully.");
    }

    ////////////////////

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

        if (template.queryForInt("select count(*) from role where id = 12") == 0) {
            LOG.info("Role 'search' not found in database. Creating it.");
            template.execute("insert into role values (12, 'search')");
            // default for admin/stream role
            template.execute(
                    "insert into user_role " + "select distinct u.username, 12 from user u, user_role ur "
                            + "where u.username = ur.username and ur.role_id = 8");
            LOG.info("Role 'search' was created successfully.");
        }
    }
    ////////////////////

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

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

    ////////////////////

    // Add new User Role 'search' and add as default
    if (template.queryForInt("select count(*) from role where id = 12") == 0) {
        LOG.info("Role 'search' not found in database. Creating it.");
        template.execute("insert into role values (12, 'search')");
        // default for admin/stream role
        template.execute("insert into user_role " + "select distinct u.username, 12 from user u, user_role ur "
                + "where u.username = ur.username and ur.role_id = 8");
        LOG.info("Role 'search' was created successfully.");
    }

    ////////////////////

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

        // wtv transcoding 
        if (template.queryForInt("select count(*) from transcoding2 where name = 'wtv video'") == 0) {
            template.execute(
                    "insert into transcoding2(name, source_formats, target_format, step1) values('wtv video', 'wtv', 'flv', "
                            + "'ffmpeg -ss %o -i %s -async 30 -b %bk -r 23-.976 -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -vcodec libx264 -preset fast -threads 0 -')");
        }
        // FLAC transcoding
        if (template.queryForInt("select count(*) from transcoding2 where name = 'FLAC audio'") == 0) {

            if (template.queryForInt(
                    "SELECT count(*) from transcoding2 where source_formats like '%flac%' and name = 'mp3 audio'") == 1) {
                template.execute(
                        "update transcoding2 set source_formats = 'ogg oga aac m4a wav wma aif aiff ape mpc shn' "
                                + "where source_formats like '%flac%' and name = 'mp3 audio'");
            }
            template.execute(
                    "insert into transcoding2(name, source_formats, target_format, step1, step2) values('FLAC audio', 'flac', 'mp3', "
                            + "'ffmpeg -i %s -v 0 -f wav -', 'lame -V 0 --tt %t --ta %a --tl %l -S --resample 44.1 - -')");
        }

        // SubWiji transcoding
        if (template.queryForInt("select count(*) from transcoding2 where name = 'SubWiji'") == 0) {
            template.execute(
                    "insert into transcoding2(name, source_formats, target_format, step1, default_active) values('SubWiji', 'mp3', 'mp3', "
                            + "'ffmpeg -f mp3 -i %s -ab %bk -v 0 -f mp3 -', false)");
        }

    }
    ////////////////////

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

        // FLAC transcoding
        if (template.queryForInt("select count(*) from transcoding2 where name = 'FLAC audio'") == 1) {

            template.execute("delete from transcoding2 where name = 'FLAC audio'");

            if (template.queryForInt(
                    "SELECT count(*) from transcoding2 where source_formats like '%m4a%' and name = 'mp3 audio'") == 1) {
                template.execute(
                        "update transcoding2 set source_formats = 'ogg oga aac wav wma aif aiff ape mpc shn' "
                                + "where source_formats like '%m4a%' and name = 'mp3 audio'");
            }

            template.execute(
                    "insert into transcoding2(name, source_formats, target_format, step1, step2) values('m4a/FLAC audio', 'flac m4a', 'mp3', "
                            + "'ffmpeg -i %s -v 0 -f wav -', 'lame -V 0 --tt %t --ta %a --tl %l -S --resample 44.1 - -')");
        }

        LOG.info("new transcoding in table 'transcoding2' was inserted successfully.");
    }

    ////////////////////

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

        // FLAC transcoding
        if (template.queryForInt("select count(*) from transcoding2 where name = 'm4a/FLAC audio'") == 1) {

            template.execute("delete from transcoding2 where name = 'm4a/FLAC audio'");

            template.execute(
                    "insert into transcoding2(name, source_formats, target_format, step1 ) values('m4a/FLAC audio', 'm4a flac', 'mp3', "
                            + "'Audioffmpeg -i %s -ab 256k -ar 44100 -ac 2 -v 0 -f mp3 -')");

            template.execute(
                    "update transcoding2 set step1 = 'Audioffmpeg -i %s -ab %bk -v 0 -f mp3 -' where name = 'mp3 audio'");
            template.execute(
                    "update transcoding2 set step1 = 'Audioffmpeg -f mp3 -i %s -ab %bk -v 0 -f mp3 -' where name = 'SubWiji'");

        }

        LOG.info("new transcoding in table 'transcoding2' was inserted successfully.");
    }

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

    ////////////////////

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

        // Add Group Table
        if (!tableExists(template, "user_group")) {
            LOG.info("Database table 'user_group' not found.  Creating it.");
            template.execute("create table user_group (" + "id identity, " + "name varchar not null, "
                    + "primary key (id))");
            LOG.info("Database table 'user_group' was created successfully.");
        }

        // Add Group Access Table
        if (!tableExists(template, "user_group_access")) {
            LOG.info("Database table 'user_group_access' not found.  Creating it.");
            template.execute("create table user_group_access (" + "user_group_id integer not null, "
                    + "music_folder_id integer not null, " + "enabled boolean default true not null, "
                    + "primary key (user_group_id, music_folder_id),"
                    + "foreign key (user_group_id) references user_group(id) on delete cascade,"
                    + "foreign key (music_folder_id) references music_folder(id) on delete cascade)");
            LOG.info("Database table 'user_group_access' was created successfully.");

            template.execute(
                    "create index idx_user_group_access_user_group_id_music_folder_id_enabled on user_group_access(user_group_id, music_folder_id, enabled)");
            LOG.info(
                    "Database index 'idx_user_group_access_user_group_id_music_folder_id_enabled' was added successfully.");
        }
    }

    ////////////////////

    // new transcoding settings
    if (template.queryForInt("select count(*) from version where version = 111") == 0) {

        LOG.info("Updating database schema to version 111.");
        template.execute("insert into version values (111)");

        //ALTER TABLE USER drop constraint FK_2
        //ALTER TABLE USER drop group_id

        template.execute("alter table user add column group_id integer default 0 not null;");

        template.execute("insert into user_group (id, name) values (0, 'ALL')");
        template.execute("insert into user_group (id, name) values (1, 'GUEST')");
        template.execute("insert into user_group (id, name) values (2, 'FAMILY')");
        template.execute("insert into user_group (id, name) values (3, 'FRIENDS')");
        template.execute("insert into user_group (id, name) values (4, 'LIMITED')");

        // Insert Default Access to admin

        // template.execute("insert into public.user_group_access (user_group_id, music_folder_id) values (0, 0)");

        // Insert Default Access to all

        template.execute("insert into user_group_access (user_group_id, music_folder_id, enabled) "
                + "(select distinct g.id as user_group_id, f.id as music_folder_id, 'true' as enabled from user_group g, music_folder f)");

        template.execute(
                "alter table user add constraint fk_group_id foreign key (group_id) references user_group (id)");

        LOG.info("Database table 'user' was updated successfully.");
    }

    // Reset Access to default
    if (template.queryForInt("select count(*) from version where version = 112") == 0) {

        LOG.info("Updating database schema to version 112.");
        template.execute("insert into version values (112)");

        template.execute("delete from user_group_access");
        template.execute("insert into user_group_access (user_group_id, music_folder_id, enabled) "
                + "(select distinct g.id as user_group_id, f.id as music_folder_id, 'true' as enabled from user_group g, music_folder f)");
    }
}

From source file:com.fengjing.framework.shiro.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);

    System.out.println(CREATE_TABLES);

    //password is 'admin' 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>, <cryptographically strong randomly generated salt> (not the username!) )
    String query = "insert into users values ('admin', '" + new Sha256Hash("admin", "admin").toBase64() + "' )";
    jdbcTemplate.execute(query);
    log.debug("admin.");

    //password is 'user' SHA hashed and base64 encoded:
    query = "insert into users values ( 'user', '" + new Sha256Hash("user", "user").toBase64() + "' )";
    jdbcTemplate.execute(query);//from   w  w w . java 2 s .c o m
    log.debug("user.");

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

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

    query = "insert into roles_permissions values ( 'Admin', 'user:view')";
    jdbcTemplate.execute(query);
    log.debug("Adminuser:view");

    query = "insert into roles_permissions values ( 'Admin', 'user:edit')";
    jdbcTemplate.execute(query);
    log.debug("Adminuser:edit");

    query = "insert into roles_permissions values ( 'User', 'user:view')";
    jdbcTemplate.execute(query);
    log.debug("Useruser:view");

    query = "insert into user_roles values ( 'admin', 'Admin' )";
    jdbcTemplate.execute(query);
    query = "insert into user_roles values ( 'admin', 'User' )";
    jdbcTemplate.execute(query);
    log.debug("adminAdmin User");

    query = "insert into user_roles values ( 'user', 'User' )";
    jdbcTemplate.execute(query);
    log.debug("user User");
}

From source file:springbatch.test.jdbc.datasource.DataSourceInitializer.java

private void doExecuteScript(final Resource scriptResource) {
    if (scriptResource == null || !scriptResource.exists())
        return;//from  w  w w  .j av a2 s  .co m
    TransactionTemplate transactionTemplate = new TransactionTemplate(
            new DataSourceTransactionManager(dataSource));
    transactionTemplate.execute(new TransactionCallback() {

        @SuppressWarnings("unchecked")
        public Object doInTransaction(TransactionStatus status) {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            String[] scripts;
            try {
                scripts = StringUtils.delimitedListToStringArray(
                        stripComments(IOUtils.readLines(scriptResource.getInputStream())), ";");
            } catch (IOException e) {
                throw new BeanInitializationException("Cannot load script from [" + scriptResource + "]", e);
            }
            for (int i = 0; i < scripts.length; i++) {
                String script = scripts[i].trim();
                if (StringUtils.hasText(script)) {
                    try {
                        jdbcTemplate.execute(scripts[i]);
                    } catch (DataAccessException e) {
                        if (!script.toUpperCase().startsWith("DROP")) {
                            throw e;
                        }
                    }
                }
            }
            return null;
        }

    });

}

From source file:com.khs.test.jdbc.datasource.DSInitializer.java

private void doExecuteScript(final Resource scriptResource) {
    if (scriptResource == null || !scriptResource.exists())
        return;//from  w w w.j  av  a 2s . c  o  m
    TransactionTemplate transactionTemplate = new TransactionTemplate(
            new DataSourceTransactionManager(dataSource));
    transactionTemplate.execute(new TransactionCallback() {

        @SuppressWarnings("unchecked")
        public Object doInTransaction(TransactionStatus status) {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            String[] scripts;
            try {
                scripts = StringUtils.delimitedListToStringArray(
                        stripComments(IOUtils.readLines(scriptResource.getInputStream())), ";");
            } catch (IOException e) {
                throw new BeanInitializationException("Cannot load script from [" + scriptResource + "]", e);
            }
            for (int i = 0; i < scripts.length; i++) {
                String script = scripts[i].trim();
                if (StringUtils.hasText(script)) {
                    try {
                        jdbcTemplate.execute(script);
                    } catch (DataAccessException e) {
                        if (ignoreFailedDrop && script.toLowerCase().startsWith("drop")) {
                            logger.debug("DROP script failed (ignoring): " + script);
                        } else {
                            throw e;
                        }
                    }
                }
            }
            return null;
        }

    });

}

From source file:org.works.common.data.layer.datasource.InitializingDataSourceFactoryBean.java

private void doExecuteScript(final Resource scriptResource) {
    if (scriptResource == null || !scriptResource.exists())
        return;/* w w w.j  a  v  a  2s .c o m*/
    TransactionTemplate transactionTemplate = new TransactionTemplate(
            new DataSourceTransactionManager(dataSource));
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            String[] scripts;
            try {
                scripts = StringUtils.delimitedListToStringArray(
                        stripComments(IOUtils.readLines(scriptResource.getInputStream())), ";");
            } catch (IOException e) {
                throw new BeanInitializationException("Cannot load script from [" + scriptResource + "]", e);
            }
            for (int i = 0; i < scripts.length; i++) {
                String script = scripts[i].trim();
                if (StringUtils.hasText(script)) {
                    try {
                        jdbcTemplate.execute(script);
                    } catch (DataAccessException e) {
                        if (ignoreFailedDrop && script.toLowerCase().startsWith("drop")) {
                            logger.debug("DROP script failed (ignoring): " + script);
                        } else {
                            throw e;
                        }
                    }
                }
            }
            return null;
        }

    });

}

From source file:com.googlecode.jdbcproc.daofactory.impl.block.service.ParametersSetterBlockServiceImpl.java

private Map<String, Integer> createTypes(JdbcTemplate jdbcTemplate, final String tableName) {
    return jdbcTemplate.execute(new StatementCallback<Map<String, Integer>>() {
        public Map<String, Integer> doInStatement(Statement stmt) throws SQLException, DataAccessException {
            ResultSet rs = stmt.executeQuery("select * from " + tableName);
            try {
                ResultSetMetaData meta = rs.getMetaData();
                Map<String, Integer> types = new HashMap<String, Integer>();
                int count = meta.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    String name = meta.getColumnName(i);
                    int type = meta.getColumnType(i);
                    types.put(name, type);
                }//from  w w w  .j a v a 2  s . com
                return types;
            } finally {
                rs.close();
            }
        }
    });
}

From source file:net.sourceforge.subsonic.backend.dao.schema.Schema10.java

public void execute(JdbcTemplate template) {

    /*/* ww w .j  a  va  2  s  . c o m*/
    Example row 1:
            
    id: 123
    license_holder: sindre@activeobjects.no
    server_id: 972342834928656
    redirect_from: sindre
    redirect_to: http://23.45.123.56:8080/subsonic
    local_redirect_to: http://192.168.0.7:80/subsonic
    trial: false
    trial_expires: null
            
    Example row 2:
            
    id: 124
    license_holder: null
    server_id: 72121983567129
    redirect_from: joe
    redirect_to: http://232.21.18.14/subsonic
    local_redirect_to: http://192.168.0.7:80/subsonic
    trial: true
    trial_expires: 2010-01-13 05:34:17
     */

    if (!tableExists(template, "redirection")) {
        LOG.info("Database table 'redirection' not found.  Creating it.");
        template.execute("create cached table redirection (" + "id identity," + "license_holder varchar,"
                + "server_id varchar not null," + "redirect_from varchar not null,"
                + "redirect_to varchar not null," + "trial boolean not null," + "trial_expires datetime,"
                + "last_updated datetime," + "last_read datetime," + "unique(redirect_from))");
        template.execute("create index idx_redirection_redirect_from on redirection(redirect_from)");
        template.execute("create index idx_redirection_server_id on redirection(server_id)");

        createRedirection(template, "demo", "http://subsonic.org/demo");

        LOG.info("Database table 'redirection' was created successfully.");
    }

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

    if (!columnExists(template, "read_count", "redirection")) {
        LOG.info("Database column 'redirection.read_count' not found.  Creating it.");
        template.execute("alter table redirection " + "add read_count int default 0 not null");
        LOG.info("Database column 'redirection.read_count' was added successfully.");
    }
}