Spring Dao - JDBC SELECT statement examples

Spring JDBC/Dao FAQ: Can you share some Spring JDBC examples, specifically SELECT query examples using Spring Dao objects?

Sure. I've done a lot of work with Spring lately, and I love the Spring Dao approach, so this page is a collection of Spring JDBC SELECT query examples (Spring DAO examples) from a real-world Java project I've been working on. In each example I try to show each type of SQL SELECT query that I've used in that project, with a brief explanation before each SQL query.

(The project is a Java-based web interface for the open source Nagios project, but that doesn't matter too much. I just wanted to explain why you're going to see the name "Nagios" in these examples.)

You'll see that many of these examples refer to a NagiosRowMapper. I show the code for that RowMapper (which is an instance of the Spring Framework ParameterizedRowMapper) near the end of this blog post, so you can refer to it as needed.

Spring JDBC/Dao example #1: Perform a query for exactly one field

In this first Spring Dao example, I use Spring JDBC to get exactly one String field from one record. That is, this Spring DAO method shows how to return one String, not a list of String items:

// spring jdbc select example (returns a String)
public String getNagiosHostTemplateNameById(int hostTemplateId)
{
  String SELECT = " SELECT name "
                + " FROM hosts"
                + " WHERE is_template=true AND id = :id";
  SqlParameterSource namedParameters = new MapSqlParameterSource("id", hostTemplateId);
  return (String)getSimpleJdbcTemplate().queryForObject(SELECT, String.class, namedParameters);
}

Spring DAO example #2: How to perform a query for one complex object

In this Spring Dao example, I perform a JDBC SELECT query for the given hostId, and then return a complex object (a NagiosHost object) from the query results:

// spring jdbc select example (returns an Object)
public NagiosHost getNagiosHostById(int hostId) 
{
  System.err.println("\nENTERED BaseNagiosHostDao::getNagiosHostById");
  String SELECT = " SELECT * "
                + " FROM hosts"
                + " WHERE is_template=false AND id = :id";
  SqlParameterSource namedParameters = new MapSqlParameterSource("id", hostId);
  return (NagiosHost)getSimpleJdbcTemplate().queryForObject(SELECT, new NagiosHostMapper(), namedParameters);
}

Spring DAO example #3: Pass a parameter in, get a list/collection back

In this next Spring Dao example, I perform a JDBC SELECT query for the given hostId, and then return a list of complex objects (NagiosHost objects) from the query results:

// spring jdbc select example (returns a List)
public List getAllHostsExceptGivenId(int hostId) 
{
  String SELECT = " SELECT * FROM hosts "
    + " WHERE is_template=false"
    + " AND id <> ?"
    + " ORDER BY host_name";

  return getSimpleJdbcTemplate().query(SELECT, new NagiosHostMapper(), new Object[]{hostId} );
}

Spring JDBC example #4: Pass in no parameters and get a list/collection back

In this next Spring JDBC example, I return a list of all NagiosHost objects from a SQL SELECT statement:

// spring jdbc select example (returns a List)
public List getAllHosts() 
{
  String SELECT_ALL = " SELECT * FROM hosts "
    + " WHERE is_template=false" 
    + " ORDER BY host_name";

  return getSimpleJdbcTemplate().query(SELECT_ALL, new NagiosHostMapper());
}

Spring DAO example #5: Pass in a parameter and query for a record count

In this Spring Dao example, I show how to get a record count that matches the criteria passed in:

// spring jdbc select example
// returns a count of hosts (isTemplate==false) or templates (isTemplate==true)
//
public int getHostCount(boolean isTemplate)
{ 
  String SELECT_COUNT = "SELECT COUNT(0) FROM hosts WHERE is_template = :is_template"; 
  SqlParameterSource namedParameters = new MapSqlParameterSource("is_template", isTemplate); 
  return getSimpleJdbcTemplate().queryForInt(sql, namedParameters);
} 

Spring JDBC example #6: Query for a record count (no parameters passed in)

In this final Spring JDBC example, I show how to get a record count that matches the criteria passed in:

// spring jdbc select example
// returns a count of hosts (isTemplate==false) or templates (isTemplate==true)
//
public int getHostCount()
{ 
  String SELECT_COUNT = "SELECT COUNT(0) FROM hosts WHERE is_template = false"; 
  SqlParameterSource namedParameters = new MapSqlParameterSource("is_template", isTemplate); 
  return getSimpleJdbcTemplate().queryForInt(sql, namedParameters);
} 

The Spring RowMapper class needed for the earlier examples

For most of the previous examples referred to a NagiosHostMapper class. The following Spring JDBC example shows most of that class, where I put a comment by the repetitive lines that I've omitted. Just create a class like this as an inner class in your Spring DAO class, and you'll be ready to go.

//
// create this as an inner class in your spring dao class
//
protected static final class NagiosHostMapper implements ParameterizedRowMapper
{
  public NagiosHost mapRow(ResultSet rs, int rowNum)
  throws SQLException 
  {
    NagiosHost host = new NagiosHost();
    host.setId( rs.getInt("id") );
    host.setIsTemplate( rs.getBoolean("is_template") );
    host.setName( rs.getString("name") );
    //
    // i skipped 30+ lines here to keep this short ...
    //
    host.setInitialStateUp( rs.getBoolean("initial_state_up") );
    host.setUpdatedBy( rs.getInt("updated_by_id") );
    host.setUpdatedTime( rs.getTimestamp("updated_time") );
    return host;
  }
}

Other Spring DAO class details

It may also help to know a few other details about my Spring DAO class. I'll show some of the information here, and then add more details in another blog post when I have more time.

For now, here are the other parts of my Spring DAO class that I think are important:

/**
 * Some details from my Spring DAO class.
 */
public class BaseNagiosHostDao
  extends SimpleJdbcDaoSupport
  implements BaseDao
{
  protected SimpleJdbcTemplate simpleJdbcTemplate;
  protected TransactionTemplate transactionTemplate;  

  // used for dynamic insert statements
  SimpleJdbcInsert insertHost;

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

  // spring-injected
  public void setTransactionTemplate(TransactionTemplate transactionTemplate) 
  {
    this.transactionTemplate = transactionTemplate;
  }

  //
  // the rest of the methods i've shown above are in this area ...
  //

}