环境：springboot2.3.9.RELEASE + MyBatis + MySQL
环境配置
- 依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
- 应用配置
spring:
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/testjpa?serverTimezone=GMT%2B8
username: root
password: 123123
type: com.zaxxer.hikari.HikariDataSource
hikari:
minimumIdle: 10
maximumPoolSize: 200
autoCommit: true
idleTimeout: 30000
poolName: MasterDatabookHikariCP
maxLifetime: 1800000
connectionTimeout: 30000
connectionTestQuery: SELECT 1
---
spring:
jpa:
generateDdl: false
hibernate:
ddlAuto: update
openInView: true
show-sql: true
---
pagehelper:
helperDialect: mysql
reasonable: true
pageSizeZero: true
offsetAsPageNum: true
rowBoundsWithCount: true
---
mybatis:
type-aliases-package: com.pack.domain
mapper-locations:
- classpath:/mappers/*.xml
主要是数据源 + MyBatis starter + pagehelper相关配置。
Mapper接口参数传值方式
方式1：
List<Users> queryUsers1(String idNo, String username) ;
xml
<select id="queryUsers1" resultMap="usersMapper">
SELECT * FROM bc_users T where T.ID_NO = #{param1} AND T.USERNAME LIKE CONCAT(CONCAT('%',#{param2}), '%')
</select>
根据方法的参数顺序param*。
或者：
<select id="queryUsers1" resultMap="usersMapper">
SELECT * FROM bc_users T where T.ID_NO = #{idNo} AND T.USERNAME LIKE CONCAT(CONCAT('%',#{username}), '%')
</select>
直接写参数名称。
方式2：
通过@Param注解指明参数的名称
List<Users> queryUsers2(@Param("no")String idNo, @Param("un")String username) ;
xml
<select id="queryUsers2" resultMap="usersMapper">
SELECT * FROM bc_users T where T.ID_NO = #{no} AND T.USERNAME LIKE CONCAT(CONCAT('%',#{un}), '%')
</select>
方式3：
通过Map传参
List<Users> queryUsers3(Map<String, Object> params) ;
xml
<select id="queryUsers3" resultMap="usersMapper" parameterType="hashmap">
SELECT * FROM bc_users T where T.ID_NO = #{id_no} AND T.USERNAME LIKE CONCAT(CONCAT('%',#{user_name}), '%')
</select>
这里的#{xxx} 就是存入Map中的Key。
方式4：
通过对象传参
List<Users> queryUsers4(UsersDTO params) ;
UsersDTO.java
public class UsersDTO extends ParamsDTO {
private String idNo ;
private String username ;
public String getIdNo() {
return idNo;
}
public void setIdNo(String idNo) {
this.idNo = idNo;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
}
xml
<select id="queryUsers4" resultMap="usersMapper" parameterType="com.pack.utils.UsersDTO">
SELECT * FROM bc_users T where T.ID_NO = #{idNo} AND T.USERNAME LIKE CONCAT(CONCAT('%',#{username}), '%')
</select>
这里的#{xxx} 对象中必须有该属性对应的getter方法。
方式5：
List集合参数
List<Users> queryUsers5(Collection<String> params) ;
xml
<select id="queryUsers5" resultMap="usersMapper" parameterType="com.pack.utils.UsersDTO">
SELECT * FROM bc_users T where T.id in
<foreach collection="params" open="(" separator="," close=")" item="id">
#{id}
</foreach>
</select>
Mapper
@Mapper
public interface UsersMapper {
List<Users> queryUsers1(String idNo, String username) ;
List<Users> queryUsers2(@Param("no")String idNo, @Param("un")String username) ;
List<Users> queryUsers3(Map<String, Object> params) ;
List<Users> queryUsers4(UsersDTO params) ;
List<Users> queryUsers5(Collection<String> params) ;
}
XML
<mapper namespace="com.pack.mapper.UsersMapper">
<resultMap type="com.pack.domain.Users" id="usersMapper">
<id column="id" property="id"/>
<id column="username" property="username"/>
<id column="real_name" property="realName"/>
<id column="create_time" property="createTime"/>
<id column="status" property="status"/>
<id column="authority" property="authority"/>
<id column="id_no" property="idNo"/>
</resultMap>
<select id="queryUsers1" resultMap="usersMapper">
SELECT * FROM bc_users T where T.ID_NO = #{idNo} AND T.USERNAME LIKE CONCAT(CONCAT('%',#{username}), '%')
</select>
<select id="queryUsers2" resultMap="usersMapper">
SELECT * FROM bc_users T where T.ID_NO = #{no} AND T.USERNAME LIKE CONCAT(CONCAT('%',#{un}), '%')
</select>
<select id="queryUsers3" resultMap="usersMapper" parameterType="hashmap">
SELECT * FROM bc_users T where T.ID_NO = #{id_no} AND T.USERNAME LIKE CONCAT(CONCAT('%',#{user_name}), '%')
</select>
<select id="queryUsers4" resultMap="usersMapper" parameterType="com.pack.utils.UsersDTO">
SELECT * FROM bc_users T where T.ID_NO = #{idNo} AND T.USERNAME LIKE CONCAT(CONCAT('%',#{username}), '%')
</select>
<select id="queryUsers5" resultMap="usersMapper" parameterType="com.pack.utils.UsersDTO">
SELECT * FROM bc_users T where T.id in
<foreach collection="params" open="(" separator="," close=")" item="id">
#{id}
</foreach>
</select>
</mapper>
Controller
@Resource
private UsersMapper usersMapper ;
@GetMapping("/q1")
public Object q1(UsersDTO params) {
return R.success(Pager.query(params, () -> {
return usersMapper.queryUsers1(params.getIdNo(), params.getUsername()) ;
})) ;
}
@GetMapping("/q2")
public Object q2(UsersDTO params) {
return R.success(Pager.query(params, () -> {
return usersMapper.queryUsers2(params.getIdNo(), params.getUsername()) ;
})) ;
}
@GetMapping("/q3")
public Object q3(UsersDTO params) {
return R.success(Pager.query(params, () -> {
Map<String, Object> ps = new HashMap<>() ;
ps.put("id_no", params.getIdNo()) ;
ps.put("user_name", params.getUsername()) ;
return usersMapper.queryUsers3(ps) ;
})) ;
}
@GetMapping("/q4")
public Object q4(UsersDTO params) {
return R.success(Pager.query(params, () -> {
return usersMapper.queryUsers4(params) ;
})) ;
}
@GetMapping("/q5")
public Object q5(@RequestBody List<String> ids) {
return R.success(Pager.query(new UsersDTO(), () -> {
return usersMapper.queryUsers5(ids) ;
})) ;
}
完毕！！！
给个 关注+转发 谢谢
