org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource#org.springframework.samples.petclinic.model.Owner源码实例Demo

@RequestMapping(value = "/owners", method = RequestMethod.GET)
public String processFindForm(Owner owner, BindingResult result, Map<String, Object> model) {

    // allow parameterless GET request for /owners to return all records
    if (owner.getLastName() == null) {
        owner.setLastName(""); // empty string signifies broadest possible search
    }

    // find owners by last name
    Collection<Owner> results = this.clinicService.findOwnerByLastName(owner.getLastName());
    if (results.isEmpty()) {
        // no owners found
        result.rejectValue("lastName", "notFound", "not found");
        return "owners/findOwners";
    }
    else if (results.size() == 1) {
	// 1 owner found
	owner = results.iterator().next();
	return "redirect:/owners/" + owner.getId();
    }
    else {
        // multiple owners found
        model.put("selections", results);
        return "owners/ownersList";
    }
}
 

/**
 * 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.
 */
@Override
public Owner findById(int id) throws DataAccessException {
    Owner owner;
    try {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", id);
        owner = this.namedParameterJdbcTemplate.queryForObject(
                "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE id= :id",
                params,
                BeanPropertyRowMapper.newInstance(Owner.class)
        );
    } catch (EmptyResultDataAccessException ex) {
        throw new ObjectRetrievalFailureException(Owner.class, id);
    }
    loadPetsAndVisits(owner);
    return owner;
}
 

public void loadPetsAndVisits(final Owner owner) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("id", owner.getId().intValue());
    final List<JdbcPet> pets = this.namedParameterJdbcTemplate.query(
            "SELECT id, name, birth_date, type_id, owner_id FROM pets WHERE owner_id=:id",
            params,
            new JdbcPetRowMapper()
    );
    for (JdbcPet pet : pets) {
        owner.addPet(pet);
        pet.setType(EntityUtils.getById(getPetTypes(), PetType.class, pet.getTypeId()));
        List<Visit> visits = this.visitRepository.findByPetId(pet.getId());
        for (Visit visit : visits) {
            pet.addVisit(visit);
        }
    }
}
 

@RequestMapping(value = "/owners", method = RequestMethod.GET)
public String processFindForm(Owner owner, BindingResult result, Map<String, Object> model) {

    // allow parameterless GET request for /owners to return all records
    if (owner.getLastName() == null) {
        owner.setLastName(""); // empty string signifies broadest possible search
    }

    // find owners by last name
    Collection<Owner> results = this.clinicService.findOwnerByLastName(owner.getLastName());
    if (results.isEmpty()) {
        // no owners found
        result.rejectValue("lastName", "notFound", "not found");
        return "owners/findOwners";
    } else if (results.size() == 1) {
        // 1 owner found
        owner = results.iterator().next();
        return "redirect:/owners/" + owner.getId();
    } else {
        // multiple owners found
        model.put("selections", results);
        return "owners/ownersList";
    }
}
 

public void loadPetsAndVisits(final Owner owner) {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("id", owner.getId().intValue());
    final List<JdbcPet> pets = this.namedParameterJdbcTemplate.query(
            "SELECT id, name, birth_date, type_id, owner_id FROM pets WHERE owner_id=:id",
            params,
            new JdbcPetRowMapper()
    );
    for (JdbcPet pet : pets) {
        owner.addPet(pet);
        pet.setType(EntityUtils.getById(getPetTypes(), PetType.class, pet.getTypeId()));
        List<Visit> visits = this.visitRepository.findByPetId(pet.getId());
        for (Visit visit : visits) {
            pet.addVisit(visit);
        }
    }
}
 

