Spring Data JPA使用NamedParameterJdbcTemplate
對Oracle 11g使用native sql做動態條件及分頁查詢範例。
範例環境:
- Java 8
- Spring Boot 2.3.9.RELEASE
- Spring Data JPA
- Lombok
- Oracle 11g
現有資料表EMPLOYEE
及DEPARTMENT
資料如下;
EMPLOYEE
+----+---------------+--------+-----+
| ID | DEPARTMENT_ID | NAME | AGE |
+----+---------------+--------+-----+
| 1 | 1 | John | 33 |
| 2 | 1 | Mary | 28 |
| 3 | 2 | Andy | 26 |
| 4 | 2 | Zoey | 22 |
| 5 | 3 | Jason | 34 |
| 6 | 3 | Luke | 36 |
| 7 | 3 | Bob | 38 |
| 8 | 4 | Ken | 39 |
| 9 | 4 | Helen | 40 |
| 10 | 4 | Oliver | 43 |
+----+---------------+--------+-----+
DEPARTMENT
+----+------+
| ID | NAME |
+----+------+
| 1 | HR |
| 2 | MKTG |
| 3 | IT |
| 4 | FIN |
+----+------+
資料表對映的entity類Employee
及Department
Employee
package com.abc.demo.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class Employee implements Serializable {
private static final long serialVersionUID = 1L;
@Id
private long id;
private long departmentId;
private String name;
private Integer age;
}
Department
package com.abc.demo.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class Department implements Serializable {
private static final long serialVersionUID = 1L;
@Id
private long id;
private String name;
}
EmployeeDto
為查詢的資料結果,包含員工資訊和部門名稱如下。
EmployeeDto
package com.abc.demo.dto;
import lombok.Data;
@Data
public class EmployeeDto {
private long id;
private long departmentId;
private String name;
private Integer age;
private String departmentName;
}
EmployeeDao
使用NamedParameterJdbcTemplate
做分頁查詢,因為要包含員工及部門資料所以要join查詢。使用Oracle WITH
暫存主查詢SQL,然後用來進行分頁查詢及總筆數查詢。
EmployeeDao
package com.abc.demo.dao;
import com.abc.demo.dto.EmployeeDto;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;
@Slf4j
@Repository
public class EmployeeDao {
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public Page<EmployeeDto> findPagedEmployeeList(
String name, Integer age, int page, int size, int order, String col) {
MapSqlParameterSource params = new MapSqlParameterSource();
StringBuilder withClauseBuilder = new StringBuilder("with emp as (\n" +
" select\n" +
" e.*,\n" +
" d.name as department_name\n" +
" from employee e\n" +
" join department d on d.id = e.department_id\n" +
" where 1 = 1\n");
Optional.ofNullable(name)
.filter(StringUtils::isNotEmpty)
.ifPresent(e -> {
withClauseBuilder.append(" and e.name like ('%' || :name || '%') \n");
params.addValue("name", e);
});
Optional.ofNullable(age)
.ifPresent(e -> {
withClauseBuilder.append(" and e.age = :age\n");
params.addValue("age", e);
});
Optional.ofNullable(col)
.filter(StringUtils::isNotEmpty)
.ifPresent(e ->
withClauseBuilder.append(" order by ").append(col).append(order == 1 ? " ASC\n" : " DESC\n"));
String with = withClauseBuilder.append(")\n").toString();
int startRow = (page - 1) * size + 1;
int endRow = page * size;
params.addValue("startRow", startRow).addValue("endRow", endRow);
// 分頁sql
String pageSql = with +
"select t.* from (\n" +
" select \n" +
" rownum rn, \n" +
" emp.* \n" +
" from emp\n" +
") t\n" +
"where rn between :startRow and :endRow \n";
log.info("pageSql={}", pageSql);
List<EmployeeDto> employeeDtoList =
namedParameterJdbcTemplate.query(
pageSql,
params,
new BeanPropertyRowMapper<>(EmployeeDto.class));
// 總筆數sql
String countSql = with + "select count(*) as cnt from emp";
log.info("countSql={}", countSql);
Long total = namedParameterJdbcTemplate.queryForObject(countSql, params, Long.class);
Pageable pageable = PageRequest.of(page - 1, size);
return new PageImpl<>(employeeDtoList, pageable, Optional.ofNullable(total).orElse(0L));
}
}
測試
EmployeeDaoTests
package com.abc.demo.dao;
import com.abc.demo.dto.EmployeeDto;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
@SpringBootTest
public class EmployeeDaoTests {
@Autowired
private EmployeeDao employeeDao;
@Test
public void findPagedEmployeeList_noConditionPageOneSizeFiveOrderByAgeAsc() {
Page<Employee> employeeDtoPage =
employeeDao.findPagedEmployeeList(null, null, 1, 5, 1, "age");
Assertions.assertAll(
() -> Assertions.assertEquals(5, employeeDtoPage.getContent().size()),
() -> Assertions.assertEquals(10, employeeDtoPage.getTotalElements()),
() -> Assertions.assertEquals(2, employeeDtoPage.getTotalPages()),
() -> Assertions.assertEquals("Zoey", employeeDtoPage.getContent().get(0).getName())
);
}
@Test
public void findPagedEmployeeList_noConditionPageOneSizeFiveOrderByAgeDesc() {
Page<Employee> employeeDtoPage =
employeeDao.findPagedEmployeeList(null, null, 1, 5, 2, "age");
Assertions.assertAll(
() -> Assertions.assertEquals(5, employeeDtoPage.getContent().size()),
() -> Assertions.assertEquals(10, employeeDtoPage.getTotalElements()),
() -> Assertions.assertEquals(2, employeeDtoPage.getTotalPages()),
() -> Assertions.assertEquals("Oliver", employeeDtoPage.getContent().get(0).getName())
);
}
@Test
public void findPagedEmployeeList_nameLikePageOneSizeFiveOrderByAgeAsc() {
Page<Employee> employeeDtoPage =
employeeDao.findPagedEmployeeList("J", null, 1, 5, 1, "age");
Assertions.assertAll(
() -> Assertions.assertEquals(2, employeeDtoPage.getContent().size()),
() -> Assertions.assertEquals(2, employeeDtoPage.getTotalElements()),
() -> Assertions.assertEquals(1, employeeDtoPage.getTotalPages()),
() -> Assertions.assertEquals("John", employeeDtoPage.getContent().get(0).getName())
);
}
@Test
public void findPagedEmployeeList_nameLikeAndAgePageOneSizeFiveOrderByAgeAsc() {
Page<Employee> employeeDtoPage =
employeeDao.findPagedEmployeeList("J", 34, 1, 5, 1, "age");
Assertions.assertAll(
() -> Assertions.assertEquals(1, employeeDtoPage.getContent().size()),
() -> Assertions.assertEquals(1, employeeDtoPage.getTotalElements()),
() -> Assertions.assertEquals(1, employeeDtoPage.getTotalPages()),
() -> Assertions.assertEquals("Jason", employeeDtoPage.getContent().get(0).getName())
);
}
}
最後一個測試的執行sql如下。
-- pageSql
with emp as (
select
e.*,
d.name as department_name
from employee e
join department d on d.id = e.department_id
where 1 = 1
and e.name like ('%' || :name || '%')
and e.age = :age
order by age ASC
)
select t.* from (
select
rownum rn,
emp.*
from emp
) t
where rn between :startRow and :endRow
-- countSql
with emp as (
select
e.*,
d.name as department_name
from employee e
join department d on d.id = e.department_id
where 1 = 1
and e.name like ('%' || :name || '%')
and e.age = :age
order by age ASC
)
select count(*) as cnt from emp
看到同事寫的覺得很棒所以紀錄一下。原來with這麼好用。
2 則留言:
謝謝博主的分享,一直有看博主的文章,留言支持一下。
謝謝您的留言支持!
張貼留言