Spring使用JdbcTemplate实现对数据库操作

迷南。 2022-07-13 03:19 144阅读 0赞

spring对数据库的操作使用JdbcTemplate来封装JDBC，结合Spring的注入特性可以很方便的实现对数据库的访问操作。  
使用JdbcTemplate可以像JDBC一样来编写数据库的操作代码，与hibernate相比对SQL语句的控制上会更灵活，下面以一个例子来讲解JdbcTemplate的使用及相应的API。  
![这里写图片描述][SouthEast]  
一、实体Bean

package com.orm.dto;  
    
    import java.sql.Timestamp;  
    
    public class AreaDto implements java.io.Serializable {   
    
        private static final long serialVersionUID = 1L;  
        private Integer areaid;  
        private String area_name;  
        private String area_detail;  
        private Integer floor_id;  
        private Integer build_id;  
        private String region_name;  
        private String sortno;  
        private Timestamp insert_time;  
        private Timestamp update_time;  
        private Integer operate_id;  
    
        public AreaDto() {  
        }  
    
        public AreaDto(Integer areaid) {  
            this.areaid = areaid;  
        }  
    
        public Integer getAreaid() {  
            return areaid;  
        }  
    
        public void setAreaid(Integer areaid) {  
            this.areaid = areaid;  
        }  
    
        public String getArea_name() {  
            return area_name;  
        }  
    
        public void setArea_name(String area_name) {  
            this.area_name = area_name;  
        }  
    
        public String getArea_detail() {  
            return area_detail;  
        }  
    
        public void setArea_detail(String area_detail) {  
            this.area_detail = area_detail;  
        }  
    
        public Integer getFloor_id() {  
            return floor_id;  
        }  
    
        public void setFloor_id(Integer floor_id) {  
            this.floor_id = floor_id;  
        }  
    
        public Integer getBuild_id() {  
            return build_id;  
        }  
    
        public void setBuild_id(Integer build_id) {  
            this.build_id = build_id;  
        }  
    
        public String getRegion_name() {  
            return region_name;  
        }  
    
        public void setRegion_name(String region_name) {  
            this.region_name = region_name;  
        }  
    
        public String getSortno() {  
            return sortno;  
        }  
    
        public void setSortno(String sortno) {  
            this.sortno = sortno;  
        }  
    
        public Timestamp getInsert_time() {  
            return insert_time;  
        }  
    
        public void setInsert_time(Timestamp insert_time) {  
            this.insert_time = insert_time;  
        }  
    
        public Timestamp getUpdate_time() {  
            return update_time;  
        }  
    
        public void setUpdate_time(Timestamp update_time) {  
            this.update_time = update_time;  
        }  
    
        public Integer getOperate_id() {  
            return operate_id;  
        }  
    
        public void setOperate_id(Integer operate_id) {  
            this.operate_id = operate_id;  
        }  
    
    }

