org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper Java Examples

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

/**
 * 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);
			}
		}
	}
}
 

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

public static <T> DBTable<T> bulidDbTableFromSQL(String sql, Class<T> clazz, JdbcTemplate jdbcTemplate) throws InstantiationException, IllegalAccessException, Exception {
	DBTable<T> dbTable = new DBTable<T>();
	dbTable.setTableName(PublicUtil.getTableName(sql));
	dbTable.setClass1(clazz);
	List<T> dataList = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(clazz));
	dbTable.setTableData(dataList);
	return dbTable;
}
 

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

protected ParameterizedBeanPropertyRowMapper resultBeanMapper(Class clazz) {
	return ParameterizedBeanPropertyRowMapper.newInstance(clazz);
}
 

/**
 * Returning ResultSet/REF Cursor from a SimpleJdbcCall
 * 
 * DELIMITER //
 * CREATE PROCEDURE read_all_customers()
 * BEGIN
 * SELECT a.CUST_ID, a.NAME, a.AGE FROM customer a;
 * END//
 * 
 * 使用simpleJdbcCall调用read_all_customers存储过程返回一个ResultSet Cursor
 * 
 */
@SuppressWarnings("unchecked")
public List<Customer> getResultSetCursorUseSimpleJdbcCall(){
	Map<String, Object> map = simpleJdbcCall.withProcedureName("read_all_customers").
			returningResultSet("customers", ParameterizedBeanPropertyRowMapper.
					newInstance(Customer.class)).
			execute(new HashMap<String, Object>(0));
	return (List<Customer>) map.get("customers");
}