List of usage examples for org.springframework.jdbc.core JdbcTemplate execute
@Override
public void execute(final String sql) throws DataAccessException
From source file:de.hybris.platform.core.TenantRestartTest.java
private void createTable(final JdbcTemplate template, final String tableName) { if (LOG.isDebugEnabled()) { LOG.debug("Create table " + tableName + " for tenant " + Registry.getCurrentTenantNoFallback()); }/*from w ww. jav a2 s . c o m*/ template.execute("CREATE TABLE " + tableName + " ( ID VARCHAR(10))"); }
From source file:com.sg.capstone.dao.tests.BlogDaoDbImplTests.java
@Before public void setUp() { // Ask Spring for my DAO ApplicationContext ctx = new ClassPathXmlApplicationContext("test-applicationContext.xml"); blogDao = (BlogDao) ctx.getBean("BlogDao"); tagDao = (TagDao) ctx.getBean("TagDao"); catDao = (CategoryDao) ctx.getBean("CategoryDao"); // Grab a JdbcTemplate to use for cleaning up JdbcTemplate cleaner = (JdbcTemplate) ctx.getBean("jdbcTemplate"); cleaner.execute("truncate blogPost"); cleaner.execute("truncate categories"); cleaner.execute("truncate tags"); cleaner.execute("truncate blogPost_categories"); cleaner.execute("truncate blogPost_tags"); BlogPost blogPost1 = new BlogPost(); blogPost1.setDate("2015-10-01 00:00:00"); blogPost1.setTitle("Blog Post 1"); blogPost1.setImagePath("https://upload.wikimedia.org/wikipedia/en/1/14/Space_jam.jpg"); blogPost1.setBlogText("Blog Text 1"); BlogPost blogPost2 = new BlogPost(); blogPost2.setDate("2015-10-02 10:00:00"); blogPost2.setTitle("Blog Post 2"); blogPost2.setImagePath("https://upload.wikimedia.org/wikipedia/en/1/14/Space_jam.jpg"); blogPost2.setBlogText("Blog Text 2"); BlogPost blogPost3 = new BlogPost(); blogPost3.setDate("2016-12-03 20:00:00"); blogPost3.setTitle("Blog Post 3"); blogPost3.setImagePath("https://upload.wikimedia.org/wikipedia/en/1/14/Space_jam.jpg"); blogPost3.setBlogText("Blog Text 3"); Tag tag1 = new Tag(); tag1.setName("tag1"); tagDao.addTag(tag1);//from w w w. j a va 2 s . c o m Tag tag2 = new Tag(); tag2.setName("tag2"); tagDao.addTag(tag2); Tag tag3 = new Tag(); tag3.setName("tag3"); tagDao.addTag(tag3); Category cat1 = new Category(); cat1.setName("cat1"); catDao.addCategory(cat1); Category cat2 = new Category(); cat2.setName("cat2"); catDao.addCategory(cat2); Category cat3 = new Category(); cat3.setName("cat3"); catDao.addCategory(cat3); ArrayList<Integer> blogPost1TagIds = new ArrayList<>(); blogPost1TagIds.add(1); blogPost1.setTagIds(blogPost1TagIds); ArrayList<Integer> blogPost2TagIds = new ArrayList<>(); blogPost2TagIds.add(1); blogPost2TagIds.add(2); blogPost2.setTagIds(blogPost2TagIds); ArrayList<Integer> blogPost3TagIds = new ArrayList<>(); blogPost3TagIds.add(1); blogPost3TagIds.add(2); blogPost3TagIds.add(3); blogPost3.setTagIds(blogPost3TagIds); ArrayList<Integer> blogPost1CategoryIds = new ArrayList<>(); blogPost1CategoryIds.add(1); blogPost1.setCategoryIds(blogPost1CategoryIds); ArrayList<Integer> blogPost2CategoryIds = new ArrayList<>(); blogPost2CategoryIds.add(1); blogPost2CategoryIds.add(2); blogPost2.setCategoryIds(blogPost2CategoryIds); ArrayList<Integer> blogPost3CategoryIds = new ArrayList<>(); blogPost3CategoryIds.add(1); blogPost3CategoryIds.add(2); blogPost3CategoryIds.add(3); blogPost3.setCategoryIds(blogPost3CategoryIds); blogPostsForTesting[0] = blogPost1; blogPostsForTesting[1] = blogPost2; blogPostsForTesting[2] = blogPost3; }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema47.java
@Override public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 20") == 0) { LOG.info("Updating database schema to version 20."); template.execute("insert into version values (20)"); }/*from w w w. ja v a 2 s. co m*/ if (!tableExists(template, "media_file")) { LOG.info("Database table 'media_file' not found. Creating it."); template.execute("create cached table media_file (" + "id identity," + "path varchar not null," + "folder varchar," + "type varchar not null," + "format varchar," + "title varchar," + "album varchar," + "artist varchar," + "album_artist varchar," + "disc_number int," + "track_number int," + "year int," + "genre varchar," + "bit_rate int," + "variable_bit_rate boolean not null," + "duration_seconds int," + "file_size bigint," + "width int," + "height int," + "cover_art_path varchar," + "parent_path varchar," + "play_count int not null," + "last_played datetime," + "comment varchar," + "created datetime not null," + "changed datetime not null," + "last_scanned datetime not null," + "children_last_updated datetime not null," + "present boolean not null," + "version int not null," + "unique (path))"); template.execute("create index idx_media_file_path on media_file(path)"); template.execute("create index idx_media_file_parent_path on media_file(parent_path)"); template.execute("create index idx_media_file_type on media_file(type)"); template.execute("create index idx_media_file_album on media_file(album)"); template.execute("create index idx_media_file_artist on media_file(artist)"); template.execute("create index idx_media_file_album_artist on media_file(album_artist)"); template.execute("create index idx_media_file_present on media_file(present)"); template.execute("create index idx_media_file_genre on media_file(genre)"); template.execute("create index idx_media_file_play_count on media_file(play_count)"); template.execute("create index idx_media_file_created on media_file(created)"); template.execute("create index idx_media_file_last_played on media_file(last_played)"); LOG.info("Database table 'media_file' was created successfully."); } if (!tableExists(template, "artist")) { LOG.info("Database table 'artist' not found. Creating it."); template.execute("create cached table artist (" + "id identity," + "name varchar not null," + "cover_art_path varchar," + "album_count int default 0 not null," + "last_scanned datetime not null," + "present boolean not null," + "unique (name))"); template.execute("create index idx_artist_name on artist(name)"); template.execute("create index idx_artist_present on artist(present)"); LOG.info("Database table 'artist' was created successfully."); } if (!tableExists(template, "album")) { LOG.info("Database table 'album' not found. Creating it."); template.execute("create cached table album (" + "id identity," + "path varchar not null," + "name varchar not null," + "artist varchar not null," + "song_count int default 0 not null," + "duration_seconds int default 0 not null," + "cover_art_path varchar," + "play_count int default 0 not null," + "last_played datetime," + "comment varchar," + "created datetime not null," + "last_scanned datetime not null," + "present boolean not null," + "unique (artist, name))"); template.execute("create index idx_album_artist_name on album(artist, name)"); template.execute("create index idx_album_play_count on album(play_count)"); template.execute("create index idx_album_last_played on album(last_played)"); template.execute("create index idx_album_present on album(present)"); LOG.info("Database table 'album' was created successfully."); } // Added in 4.7.beta3 if (!rowExists(template, "table_name='ALBUM' and column_name='NAME' and ordinal_position=1", "information_schema.system_indexinfo")) { template.execute("create index idx_album_name on album(name)"); } if (!tableExists(template, "starred_media_file")) { LOG.info("Database table 'starred_media_file' not found. Creating it."); template.execute("create table starred_media_file (" + "id identity," + "media_file_id int not null," + "username varchar not null," + "created datetime not null," + "foreign key (media_file_id) references media_file(id) on delete cascade," + "foreign key (username) references user(username) on delete cascade," + "unique (media_file_id, username))"); template.execute( "create index idx_starred_media_file_media_file_id on starred_media_file(media_file_id)"); template.execute("create index idx_starred_media_file_username on starred_media_file(username)"); LOG.info("Database table 'starred_media_file' was created successfully."); } if (!tableExists(template, "starred_album")) { LOG.info("Database table 'starred_album' not found. Creating it."); template.execute("create table starred_album (" + "id identity," + "album_id int not null," + "username varchar not null," + "created datetime not null," + "foreign key (album_id) references album(id) on delete cascade," + "foreign key (username) references user(username) on delete cascade," + "unique (album_id, username))"); template.execute("create index idx_starred_album_album_id on starred_album(album_id)"); template.execute("create index idx_starred_album_username on starred_album(username)"); LOG.info("Database table 'starred_album' was created successfully."); } if (!tableExists(template, "starred_artist")) { LOG.info("Database table 'starred_artist' not found. Creating it."); template.execute("create table starred_artist (" + "id identity," + "artist_id int not null," + "username varchar not null," + "created datetime not null," + "foreign key (artist_id) references artist(id) on delete cascade," + "foreign key (username) references user(username) on delete cascade," + "unique (artist_id, username))"); template.execute("create index idx_starred_artist_artist_id on starred_artist(artist_id)"); template.execute("create index idx_starred_artist_username on starred_artist(username)"); LOG.info("Database table 'starred_artist' was created successfully."); } if (!tableExists(template, "playlist")) { LOG.info("Database table 'playlist' not found. Creating it."); template.execute("create table playlist (" + "id identity," + "username varchar not null," + "is_public boolean not null," + "name varchar not null," + "comment varchar," + "file_count int default 0 not null," + "duration_seconds int default 0 not null," + "created datetime not null," + "changed datetime not null," + "foreign key (username) references user(username) on delete cascade)"); LOG.info("Database table 'playlist' was created successfully."); } if (!columnExists(template, "imported_from", "playlist")) { LOG.info("Database column 'playlist.imported_from' not found. Creating it."); template.execute("alter table playlist add imported_from varchar"); LOG.info("Database column 'playlist.imported_from' was added successfully."); } if (!tableExists(template, "playlist_file")) { LOG.info("Database table 'playlist_file' not found. Creating it."); template.execute("create cached table playlist_file (" + "id identity," + "playlist_id int not null," + "media_file_id int not null," + "foreign key (playlist_id) references playlist(id) on delete cascade," + "foreign key (media_file_id) references media_file(id) on delete cascade)"); LOG.info("Database table 'playlist_file' was created successfully."); } if (!tableExists(template, "playlist_user")) { LOG.info("Database table 'playlist_user' not found. Creating it."); template.execute("create table playlist_user (" + "id identity," + "playlist_id int not null," + "username varchar not null," + "unique(playlist_id, username)," + "foreign key (playlist_id) references playlist(id) on delete cascade," + "foreign key (username) references user(username) on delete cascade)"); LOG.info("Database table 'playlist_user' was created successfully."); } if (!tableExists(template, "bookmark")) { LOG.info("Database table 'bookmark' not found. Creating it."); template.execute("create table bookmark (" + "id identity," + "media_file_id int not null," + "position_millis bigint not null," + "username varchar not null," + "comment varchar," + "created datetime not null," + "changed datetime not null," + "foreign key (media_file_id) references media_file(id) on delete cascade," + "foreign key (username) references user(username) on delete cascade," + "unique (media_file_id, username))"); template.execute("create index idx_bookmark_media_file_id on bookmark(media_file_id)"); template.execute("create index idx_bookmark_username on bookmark(username)"); LOG.info("Database table 'bookmark' was created successfully."); } }
From source file:edu.mayo.cts2.uriresolver.dao.DAOUtiltities.java
public static boolean importMySQLDataToH2Database(DataSource ds) { JdbcTemplate jdbcTemplateObject; jdbcTemplateObject = new JdbcTemplate(ds); StringBuffer sqlBuffer = new StringBuffer(); BufferedReader bufferedReader = null; Reader reader = null;/*from w ww . j ava2s . com*/ try { InputStream in = ResolveURI.class.getResourceAsStream("/uriresolver.sql"); reader = new InputStreamReader(in, "UTF-8"); bufferedReader = new BufferedReader(reader); while (bufferedReader.ready()) { String line = bufferedReader.readLine().trim(); if (isSQLCode(line)) { sqlBuffer.append(convertToH2(line)); } } bufferedReader.close(); reader.close(); } catch (IOException e) { logger.error("Error while importing data to in memory database: " + e.getMessage()); return false; } finally { try { if (bufferedReader != null) { bufferedReader.close(); } if (reader != null) { reader.close(); } } catch (IOException ex) { logger.error("Error while closing access to in memory database: " + ex.getMessage()); return true; } } jdbcTemplateObject.execute(sqlBuffer.toString()); return true; }
From source file:org.cloudfoundry.identity.uaa.db.postgresql.V1_5_4__NormalizeTableAndColumnNames.java
@Override public void migrate(JdbcTemplate jdbcTemplate) throws Exception { logger.info("[V1_5_4] Running SQL: " + colQuery); List<ColumnInfo> columns = jdbcTemplate.query(colQuery, new ColumnMapper()); for (ColumnInfo column : columns) { if (processColumn(column)) { String sql = "ALTER TABLE " + column.tableName + " RENAME \"" + column.columnName + "\" TO \"" + column.columnName.toLowerCase() + "\""; logger.info("Renaming column: [" + sql + "]"); jdbcTemplate.execute(sql); }// w w w . jav a 2s . c o m } }
From source file:commonline.query.sql.RecordParserDataSource.java
public void afterPropertiesSet() throws Exception { delegate = createDelegate();/*from w ww. j a va 2s . c o m*/ delegate.setUrl(url); delegate.setPassword(password); delegate.setUsername(username); delegate.setDriverClassName(driverClassName); JdbcTemplate template = new JdbcTemplate(delegate); for (RecordParserImpl parser : parsers) { CommonLineRecordLayoutResolver resolver = (CommonLineRecordLayoutResolver) parser .getRecordLayoutResolver(); for (Object obj : resolver.getRecordLayouts()) { RecordLayoutTableInfo tableInfo = layoutTableInfoFactory.build(parser, (RecordLayout) obj); template.execute(sqlTableFactory.build(tableInfo)); tableInfos.add(tableInfo); } } }
From source file:org.owasp.proxy.http.dao.JdbcMessageDAO.java
public void createTables() throws DataAccessException { JdbcTemplate template = getJdbcTemplate(); try {/* ww w . ja v a2 s.co m*/ template.execute(CREATE_CONTENTS_TABLE); template.execute(CREATE_HEADERS_TABLE); template.execute(CREATE_REQUESTS_TABLE); template.execute(CREATE_RESPONSES_TABLE); template.execute(CREATE_CONVERSATIONS_TABLE); } catch (BadSqlGrammarException e) { e.printStackTrace(); // FIXME: get database metadata, and see if the tables already exist } }
From source file:com.alibaba.otter.canal.example.db.dialect.AbstractDbDialect.java
public AbstractDbDialect(final JdbcTemplate jdbcTemplate, LobHandler lobHandler) { this.jdbcTemplate = jdbcTemplate; this.lobHandler = lobHandler; // ?transction this.transactionTemplate = new TransactionTemplate(); transactionTemplate.setTransactionManager(new DataSourceTransactionManager(jdbcTemplate.getDataSource())); transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); // ??//from w w w. ja va 2 s.c o m jdbcTemplate.execute(new ConnectionCallback() { public Object doInConnection(Connection c) throws SQLException, DataAccessException { DatabaseMetaData meta = c.getMetaData(); databaseName = meta.getDatabaseProductName(); databaseMajorVersion = meta.getDatabaseMajorVersion(); databaseMinorVersion = meta.getDatabaseMinorVersion(); return null; } }); initTables(jdbcTemplate); }
From source file:com.swcguild.capstoneproject.dao.BlogDaoDbImplTest.java
@Before public void setUp() { ApplicationContext ctx = new ClassPathXmlApplicationContext("test-applicationContext.xml"); dao = (BlogDao) ctx.getBean("BlogDao"); JdbcTemplate cleaner = (JdbcTemplate) ctx.getBean("jdbcTemplate"); cleaner.execute("delete from posts"); cleaner.execute("delete from users"); cleaner.execute("delete from authorities"); cleaner.execute("delete from tags"); cleaner.execute("delete from pinposts"); }