Example usage for org.springframework.jdbc.core SqlParameter SqlParameter

List of usage examples for org.springframework.jdbc.core SqlParameter SqlParameter

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

Introduction

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

Prototype

public SqlParameter(String name, int sqlType)

Source Link

Document

Create a new SqlParameter, supplying name and SQL type.

Usage

From source file:org.springframework.jdbc.core.simple.SimpleJdbcCallTests.java

public void testAddInvoiceFuncWithoutMetaDataUsingMapParamSource() throws Exception {
    final int amount = 1103;
    final int custid = 3;

    initializeAddInvoiceWithoutMetaData(true);

    replay();/*from w w w  .j  a  va2  s .  co m*/

    SimpleJdbcCall adder = new SimpleJdbcCall(mockDataSource).withFunctionName("add_invoice");
    adder.declareParameters(new SqlOutParameter("return", Types.INTEGER),
            new SqlParameter("amount", Types.INTEGER), new SqlParameter("custid", Types.INTEGER));
    Number newId = adder.executeFunction(Number.class,
            new MapSqlParameterSource().addValue("amount", amount).addValue("custid", custid));
    assertEquals(4, newId.intValue());
}

From source file:org.springframework.jdbc.core.simple.SimpleJdbcCallTests.java

public void testAddInvoiceFuncWithoutMetaDataUsingArrayParams() throws Exception {
    final int amount = 1103;
    final int custid = 3;

    initializeAddInvoiceWithoutMetaData(true);

    replay();//  ww  w. j a v a 2s . c o  m

    SimpleJdbcCall adder = new SimpleJdbcCall(mockDataSource).withFunctionName("add_invoice");
    adder.declareParameters(new SqlOutParameter("return", Types.INTEGER),
            new SqlParameter("amount", Types.INTEGER), new SqlParameter("custid", Types.INTEGER));
    Number newId = adder.executeFunction(Number.class, amount, custid);
    assertEquals(4, newId.intValue());
}

From source file:org.springframework.jdbc.object.SqlQueryTests.java

public void testQueryWithoutEnoughParams() {
    replay();/*from   ww w  .  j av a2  s.co  m*/

    MappingSqlQuery query = new MappingSqlQuery() {
        protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
            return new Integer(rs.getInt(1));
        }

    };
    query.setDataSource(mockDataSource);
    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();

    try {
        List list = query.execute();
        fail("Shouldn't succeed in running query without enough params");
    } catch (InvalidDataAccessApiUsageException ex) {
        // OK
    }
}

From source file:org.springframework.jdbc.object.SqlQueryTests.java

public void testQueryWithMissingMapParams() {
    replay();//  w w w  .jav a 2 s.  c om

    MappingSqlQuery query = new MappingSqlQuery() {
        protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
            return new Integer(rs.getInt(1));
        }
    };
    query.setDataSource(mockDataSource);
    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();

    try {
        Map params = new HashMap();
        params.put(COLUMN_NAMES[0], "Value");
        List list = query.executeByNamedParam(params);
        fail("Shouldn't succeed in running query with missing params");
    } catch (InvalidDataAccessApiUsageException ex) {
        // OK
    }
}

From source file:org.springframework.jdbc.object.SqlQueryTests.java

