这是一个查询两站点之间列车信息的方法
public String executeQuery(String startStation, String endStation) {
this.openConnection();
JSONArray jsonArray_StationToStation = new JSONArray();
ResultSet rs = null;
String strResults = new String(); try {
String sql = null;
Statement stmt = conn.createStatement();
sql = "select A.trainNum, A.type, A.arriveTime,A.startDriveTime,ABS(sum(B.km-A.km)) " +
"from train_091203 " +
"A Join (select * from train_091203 where station='%" + startStation + "%')B" +
" On A.trainNum=B.trainNum where A.station = '%" + endStation + "%' Group by A.trainNum";
System.out.println("startStation = " + startStation);
System.out.println("endStation = " + endStation); rs = stmt.executeQuery(sql);
while(rs.next()) {
JSONObject jsonTrainNumInfo = new JSONObject();
jsonTrainNumInfo.put("trainNum", rs.getString("trainNum"));
jsonTrainNumInfo.put("type", rs.getString("type"));
jsonTrainNumInfo.put("arriveTime", rs.getString("arriveTime"));
jsonTrainNumInfo.put("startDriveTime", rs.getString("startDriveTime"));
jsonTrainNumInfo.put("km", rs.getString("ABS(sum(B.km-A.km))"));
jsonArray_StationToStation.add(jsonTrainNumInfo);
}
strResults = jsonArray_StationToStation.toString();
} catch(Exception e) {
e.printStackTrace();
}
if(strResults.length()==0) {
strResults = "暂无此数据!";
}
return strResults;
}
感觉问题出在神奇的sql语句上,
sql = "select A.trainNum, A.type, A.arriveTime,A.startDriveTime,ABS(sum(B.km-A.km)) " +
"from train_091203 " +
"A Join (select * from train_091203 where station='%" + startStation + "%')B" +
" On A.trainNum=B.trainNum where A.station = '%" + endStation + "%' Group by A.trainNum";
现象:当调用这个方法时,比如传递进去的方法参数是(“北京”,“上海”),竟然查询结果为空,一开始以为sql语句错了,后来把“北京”和“上海”直接放在sql语句中,即:
sql = "select A.trainNum, A.type, A.arriveTime,A.startDriveTime,ABS(sum(B.km-A.km)) " +
"from train_091203 " +
"A Join (select * from train_091203 where station='北京')B" +
" On A.trainNum=B.trainNum where A.station = '上海' Group by A.trainNum";
这时,竟然又能查询出结果,比较郁闷,请高人分析一下,多谢
public String executeQuery(String startStation, String endStation) {
this.openConnection();
JSONArray jsonArray_StationToStation = new JSONArray();
ResultSet rs = null;
String strResults = new String(); try {
String sql = null;
Statement stmt = conn.createStatement();
sql = "select A.trainNum, A.type, A.arriveTime,A.startDriveTime,ABS(sum(B.km-A.km)) " +
"from train_091203 " +
"A Join (select * from train_091203 where station='%" + startStation + "%')B" +
" On A.trainNum=B.trainNum where A.station = '%" + endStation + "%' Group by A.trainNum";
System.out.println("startStation = " + startStation);
System.out.println("endStation = " + endStation); rs = stmt.executeQuery(sql);
while(rs.next()) {
JSONObject jsonTrainNumInfo = new JSONObject();
jsonTrainNumInfo.put("trainNum", rs.getString("trainNum"));
jsonTrainNumInfo.put("type", rs.getString("type"));
jsonTrainNumInfo.put("arriveTime", rs.getString("arriveTime"));
jsonTrainNumInfo.put("startDriveTime", rs.getString("startDriveTime"));
jsonTrainNumInfo.put("km", rs.getString("ABS(sum(B.km-A.km))"));
jsonArray_StationToStation.add(jsonTrainNumInfo);
}
strResults = jsonArray_StationToStation.toString();
} catch(Exception e) {
e.printStackTrace();
}
if(strResults.length()==0) {
strResults = "暂无此数据!";
}
return strResults;
}
感觉问题出在神奇的sql语句上,
sql = "select A.trainNum, A.type, A.arriveTime,A.startDriveTime,ABS(sum(B.km-A.km)) " +
"from train_091203 " +
"A Join (select * from train_091203 where station='%" + startStation + "%')B" +
" On A.trainNum=B.trainNum where A.station = '%" + endStation + "%' Group by A.trainNum";
现象:当调用这个方法时,比如传递进去的方法参数是(“北京”,“上海”),竟然查询结果为空,一开始以为sql语句错了,后来把“北京”和“上海”直接放在sql语句中,即:
sql = "select A.trainNum, A.type, A.arriveTime,A.startDriveTime,ABS(sum(B.km-A.km)) " +
"from train_091203 " +
"A Join (select * from train_091203 where station='北京')B" +
" On A.trainNum=B.trainNum where A.station = '上海' Group by A.trainNum";
这时,竟然又能查询出结果,比较郁闷,请高人分析一下,多谢
因为是 等号 不是like
station like '%" + startStation + "%')
上面为你程序的片段。
while(rs.next()) {
JSONObject jsonTrainNumInfo = new JSONObject();
jsonTrainNumInfo.put("trainNum", rs.getString("trainNum"));
jsonTrainNumInfo.put("type", rs.getString("type"));
jsonTrainNumInfo.put("arriveTime", rs.getString("arriveTime"));
jsonTrainNumInfo.put("startDriveTime", rs.getString("startDriveTime"));
jsonTrainNumInfo.put("km", rs.getString("ABS(sum(B.km-A.km))"));
jsonArray_StationToStation.add(jsonTrainNumInfo);
}