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

刺骨的言语ヽ痛彻心扉 2022-02-12 10:10 423阅读 0赞

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

2 写service接口

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

3 写service实现

/**
     * 车辆超级电容分析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层接口

/**
     * 超级电容数据分析Dao
     * @author hanshibo
     *
     */
    public interface BusSuperCapityAnalyDao {
    
    	public List<BusSuperCapityDataModel> 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<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;
    		}
    	}
    
    
    
    }

如下注意：

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 。