public void testFindCustomerMixed() throws SQLException {
    MockControl ctrlResultSet2;/*  w  w  w . j  av a  2 s.  co  m*/
    ResultSet mockResultSet2;
    MockControl ctrlPreparedStatement2;
    PreparedStatement mockPreparedStatement2;

    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getInt("id");
    ctrlResultSet.setReturnValue(1);
    mockResultSet.getString("forename");
    ctrlResultSet.setReturnValue("rod");
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    mockPreparedStatement.setObject(1, new Integer(1), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setString(2, "rod");
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

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

    ctrlPreparedStatement2 = MockControl.createControl(PreparedStatement.class);
    mockPreparedStatement2 = (PreparedStatement) ctrlPreparedStatement2.getMock();
    mockPreparedStatement2.setObject(1, new Integer(1), Types.INTEGER);
    ctrlPreparedStatement2.setVoidCallable();
    mockPreparedStatement2.setString(2, "Roger");
    ctrlPreparedStatement2.setVoidCallable();
    mockPreparedStatement2.executeQuery();
    ctrlPreparedStatement2.setReturnValue(mockResultSet2);
    if (debugEnabled) {
        mockPreparedStatement2.getWarnings();
        ctrlPreparedStatement2.setReturnValue(null);
    }
    mockPreparedStatement2.close();
    ctrlPreparedStatement2.setVoidCallable();

    mockConnection.prepareStatement(SELECT_ID_WHERE);
    ctrlConnection.setReturnValue(mockPreparedStatement);
    mockConnection.prepareStatement(SELECT_ID_WHERE);
    ctrlConnection.setReturnValue(mockPreparedStatement2);

    ctrlResultSet2.replay();
    ctrlPreparedStatement2.replay();
    replay();

    class CustomerQuery extends MappingSqlQuery {

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

        protected Object 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 (Customer) findObject(new Object[] { new Integer(id), name });
        }
    }

    CustomerQuery query = new CustomerQuery(mockDataSource);

    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);
}

From source file:org.springframework.jdbc.object.SqlQueryTests.java

private void doTestNamedParameterCustomerQuery(final boolean namedDeclarations) throws SQLException {
    mockResultSet.next();//ww w.j  a v a 2 s  .c  o  m
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getInt("id");
    ctrlResultSet.setReturnValue(1);
    mockResultSet.getString("forename");
    ctrlResultSet.setReturnValue("rod");
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setString(2, "UK");
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.prepareStatement(SELECT_ID_FORENAME_NAMED_PARAMETERS_PARSED, ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();

    class CustomerQuery extends MappingSqlQuery {

        public CustomerQuery(DataSource ds) {
            super(ds, SELECT_ID_FORENAME_NAMED_PARAMETERS);
            setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
            if (namedDeclarations) {
                declareParameter(new SqlParameter("country", Types.VARCHAR));
                declareParameter(new SqlParameter("id", Types.NUMERIC));
            } else {
                declareParameter(new SqlParameter(Types.NUMERIC));
                declareParameter(new SqlParameter(Types.VARCHAR));
            }
            compile();
        }

        protected Object 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 country) {
            Map params = new HashMap();
            params.put("id", new Integer(id));
            params.put("country", country);
            return (Customer) executeByNamedParam(params).get(0);
        }
    }

    CustomerQuery query = new CustomerQuery(mockDataSource);
    Customer cust = query.findCustomer(1, "UK");
    assertTrue("Customer id was assigned correctly", cust.getId() == 1);
    assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
}

From source file:org.springframework.jdbc.object.SqlQueryTests.java

public void testNamedParameterInListQuery() throws SQLException {
    mockResultSet.next();//  w  w  w  .  j a v a 2s.c  om
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getInt("id");
    ctrlResultSet.setReturnValue(1);
    mockResultSet.getString("forename");
    ctrlResultSet.setReturnValue("rod");
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getInt("id");
    ctrlResultSet.setReturnValue(2);
    mockResultSet.getString("forename");
    ctrlResultSet.setReturnValue("juergen");
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setObject(2, new Integer(2), Types.NUMERIC);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID_IN_LIST_1, ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();

    class CustomerQuery extends MappingSqlQuery {

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

        protected Object 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 findCustomers(List ids) {
            Map params = new HashMap();
            params.put("ids", ids);
            return (List) executeByNamedParam(params);
        }
    }

    CustomerQuery query = new CustomerQuery(mockDataSource);
    List ids = new ArrayList();
    ids.add(new Integer(1));
    ids.add(new Integer(2));
    List cust = query.findCustomers(ids);

    assertEquals("We got two customers back", cust.size(), 2);
    Assert.assertEquals("First customer id was assigned correctly", ((Customer) cust.get(0)).getId(), 1);
    Assert.assertEquals("First customer forename was assigned correctly",
            ((Customer) cust.get(0)).getForename(), "rod");
    Assert.assertEquals("Second customer id was assigned correctly", ((Customer) cust.get(1)).getId(), 2);
    Assert.assertEquals("Second customer forename was assigned correctly",
            ((Customer) cust.get(1)).getForename(), "juergen");
}

