使用JdbcTemplate获取多表查询结果集并返回json数组

Spring中，JdbcTemplate的query方法的参数中实现RowMapper接口，可以将查询结果的每行映射为数据库表对应的EntityBean，并最终返回List<EntityBean>，这个属于基本运用，但实际应用往往是多表查询返回的结果集并非可以直接映射为表的实体类；另，jsp页面使用ajax返回结果为json字符串处理起来相对方便；下面记录一下sping JdbcTemplate多表查询结果处理为json字符串并在页面展现。

 

1，JdbcTemplate DAO

两表连接查询，获取结果集为List<Map>，方便处理为json数组

@Repository
public class DAOJdbcTemplate {
	private static Logger log =  Logger.getLogger(DAOJdbcTemplate.class.getName());
	
	private JdbcTemplate jdbctemplate;
	
	@Autowired
	public void setDataSource(DataSource dbcp2datasource){
		this.jdbctemplate = new JdbcTemplate(dbcp2datasource);
	} 
		
	public List getInfoList(String pubday, String title){//
		StringBuffer sql = new StringBuffer();
		List lst = null;

		sql.append("select t.title as title, t.pubday as pubday, u.name as name from info left join user on t.puber=u.id where 1=1 ");
		if(null != pubday && !"".equals(pubday)){
			sql.append("and t.pubday='"+ pubday +"' ");

		}
		if(null != title && !"".equals(title)){
			sql.append("and t.title like '%"+ title +"%' ");

		}
		sql.append("order by t.optime desc ");		
		
		lst = this.jdbctemplate.query(sql.toString(),new RowMapper<Map<String,String>>(){

			@Override
			public Map<String,String> mapRow(ResultSet rs, int index) throws SQLException {
				
				Map<String,String> mp = new HashMap<String,String>();
				mp.put("title", rs.getString("title"));
				mp.put("pubday", rs.getDate("pubday").toString());
				mp.put("name", rs.getString("name"));					
				return mp;
			}			
		});
		
		return lst;
		
	}

}

 2，Controller中处理结果返回json响应

	@ResponseBody
	@RequestMapping(path="/querybyparam",method={RequestMethod.POST})
	public void getlistbyparam(@RequestParam(name="pubday") String pubday, @RequestParam(name="title")  String title, 
			HttpServletRequest request, HttpServletResponse response){		
		
		try {			
			log.info("pubday: "+ pubday + ", title: "+ title);			
			List lst = commonimpl.getInfoList(pubday,title);			
			log.info(lst);			
			JSONArray jsn = JSONArray.fromObject(lst);			
			response.getWriter().print(jsn);			
			return;
			
		} catch (IOException e) {
			log.info(e.toString());
		}		
		
	}

 3，页面请求、响应处理、返回结果展现

	function query(){
		
		$.ajax({
			url:'info/querybyparam',
			dataType:'json',
			type:'post',
			cache:false,
			data:{'pubday':$('#tonggaoid').val(),'title':$('#tonggaotitle').val()},
			success:function(data){
				var html = '';
				$.each(data,function(index,elem){					
					html += '<tr>';
					html +='<td style="width:10%;font-weight: bold;text-align: center;">'+ (index+1) +'</td>';
					html += '<td style="width: 40%;text-align: center;">'+ elem.title +'</td>';
					html += '<td style="width: 25%;text-align: center;">'+ elem.pubday+'</td>';
					html += '<td style="width: 25%;text-align: center;">'+ elem.name +'</td>';
					html += '</tr>';					
				});
				$('#datatable').append(html);
			},
			error:function(XMLHttpRequest){
				alert(XMLHttpRequest.responseText);
			}
			
		});
		
	}