List of usage examples for org.springframework.jdbc.core JdbcTemplate execute
@Override
public void execute(final String sql) throws DataAccessException
From source file:org.geowebcache.storage.MetastoreRemover.java
/** * Drop all the tiles to prevent a future migration * /*from w w w .j a v a2 s. c o m*/ * @param template */ private void removeTiles(JdbcTemplate template) { template.execute("delete from tiles"); }
From source file:com.px100systems.data.plugin.persistence.jdbc.Storage.java
protected void resetSchema(final String schemaName) { connection.write(new JdbcCallback<Void>() { @Override//from w w w.j a v a2 s. c o m public Void transaction(JdbcTemplate jdbc) { jdbc.execute("DROP SCHEMA " + schemaName); jdbc.execute("CREATE SCHEMA " + schemaName); return null; } }); }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema26.java
public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 2") == 0) { LOG.info("Updating database schema to version 2."); template.execute("insert into version values (2)"); }/*from ww w. ja v a 2s . c o m*/ if (!tableExists(template, "music_folder")) { LOG.info("Database table 'music_folder' not found. Creating it."); template.execute("create table music_folder (" + "id identity," + "path varchar not null," + "name varchar not null," + "enabled boolean not null)"); template.execute("insert into music_folder values (null, '" + Util.getDefaultMusicFolder() + "', 'Music', true)"); LOG.info("Database table 'music_folder' was created successfully."); } if (!tableExists(template, "music_file_info")) { LOG.info("Database table 'music_file_info' not found. Creating it."); template.execute("create cached table music_file_info (" + "id identity," + "path varchar not null," + "rating int," + "comment varchar," + "play_count int," + "last_played datetime)"); template.execute("create index idx_music_file_info_path on music_file_info(path)"); LOG.info("Database table 'music_file_info' was created successfully."); } if (!tableExists(template, "internet_radio")) { LOG.info("Database table 'internet_radio' not found. Creating it."); template.execute("create table internet_radio (" + "id identity," + "name varchar not null," + "stream_url varchar not null," + "homepage_url varchar," + "enabled boolean not null)"); LOG.info("Database table 'internet_radio' was created successfully."); } if (!tableExists(template, "player")) { LOG.info("Database table 'player' not found. Creating it."); template.execute("create table player (" + "id int not null," + "name varchar," + "type varchar," + "username varchar," + "ip_address varchar," + "auto_control_enabled boolean not null," + "last_seen datetime," + "cover_art_scheme varchar not null," + "transcode_scheme varchar not null," + "primary key (id))"); LOG.info("Database table 'player' was created successfully."); } // 'dynamic_ip' was added in 2.6.beta2 if (!columnExists(template, "dynamic_ip", "player")) { LOG.info("Database column 'player.dynamic_ip' not found. Creating it."); template.execute("alter table player " + "add dynamic_ip boolean default true not null"); LOG.info("Database column 'player.dynamic_ip' was added successfully."); } if (template.queryForInt("select count(*) from role where id = 6") == 0) { LOG.info("Role 'comment' not found in database. Creating it."); template.execute("insert into role values (6, 'comment')"); template.execute("insert into user_role " + "select distinct u.username, 6 from user u, user_role ur " + "where u.username = ur.username and ur.role_id in (1, 5)"); LOG.info("Role 'comment' was created successfully."); } }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema43.java
@Override public void execute(JdbcTemplate template) { // version 16 was used for 4.3.beta1 if (template.queryForInt("select count(*) from version where version = 16") == 0) { LOG.info("Updating database schema to version 16."); template.execute("insert into version values (16)"); }//from www .jav a2 s . c om if (template.queryForInt("select count(*) from version where version = 17") == 0) { LOG.info("Updating database schema to version 17."); template.execute("insert into version values (17)"); for (String format : Arrays.asList("avi", "mpg", "mpeg", "mp4", "m4v", "mkv", "mov", "wmv", "ogv")) { template.update("delete from transcoding where source_format=? and target_format=?", new Object[] { format, "flv" }); template.execute("insert into transcoding values(null,'" + format + " > flv' ,'" + format + "' ,'flv','ffmpeg -ss %o -i %s -async 1 -b %bk -s %wx%h -ar 44100 -ac 2 -v 0 -f flv -',null,null,true,true)"); template.execute( "insert into player_transcoding select p.id as player_id, t.id as transaction_id from player p, transcoding t where t.name = '" + format + " > flv'"); } LOG.info("Created video transcoding configuration."); } if (!columnExists(template, "email", "user")) { LOG.info("Database column 'user.email' not found. Creating it."); template.execute("alter table user add email varchar"); LOG.info("Database column 'user.email' was added successfully."); } }
From source file:com.px100systems.data.plugin.persistence.jdbc.Storage.java
protected void create(final boolean lastSavedStorage) { connection.write(new JdbcCallback<Void>() { @Override/*from w ww. j a v a2s .c o m*/ public Void transaction(JdbcTemplate jdbc) { jdbc.execute("CREATE TABLE " + table + " (pk_id BIGINT AUTO_INCREMENT, unit_name VARCHAR(50), generator_name VARCHAR(50), class_name VARCHAR(100), id BIGINT, block_number INT, data_size INT, data " + binaryType + "(" + blockSize + ")," + " PRIMARY KEY (pk_id))"); jdbc.execute("CREATE INDEX " + table + "_index0 ON " + table + " (unit_name, id)"); if (lastSavedStorage) { jdbc.execute( "CREATE TABLE last_saved (pk_id BIGINT NOT NULL, save_time BIGINT, PRIMARY KEY (pk_id))"); jdbc.update("INSERT INTO last_saved (pk_id, save_time) VALUES (0, NULL)"); } return null; } }); }
From source file:com.github.viktornar.migration.schema.Schema.java
/** * Returns whether the given column in the given table exists. * * @param template The JDBC template to use. * @param column The column in question. * @param table The table in question. * @return Whether the column exists./*from w w w . ja v a 2 s .c o m*/ */ protected boolean columnExists(JdbcTemplate template, String column, String table) { assert template != null; assert column != null && !column.isEmpty(); assert table != null && !table.isEmpty(); try { template.execute(format("select %s from %s where 1 = 0", column, table)); } catch (Exception ex) { return false; } return true; }
From source file:net.sourceforge.subsonic.dao.schema.hsql.Schema28.java
public void execute(JdbcTemplate template) { if (template.queryForInt("select count(*) from version where version = 4") == 0) { LOG.info("Updating database schema to version 4."); template.execute("insert into version values (4)"); }/*from w ww . ja v a 2 s.c om*/ if (!tableExists(template, "user_settings")) { LOG.info("Database table 'user_settings' not found. Creating it."); template.execute("create table user_settings (" + "username varchar not null," + "locale varchar," + "theme_id varchar," + "final_version_notification boolean default true not null," + "beta_version_notification boolean default false not null," + "main_caption_cutoff int default 35 not null," + "main_track_number boolean default true not null," + "main_artist boolean default true not null," + "main_album boolean default false not null," + "main_genre boolean default false not null," + "main_year boolean default false not null," + "main_bit_rate boolean default false not null," + "main_duration boolean default true not null," + "main_format boolean default false not null," + "main_file_size boolean default false not null," + "playlist_caption_cutoff int default 35 not null," + "playlist_track_number boolean default false not null," + "playlist_artist boolean default true not null," + "playlist_album boolean default true not null," + "playlist_genre boolean default false not null," + "playlist_year boolean default true not null," + "playlist_bit_rate boolean default false not null," + "playlist_duration boolean default true not null," + "playlist_format boolean default true not null," + "playlist_file_size boolean default true not null," + "primary key (username)," + "foreign key (username) references user(username) on delete cascade)"); LOG.info("Database table 'user_settings' was created successfully."); } if (!tableExists(template, "transcoding")) { LOG.info("Database table 'transcoding' not found. Creating it."); template.execute("create table transcoding (" + "id identity," + "name varchar not null," + "source_format varchar not null," + "target_format varchar not null," + "step1 varchar not null," + "step2 varchar," + "step3 varchar," + "enabled boolean not null)"); template.execute( "insert into transcoding values(null,'wav > mp3', 'wav', 'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); template.execute( "insert into transcoding values(null,'flac > mp3','flac','mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); template.execute( "insert into transcoding values(null,'ogg > mp3' ,'ogg' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); template.execute( "insert into transcoding values(null,'wma > mp3' ,'wma' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); template.execute( "insert into transcoding values(null,'m4a > mp3' ,'m4a' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,false)"); template.execute( "insert into transcoding values(null,'aac > mp3' ,'aac' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,false)"); template.execute( "insert into transcoding values(null,'ape > mp3' ,'ape' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); template.execute( "insert into transcoding values(null,'mpc > mp3' ,'mpc' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); template.execute( "insert into transcoding values(null,'mv > mp3' ,'mv' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); template.execute( "insert into transcoding values(null,'shn > mp3' ,'shn' ,'mp3','ffmpeg -i %s -v 0 -f wav -','lame -b %b --tt %t --ta %a --tl %l -S --resample 44.1 - -',null,true)"); LOG.info("Database table 'transcoding' was created successfully."); } if (!tableExists(template, "player_transcoding")) { LOG.info("Database table 'player_transcoding' not found. Creating it."); template.execute("create table player_transcoding (" + "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 transcoding(id) on delete cascade)"); LOG.info("Database table 'player_transcoding' was created successfully."); } }
From source file:org.opennms.netmgt.notifd.HttpNotificationStrategy.java
private void doSql(String contents) { if (getSql() == null) { LOG.info("send: optional sql argument is null."); return;// ww w . j a v a 2s.com } if (contents == null) { LOG.info("doSql: HTTP reply is null"); return; } LOG.debug("send: compiling expression: {}", getSwitchValue("result-match")); Pattern p = Pattern.compile(getSwitchValue("result-match")); Matcher m = p.matcher(contents); if (m.matches()) { LOG.debug("send: compiled expression ready to run sql: {}", getSql()); MatchTable matches = new MatchTable(m); String sqlString = PropertiesUtils.substitute(getSql(), matches); LOG.debug("send: running sql: {}", sqlString); JdbcTemplate template = new JdbcTemplate(DataSourceFactory.getInstance()); template.execute(sqlString); } else { LOG.info("send: result didn't match, not running sql"); } }
From source file:net.sourceforge.subsonic.backend.dao.schema.Schema30.java
public void execute(JdbcTemplate template) { if (!tableExists(template, "subscription")) { LOG.info("Database table 'subscription' not found. Creating it."); template.execute("create cached table subscription (" + "id identity," + "subscr_id varchar," + // PayPal subscription ID "payer_id varchar," + // PayPal payer ID "btn_id varchar," + // PayPal button ID "email varchar_ignorecase," + "first_name varchar," + "last_name varchar," + "country varchar," + "valid_from datetime," + "valid_to datetime," + "processing_status varchar not null," + "created datetime not null," + "updated datetime not null)"); template.execute("create index idx_subscription_subscr_id on subscription(subscr_id)"); template.execute("create index idx_subscription_payer_id on subscription(payer_id)"); template.execute("create index idx_subscription_created on subscription(created)"); template.execute("create index idx_subscription_processing_status on subscription(processing_status)"); template.execute("create index idx_subscription_email on subscription(email)"); LOG.info("Database table 'subscription' was created successfully."); }/* w w w . java 2s .c o m*/ if (!tableExists(template, "subscription_payment")) { LOG.info("Database table 'subscription_payment' not found. Creating it."); template.execute("create cached table subscription_payment (" + "id identity," + "subscr_id varchar," + // PayPal subscription ID "payer_id varchar," + // PayPal payer ID "btn_id varchar," + // PayPal button ID "ipn_track_id varchar," + // PayPal IPN track ID "txn_id varchar," + // PayPal IPN track ID "email varchar_ignorecase," + "amount double," + "fee double," + "currency varchar," + "created datetime not null)"); template.execute("create index idx_subscription_payment_email on subscription_payment(email)"); LOG.info("Database table 'subscription_payment' was created successfully."); } if (!tableExists(template, "subscription_notification")) { LOG.info("Database table 'subscription_notification' not found. Creating it."); template.execute( "create cached table subscription_notification (" + "id identity," + "subscr_id varchar," + // PayPal subscription ID "payer_id varchar," + // PayPal payer ID "btn_id varchar," + // PayPal button ID "ipn_track_id varchar," + // PayPal IPN track ID "txn_type varchar," + "email varchar_ignorecase," + "created datetime not null)"); template.execute( "create index idx_subscription_notification_email on subscription_notification(email)"); LOG.info("Database table 'subscription_notification' was created successfully."); } if (!columnExists(template, "valid_to", "whitelist")) { LOG.info("Database column 'whitelist.valid_to' not found. Creating it."); template.execute("alter table whitelist add valid_to datetime null"); LOG.info("Database column 'whitelist.valid_to' was added successfully."); } if (!columnExists(template, "valid_to", "payment")) { LOG.info("Database column 'payment.valid_to' not found. Creating it."); template.execute("alter table payment add valid_to datetime null"); LOG.info("Database column 'payment.valid_to' was added successfully."); } if (!tableExists(template, "currency_conversion")) { LOG.info("Database table 'currency_conversion' not found. Creating it."); template.execute("create table currency_conversion (" + "id identity," + "source varchar not null," + "target varchar not null," + "rate double not null)"); template.execute("insert into currency_conversion values(null, 'EUR', 'EUR', 1.0)"); template.execute("insert into currency_conversion values(null, 'EUR', 'USD', 0.77)"); template.execute("insert into currency_conversion values(null, 'EUR', 'NOK', 0.13)"); template.execute("insert into currency_conversion values(null, 'EUR', 'SEK', 0.12)"); LOG.info("Database table 'currency_conversion' was created successfully."); } if (!rowExists(template, "table_name='SUBSCRIPTION' and column_name='VALID_TO' and ordinal_position=1", "information_schema.system_indexinfo")) { template.execute("create index idx_subscription_valid_to on subscription(valid_to)"); LOG.info("Database index idx_subscription_valid_to created successfully."); } if (!rowExists(template, "table_name='PAYMENT' and column_name='VALID_TO' and ordinal_position=1", "information_schema.system_indexinfo")) { template.execute("create index idx_payment_valid_to on payment(valid_to)"); LOG.info("Database index idx_payment_valid_to created successfully."); } if (!rowExists(template, "table_name='PAYMENT' and column_name='PROCESSING_STATUS' and ordinal_position=1", "information_schema.system_indexinfo")) { template.execute("create index idx_payment_processing_status on payment(processing_status)"); LOG.info("Database index idx_payment_processing_status created successfully."); } }
From source file:de.hybris.platform.util.database.TableNameDatabaseMetaDataCallbackTest.java
private void createTable(final DataSource dataSource, final String tableName) { final JdbcTemplate create = new JdbcTemplate(dataSource); create.execute("CREATE TABLE " + tableName + " ( ID VARCHAR(10))"); createdTables.add(tableName);// w ww .j a va 2 s. c o m }