private List<Station> getDataSet(String startTime, String endTime) {
List<Station> listStation = new ArrayList<Station>();
try {
// Connecting the database
Connection cn = DB.getConn();
System.out.println("Searching..");
String sql =
"select AVG(nbBikes) as vc,BIXI.station_id,BIXI_STATIONS.station_name,BIXI.datetime from BIXI INNER JOIN BIXI_STATIONS ON BIXI.station_id = BIXI_STATIONS.station_id WHERE BIXI.datetime >= timestamp('"
+ startTime
+ "') and BIXI.datetime <= timestamp('"
+ endTime
+ "')GROUP BY station_name order by vc desc limit 10";
PreparedStatement ps = cn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
System.out.println("End");
while (rs.next()) {
Station station = new Station();
station.setVc(rs.getInt(1));
station.setStation_id(rs.getInt(2));
station.setStation_name(rs.getString(3));
station.setDatatime(new java.util.Date(rs.getDate(4).getTime()));
listStation.add(station);
}
} catch (Exception e) {
e.printStackTrace();
}
return listStation;
}
/**
* 查询申成知道提问
*
* @param question
* @return
*/
public static List<CloudKnowAsk> queryCloudKnowAsksByQuestion(String question) throws Exception {
List<CloudKnowAsk> list = new ArrayList<CloudKnowAsk>();
String sql =
"SELECT id,user_id,question,description,type,urgent,state,"
+ "create_date,create_time,create_ip,update_date,update_time,update_ip FROM"
+ " cloud_know_ask WHERE question like '%"
+ question
+ "%' ORDER BY id DESC"; // AND state=" + STATE_NORMAL +" 前面有or这里用and好像无效
Connection c = DB.getConn();
Statement stmt = DB.createStatement(c);
ResultSet rs = DB.executeQuery(c, stmt, sql);
try {
if (rs == null) {
throw new RuntimeException("数据库操作出错，请重试！");
}
while (rs.next()) {
int id = rs.getInt("id");
int userId = rs.getInt("user_id");
String newQuestion = rs.getString("question");
String description = rs.getString("description");
String type = rs.getString("type");
int urgent = rs.getInt("urgent");
int state = rs.getInt("state");
String createDate = rs.getString("create_date");
String createTime = rs.getString("create_time");
String createIp = rs.getString("create_ip");
String updateDate = rs.getString("update_date");
String updateTime = rs.getString("update_time");
String updateIp = rs.getString("update_ip");
/** SQL 语句总 AND state=" + STATE_NORMAL +" 前面有or这里用and好像无效 */
if (state != STATE_NORMAL) {
continue;
}
CloudKnowAsk cloudKnowAsk =
new CloudKnowAsk(
id,
userId,
newQuestion,
description,
type,
urgent,
state,
createDate,
createTime,
createIp,
updateDate,
updateTime,
updateIp);
list.add(cloudKnowAsk);
}
return list;
} finally {
DB.close(rs);
DB.close(stmt);
DB.close(c);
}
}
/**
* 根据id查申成知道提问
*
* @param id
* @return
* @throws Exception
*/
public static CloudKnowAsk getCloudKnowAskById(int id) throws Exception {
CloudKnowAsk cloudKnowAsk = null;
String sql =
"SELECT id,user_id,question,description,type,urgent,state,create_date,create_time,"
+ "create_ip,update_date,update_time,update_ip FROM cloud_know_ask WHERE id="
+ id;
Connection c = DB.getConn();
Statement stmt = DB.createStatement(c);
ResultSet rs = DB.executeQuery(c, stmt, sql);
try {
if (rs == null) {
throw new RuntimeException("数据库操作出错，请重试！");
}
while (rs.next()) {
int userId = rs.getInt("user_id");
String question = rs.getString("question");
String description = rs.getString("description");
String type = rs.getString("type");
int urgent = rs.getInt("urgent");
int state = rs.getInt("state");
String createDate = rs.getString("create_date");
String createTime = rs.getString("create_time");
String createIp = rs.getString("create_ip");
String updateDate = rs.getString("update_date");
String updateTime = rs.getString("update_time");
String updateIp = rs.getString("update_ip");
cloudKnowAsk =
new CloudKnowAsk(
id,
userId,
question,
description,
type,
urgent,
state,
createDate,
createTime,
createIp,
updateDate,
updateTime,
updateIp);
}
return cloudKnowAsk;
} finally {
DB.close(rs);
DB.close(stmt);
DB.close(c);
}
}
/**
* 根据用户id查最大的申成知道提问Id
*
* @param userId
* @return
* @throws Exception
*/
public static int getMaxIdByUserId(int userId) throws Exception {
String sql = "SELECT MAX(id) max_id FROM cloud_know_ask WHERE user_id=" + userId;
Connection c = DB.getConn();
Statement stmt = DB.createStatement(c);
ResultSet rs = DB.executeQuery(c, stmt, sql);
try {
if (rs == null) {
throw new RuntimeException("数据库操作出错，请重试！");
}
while (rs.next()) {
return rs.getInt("max_id");
}
return 0;
} finally {
DB.close(rs);
DB.close(stmt);
DB.close(c);
}
}