package com.orm.dto;  
    
    import java.sql.Timestamp;  
    
    public class BuildingDto implements java.io.Serializable {   
    
        private static final long serialVersionUID = 1L;  
        private Integer buildid;  
        private String buildname;  
        private String detail;  
        private String sortno;  
        private Timestamp insertTime;  
        private Timestamp updateTime;  
        private Integer operateId;  
    
        public BuildingDto() {  
        }  
    
        public BuildingDto(Integer buildid) {  
            this.buildid = buildid;  
        }  
    
        public BuildingDto(Integer buildid, String buildname, String detail, String sortno, Timestamp insertTime,  
                Timestamp updateTime, Integer operateId) {  
            this.buildid = buildid;  
            this.buildname = buildname;  
            this.detail = detail;  
            this.sortno = sortno;  
            this.insertTime = insertTime;  
            this.updateTime = updateTime;  
            this.operateId = operateId;  
        }  
    
        public Integer getBuildid() {  
            return this.buildid;  
        }  
    
        public void setBuildid(Integer buildid) {  
            this.buildid = buildid;  
        }  
    
        public String getBuildname() {  
            return this.buildname;  
        }  
    
        public void setBuildname(String buildname) {  
            this.buildname = buildname;  
        }  
    
        public String getDetail() {  
            return this.detail;  
        }  
    
        public void setDetail(String detail) {  
            this.detail = detail;  
        }  
    
        public String getSortno() {  
            return this.sortno;  
        }  
    
        public void setSortno(String sortno) {  
            this.sortno = sortno;  
        }  
    
        public Timestamp getInsertTime() {  
            return this.insertTime;  
        }  
    
        public void setInsertTime(Timestamp insertTime) {  
            this.insertTime = insertTime;  
        }  
    
        public Timestamp getUpdateTime() {  
            return this.updateTime;  
        }  
    
        public void setUpdateTime(Timestamp updateTime) {  
            this.updateTime = updateTime;  
        }  
    
        public Integer getOperateId() {  
            return this.operateId;  
        }  
    
        public void setOperateId(Integer operateId) {  
            this.operateId = operateId;  
        }  
    
    }

package com.orm.dto;  
    
    import java.sql.Timestamp;  
    
    public class FloorDto implements java.io.Serializable {   
    
        private static final long serialVersionUID = 1L;  
        private Integer floorid;  
        private String floorname;  
        private Integer floor;  
        private String detail;  
        private Integer buildId;  
        private String sortno;  
        private Timestamp insertTime;  
        private Timestamp updateTime;  
        private Integer operateId;  
    
        public FloorDto() {  
        }  
    
        public FloorDto(Integer floorid) {  
            this.floorid = floorid;  
        }  
    
        public Integer getFloorid() {  
            return this.floorid;  
        }  
    
        public void setFloorid(Integer floorid) {  
            this.floorid = floorid;  
        }  
    
        public String getFloorname() {  
            return this.floorname;  
        }  
    
        public void setFloorname(String floorname) {  
            this.floorname = floorname;  
        }  
    
        public String getDetail() {  
            return this.detail;  
        }  
    
        public void setDetail(String detail) {  
            this.detail = detail;  
        }  
    
        public Integer getBuildId() {  
            return this.buildId;  
        }  
    
        public void setBuildId(Integer buildId) {  
            this.buildId = buildId;  
        }  
    
        public String getSortno() {  
            return this.sortno;  
        }  
    
        public void setSortno(String sortno) {  
            this.sortno = sortno;  
        }  
    
        public Timestamp getInsertTime() {  
            return this.insertTime;  
        }  
    
        public void setInsertTime(Timestamp insertTime) {  
            this.insertTime = insertTime;  
        }  
    
        public Timestamp getUpdateTime() {  
            return this.updateTime;  
        }  
    
        public void setUpdateTime(Timestamp updateTime) {  
            this.updateTime = updateTime;  
        }  
    
        public Integer getOperateId() {  
            return this.operateId;  
        }  
    
        public void setOperateId(Integer operateId) {  
            this.operateId = operateId;  
        }  
    
        public Integer getFloor() {  
            return floor;  
        }  
    
        public void setFloor(Integer floor) {  
            this.floor = floor;  
        }  
    
    }

