旅游网FavoriteDaoImpl

最新推荐文章于 2022-10-06 01:00:00 发布

原创最新推荐文章于 2022-10-06 01:00:00 发布 · 249 阅读

0 ·

CC 4.0 BY-SA版权

java 同时被 3 个专栏收录

12 篇文章

订阅专栏

旅游网

10 篇文章

订阅专栏

Dao

5 篇文章

订阅专栏

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

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

}

}