/** * 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); }
@Override @Nullable public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException { return queryForObject(sql, getSingleColumnRowMapper(requiredType)); }
@Override @Nullable public <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType) throws DataAccessException { return queryForObject(sql, args, argTypes, getSingleColumnRowMapper(requiredType)); }
@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 testQueryForObjectWithBigInteger() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getObject(1, BigInteger.class)).willReturn(new BigInteger("22")); assertEquals(new BigInteger("22"), this.template.queryForObject(sql, BigInteger.class)); verify(this.resultSet).close(); verify(this.statement).close(); }
@Test public void testQueryForObjectWithBigDecimal() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getBigDecimal(1)).willReturn(new BigDecimal("22.5")); assertEquals(new BigDecimal("22.5"), this.template.queryForObject(sql, BigDecimal.class)); verify(this.resultSet).close(); verify(this.statement).close(); }
@Test public void testQueryForInt() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getInt(1)).willReturn(22); int i = this.template.queryForObject(sql, Integer.class).intValue(); assertEquals("Return of an int", 22, i); verify(this.resultSet).close(); verify(this.statement).close(); }
@Test public void testQueryForLong() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getLong(1)).willReturn(87L); long l = this.template.queryForObject(sql, Long.class).longValue(); assertEquals("Return of a long", 87, l); verify(this.resultSet).close(); verify(this.statement).close(); }
@Test public void scriptWithMultipleStatements() throws Exception { databasePopulator.addScript(defaultSchema()); databasePopulator.addScript(resource("db-test-data-multiple.sql")); DatabasePopulatorUtils.execute(databasePopulator, db); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Keith'", Integer.class), equalTo(1)); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Dave'", Integer.class), equalTo(1)); }
@Test public void testQueryForIntWithArgs() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = ?"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getInt(1)).willReturn(22); int i = this.template.queryForObject(sql, new Object[] {3}, Integer.class).intValue(); assertEquals("Return of an int", 22, i); verify(this.preparedStatement).setObject(1, 3); verify(this.resultSet).close(); verify(this.preparedStatement).close(); }
@Test public void scriptWithMultipleStatementsAndNewlineSeparator() throws Exception { databasePopulator.addScript(defaultSchema()); databasePopulator.addScript(resource("db-test-data-newline.sql")); DatabasePopulatorUtils.execute(databasePopulator, db); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Keith'", Integer.class), equalTo(1)); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Dave'", Integer.class), equalTo(1)); }
@Test public void testQueryForLongWithArgs() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = ?"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getLong(1)).willReturn(87L); long l = this.template.queryForObject(sql, new Object[] {3}, Long.class).longValue(); assertEquals("Return of a long", 87, l); verify(this.preparedStatement).setObject(1, 3); verify(this.resultSet).close(); verify(this.preparedStatement).close(); }
@Test public void testQueryForObjectWithInteger() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getInt(1)).willReturn(22); assertEquals(Integer.valueOf(22), this.template.queryForObject(sql, Integer.class)); verify(this.resultSet).close(); verify(this.statement).close(); }
@Test public void testQueryForObjectWithArgsAndInteger() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = ?"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getInt(1)).willReturn(22); Object o = this.template.queryForObject(sql, new Object[] {3}, Integer.class); assertTrue("Correct result type", o instanceof Integer); verify(this.preparedStatement).setObject(1, 3); verify(this.resultSet).close(); verify(this.preparedStatement).close(); }
@Test public void scriptWithMultipleStatementsAndLongSeparator() throws Exception { databasePopulator.addScript(defaultSchema()); databasePopulator.addScript(resource("db-test-data-endings.sql")); databasePopulator.setSeparator("@@"); DatabasePopulatorUtils.execute(databasePopulator, db); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Keith'", Integer.class), equalTo(1)); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Dave'", Integer.class), equalTo(1)); }
@Test public void scriptWithMultipleStatementsAndWhitespaceSeparator() throws Exception { databasePopulator.addScript(defaultSchema()); databasePopulator.addScript(resource("db-test-data-whitespace.sql")); databasePopulator.setSeparator("/\n"); DatabasePopulatorUtils.execute(databasePopulator, db); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Keith'", Integer.class), equalTo(1)); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Dave'", Integer.class), equalTo(1)); }
@Test public void scriptWithMultipleStatementsAndMultipleNewlineSeparator() throws Exception { databasePopulator.addScript(defaultSchema()); databasePopulator.addScript(resource("db-test-data-multi-newline.sql")); databasePopulator.setSeparator("\n\n"); DatabasePopulatorUtils.execute(databasePopulator, db); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Keith'", Integer.class), equalTo(1)); assertThat(jdbcTemplate.queryForObject("select COUNT(NAME) from T_TEST where NAME='Dave'", Integer.class), equalTo(1)); }
@Test public void testQueryForLongPrimitive() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getLong(1)).willReturn(87L); long l = this.template.queryForObject(sql, long.class); assertEquals("Return of a long", 87, l); verify(this.resultSet).close(); verify(this.statement).close(); }
@Test public void testQueryForObjectWithIntegerAndNull() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; given(this.resultSet.next()).willReturn(true, false); given(this.resultSet.getInt(1)).willReturn(0); given(this.resultSet.wasNull()).willReturn(true); assertNull(this.template.queryForObject(sql, Integer.class)); verify(this.resultSet).close(); verify(this.statement).close(); }
@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(); }