package com.orm.dto;  
    
    import java.sql.Timestamp;  
    
    public class StoreDto implements java.io.Serializable {   
    
        private static final long serialVersionUID = 1L;  
        private Integer id;  
        private String storename;  
        private String storenameen;  
        private String storeno;  
        private Integer build_Id;  
        private Integer floor_Id;  
        private Integer area_Id;  
        private Integer type_Id;  
        private Integer point_Id;  
        private String storeowner;  
        private String area;  
        private String remark;  
        private String status;  
        private String sortno;  
        private Timestamp inserttime;  
        private Timestamp updatetime;  
        private Integer operateid;  
    
        public StoreDto() {  
        }  
    
        public StoreDto(Integer id) {  
            this.id = id;  
        }  
    
        public Integer getId() {  
            return this.id;  
        }  
    
        public void setId(Integer id) {  
            this.id = id;  
        }  
    
        public String getStorename() {  
            return this.storename;  
        }  
    
        public void setStorename(String storename) {  
            this.storename = storename;  
        }  
    
        public String getStorenameen() {  
            return this.storenameen;  
        }  
    
        public void setStorenameen(String storenameen) {  
            this.storenameen = storenameen;  
        }  
    
        public String getStoreno() {  
            return this.storeno;  
        }  
    
        public void setStoreno(String storeno) {  
            this.storeno = storeno;  
        }  
    
        public Integer getBuild_Id() {  
            return this.build_Id;  
        }  
    
        public void setBuild_Id(Integer build_Id) {  
            this.build_Id = build_Id;  
        }  
    
        public Integer getFloor_Id() {  
            return this.floor_Id;  
        }  
    
        public void setFloor_Id(Integer floor_Id) {  
            this.floor_Id = floor_Id;  
        }  
    
        public Integer getArea_Id() {  
            return this.area_Id;  
        }  
    
        public void setArea_Id(Integer area_Id) {  
            this.area_Id = area_Id;  
        }  
    
        public Integer getType_Id() {  
            return this.type_Id;  
        }  
    
        public void setType_Id(Integer type_Id) {  
            this.type_Id = type_Id;  
        }  
    
        public Integer getPoint_Id() {  
            return this.point_Id;  
        }  
    
        public void setPoint_Id(Integer point_Id) {  
            this.point_Id = point_Id;  
        }  
    
        public String getStoreowner() {  
            return this.storeowner;  
        }  
    
        public void setStoreowner(String storeowner) {  
            this.storeowner = storeowner;  
        }  
    
        public String getArea() {  
            return this.area;  
        }  
    
        public void setArea(String area) {  
            this.area = area;  
        }  
    
        public String getRemark() {  
            return this.remark;  
        }  
    
        public void setRemark(String remark) {  
            this.remark = remark;  
        }  
    
        public String getStatus() {  
            return this.status;  
        }  
    
        public void setStatus(String status) {  
            this.status = status;  
        }  
    
        public String getSortno() {  
            return this.sortno;  
        }  
    
        public void setSortno(String sortno) {  
            this.sortno = sortno;  
        }  
    
        public Timestamp getInserttime() {  
            return this.inserttime;  
        }  
    
        public void setInserttime(Timestamp inserttime) {  
            this.inserttime = inserttime;  
        }  
    
        public Timestamp getUpdatetime() {  
            return this.updatetime;  
        }  
    
        public void setUpdatetime(Timestamp updatetime) {  
            this.updatetime = updatetime;  
        }  
    
        public Integer getOperateid() {  
            return this.operateid;  
        }  
    
        public void setOperateid(Integer operateid) {  
            this.operateid = operateid;  
        }  
    
    }

二、jdbc.properties

jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver  
    jdbc.url=jdbc:sqlserver://127.0.0.1:1433;databaseName=test 
    jdbc.username=sa  
    jdbc.password=admin@2013  
    jdbc.maxActive=50  
    jdbc.maxIdle=10  
    jdbc.maxWait=50  
    jdbc.defaultAutoCommit=true

三、Spring配置文件【beans.xml】

