spring jdbcTemplate 使用占位符(?)的query方法进行多表

当前位置 : 首页 > 网页制作 > CSS > spring jdbcTemplate 使用占位符(?)的query方法进行多表

spring jdbcTemplate 使用占位符(?)的query方法进行多表

来源: 作者: 时间:2016-02-16 10:04
1 在spring 的配置文件中applicationContext.xml中,配置service,dao.(前台使用的是flex,把flex也配置上了。)[html]bean id=busSuperCapityAnalyDao class=com.tm.dao.impl.sjwj.BusSuperCapityAnalyDao...

1 在spring 的配置文件中applicationContext.xml中,配置service,dao.(前台使用的是flex,把flex也配置上了。)

[]
<bean id="busSuperCapityAnalyDao" class="com.tm.dao.impl.sjwj.BusSuperCapityAnalyDaoImpl"></bean> 
 <bean id="busSuperCapityAnalyService" class="com.tm.service.impl.sjwj.BusSuperCapityAnalyServiceImpl"> 
  <flex:remoting-destination /> 
 </bean> 

<bean id="busSuperCapityAnalyDao" class="com.tm.dao.impl.sjwj.BusSuperCapityAnalyDaoImpl"></bean>
 <bean id="busSuperCapityAnalyService" class="com.tm.service.impl.sjwj.BusSuperCapityAnalyServiceImpl">
  <flex:remoting-destination />
 </bean>

2 写service接口

[java]
/**
 * 车辆超级电容数据分析service
 * @author hanshibo
 */ 
public interface BusSuperCapityAnalyService { 
    public List<BusSuperCapityDataModel>  queryBusSuperCapityData(String busno ,String startTime,String endTime); 

/**
 * 车辆超级电容数据分析service
 * @author hanshibo
 */
public interface BusSuperCapityAnalyService {
 public List<BusSuperCapityDataModel>  queryBusSuperCapityData(String busno ,String startTime,String endTime);
}3 写service实现

[java]
/**
 * 车辆超级电容分析ServiceImpl
 * @author hanshibo
 *
 */ 
public class BusSuperCapityAnalyServiceImpl implements BusSuperCapityAnalyService{ 
    @Resource  
    private BusSuperCapityAnalyDao busSuperCapityAnalyDao; 
    public List<BusSuperCapityDataModel> queryBusSuperCapityData(String busno,String startTime ,String endTime) { 
         
        return busSuperCapityAnalyDao.queryBusSuperCapityData(busno, startTime, endTime); 
    } 
 

/**
 * 车辆超级电容分析ServiceImpl
 * @author hanshibo
 *
 */
public class BusSuperCapityAnalyServiceImpl implements BusSuperCapityAnalyService{
 @Resource
 private BusSuperCapityAnalyDao busSuperCapityAnalyDao;
 public List<BusSuperCapityDataModel> queryBusSuperCapityData(String busno,String startTime ,String endTime) {
  
  return busSuperCapityAnalyDao.queryBusSuperCapityData(busno, startTime, endTime);
 }

}4  写dao层接口

[java]
/**
 * 超级电容数据分析Dao
 * @author hanshibo
 *
 */ 
public interface BusSuperCapityAnalyDao { 
 
    public List<BusSuperCapityDataModel> queryBusSuperCapityData(String busno,String startTime ,String endTime); 

/**
 * 超级电容数据分析Dao
 * @author hanshibo
 *
 */
public interface BusSuperCapityAnalyDao {

 public List<BusSuperCapityDataModel> queryBusSuperCapityData(String busno,String startTime ,String endTime);
}5 写dao层实现。使用spring jdbcTemplate 的query方法,用占位符(?)查询。

[java]
/**
 * 车辆超级电容数据分析daoImpl
 * @author hanshibo
 *
 */ 
public class BusSuperCapityAnalyDaoImpl implements BusSuperCapityAnalyDao{ 
         @Resource 
    private JdbcTemplate jdbcTemplate; 
     
