Spring JdbcTemplate Querying Examples
参考地址: https://www.mkyong.com/spring/spring-jdbctemplate-querying-examples/
Here are a few examples to show you how to use Spring JdbcTemplate
to query or extract data from database.
Technologies used :
- Spring Boot 2.1.2.RELEASE
- Spring JDBC 5.1.4.RELEASE
- Maven 3
- Java 8
In Short:
-
jdbcTemplate.queryForObject
for single row or value -
jdbcTemplate.query
for multiple rows or list
The article is updated from Spring core 2.5.x to Spring Boot 2.1.x
P.S You may also interested in this Spring Boot JDBC Examples
1. Query for Single Row
In Spring, we can use jdbcTemplate.queryForObject()
to query a single row record from database, and convert the row into an object via row mapper.
1.1 Custom RowMapper
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CustomerRowMapper implements RowMapper<Customer> {
@Override
public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
Customer customer = new Customer();
customer.setID(rs.getLong("ID"));
customer.setName(rs.getString("NAME"));
customer.setAge(rs.getInt("AGE"));
customer.setCreatedDate(rs.getTimestamp("created_date").toLocalDateTime());
return customer;
}
}
import org.springframework.jdbc.core.JdbcTemplate;
@Autowired
private JdbcTemplate jdbcTemplate;
public Customer findByCustomerId(Long id) {
String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";
return jdbcTemplate.queryForObject(sql, new Object[]{id}, new CustomerRowMapper());
}
1.2 Spring BeanPropertyRowMapper
, this class saves you a lot of time for the mapping.
import org.springframework.jdbc.core.BeanPropertyRowMapper;
public Customer findByCustomerId2(Long id) {
String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";
return (Customer) jdbcTemplate.queryForObject(
sql,
new Object[]{id},
new BeanPropertyRowMapper(Customer.class));
}
1.3 In Java 8, we can map it directly:
public Customer findByCustomerId3(Long id) {
String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";
return jdbcTemplate.queryForObject(sql, new Object[]{id}, (rs, rowNum) ->
new Customer(
rs.getLong("id"),
rs.getString("name"),
rs.getInt("age"),
rs.getTimestamp("created_date").toLocalDateTime()
));
}
2. Query for Multiple Rows
For multiple rows, we use jdbcTemplate.query()
2.1 Custom RowMapper
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = jdbcTemplate.query(
sql,
new CustomerRowMapper());
return customers;
}
2.2 BeanPropertyRowMapper
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = jdbcTemplate.query(
sql,
new BeanPropertyRowMapper(Customer.class));
return customers;
}
2.3 Java 8
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
return jdbcTemplate.query(
sql,
(rs, rowNum) ->
new Customer(
rs.getLong("id"),
rs.getString("name"),
rs.getInt("age"),
rs.getTimestamp("created_date").toLocalDateTime()
)
);
}
2.4 jdbcTemplate.queryForList
, it works, but not recommend, the mapping in Map
may not same as the object, need casting.
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = new ArrayList<>();
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
for (Map row : rows) {
Customer obj = new Customer();
obj.setID(((Integer) row.get("ID")).longValue());
obj.setName((String) row.get("NAME"));
// Spring returns BigDecimal, need convert
obj.setAge(((BigDecimal) row.get("AGE")).intValue());
obj.setCreatedDate(((Timestamp) row.get("CREATED_DATE")).toLocalDateTime());
customers.add(obj);
}
return customers;
}
3. Query for a Single Value
It’s same like query a single row from database, uses jdbcTemplate.queryForObject()
3.1 Single column name
public String findCustomerNameById(Long id) {
String sql = "SELECT NAME FROM CUSTOMER WHERE ID = ?";
return jdbcTemplate.queryForObject(
sql, new Object[]{id}, String.class);
}
3.2 Count
public int count() {
String sql = "SELECT COUNT(*) FROM CUSTOMER";
// queryForInt() is Deprecated
// https://www.mkyong.com/spring/jdbctemplate-queryforint-is-deprecated/
//int total = jdbcTemplate.queryForInt(sql);
return jdbcTemplate.queryForObject(sql, Integer.class);
}
4. Test
Run a Spring Boot CommandLineRunner
application, create tables and test the APIs.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- in-memory database -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
package com.mkyong;
import com.mkyong.customer.Customer;
import com.mkyong.customer.CustomerRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;
@SpringBootApplication
public class StartApplication implements CommandLineRunner {
private static final Logger log = LoggerFactory.getLogger(StartApplication.class);
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
CustomerRepository customerRepository;
public static void main(String[] args) {
SpringApplication.run(StartApplication.class, args);
}
@Override
public void run(String... args) {
log.info("StartApplication...");
startCustomerApp();
}
// Tested with H2 database
void startCustomerApp() {
jdbcTemplate.execute("DROP TABLE customer IF EXISTS");
jdbcTemplate.execute("CREATE TABLE customer(" +
"id SERIAL, name VARCHAR(255), age NUMERIC(2), created_date timestamp)");
List<Customer> list = Arrays.asList(
new Customer("Customer A", 19),
new Customer("Customer B", 20),
new Customer("Customer C", 21),
new Customer("Customer D", 22)
);
list.forEach(x -> {
log.info("Saving...{}", x.getName());
customerRepository.save(x);
});
log.info("[FIND_BY_ID]");
log.info("{}", customerRepository.findByCustomerId(1L));
log.info("{}", customerRepository.findByCustomerId2(2L));
log.info("{}", customerRepository.findByCustomerId3(3L));
log.info("[FIND_ALL]");
log.info("{}", customerRepository.findAll());
log.info("{}", customerRepository.findAll2());
log.info("{}", customerRepository.findAll3());
log.info("{}", customerRepository.findAll4());
log.info("[FIND_NAME_BY_ID]");
log.info("{}", customerRepository.findCustomerNameById(4L));
log.info("[COUNT]");
log.info("{}", customerRepository.count());
}
}
Output
INFO com.mkyong.StartApplication - Saving...Customer A
INFO com.mkyong.StartApplication - Saving...Customer B
INFO com.mkyong.StartApplication - Saving...Customer C
INFO com.mkyong.StartApplication - Saving...Customer D
INFO com.mkyong.StartApplication - [FIND_BY_ID]
INFO com.mkyong.StartApplication - Customer{ID=1, name='Customer A', age=19, createdDate=2019-08-01T15:48:45.950848}
INFO com.mkyong.StartApplication - Customer{ID=2, name='Customer B', age=20, createdDate=2019-08-01T15:48:45.961819}
INFO com.mkyong.StartApplication - Customer{ID=3, name='Customer C', age=21, createdDate=2019-08-01T15:48:45.961819}
INFO com.mkyong.StartApplication - [FIND_ALL]
INFO com.mkyong.StartApplication - [
Customer{ID=1, name='Customer A', age=19, createdDate=2019-08-01T15:48:45.950848},
Customer{ID=2, name='Customer B', age=20, createdDate=2019-08-01T15:48:45.961819},
Customer{ID=3, name='Customer C', age=21, createdDate=2019-08-01T15:48:45.961819},
Customer{ID=4, name='Customer D', age=22, createdDate=2019-08-01T15:48:45.961819}
]
//...omitted, duplicate code
INFO com.mkyong.StartApplication - [FIND_NAME_BY_ID]
INFO com.mkyong.StartApplication - Customer D
INFO com.mkyong.StartApplication - [COUNT]
INFO com.mkyong.StartApplication - 4
相关推荐
**基于注解的Spring JdbcTemplate** 在Java世界中,Spring框架是企业级应用开发的首选。Spring JDBC模絫提供了一种简洁的方式来处理数据库操作,而`Spring JdbcTemplate`是这个模絫的核心组件。本教程将深入探讨...
《深入解析Spring JdbcTemplate》 Spring JDBC Template是Spring框架中用于简化JDBC操作的一个核心组件,它是Spring对JDBC API的封装,旨在提供一个更加简洁、健壮且可测试的数据库访问层。在这个实例工程中,我们...
SpringJdbcTemplate是Spring框架中用于简化Java数据库访问的工具,它是Spring JDBC模块的核心。这个封装工具类的出现是为了提供一种更简洁、易于使用的接口来执行SQL操作,减轻开发者处理数据库连接、事务管理以及...
Druid数据库连接池的SpringJDBCTemplate所需的jar包,Druid数据库连接池的SpringJDBCTemplate所需的jar包,Druid数据库连接池的SpringJDBCTemplate所需的jar包,Druid数据库连接池的SpringJDBCTemplate所需的jar包,...
Struts2、Spring和Spring JDBC Template是Java Web开发中常用的三个框架,它们分别负责不同的职责。Struts2作为MVC(Model-View-Controller)框架,主要处理前端请求和业务逻辑;Spring则是一个全面的后端框架,提供...
**Spring JdbcTemplate**是Spring框架中的一个核心组件,主要用于简化Java数据库访问。它提供了一种模板化的方式来执行SQL语句,使得开发人员可以避免编写大量的重复代码,专注于业务逻辑,而不是底层的数据库交互...
3. `spring-jdbc-5.0.0.RELEASE.jar`:包含Spring对JDBC的支持,包括JDBCTemplate和DataSourceTransactionManager,它们是与数据库交互和管理事务的关键。 4. `spring-tx-5.0.0.RELEASE.jar`:提供了事务管理功能,...
使用 Spring JdbcTemplate 调用 Oracle 存储过程实现 CRUD 在本文中,我们将讨论如何使用 Spring JdbcTemplate 调用 Oracle 存储过程来实现 CRUD(Create、Read、Update、Delete)操作。我们将首先编写 Oracle 存储...
Spring的JdbcTemplate是Spring框架中用于简化数据库操作的工具类,它是基于JDBC但又抽象出了一层,避免了直接与数据库驱动API交互,从而提高了代码的可读性和可维护性。本文将深入探讨Spring JdbcTemplate的常用方法...
Spring JdbcTemplate是Spring框架中用于简化数据库操作的一个重要组件,它是Spring对JDBC的轻量级封装,旨在提供一种结构良好、易于使用的SQL执行机制,同时保持了JDBC的灵活性。在本实例中,我们将深入探讨Spring ...
Spring JdbcTemplate是Spring框架中的一个核心组件,主要用来简化数据库操作。它提供了一种模板方法设计模式,将SQL语句的执行与结果处理进行了抽象,使得开发者可以更加专注于业务逻辑,而无需关心底层数据访问的...
模仿spring jdbcTemplate的粗略实现,只有很小的参考价值,如果是java初学者可以使用这个封装好的工具进行数据库操作,只需要在db.properties里配置好driver,url等信息
在Spring框架中,JdbcTemplate是用于简化数据库操作的重要工具,它是Spring JDBC模块的一部分。通过使用JdbcTemplate,开发者可以避免编写大量的重复代码,如手动管理连接、处理结果集等,从而专注于业务逻辑。本文...
在Spring框架中,`JdbcTemplate`是用于简化Java数据库连接(JDBC)操作的一个核心组件。这个类提供了很多方法来执行SQL查询、更新语句,包括批处理操作。本篇文章将详细探讨`batchUpdate`方法及其在实际开发中的应用...
根据给定的文件信息,以下是对“Spring JdbcTemplate API”的详细知识点解析: ### Spring JdbcTemplate API:数据库操作的模板模式 #### 概述 在Spring框架中,`JdbcTemplate`是一个用于简化JDBC编程的工具类,...
1.Spring4前 spring-jdbc包是独立的,4以后spring-jdbc 就已经没有了
Spring JDBCTemplate是Spring框架中的一个核心组件,它提供了一种简化数据库操作的抽象层,使得开发者可以更加方便、高效地处理数据库事务。本篇将深入探讨Spring JDBCTemplate的使用及其源码解析,帮助你理解其背后...
spring JdbcTemplate query方法使用示例,欢迎下载借鉴
本主题将深入探讨Spring框架中的JdbcTemplate组件,以及模板模式和回调机制的概念。 **1. Spring JdbcTemplate** JdbcTemplate是Spring提供的一个用于简化数据库操作的API,它是Spring对JDBC(Java Database ...
在Java Web开发中,Spring框架提供了丰富的工具来简化数据库操作,其中之一就是`Spring JdbcTemplate`。`JdbcTemplate`是Spring对JDBC(Java Database Connectivity)的一层轻量级封装,它使得开发者能够更加方便地...