需求：查詢用戶名具體負(fù)責(zé)的項目的名稱
已有數(shù)據(jù)庫表如下所示，二者通過項目ID關(guān)聯(lián)
test數(shù)據(jù)庫的user表
test1數(shù)據(jù)庫的project表
需要實(shí)現(xiàn)的SQL查詢
select user.id,user.name,projectName from test.user left join test1.project on user.`projectId`=project.id;
查詢結(jié)果
實(shí)現(xiàn)：核心思想，手動新建Bean與Mapper。
1.自動生成代碼model與mapper，包括test.user test1.project
2.新建一個bean命名為UserPro，其屬性為上述所需的3個字段
public class UserPro {
private Integer id;
private String name;
private String projectname;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getProjectname() {
return projectname;
}
public void setProjectname(String projectname) {
this.projectname = projectname;
}
}
3.新建一個mapper命名為UserProMapper,返回新建的bean: UserPro
@Repository
public interface UserProMapper {
@Select("select user.id,user.name,projectName from test.user left join test1.project on user.`projectId`=project.id limit 1")
UserPro findProName();
}
4.基本已經(jīng)實(shí)現(xiàn)核心的內(nèi)容了，接下來就是用controller實(shí)現(xiàn)調(diào)用關(guān)系
@Controller
public class testController {
@Autowired
private StuClassMapper stuClassMapper;
@Autowired
private UserMapper userMapper;
@Autowired
private UserProMapper userProMapper;
@RequestMapping("/test")
@ResponseBody
public String getStuClass(){
User user=userMapper.findByUserId();
System.out.println(user.getName());
return user.getName();
}
@RequestMapping("/test2")
@ResponseBody
public String getStuClassByJoin(){
System.out.println(userMapper.findByUserIdJoin());
return "userMapper.findByUserIdJoin()";
}
@RequestMapping("/test3")
@ResponseBody
public UserPro getProName(){
UserPro userPro=userProMapper.findProName();
return userPro;
}
@RequestMapping("/test4")
@ResponseBody
public String findUserNameByUserIdJoin(){
return userMapper.findUserNameByUserIdJoin();
}
}
5.啟動項目，在瀏覽器輸入：http://localhost:8091/test3
執(zhí)行結(jié)果
大家也發(fā)現(xiàn)了，controller中有很多的路由匹配，這是模擬了其他若干場景。另外為了方便，Mapper返回的查詢結(jié)果條數(shù)限制為1：
# test
select * from test.user where id in(select userId from test1.stuClass) limit 1;
# test2
select count(*) from test.user left join test1.stuClass on user.id=stuClass.userId
# test3
select user.id,user.name,projectName from test.user left join test1.project on user.`projectId`=project.id;
# test4
select stuClass.userId from test.user left join test1.stuClass on user.id=stuClass.userId limit 1
# ？
select * from test.user left join test1.stuClass on user.id=stuClass.userId;
