/** * Drop the specified tables. * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations * @param tableNames the names of the tables to drop */ public static void dropTables(JdbcTemplate jdbcTemplate, String... tableNames) { for (String tableName : tableNames) { jdbcTemplate.execute("DROP TABLE " + tableName); if (logger.isInfoEnabled()) { logger.info("Dropped table " + tableName); } } }
public static void main(String[] args) { ApplicationContext context = SpringApplication.run(ConfigServerBootstrap.class); JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class); jdbcTemplate.execute("delete from properties"); jdbcTemplate.execute("INSERT INTO properties VALUES(1, 'com.didispace.message', 'test-stage-master', 'config-client', 'stage', 'master')"); jdbcTemplate.execute("INSERT INTO properties VALUES(2, 'com.didispace.message', 'test-online-master', 'config-client', 'online', 'master')"); jdbcTemplate.execute("INSERT INTO properties VALUES(3, 'com.didispace.message', 'test-online-develop', 'config-client', 'online', 'develop')"); jdbcTemplate.execute("INSERT INTO properties VALUES(4, 'com.didispace.message', 'hello-online-master', 'hello-service', 'online', 'master')"); jdbcTemplate.execute("INSERT INTO properties VALUES(5, 'com.didispace.message', 'hello-online-develop', 'hello-service', 'online', 'develop')"); }
@Override public void execute(final String sql) throws DataAccessException { if (logger.isDebugEnabled()) { logger.debug("Executing SQL statement [" + sql + "]"); } /** * Callback to execute the statement. */ class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider { @Override @Nullable public Object doInStatement(Statement stmt) throws SQLException { stmt.execute(sql); return null; } @Override public String getSql() { return sql; } } execute(new ExecuteStatementCallback()); }
@Override @Nullable public <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException { return execute(new SimplePreparedStatementCreator(sql), action); }
@Override @Nullable public <T> T execute(String callString, CallableStatementCallback<T> action) throws DataAccessException { return execute(new SimpleCallableStatementCreator(callString), action); }
@Bean public JdbcTemplate jdbcTemplate(DataSourceProxy dataSourceProxy) { JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSourceProxy); jdbcTemplate.execute("TRUNCATE TABLE order_tbl"); return jdbcTemplate; }
return execute(new QueryStatementCallback());
@Override public int update(final String sql) throws DataAccessException { Assert.notNull(sql, "SQL must not be null"); if (logger.isDebugEnabled()) { logger.debug("Executing SQL update [" + sql + "]"); } /** * Callback to execute the update statement. */ class UpdateStatementCallback implements StatementCallback<Integer>, SqlProvider { @Override public Integer doInStatement(Statement stmt) throws SQLException { int rows = stmt.executeUpdate(sql); if (logger.isTraceEnabled()) { logger.trace("SQL update affected " + rows + " rows"); } return rows; } @Override public String getSql() { return sql; } } return updateCount(execute(new UpdateStatementCallback())); }
public static void createGroupTables(JdbcTemplate template) { // Group tables and data template.execute("CREATE TABLE GROUPS(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY, GROUP_NAME VARCHAR_IGNORECASE(50) NOT NULL)"); template.execute("CREATE TABLE GROUP_AUTHORITIES(GROUP_ID BIGINT NOT NULL, AUTHORITY VARCHAR(50) NOT NULL, CONSTRAINT FK_GROUP_AUTHORITIES_GROUP FOREIGN KEY(GROUP_ID) REFERENCES GROUPS(ID))"); template.execute("CREATE TABLE GROUP_MEMBERS(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY, USERNAME VARCHAR(50) NOT NULL, GROUP_ID BIGINT NOT NULL, CONSTRAINT FK_GROUP_MEMBERS_GROUP FOREIGN KEY(GROUP_ID) REFERENCES GROUPS(ID))"); }
protected int update(final PreparedStatementCreator psc, @Nullable final PreparedStatementSetter pss) throws DataAccessException { logger.debug("Executing prepared SQL update"); return updateCount(execute(psc, ps -> { try { if (pss != null) { pss.setValues(ps); } int rows = ps.executeUpdate(); if (logger.isTraceEnabled()) { logger.trace("SQL update affected " + rows + " rows"); } return rows; } finally { if (pss instanceof ParameterDisposer) { ((ParameterDisposer) pss).cleanupParameters(); } } })); }
private void insertJoe() { template.execute("insert into users (username, password, enabled) values ('joe','password','true')"); template.execute("insert into authorities (username, authority) values ('joe','A')"); template.execute("insert into authorities (username, authority) values ('joe','B')"); template.execute("insert into authorities (username, authority) values ('joe','C')"); cache.putUserInCache(joe); }
@After public void dropTablesAndClearContext() { template.execute("drop table authorities"); template.execute("drop table users"); template.execute("drop table group_authorities"); template.execute("drop table group_members"); template.execute("drop table groups"); SecurityContextHolder.clearContext(); }
@Override public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder) throws DataAccessException { Assert.notNull(generatedKeyHolder, "KeyHolder must not be null"); logger.debug("Executing SQL update and returning generated keys"); return updateCount(execute(psc, ps -> { int rows = ps.executeUpdate(); List<Map<String, Object>> generatedKeys = generatedKeyHolder.getKeyList(); generatedKeys.clear(); ResultSet keys = ps.getGeneratedKeys(); if (keys != null) { try { RowMapperResultSetExtractor<Map<String, Object>> rse = new RowMapperResultSetExtractor<>(getColumnMapRowMapper(), 1); generatedKeys.addAll(result(rse.extractData(keys))); } finally { JdbcUtils.closeResultSet(keys); } } if (logger.isTraceEnabled()) { logger.trace("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys"); } return rows; })); }
public void destroy() throws Exception { System.out.println("Shutting down database: " + name); new JdbcTemplate(this).execute("SHUTDOWN"); } }
@AfterTransaction public void clearContextAndData() throws Exception { SecurityContextHolder.clearContext(); jdbcTemplate.execute("drop table acl_entry"); jdbcTemplate.execute("drop table acl_object_identity"); jdbcTemplate.execute("drop table acl_class"); jdbcTemplate.execute("drop table acl_sid"); aclCache.clearCache(); }
@Test public void testConnectionCallback() throws Exception { String result = this.template.execute(new ConnectionCallback<String>() { @Override public String doInConnection(Connection con) { assertTrue(con instanceof ConnectionProxy); assertSame(JdbcTemplateTests.this.connection, ((ConnectionProxy) con).getTargetConnection()); return "test"; } }); assertEquals("test", result); }
private void setUpAccLockingColumns() { template.execute("alter table users add column acc_locked boolean default false not null"); template.execute("alter table users add column acc_expired boolean default false not null"); template.execute("alter table users add column creds_expired boolean default false not null"); manager.setUsersByUsernameQuery( "select username,password,enabled, acc_locked, acc_expired, creds_expired from users where username = ?"); manager.setCreateUserSql( "insert into users (username, password, enabled, acc_locked, acc_expired, creds_expired) values (?,?,?,?,?,?)"); manager.setUpdateUserSql( "update users set password = ?, enabled = ?, acc_locked=?, acc_expired=?, creds_expired=? where username = ?"); }
@Test(expected = IllegalArgumentException.class) public void nullOwnerIsNotSupported() { String query = "INSERT INTO acl_object_identity(ID,OBJECT_ID_CLASS,OBJECT_ID_IDENTITY,PARENT_OBJECT,OWNER_SID,ENTRIES_INHERITING) VALUES (6,2,104,null,null,1);"; getJdbcTemplate().execute(query); ObjectIdentity oid = new ObjectIdentityImpl(TARGET_CLASS, new Long(104)); strategy.readAclsById(Arrays.asList(oid), Arrays.asList(BEN_SID)); }
@After public void emptyDatabase() { String query = "DELETE FROM acl_entry;" + "DELETE FROM acl_object_identity WHERE ID = 9;" + "DELETE FROM acl_object_identity WHERE ID = 8;" + "DELETE FROM acl_object_identity WHERE ID = 7;" + "DELETE FROM acl_object_identity WHERE ID = 6;" + "DELETE FROM acl_object_identity WHERE ID = 5;" + "DELETE FROM acl_object_identity WHERE ID = 4;" + "DELETE FROM acl_object_identity WHERE ID = 3;" + "DELETE FROM acl_object_identity WHERE ID = 2;" + "DELETE FROM acl_object_identity WHERE ID = 1;" + "DELETE FROM acl_class;" + "DELETE FROM acl_sid;"; getJdbcTemplate().execute(query); }
@Test public void testConnectionCallbackWithStatementSettings() throws Exception { String result = this.template.execute(new ConnectionCallback<String>() { @Override public String doInConnection(Connection con) throws SQLException { PreparedStatement ps = con.prepareStatement("some SQL"); ps.setFetchSize(10); ps.setMaxRows(20); ps.close(); return "test"; } }); assertEquals("test", result); verify(this.preparedStatement).setFetchSize(10); verify(this.preparedStatement).setMaxRows(20); verify(this.preparedStatement).close(); verify(this.connection).close(); }