/**
* 查询申成知道提问
*
* @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);
}
}
/**
* Fill the table using m_sql
*
* @param table table
*/
private static void tableLoad(MiniTable table) {
// log.finest(m_sql + " - " + m_groupBy);
String sql =
MRole.getDefault()
.addAccessSQL(m_sql.toString(), "tab", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO)
+ m_groupBy
+ m_orderBy;
log.finest(sql);
try {
Statement stmt = DB.createStatement();
ResultSet rs = stmt.executeQuery(sql);
table.loadTable(rs);
stmt.close();
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
}
} // tableLoad
/**
* 根据用户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);
}
}