Spring Data JPA NamedParameterJdbcTemplate Oracle 11g page query 分頁查詢範例

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這麼好用。