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

Example 61 with SqlParameter

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

the class SqlQueryTests method testFindCustomerString.

@Test
public void testFindCustomerString() 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_FORENAME_WHERE);
            declareParameter(new SqlParameter(Types.VARCHAR));
            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(String id) {
            return findObject(id);
        }
    }
    CustomerQuery query = new CustomerQuery(dataSource);
    Customer cust = query.findCustomer("rod");
    assertTrue("Customer id was assigned correctly", cust.getId() == 1);
    assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
    verify(preparedStatement).setString(1, "rod");
    verify(connection).prepareStatement(SELECT_ID_FORENAME_WHERE);
    verify(resultSet).close();
    verify(preparedStatement).close();
    verify(connection).close();
}

Example 62 with SqlParameter

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

the class SqlQueryTests method testListCustomersString.

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

        public CustomerQuery(DataSource ds) {
            super(ds, SELECT_ID_FORENAME_WHERE);
            declareParameter(new SqlParameter(Types.VARCHAR));
            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;
        }
    }
    CustomerQuery query = new CustomerQuery(dataSource);
    List<Customer> list = query.execute("one");
    assertTrue("2 results in list", list.size() == 2);
    assertThat(list.get(0).getForename(), is("rod"));
    assertThat(list.get(1).getForename(), is("dave"));
    verify(preparedStatement).setString(1, "one");
    verify(connection).prepareStatement(SELECT_ID_FORENAME_WHERE);
    verify(resultSet).close();
    verify(preparedStatement).close();
    verify(connection).close();
}

Example 63 with SqlParameter

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

the class SqlQueryTests method testFindTooManyCustomers.

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

        public CustomerQuery(DataSource ds) {
            super(ds, SELECT_ID_FORENAME_WHERE);
            declareParameter(new SqlParameter(Types.VARCHAR));
            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(String id) {
            return findObject(id);
        }
    }
    CustomerQuery query = new CustomerQuery(dataSource);
    thrown.expect(IncorrectResultSizeDataAccessException.class);
    try {
        query.findCustomer("rod");
    } finally {
        verify(preparedStatement).setString(1, "rod");
        verify(connection).prepareStatement(SELECT_ID_FORENAME_WHERE);
        verify(resultSet).close();
        verify(preparedStatement).close();
        verify(connection).close();
    }
}

Example 64 with SqlParameter

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

the class SqlQueryTests method testNamedParameterUsingInvalidQuestionMarkPlaceHolders.

@Test
public void testNamedParameterUsingInvalidQuestionMarkPlaceHolders() throws SQLException {
    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_1);
            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 id1) {
            Map<String, Integer> params = new HashMap<>();
            params.put("id1", id1);
            return executeByNamedParam(params);
        }
    }
    CustomerQuery query = new CustomerQuery(dataSource);
    thrown.expect(InvalidDataAccessApiUsageException.class);
    query.findCustomers(1);
}

Example 65 with SqlParameter

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

the class SqlQueryTests method testUpdateCustomers.

@Test
public void testUpdateCustomers() throws SQLException {
    given(resultSet.next()).willReturn(true, true, false);
    given(resultSet.getInt("id")).willReturn(1, 2);
    given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)).willReturn(preparedStatement);
    class CustomerUpdateQuery extends UpdatableSqlQuery<Customer> {

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

        @Override
        protected Customer updateRow(ResultSet rs, int rownum, Map<?, ?> context) throws SQLException {
            rs.updateString(2, "" + context.get(rs.getInt(COLUMN_NAMES[0])));
            return null;
        }
    }
    CustomerUpdateQuery query = new CustomerUpdateQuery(dataSource);
    Map<Integer, String> values = new HashMap<>(2);
    values.put(1, "Rod");
    values.put(2, "Thomas");
    query.execute(2, values);
    verify(resultSet).updateString(2, "Rod");
    verify(resultSet).updateString(2, "Thomas");
    verify(resultSet, times(2)).updateRow();
    verify(preparedStatement).setObject(1, 2, Types.NUMERIC);
    verify(resultSet).close();
    verify(preparedStatement).close();
    verify(connection).close();
}