Use JdbcTemplate To Execute Aggregate Function: queryForLong : JdbcTemplate « Spring « Java

Use JdbcTemplate To Execute Aggregate Function: queryForLong

File: context.xml

<?xml version="1.0" encoding="UTF-8"?>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
  <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
  <property name="url" value="jdbc:hsqldb:mem:."/>
  <property name="username" value="sa"/>
  <property name="password" value=""/>

<bean id="employeeDao" class="EmployeeDaoImpl">
  <property name="dataSource" ref="dataSource"/>



import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.util.FileCopyUtils;

//import org.apache.commons.dbcp.BasicDataSource;

public class EmployeeDaoImpl extends JdbcDaoSupport{
  protected void initDao() throws Exception {
    getJdbcTemplate().setNativeJdbcExtractor(new SimpleNativeJdbcExtractor());


  public int getTotalNumberOfEmployees() {
    return getJdbcTemplate().queryForInt("SELECT COUNT(0) FROM employees");
  public long getTotalAge() {
    return getJdbcTemplate().queryForLong("SELECT SUM(age) FROM employee");

  public long getAverageAge() {
    return getJdbcTemplate().queryForLong("SELECT AVG(age) FROM employee");

  public long getOldestAge() {
    return getJdbcTemplate().queryForLong("SELECT MAX(age) FROM employee");

  public long getYoungestAge() {
    return getJdbcTemplate().queryForLong("SELECT MIN(age) FROM employee");


class Employee {
  private Integer id;

  private Name name = new Name();

  private Integer age;

  private Sex sex;

  private Address address = new Address();

  private List<PhoneNumber> phoneNumbers = new ArrayList<PhoneNumber>();

  public Employee() {

  public Employee(String firstName, String lastName) {

  void setId(Integer id) { = id;

  public Integer getId() {
    return id;

  public Address getAddress() {
    return address;

  public Integer getAge() {
    return age;

  public void setAge(Integer age) {
    this.age = age;

  public Name getName() {
    return name;

  public List<PhoneNumber> getPhoneNumbers() {
    return Collections.unmodifiableList(phoneNumbers);

  public void addPhoneNumber(PhoneNumber phoneNumber) {

  public void removePhoneNumber(PhoneNumber phoneNumber) {

  public void removePhoneNumber(int index) {

  public Sex getSex() {
    return sex;

  public void setSex(Sex sex) { = sex;

abstract class Sex {

  public static final Sex MALE = new Male();

  public static final Sex FEMALE = new Female();

  public boolean equals(Object o) {
    if (o == null) {
      return false;
    return getClass().equals(o.getClass());

class PhoneNumber {


class Address {
  private String line1;

  private String line2;

  private String city;

  private String state;

  private String zip;

  public void setLine1(String line1) {
    this.line1 = line1;

  public String getLine1() {
    return this.line1;

  public void setLine2(String line2) {
    this.line2 = line2;

  public String getLine2() {
    return this.line2;

  public void setCity(String city) { = city;

  public String getCity() {

  public void setState(String state) {
    this.state = state;

  public String getState() {
    return this.state;

  public void setZip(String zip) { = zip;

  public String getZip() {

final class Male extends Sex {
  protected Male() {


final class Female extends Sex {
  protected Female() {


class Name {
  private String first;

  private String middle;

  private String last;

  public void setFirst(String first) {
    this.first = first;

  public String getFirst() {
    return this.first;

  public void setMiddle(String middle) {
    this.middle = middle;

  public String getMiddle() {
    return this.middle;

  public void setLast(String last) {
    this.last = last;

  public String getLast() {
    return this.last;


import java.util.Date;
import java.util.GregorianCalendar;

import org.springframework.context.ApplicationContext;

class Main {
  public static void main(String args[]) throws Exception {
    ApplicationContext ctx = new ClassPathXmlApplicationContext(
    EmployeeDaoImpl ws = (EmployeeDaoImpl) ctx.getBean("employeeDao");


       3,660 k)

Related examples in the same category

1.Use queryForInt To Execute Aggregate Function
2.Use JdbcTemplate To Execute Delete Statement With Parameter
3.Use JdbcTemplate To Create Table
4.Pass DataSource to se JdbcTemplate
5.Use batchUpdate from JdbcTemplate To Execute Two UpdateStatements
6.UpdateStatement With Two Parameters
7.Execute Update and Delete Statements With JdbcTemplate
8.Query For Rowset
9.Query For Object, Return String
10.Query For Map
11.Query For List, Returns List Of Linked HashMap
12.Query For List
13.Pass Parameter As Object Array
14.Pass Object Array To Query Method In JdbcTemplate
15.Number Of Row Affected
16.Execute Delete Statement
17.Deal With TimeStamp
18.Control Parameter Type In JdbcTemplate.Query