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;
 		}
 	}
 
 }