旅游网FavoriteDaoImpl_at cn.itcast.travel.dao.impl.favoritedaoimpl.<init-优快云博客

本文链接：https://blog.youkuaiyun.com/AKABUGmaker/article/details/102691796

本文展示了一个使用Java实现的DAO层，通过JdbcTemplate操作数据库，包括根据用户ID查询收藏的旅游路线数量、分页查询路线详情及按条件筛选排名前的旅游路线。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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());

}

}