package org.springframework.samples.petclinic.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jmx.export.annotation.ManagedOperation;
import org.springframework.jmx.export.annotation.ManagedResource;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.samples.petclinic.Clinic;
import org.springframework.samples.petclinic.Owner;
import org.springframework.samples.petclinic.Pet;
import org.springframework.samples.petclinic.PetType;
import org.springframework.samples.petclinic.Specialty;
import org.springframework.samples.petclinic.Vet;
import org.springframework.samples.petclinic.Visit;
import org.springframework.samples.petclinic.util.EntityUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
/**
* A simple JDBC-based implementation of the {@link Clinic} interface.
*
* <p>This class uses Java 5 language features and the {@link SimpleJdbcTemplate}
* plus {@link SimpleJdbcInsert}. It also takes advantage of classes like
* {@link BeanPropertySqlParameterSource} and
* {@link ParameterizedBeanPropertyRowMapper} which provide automatic mapping
* between JavaBean properties and JDBC parameters or query results.
*
* <p>SimpleJdbcClinic is a rewrite of the AbstractJdbcClinic which was the base
* class for JDBC implementations of the Clinic interface for Spring 2.0.
*
* @author Ken Krebs
* @author Juergen Hoeller
* @author Rob Harrop
* @author Sam Brannen
* @author Thomas Risberg
* @author Mark Fisher
*/
@Service
@ManagedResource("petclinic:type=Clinic")
public class SimpleJdbcClinic implements Clinic, SimpleJdbcClinicMBean {
private final Logger logger = LoggerFactory.getLogger(getClass());
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcInsert insertOwner;
private SimpleJdbcInsert insertPet;
private SimpleJdbcInsert insertVisit;
private final List<Vet> vets = new ArrayList<Vet>();
@Autowired
public void init(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
this.insertOwner = new SimpleJdbcInsert(dataSource)
.withTableName("owners")
.usingGeneratedKeyColumns("id");
this.insertPet = new SimpleJdbcInsert(dataSource)
.withTableName("pets")
.usingGeneratedKeyColumns("id");
this.insertVisit = new SimpleJdbcInsert(dataSource)
.withTableName("visits")
.usingGeneratedKeyColumns("id");
}
/**
* Refresh the cache of Vets that the Clinic is holding.
* @see org.springframework.samples.petclinic.Clinic#getVets()
*/
@ManagedOperation
@Transactional(readOnly = true)
public void refreshVetsCache() throws DataAccessException {
synchronized (this.vets) {
this.logger.info("Refreshing vets cache");
// Retrieve the list of all vets.
this.vets.clear();
this.vets.addAll(this.simpleJdbcTemplate.query(
"SELECT id, first_name, last_name FROM vets ORDER BY last_name,first_name",
ParameterizedBeanPropertyRowMapper.newInstance(Vet.class)));
// Retrieve the list of all possible specialties.
final List<Specialty> specialties = this.simpleJdbcTemplate.query(
"SELECT id, name FROM specialties",
ParameterizedBeanPropertyRowMapper.newInstance(Specialty.class));
// Build each vet's list of specialties.
for (Vet vet : this.vets) {
final List<Integer> vetSpecialtiesIds = this.simpleJdbcTemplate.query(
"SELECT specialty_id FROM vet_specialties WHERE vet_id=?",
new ParameterizedRowMapper<Integer>() {
public Integer mapRow(ResultSet rs, int row) throws SQLException {
return Integer.valueOf(rs.getInt(1));
}},
vet.getId().intValue());
for (int specialtyId : vetSpecialtiesIds) {
Specialty specialty = EntityUtils.getById(specialties, Specialty.class, specialtyId);
vet.addSpecialty(specialty);
}
}
}
}
// START of Clinic implementation section *******************************
@Transactional(readOnly = true)
public Collection<Vet> getVets() throws DataAccessException {
synchronized (this.vets) {
if (this.vets.isEmpty()) {
refreshVetsCache();
}
return this.vets;
}
}
@Transactional(readOnly = true)
public Collection<PetType> getPetTypes() throws DataAccessException {
return this.simpleJdbcTemplate.query(
"SELECT id, name FROM types ORDER BY name",
ParameterizedBeanPropertyRowMapper.newInstance(PetType.class));
}
/**
* Loads {@link Owner Owners} from the data store by last name, returning
* all owners whose last name <i>starts</i> with the given name; also loads
* the {@link Pet Pets} and {@link Visit Visits} for the corresponding
* owners, if not already loaded.
*/
@Transactional(readOnly = true)
public Collection<Owner> findOwners(String lastName) throws DataAccessException {
List<Owner> owners = this.simpleJdbcTemplate.query(
"SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE last_name like ?",
ParameterizedBeanPropertyRowMapper.newInstance(Owner.class),
lastName + "%");
loadOwnersPetsAndVisits(owners);
return owners;
}
/**
* Loads the {@link Owner} with the supplied <code>id</code>; also loads
* the {@link Pet Pets} and {@link Visit Visits} for the corresponding
* owner, if not already loaded.
*/
@Transactional(readOnly = true)
public Owner loadOwner(int id) throws DataAccessException {
Owner owner;
try {
owner = this.simpleJdbcTemplate.queryForObject(
"SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE id=?",
ParameterizedBeanPropertyRowMapper.newInstance(Owner.class),
id);
}
catch (EmptyResultDataAccessException ex) {
throw new ObjectRetrievalFailureException(Owner.class, new Integer(id));
}
loadPetsAndVisits(owner);
return owner;
}
@Transactional(readOnly = true)
public Pet loadPet(int id) throws DataAccessException {
JdbcPet pet;
try {
pet = this.simpleJdbcTemplate.queryForObject(
"SELECT id, name, birth_date, type_id, owner_id FROM pets WHERE id=?",
new JdbcPetRowMapper(),
id);
}
catch (EmptyResultDataAccessException ex) {
throw new ObjectRetrievalFailureException(Pet.class, new Integer(id));
}
Owner owner = loadOwner(pet.getOwnerId());
owner.addPet(pet);
pet.setType(EntityUtils.getById(getPetTypes(), PetType.class, pet.getTypeId()));
loadVisits(pet);
return pet;
}
@Transactional
public void storeOwner(Owner owner) throws DataAccessException {
if (owner.isNew()) {
Number newKey = this.insertOwner.executeAndReturnKey(
new BeanPropertySqlParameterSource(owner));
owner.setId(newKey.intValue());
}
else {
this.simpleJdbcTemplate.update(
"UPDATE owners SET first_name=:firstName, last_name=:lastName, address=:address, " +
"city=:city, telephone=:telephone WHERE id=:id",
new BeanPropertySqlParameterSource(owner));
}
}
@Transactional
public void storePet(Pet pet) throws DataAccessException {
if (pet.isNew()) {
Number newKey = this.insertPet.executeAndReturnKey(
createPetParameterSource(pet));
pet.setId(newKey.intValue());
}
else {
this.simpleJdbcTemplate.update(
"UPDATE pets SET name=:name, birth_date=:birth_date, type_id=:type_id, " +
"owner_id=:owner_id WHERE id=:id",
createPetParameterSource(pet));
}
}
@Transactional
public void storeVisit(Visit visit) throws DataAccessException {
if (visit.isNew()) {
Number newKey = this.insertVisit.executeAndReturnKey(
createVisitParameterSource(visit));
visit.setId(newKey.intValue());
}
else {
throw new UnsupportedOperationException("Visit update not supported");
}
}
public void deletePet(int id) throws DataAccessException {
this.simpleJdbcTemplate.update("DELETE FROM pets WHERE id=?", id);
}
// END of Clinic implementation section ************************************
/**
* Creates a {@link MapSqlParameterSource} based on data values from the
* supplied {@link Pet} instance.
*/
private MapSqlParameterSource createPetParameterSource(Pet pet) {
return new MapSqlParameterSource()
.addValue("id", pet.getId())
.addValue("name", pet.getName())
.addValue("birth_date", pet.getBirthDate())
.addValue("type_id", pet.getType().getId())
.addValue("owner_id", pet.getOwner().getId());
}
/**
* Creates a {@link MapSqlParameterSource} based on data values from the
* supplied {@link Visit} instance.
*/
private MapSqlParameterSource createVisitParameterSource(Visit visit) {
return new MapSqlParameterSource()
.addValue("id", visit.getId())
.addValue("visit_date", visit.getDate())
.addValue("description", visit.getDescription())
.addValue("pet_id", visit.getPet().getId());
}
/**
* Loads the {@link Visit} data for the supplied {@link Pet}.
*/
private void loadVisits(JdbcPet pet) {
final List<Visit> visits = this.simpleJdbcTemplate.query(
"SELECT id, visit_date, description FROM visits WHERE pet_id=?",
new ParameterizedRowMapper<Visit>() {
public Visit mapRow(ResultSet rs, int row) throws SQLException {
Visit visit = new Visit();
visit.setId(rs.getInt("id"));
visit.setDate(rs.getTimestamp("visit_date"));
visit.setDescription(rs.getString("description"));
return visit;
}
},
pet.getId().intValue());
for (Visit visit : visits) {
pet.addVisit(visit);
}
}
/**
* Loads the {@link Pet} and {@link Visit} data for the supplied
* {@link Owner}.
*/
private void loadPetsAndVisits(final Owner owner) {
final List<JdbcPet> pets = this.simpleJdbcTemplate.query(
"SELECT id, name, birth_date, type_id, owner_id FROM pets WHERE owner_id=?",
new JdbcPetRowMapper(),
owner.getId().intValue());
for (JdbcPet pet : pets) {
owner.addPet(pet);
pet.setType(EntityUtils.getById(getPetTypes(), PetType.class, pet.getTypeId()));
loadVisits(pet);
}
}
/**
* Loads the {@link Pet} and {@link Visit} data for the supplied
* {@link List} of {@link Owner Owners}.
*
* @param owners the list of owners for whom the pet and visit data should be loaded
* @see #loadPetsAndVisits(Owner)
*/
private void loadOwnersPetsAndVisits(List<Owner> owners) {
for (Owner owner : owners) {
loadPetsAndVisits(owner);
}
}
/**
* {@link ParameterizedRowMapper} implementation mapping data from a
* {@link ResultSet} to the corresponding properties of the {@link JdbcPet} class.
*/
private class JdbcPetRowMapper implements ParameterizedRowMapper<JdbcPet> {
public JdbcPet mapRow(ResultSet rs, int rownum) throws SQLException {
JdbcPet pet = new JdbcPet();
pet.setId(rs.getInt("id"));
pet.setName(rs.getString("name"));
pet.setBirthDate(rs.getDate("birth_date"));
pet.setTypeId(rs.getInt("type_id"));
pet.setOwnerId(rs.getInt("owner_id"));
return pet;
}
}
}