    private String partParam ; 
 
public List<BusSuperCapityDataModel> queryBusSuperCapityData(String busno, 
            String startTime, String endTime) { 
    List<BusSuperCapityDataModel> busList = null; 
         
    busList = new ArrayList<BusSuperCapityDataModel>(); 
         String params[]=new String[]{busno,startTime,endTime};  
         int[] types = new int[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR};   
         busList=jdbcTemplate.query(getcurSql(),params,types, new SuperCapityDataMapper()); 
         return busList; 

 
private String getcurSql(){ 
String sqlStr=" select t.bus_job_no ,to_char(l.upload_time, 'YYYY-MM-DD HH24:MI:SS')      uploadTime,l.SINGLECAPAMAXVOL,l.CAPAMAXVOLTAGENO,l.SINGLECAPAMINVOL,l.CAPAMINVOLTAGENO,l.SINGLECAPAMAXTEM,l.SINGLECAPAMAXTEMNO,l.SINGLECAPAMINTEM, l.SINGLECAPAMINTEMNO" + 
" from tm_engine_basic_log l,tm_newenergy_bus_info t"+ 
" where t.bus_no =?"+ 
" and   l.upload_time between to_date(?, 'yyyy-mm-dd hh24:mi:ss') " + 
" and   to_date(?, 'yyyy-mm-dd hh24:mi:ss')" + 
" and l.bus_no = t.bus_no"; 
    return sqlStr ;  

 
        /**
     *RowMapper 取值
     */ 
    class SuperCapityDataMapper implements RowMapper<BusSuperCapityDataModel>{ 
 
        public BusSuperCapityDataModel mapRow(ResultSet rs, int rowID) 
                throws SQLException { 
            BusSuperCapityDataModel  bm = new BusSuperCapityDataModel(); 
            bm.setBusjobno(rs.getString("bus_job_no")); 
            bm.setUploadTime(rs.getString("uploadTime")); 
            bm.setCapaMaxVolTageNo(rs.getInt("CAPAMAXVOLTAGENO")); 
            bm.setCapaminVolTageNo(rs.getInt("CAPAMINVOLTAGENO")); 
            bm.setSingleCapaMaxtem(rs.getInt("SINGLECAPAMAXTEM")); 
            bm.setSingleCapaMaxVol(rs.getInt("SINGLECAPAMAXVOL")); 
            bm.setSingleCapaMinTemNo(rs.getInt("SINGLECAPAMINTEMNO")); 
            bm.setSingleCapaMinVol(rs.getInt("SINGLECAPAMINVOL")); 
            bm.setSingleCpaMaxTemNo(rs.getInt("SINGLECAPAMAXTEMNO")); 
            bm.setSingleCpaMinTem(rs.getInt("SINGLECAPAMINTEM")); 
            return bm; 
        } 
    } 
 
 
 

/**
 * 车辆超级电容数据分析daoImpl
 * @author hanshibo
 *
 */
public class BusSuperCapityAnalyDaoImpl implements BusSuperCapityAnalyDao{
         @Resource
 private JdbcTemplate jdbcTemplate;
 
 private String partParam ;

public List<BusSuperCapityDataModel> queryBusSuperCapityData(String busno,
   String startTime, String endTime) {
 List<BusSuperCapityDataModel> busList = null;
  
 busList = new ArrayList<BusSuperCapityDataModel>();
         String params[]=new String[]{busno,startTime,endTime};
         int[] types = new int[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR}; 
         busList=jdbcTemplate.query(getcurSql(),params,types, new SuperCapityDataMapper());
         return busList;
}

private String getcurSql(){
String sqlStr=" select t.bus_job_no ,to_char(l.upload_time, 'YYYY-MM-DD HH24:MI:SS')      uploadTime,l.SINGLECAPAMAXVOL,l.CAPAMAXVOLTAGENO,l.SINGLECAPAMINVOL,l.CAPAMINVOLTAGENO,l.SINGLECAPAMAXTEM,l.SINGLECAPAMAXTEMNO,l.SINGLECAPAMINTEM, l.SINGLECAPAMINTEMNO" +
" from tm_engine_basic_log l,tm_newenergy_bus_info t"+
" where t.bus_no =?"+
" and   l.upload_time between to_date(?, 'yyyy-mm-dd hh24:mi:ss') " +
" and   to_date(?, 'yyyy-mm-dd hh24:mi:ss')" +
" and l.bus_no = t.bus_no";
 return sqlStr ;
}

        /**
  *RowMapper 取值
  */
 class SuperCapityDataMapper implements RowMapper<BusSuperCapityDataModel>{

  public BusSuperCapityDataModel mapRow(ResultSet rs, int rowID)
    throws SQLException {
   BusSuperCapityDataModel  bm = new BusSuperCapityDataModel();
   bm.setBusjobno(rs.getString("bus_job_no"));
   bm.setUploadTime(rs.getString("uploadTime"));
   bm.setCapaMaxVolTageNo(rs.getInt("CAPAMAXVOLTAGENO"));
   bm.setCapaminVolTageNo(rs.getInt("CAPAMINVOLTAGENO"));
   bm.setSingleCapaMaxtem(rs.getInt("SINGLECAPAMAXTEM"));
   bm.setSingleCapaMaxVol(rs.getInt("SINGLECAPAMAXVOL"));
   bm.setSingleCapaMinTemNo(rs.getInt("SINGLECAPAMINTEMNO"));
   bm.setSingleCapaMinVol(rs.getInt("SINGLECAPAMINVOL"));
   bm.setSingleCpaMaxTemNo(rs.getInt("SINGLECAPAMAXTEMNO"));
   bm.setSingleCpaMinTem(rs.getInt("SINGLECAPAMINTEM"));
   return bm;
  }
 }

 

}如下注意:

[java]
String params[]=new String[]{busno,startTime,endTime};    
int[] types = new int[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR};   
busList=jdbcTemplate.query(getcurSql(),params,types, new SuperCapityDataMapper()); 

String params[]=new String[]{busno,startTime,endTime};  
int[] types = new int[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR}; 
busList=jdbcTemplate.query(getcurSql(),params,types, new SuperCapityDataMapper());1     params参数,这是一个String 数组,这个数组里面的参数,都是要使用占用符(?)的传进来的参数(形式参数)。这里就三个busno,startTime,endTime,这3个参数,都是sql语句里面,where后面要使用的参数条件。

2    types是一个int 类型的数组,这个数组主要对应params参数里面的参数信息。types数组主要存放一些参数数据类型。这里是Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,因为params参数里面busno,startTime,endTime 这三个参数,都是字符串。

3    new SuperCapityDataMapper 。使用Mapper取到值,并且存到Model实体信息里面。

4    使用jdbcTemplate.query(getcurSql(),params,types, new SuperCapityDataMapper());  查询得到一个list 。

 

Tag:
上一篇:CSS使用
下一篇:maven jetty plugin
网友评论

<