基于java的收藏排行功能之五：Dao层对数据库的操作&数据表设计

接上一篇

11：dao接口

package cn.itcast.travel.dao;

import cn.itcast.travel.domain.Route;

import java.util.List;

public interface RouteDao {
    public List<Route> findRouteByPage(int cid, int startPage, int pageSize, String sname, int ihighPrice, int ilowPrice, int order);
    public int findTotalCount(int cid, String sname, int ihighPrice, int ilowPrice, int order);

}

12dao实现类：

package cn.itcast.travel.dao.impl;

import cn.itcast.travel.dao.RouteDao;
import cn.itcast.travel.domain.Route;
import cn.itcast.travel.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class RouteDaoImp implements RouteDao{
   private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());
  
    @Override
    public List<Route> findRouteByPage(int cid, int startPage, int pageSize, String cname, int highPrice, int lowPrice, int order) {
        String sql="select * from tab_route  WHERE 1=1 ";
        StringBuilder sb=new StringBuilder(sql);
        List params=new ArrayList();
        if(cid!=0){
            sb.append(" and cid = ? ");
            params.add(cid);
        }
        if(cname!=null&&cname.length()>0&&!"null".equals(cname)){
            sb.append(" and rname like ? ");
            params.add("%"+cname+"%");
        }
        if(highPrice!=-1){
            sb.append(" and price < ?");
            params.add(highPrice);
        }
        if(lowPrice!=-1){
                sb.append(" and price > ?");
                params.add(lowPrice);

        }
        if(order==1){
            sb.append(" order by count desc ");
        }else if(order==2) {
            sb.append(" order by rdate desc ");
        }else if(order==3){
            sb.append(" order by isThemeTour  desc ");
        }
        sb.append(" limit ? , ? ");
        params.add(startPage);
        params.add(pageSize);
        sql=sb.toString();


        System.out.println(sql);
        System.out.println(params);

        List<Route> list=template.query(sql,new BeanPropertyRowMapper<Route>(Route.class),params.toArray());
        System.out.println("RouteDaoImp的："+list);
        return list ;
    }
 
    @Override
    public int findTotalCount(int cid, String cname, int highPrice, int lowPrice, int order) {
        String sql="select count(*) from tab_route  WHERE 1=1 ";
        StringBuilder sb=new StringBuilder(sql);
        List params=new ArrayList();
        if(cid!=0){
            sb.append(" and cid = ? ");
            params.add(cid);
        }
        if(cname!=null&&cname.length()>0&&!"null".equals(cname)){
            sb.append(" and rname like ? ");
            params.add("%"+cname+"%");
        }

        if(highPrice!=-1){
            sb.append(" and price < ?");
            params.add(highPrice);
        }
        if(lowPrice!=-1){
            sb.append(" and price > ?");
            params.add(lowPrice);

        }
        if(order==1){
            sb.append(" order by count desc ");
        }
        sql=sb.toString();

        Integer count = template.queryForObject(sql, Integer.class, params.toArray());
        return count;
    }

}

13数据库表的设计