spring jdbcTemplate 使用占位符(?)的query方法进行多表-自学php网

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

来源：自学PHP网时间：2015-04-14 14:51 作者：阅读:次

[导读] 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也配置上了。)

[html]
<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 。

CSS使用 - html/css语言栏目：html.css - 自学php

maven jetty plugin - html/css语言栏目：html.css - 自学

子栏目

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

最新评论

添加评论

更多文章推荐

添加评论