Spring SimpleJdbcTemplate Querying examples

时间:2022-07-03 07:51:49

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的更多相关文章

  1. Spring JdbcTemplate Querying examples

    Here are few examples to show you how to use JdbcTemplate query() methods to query or extract data f ...

  2. Spring Named Parameters examples in SimpleJdbcTemplate

    In JdbcTemplate, SQL parameters are represented by a special placeholder "?" symbol and bi ...

  3. Spring SimpleJdbcTemplate batchUpdate&lpar;&rpar; example

    In this tutorial, we show you how to use batchUpdate() in SimpleJdbcTemplate class. See batchUpdate( ...

  4. Spring &plus; JdbcTemplate &plus; JdbcDaoSupport examples

    In Spring JDBC development, you can use JdbcTemplate and JdbcDaoSupport classes to simplify the over ...

  5. Spring SimpleJdbcTemplate查询示例

    这里有几个例子来说明如何使用SimpleJdbcTemplate query()方法来查询或从数据库中提取数据.在 JdbcTemplate query() 方法,需要手动转换返回的结果转换为一个目标 ...

  6. Spring AOP Example – Pointcut &comma; Advisor

    In last Spring AOP advice examples, the entire methods of a class are intercepted automatically. But ...

  7. Complete Guide for Spring Boot Actuator

    You are here to learn about Spring Boot Actuator for collecting metrics about your production grade ...

  8. spring boot rest例子

    简介: 本文将帮助您使用 Spring Boot 创建简单的 REST 服务. 你将学习 什么是 REST 服务? 如何使用 Spring Initializr 引导创建 Rest 服务应用程序? 如 ...

  9. 【Spring Boot】构造、访问Restful Webservice与定时任务

    Spring Boot Guides Examples(1~3) 参考网址:https://spring.io/guides 创建一个RESTful Web Service 使用Eclipse 创建一 ...

随机推荐

  1. HTML5- Canvas入门(六)

    已经第六章了,也差不多接近尾声,如果你从第一章耐心follow到本章结束,那你便能掌握canvas的大部分知识点(当然如果要精通,还是得多靠练习,做一些小案例). 今天我们要学习的是canvas的变形 ...

  2. ImageLoader

    配置ImageLoader 一般我们在使用ImageLoader的时候,需要在应用程序的入口进行它的一个配置,这个配置一般写到Application里边 * public void initImage ...

  3. JAVA日期加减运算

    1.用java.util.Calender来实现 Calendar calendar=Calendar.getInstance();      calendar.setTime(new Date()) ...

  4. SQLServer 维护脚本分享(07)IO

    sp_helpfile --当前数据库文件分配情况 sp_spaceused --当前db空间大小(有时不准) sp_spaceused 'dbo.user' --指定表的空间大小(有时不准) sp_ ...

  5. HDU4272LianLianKan(dfs)

    Problem Description I like playing game with my friend, although sometimes looks pretty naive. Today ...

  6. SVN基础命令手册

    SVN版本号:1.5 及更新版本号 名词说明: WC:Working Copy 你的工作区 Versioned:受控的:受版本号控制的 SVN是什么? SVN是开源的版本号控制系统. 比CVS很多其它 ...

  7. atom那些事儿

    基于electron(Electron 的底层基于Chromium 和node.js)

  8. ORA-01017&colon; invalid username&sol;password&semi; logon denied 解决方案

    在SQLPLUS窗口下进行用户登录,出现ORA-01017:invalid username/password:logon denied .如下图: 在网上找了很久,发现一个实用的解决方案,操作如下: ...

  9. ruby on rails在fedora18上install

    ruby on rails 在fedora18下的安装 天朝的网络原因,安装不是很顺畅,所以把过程记录下备用 前面下载rubygem什么的都比较快,新建一个project的时候会出问题 gem new ...

  10. javascript改写 &lt&semi;select&gt&semi;checked的选中,并不会出发change事件&comma;太好了!

    <html> <title>测试select标签的分别用程序checked.用户手动checked</title> <body> 第一级菜单: < ...