关于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, " + " furg.responsibility_application_id, " + " frv.responsibility_name " + " FROM fnd_user_resp_groups_all furg, fnd_responsibility_vl frv " + " WHERE furg.user_id = ? "+ " 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, " + " menu.menu_name, " + " menu.user_menu_name, " + " func.function_name, " + " func.user_function_name, " + " sub_menu.prompt, " + " func.type " + " FROM fnd_responsibility_vl res, " + " fnd_menus_vl menu, " + " fnd_menu_entries_vl sub_menu, " + " fnd_form_functions_vl func " + " WHERE res.menu_id = sub_menu.menu_id " + " AND menu.menu_id = sub_menu.menu_id " + " AND sub_menu.function_id = func.function_id " + " AND sub_menu.sub_menu_id IS NULL " + " AND sub_menu.function_id IS NOT NULL " + " AND sub_menu.prompt IS NOT NULL " + " AND func.function_name NOT IN " + " ('FNDCPSRSSSWA', 'FNDCPVIEWREQUEST', 'ICX_SSWA_USER_PREFERENCES')"+ " AND func.type = 'JSP' " + " AND res.RESPONSIBILITY_ID = ? " + "UNION " + "SELECT res.responsibility_name, " + " menu.menu_name, " + " menu.user_menu_name, " + " func.function_name, " + " func.user_function_name, " + " sub_menu1.prompt, " + " func.type " + " FROM fnd_responsibility_vl res, " + " fnd_menus_vl menu, " + " fnd_menu_entries_vl sub_menu, " + " fnd_menu_entries_vl sub_menu1, " + " fnd_form_functions_vl func " + " WHERE res.menu_id = sub_menu.menu_id " + " AND sub_menu.menu_id = menu.menu_id " + " AND sub_menu.sub_menu_id = sub_menu1.menu_id " + " AND sub_menu1.function_id = func.function_id " + " AND sub_menu.sub_menu_id IS NOT NULL " + " AND sub_menu.function_id IS NULL " + " AND sub_menu1.prompt IS NOT NULL " + " AND func.function_name NOT IN " + " ('FNDCPSRSSSWA', 'FNDCPVIEWREQUEST', 'ICX_SSWA_USER_PREFERENCES')"+ " AND func.type = 'JSP' " + " 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)+" "+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)+" "+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()); } }