List of usage examples for org.springframework.jdbc.core JdbcTemplate execute
@Override public void execute(final String sql) throws DataAccessException
From source file:com.thesoftwareguild.dvdlibrary.DvdLibraryDaoTest.java
@Before public void setUp() { ApplicationContext ctx = new ClassPathXmlApplicationContext("test-applicationContext.xml"); dao = (DvdLibraryDao) ctx.getBean("dvdLibraryDao"); JdbcTemplate cleaner = (JdbcTemplate) ctx.getBean("jdbcTemplate"); cleaner.execute("delete from dvds"); // dao = ctx.getBean("dvdLibraryDao", DvdLibraryDao.class); }
From source file:net.sourceforge.subsonic.backend.dao.schema.Schema20.java
public void execute(JdbcTemplate template) { if (!tableExists(template, "payment")) { LOG.info("Database table 'payment' not found. Creating it."); template.execute("create cached table payment (" + "id identity," + "transaction_id varchar not null," + "transaction_type varchar," + // cart, web_accept "item varchar," + "payment_type varchar," + // echeck, instant "payment_status varchar," + // Completed, Pending, Denied, Failed, ... "payment_amount int," + "payment_currency varchar," + "payer_email varchar," + "payer_first_name varchar," + "payer_last_name varchar," + "payer_country varchar," + "processing_status varchar not null," + "created datetime," + "last_updated datetime," + "unique(transaction_id))"); template.execute("create index idx_payment_transaction_id on payment(transaction_id)"); template.execute("create index idx_payment_created on payment(created)"); template.execute("create index idx_payment_payer_email on payment(payer_email)"); LOG.info("Database table 'payment' was created successfully."); }//from www. j a v a 2s.c om if (!columnExists(template, "payer_email_lower", "payment")) { LOG.info("Database column 'payment.payer_email_lower' not found. Creating it."); template.execute("alter table payment " + "add payer_email_lower varchar"); template.execute("update payment set payer_email_lower=lcase(payer_email)"); template.execute("create index idx_payment_payer_email_lower on payment(payer_email_lower)"); LOG.info("Database column 'payment.payer_email_lower' was added successfully."); } if (!tableExists(template, "whitelist")) { LOG.info("Database table 'whitelist' not found. Creating it."); template.execute("create cached table whitelist (" + "id identity," + "email varchar not null)"); template.execute("create index idx_whitelist_email on whitelist(email)"); LOG.info("Database table 'whitelist' was created successfully."); } if (!tableExists(template, "blacklist")) { LOG.info("Database table 'blacklist' not found. Creating it."); template.execute("create cached table blacklist (" + "id identity," + "email varchar not null)"); template.execute("create index idx_blacklist_email on blacklist(email)"); LOG.info("Database table 'blacklist' was created successfully."); } }
From source file:com.pontecultural.flashcards.JdbcFlashcardsDao.java
public void deleteAll() { JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); jdbcTemplate.execute("delete from cards"); jdbcTemplate.execute("delete from decks"); }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema51.java
@Override public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 23") == 0) { LOG.info("Updating database schema to version 23."); template.execute("insert into version values (23)"); }// w w w . j a va 2 s . co m if (!columnExists(template, "show_artist_info", "user_settings")) { LOG.info("Database column 'user_settings.show_artist_info' not found. Creating it."); template.execute("alter table user_settings add show_artist_info boolean default true not null"); LOG.info("Database column 'user_settings.show_artist_info' was added successfully."); } if (!columnExists(template, "auto_hide_play_queue", "user_settings")) { LOG.info("Database column 'user_settings.auto_hide_play_queue' not found. Creating it."); template.execute("alter table user_settings add auto_hide_play_queue boolean default true not null"); LOG.info("Database column 'user_settings.auto_hide_play_queue' was added successfully."); } if (!columnExists(template, "view_as_list", "user_settings")) { LOG.info("Database column 'user_settings.view_as_list' not found. Creating it."); template.execute("alter table user_settings add view_as_list boolean default false not null"); LOG.info("Database column 'user_settings.view_as_list' was added successfully."); } }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema45.java
@Override public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 18") == 0) { LOG.info("Updating database schema to version 18."); template.execute("insert into version values (18)"); }//from www . ja va2s . c om if (template.queryForInt("select count(*) from role where id = 11") == 0) { LOG.info("Role 'share' not found in database. Creating it."); template.execute("insert into role values (11, 'share')"); template.execute("insert into user_role " + "select distinct u.username, 11 from user u, user_role ur " + "where u.username = ur.username and ur.role_id = 1"); LOG.info("Role 'share' was created successfully."); } if (!tableExists(template, "share")) { LOG.info("Table 'share' not found in database. Creating it."); template.execute("create cached table share (" + "id identity," + "name varchar not null," + "description varchar," + "username varchar not null," + "created datetime not null," + "expires datetime," + "last_visited datetime," + "visit_count int default 0 not null," + "unique (name)," + "foreign key (username) references user(username) on delete cascade)"); template.execute("create index idx_share_name on share(name)"); LOG.info("Table 'share' was created successfully."); LOG.info("Table 'share_file' not found in database. Creating it."); template.execute("create cached table share_file (" + "id identity," + "share_id int not null," + "path varchar not null," + "foreign key (share_id) references share(id) on delete cascade)"); LOG.info("Table 'share_file' was created successfully."); } }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema50.java
@Override public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 22") == 0) { LOG.info("Updating database schema to version 22."); template.execute("insert into version values (22)"); template.execute(/*from w ww .j a v a2 s . co m*/ "insert into transcoding2(name, source_formats, target_format, step1, default_active) values('mkv video', " + "'avi mpg mpeg mp4 m4v mkv mov wmv ogv divx m2ts', 'mkv', " + "'ffmpeg -ss %o -i %s -c:v libx264 -preset superfast -b:v %bk -c:a libvorbis -f matroska -threads 0 -', 'true')"); template.execute("insert into player_transcoding2(player_id, transcoding_id) " + "select distinct p.id, t.id from player p, transcoding2 t where t.name='mkv video'"); LOG.info("Added mkv transcoding."); } if (!columnExists(template, "song_notification", "user_settings")) { LOG.info("Database column 'user_settings.song_notification' not found. Creating it."); template.execute("alter table user_settings add song_notification boolean default true not null"); LOG.info("Database column 'user_settings.song_notification' was added successfully."); } // Added in 5.0.beta2 if (template.queryForInt("select count(*) from version where version = 23") == 0) { LOG.info("Updating database schema to version 23."); template.execute("insert into version values (23)"); template.execute( "update transcoding2 set step1='ffmpeg -i %s -map 0:0 -b:a %bk -v 0 -f mp3 -' where name='mp3 audio'"); } }
From source file:com.github.viktornar.migration.schema.Schema.java
/** * Returns whether the given table exists. * * @param template The JDBC template to use. * @param table The table in question. * @return Whether the table exists.// w ww . j a v a2 s . co m */ protected boolean tableExists(JdbcTemplate template, String table) { assert template != null; assert table != null && !table.isEmpty(); try { template.execute(format("select 1 from %s", table)); } catch (Exception x) { return false; } return true; }
From source file:commonline.query.gui.action.ClearDatabaseAction.java
public void actionPerformed(ActionEvent actionEvent) { if (JOptionPane.showConfirmDialog(parent, "Are you sure you want to clear the databases?", "Clear DB", JOptionPane.YES_NO_OPTION) == JOptionPane.OK_OPTION) { SwingWorker worker = new SwingWorker<Void, Void>() { protected Void doInBackground() throws Exception { for (RecordParserDataSource dataSource : dataSources) { JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); for (RecordLayoutTableInfo tableInfo : dataSource.getTableInfos()) { try { jdbcTemplate.execute("delete from " + tableInfo.getTableName()); } catch (Exception err) { throw new RuntimeException("Problem clearing table:" + tableInfo.getTableName() + ", in DB:" + dataSource.getUrl(), err); }//from ww w . j a v a 2 s.c o m } } return null; } }; worker.execute(); } }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema49.java
@Override public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 21") == 0) { LOG.info("Updating database schema to version 21."); template.execute("insert into version values (21)"); }/*from w w w .java 2 s .c o m*/ if (!columnExists(template, "year", "album")) { LOG.info("Database column 'album.year' not found. Creating it."); template.execute("alter table album add year int"); LOG.info("Database column 'album.year' was added successfully."); } if (!columnExists(template, "genre", "album")) { LOG.info("Database column 'album.genre' not found. Creating it."); template.execute("alter table album add genre varchar"); LOG.info("Database column 'album.genre' was added successfully."); } if (!tableExists(template, "genre")) { LOG.info("Database table 'genre' not found. Creating it."); template.execute("create table genre (" + "name varchar not null," + "song_count int not null)"); LOG.info("Database table 'genre' was created successfully."); } if (!columnExists(template, "album_count", "genre")) { LOG.info("Database column 'genre.album_count' not found. Creating it."); template.execute("alter table genre add album_count int default 0 not null"); LOG.info("Database column 'genre.album_count' was added successfully."); } }