Spring jdbcTemplate uses placeholders (?) Multi-Table query

1. Configure service and dao in applicationContext. xml in the spring configuration file (flex is used in the foreground and flex is configured as well .)

[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>

<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. Write the service Interface

[Java]
/**
* Vehicle super capacitor Data Analysis service
* @ Author hanshibo
*/
Public interface BusSuperCapityAnalyService {
Public List <BusSuperCapityDataModel> queryBusSuperCapityData (String busno, String startTime, String endTime );
}

/**
* Vehicle super capacitor Data Analysis service
* @ Author hanshibo
*/
Public interface BusSuperCapityAnalyService {
Public List <BusSuperCapityDataModel> queryBusSuperCapityData (String busno, String startTime, String endTime );
} 3 Write service implementation

[Java]
/**
* Vehicle super capacitor analysis 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 );
}
 
}

/**
* Vehicle super capacitor analysis 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 Write dao layer interface

[Java]
/**
* Super capacitor data analysis Dao
* @ Author hanshibo
*
*/
Public interface BusSuperCapityAnalyDao {
 
Public List <BusSuperCapityDataModel> queryBusSuperCapityData (String busno, String startTime, String endTime );
}

/**
* Super capacitor data analysis Dao
* @ Author hanshibo
*
*/
Public interface BusSuperCapityAnalyDao {

Public List <BusSuperCapityDataModel> queryBusSuperCapityData (String busno, String startTime, String endTime );
} 5 Write the dao layer implementation. Use the query method of spring jdbcTemplate, and use a placeholder (?) Query.

[Java]
/**
* Vehicle super capacitor data analysis 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 Value
*/
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;
}
}
 
 
 
}

/**
* Vehicle super capacitor data analysis 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 Value
*/
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;
}
}

 

} Note the following:

[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 parameter. This is a String array. All parameters in this array must use the placeholder (?) Parameters (formal parameters ). Here there are three busno, startTime, and endTime parameters, all of which are in the SQL statement and the parameter conditions to be used after where.

2 types is an array of the int type. This array mainly corresponds to the parameter information in the params parameter. The types array mainly stores some parameter data types. Here is Types. VARCHAR, because the busno, startTime, and endTime parameters in the params parameter are strings.

3 new SuperCapityDataMapper. Use Mapper to get the value and store it in the Model object information.

4. Use jdbcTemplate. query (getcurSql (), params, types, new SuperCapityDataMapper (); to obtain a list.