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:io.pivotal.spring.xd.jdbcgpfdist.LoadIT.java

@Test
public void testInsert() {
    context.register(Config1.class, CommonConfig.class);
    context.refresh();/* w  ww . jav  a 2  s  . c  o  m*/
    JdbcTemplate template = context.getBean(JdbcTemplate.class);
    String drop = "DROP TABLE IF EXISTS AbstractLoadTests;";
    String create = "CREATE TABLE AbstractLoadTests (data text);";
    template.execute(drop);
    template.execute(create);

    List<String> data = new ArrayList<String>();
    for (int i = 0; i < 10; i++) {
        data.add("DATA" + i + "\n");
    }

    broadcastData(data);

    GreenplumLoad greenplumLoad = context.getBean(GreenplumLoad.class);
    greenplumLoad.load();

    List<Map<String, Object>> queryForList = template.queryForList("SELECT * from AbstractLoadTests;");
    assertThat(queryForList, notNullValue());
    assertThat(queryForList.size(), is(10));
    List<String> queryData = new ArrayList<String>();
    for (int i = 0; i < 10; i++) {
        queryData.add((String) queryForList.get(i).get("data"));
    }
    assertThat(queryData, containsInAnyOrder("DATA0", "DATA1", "DATA2", "DATA3", "DATA4", "DATA5", "DATA6",
            "DATA7", "DATA8", "DATA9"));
}

From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema35.java