<?xml version="1.0" encoding="GB18030"?>  
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd" default-autowire="byName" default-lazy-init="true">  
          
        <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">  
            <property name="locations">  
                <list>  
                    <value>classpath*:config/*.properties</value>  
                </list>  
            </property>  
        </bean>  
          
        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
            <property name="driverClassName" value="${jdbc.driverClassName}"></property>  
            <property name="url" value="${jdbc.url}"></property>  
            <property name="username" value="${jdbc.username}"></property>  
            <property name="password" value="${jdbc.password}"></property>  
            <property name="maxActive" value="${jdbc.maxActive}"></property>  
            <property name="maxIdle" value="${jdbc.maxIdle}"></property>  
            <property name="maxWait" value="${jdbc.maxWait}"></property>  
            <property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"></property>  
        </bean>  
    
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" abstract="false" lazy-init="false" autowire="default" >  
            <property name="dataSource">  
                <ref bean="dataSource" />  
            </property>  
        </bean>  
    
        <bean id="ormDao" class="com.orm.dao.impl.OrmDaoImpl">  
           <property name="template">  
              <ref bean="jdbcTemplate" />  
           </property>  
        </bean>  
    </beans>

四、web.xml

<?xml version="1.0" encoding="UTF-8"?>  
    <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">  
          
        <context-param>  
            <param-name>contextConfigLocation</param-name>  
            <param-value>classpath*:spring/*.xml</param-value>  
        </context-param>  
          
        <listener>  
            <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>  
        </listener>  
          
        <filter>  
            <filter-name>encodingFilter</filter-name>  
            <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>  
            <init-param>  
                <param-name>encoding</param-name>  
                <param-value>GBK</param-value>  
            </init-param>  
        </filter>  
    
    
          
        <listener>  
            <listener-class>  
                org.springframework.web.util.IntrospectorCleanupListener</listener-class>  
        </listener>  
          
        <session-config>  
            <session-timeout>30</session-timeout>  
        </session-config>  
    </web-app>

五、数据库访问接口【OrmDao.java】

package com.orm.dao;  
    
    import java.util.List;  
    import java.util.Map;  
    
    import com.orm.dto.AreaDto;  
    import com.orm.dto.BuildingDto;  
    import com.orm.dto.FloorDto;  
    import com.orm.dto.StoreDto;  
    
    public interface OrmDao {   
        public List<AreaDto> getAreaDtos();  
    
        public List<AreaDto> getAreaDtos1();  
    
        public Map<String, Object> getBuilds();  
    
        public Map<String, Object> getFloors(String build);  
    
        public Map<String, Object> getAreas(String build, String floor);  
    
        public List<StoreDto> getStoreDtos(int buildid, int floorid, int areaid);  
    
        public List<BuildingDto> getBuildingDto();  
    
        public List<FloorDto> getFloorDtos(int buildid);  
    
        public List<AreaDto> getAreaDto(int buildid, int floorid);  
    
        public AreaDto getAreaDto(int id);  
    
        public BuildingDto getBuildingDto(int buildId);  
    
        public FloorDto getFloorDto(int floorId);  
    
        public List<StoreDto> getAllStores();  
    
        public String storeName(int storeId);  
    
        public StoreDto getStoreById(int storeId);  
    
        public int getCountStore();  
    
        public void saveBuild(BuildingDto buildingDto);  
    
        public void deleteBuildById(int buildid);  
    
        public void updateBuildById(BuildingDto buildingDto);  
    }

六、数据库访问接口的实现类【OrmDaoImpl.java】

package com.orm.dao.impl;  
    
    import java.sql.ResultSet;  
    import java.sql.SQLException;  
    import java.util.HashMap;  
    import java.util.LinkedHashMap;  
    import java.util.List;  
    import java.util.Map;  
    
    import org.springframework.jdbc.core.BeanPropertyRowMapper;  
    import org.springframework.jdbc.core.JdbcTemplate;  
    import org.springframework.jdbc.core.RowMapper;  
    
    import com.orm.dao.OrmDao;  
    import com.orm.dto.AreaDto;  
    import com.orm.dto.BuildingDto;  
    import com.orm.dto.FloorDto;  
    import com.orm.dto.StoreDto;  
    
    @SuppressWarnings({ "unchecked", "rawtypes" })  
    public class OrmDaoImpl implements OrmDao {   
        private JdbcTemplate template;  
    
        public JdbcTemplate getTemplate() {  
            return template;  
        }  
    
        public void setTemplate(JdbcTemplate template) {  
            this.template = template;  
        }  
    
        public List<AreaDto> getAreaDtos() {  
            String sql = "select * from t_area ";  
            return (List<AreaDto>) template.query(sql, new BeanPropertyRowMapper(AreaDto.class));  
        }  
    
        public List<AreaDto> getAreaDtos1() {  
            String sql = "select * from t_area ";  
            return (List<AreaDto>) template.query(sql, new AreaRowMapper());  
        }  
    
        public Map<String, Object> getBuilds() {  
            String sql = "select buildid,buildname from t_building ";  
            List<BuildingDto> list = this.template.query(sql, new BeanPropertyRowMapper(BuildingDto.class));  
            Map<String, Object> map = new HashMap<String, Object>();  
            for (BuildingDto build : list) {  
                map.put(String.valueOf(build.getBuildid()), build.getBuildname());  
            }  
            return map;  
        }  
    
        public Map<String, Object> getFloors(String build) {  
            String sql = "select floorid ,floorname from t_floor where build_id=?";  
            List<FloorDto> list = this.template.query(sql, new Object[] { build }, new BeanPropertyRowMapper(FloorDto.class));  
            Map<String, Object> map = new LinkedHashMap<String, Object>();  
            for (FloorDto floorDto : list) {  
                System.out.println(floorDto.getFloorid());  
                map.put(String.valueOf(floorDto.getFloorid()), floorDto.getFloorname());  
            }  
            return map;  
        }  
    
        public Map<String, Object> getAreas(String build, String floor) {  
            String sql = "select areaid ,area_name from t_area where build_id=? and floor_id=?";  
            List<AreaDto> list = this.template.query(sql, new Object[] { build, floor }, new BeanPropertyRowMapper(AreaDto.class));  
            Map<String, Object> map = new LinkedHashMap<String, Object>();  
            for (AreaDto areaDto : list) {  
                System.out.println(areaDto.getAreaid());  
                map.put(String.valueOf(areaDto.getAreaid()), areaDto.getArea_name());  
            }  
            return map;  
        }  
    
        public List<StoreDto> getStoreDtos(int buildid, int floorid, int areaid) {  
            String sql = "select * from t_store where build_id=? and floor_id=? and area_id=?";  
            return (List<StoreDto>) template.query(sql, new Object[] { buildid, floorid, areaid }, new BeanPropertyRowMapper(  
                    StoreDto.class));  
        }  
    
        public List<BuildingDto> getBuildingDto() {  
            String sql = "select * from t_building ";  
            return (List<BuildingDto>) template.query(sql, new BeanPropertyRowMapper(BuildingDto.class));  
        }  
    
        public List<FloorDto> getFloorDtos(int buildid) {  
            String sql = "select * from t_floor where build_id=? ";  
            return (List<FloorDto>) template.query(sql, new Object[] { buildid }, new BeanPropertyRowMapper(FloorDto.class));  
        }  
    
        public List<AreaDto> getAreaDto(int buildid, int floorid) {  
            String sql = "select * from t_area where build_id=? and floor_id=?";  
            return (List<AreaDto>) template.query(sql, new Object[] { buildid, floorid }, new BeanPropertyRowMapper(AreaDto.class));  
        }  
    
        public AreaDto getAreaDto(int id) {  
            String sql = "select * from t_area where areaid=?";  
            return template.queryForObject(sql, new Object[] { id }, new BeanPropertyRowMapper(AreaDto.class));  
        }  
    
        public BuildingDto getBuildingDto(int buildId) {  
            String sql = "select * from t_building where buildid=?";  
            return template.queryForObject(sql, new Object[] { buildId }, new BeanPropertyRowMapper(BuildingDto.class));  
        }  
    
        public FloorDto getFloorDto(int floorId) {  
            String sql = "select * from t_floor where floorid=?";  
            return template.queryForObject(sql, new Object[] { floorId }, new BeanPropertyRowMapper(FloorDto.class));  
        }  
    
        public List<StoreDto> getAllStores() {  
            String sql = "select * from t_store ";  
            return (List<StoreDto>) template.query(sql, new BeanPropertyRowMapper(StoreDto.class));  
        }  
    
        public String storeName(int storeId) {  
            String sql = "select storename from t_store where id=?";  
            return template.queryForObject(sql, new Object[] { storeId }, String.class);  
        }  
    
        public StoreDto getStoreById(int storeInt) {  
            String sql = "select * from t_store where id=?";  
            return template.queryForObject(sql, new Object[] { storeInt }, new BeanPropertyRowMapper(StoreDto.class));  
        }  
    
        public int getCountStore() {  
            String sql = "select count(id) from t_store";  
            return this.template.queryForInt(sql);  
        }  
    
        public void saveBuild(BuildingDto buildingDto) {  
        }  
    
        public void deleteBuildById(int buildid) {  
            String sql = "delete from t_store where id=?";  
            this.template.update(sql, buildid);  
        }  
    
        public void updateBuildById(BuildingDto buildingDto) {  
    
        }  
    }  
    
    class AreaRowMapper implements RowMapper {  
        public Object mapRow(ResultSet rs, int index) throws SQLException {  
            AreaDto areaDto = new AreaDto();  
            areaDto.setAreaid(rs.getInt("areaid"));  
            areaDto.setArea_name(rs.getString("area_name"));  
            areaDto.setArea_detail(rs.getString("area_detail"));  
            areaDto.setBuild_id(rs.getInt("build_id"));  
            areaDto.setFloor_id(rs.getInt("floor_id"));  
            areaDto.setInsert_time(rs.getTimestamp("insert_time"));  
            areaDto.setOperate_id(rs.getInt("operate_id"));  
            areaDto.setUpdate_time(rs.getTimestamp("update_time"));  
            areaDto.setRegion_name(rs.getString("region_name"));  
            areaDto.setSortno(rs.getString("sortno"));  
            return areaDto;  
        }  
    }

七、测试类

package com.orm.test; 
    
    import java.util.Iterator; 
    import java.util.List; 
    import java.util.Map; 
    import java.util.Map.Entry; 
    import java.util.Set; 
    
    import org.junit.Before; 
    import org.junit.Test; 
    import org.springframework.context.ApplicationContext; 
    import org.springframework.context.support.ClassPathXmlApplicationContext; 
    
    import com.orm.dao.OrmDao; 
    import com.orm.dto.AreaDto; 
    
    public class AreaTest{  
        private ApplicationContext ctx = null; 
        private OrmDao ormDao = null; 
        @Before  
        public void setUp() throws Exception {  
            ctx = new ClassPathXmlApplicationContext("classpath:spring/beans.xml"); 
            ormDao = (OrmDao) ctx.getBean("ormDao"); 
        }  
        @Test  
        public void getAreaDtosTest(){  
            List<AreaDto> list = this.ormDao.getAreaDtos(); 
            for(AreaDto areaDto : list){  
                System.out.println(areaDto.getArea_name()); 
            }  
            System.out.println(list.size()); 
        }  
        @Test  
        public void getAreaDtosTest1(){  
            List<AreaDto> list = this.ormDao.getAreaDtos1(); 
            for(AreaDto areaDto : list){  
                System.out.println(areaDto.getArea_name()); 
            }  
            System.out.println(list.size()); 
        }  
        @Test  
        public void getBuildsTest(){  
            Map<String, Object> builds = this.ormDao.getBuilds(); 
            Iterator<String> keys = builds.keySet().iterator(); 
            String key = ""; 
            while(keys.hasNext()){  
                key = keys.next(); 
                System.out.println("key = "+key+",value = "+builds.get(key)); 
            }  
        }  
        @Test  
        public void getFloorsTest(){  
            Map<String, Object> floors = this.ormDao.getFloors("1"); 
            System.out.println(floors); 
            Set<Entry<String, Object>> floorSet = floors.entrySet(); 
            for(Entry floor : floorSet){  
                System.out.println(floor.getKey()+","+floor.getValue()); 
            }  
        }  
        @Test  
        public void getAreasTest(){  
            Map<String, Object> areas = this.ormDao.getAreas("1","1"); 
            System.out.println(areas); 
            Set<Entry<String, Object>> areaSet = areas.entrySet(); 
            for(Entry area : areaSet){  
                System.out.println(area.getKey()+","+area.getValue()); 
            }  
        }  
        @Test  
        public void getCountStoreTest(){  
            System.out.println(this.ormDao.getCountStore()); 
        }  
    }

说明：  
JdbcTemplate提供的方法很多如：queryForXXX，可以将查询结果以int、long、Object、List、Map来返回，这里有几个需要注意的：  
下面是针对老版本的Spring1.2.x的JdbcTemplate操作：使用RowMapperResultReader对象来处理

class UserRowMapper implements RowMapper {   
        public Object mapRow(ResultSet rs, int index) throws SQLException {  
            User user = new User();  
    
            user.setId(rs.getString("user_id"));  
            user.setName(rs.getString("name"));  
            user.setSex(rs.getString("sex").charAt(0));  
            user.setAge(rs.getInt("age"));  
    
            return user;  
        }  
    }  
    
    public List findAllByRowMapperResultReader() {  
        String sql = "SELECT * FROM USER";  
        return jdbcTemplate.query(sql, new RowMapperResultReader(new UserRowMapper()));  
    }

但是在Spring2及以上的版本中却没有RowMapperResultReader这个对象，所以直接传替封装类来使用：

public List<AreaDto> getAreaDtos1() {  
            String sql = "select * from t_area ";  
            return (List<AreaDto>) template.query(sql, new AreaRowMapper());  
        }  
    class AreaRowMapper implements RowMapper {   
        public Object mapRow(ResultSet rs, int index) throws SQLException {  
            AreaDto areaDto = new AreaDto();  
            areaDto.setAreaid(rs.getInt("areaid"));  
            areaDto.setArea_name(rs.getString("area_name"));  
            areaDto.setArea_detail(rs.getString("area_detail"));  
            areaDto.setBuild_id(rs.getInt("build_id"));  
            areaDto.setFloor_id(rs.getInt("floor_id"));  
            areaDto.setInsert_time(rs.getTimestamp("insert_time"));  
            areaDto.setOperate_id(rs.getInt("operate_id"));  
            areaDto.setUpdate_time(rs.getTimestamp("update_time"));  
            areaDto.setRegion_name(rs.getString("region_name"));  
            areaDto.setSortno(rs.getString("sortno"));  
            return areaDto;  
        }  
    }

queryForList方法  
此方法返回List数据，但是注意List中的数据却是Map形式，类似：\[\{AREAID=1, AREA\_NAME=1楼报名咨询区\}, \{AREAID=2, AREA\_NAME=2楼教学区\}, \{AREAID=3, AREA\_NAME=3楼课外辅导区\}\]，其中字段名为key，字段值为value

queryForMap方法  
返回Map类型的数据，数据格式为\{AREAID=1, AREA\_NAME=1楼报名咨询区\}，注意：此方法必须只能返回一条记录，如果查询有多条记录就会报错：“org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2”

如果需要查询数据库返回一个List类型的对象该如何实现？  
目前有两种形式：  
第一种：

第二种：

public List<AreaDto> getAreaDtos() {  
            String sql = "select * from t_area ";  
            System.out.println(template.queryForList(sql));  
            return (List<AreaDto>) template.query(sql, new BeanPropertyRowMapper(AreaDto.class));  
        }

很显然采用第二种会更简单。

[SouthEast]: /images/20220713/a4cf42de39ba48b9a606fbfc2f40c476.png