@Override
public Users getUsersById(Long id) throws Exception {
String sql = "select * from users where id=?";
Users user =
this.queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(Users.class), id);
return user;
}
public List<PetType> findPetTypes() throws DataAccessException {
Map<String, Object> params = new HashMap<String, Object>();
return this.namedParameterJdbcTemplate.query(
"SELECT id, name FROM types ORDER BY name",
params,
ParameterizedBeanPropertyRowMapper.newInstance(PetType.class));
}
public List<NoticeBean> getPreLine(String route_id) {
String sql =
"select t.route_id,t.longitude,t.latitude from CLW_TQC_EMP_PRELINE_T t where t.route_id = ?";
return jdbcTemplate.query(
sql,
new Object[] {route_id},
ParameterizedBeanPropertyRowMapper.newInstance(NoticeBean.class));
}
@Override
public Users getUsersByLoginName(String loginName) throws Exception {
String sql = "select * from users where loginName = ?";
Users users =
this.queryForObject(
sql, ParameterizedBeanPropertyRowMapper.newInstance(Users.class), loginName);
return users;
}
/**
* 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 OrderStatus getById(int id) {
String sql = "SELECT * from order_status where id = ? ";
List<OrderStatus> retlist =
jdbcTemplate.query(
sql,
new Object[] {id},
ParameterizedBeanPropertyRowMapper.newInstance(OrderStatus.class));
if (retlist.size() > 0) return retlist.get(0);
return null;
}
public List<Menu> findByIds(Long[] menuIds) {
String sql = "select * from " + MenuSqlMapper.TABLE_NAME + " where id in (0";
for (Long mid : menuIds) {
sql += "," + mid.toString();
}
sql += ") and status = 0 order by LENGTH(code)";
if (LOG.isDebugEnabled()) {
LOG.debug("findByIds sql : {}", sql);
}
return jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Menu.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.
*/
@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,
ParameterizedBeanPropertyRowMapper.newInstance(Owner.class));
loadOwnersPetsAndVisits(owners);
return owners;
}
public AppealInfo getFindById(String id) {
String sql = getFindByIdSql();
List<AppealInfo> list =
getSimpleJdbcTemplate()
.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(getEntityClass()), id);
if (list.size() > 0) {
return list.get(0);
}
return null;
}
@Override
public <T> List<T> findByProperties(String[] args, Class<T> clazz, Object... objs) {
String sql = "select * from goj_" + clazz.getSimpleName().toLowerCase();
if (args != null) {
for (int i = 0; i < args.length; i++) {
if (i == 0) sql = sql + " where " + args[i] + "=?";
else sql = sql + " and " + args[i] + "=?";
}
}
List<T> list =
this.simple.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(clazz), objs);
return list;
}
/**
* 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 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;
}
/*
* @see com.beike.dao.background.guest.GuestDao#queryBrandById(java.lang.String)
*/
public Guest queryBrandById(String guestId) throws Exception {
StringBuilder sql = new StringBuilder();
sql.append(
"SELECT guest_id,guest_cn_name,guest_type,guest_country_id,guest_province_id,guest_city_id,guest_address,");
sql.append(
"guest_account,guest_account_bank,guest_contract_no,guest_email,guest_status,brand_id,guest_city_area_id FROM beiker_guest_info ");
sql.append("WHERE guest_id = ? ");
Guest guest =
this.getSimpleJdbcTemplate()
.queryForObject(
sql.toString(),
ParameterizedBeanPropertyRowMapper.newInstance(Guest.class),
Integer.parseInt(guestId));
return guest;
}
/**
* 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,
ParameterizedBeanPropertyRowMapper.newInstance(Owner.class));
} catch (EmptyResultDataAccessException ex) {
throw new ObjectRetrievalFailureException(Owner.class, id);
}
loadPetsAndVisits(owner);
return owner;
}
@Override
public List<FilmShow> queryFilmShowPlainByCinema(Long cinemaId, Long filmId) {
// 只查询最近三天的放映计划
Date now = new Date();
Calendar threeDaysAfter = Calendar.getInstance();
threeDaysAfter.set(Calendar.DATE, threeDaysAfter.get(Calendar.DATE) + 3);
String sql =
"select * from beiker_film_show where show_time >= :showTimeStart and is_available = 1 and status = 1 and cinema_id = :cinemaId and film_id = :filmId";
MapSqlParameterSource parameterSource = new MapSqlParameterSource();
parameterSource.addValue("showTimeStart", now);
// parameterSource.addValue("showTimeEnd", threeDaysAfter);
parameterSource.addValue("cinemaId", cinemaId);
parameterSource.addValue("filmId", filmId);
List<FilmShow> result =
getSimpleJdbcTemplate()
.query(
sql,
ParameterizedBeanPropertyRowMapper.newInstance(FilmShow.class),
parameterSource);
return result;
}
public List findAll() {
String sql = getSelectPrefix();
return getSimpleJdbcTemplate()
.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(getEntityClass()));
}
public List<Menu> findByUserId(Long userId, int type) {
return jdbcTemplate.query(
MenuSqlMapper.FIND_BY_USER_AND_TYPE,
new Object[] {userId, type},
ParameterizedBeanPropertyRowMapper.newInstance(Menu.class));
}
public List<Menu> findByRoleId(Long roleId) {
return jdbcTemplate.query(
MenuSqlMapper.FIND_BY_ROLE,
new Object[] {roleId},
ParameterizedBeanPropertyRowMapper.newInstance(Menu.class));
}
public List<Menu> findAll() {
return jdbcTemplate.query(
MenuSqlMapper.FIND_ALL, ParameterizedBeanPropertyRowMapper.newInstance(Menu.class));
}
@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));
}
public List<Menu> findByParam(int status) {
return jdbcTemplate.query(
MenuSqlMapper.FIND_LIST,
new Object[] {status},
ParameterizedBeanPropertyRowMapper.newInstance(Menu.class));
}
public Collection<PetType> getPetTypes() throws DataAccessException {
return this.namedParameterJdbcTemplate.query(
"SELECT id, name FROM types ORDER BY name",
new HashMap<String, Object>(),
ParameterizedBeanPropertyRowMapper.newInstance(PetType.class));
}