@Override
public void execute(JdbcTemplate template) {

    if (template.queryForInt("select count(*) from version where version = 11") == 0) {
        LOG.info("Updating database schema to version 11.");
        template.execute("insert into version values (11)");
    }/*from w  ww. ja v a2  s.c om*/

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

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

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

    if (!tableExists(template, "system_avatar")) {
        LOG.info("Database table 'system_avatar' not found.  Creating it.");
        template.execute("create table system_avatar (" + "id identity," + "name varchar,"
                + "created_date datetime not null," + "mime_type varchar not null," + "width int not null,"
                + "height int not null," + "data binary not null)");
        LOG.info("Database table 'system_avatar' was created successfully.");
    }

    for (String avatar : AVATARS) {
        createAvatar(template, avatar);
    }

    if (!tableExists(template, "custom_avatar")) {
        LOG.info("Database table 'custom_avatar' not found.  Creating it.");
        template.execute("create table custom_avatar (" + "id identity," + "name varchar,"
                + "created_date datetime not null," + "mime_type varchar not null," + "width int not null,"
                + "height int not null," + "data binary not null," + "username varchar not null,"
                + "foreign key (username) references user(username) on delete cascade)");
        LOG.info("Database table 'custom_avatar' was created successfully.");
    }

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

    if (!columnExists(template, "system_avatar_id", "user_settings")) {
        LOG.info("Database column 'user_settings.system_avatar_id' not found.  Creating it.");
        template.execute("alter table user_settings add system_avatar_id int");
        template.execute(
                "alter table user_settings add foreign key (system_avatar_id) references system_avatar(id)");
        LOG.info("Database column 'user_settings.system_avatar_id' was added successfully.");
    }

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

From source file:com.talkingdata.orm.tool.ORMGenerateAction.java

@Override
public void actionPerformed(AnActionEvent e) {
    Project project = e.getRequiredData(CommonDataKeys.PROJECT);
    ORMConfig config = ORMConfig.getInstance(project);

    // 1. validate input parameter
    if (!validateConfig(project, config)) {
        return;/*from   w w w. j  a va  2 s  . c om*/
    }

    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl(
            String.format("jdbc:mysql://%s:%s/%s", config.getIp(), config.getPort(), config.getDatabase()));
    dataSource.setUsername(config.getUser());
    dataSource.setPassword(config.getPassword());

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    // 2. validate database is connected
    try {
        jdbcTemplate.execute("SELECT 1");
    } catch (Exception exception) {
        Messages.showWarningDialog(project, "The database is not connected.", "Warning");
        return;
    }

    File resourceDirectory = new File(project.getBasePath(), "/src/main/resources/mybatis");
    if (!resourceDirectory.exists()) {
        resourceDirectory.mkdirs();
    }

    File packageDirectory = new File(project.getBasePath(),
            "/src/main/java/" + config.getPackageName().replaceAll("\\.", "/"));
    if (!packageDirectory.exists()) {
        packageDirectory.mkdirs();
    }

    Properties p = new Properties();
    p.setProperty(RuntimeConstants.RUNTIME_LOG_LOGSYSTEM_CLASS,
            "org.apache.velocity.runtime.log.Log4JLogChute");
    Velocity.init(p);

    // 3. query all table
    SqlParser sqlParser = new SqlParser();

    try {
        for (ClassDefinition cls : sqlParser.getTables(jdbcTemplate, config.getDatabase(),
                config.getPackageName())) {
            Map<String, Object> map = new HashMap<>(1);
            map.put("cls", cls);

            File domainDirectory = new File(packageDirectory, "domain");
            if (!domainDirectory.exists()) {
                domainDirectory.mkdirs();
            }
            File clsFile = new File(domainDirectory, cls.getClassName() + ".java");
            if (!clsFile.exists()) {
                clsFile.createNewFile();
            }
            writeFile("com/talkingdata/orm/tool/vm/class.vm", map, new FileWriter(clsFile));

            File daoDirectory = new File(packageDirectory, "dao");
            if (!daoDirectory.exists()) {
                daoDirectory.mkdirs();
            }
            File daoFile = new File(daoDirectory, cls.getClassName() + "Dao.java");
            if (!daoFile.exists()) {
                daoFile.createNewFile();
            }
            writeFile("com/talkingdata/orm/tool/vm/dao.vm", map, new FileWriter(daoFile));

            File mapperFile = new File(resourceDirectory, cls.getClassInstanceName() + ".xml");
            if (!mapperFile.exists()) {
                mapperFile.createNewFile();
            }
            writeFile("com/talkingdata/orm/tool/vm/mapper.vm", map, new FileWriter(mapperFile));
        }
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    }

}

From source file:de.hybris.platform.core.TenantRestartTest.java

private void dropTable(final DataSource dataSource, final String tableName) {

    try {//from ww w.  j av a 2s.  c  om
        final JdbcTemplate create = new JdbcTemplate(dataSource);
        create.execute("DROP TABLE " + tableName);
    } catch (final DataAccessException e) {
        if (LOG.isDebugEnabled()) {
            LOG.debug(e);
        }
    }
}

From source file:io.pivotal.spring.xd.jdbcgpfdist.LoadIT.java

@Test
public void testUpdate() {
    context.register(Config2.class, CommonConfig.class);
    context.refresh();//from  w ww. j av  a2 s  .  co m
    JdbcTemplate template = context.getBean(JdbcTemplate.class);
    String drop = "DROP TABLE IF EXISTS AbstractLoadTests;";
    String create = "CREATE TABLE AbstractLoadTests (col1 text, col2 text);";
    template.execute(drop);
    template.execute(create);

    List<String> data = new ArrayList<String>();
    for (int i = 0; i < 10; i++) {
        template.execute("insert into AbstractLoadTests values('DATA" + i + "', 'DATA');");
        data.add("DATA" + i + "\tDATA" + i + "\n");
    }

    broadcastData(data);

    GreenplumLoad greenplumLoad = context.getBean(GreenplumLoad.class);
    greenplumLoad.load();

    List<Map<String, Object>> queryForList = template.queryForList("SELECT * from AbstractLoadTests;");
    assertThat(queryForList, notNullValue());
    assertThat(queryForList.size(), is(10));
    for (int i = 0; i < 10; i++) {
        assertThat(queryForList.get(i).get("col2"), is(queryForList.get(i).get("col1")));
    }
}

From source file:de.hybris.platform.util.database.TableNameDatabaseMetaDataCallbackTest.java

private void dropTable(final DataSource dataSource, final String tableName) {

    final JdbcTemplate create = new JdbcTemplate(dataSource);

    try {/*from   ww  w.  ja  va  2 s  .  c  o m*/
        create.execute("DROP TABLE " + tableName);
    } catch (final DataAccessException e) {
        if (LOG.isDebugEnabled()) {
            LOG.debug(e);
        }
    }
}

From source file:com.demo.camelrestsqldemo.DatabaseBean.java

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

    String sql = "create table orders (\n" + "  id integer primary key,\n" + "  item varchar(10),\n"
            + "  amount integer,\n" + "  description varchar(30),\n" + "  processed boolean\n" + ")";

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

    try {//from ww w  .  j av  a 2s.  c o m
        jdbc.execute("drop table orders");
    } catch (Throwable e) {
        // ignore
    }

    jdbc.execute(sql);

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

From source file:io.pivotal.spring.xd.jdbcgpfdist.LoadIT.java

@Test
public void testUpdateMultiColumns() {
    context.register(Config3.class, CommonConfig.class);
    context.refresh();/*from  www. ja va  2 s.c o m*/
    JdbcTemplate template = context.getBean(JdbcTemplate.class);
    String drop = "DROP TABLE IF EXISTS AbstractLoadTests;";
    String create = "CREATE TABLE AbstractLoadTests (col1 text, col2 text, col3 text);";
    template.execute(drop);
    template.execute(create);

    List<String> data = new ArrayList<String>();
    for (int i = 0; i < 10; i++) {
        template.execute("insert into AbstractLoadTests values('DATA" + i + "', 'DATA', 'DATA');");
        data.add("DATA" + i + "\tDATA" + i + "\tDATA" + i + "\n");
    }

    broadcastData(data);

    GreenplumLoad greenplumLoad = context.getBean(GreenplumLoad.class);
    greenplumLoad.load();

    List<Map<String, Object>> queryForList = template.queryForList("SELECT * from AbstractLoadTests;");
    assertThat(queryForList, notNullValue());
    assertThat(queryForList.size(), is(10));
    for (int i = 0; i < 10; i++) {
        assertThat(queryForList.get(i).get("col2"), is(queryForList.get(i).get("col1")));
        assertThat(queryForList.get(i).get("col3"), is(queryForList.get(i).get("col1")));
    }
}

From source file:DolphinPostDaoTest.java

@Before
public void setUp() {
    ApplicationContext ctx = new ClassPathXmlApplicationContext("test-applicationContext.xml");
    dao = ctx.getBean("jdbcDao", DolphinPostDao.class);
    JdbcTemplate cleaner = ctx.getBean("jdbcTemplateBean", JdbcTemplate.class);
    cleaner.execute("DELETE FROM Comments ");
    cleaner.execute("Truncate Table Comments ");
    cleaner.execute("DELETE FROM HashtagsPosts WHERE 1=1");
    cleaner.execute("DELETE FROM Hashtags WHERE 1=1");
    cleaner.execute("DELETE FROM Posts WHERE 1=1");
    cleaner.execute("DELETE FROM Pages");
}

From source file:com.smtp.webservice.DatabaseBean.java

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

    String sql = "create table orders (\n" + "  id integer primary key,\n" + "  item varchar(10),\n"
            + "  amount varchar(5),\n" + "  description varchar(30),\n" + "  processed boolean\n" + ")";

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

    try {//www  .j  a  v  a 2 s .  c o m
        jdbc.execute("drop table orders");
    } catch (Throwable e) {
        // ignore
    }

    jdbc.execute(sql);

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