Spring JdbcTemplate vs NamedParameterJdbcTemplate: Complete Guide with Code Examples

Project Setup and Dependencies

The article begins by creating a Maven project with the following key dependencies (all version 5.3.17 unless noted):

spring-context

spring-jdbc

spring-test (test scope)

mysql-connector-java 8.0.28

druid 1.2.8 (connection pool)

junit 4.13.2 (test scope)

commons-lang3 3.9

After refreshing Maven, the project structure is organized into packages: config (Spring configuration), dao (JdbcTemplate operations), domain (Java beans), service (business logic), resources (configuration files), and test (JUnit tests). The demo operates on an Employee table to showcase both JdbcTemplate variants.

Database Preparation

A MySQL database named testdb is created with an employee table containing columns e_id (auto-increment primary key), e_name (varchar 50), and e_salary (int). Seven initial rows are inserted with sample Chinese names and salaries ranging from 25000 to 40000.

Configuration Details

jdbc.properties

Database connection parameters are externalized:

mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/testdb?useSSL=false
mysql.username=root
mysql.password=123456
# Druid pool settings
druid.initialSize=3
druid.maxActive=20
druid.maxWait=3000

JdbcConfig (Spring Bean Configuration)

The JdbcConfig class uses @PropertySource to load jdbc.properties and @Value to inject each property. It defines three @Bean methods: getDataSource() — creates a DruidDataSource with the injected driver, URL, username, password, and pool settings. getJdbcTemplate() — returns a JdbcTemplate instance named "jdbcTemplate1" using the data source. getJdbcTemplate2() — returns a NamedParameterJdbcTemplate instance named "jdbcTemplate2" using the same data source.

SpringConfig

A minimal @Configuration class with @ComponentScan("com.jobs") and @Import(JdbcConfig.class) to wire everything together.

Domain Class

The Employee class mirrors the database columns exactly ( e_id, e_name, e_salary) with getters, setters, constructors, and toString(). The author emphasizes that matching field names enables automatic mapping via BeanPropertyRowMapper, greatly improving development efficiency.

DAO Layer: Two JdbcTemplate Implementations

EmployeeDao1 — Classic JdbcTemplate (Positional Parameters)

Injected with @Resource(name="jdbcTemplate1"). Each method builds SQL with ? placeholders and passes an Object[] array where parameter order must match the SQL.

add(Employee) — INSERT ... VALUES(?,?,?) with {emp.getE_id(), emp.getE_name(), emp.getE_salary()}.

update(Employee) — UPDATE ... SET e_name=?, e_salary=? WHERE e_id=? with {emp.getE_name(), emp.getE_salary(), emp.getE_id()}.

getEmployeeCount() — SELECT COUNT(*) FROM employee using queryForObject(sql, Long.class).

getEmployeeById(Integer) — uses a custom RowMapper<Employee> lambda to map ResultSet columns to bean properties, then queryForObject(sql, rm, eid).

selectAll() — leverages BeanPropertyRowMapper<Employee>(Employee.class) for automatic mapping when column names match bean fields.

selectByCondition(String, Integer, Integer) — dynamically builds SQL with StringBuilder and collects parameters in a List<Object>. Conditions for name (LIKE), salaryStart (>=), and salaryEnd (<=) are appended only when non-null. The list is converted to array via paramList.toArray() for query().

deleteByIds(Integer...) — constructs an IN clause with a variable number of ? placeholders, adding each ID to paramList. If no IDs provided, uses WHERE 1=2 to delete nothing. The commented-out alternative shows string concatenation (without parameters) for comparison.

EmployeeDao2 — NamedParameterJdbcTemplate (Named Parameters)

Injected with @Resource(name="jdbcTemplate2"). SQL uses :name syntax; parameters are supplied via a Map<String, Object> where keys are parameter names without the colon. Order in the map is irrelevant.

add(Employee) — INSERT ... VALUES(:id, :name, :salary) with map entries "id", "name", "salary".

update(Employee) — UPDATE ... SET e_name=:name, e_salary=:salary WHERE e_id=:id with same map keys.

getEmployeeCount() — queryForObject(sql, new HashMap<>(), Long.class) (empty map for no parameters).

getEmployeeById(Integer) — custom RowMapper plus map with "id" key.

selectAll() — same BeanPropertyRowMapper convenience.

selectByCondition(...) — dynamic SQL appends :name, :start, :end; each non-null condition adds an entry to paramMap.

deleteByIds(Integer...) — builds IN (:id0, :id1, ...) with keys "id0", "id1", etc., populated from the varargs array. Falls back to WHERE 1=2 when empty.

The author explicitly recommends NamedParameterJdbcTemplate for real projects because named parameters eliminate ordering errors and improve readability.

Service Layer

Two thin service classes ( EmployeeService1, EmployeeService2) each autowire their corresponding DAO and delegate every call. This demonstrates a typical layered architecture where services orchestrate DAOs without containing SQL logic.

Test Verification

Two JUnit 4 test classes annotated with @RunWith(SpringJUnit4ClassRunner.class) and @ContextConfiguration(classes = SpringConfig.class) exercise the full CRUD cycle: EmployeeServiceTest1 tests EmployeeService1 (classic JdbcTemplate). EmployeeServiceTest2 tests EmployeeService2 (NamedParameterJdbcTemplate).

Each test class contains methods for add, update, getEmployeeCount, getEmployeeById, selectAll, selectByCondition (name="任", salary 30000–60000), and deleteByIds (IDs 100, 101, 102). The test for deleteByIds in the second class passes an Integer[] array instead of varargs, showing both invocation styles work.

Conclusion

The article concludes that both JdbcTemplate variants are simple to use, the demo code is fully tested, and NamedParameterJdbcTemplate is strongly recommended for actual development due to its named-parameter clarity and safety.