Examples with SqlParameter org.springframework.jdbc.core.SqlParameter used on opensource projects

Example 31 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project spring-framework by spring-projects.

the class SqlQueryTests method testFindCustomerIntInt.

@Test
public void testFindCustomerIntInt() throws SQLException {
    given(resultSet.next()).willReturn(true, false);
    given(resultSet.getInt("id")).willReturn(1);
    given(resultSet.getString("forename")).willReturn("rod");
    class CustomerQuery extends MappingSqlQuery<Customer> {

        public CustomerQuery(DataSource ds) {
            super(ds, SELECT_ID_WHERE);
            declareParameter(new SqlParameter(Types.NUMERIC));
            declareParameter(new SqlParameter(Types.NUMERIC));
            compile();
        }

        @Override
        protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
            Customer cust = new Customer();
            cust.setId(rs.getInt(COLUMN_NAMES[0]));
            cust.setForename(rs.getString(COLUMN_NAMES[1]));
            return cust;
        }

        public Customer findCustomer(int id, int otherNum) {
            return findObject(id, otherNum);
        }
    }
    CustomerQuery query = new CustomerQuery(dataSource);
    Customer cust = query.findCustomer(1, 1);
    assertTrue("Customer id was assigned correctly", cust.getId() == 1);
    assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
    verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
    verify(preparedStatement).setObject(2, 1, Types.NUMERIC);
    verify(connection).prepareStatement(SELECT_ID_WHERE);
    verify(resultSet).close();
    verify(preparedStatement).close();
    verify(connection).close();
}

Example 32 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project spring-framework by spring-projects.

the class SqlQueryTests method testNamedParameterQueryReusingParameter.

@Test
public void testNamedParameterQueryReusingParameter() throws SQLException {
    given(resultSet.next()).willReturn(true, true, false);
    given(resultSet.getInt("id")).willReturn(1, 2);
    given(resultSet.getString("forename")).willReturn("rod", "juergen");
    given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID_REUSED_1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)).willReturn(preparedStatement);
    class CustomerQuery extends MappingSqlQuery<Customer> {

        public CustomerQuery(DataSource ds) {
            super(ds, SELECT_ID_FORENAME_WHERE_ID_REUSED_2);
            setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
            declareParameter(new SqlParameter("id1", Types.NUMERIC));
            compile();
        }

        @Override
        protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
            Customer cust = new Customer();
            cust.setId(rs.getInt(COLUMN_NAMES[0]));
            cust.setForename(rs.getString(COLUMN_NAMES[1]));
            return cust;
        }

        public List<Customer> findCustomers(Integer id) {
            Map<String, Object> params = new HashMap<>();
            params.put("id1", id);
            return executeByNamedParam(params);
        }
    }
    CustomerQuery query = new CustomerQuery(dataSource);
    List<Customer> cust = query.findCustomers(1);
    assertEquals("We got two customers back", cust.size(), 2);
    assertEquals("First customer id was assigned correctly", cust.get(0).getId(), 1);
    assertEquals("First customer forename was assigned correctly", cust.get(0).getForename(), "rod");
    assertEquals("Second customer id was assigned correctly", cust.get(1).getId(), 2);
    assertEquals("Second customer forename was assigned correctly", cust.get(1).getForename(), "juergen");
    verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
    verify(preparedStatement).setObject(2, 1, Types.NUMERIC);
    verify(resultSet).close();
    verify(preparedStatement).close();
    verify(connection).close();
}

Example 33 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project spring-framework by spring-projects.

the class SqlQueryTests method testNamedParameterInListQuery.

@Test
public void testNamedParameterInListQuery() throws SQLException {
    given(resultSet.next()).willReturn(true, true, false);
    given(resultSet.getInt("id")).willReturn(1, 2);
    given(resultSet.getString("forename")).willReturn("rod", "juergen");
    given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID_IN_LIST_1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)).willReturn(preparedStatement);
    class CustomerQuery extends MappingSqlQuery<Customer> {

        public CustomerQuery(DataSource ds) {
            super(ds, SELECT_ID_FORENAME_WHERE_ID_IN_LIST_2);
            setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
            declareParameter(new SqlParameter("ids", Types.NUMERIC));
            compile();
        }

        @Override
        protected Customer mapRow(ResultSet rs, int rownum) throws SQLException {
            Customer cust = new Customer();
            cust.setId(rs.getInt(COLUMN_NAMES[0]));
            cust.setForename(rs.getString(COLUMN_NAMES[1]));
            return cust;
        }

        public List<Customer> findCustomers(List<Integer> ids) {
            Map<String, Object> params = new HashMap<>();
            params.put("ids", ids);
            return executeByNamedParam(params);
        }
    }
    CustomerQuery query = new CustomerQuery(dataSource);
    List<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);
    List<Customer> cust = query.findCustomers(ids);
    assertEquals("We got two customers back", cust.size(), 2);
    assertEquals("First customer id was assigned correctly", cust.get(0).getId(), 1);
    assertEquals("First customer forename was assigned correctly", cust.get(0).getForename(), "rod");
    assertEquals("Second customer id was assigned correctly", cust.get(1).getId(), 2);
    assertEquals("Second customer forename was assigned correctly", cust.get(1).getForename(), "juergen");
    verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
    verify(preparedStatement).setObject(2, 2, Types.NUMERIC);
    verify(resultSet).close();
    verify(preparedStatement).close();
    verify(connection).close();
}

Example 34 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project spring-framework by spring-projects.

the class SqlQueryTests method testQueryWithoutEnoughParams.

@Test
public void testQueryWithoutEnoughParams() {
    MappingSqlQuery<Integer> query = new MappingSqlQuery<Integer>() {

        @Override
        protected Integer mapRow(ResultSet rs, int rownum) throws SQLException {
            return rs.getInt(1);
        }
    };
    query.setDataSource(dataSource);
    query.setSql(SELECT_ID_WHERE);
    query.declareParameter(new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
    query.declareParameter(new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
    query.compile();
    thrown.expect(InvalidDataAccessApiUsageException.class);
    query.execute();
}

Example 35 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project musiccabinet by hakko.

the class JdbcGroupWeeklyArtistChartDao method batchInsert.

private void batchInsert(GroupWeeklyArtistChart artistChart) {
    String sql = "insert into music.groupweeklyartistchart_import (lastfmgroup_name, artist_name, playcount) values (?,?,?)";
    BatchSqlUpdate batchUpdate = new BatchSqlUpdate(jdbcTemplate.getDataSource(), sql);
    batchUpdate.setBatchSize(1000);
    batchUpdate.declareParameter(new SqlParameter("lastfmgroup_name", Types.VARCHAR));
    batchUpdate.declareParameter(new SqlParameter("artist_name", Types.VARCHAR));
    batchUpdate.declareParameter(new SqlParameter("playcount", Types.INTEGER));
    for (ArtistPlayCount apc : artistChart.getArtistPlayCounts()) {
        batchUpdate.update(new Object[] { artistChart.getGroupName(), apc.getArtist().getName(), apc.getPlayCount() });
    }
    batchUpdate.flush();
}