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
相关推荐
strut2+spring+springjdbctemplate做的简易登录系统
Druid数据库连接池的SpringJDBCTemplate所需的jar包,Druid数据库连接池的SpringJDBCTemplate所需的jar包,Druid数据库连接池的SpringJDBCTemplate所需的jar包,Druid数据库连接池的SpringJDBCTemplate所需的jar包,...
一个非常简单基于注解的Spring JdbcTemplate,供初学者参考用。
Spring JDBCTemplate连接池jar包
Spring JdbcTemplate调用Oracle存储过程输出游标结果集实现增删改查
模仿spring jdbcTemplate的粗略实现,只有很小的参考价值,如果是java初学者可以使用这个封装好的工具进行数据库操作,只需要在db.properties里配置好driver,url等信息
Spring JdbcTemplate的操作,包括接口,BaseDao,log4J配置文件,主要为oracle数据库操作,很多特殊方法只对oracle有效
spring-jdbcTemplate实例工程
SpringJdbcTemplate封装工具包,包括规范model格式接口,封装SpringJdbcTemplate,实现分页,自适应多种数据库
1.Spring4前 spring-jdbc包是独立的,4以后spring-jdbc 就已经没有了
spring JdbcTemplate query方法使用示例,欢迎下载借鉴
使用Spring的JdbcTemplate调用Oracle的存储过程
JdbcTemplate api 下载 Spring
使用Spring的JdbcTemplate实现分页功能
NULL 博文链接:https://rayfuxk.iteye.com/blog/2286519
spring jdbctemplate组件的简单实例。可以直接运行该实例来学习spring的jdbctemplate。处于初学者,或开发互联网性能要求较高的比较有料。
SSH笔记-Spring JdbcTemplate,使用JdbcTemplate对数据库进行操作,使用具名参数和JDBC模板,简化操作
运用Spring中关于JDBC的一个辅助类(JDBC Template),进行curd操作
本篇文档主要介绍Spring JDBCTemplate知识。亦可作为Spring JDBCTemplate的API文档。文档中的内容,对于学习Spring JDBCTemplate有很大的帮助,也可帮助他人学习Spring Boot中的JDBCTemplate知识。
帮助更多人上手java Spring JdbcTemplate操作数据库