Home | 簡體中文 | 繁體中文 | 雜文 | 知乎專欄 | Github | OSChina 博客 | 雲社區 | 雲棲社區 | Facebook | Linkedin | 視頻教程 | 打賞(Donations) | About
知乎專欄多維度架構 | 微信號 netkiller-ebook | QQ群:128659835 請註明“讀者”

8.6. Spring Data with JdbcTemplate

8.6.1. execute

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

8.6.2. queryForInt

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

8.6.3. queryForLong

		
lang count = jdbcTemplate.queryForLong("SELECT COUNT(*) FROM USER");		
		
		

8.6.4. queryForObject

8.6.4.1. 返回整形與字元型

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

8.6.4.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;
	}
				
			

8.6.4.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));
	}
			
			

8.6.4.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));
		});

	}			
				
			

8.6.4.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);
	}
}
				
			

8.6.4.6. RowMapper 記錄映射

				
package cn.netkiller.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.setId(rs.getInt("ID"));
		customer.setName(rs.getString("NAME"));
		customer.setAge(rs.getInt("AGE"));
		return customer;
	}

}
				
			
				
public Customer findByCustomerId(int id){

	String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";

	Customer customer = (Customer)getJdbcTemplate().queryForObject(
			sql, new Object[] { id }, new CustomerRowMapper());

	return customer;
}
				
			

				
Member member = this.jdbcTemplate.queryForObject("select first_name, last_name from member where id = ?",new Object[]{112L},
new RowMapper<Member>() {
    public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
        Member member = new Member();
        member.setFirstName(rs.getString("first_name"));
        member.setLastName(rs.getString("last_name"));
        return member;
    }
});
				
			

8.6.5. 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");  
}
			
		

8.6.5.1. Iterator 用法

		
List<Map<String, Object>> rows = jdbcTemplate.queryForList("select * from user_token where address=? and contract_address not in (select contract_address from token)", new Object[] { address });
Iterator<Map<String, Object>> it = rows.iterator();
while (it.hasNext()) {
	Map<String, Object> userMap = (Map<String, Object>) it.next();
	
	String contractAddress = (String) userMap.get("contract_address");
	String symbol = (String) userMap.get("symbol");
	int decimals = (int) userMap.get("decimals");
}		
		
			

8.6.5.2. for 循環

		
	@RequestMapping("/article/tag/{siteId}")
	public ResponseRestful tag(@PathVariable int siteId) {
		List<Tag> tags = new ArrayList<Tag>();
		List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();

		String sql = "SELECT id,name FROM cms.tag WHERE site_id = ?";
		rows = jdbcTemplate.queryForList(sql, new Object[] { siteId });

		for (Map<String, Object> row : rows) {
			Tag tag = new Tag();
			tag.setId((Integer) row.get("id"));
			tag.setName((String) row.get("name"));
			tags.add(tag);
		}
		logger.info("tag {} SQL: {}", siteId, sql);
		return new ResponseRestful(true, tags.size(), "標籤", tags);
	}			
		
			

8.6.5.3. forEach 用法

			
		jdbcTemplate.queryForList("select id from public.contract").forEach(item -> {
			logger.debug(item.toString());
		});			
			
			

8.6.6. queryForMap

		
Map<String, Object> map = this.jdbcTemplate.queryForMap("SELECT * FROM USERS WHERE USERNAME=?", "username"); 

System.out.println(map.get("USERNAME")); 
		
		

8.6.7. query

8.6.7.1. ResultSet

			
HashMap<String,String> member = jdbcTemplate.query("select name,age from member where id=1", (ResultSet rs) -> {
    HashMap<String,String> results = new HashMap<>();
    while (rs.next()) {
        results.put(rs.getString("name"), rs.getString("age"));
    }
    return results;
}); 
			
			

8.6.7.2. ResultSetExtractor

