j2ee-规范-jdbc-视图调用(oracle为例）

view

/* 创建帐单列表所用视图，注意是三表连接 */
CREATE OR REPLACE VIEW v_bill
AS
  SELECT b.*, g.g_name,p.p_name 
     FROM bill b INNER JOIN goods g ON b.g_id = g.g_id
        inner join provider p on b.p_id = p.p_id
     order by b.b_id;

java bean

/**
 * 针对视图v_bill的实体类
 * @author Jon
 *
 */
public class V_Bill extends Bill {
    private String g_name; //商品名称
    private String p_name; //供应商名称

    public String getG_name() {
        return g_name;
    }

    public void setG_name(String g_name) {
        this.g_name = g_name;
    }

    /**
     * @return the p_name
     */
    public String getP_name() {
        return p_name;
    }

    /**
     * @param p_name the p_name to set
     */
    public void setP_name(String p_name) {
        this.p_name = p_name;
    }
}

java dao

/* 新调整的帐单分页查询 */
    public List<V_Bill> select(int page, int rows, String gname, int paid)
            throws SQLException {
        List<V_Bill> lst = new ArrayList<V_Bill>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String paidStr = ""; //付款标记，默认“请选择”
        if(paid == 0 || paid==1){ //如果0未付款或1已付款，则添加条件
            paidStr = "and paid = "+paid;
        }
        String sql = "SELECT * from (select rownum rn, b.* from v_bill b where g_name like ? "+paidStr+") where rn BETWEEN ? AND ?";

        try{
            conn = super.getConn();
            ps = conn.prepareStatement(sql);
            if(gname == null) gname = "";
            ps.setString(1, "%"+gname+"%");
            ps.setInt(2, (page-1)*rows+1);
            ps.setInt(3, page*rows);
            rs = ps.executeQuery();
            while(rs.next()){
                lst.add(toV_Bill(rs));
            }
            return lst;
        }finally{
            super.closeAll(conn, ps, rs);
        }
    }

    private V_Bill toV_Bill(ResultSet rs) throws SQLException {
        V_Bill b = new V_Bill();
        b.setB_count(rs.getInt("b_count"));
        b.setB_date(rs.getTimestamp("b_date"));
        b.setB_description(rs.getString("b_description"));
        b.setB_id(rs.getInt("b_id"));
        b.setDeal(rs.getDouble("deal"));
        b.setG_id(rs.getInt("g_id"));
        b.setP_id(rs.getInt("p_id"));
        b.setPaid(rs.getBoolean("paid"));
        b.setU_id(rs.getInt("u_id"));
        b.setG_name(rs.getString("g_name"));  //商品名称
        b.setP_name(rs.getString("p_name"));  //供应商名称
        return b;
    }