A Spring JDBC SELECT and INSERT example (Spring DAO)

A Spring JDBC SELECT and INSERT example: Here's the source code for a complete Spring DAO class from a project that I'm currently working on (a Java-based web interface to the open source Nagios project) that shows how to use a few Spring JDBC methods, including both a SELECT example and a simple INSERT example.

I'll add more examples to this site later (and with more introduction/description), but for now I'm just going to drop this Java class sample out here and hope that it helps you "Learn Spring JDBC by example".

Without any further ado, here's the Java source code to a complete Spring DAO class that uses Spring JDBC:

package com.nagios.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.faces.context.FacesContext;
import javax.sql.DataSource;
import com.nagios.model.NagiosCommand;
import com.nagios.model.NagiosObject;
import com.nagios.model.NagiosUser;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

// SimpleJdbcDaoSupport supports varargs and autoboxing
public class NagiosCommandDao 
       extends SimpleJdbcDaoSupport
       implements BaseDao
{
  private static Log logger = LogFactory.getLog(NagiosCommandDao.class);

  private SimpleJdbcTemplate simpleJdbcTemplate;
  private SimpleJdbcInsert insertCommand;

  private static final String SELECT = " SELECT id, command_name, command_line, updated_by_id, updated_time "
                                     + " FROM commands ORDER BY command_name";

  public void setSimpleJdbcTemplate(SimpleJdbcTemplate simpleJdbcTemplate)
  {
    this.simpleJdbcTemplate = simpleJdbcTemplate;
  }

  // these were NagiosCommand
  public List<NagiosCommand> getAllObjects() 
  {
    // execute the spring jdbc select statement
    // (the select statement is shown above)
    return getSimpleJdbcTemplate().query(SELECT,
                new ParameterizedRowMapper<NagiosCommand>() {
                   public NagiosCommand mapRow(ResultSet rs, int rowNum)
                                     throws SQLException 
                   {
                     NagiosCommand command = new NagiosCommand();
                     command.setId( rs.getInt("id") );
                     command.setCommandName( rs.getString("command_name") );
                     command.setCommandLine( rs.getString("command_line") );
                     command.setUpdatedById( rs.getInt("updated_by_id") );
                     command.setUpdatedTime( rs.getTimestamp("updated_time") );
                     return command;
                   }
                });
  }

  public void insertObject(NagiosObject command)
  {
    FacesContext facesContext = FacesContext.getCurrentInstance();
    NagiosUser currentUser = (NagUser)facesContext.getApplication()
          .createValueBinding("#{user}").getValue(facesContext);

    NagiosCommand theCommand = (NagiosCommand)command;

    // note from al: the html editor on this website may have eaten some
    // code right here. if you see anything missing, let me know, and 
    // i'll see if i can dig up the original source code.

    // spring jdbc insert
    this.insertCommand = new SimpleJdbcInsert(getDataSource()) 
          .withTableName("commands") 
          .usingGeneratedKeyColumns("id");

    Map<String, Object> parameters = new HashMap<String, Object>(2); 
    parameters.put("command_name", theCommand.getCommandName()); 
    parameters.put("command_line", theCommand.getCommandLine());
    parameters.put("updated_by_id", currentUser.getId());

    Number newId = insertCommand.executeAndReturnKey(parameters); 
    // thought i might need this, but i don't
//    theCommand.setId(newId.intValue());
    
  }

}

I hope this Spring DAO class can be helpful to some people without any explanation, but if you have any questions or comments leave them below, and I'll be glad to provide more information.