@Test
@Transactional
public void shouldInsertPetIntoDatabaseAndGenerateId() {
    Owner owner6 = this.clinicService.findOwnerById(6);
    int found = owner6.getPets().size();
    
    Pet pet = new Pet();
    pet.setName("bowser");
    Collection<PetType> types = this.clinicService.findPetTypes();
    pet.setType(EntityUtils.getById(types, PetType.class, 2));
    pet.setBirthDate(new DateTime());
    owner6.addPet(pet);
    assertThat(owner6.getPets().size()).isEqualTo(found + 1);
    
    this.clinicService.savePet(pet);
    this.clinicService.saveOwner(owner6);
    
    owner6 = this.clinicService.findOwnerById(6);
    assertThat(owner6.getPets().size()).isEqualTo(found + 1);
    // checks that id has been generated
    assertThat(pet.getId()).isNotNull();
}
 

@RequestMapping(value = "/owners", method = RequestMethod.GET)
public String processFindForm(Owner owner, BindingResult result, Map<String, Object> model) {

    // allow parameterless GET request for /owners to return all records
    if (owner.getLastName() == null) {
        owner.setLastName(""); // empty string signifies broadest possible search
    }

    // find owners by last name
    Collection<Owner> results = this.clinicService.findOwnerByLastName(owner.getLastName());
    if (results.isEmpty()) {
        // no owners found
        result.rejectValue("lastName", "notFound", "not found");
        return "owners/findOwners";
    }
    else if (results.size() == 1) {
	// 1 owner found
	owner = results.iterator().next();
	return "redirect:/owners/" + owner.getId();
    }
    else {
        // multiple owners found
        model.put("selections", results);
        return "owners/ownersList";
    }
}
 

/**
 * 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.
 */
@Override
public Owner findById(int id) throws DataAccessException {
    Owner owner;
    try {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", id);
        owner = this.namedParameterJdbcTemplate.queryForObject(
                "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE id= :id",
                params,
                BeanPropertyRowMapper.newInstance(Owner.class)
        );
    } catch (EmptyResultDataAccessException ex) {
        throw new ObjectRetrievalFailureException(Owner.class, id);
    }
    loadPetsAndVisits(owner);
    return owner;
}
 

@RequestMapping(value = "/owners/new", method = RequestMethod.POST)
public String processCreationForm(@Valid Owner owner, BindingResult result, SessionStatus status) {
    if (result.hasErrors()) {
        return "owners/createOrUpdateOwnerForm";
    } else {
        this.clinicService.saveOwner(owner);
        status.setComplete();
        return "redirect:/owners/" + owner.getId();
    }
}
 

@RequestMapping(value = "/pets/new", method = RequestMethod.GET)
public String initCreationForm(Owner owner, ModelMap model) {
    Pet pet = new Pet();
    owner.addPet(pet);
    model.put("pet", pet);
    return "pets/createOrUpdatePetForm";
}
 

@Override
public Owner findById(int id) {
    // using 'join fetch' because a single query should load both owners and pets
    // using 'left join fetch' because it might happen that an owner does not have pets yet
    Query query = this.em.createQuery("SELECT owner FROM Owner owner left join fetch owner.pets WHERE owner.id =:id");
    query.setParameter("id", id);
    return (Owner) query.getSingleResult();
}
 

@RequestMapping(value = "/owners/{ownerId}/edit", method = RequestMethod.POST)
public String processUpdateOwnerForm(@Valid Owner owner, BindingResult result, @PathVariable("ownerId") int ownerId) {
    if (result.hasErrors()) {
        return "owners/createOrUpdateOwnerForm";
    } else {
        owner.setId(ownerId);
        this.clinicService.saveOwner(owner);
        return "redirect:/owners/{ownerId}";
    }
}
 

@Override
public Owner findById(int id) {
    // using 'join fetch' because a single query should load both owners and pets
    // using 'left join fetch' because it might happen that an owner does not have pets yet
    Query query = this.em.createQuery("SELECT owner FROM Owner owner left join fetch owner.pets WHERE owner.id =:id");
    query.setParameter("id", id);
    return (Owner) query.getSingleResult();
}
 

/**
 * 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.
 */
