NamedParameterJdbcTemplate usage from inside its own Mapper

Question

Im kindda new to Spring, and I'm using spring mvc to compose a simple web page that connects to an mysql db but i'm getting null pointers where I try to use a NamedParameterJdbcTemplate query where another NamedParameterJdbcTemplate is currently executing

The db definition its made through this bean configuration (spring-web-servlet.xml)

<bean id="dataSource" destroy-method="close"
    class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url"
        value="jdbc:mysql://#{systemEnvironment[OPENSHIFT_MYSQL_DB_HOST]}:#{systemEnvironment[OPENSHIFT_MYSQL_DB_PORT]}/#{systemEnvironment[OPENSHIFT_APP_NAME]}" />
    <property name="username"
        value="#{systemEnvironment[OPENSHIFT_MYSQL_DB_USERNAME]}" />
    <property name="password"
        value="#{systemEnvironment[OPENSHIFT_MYSQL_DB_PASSWORD]}" />
    <property name="initialSize" value="3" />
</bean>

And this config class (SpringDBConfig.java)

@Configuration
public class SpringDBConfig {
    @Autowired
    DataSource dataSource;

    @Bean
    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return new NamedParameterJdbcTemplate(dataSource);
    }
}

First of all my DB is of this kind

CREATE TABLE `TIPOSREGALO` (
  `TIPOREGALO_ID` varchar(50) NOT NULL,
  `DESCRIPCION` varchar(5000) NOT NULL,
  `URLFOTO` varchar(255) NOT NULL, 
  PRIMARY KEY (`TIPOREGALO_ID`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `REGALOS` (
  `REGALO_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TIPOREGALO_ID` varchar(50) NOT NULL,
  `TITULO` varchar(50) NOT NULL,
  `DESCRIPCION` varchar(5000) NOT NULL,
  `URLFOTO` varchar(255) NOT NULL, 
  `PRECIO` integer unsigned NOT NULL,
  PRIMARY KEY (`REGALO_ID`) USING BTREE,
  FOREIGN KEY (`TIPOREGALO_ID`) REFERENCES `TIPOSREGALO`(`TIPOREGALO_ID`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8;

My DAO its called from the controller like so

@RequestMapping(value = "/gift/{id}", method = RequestMethod.GET)
public String giftdetail(@PathVariable("id") int regalo_id, Model model) {
    model.addAttribute("regalo", regaloService.findById(regalo_id));
    return "weddinglist/gift";
}

That RegaloService method is

@Override
public Regalo findById(int id) {
    return regaloDao.findById(id);
}

And regaloDao is

 @Repository
    public class RegaloDaoImpl implements RegaloDao {

        private final Logger logger = LoggerFactory.getLogger(RegaloDaoImpl.class);

        NamedParameterJdbcTemplate namedParameterJdbcTemplate;

        @Autowired
        public void setNamedParameterJdbcTemplate(
                NamedParameterJdbcTemplate namedParameterJdbcTemplate)
                throws DataAccessException {
            this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
        }

        @Override
        public Regalo findById(Integer regalo_id) {
            logger.debug("findById() id: \"{}\"", regalo_id);

            Map<String, Object> params = new HashMap<String, Object>();
            params.put("regalo_id", regalo_id);

            String sql = "SELECT * FROM REGALOS WHERE REGALO_ID=:regalo_id";

            Regalo result = null;
            try {
                result = namedParameterJdbcTemplate.queryForObject(sql, params,
                        new RegaloMapper());
            } catch (EmptyResultDataAccessException e) {
                // do nothing, return null
            }
            return result;
        }
    ...

        private static final class RegaloMapper implements RowMapper<Regalo> {

            private final Logger logger = LoggerFactory
                .getLogger(RegaloMapper.class);

            public Regalo mapRow(ResultSet rs, int rowNum) throws SQLException {


                Regalo regalo = new Regalo();
                regalo.setRegalo_id(rs.getInt("REGALO_ID"));

                TipoRegaloDao tipoRegaloDao = new TipoRegaloDaoImpl();
                regalo.setTiporegalo(tipoRegaloDao.findById(rs
                        .getString("TIPOREGALO_ID")));

                regalo.setTitulo(rs.getString("TITULO"));
                regalo.setDescripcion(rs.getString("DESCRIPCION"));
                regalo.setUrlfoto(rs.getString("URLFOTO"));
                regalo.setPrecio(rs.getInt("PRECIO"));
                return regalo;
            }
        }
    }

As you can see, a call to regalo.setTiporegalo(tipoRegaloDao.findById(rs.getString("TIPOREGALO_ID"))); is made inside the Mapper when the former call to queryForObject isnt finished, then the execution goes to TipoRegaloDaoImpl.findById() and tries to call a nulled namedParameterJdbcTemplate object

@Repository
public class TipoRegaloDaoImpl implements TipoRegaloDao {

    private final Logger logger = LoggerFactory
            .getLogger(TipoRegaloDaoImpl.class);

    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    public void setNamedParameterJdbcTemplate(
            NamedParameterJdbcTemplate namedParameterJdbcTemplate)
            throws DataAccessException {
        logger.debug("setNamedParameterJdbcTemplate() namedParameterJdbcTemplate: \"{}\"", namedParameterJdbcTemplate);
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    @Override
    public TipoRegalo findById(String tiporegalo_id) {
        logger.debug("findById() id: \"{}\"", tiporegalo_id);

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("tiporegalo_id", tiporegalo_id);

        String sql = "SELECT * FROM TIPOSREGALO WHERE TIPOREGALO_ID=:tiporegalo_id";

        TipoRegalo result = null;
        try {
            result = namedParameterJdbcTemplate.queryForObject(sql, params,
                        new TipoRegaloMapper());

            logger.debug("findById() result: \"{}\"", result);


        } catch (EmptyResultDataAccessException e) {
            logger.error("findById() Resultado vacio, excepcion " + e);
            // do nothing, return null
        } catch (Exception e) {
            logger.error("findById() No se que ha pasado aqui, excepcion " + e);
            // do nothing, return null
        }
        return result;
    }
 ....

And queryForObject fails with a nullpointerexception

Any clues?

Thanks

score 1 · Accepted Answer · answered Mar 05 '16 at 19:57

TipoRegaloDao tipoRegaloDao = new TipoRegaloDaoImpl();

This creates a TipoRegaloDaoImpl, and never initializes its namedParameterJdbcTemplate. Spring can only autowire beans that it creates itself. Not the ones you create yourself using new. Inject the TipoRegaloDao inside the RegaloDaoImpl (and make the mapper an inner class instead of a nested static class to let it access the field).

You'd never have this problem if you used constructor injection rather than field injection, because it would then be obvious that the TipoRegaloDaoImpl needs a namedParameterJdbcTemplate: it would be one of its constructor arguments. But in general, every time you use new to create a Spring bean, something is wrong.

NamedParameterJdbcTemplate usage from inside its own Mapper

1 Answers1