From source file:org.springframework.jdbc.object.SqlQueryTests.java

public void testNamedParameterQueryReusingParameter() throws SQLException {
    mockResultSet.next();//from  ww w .j  a v  a 2s  .c  o m
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getInt("id");
    ctrlResultSet.setReturnValue(1);
    mockResultSet.getString("forename");
    ctrlResultSet.setReturnValue("rod");
    mockResultSet.next();
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getInt("id");
    ctrlResultSet.setReturnValue(2);
    mockResultSet.getString("forename");
    ctrlResultSet.setReturnValue("juergen");
    mockResultSet.next();
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setObject(2, new Integer(1), Types.NUMERIC);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID_REUSED_1, ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();

    class CustomerQuery extends MappingSqlQuery {

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

        protected Object 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 findCustomers(Integer id) {
            Map params = new HashMap();
            params.put("id1", id);
            return (List) executeByNamedParam(params);
        }
    }

    CustomerQuery query = new CustomerQuery(mockDataSource);
    List cust = query.findCustomers(new Integer(1));

    assertEquals("We got two customers back", cust.size(), 2);
    Assert.assertEquals("First customer id was assigned correctly", ((Customer) cust.get(0)).getId(), 1);
    Assert.assertEquals("First customer forename was assigned correctly",
            ((Customer) cust.get(0)).getForename(), "rod");
    Assert.assertEquals("Second customer id was assigned correctly", ((Customer) cust.get(1)).getId(), 2);
    Assert.assertEquals("Second customer forename was assigned correctly",
            ((Customer) cust.get(1)).getForename(), "juergen");
}

From source file:org.springframework.jdbc.object.SqlQueryTests.java

public void testNamedParameterUsingInvalidQuestionMarkPlaceHolders() throws SQLException {

    mockConnection.prepareStatement(SELECT_ID_FORENAME_WHERE_ID_REUSED_1, ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();//w  w  w  .  j  a  v  a 2 s  .  co m

    class CustomerQuery extends MappingSqlQuery {

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

        protected Object 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 findCustomers(Integer id1) {
            Map params = new HashMap();
            params.put("id1", id1);
            return (List) executeByNamedParam(params);
        }
    }

    CustomerQuery query = new CustomerQuery(mockDataSource);
    try {
        List cust = query.findCustomers(new Integer(1));
        fail("Should have caused an InvalidDataAccessApiUsageException");
    } catch (InvalidDataAccessApiUsageException e) {
    }

}

From source file:org.springframework.jdbc.object.SqlUpdateTests.java

private void doTestNamedParameterUpdate(final boolean namedDeclarations) throws SQLException {
    mockPreparedStatement.setObject(1, new Integer(1), Types.NUMERIC);
    mockPreparedStatement.setObject(2, new Integer(1), Types.DECIMAL);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeUpdate();
    ctrlPreparedStatement.setReturnValue(1);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }//w  w  w  .j  ava 2 s.c o m
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.prepareStatement(UPDATE_INT_INT);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();

    class NamedParameterUpdater extends SqlUpdate {

        public NamedParameterUpdater() {
            setSql(UPDATE_NAMED_PARAMETERS);
            setDataSource(mockDataSource);
            if (namedDeclarations) {
                declareParameter(new SqlParameter("priceId", Types.DECIMAL));
                declareParameter(new SqlParameter("perfId", Types.NUMERIC));
            } else {
                declareParameter(new SqlParameter(Types.NUMERIC));
                declareParameter(new SqlParameter(Types.DECIMAL));
            }
            compile();
        }

        public int run(int performanceId, int type) {
            Map params = new HashMap();
            params.put("perfId", new Integer(performanceId));
            params.put("priceId", new Integer(type));
            return updateByNamedParam(params);
        }
    }

    NamedParameterUpdater pc = new NamedParameterUpdater();
    int rowsAffected = pc.run(1, 1);
    assertEquals(1, rowsAffected);
}