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

Example 16 with SqlParameter

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

the class SqlQueryTests method testFancyCustomerQuery.

@Test
public void testFancyCustomerQuery() throws SQLException {
    given(resultSet.next()).willReturn(true, false);
    given(resultSet.getInt("id")).willReturn(1);
    given(resultSet.getString("forename")).willReturn("rod");
    given(connection.prepareStatement(SELECT_ID_FORENAME_WHERE, 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);
            setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
            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) {
            return findObject(id);
        }
    }
    CustomerQuery query = new CustomerQuery(dataSource);
    Customer cust = query.findCustomer(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(resultSet).close();
    verify(preparedStatement).close();
    verify(connection).close();
}

Example 17 with SqlParameter

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

the class SqlQueryTests method testFindCustomerMixed.

@Test
public void testFindCustomerMixed() throws SQLException {
    reset(connection);
    PreparedStatement preparedStatement2 = mock(PreparedStatement.class);
    ResultSet resultSet2 = mock(ResultSet.class);
    given(preparedStatement2.executeQuery()).willReturn(resultSet2);
    given(resultSet.next()).willReturn(true, false);
    given(resultSet.getInt("id")).willReturn(1);
    given(resultSet.getString("forename")).willReturn("rod");
    given(resultSet2.next()).willReturn(false);
    given(connection.prepareStatement(SELECT_ID_WHERE)).willReturn(preparedStatement, preparedStatement2);
    class CustomerQuery extends MappingSqlQuery<Customer> {

        public CustomerQuery(DataSource ds) {
            super(ds, SELECT_ID_WHERE);
            declareParameter(new SqlParameter(COLUMN_NAMES[0], COLUMN_TYPES[0]));
            declareParameter(new SqlParameter(COLUMN_NAMES[1], COLUMN_TYPES[1]));
            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, String name) {
            return findObject(new Object[] { id, name });
        }
    }
    CustomerQuery query = new CustomerQuery(dataSource);
    Customer cust1 = query.findCustomer(1, "rod");
    assertTrue("Found customer", cust1 != null);
    assertTrue("Customer id was assigned correctly", cust1.getId() == 1);
    Customer cust2 = query.findCustomer(1, "Roger");
    assertTrue("No customer found", cust2 == null);
    verify(preparedStatement).setObject(1, 1, Types.INTEGER);
    verify(preparedStatement).setString(2, "rod");
    verify(preparedStatement2).setObject(1, 1, Types.INTEGER);
    verify(preparedStatement2).setString(2, "Roger");
    verify(resultSet).close();
    verify(resultSet2).close();
    verify(preparedStatement).close();
    verify(preparedStatement2).close();
    verify(connection, times(2)).close();
}

Example 18 with SqlParameter

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

the class SqlQueryTests method testQueryWithMissingMapParams.

@Test
public void testQueryWithMissingMapParams() {
    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.executeByNamedParam(Collections.singletonMap(COLUMN_NAMES[0], "value"));
}

Example 19 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 20 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();
}