mybatis的通用mapper,多用于单表查询,接口内部为我们提供了单表查询的基础查询语法,可以极大地帮助我们简化编程。
接下来让我们动手试一试:
我建的是springboot项目:
先导依赖:
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.0.4</version>
</dependency>
这里连接池我用的是阿里的druid,数据库用的mysql
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
我的数据库两张测试用表:
接下来搭建基本框架,先写实体类:
需要注意的是:使用通用Mapper需要给实体类加注解:
@Entity
@Table(name="t_users")
public class User implements Serializable{
private static final long serialVersionUID = 8941012353272388061L;
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
@Column
private String name;
@Column
private String password;
@Column(name="role_id")
private Long roleId;
}
别忘了实体类的getter/setter方法,然后dao层实现Mapper接口,注意UserMapper是接口不是类
public interface UserMapper extends Mapper<User> {
}
然后在主类上打开mapper扫描,
注意是tk.mybatis下的@MapperScan, 不要导成org.mybatis.spring.annotation.MapperScan
import tk.mybatis.spring.annotation.MapperScan;
@SpringBootApplication
@MapperScan("com.test.dao")
public class MapperTestApplication {
public static void main(String[] args) {
SpringApplication.run(MapperTestApplication.class, args);
}
}
准备就绪,接下来测试;
1)、(单条件查询)根据role_id查询所有人
业务类:
/*
* 根据role_id查询所有人
* select * from t_users where role_id = ?;
*/
@Override
public List<User> selectByUser1(User user) {
Example example = new Example(User.class,true,true);
Example.Criteria ec = example.createCriteria();
ec.andEqualTo("roleId", user.getRoleId());
return userMapper.selectByExample(example);
}
测试类:
@RunWith(SpringRunner.class)
@SpringBootTest
public class MapperTestApplicationTests {
@Autowired
private IUserServ userServ;
@Test
public void contextLoads() {
User user = new User();
user.setRoleId(1L);
List<User> ulist = userServ.selectByUser1(user);
ulist.forEach(System.err::println);
}
}
测试结果:通过:控制台打印
DEBUG 6268 --- [main] com.test.dao.UserMapper.selectByExample : ==> Preparing: SELECT id,name,password,role_id FROM t_users WHERE ( role_id = ? )
DEBUG 6268 --- [main] com.test.dao.UserMapper.selectByExample : ==> Parameters: 1(Long)
DEBUG 6268 --- [main] com.test.dao.UserMapper.selectByExample : <== Total: 4
User [id=2, name=员工1, password=111, roleId=1, role=Role [id=null, describe=null]]
User [id=3, name=员工2, password=222, roleId=1, role=Role [id=null, describe=null]]
User [id=4, name=员工3, password=333, roleId=1, role=Role [id=null, describe=null]]
User [id=5, name=员工4, password=444, roleId=1, role=Role [id=null, describe=null]]
2)测试二:(多条件查询)
业务类:
/*
(多条件查询)根据role_id查询id大于min小于max,或者name为?的人
* select * from t_users where role_id = ? and id between min and max and name = ?
*/
@Override
public List<User> selectByUser2(Long min,Long max,User user) {
Example example = new Example(User.class,true,true);
Example.Criteria ec = example.createCriteria();
ec.andEqualTo("roleId", user.getRoleId()).andBetween("id", min, max).orEqualTo("name", user.getName());
return userMapper.selectByExample(example);
}
测试类:
@Test
public void contextLoads() {
User user = new User();
user.setRoleId(1L);
user.setName("员工4");
List<User> ulist = userServ.selectByUser2(2L,3L,user);
ulist.forEach(System.err::println);
}
测试结果:通过。控制台打印:
DEBUG 14728 --- [main] com.test.dao.UserMapper.selectByExample : ==> Preparing: SELECT id,name,password,role_id FROM t_users WHERE ( role_id = ? and id between ? and ? or name = ? )
DEBUG 14728 --- [main] com.test.dao.UserMapper.selectByExample : ==> Parameters: 1(Long), 2(Long), 3(Long), 员工4(String)
DEBUG 14728 --- [main] com.test.dao.UserMapper.selectByExample : <== Total: 3
User [id=2, name=员工1, password=111, roleId=1, role=Role [id=null, describe=null]]
User [id=3, name=员工2, password=222, roleId=1, role=Role [id=null, describe=null]]
User [id=5, name=员工4, password=444, roleId=1, role=Role [id=null, describe=null]]
3)排序(我们继续用刚才的多条件查询,结果倒序)
select * from t_users where ( role_id = ? and id between ? and ? or name = ? ) order by id DESC
业务层:(加example.setOrderByClause("id DESC");)
@Override
public List<User> selectByUser2(Long min,Long max,User user) {
Example example = new Example(User.class,true,true);
Example.Criteria ec = example.createCriteria();
ec.andEqualTo("roleId", user.getRoleId()).andBetween("id", min, max).orEqualTo("name", user.getName());
example.setOrderByClause("id DESC");
return userMapper.selectByExample(example);
}
测试类不变:
测试结果:通过:控制台打印
DEBUG 10780 --- [main] com.test.dao.UserMapper.selectByExample : ==> Preparing: SELECT id,name,password,role_id FROM t_users WHERE ( role_id = ? and id between ? and ? or name = ? ) order by id DESC
DEBUG 10780 --- [main] com.test.dao.UserMapper.selectByExample : ==> Parameters: 1(Long), 2(Long), 3(Long), 员工4(String)
DEBUG 10780 --- [main] com.test.dao.UserMapper.selectByExample : <== Total: 3
User [id=5, name=员工4, password=444, roleId=1, role=Role [id=null, describe=null]]
User [id=3, name=员工2, password=222, roleId=1, role=Role [id=null, describe=null]]
User [id=2, name=员工1, password=111, roleId=1, role=Role [id=null, describe=null]]
此外:Example类还为我们封装了指定列查询,排除列查询等许多单表查询的方法,具体需求具体分析,我们可以去追 Example类的源码找寻自己需要的方法。
总结:通用Mapper适合于基于单表的复杂查询,涉及多张表的查询建议使用反向映射生成mapper.XML查询