List of usage examples for org.springframework.jdbc.core JdbcTemplate execute
@Override
public void execute(final String sql) throws DataAccessException
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema25.java
public void execute(JdbcTemplate template) { if (!tableExists(template, "version")) { // Increase data file limit. See http://www.hsqldb.org/doc/guide/ch04.html template.execute("set property \"hsqldb.cache_file_scale\" 8"); LOG.info("Database table 'version' not found. Creating it."); template.execute("create table version (version int not null)"); template.execute("insert into version values (1)"); LOG.info("Database table 'version' was created successfully."); }/* w w w .j a v a 2 s. c o m*/ if (!tableExists(template, "role")) { LOG.info("Database table 'role' not found. Creating it."); template.execute( "create table role (" + "id int not null," + "name varchar not null," + "primary key (id))"); template.execute("insert into role values (1, 'admin')"); template.execute("insert into role values (2, 'download')"); template.execute("insert into role values (3, 'upload')"); template.execute("insert into role values (4, 'playlist')"); template.execute("insert into role values (5, 'coverart')"); LOG.info("Database table 'role' was created successfully."); } if (!tableExists(template, "user")) { LOG.info("Database table 'user' not found. Creating it."); template.execute("create table user (" + "username varchar not null," + "password varchar not null," + "primary key (username))"); template.execute("insert into user values ('admin', 'admin')"); LOG.info("Database table 'user' was created successfully."); } if (!tableExists(template, "user_role")) { LOG.info("Database table 'user_role' not found. Creating it."); template.execute("create table user_role (" + "username varchar not null," + "role_id int not null," + "primary key (username, role_id)," + "foreign key (username) references user(username)," + "foreign key (role_id) references role(id))"); template.execute("insert into user_role values ('admin', 1)"); template.execute("insert into user_role values ('admin', 2)"); template.execute("insert into user_role values ('admin', 3)"); template.execute("insert into user_role values ('admin', 4)"); template.execute("insert into user_role values ('admin', 5)"); LOG.info("Database table 'user_role' was created successfully."); } }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema37.java
@Override public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 13") == 0) { LOG.info("Updating database schema to version 13."); template.execute("insert into version values (13)"); }//from w ww.java 2s. c o m if (template.queryForInt("select count(*) from role where id = 9") == 0) { LOG.info("Role 'settings' not found in database. Creating it."); template.execute("insert into role values (9, 'settings')"); template.execute("insert into user_role select distinct u.username, 9 from user u"); LOG.info("Role 'settings' was created successfully."); } if (template.queryForInt("select count(*) from role where id = 10") == 0) { LOG.info("Role 'jukebox' not found in database. Creating it."); template.execute("insert into role values (10, 'jukebox')"); template.execute("insert into user_role " + "select distinct u.username, 10 from user u, user_role ur " + "where u.username = ur.username and ur.role_id = 1"); LOG.info("Role 'jukebox' was created successfully."); } if (!columnExists(template, "changed", "music_folder")) { LOG.info("Database column 'music_folder.changed' not found. Creating it."); template.execute("alter table music_folder add changed datetime default 0 not null"); LOG.info("Database column 'music_folder.changed' was added successfully."); } if (!columnExists(template, "changed", "internet_radio")) { LOG.info("Database column 'internet_radio.changed' not found. Creating it."); template.execute("alter table internet_radio add changed datetime default 0 not null"); LOG.info("Database column 'internet_radio.changed' was added successfully."); } if (!columnExists(template, "changed", "user_settings")) { LOG.info("Database column 'user_settings.changed' not found. Creating it."); template.execute("alter table user_settings add changed datetime default 0 not null"); LOG.info("Database column 'user_settings.changed' was added successfully."); } }
From source file:org.awesomeagile.dao.testing.TestDatabase.java
private void executeScript(Optional<String> databaseName, final String script) { JdbcTemplate jdbcTemplate = databaseName.isPresent() ? jdbcTemplate(databaseName.get()) : jdbcTemplate(); jdbcTemplate.execute(new ConnectionCallback<Object>() { @Override//from w w w . j a v a2s .c om public Object doInConnection(Connection con) throws SQLException, DataAccessException { ScriptUtils.executeSqlScript(con, new ClassPathResource(script)); return null; } }); }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema32.java
public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 8") == 0) { LOG.info("Updating database schema to version 8."); template.execute("insert into version values (8)"); }/* w ww . ja v a 2s .co m*/ if (!columnExists(template, "show_now_playing", "user_settings")) { LOG.info("Database column 'user_settings.show_now_playing' not found. Creating it."); template.execute("alter table user_settings add show_now_playing boolean default true not null"); LOG.info("Database column 'user_settings.show_now_playing' was added successfully."); } if (!columnExists(template, "selected_music_folder_id", "user_settings")) { LOG.info("Database column 'user_settings.selected_music_folder_id' not found. Creating it."); template.execute("alter table user_settings add selected_music_folder_id int default -1 not null"); LOG.info("Database column 'user_settings.selected_music_folder_id' was added successfully."); } if (!tableExists(template, "podcast_channel")) { LOG.info("Database table 'podcast_channel' not found. Creating it."); template.execute( "create table podcast_channel (" + "id identity," + "url varchar not null," + "title varchar," + "description varchar," + "status varchar not null," + "error_message varchar)"); LOG.info("Database table 'podcast_channel' was created successfully."); } if (!tableExists(template, "podcast_episode")) { LOG.info("Database table 'podcast_episode' not found. Creating it."); template.execute("create table podcast_episode (" + "id identity," + "channel_id int not null," + "url varchar not null," + "path varchar," + "title varchar," + "description varchar," + "publish_date datetime," + "duration varchar," + "bytes_total bigint," + "bytes_downloaded bigint," + "status varchar not null," + "error_message varchar," + "foreign key (channel_id) references podcast_channel(id) on delete cascade)"); LOG.info("Database table 'podcast_episode' was created successfully."); } if (template.queryForInt("select count(*) from role where id = 7") == 0) { LOG.info("Role 'podcast' not found in database. Creating it."); template.execute("insert into role values (7, 'podcast')"); template.execute("insert into user_role " + "select distinct u.username, 7 from user u, user_role ur " + "where u.username = ur.username and ur.role_id = 1"); LOG.info("Role 'podcast' was created successfully."); } }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema52.java
@Override public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 24") == 0) { LOG.info("Updating database schema to version 24."); template.execute("insert into version values (24)"); }/*ww w .j av a 2s . com*/ if (!tableExists(template, "music_folder_user")) { LOG.info("Database table 'music_folder_user' not found. Creating it."); template.execute("create table music_folder_user (" + "music_folder_id int not null," + "username varchar not null, " + "foreign key (username) references user(username) on delete cascade, " + "foreign key (music_folder_id) references music_folder(id) on delete cascade)"); template.execute("create index idx_music_folder_user_username on music_folder_user(username)"); template.execute( "insert into music_folder_user select music_folder.id, user.username from music_folder, user"); LOG.info("Database table 'music_folder_user' was created successfully."); } if (!columnExists(template, "folder_id", "album")) { LOG.info("Database column 'album.folder_id' not found. Creating it."); template.execute("alter table album add folder_id int"); LOG.info("Database column 'album.folder_id' was added successfully."); } if (!tableExists(template, "play_queue")) { LOG.info("Database table 'play_queue' not found. Creating it."); template.execute("create table play_queue (" + "id identity," + "username varchar not null," + "current int," + "position_millis bigint," + "changed datetime not null," + "changed_by varchar not null," + "foreign key (username) references user(username) on delete cascade)"); LOG.info("Database table 'play_queue' was created successfully."); } if (!tableExists(template, "play_queue_file")) { LOG.info("Database table 'play_queue_file' not found. Creating it."); template.execute("create cached table play_queue_file (" + "id identity," + "play_queue_id int not null," + "media_file_id int not null," + "foreign key (play_queue_id) references play_queue(id) on delete cascade," + "foreign key (media_file_id) references media_file(id) on delete cascade)"); LOG.info("Database table 'play_queue_file' was created successfully."); } }
From source file:com.github.viktornar.migration.schema.hsqldb.SQLSchema1.java
@Override public void execute(JdbcTemplate template) { assert template != null; if (!tableExists(template, "version")) { logger.info("Database table 'version' not found. Creating it."); template.execute("CREATE TABLE version (version INT NOT NULL)"); template.execute("INSERT INTO version VALUES (1)"); logger.info("Database table 'version' was created successfully."); }/* ww w . j a v a 2 s. c o m*/ if (!tableExists(template, "extent")) { logger.info("Database table 'extent' not found. Creating it."); template.execute("CREATE TABLE extent (" + " id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY," + " xmin DOUBLE," + " ymin DOUBLE," + " xmax DOUBLE," + " ymax DOUBLE)"); logger.info("Database table 'extent' was created successfully."); } if (!tableExists(template, "atlas")) { logger.info("Database table 'atlas' not found. Creating it."); template.execute("CREATE TABLE atlas (" + " id VARCHAR(10) NOT NULL PRIMARY KEY," + " atlas_name VARCHAR(50)," + " atlas_folder VARCHAR(1024)," + " orientation VARCHAR(10)," + " size VARCHAR(10)," + " zoom INT," + " columns INT," + " rows INT," + " progress INT," + " extent_id INT NOT NULL," + " CONSTRAINT fk_atlas_extent FOREIGN KEY(extent_id) REFERENCES extent(ID) ON DELETE CASCADE)"); logger.info("Database table 'atlas' was created successfully."); } }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema53.java
@Override public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 25") == 0) { LOG.info("Updating database schema to version 25."); template.execute("insert into version values (25)"); }/*from w w w. ja va 2s. c o m*/ if (!rowExists(template, "table_name='PODCAST_EPISODE' and column_name='URL' and ordinal_position=1", "information_schema.system_indexinfo")) { template.execute("create index idx_podcast_episode_url on podcast_episode(url)"); LOG.info("Created index for podcast_episode.url"); } if (!columnExists(template, "default_album_list", "user_settings")) { LOG.info("Database column 'user_settings.default_album_list' not found. Creating it."); template.execute("alter table user_settings add default_album_list varchar default '" + AlbumListType.RANDOM.getId() + "' not null"); LOG.info("Database column 'user_settings.default_album_list' was added successfully."); } if (!columnExists(template, "queue_following_songs", "user_settings")) { LOG.info("Database column 'user_settings.queue_following_songs' not found. Creating it."); template.execute("alter table user_settings add queue_following_songs boolean default true not null"); LOG.info("Database column 'user_settings.queue_following_songs' was added successfully."); } if (!columnExists(template, "image_url", "podcast_channel")) { LOG.info("Database column 'podcast_channel.image_url' not found. Creating it."); template.execute("alter table podcast_channel add image_url varchar"); LOG.info("Database column 'podcast_channel.image_url' was added successfully."); } if (!columnExists(template, "show_side_bar", "user_settings")) { LOG.info("Database column 'user_settings.show_side_bar' not found. Creating it."); template.execute("alter table user_settings add show_side_bar boolean default true not null"); LOG.info("Database column 'user_settings.show_side_bar' was added successfully."); } if (!columnExists(template, "folder_id", "artist")) { LOG.info("Database column 'artist.folder_id' not found. Creating it."); template.execute("alter table artist add folder_id int"); LOG.info("Database column 'artist.folder_id' was added successfully."); } }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema46.java
@Override public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 19") == 0) { LOG.info("Updating database schema to version 19."); template.execute("insert into version values (19)"); }// ww w .ja v a 2 s. co m if (!tableExists(template, "transcoding2")) { LOG.info("Database table 'transcoding2' not found. Creating it."); template.execute("create table transcoding2 (" + "id identity," + "name varchar not null," + "source_formats varchar not null," + "target_format varchar not null," + "step1 varchar not null," + "step2 varchar," + "step3 varchar)"); template.execute( "insert into transcoding2(name, source_formats, target_format, step1) values('mp3 audio'," + "'ogg oga aac m4a flac wav wma aif aiff ape mpc shn', 'mp3', " + "'ffmpeg -i %s -ab %bk -v 0 -f mp3 -')"); template.execute( "insert into transcoding2(name, source_formats, target_format, step1) values('flv/h264 video', " + "'avi mpg mpeg mp4 m4v mkv mov wmv ogv divx m2ts', 'flv', " + "'ffmpeg -ss %o -i %s -async 1 -b %bk -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -vcodec libx264 -preset superfast -threads 0 -')"); LOG.info("Database table 'transcoding2' was created successfully."); } if (!tableExists(template, "player_transcoding2")) { LOG.info("Database table 'player_transcoding2' not found. Creating it."); template.execute("create table player_transcoding2 (" + "player_id int not null," + "transcoding_id int not null," + "primary key (player_id, transcoding_id)," + "foreign key (player_id) references player(id) on delete cascade," + "foreign key (transcoding_id) references transcoding2(id) on delete cascade)"); template.execute("insert into player_transcoding2(player_id, transcoding_id) " + "select distinct p.id, t.id from player p, transcoding2 t"); LOG.info("Database table 'player_transcoding2' was created successfully."); } if (!columnExists(template, "default_active", "transcoding2")) { LOG.info("Database column 'transcoding2.default_active' not found. Creating it."); template.execute("alter table transcoding2 add default_active boolean default true not null"); LOG.info("Database column 'transcoding2.default_active' was added successfully."); } }
From source file:com.sg.capstone.dao.tests.CategoryDaoDbImplTests.java
@Before public void setUp() { // Ask Spring for my DAO ApplicationContext ctx = new ClassPathXmlApplicationContext("test-applicationContext.xml"); dao = (CategoryDao) ctx.getBean("CategoryDao"); blogDao = (BlogDao) ctx.getBean("BlogDao"); // Grab a JdbcTemplate to use for cleaning up JdbcTemplate cleaner = (JdbcTemplate) ctx.getBean("jdbcTemplate"); cleaner.execute("truncate blogPost_categories"); cleaner.execute("truncate blogPost"); cleaner.execute("truncate categories"); Category category1 = new Category(); category1.setName("Chocolate"); Category category2 = new Category(); category2.setName("Space Jam"); Category category3 = new Category(); category3.setName("Holidays"); categoriesForTesting[0] = category1; categoriesForTesting[1] = category2; categoriesForTesting[2] = category3; ArrayList<Integer> tagsForInsertion = new ArrayList<>(); BlogPost blogPost1 = new BlogPost(); blogPost1.setTitle("Post1"); blogPost1.setDate("2016-12-06 21:38:31"); blogPost1.setTagIds(tagsForInsertion); BlogPost blogPost2 = new BlogPost(); blogPost2.setTitle("Post2"); blogPost2.setDate("2016-12-07 09:34:38"); blogPost2.setTagIds(tagsForInsertion); blogPost2.setIsApproved(1);//from w w w.ja v a2 s . c o m BlogPost blogPost3 = new BlogPost(); blogPost3.setTitle("Post3"); blogPost3.setDate("2016-12-01 13:36:50"); blogPost3.setTagIds(tagsForInsertion); blogPost3.setIsApproved(0); blogPostsForTesting[0] = blogPost1; blogPostsForTesting[1] = blogPost2; blogPostsForTesting[2] = blogPost3; }
From source file:org.hbird.business.parameterstorage.simple.SimpleParameterStorageTest.java
/** * Set up the environment for the test: * On the first run only, add all necessary routes and prepare and fill the database. * //from w ww . j av a 2 s . co m * On every run, reset the mock endpoints. * * @throws Exception */ @Before public void initialize() throws Exception { if (thisIsTheFirstRun) { // Add routes that are necessary to run the tests. storageContext.addRoutes(new RouteBuilder() { public void configure() throws Exception { from("activemq:RetrievedParameters").to("mock:Results"); from("activemq:RetrieverCommandsFailed").to("mock:FailedCommands"); } }); // In case that there are still old parameters left in the parameters topic, // wait until all have been routed to the 'result' and 'failed' components, // so that they don't disturb testing. int oldCount = -1; int newCount = 0; while (oldCount < newCount) { Thread.sleep(250); oldCount = newCount; newCount = result.getReceivedCounter() + failed.getReceivedCounter(); } // Prepare database JdbcTemplate jdbcTemplate = new JdbcTemplate(database); jdbcTemplate.execute("DROP TABLE IF EXISTS " + parameterName.toUpperCase() + ";"); jdbcTemplate.execute("DROP TABLE IF EXISTS " + parameterName.toLowerCase() + ";"); // Store test-parameters in Database. Wait a second after sending them to the archiver // so that it has time to store them. for (Parameter p : testParameters) { archiverProducer.sendBody(p); } Thread.sleep(1000); thisIsTheFirstRun = false; } result.reset(); failed.reset(); }