Spring的jdbcTemplate使用方法

1、java查询数据库数据的方法，有mybatis和hibernate这样的框架，也可以的使用spring的sqlsession和jdbcTemplate框架

下面介绍jdbcTemplate的使用：

1、spring配置文件的配置实例

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource">
			<ref local="dataSource" />
		</property>
	</bean>
	<!-- 事务管理器配置, 使用jdbc事务 -->
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>

从代码我可以看到，它依赖的数据源还是datasource，c3p0的数据源接口等，这就是调取数据库的api

2、java端注解的使用

@Resource(name = "jdbcTemplate")
protected JdbcTemplate jdbcTemplate;

然后就可以使用jdbc接口查询和操作数据库了

3、常用的语句和结构

1、多条件的查询，还有就是返回的数据中直接使用实体类进行接收

   String SQL="select a.id,a.url,a.diaoyu_type,a.time,a.msisdn,a.operater,a.status,a.name,a.id_numb,a.city, a.street, a.longitude, a.latitude,b.count from (select url,count(url) as count from user_info_view GROUP BY url) b,(SELECT * FROM  user_info_view WHERE id IN ( SELECT  MIN(id)  FROM    user_info_view  GROUP BY url ) )a where a.url=b.url";
	    	   String sql=null;
	    	   RowMapper<UserInfoView> rowMapper = new BeanPropertyRowMapper<UserInfoView>(UserInfoView.class);    	   
	    	   if(!StringUtils.isEmpty(searchForm.getUrl())){
	    		   SQL+="  and url="+"'"+searchForm.getUrl()+"'";
	    	   }
	    	   if(!StringUtils.isEmpty(searchForm.getDiaoYuType())){
	    		   SQL+=" and diaoyu_type="+"'"+searchForm.getDiaoYuType()+"'";
	    	   }
	    	   if(!StringUtils.isEmpty(searchForm.getTimeEnd())){
	    		   int dateEnd=stringToTimeStamp(searchForm.getTimeEnd());
	    		   SQL+=" and time<="+"'"+dateEnd+"'";	    		  
	    	   }
	    	   if(!StringUtils.isEmpty(searchForm.getTimeStart())){
	    		   int dateStart=stringToTimeStamp(searchForm.getTimeStart());
	    		   SQL+=" and time>="+"'"+dateStart+"'";
	    	   }	    	
	    	   sql=SQL+" limit "+ start+','+pagesize;
	    	   result=jdbcTemplate.query(sql, rowMapper);

2、查询条件的动态配置

        String sql = "select p.id, p.login,p.password,p.name,p.email,p.tel,g.name,p.create_time,p.state,p.withdraw_time from oper_person_info as p "  
                + "left join oper_group_info as g on p.oper_group_id = g.id   where 1 = 1 ";  
        List <Object> queryList=new  ArrayList<Object>();  
        if (!name.equals("")) {  
                       sql += " and p.name like ? ";  
            queryList.add("%" + name + "%");  
        }  
        if (!oper_group_id.equals("")) {  
            sql += " and p.oper_group_id = ? ";  
            queryList.add(oper_group_id）;  
        }  
        if (state == 1 || state == 0) {  
            sql += " and p.state = ? ";  
            queryList.add(state);  
        }  
          
        JdbcTemplate().query(sql, queryList.toArray(),  
                new OperatorSimpleMapper());