get占位符传多个参数_spring jdbcTemplate 使用占位符(?)的query方法进行多表查询 | 学步园...

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

2 写service接口

/**

* 车辆超级电容数据分析service

* @author hanshibo

*/

public interface BusSuperCapityAnalyService {

public List queryBusSuperCapityData(String busno ,String startTime,String endTime);

}

3 写service实现

/**

* 车辆超级电容分析ServiceImpl

* @author hanshibo

*

*/

public class BusSuperCapityAnalyServiceImpl implements BusSuperCapityAnalyService{

@Resource

private BusSuperCapityAnalyDao busSuperCapityAnalyDao;

public List queryBusSuperCapityData(String busno,String startTime ,String endTime) {

return busSuperCapityAnalyDao.queryBusSuperCapityData(busno, startTime, endTime);

}

}

4  写dao层接口

/**

* 超级电容数据分析Dao

* @author hanshibo

*

*/

public interface BusSuperCapityAnalyDao {

public List queryBusSuperCapityData(String busno,String startTime ,String endTime);

}

5 写dao层实现。使用spring jdbcTemplate 的query方法，用占位符(?)查询。

/**

* 车辆超级电容数据分析daoImpl

* @author hanshibo

*

*/

public class BusSuperCapityAnalyDaoImpl implements BusSuperCapityAnalyDao{

@Resource

private JdbcTemplate jdbcTemplate;

private String partParam ;

public List queryBusSuperCapityData(String busno,

String startTime, String endTime) {

List busList = null;

busList = new ArrayList();

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{

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;

}

}

}

如下注意：

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 。