Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Search | Email

10.6. JdbcTemplate

10.6.1. execute

jdbcTemplate.execute("CREATE TABLE USER (id integer, name varchar(100))");  			
			

10.6.2. queryForInt

int count = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM USER");			
			

10.6.3. queryForObject

10.6.3.1. 返回整形与字符型

Integer age = queryForObject("select age from emp", Integer.class);
String name = queryForObject("select name from emp",String.class);
				

10.6.3.2. 查询 Double 类型数据库

				
	private double getSumByMemberId(int memberId) {
		double result = 0.0d;
		String sql = "SELECT sum(o.price::NUMERIC) as total FROM public.order o group by member_id ="+ memberId;
		try {
			result = jdbcTemplate.queryForObject(sql, Double.class);
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			log.info("{} {}", MemberId, e.toString());
		}
		return result;
	}
				
				

10.6.3.3. 返回日期

注意 Date 是 java.util 不是 java.sql

	private static final Logger log = LoggerFactory.getLogger(ScheduledTasks.class);
	private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd HH:mm:ss");
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Scheduled(initialDelay = 1000, fixedRate = 60000)
	public void currentDate() {
		Date date = jdbcTemplate.queryForObject("select sysdate from dual", Date.class);
		log.info("The oracle sysdate is {}", dateFormat.format(date));
	}			
				

10.6.3.4. 返回结果集

				
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@RequestMapping(value = "/article")
	public @ResponseBody String dailyStats(@RequestParam Integer id) {
		String query = "SELECT id, title, content from article where id = " + id;

		return jdbcTemplate.queryForObject(query, (resultSet, i) -> {
			System.out.println(resultSet.getLong(1) + "," + resultSet.getString(2) + "," + resultSet.getString(3));
			return (resultSet.getLong(1) + "," + resultSet.getString(2) + "," + resultSet.getString(3));
		});

	}			
				
				

10.6.3.5. 通过 "?" 向SQL传递参数

				
package com.example.api.restful;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.example.api.pojo.ResponseRestful;

@RestController
@RequestMapping("/restful/cms")
public class CmsRestController {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@RequestMapping(value = "/article/update/count/{articleId}", method = RequestMethod.GET, produces = { "application/xml", "application/json" })
	public ResponseRestful updateCount(@PathVariable int articleId) {
		String sql = "SELECT count(*) FROM cms.article WHERE id > ?";
		int count = jdbcTemplate.queryForObject(sql, new Object[] { articleId }, Integer.class);
		return new ResponseRestful(true, 1, "文章更新", count);
	}
}
				
				

10.6.4. queryForList

			
List rows = jdbcTemplate.queryForList("SELECT * FROM USER");  
Iterator it = rows.iterator();  
while(it.hasNext()) {  
    Map userMap = (Map) it.next();  
    System.out.print(userMap.get("id") + "\t");  
    System.out.print(userMap.get("name") + "\t");  
    System.out.print(userMap.get("sex") + "\t");  
    System.out.println(userMap.get("age") + "\t");  
}