需求:查询用户名具体负责的项目的名称
已有数据库表如下所示,二者通过项目ID关联
test数据库的user表
test1数据库的project表
需要实现的SQL查询
select user.id,user.name,projectName from test.user left join test1.project on user.`projectId`=project.id;
查询结果
查询结果
实现:核心思想,手动新建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.基本已经实现核心的内容了,接下来就是用controller实现调用关系
@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
大家也发现了,controller中有很多的路由匹配,这是模拟了其他若干场景。另外为了方便,Mapper返回的查询结果条数限制为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;
网友评论