Spring JDBC NamedParameterJdbcTemplate CRUD 新增、查詢、修改、刪除 範例

Spring JDBC NamedParameterJdbcTemplate新增、查詢、修改、刪除範例如下。

package com.abc.demo.dao;

import com.abc.demo.entity.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class DemoDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /**
     * 新增
     */
    public int insert(Employee employee) {
        String sql = "insert into EMPLOYEE (name, age) values (:name, :age)";
        MapSqlParameterSource params =
                new MapSqlParameterSource()
                        .addValue("name", name)
                        .addValue("age", age);
                        
        int count = namedParameterJdbcTemplate.update(sql, params);
        return count; // 新增筆數
    }

    /**
     * 查詢
     */
    public List<Employee> query(int age) {
        String sql = "select * from EMPLOYEE where age = :age";
        MapSqlParameterSource params =
                new MapSqlParameterSource()
                        .addValue("age", age);

        List<Employee> employeeList =
                namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) -> {
                    Employee employee = new Employee();
                    employee.setId(rs.getLong("ID"));
                    employee.setName(rs.getString("NAME"));
                    employee.setAge(rs.getInt("AGE"));
                    return employee;
                });

        return employeeList;
    }

    /**
     * 修改
     */
    public int update(long id, int age) {
        String sql = "update EMPLOYEE set age = :age where id = :id";
        MapSqlParameterSource params =
                new MapSqlParameterSource()
                        .addValue("id", id)
                        .addValue("age", age);
                        
        int count = namedParameterJdbcTemplate.update(sql, params);
        return count; // 修改筆數
    }

    /**
     * 刪除
     */
    public int delete(long id) {
        String sql = "delete from EMPLOYEE where id = :id";
        MapSqlParameterSource params =
                new MapSqlParameterSource()
                        .addValue("id", id);
                        
        int count = namedParameterJdbcTemplate.update(sql, params);
        return count; // 刪除筆數
    }
}

• Spring JDBC JdbcTemplate 與 NamedParameterJdbcTemplate 差別
• Spring JDBC MapSqlParameterSource
• Spring Data JPA NamedParameterJdbcTemplate Oracle 11g page query 分頁查詢範例