Example usage for org.springframework.jdbc.core JdbcTemplate query

List of usage examples for org.springframework.jdbc.core JdbcTemplate query

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.*
  5. JdbcTemplate
  6. query

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate query.

Prototype

@Override
    public <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException

Source Link

Usage

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testSQLErrorCodeTranslation() throws Exception {
    final SQLException sex = new SQLException("I have a known problem", "99999", 1054);
    final String sql = "SELECT ID FROM CUSTOMER";

    MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
    ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
    mockResultSet.next();/*from   w w w.  j  a va  2s.  com*/
    ctrlResultSet.setReturnValue(true);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
    mockStatement.executeQuery(sql);
    ctrlStatement.setReturnValue(mockResultSet);
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.getDatabaseProductName();
    ctrlDatabaseMetaData.setReturnValue("MySQL");

    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);
    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData);

    ctrlResultSet.replay();
    ctrlStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource);
    try {
        template.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                throw sex;
            }
        });
        fail("Should have thrown BadSqlGrammarException");
    } catch (BadSqlGrammarException ex) {
        // expected
        assertTrue("Wanted same exception back, not " + ex, sex == ex.getCause());
    }

    ctrlResultSet.verify();
    ctrlStatement.verify();
    ctrlDatabaseMetaData.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testSQLErrorCodeTranslationWithSpecifiedDbName() throws Exception {
    final SQLException sex = new SQLException("I have a known problem", "99999", 1054);
    final String sql = "SELECT ID FROM CUSTOMER";

    MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
    ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
    mockResultSet.next();/*from   w  w  w .  j av a  2 s  . co m*/
    ctrlResultSet.setReturnValue(true);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
    mockStatement.executeQuery(sql);
    ctrlStatement.setReturnValue(mockResultSet);
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);

    ctrlResultSet.replay();
    ctrlStatement.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate();
    template.setDataSource(mockDataSource);
    template.setDatabaseProductName("MySQL");
    template.afterPropertiesSet();
    try {
        template.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                throw sex;
            }
        });
        fail("Should have thrown BadSqlGrammarException");
    } catch (BadSqlGrammarException ex) {
        // expected
        assertTrue("Wanted same exception back, not " + ex, sex == ex.getCause());
    }

    ctrlResultSet.verify();
    ctrlStatement.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

/**
 * Test that we see an SQLException translated using Error Code.
 * If we provide the SQLExceptionTranslator, we shouldn't use a connection
 * to get the metadata//from  w  ww.j ava  2s .  co m
 */
