public class FavoriteDaoImpl implements FavoriteDao {
JdbcTemplate jdbcTemplate = new JdbcTemplate(C3P0Util.getDataSource());
@Override
public int queryRouteByUidCount(int uid) {
String sql = "select count(*) from tab_route inner join tab_favorite on tab_route.rid = tab_favorite.rid where uid = ?";
return jdbcTemplate.queryForObject(sql, Integer.class, uid);
}
@Override
public List<Route> queryRouteByUid(int uid, int pageOffset, int pageSize) {
String sql = "select tab_route.* from tab_route inner join tab_favorite on tab_route.rid = tab_favorite.rid where uid = ? order by count desc limit ?,?";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Route.class), uid, pageOffset, pageSize);
}
@Override
public List<Route> queryRankPage(String word, String priceBegin, String priceEnd, int pageOffset, int pageSize) {
StringBuilder sql = new StringBuilder();
sql.append("select * from tab_route where rflag = 1");
List<Object> parameters = new ArrayList<>();
if (word != null && !word.equals("")){
sql.append(" and rname like ?");
parameters.add("%"+word+"%");
}
if (priceBegin != null && !priceBegin.equals("")){
sql.append(" and price >= ?");
parameters.add(priceBegin);
}
if (priceEnd != null && !priceEnd.equals("")){
sql.append(" and price <= ?");
parameters.add(priceEnd);
}
sql.append(" order by count desc limit ?,?");
parameters.add(pageOffset);
parameters.add(pageSize);
return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Route.class), parameters.toArray());
// String sql = “select * from tab_route order by count desc limit ?,?”;
// return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Route.class), pageOffset, pageSize);
}
@Override
public int queryRecordCount(String word, String priceBegin, String priceEnd) {
StringBuilder sql = new StringBuilder();
sql.append("select count(*) from tab_route where rflag = 1");
List<Object> parameters = new ArrayList<>();
if (word != null && !word.equals("")){
sql.append(" and rname like ?");
parameters.add("%"+word+"%");
}
if (priceBegin != null && !priceBegin.equals("")){
sql.append(" and price >= ?");
parameters.add(priceBegin);
}
if (priceEnd != null && !priceEnd.equals("")){
sql.append(" and price <= ?");
parameters.add(priceEnd);
}
return jdbcTemplate.queryForObject(sql.toString(), Integer.class, parameters.toArray());
}
}