Here are few examples to show how to use SimpleJdbcTemplate query()
methods to query or extract data from database. In JdbcTemplate
query()
, you need to manually cast the returned result to desire object type, and pass an Object array as parameters. In SimpleJdbcTemplate
, it is more user friendly and simple.
jdbctemplate
vesus simplejdbctemplate
Please compare this SimpleJdbcTemplate
example with this JdbcTemplate
example.
1. Querying for Single Row
Here’s two ways to show you how to query or extract a single row from database, and convert it into a model class.
1.1 Custom RowMapper
In general, It’s always recommend to implement the RowMapper
interface to create a custom RowMapper
to suit your needs.
package com.mkyong.customer.model;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class CustomerRowMapper implements RowMapper
{
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Customer customer = new Customer();
customer.setCustId(rs.getInt("CUST_ID"));
customer.setName(rs.getString("NAME"));
customer.setAge(rs.getInt("AGE"));
return customer;
}
}
public Customer findByCustomerId(int custId){
String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
Customer customer = getSimpleJdbcTemplate().queryForObject(
sql, new CustomerParameterizedRowMapper(), custId);
return customer;
}
1.2 BeanPropertyRowMapper
In SimpleJdbcTemplate
, you need to use ‘ParameterizedBeanPropertyRowMapper
’ instead of ‘BeanPropertyRowMapper
’.
public Customer findByCustomerId2(int custId){
String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
Customer customer = getSimpleJdbcTemplate().queryForObject(sql,
ParameterizedBeanPropertyRowMapper.newInstance(Customer.class), custId);
return customer;
}
2. Querying for Multiple Rows
Query or extract multiple rows from database, and convert it into a List.
2.1 ParameterizedBeanPropertyRowMapper
public List<Customer> findAll(){
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers =
getSimpleJdbcTemplate().query(sql,
ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));
return customers;
}
3. Querying for a Single Value
Query or extract a single column value from database.
3.1 Single column name
It shows how to query a single column name as String
.
public String findCustomerNameById(int custId){
String sql = "SELECT NAME FROM CUSTOMER WHERE CUST_ID = ?";
String name = getSimpleJdbcTemplate().queryForObject(
sql, String.class, custId);
return name;
}
3.2 Total number of rows
It shows how to query a total number of rows from database.
public int findTotalCustomer(){
String sql = "SELECT COUNT(*) FROM CUSTOMER";
int total = getSimpleJdbcTemplate().queryForInt(sql);
return total;
}
Run it
package com.mkyong.common;
import java.util.ArrayList;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.mkyong.customer.dao.CustomerDAO;
import com.mkyong.customer.model.Customer;
public class SimpleJdbcTemplateApp
{
public static void main( String[] args )
{
ApplicationContext context =
new ClassPathXmlApplicationContext("Spring-Customer.xml");
CustomerDAO customerSimpleDAO =
(CustomerDAO) context.getBean("customerSimpleDAO");
Customer customerA = customerSimpleDAO.findByCustomerId(1);
System.out.println("Customer A : " + customerA);
Customer customerB = customerSimpleDAO.findByCustomerId2(1);
System.out.println("Customer B : " + customerB);
List<Customer> customerAs = customerSimpleDAO.findAll();
for(Customer cust: customerAs){
System.out.println("Customer As : " + customerAs);
}
List<Customer> customerBs = customerSimpleDAO.findAll2();
for(Customer cust: customerBs){
System.out.println("Customer Bs : " + customerBs);
}
String customerName = customerSimpleDAO.findCustomerNameById(1);
System.out.println("Customer Name : " + customerName);
int total = customerSimpleDAO.findTotalCustomer();
System.out.println("Total : " + total);
}
}
Conclusion
The SimpleJdbcTemplate
isn’t a replacement for JdbcTemplate
, it’s just a java5-friendly supplement to it.
Spring SimpleJdbcTemplate Querying examples的更多相关文章
-
Spring JdbcTemplate Querying examples
Here are few examples to show you how to use JdbcTemplate query() methods to query or extract data f ...
-
Spring Named Parameters examples in SimpleJdbcTemplate
In JdbcTemplate, SQL parameters are represented by a special placeholder "?" symbol and bi ...
-
Spring SimpleJdbcTemplate batchUpdate() example
In this tutorial, we show you how to use batchUpdate() in SimpleJdbcTemplate class. See batchUpdate( ...
-
Spring + JdbcTemplate + JdbcDaoSupport examples
In Spring JDBC development, you can use JdbcTemplate and JdbcDaoSupport classes to simplify the over ...
-
Spring SimpleJdbcTemplate查询示例
这里有几个例子来说明如何使用SimpleJdbcTemplate query()方法来查询或从数据库中提取数据.在 JdbcTemplate query() 方法,需要手动转换返回的结果转换为一个目标 ...
-
Spring AOP Example – Pointcut , Advisor
In last Spring AOP advice examples, the entire methods of a class are intercepted automatically. But ...
-
Complete Guide for Spring Boot Actuator
You are here to learn about Spring Boot Actuator for collecting metrics about your production grade ...
-
spring boot rest例子
简介: 本文将帮助您使用 Spring Boot 创建简单的 REST 服务. 你将学习 什么是 REST 服务? 如何使用 Spring Initializr 引导创建 Rest 服务应用程序? 如 ...
-
【Spring Boot】构造、访问Restful Webservice与定时任务
Spring Boot Guides Examples(1~3) 参考网址:https://spring.io/guides 创建一个RESTful Web Service 使用Eclipse 创建一 ...
随机推荐
-
HTML5- Canvas入门(六)
已经第六章了,也差不多接近尾声,如果你从第一章耐心follow到本章结束,那你便能掌握canvas的大部分知识点(当然如果要精通,还是得多靠练习,做一些小案例). 今天我们要学习的是canvas的变形 ...
-
ImageLoader
配置ImageLoader 一般我们在使用ImageLoader的时候,需要在应用程序的入口进行它的一个配置,这个配置一般写到Application里边 * public void initImage ...
-
JAVA日期加减运算
1.用java.util.Calender来实现 Calendar calendar=Calendar.getInstance(); calendar.setTime(new Date()) ...
-
SQLServer 维护脚本分享(07)IO
sp_helpfile --当前数据库文件分配情况 sp_spaceused --当前db空间大小(有时不准) sp_spaceused 'dbo.user' --指定表的空间大小(有时不准) sp_ ...
-
HDU4272LianLianKan(dfs)
Problem Description I like playing game with my friend, although sometimes looks pretty naive. Today ...
-
SVN基础命令手册
SVN版本号:1.5 及更新版本号 名词说明: WC:Working Copy 你的工作区 Versioned:受控的:受版本号控制的 SVN是什么? SVN是开源的版本号控制系统. 比CVS很多其它 ...
-
atom那些事儿
基于electron(Electron 的底层基于Chromium 和node.js)
-
ORA-01017: invalid username/password; logon denied 解决方案
在SQLPLUS窗口下进行用户登录,出现ORA-01017:invalid username/password:logon denied .如下图: 在网上找了很久,发现一个实用的解决方案,操作如下: ...
-
ruby on rails在fedora18上install
ruby on rails 在fedora18下的安装 天朝的网络原因,安装不是很顺畅,所以把过程记录下备用 前面下载rubygem什么的都比较快,新建一个project的时候会出问题 gem new ...
-
javascript改写 <;select>;checked的选中,并不会出发change事件,太好了!
<html> <title>测试select标签的分别用程序checked.用户手动checked</title> <body> 第一级菜单: < ...