public void testUseCustomSQLErrorCodeTranslator() throws Exception {
    // Bad SQL state
    final SQLException sex = new SQLException("I have a known problem", "07000", 1054);
    final String sql = "SELECT ID FROM CUSTOMER";

    MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
    ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
    mockStatement.executeQuery(sql);
    ctrlStatement.setReturnValue(mockResultSet);
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);

    // Change behaviour in setUp() because we only expect one call to getConnection():
    // none is necessary to get metadata for exception translator
    ctrlConnection = MockControl.createControl(Connection.class);
    mockConnection = (Connection) ctrlConnection.getMock();
    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement, 1);
    mockConnection.close();
    ctrlConnection.setVoidCallable(1);
    ctrlConnection.replay();

    ctrlDataSource = MockControl.createControl(DataSource.class);
    mockDataSource = (DataSource) ctrlDataSource.getMock();
    mockDataSource.getConnection();
    ctrlDataSource.setReturnValue(mockConnection, 1);
    ctrlDataSource.replay();
    ///// end changed behaviour

    ctrlResultSet.replay();
    ctrlStatement.replay();

    JdbcTemplate template = new JdbcTemplate();
    template.setDataSource(mockDataSource);
    // Set custom exception translator
    template.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
    template.afterPropertiesSet();
    try {
        template.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                throw sex;
            }
        });
        fail("Should have thrown exception");
    } catch (BadSqlGrammarException ex) {
        assertTrue("Wanted same exception back, not " + ex, sex == ex.getCause());
    }

    ctrlResultSet.verify();
    ctrlStatement.verify();

    // We didn't call superclass replay() so we need to check these ourselves
    ctrlDataSource.verify();
    ctrlConnection.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testNativeJdbcExtractorInvoked() throws Exception {
    MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
    final ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
    mockResultSet.close();//from w  w w.ja  va  2s . c o  m
    ctrlResultSet.setVoidCallable(2);

    MockControl ctrlStatement = MockControl.createControl(Statement.class);
    final Statement mockStatement = (Statement) ctrlStatement.getMock();
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();
    MockControl ctrlStatement2 = MockControl.createControl(Statement.class);
    final Statement mockStatement2 = (Statement) ctrlStatement2.getMock();
    mockStatement2.executeQuery("my query");
    ctrlStatement2.setReturnValue(mockResultSet, 1);

    MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    final PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();
    MockControl ctrlPreparedStatement2 = MockControl.createControl(PreparedStatement.class);
    final PreparedStatement mockPreparedStatement2 = (PreparedStatement) ctrlPreparedStatement2.getMock();
    mockPreparedStatement2.executeQuery();
    ctrlPreparedStatement2.setReturnValue(mockResultSet, 1);

    MockControl ctrlReturnResultSet = MockControl.createControl(ResultSet.class);
    final ResultSet mockReturnResultSet = (ResultSet) ctrlReturnResultSet.getMock();
    mockReturnResultSet.next();
    ctrlReturnResultSet.setReturnValue(false);
    mockReturnResultSet.close();
    ctrlReturnResultSet.setVoidCallable(2);

    MockControl ctrlCallableStatement = MockControl.createControl(CallableStatement.class);
    final CallableStatement mockCallableStatement = (CallableStatement) ctrlCallableStatement.getMock();
    if (debugEnabled) {
        mockCallableStatement.getWarnings();
        ctrlCallableStatement.setReturnValue(null);
    }
    mockCallableStatement.close();
    ctrlCallableStatement.setVoidCallable();
    MockControl ctrlCallableStatement2 = MockControl.createControl(CallableStatement.class);
    final CallableStatement mockCallableStatement2 = (CallableStatement) ctrlCallableStatement2.getMock();
    mockCallableStatement2.execute();
    ctrlCallableStatement2.setReturnValue(true);
    mockCallableStatement2.getUpdateCount();
    ctrlCallableStatement2.setReturnValue(-1);
    mockCallableStatement2.getResultSet();
    ctrlCallableStatement2.setReturnValue(mockReturnResultSet);
    mockCallableStatement2.getMoreResults();
    ctrlCallableStatement2.setReturnValue(false);
    mockCallableStatement2.getUpdateCount();
    ctrlCallableStatement2.setReturnValue(-1);

    ctrlResultSet.replay();
    ctrlStatement.replay();
    ctrlStatement2.replay();
    ctrlPreparedStatement.replay();
    ctrlPreparedStatement2.replay();
    ctrlReturnResultSet.replay();
    ;
    ctrlCallableStatement.replay();
    ctrlCallableStatement2.replay();

    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement, 1);
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource);
    template.setNativeJdbcExtractor(new NativeJdbcExtractor() {
        public boolean isNativeConnectionNecessaryForNativeStatements() {
            return false;
        }

        public boolean isNativeConnectionNecessaryForNativePreparedStatements() {
            return false;
        }

        public boolean isNativeConnectionNecessaryForNativeCallableStatements() {
            return false;
        }

        public Connection getNativeConnection(Connection con) {
            return con;
        }

        public Connection getNativeConnectionFromStatement(Statement stmt) throws SQLException {
            return stmt.getConnection();
        }

        public Statement getNativeStatement(Statement stmt) {
            assertTrue(stmt == mockStatement);
            return mockStatement2;
        }

        public PreparedStatement getNativePreparedStatement(PreparedStatement ps) {
            assertTrue(ps == mockPreparedStatement);
            return mockPreparedStatement2;
        }

        public CallableStatement getNativeCallableStatement(CallableStatement cs) {
            assertTrue(cs == mockCallableStatement);
            return mockCallableStatement2;
        }

        public ResultSet getNativeResultSet(ResultSet rs) {
            return rs;
        }
    });

    template.query("my query", new ResultSetExtractor() {
        public Object extractData(ResultSet rs2) {
            assertEquals(mockResultSet, rs2);
            return null;
        }
    });

    template.query(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection conn) {
            return mockPreparedStatement;
        }
    }, new ResultSetExtractor() {
        public Object extractData(ResultSet rs2) {
            assertEquals(mockResultSet, rs2);
            return null;
        }
    });

    template.call(new CallableStatementCreator() {
        public CallableStatement createCallableStatement(Connection con) {
            return mockCallableStatement;
        }
    }, new ArrayList());

    ctrlStatement.verify();
    ctrlStatement2.verify();
    ctrlPreparedStatement.verify();
    ctrlPreparedStatement2.verify();
    ctrlCallableStatement.verify();
    ctrlCallableStatement2.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testStaticResultSetClosed() throws Exception {
    MockControl ctrlResultSet;//ww w .  java  2  s  .  c o  m
    ResultSet mockResultSet;
    MockControl ctrlStatement;
    Statement mockStatement;
    MockControl ctrlResultSet2;
    ResultSet mockResultSet2;
    MockControl ctrlPreparedStatement;
    PreparedStatement mockPreparedStatement;

    ctrlResultSet = MockControl.createControl(ResultSet.class);
    mockResultSet = (ResultSet) ctrlResultSet.getMock();
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    ctrlStatement = MockControl.createControl(Statement.class);
    mockStatement = (Statement) ctrlStatement.getMock();
    mockStatement.executeQuery("my query");
    ctrlStatement.setReturnValue(mockResultSet);
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    ctrlResultSet2 = MockControl.createControl(ResultSet.class);
    mockResultSet2 = (ResultSet) ctrlResultSet2.getMock();
    mockResultSet2.close();
    ctrlResultSet2.setVoidCallable();

    ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet2);
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);
    mockConnection.prepareStatement("my query");
    ctrlConnection.setReturnValue(mockPreparedStatement);

    ctrlResultSet.replay();
    ctrlStatement.replay();
    ctrlResultSet2.replay();
    ctrlPreparedStatement.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource);

    try {
        template.query("my query", new ResultSetExtractor() {
            public Object extractData(ResultSet rs) {
                throw new InvalidDataAccessApiUsageException("");
            }
        });
        fail("Should have thrown InvalidDataAccessApiUsageException");
    } catch (InvalidDataAccessApiUsageException idaauex) {
        // ok
    }

    try {
        template.query(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                return con.prepareStatement("my query");
            }

            public String getSql() {
                return null;
            }
        }, new ResultSetExtractor() {
            public Object extractData(ResultSet rs2) {
                throw new InvalidDataAccessApiUsageException("");
            }
        });
        fail("Should have thrown InvalidDataAccessApiUsageException");
    } catch (InvalidDataAccessApiUsageException idaauex) {
        // ok
    }

    // verify confirms if test is successful by checking if close() called
    ctrlResultSet.verify();
    ctrlStatement.verify();
    ctrlResultSet2.verify();
    ctrlPreparedStatement.verify();
}

