关于JdbcTemplate的官方描述如下：
大约的讲，将JdbcTemplate返回的list结果集生成JavaBean我找到四种方式，都还不错。
本例比较特殊，该JavaBean里面包含一个child属性，该属性类型为List，见代码，而查询父子的SQL及需要的结果完全不同。
见代码
UserFuncEntity
package com.jpaandsecurity.entity; import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.annotation.JSONField;
import lombok.Getter;
import lombok.Setter; import javax.persistence.Column;
import java.io.Serializable;
import java.util.List; @Getter
@Setter
public class UserFuncEntity implements Serializable {
private static final long serialVersionUID = -816647127390476012L; @JSONField(ordinal = 1)
@Column(name = "RESPONSIBILITY_NAME")
private String name;
@JSONField(ordinal = 2)
private List<UserFuncEntity> child;
@JSONField(ordinal = 3)
private String controller;
@JSONField(ordinal = 4)
private String req_mapping;
@JSONField(ordinal = 5)
@Column(name = "RESPONSIBILITY_ID")
private String resp_id;
@JSONField(ordinal = 6)
private String responsibility_name;
@JSONField(ordinal = 7)
@Column(name = "RESPONSIBILITY_APPLICATION_ID")
private String resp_appl_id;
@JSONField(ordinal = 8)
@Column(name = "PROMPT")
private String functionPrompt;
@JSONField(ordinal = 9)
private String params; public UserFuncEntity(){
super();
} public UserFuncEntity(String name,List<UserFuncEntity> child,String controller,String req_mapping,String resp_id,String resp_appl_id,String params){
super();
this.name = name;
this.child = child;
this.controller = controller;
this.req_mapping = req_mapping;
this.resp_id = resp_id;
this.resp_appl_id = resp_appl_id;
this.params = params;
} public String getController() {
if(getReq_mapping()!=null && getResp_appl_id()!=null && getResp_id()!=null){ return "/init_responsiblity?req_mapping="+getReq_mapping()+"&resp_id="+getResp_id()+"&resp_appl_id="+getResp_appl_id()+"¶ms="+getParams();
}else{
return controller;
}
} @Override
public String toString(){
return JSON.toJSONString(this);
} }
UserResponEntity
package com.jpaandsecurity.entity; import lombok.Getter;
import lombok.Setter;
import org.springframework.jdbc.core.RowMapper; import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException; @Getter
@Setter
public class UserResponEntity implements RowMapper<UserResponEntity>, Serializable {
private String respId;
private String responsibilityApplicationId;
private String responsibilityName;
private String prompt; public UserResponEntity(){
super();
} public UserResponEntity(String responsibilityId,String responsibilityApplicationId,String responsibilityName,String prompt){
this.respId = responsibilityId;
this.responsibilityApplicationId = responsibilityApplicationId;
this.responsibilityName = responsibilityName;
this.prompt = prompt;
} @Override
public UserResponEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
UserResponEntity userResponEntity = new UserResponEntity();
userResponEntity.setRespId(rs.getString("responsibility_id"));
userResponEntity.setResponsibilityApplicationId(rs.getString("responsibility_application_id"));
userResponEntity.setResponsibilityName(rs.getString("responsibility_name"));
return userResponEntity;
}
}
SpringTest
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.jpaandsecurity.RunApplication;
import com.jpaandsecurity.entity.UserFuncEntity;
import com.jpaandsecurity.entity.UserResponEntity;
import lombok.extern.slf4j.Slf4j;
import org.apache.catalina.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import org.springframework.test.context.junit4.SpringRunner; import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.*; import static com.jpaandsecurity.entity.ReflectUtil.reflect; @Slf4j
@SpringBootTest(classes = {RunApplication.class})
@RunWith(SpringRunner.class)
@Component
public class QueryTest {
@PersistenceContext
private EntityManager em; @Autowired
private JdbcTemplate jdbcTemplate; @Test
public void testQuery(){ List<UserFuncEntity> menuList = new LinkedList<UserFuncEntity>();
String respId = null;
String respApplId = null;
String respName = null;
String name = null; long startTime = System.currentTimeMillis();
String sql = "SELECT furg.responsibility_id,\n" +
" furg.responsibility_application_id,\n" +
" frv.responsibility_name\n" +
" FROM fnd_user_resp_groups_all furg, fnd_responsibility_vl frv\n" +
" WHERE furg.user_id = ? \n"+
" AND furg.responsibility_id = frv.responsibility_id" +
" AND SYSDATE BETWEEN furg.start_date AND nvl(furg.end_date, SYSDATE + 1)";
String userId = String.valueOf(1131); String respFuncSql = "SELECT res.responsibility_name,\n" +
" menu.menu_name,\n" +
" menu.user_menu_name,\n" +
" func.function_name,\n" +
" func.user_function_name,\n" +
" sub_menu.prompt,\n" +
" func.type\n" +
" FROM fnd_responsibility_vl res,\n" +
" fnd_menus_vl menu,\n" +
" fnd_menu_entries_vl sub_menu,\n" +
" fnd_form_functions_vl func\n" +
" WHERE res.menu_id = sub_menu.menu_id\n" +
" AND menu.menu_id = sub_menu.menu_id\n" +
" AND sub_menu.function_id = func.function_id\n" +
" AND sub_menu.sub_menu_id IS NULL\n" +
" AND sub_menu.function_id IS NOT NULL\n" +
" AND sub_menu.prompt IS NOT NULL\n" +
" AND func.function_name NOT IN\n" +
" ('FNDCPSRSSSWA', 'FNDCPVIEWREQUEST', 'ICX_SSWA_USER_PREFERENCES')"+
" AND func.type = 'JSP'\n" +
" AND res.RESPONSIBILITY_ID = ? \n" +
"UNION \n" +
"SELECT res.responsibility_name,\n" +
" menu.menu_name,\n" +
" menu.user_menu_name,\n" +
" func.function_name,\n" +
" func.user_function_name,\n" +
" sub_menu1.prompt,\n" +
" func.type\n" +
" FROM fnd_responsibility_vl res,\n" +
" fnd_menus_vl menu,\n" +
" fnd_menu_entries_vl sub_menu,\n" +
" fnd_menu_entries_vl sub_menu1,\n" +
" fnd_form_functions_vl func\n" +
" WHERE res.menu_id = sub_menu.menu_id\n" +
" AND sub_menu.menu_id = menu.menu_id\n" +
" AND sub_menu.sub_menu_id = sub_menu1.menu_id\n" +
" AND sub_menu1.function_id = func.function_id\n" +
" AND sub_menu.sub_menu_id IS NOT NULL\n" +
" AND sub_menu.function_id IS NULL\n" +
" AND sub_menu1.prompt IS NOT NULL\n" +
" AND func.function_name NOT IN\n" +
" ('FNDCPSRSSSWA', 'FNDCPVIEWREQUEST', 'ICX_SSWA_USER_PREFERENCES')"+
" AND func.type = 'JSP'\n" +
" AND res.RESPONSIBILITY_ID = ?";
startTime = System.currentTimeMillis(); //jdbcTemplate中几个常用query的使用解释
//https://blog.csdn.net/hjm4702192/article/details/39997379 //方法1，普通，使用最原始的QueryForList
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql,new Object[]{ userId });
Iterator it = rows.iterator();
while(it.hasNext()) {
Map respMap = (Map) it.next();
respId = String.valueOf( respMap.get("responsibility_id".toUpperCase()));
respApplId = String.valueOf( respMap.get("responsibility_application_id".toUpperCase()));
respName = String.valueOf( respMap.get("responsibility_name".toUpperCase()));
name = respName;
if(respName.startsWith("B18")){
name = respName.substring(10);
}
UserFuncEntity userFuncEntity = new UserFuncEntity();
userFuncEntity.setResp_id(respId);
userFuncEntity.setResp_appl_id(respApplId);
userFuncEntity.setName(name); List<UserFuncEntity> childList = new ArrayList<>();
List funcRows = jdbcTemplate.queryForList(respFuncSql,new Object[]{respId, respId}); Iterator funcIt = funcRows.iterator();
//判断职责下是否有可用的功能
int i=0;
while (funcIt.hasNext()){
Map funcMap = (Map) funcIt.next();
name = String.valueOf(funcMap.get("prompt"));
UserFuncEntity funcEntity = new UserFuncEntity();
funcEntity.setResp_id(respId);
funcEntity.setResp_appl_id(respApplId);
funcEntity.setName(name);
if(i%2==0)
funcEntity.setReq_mapping("/test");
else
funcEntity.setReq_mapping("/echart");
childList.add(funcEntity);
i++;
}
if(i==0)
continue; userFuncEntity.setChild(childList); menuList.add(userFuncEntity);
} log.info("jdbcTemplate方法1消耗时长"+(System.currentTimeMillis()-startTime)+"rows.size "+rows.size()); //方法二，使用反射
//方法3和4不太适合本次两个SQL返回至同一个bean的情况
//https://blog.csdn.net/will_awoke/article/details/12617383
//https://blog.csdn.net/will_awoke/article/details/27683703
// List<UserFuncEntity> menuList = new LinkedList<UserFuncEntity>();
// menuList = new LinkedList<UserFuncEntity>();
startTime = System.currentTimeMillis();
menuList.clear();
for (Map<String, Object> jdbcMapResult : rows)
{
UserFuncEntity userRespon =reflect(UserFuncEntity.class, jdbcMapResult);
List<Map<String, Object>> responFuncMenu =jdbcTemplate.queryForList(respFuncSql,new Object[]{userRespon.getResp_id(),
userRespon.getResp_id()}); List<UserFuncEntity> respFuncList = new LinkedList<UserFuncEntity>();
int i=0;
for (Map<String, Object> respFuncListMap: responFuncMenu){
UserFuncEntity func = reflect(UserFuncEntity.class, respFuncListMap);
func.setResp_id(userRespon.getResp_id());
func.setResp_appl_id(userRespon.getResp_appl_id());
if(i%2==0)
func.setReq_mapping("/test");
else
func.setReq_mapping("/echart");
i++;
respFuncList.add(func);
}
userRespon.setChild(respFuncList);
//利用工具类反向生成bean
menuList.add(userRespon);
}
log.info("jdbcTemplate方法2消耗时长"+(System.currentTimeMillis()-startTime)+"rows.size "+rows.size());
log.info("jdbcTemplate方法2消耗时长 "+(System.currentTimeMillis()-startTime)+"\n"+JSON.toJSONString(menuList,SerializerFeature.PrettyFormat)); //方法三，继承RowMapper接口进行实现
//该方法已经不适合两个不同的SQL需要返回至同一个JavaBean的情况。
//https://blog.csdn.net/fengshizty/article/details/43309055
//https://blog.csdn.net/u011332918/article/details/45560117
startTime = System.currentTimeMillis();
List<UserResponEntity> list = jdbcTemplate.query(sql,new UserResponEntity(),new Object[]{userId}); log.info(JSON.toJSONString(list,SerializerFeature.PrettyFormat));
log.info(" list 长度"+list.size()+" "+(System.currentTimeMillis()-startTime)); log.info("jdbcTemplate方法3消耗时长 "+(System.currentTimeMillis()-startTime)+"\n"+JSON.toJSONString(menuList,SerializerFeature.PrettyFormat)); //方法四，使用BeanPropertyRowMapper,https://blog.csdn.net/u011332918/article/details/45560117
//https://blog.csdn.net/limenghua9112/article/details/45096437
//使用BeanPropertyRowMapper源码分析
// https://www.cnblogs.com/VergiLyn/p/6040963.html
//官方文档：https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/BeanPropertyRowMapper.html
//BeanPropertyRowMapper默认提供驼峰加下划线的规则将field和cloumn进行映射，并没有提供注解的方式实现映射，需要在SQL中写别名
List<UserResponEntity> beanList = jdbcTemplate.query(sql,new Object[]{userId},new BeanPropertyRowMapper<UserResponEntity>(UserResponEntity.class)); log.info(JSON.toJSONString(beanList,SerializerFeature.PrettyFormat));
log.info(" list 长度"+beanList.size()+" "+(System.currentTimeMillis()-startTime)); //此处使用NativeQuery进行查询
// startTime = System.currentTimeMillis();
// Query query = em.createNativeQuery(sql);
//与Hiberante不同,jpa query从位置1开始
// query.setParameter(1, userId);
// List<Object[]> queryList = query.getResultList();
// log.info("createNativeQuery消耗时长" + (System.currentTimeMillis() - startTime) + "queryList.size " + queryList.size()); } }