第三课(spring-boot+mybatis+jqgrid)

65次阅读

共计 2769 个字符，预计需要花费 7 分钟才能阅读完成。

课程目标
完成与 spring boot 与的 mybatis 的集成处理数据 curd
课程计划
使用 mybatis 完成博客后台管理员列表的 jqgird 搜索
课程分析
想要完成列表的搜索，就必须对 sql 按提交搜索条件进行逻辑判断组织 sql, 也就是动态 sql
步骤
1. 加入依赖
// build.gradle
dependencies {
compile(“org.springframework.boot:spring-boot-starter-web”)
compile(“org.springframework.boot:spring-boot-starter-thymeleaf”)
compile(“org.springframework.boot:spring-boot-devtools”)
// JPA Data (We are going to use Repositories, Entities, Hibernate, etc…)
compile ‘org.springframework.boot:spring-boot-starter-data-jpa’
// Use MySQL Connector-J
compile ‘mysql:mysql-connector-java’
// 使用 mybatis
compile(“org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.2”)
developmentOnly(“org.springframework.boot:spring-boot-devtools”)
testCompile(“junit:junit”)
}
2. 配置 mybatis
spring:
jpa:
hibernate:
ddl-auto: update
datasource:
url: jdbc:mysql://localhost:3306/db_sptest?useSSL=false
username: mysqluser
password: mysqlpwd
mvc:
static-path-pattern: /static/**
mybatis:
type-aliases-package: hello.model
3. 使用 mybatis mapper
// model/AdminMapper
@Mapper
public interface AdminMapper {
// 使用注解方式
@Select(“SELECT * FROM ADMIN WHERE name = #{name} LIMIT 1”)
Admin findByName(String name);

@Select(“SELECT * FROM ADMIN WHERE id = #{id}”)
Admin findById(Integer id);

// 动态 sql
@SelectProvider(type = AdminService.class,method = “selectAdminLike”)
List<Admin> findBySearch(Admin admin);

// 动态 sql 返回行数
@SelectProvider(type = AdminService.class,method = “countAdminSearch”)
@ResultType(Integer.class)
int countBySearch(Admin admin);

}
4. 编写动态 sql 语句
// service/AdminService
import org.apache.ibatis.jdbc.SQL;

public class AdminService {

// With conditionals (note the final parameters, required for the anonymous inner class to access them)
public String selectAdminLike(Admin admin) {
return new SQL() {{
SELECT(“A.name,A.email,A.id”);
FROM(“ADMIN A”);
if (admin.getName() != null) {
WHERE(“A.name = ‘” + admin.getName() + “‘”);
}
if (admin.getEmail() != null) {
WHERE(“A.email = ” + admin.getEmail());
}
}}.toString();
}

public String countAdminSearch(Admin admin){
return new SQL() {{
SELECT(“count(*)”);
FROM(“ADMIN A”);
if (admin.getName() != null) {
WHERE(“A.name = ‘” + admin.getName() + “‘”);
}
if (admin.getEmail() != null) {
WHERE(“A.email = ” + admin.getEmail());
}
}}.toString();
}
}
5. 使用 mybatis 查询方法
// AdminController
@GetMapping(path = “get_list”)
@ResponseBody
public DataResponse<Admin> getAdminList(
Admin adminReq,
@RequestParam(defaultValue = “1”, value = “page”) String page,
@RequestParam(defaultValue = “10”, value = “rows”) String rows) {
String total; // 页数
List<Admin> admin_list;
int records;
records = adminMapper.countBySearch(adminReq);
int pageSize = Integer.valueOf(rows);
total = Integer.toString((records + pageSize – 1) / pageSize);
DataResponse<Admin> response = new DataResponse<>();
admin_list = adminMapper.findBySearch(adminReq);
response.setTotal(total);
response.setRows(admin_list);
response.setPage(page);
response.setRecords(records);
return response;
}