@Override
public Collection<Owner> findByLastName(String lastName) throws DataAccessException {
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("lastName", lastName + "%");
    List<Owner> owners = this.namedParameterJdbcTemplate.query(
            "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE last_name like :lastName",
            params,
            BeanPropertyRowMapper.newInstance(Owner.class)
    );
    loadOwnersPetsAndVisits(owners);
    return owners;
}
 

/**
 * 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.
 */
@Override
public Collection<Owner> findByLastName(String lastName) throws DataAccessException {
    Map<String, Object> params = new HashMap<>();
    params.put("lastName", lastName + "%");
    List<Owner> owners = this.namedParameterJdbcTemplate.query(
        "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE last_name like :lastName",
        params,
        BeanPropertyRowMapper.newInstance(Owner.class)
    );
    loadOwnersPetsAndVisits(owners);
    return owners;
}
 

@Override
public void save(Owner owner) throws DataAccessException {
    BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(owner);
    if (owner.isNew()) {
        Number newKey = this.insertOwner.executeAndReturnKey(parameterSource);
        owner.setId(newKey.intValue());
    } else {
        this.namedParameterJdbcTemplate.update(
                "UPDATE owners SET first_name=:firstName, last_name=:lastName, address=:address, " +
                        "city=:city, telephone=:telephone WHERE id=:id",
                parameterSource);
    }
}
 

@Override
public void save(Owner owner) throws DataAccessException {
    BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(owner);
    if (owner.isNew()) {
        Number newKey = this.insertOwner.executeAndReturnKey(parameterSource);
        owner.setId(newKey.intValue());
    } else {
        this.namedParameterJdbcTemplate.update(
            "UPDATE owners SET first_name=:firstName, last_name=:lastName, address=:address, " +
                "city=:city, telephone=:telephone WHERE id=:id",
            parameterSource);
    }
}
 

@RequestMapping(value = "/owners/new", method = RequestMethod.POST)
public String processCreationForm(@Valid Owner owner, BindingResult result, SessionStatus status) {
    if (result.hasErrors()) {
        return "owners/createOrUpdateOwnerForm";
    } else {
        this.clinicService.saveOwner(owner);
        status.setComplete();
        return "redirect:/owners/" + owner.getId();
    }
}
 

@RequestMapping(value = "/owners/{ownerId}/edit", method = RequestMethod.PUT)
public String processUpdateOwnerForm(@Valid Owner owner, BindingResult result, SessionStatus status) {
    if (result.hasErrors()) {
        return "owners/createOrUpdateOwnerForm";
    } else {
        this.clinicService.saveOwner(owner);
        status.setComplete();
        return "redirect:/owners/{ownerId}";
    }
}
 

@Test
public void shouldFindOwnersByLastName() {
    Collection<Owner> owners = this.clinicService.findOwnerByLastName("Davis");
    assertThat(owners.size()).isEqualTo(2);

    owners = this.clinicService.findOwnerByLastName("Daviss");
    assertThat(owners.isEmpty());
}
 

@Test
public void shouldFindOwnersByLastName() {
    Collection<Owner> owners = this.clinicService.findOwnerByLastName("Davis");
    assertThat(owners.size()).isEqualTo(2);

    owners = this.clinicService.findOwnerByLastName("Daviss");
    assertThat(owners.isEmpty());
}
 

@Override
public void save(Owner owner) throws DataAccessException {
    BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(owner);
    if (owner.isNew()) {
        Number newKey = this.insertOwner.executeAndReturnKey(parameterSource);
        owner.setId(newKey.intValue());
    } else {
        this.namedParameterJdbcTemplate.update(
                "UPDATE owners SET first_name=:firstName, last_name=:lastName, address=:address, " +
                        "city=:city, telephone=:telephone WHERE id=:id",
                parameterSource);
    }
}
 

@Test
@Transactional
public void shouldUpdateOwner() {
    Owner owner = this.clinicService.findOwnerById(1);
    String oldLastName = owner.getLastName();
    String newLastName = oldLastName + "X";

    owner.setLastName(newLastName);
    this.clinicService.saveOwner(owner);

    // retrieving new name from database
    owner = this.clinicService.findOwnerById(1);
    assertThat(owner.getLastName()).isEqualTo(newLastName);
}
 

