/** * Create a JdbcTemplate for the given DataSource. * Only invoked if populating the DAO with a DataSource reference! * <p>Can be overridden in subclasses to provide a JdbcTemplate instance * with different configuration, or a custom JdbcTemplate subclass. * @param dataSource the JDBC DataSource to create a JdbcTemplate for * @return the new JdbcTemplate instance * @see #setDataSource */ protected JdbcTemplate createJdbcTemplate(DataSource dataSource) { return new JdbcTemplate(dataSource); }
@Override public int update(PreparedStatementCreator psc) throws DataAccessException { return update(psc, (PreparedStatementSetter) null); }
@Override @Nullable public <T> T query(PreparedStatementCreator psc, ResultSetExtractor<T> rse) throws DataAccessException { return query(psc, null, rse); }
@Override public int update(String sql, Object[] args, int[] argTypes) throws DataAccessException { return update(sql, newArgTypePreparedStatementSetter(args, argTypes)); }
@Override @Nullable public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException { return queryForObject(sql, getSingleColumnRowMapper(requiredType)); }
@Override @Nullable public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException { return query(sql, newArgTypePreparedStatementSetter(args, argTypes), rse); }
/** * Count the rows in the given table. * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations * @param tableName name of the table to count rows in * @return the number of rows in the table */ public static int countRowsInTable(JdbcTemplate jdbcTemplate, String tableName) { Integer result = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM " + tableName, Integer.class); return (result != null ? result : 0); }
@Test public void testScriptNameWithPattern() throws Exception { context = new ClassPathXmlApplicationContext("org/springframework/jdbc/config/jdbc-initialize-pattern-config.xml"); DataSource dataSource = context.getBean("dataSource", DataSource.class); assertCorrectSetup(dataSource); JdbcTemplate t = new JdbcTemplate(dataSource); assertEquals("Dave", t.queryForObject("select name from T_TEST", String.class)); }
@Test public void testPreparedStatementSetterSucceeds() throws Exception { final String sql = "UPDATE FOO SET NAME=? WHERE ID = 1"; final String name = "Gary"; int expectedRowsUpdated = 1; given(this.preparedStatement.executeUpdate()).willReturn(expectedRowsUpdated); PreparedStatementSetter pss = ps -> ps.setString(1, name); int actualRowsUpdated = new JdbcTemplate(this.dataSource).update(sql, pss); assertEquals("updated correct # of rows", actualRowsUpdated, expectedRowsUpdated); verify(this.preparedStatement).setString(1, name); verify(this.preparedStatement).close(); verify(this.connection).close(); }
@Test public void testCouldNotGetConnectionForOperationOrExceptionTranslator() throws SQLException { SQLException sqlException = new SQLException("foo", "07xxx"); this.dataSource = mock(DataSource.class); given(this.dataSource.getConnection()).willThrow(sqlException); JdbcTemplate template = new JdbcTemplate(this.dataSource, false); RowCountCallbackHandler rcch = new RowCountCallbackHandler(); this.thrown.expect(CannotGetJdbcConnectionException.class); this.thrown.expect(exceptionCause(sameInstance(sqlException))); template.query("SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3", rcch); }
public List<String> getTenantIdList(int page, int pageSize) { String sql = "SELECT tenant_id FROM config_info WHERE tenant_id != '' GROUP BY tenant_id LIMIT ?, ?"; int from = (page - 1) * pageSize; return jt.queryForList(sql, String.class, from, pageSize); }
/** * SPR-6038: detect HSQL and stop illegal locks being taken. * TODO: Against Quartz 2.2, this test's job doesn't actually execute anymore... */ @Test public void schedulerWithHsqlDataSource() throws Exception { // Assume.group(TestGroup.PERFORMANCE); DummyJob.param = 0; DummyJob.count = 0; ClassPathXmlApplicationContext ctx = context("databasePersistence.xml"); JdbcTemplate jdbcTemplate = new JdbcTemplate(ctx.getBean(DataSource.class)); assertFalse("No triggers were persisted", jdbcTemplate.queryForList("SELECT * FROM qrtz_triggers").isEmpty()); /* Thread.sleep(3000); try { assertTrue("DummyJob should have been executed at least once.", DummyJob.count > 0); } finally { ctx.close(); } */ }
@Test public void testBatchUpdateWithEmptyList() throws Exception { final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"; JdbcTemplate template = new JdbcTemplate(this.dataSource, false); int[] actualRowsAffected = template.batchUpdate(sql, Collections.emptyList()); assertTrue("executed 0 updates", actualRowsAffected.length == 0); }
@Override public int update(String sql, @Nullable Object... args) throws DataAccessException { return update(sql, newArgPreparedStatementSetter(args)); }
/** * 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); } } }
@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())); }
@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; })); }
void assertUsersDatabaseCreated(String... lastNames) { for (String lastName : lastNames) { assertThat("Did not find user with last name [" + lastName + "].", jdbcTemplate.queryForObject("select count(0) from users where last_name = ?", Integer.class, lastName), equalTo(1)); } }
@Test public void testFactoryBeanLifecycle() throws Exception { EmbeddedDatabaseFactoryBean bean = new EmbeddedDatabaseFactoryBean(); ResourceDatabasePopulator populator = new ResourceDatabasePopulator(resource("db-schema.sql"), resource("db-test-data.sql")); bean.setDatabasePopulator(populator); bean.afterPropertiesSet(); DataSource ds = bean.getObject(); JdbcTemplate template = new JdbcTemplate(ds); assertEquals("Keith", template.queryForObject("select NAME from T_TEST", String.class)); bean.destroy(); }
@Override public void afterPropertiesSet() throws Exception { cache = jdbcTemplate.queryForList("SELECT * FROM T_TEST"); } }