代碼總結：文本欄關鍵字搜索功能實現

• 需求：根據關鍵字搜索功能（%通配符）

 
 


1
2
3
4
5
6
7
8
9
10
//jsp腳本
String keyWord = request.getParameter("keyWord");
if(keyWord == ""){
 request.setAttribute("schList",null);
}else{
 QuestionService qService = QuestionService.getService();
 List schList = qService.searchQuestion(keyWord);
 request.setAttribute("schList",schList);
 request.setAttribute("keyWord",keyWord);
}
%>
1
2
3
4
5
6
7
8
9
10
11
12
//數據庫操作
public List searchQuestion(String keyWord){
 List qList = new ArrayList();
 Connection conn = DBHelper.linkToDB();
 PreparedStatement pcmd = null;
 ResultSet rs = null;
 String sql = "SELECT a.qid,a.title,a.tags,a.content,b.nickName,b.photo,a.pubtime,a.votes,a.answers,a.visitors,a.type ";
 sql += "FROM question AS a ";
 sql += "LEFT JOIN USER AS b ";
 sql += "ON a.publisher=b.id ";
 sql += "WHERE title LIKE ? ";
 sql += "OR tags LIKE ? ";
 sql += "OR content LIKE ? ";
 try {
 pcmd = conn.prepareStatement(sql);
 pcmd.setString(1, "%"+keyWord+"%");
 pcmd.setString(2, "%"+keyWord+"%");
 pcmd.setString(3, "%"+keyWord+"%");
 rs = pcmd.executeQuery();
 while(rs.next()){
 int qid = rs.getInt(1);
 String title = rs.getString(2);
 String rsTags = rs.getString(3);
 String[] tags = rsTags.split(",");
 String content = rs.getString(4);
 String publisher = rs.getString(5);
 String photo = rs.getString(6);
 String pubtime = rs.getString(7).substring(0, 16);
 int votes = rs.getInt(8);
 int answers = rs.getInt(9);
 int visitors = rs.getInt(10);
 String type = rs.getString(11);
 qList.add(new QuestionShow(qid,title,tags,content,publisher,photo,pubtime,votes,answers,visitors,type)); 
 }
 } catch (SQLException e) {
 e.printStackTrace();
 } finally{
 DBHelper.close(conn, pcmd, rs);
 }
 return qList;
 } 

閱讀更多 程序員小新人學習 的文章

關鍵字: br pcmd 關鍵字