@RequestMapping(value = "/pets/new", method = RequestMethod.POST)
public String processCreationForm(Owner owner, @Valid Pet pet, BindingResult result, ModelMap model) {
    if (StringUtils.hasLength(pet.getName()) && pet.isNew() && owner.getPet(pet.getName(), true) != null){
        result.rejectValue("name", "duplicate", "already exists");
    }
    if (result.hasErrors()) {
        model.put("pet", pet);
        return VIEWS_PETS_CREATE_OR_UPDATE_FORM;
    } else {
        owner.addPet(pet);
        this.clinicService.savePet(pet);
        return "redirect:/owners/{ownerId}";
    }
}
 

@RequestMapping(value = "/owners/new", method = RequestMethod.POST)
public String processCreationForm(@Valid Owner owner, BindingResult result) {
    if (result.hasErrors()) {
        return VIEWS_OWNER_CREATE_OR_UPDATE_FORM;
    } else {
        this.clinicService.saveOwner(owner);
        return "redirect:/owners/" + owner.getId();
    }
}
 

@Test
@Transactional
public void shouldUpdateOwner()  {
    Owner owner = this.clinicService.findOwnerById(1);
    String oldLastName = owner.getLastName();
    String newLastName = oldLastName + "X";
    
    owner.setLastName(newLastName);
    this.clinicService.saveOwner(owner);

    // retrieving new name from database
    owner = this.clinicService.findOwnerById(1);
    assertThat(owner.getLastName()).isEqualTo(newLastName);
}
 

@RequestMapping(value = "/owners/{ownerId}/edit", method = RequestMethod.POST)
public String processUpdateOwnerForm(@Valid Owner owner, BindingResult result, @PathVariable("ownerId") int ownerId) {
    if (result.hasErrors()) {
        return VIEWS_OWNER_CREATE_OR_UPDATE_FORM;
    } else {
        owner.setId(ownerId);
        this.clinicService.saveOwner(owner);
        return "redirect:/owners/{ownerId}";
    }
}
 

/**
 * 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.
 */
@Override
public Collection<Owner> findByLastName(String lastName) throws DataAccessException {
    Map<String, Object> params = new HashMap<>();
    params.put("lastName", lastName + "%");
    List<Owner> owners = this.namedParameterJdbcTemplate.query(
        "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE last_name like :lastName",
        params,
        BeanPropertyRowMapper.newInstance(Owner.class)
    );
    loadOwnersPetsAndVisits(owners);
    return owners;
}
 

public void loadPetsAndVisits(final Owner owner) {
    Map<String, Object> params = new HashMap<>();
    params.put("id", owner.getId());
    final List<JdbcPet> pets = this.namedParameterJdbcTemplate.query(
        "SELECT pets.id, name, birth_date, type_id, owner_id, visits.id as visit_id, visit_date, description, pet_id FROM pets LEFT OUTER JOIN visits ON  pets.id = pet_id WHERE owner_id=:id",
        params,
        new JdbcPetVisitExtractor()
    );
    Collection<PetType> petTypes = getPetTypes();
    for (JdbcPet pet : pets) {
        pet.setType(EntityUtils.getById(petTypes, PetType.class, pet.getTypeId()));
        owner.addPet(pet);
    }
}
 

@Override
public Pet findById(int id) throws DataAccessException {
    Integer ownerId;
    try {
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        ownerId = this.namedParameterJdbcTemplate.queryForObject("SELECT owner_id FROM pets WHERE id=:id", params, Integer.class);
    } catch (EmptyResultDataAccessException ex) {
        throw new ObjectRetrievalFailureException(Pet.class, id);
    }
    Owner owner = this.ownerRepository.findById(ownerId);
    return EntityUtils.getById(owner.getPets(), Pet.class, id);
}