需要关联四个表
select g.*,b.name brname,s.bid,s.sname,bi.name biname from T_GOODS g
left join T_brand b on b.id = g.brandid
inner join T_smallclass s on g.sid = s.sid
inner join T_bigclass bi on bi.id = s.bid"
T_GOODS 商品表格
商品实体类
public class Goods {
public int id;
public String goodsname;
public double weight;
public double price;
public String color;
public int brandid;
public Date shangtime;
public int stock;
public int sid;
public BigClass bigclass; //其实是大分类与小分类的实体类，写一个了，不允许这样，因为主键不清
public Brand brand;//其实是把品牌实体类写在商品实体类，后台需要
}
T_brand 品牌表格
品牌实体类
public class Brand {
private int id;
private String name;
}
T_smallclass 小分类表格
public class SmallClass {
private int id;
private String name;
public int bid;
}
T_bigclass 大分类表格
public class BigClass {
//大分类
private int id;
private String name;
}
//商品关联查询
public List<Goods> goodslist();
//商品关联查询
connection = DBUtil.getConnection();
String sql = "select g.*,b.name brname,s.bid,s.sname,bi.name biname "
+ " from T_GOODS g left join T_brand b on b.id = g.brandid"
+ " inner join T_smallclass s on g.sid = s.sid"
+ " inner join T_bigclass bi on bi.id = s.bid";
p = connection.prepareStatement(sql);
set = p.executeQuery();
List<Goods> list = new ArrayList<Goods>();
while(set.next()){
Goods goods = new Goods();
goods.setId(set.getInt("id"));
goods.setGoodsname(set.getString("goodsname"));
// goods.setBrandid(set.getInt("brandid"));
goods.setColor(set.getString("color"));
goods.setPrice(set.getDouble("price"));
goods.setShangtime(set.getDate("shangtime"));
// goods.setSid(set.getInt("sid"));
goods.setStock(set.getInt("stock"));
goods.setWeight(set.getDouble("weight"));
goods.setBrand(new Brand());
goods.getBrand().setId(set.getInt("brandid"));
goods.getBrand().setName(set.getString("brname"));
goods.setBigclass(new BigClass());
goods.getBigclass().setBid(set.getInt("bid"));
goods.getBigclass().setName(set.getString("biname"));
goods.getBigclass().setSid(set.getInt("sid"));
goods.getBigclass().setSname(set.getString("sname"));
//注意，这里的值不可以重复（写错了，把大分类和小分类写在一起了，应该分开）
list.add(goods);
}
return list;
其余都是一样
前台赋值问题
关于前台赋值，如果跨实体的话，先写进的实体，然后写远的，比如，goodslist.brand.name{brand是goods实体类里面的 Brand 品牌 . 品牌实体类的name值}（自己理解）