From source file:ubic.gemma.core.analysis.expression.coexpression.links.LinkAnalysisServiceTest.java

private void checkUnsupportedLinksHaveNoSupport() {
    JdbcTemplate jt = new JdbcTemplate(dataSource);

    // see SupportDetailsTest for validation that these strings represent empty byte arrays. I think the 1 at
    // position 12 is important.
    final Collection<Long> checkme = new HashSet<>();
    // maybe these patterns aren't this reproducible.
    jt.query(
            // "SELECT ID from MOUSE_LINK_SUPPORT_DETAILS WHERE HEX(BYTES) in ('0000000200000001000000000000000200000000',"
            // + " '000006AA00000001000000000000003600000000', '0000000000000001000000000000000000000000',"
            // + "'0000003E00000001000000000000000200000000','0000003F00000001000000000000000200000000',"
            // + "'0000000500000001000000000000000200000000')", new RowCallbackHandler() {

            // 000002BB00000001000000000000001600000000
            "SELECT ID FROM MOUSE_LINK_SUPPORT_DETAILS WHERE HEX(BYTES) LIKE '00000___0000000100000000000000%'",
            new RowCallbackHandler() {

                @Override//from   w  ww .  j  av a  2s  .c o m
                public void processRow(ResultSet rs) throws SQLException {
                    Long id = rs.getLong(1);
                    checkme.add(id);
                }
            });

    // we should definitely have some of these
    assertTrue(checkme.size() > 0);

    jt.query("SELECT SUPPORT FROM MOUSE_GENE_COEXPRESSION WHERE SUPPORT_DETAILS_FK IN (?) AND SUPPORT > 0",
            new Object[] { checkme.toArray() }, new RowCallbackHandler() {
                @Override
                public void processRow(ResultSet rs) {
                    fail("Should not have had any rows");
                }
            });

}