ResultSetExtractor

			
HashMap<String,String> member = jdbcTemplate.query("select name,age from member where id=1", new ResultSetExtractor<Map>(){
    @Override
    public Map extractData(ResultSet rs) throws SQLException,DataAccessException {
        HashMap<String,String> mapResult= new HashMap<String,String>();
        while(rs.next()){
            mapResult.put(rs.getString("name"),rs.getString("age"));
        }
        return mapResult;
    }
});
			
			

8.6.7.3. RowMapper

			
List<Actor> actors = this.jdbcTemplate.query("select first_name, last_name from actor",new RowMapper<Actor>() {
    public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
        Actor actor = new Actor();
        actor.setFirstName(rs.getString("first_name"));
        actor.setLastName(rs.getString("last_name"));
        return actor;
    }
});
			
			
			
public List<Actor> findAllActors() {
    return this.jdbcTemplate.query( "select first_name, last_name from actor", new ActorMapper());
}

private static final class ActorMapper implements RowMapper<Actor> {

    public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
        Actor actor = new Actor();
        actor.setFirstName(rs.getString("first_name"));
        actor.setLastName(rs.getString("last_name"));
        return actor;
    }
}
			
			

返回第一條數據,事實上只有一條。

			
	public Token getTokenBySymbol(String symbol) {

		List<Token> response = jdbcTemplate.query("select * from token where symbol ='" + symbol + "'", new RowMapper<Token>() {
			public Token mapRow(ResultSet result, int rowNum) throws SQLException {
				Token Token = new Token();
				Token.setContractAddress(result.getString(""));
				Token.setName(result.getString("name"));
				Token.setSymbol(result.getString("symbol"));
				Token.setDecimals(result.getInt("decimals"));
				return Token;
			}
		});

		if (response.size() == 1) {
			return response.get(0);
		}
		return null;
	}			
			
			

8.6.8. queryForRowSet

		
SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from test");  
		
		

8.6.9. update

		
	@RequestMapping(value="/comment/add/{siteId}/{articleId}", method = RequestMethod.POST)
	public ResponseRestful commentAdd(@PathVariable("siteId") int siteId, @PathVariable("articleId") int articleId, @RequestBody Comment comment) {
		String sql = "insert into cms.comment("
				+ "article_id, "
				+ "ctime,"
				+ "content,"
				+ "member_id,"
				+ "nickname,"
				+ "picture"
				+ ") values(?,?,now(),?,?,?,?,?)";

		int count = jdbcTemplate.update(sql, 
				comment.getArticleId(),
				comment.getContent(),
				comment.getMemberId(),
				comment.getNickname(),
				comment.getPicture()
		);
		
		return new ResponseRestful(true, count, "評論添加成功", comment);
	}		
		
		

8.6.10. 

		
new MapSqlParameterSource("symbol", symbol)		
		
		

8.6.11. 實例參考

8.6.11.1. 參數傳遞技巧

			
	public List<PendingTransaction> getPendingTransaction(String address, String contractAddress) {
		List<PendingTransaction> pendingTransactions = new ArrayList<PendingTransaction>();
		String sql;
		Object[] param;
		if (contractAddress == null || contractAddress.equals("")) {
			sql = "select * from pending_transaction where from_address = ? and contract_address IS NULL";
			param = new Object[] { address };
		} else {
			sql = "select * from pending_transaction where from_address = ? and contract_address = ?";
			param = new Object[] { address, contractAddress };
		}
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, param);

		for (Map<String, Object> row : rows) {
			PendingTransaction pendingTransaction = new PendingTransaction();
			pendingTransaction.setHash((String) row.get("hash"));
			pendingTransaction.setFrom((String) row.get("from_address"));
			pendingTransaction.setTo((String) row.get("to_address"));
			pendingTransaction.setValue((String) row.get("value"));
			pendingTransaction.setGas((String) row.get("gas"));
			pendingTransaction.setSymbol((String) row.get("symbol"));
			pendingTransaction.setContractAddress((String) row.get("contractAddress"));
			pendingTransactions.add(pendingTransaction);
		}
		logger.info("PendingTransaction:" + pendingTransactions.toString());
		return pendingTransactions;
	}