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;
}