`三表查询
Topic类
public class Topic {
private int tid;
private String title;
private String context;
private Date ptime;
private int uid;
private int bid;
private User user;
private Board board;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Board getBoard() {
return board;
}
public void setBoard(Board board) {
this.board = board;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContext() {
return context;
}
public void setContext(String context) {
this.context = context;
}
public Date getPtime() {
return ptime;
}
public void setPtime(Date ptime) {
this.ptime = ptime;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public int getBid() {
return bid;
}
public void setBid(int bid) {
this.bid = bid;
}
Board类
public class Board {
private int bid;
private String bname;
public int getBid() {
return bid;
}
public void setBid(int bid) {
this.bid = bid;
}
public String getBname() {
return bname;
}
public void setBname(String bname) {
this.bname = bname;
}
User类
public class User {
private int uid;
private String uname;
private String upass;
//用户状态,0表示正常,1表示禁用
private int state;
//用户标识,0表示管理员,1表示用户
private int flag;
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getUpass() {
return upass;
}
public void setUpass(String upass) {
this.upass = upass;
}
public int getState() {
return state;
}
public void setState(int state) {
this.state = state;
}
public int getFlag() {
return flag;
}
public void setFlag(int flag) {
this.flag = flag;
}
创建接口RowMapper
/**
* 结果集一行和对象的映射器
* @author Admin
*
* @param <E>
*/
public interface RowMapper<E> {
/**
* 结果集的一行映射为一个对象
* @param rs 已经打开的结果集
* @return 映射好的对象
*/
E oneRowMapper(ResultSet rs);
实现接口类
public class TopicRowMapper implements RowMapper<Topic>{
@Override
public Topic oneRowMapper(ResultSet rs) {
try {
Topic topic = new Topic();
topic.setTid(rs.getInt("tid"));
topic.setTitle(rs.getString("title"));
topic.setContext(rs.getString("context"));
topic.setPtime(rs.getDate("ptime"));
User user = new User();
user.setUname(rs.getString("uname"));
topic.setUser(user);
Board board = new Board();
board.setBname(rs.getString("bname"));
topic.setBoard(board);
return topic;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
测试类
public class TopicTest {
public static void main(String[] args) throws Exception {
TopicDao topicDao =new TopicDao();
//查询全部
List<Topic> list =topicDao.list("select * from topic t inner join user u on t.uid=u.uid "
+ "inner join board b on b.bid=t.bid", new TopicRowMapper());
for(Topic topic:list) {
System.out.println(topic.getTid()+"\t"+topic.getTitle()+"\t"+topic.getContext()+
"\t"+topic.getPtime()+"\t"+topic.getUser().getUname()+"\t"+topic.getBoard().getBname());
}
Topic topic = new Topic();
//查询一个
topic = topicDao.get("select * from topic t inner join user u on t.uid=u.uid
inner join board b on b.bid=t.bid where tid=?", new TopicRowMapper(),4);
System.out.println(topic.getTid()+"\t"+topic.getTitle()+"\t"+topic.getContext()+
"\t"+topic.getPtime()+"\t"+topic.getUser().getUname()+"\t"+topic.getBoard().getBname());
}