Demo entry 6645383

spring-boot-web-thymeleaf

   

Submitted by swd on Oct 10, 2017 at 09:02
Language: Java. Code size: 5.9 kB.

@Transactional(readOnly = true)  
    public List<Map<String, Object>> searchQuestion(List<String> keyList, String acct_id, String pg, int pn,  
            String answerFilter, String topicFilter) {  
        if (!answerFilter.isEmpty()) {  
            answerFilter = "1".equals(answerFilter) ? "AND total_answer <> '0'" : "AND total_answer = '0' ";  
        }  
  
        String[] topics = topicFilter.split(",");  
        StringBuffer tempTopic = new StringBuffer();  
        for (int i = 0; i < topics.length; i++) {  
            if (i == topics.length - 1) {  
                tempTopic.append("'" + topics[i] + "'");  
            } else {  
                tempTopic.append("'" + topics[i] + "',");  
            }  
        }  
        topicFilter = !topicFilter.isEmpty() ? "AND topic_id in(" + tempTopic.toString() + ") " : "";  
  
        String filterString = answerFilter + topicFilter;  
  
        StringBuffer s1 = new StringBuffer();  
        StringBuffer s2 = new StringBuffer();  
        StringBuffer sql = new StringBuffer();  
        StringBuffer weightstr = new StringBuffer();  
        if (keyList != null && keyList.size() > 0) {  
  
            Object[] keys = keyList.toArray();  
            for (int i = 0; i < keys.length; i++) {  
                if (i == keys.length - 1) {  
                    s1.append("LOWER(ques_topic.ques_title) LIKE LOWER('%" + keys[i] + "%')");  
                    s2.append("LOWER(answer.ans_content) LIKE '%" + keys[i].toString().toLowerCase()  
                            + "%'");  
                    weightstr.append(  
                            "IF(LOWER(ques_answer_attent_info.ques_title) LIKE LOWER('%" + keys[i] + "%'),1,0)");  
                } else {  
                    s1.append("LOWER(ques_topic.ques_title) LIKE LOWER('%" + keys[i] + "%') OR ");  
                    s2.append("LOWER(answer.ans_content) LIKE '%" + keys[i].toString().toLowerCase()  
                            + "%' OR ");  
                    weightstr.append("IF(LOWER(ques_answer_attent_info.ques_title) LIKE ('%" + keys[i] + "%'),1,0) + ");  
                }  
            }  
  
            sql.append("SELECT ques_answer_attent_info.*, ");  
            sql.append(weightstr.toString());  
            sql.append(" AS matching_rate, ");  
            sql.append(  
                    "IF( ques_answer_attent_info.answer_accept_flag = '1' AND ques_answer_attent_info.answer_acct_id <>'"  
                            + acct_id  
                            + "' AND ques_answer_attent_info.point_view_flag = '0','$null',ques_answer_attent_info.ans_content) AS ans_content ");  
            sql.append(  
                    "FROM ( SELECT ques_answer_info.*, acct.nick_name AS ques_nick_name,acct.xp_user_img as ques_user_img, acct_info.total_attention AS ques_acct_total_attention, acct_info.total_fan AS ques_acct_total_fan, ");  
  
            sql.append("IF (CONCAT('_',GROUP_CONCAT(DISTINCT ques_attention.acct_id SEPARATOR '_'),'_') LIKE '%_"  
                    + acct_id + "_%',1,0) AS ques_attention_flag, ");  
            sql.append("IF (CONCAT('_',GROUP_CONCAT(DISTINCT acct_attention.acct_id SEPARATOR '_'),'_') LIKE '%_"  
                    + acct_id + "_%',1,0) AS user_attention_flag, ");  
            sql.append("IF (CONCAT('_',GROUP_CONCAT(DISTINCT point_answer.view_acct_id SEPARATOR '_'),'_') LIKE '%_"  
                    + acct_id + "_%',1,0) AS point_view_flag ");  
  
            sql.append(  
                    "FROM ( SELECT ques_topic.id AS ques_id, ques_topic.topic_id, ques_topic.acct_id AS ques_uid, ques_topic.ques_title, ques_topic.total_attention AS ques_total_attention, ques_topic.total_answer AS ques_total_answer, ques_topic.create_time AS ques_createtime, ques_topic.`view` AS ques_view, ques_topic.title AS topic_title,answer.id AS answer_id, answer.acct_id AS answer_acct_id, answer.ans_content, answer.username AS answer_nick_name,  ");  
            sql.append(  
                    "IF ( answer.accept_id = '0' OR ISNULL(answer.accept_id), 0, 1 ) AS answer_accept_flag FROM ( ( SELECT ques.*, topic.title FROM xp_question ques LEFT JOIN xp_topic topic ON ques.topic_id = topic.id WHERE ques.del_flag = '0' AND ques.`status` = '0' AND topic.`status` = '0' "  
                            + filterString  
                            + " ) ques_topic LEFT JOIN ( SELECT * FROM xp_answer WHERE del_flag = '0') answer ON ques_topic.id = answer.question_id ) ");  
            sql.append("WHERE 1 = 1 AND ( ");  
            sql.append("(" + s1.toString() + ") OR (" + s2.toString() + ") ");  
            sql.append(  
                    ") ORDER BY answer_accept_flag DESC, answer.total_praise + 0 DESC, answer.total_collection + 0 DESC, answer.update_time DESC ) ques_answer_info LEFT JOIN xp_point_answer point_answer ON point_answer.answer_id = ques_answer_info.answer_id LEFT JOIN "  
                            + CC.DATASOURCE_COMMON  
                            + ".sys_account acct ON ques_answer_info.ques_uid = acct.acct_id LEFT JOIN xp_acct_info acct_info ON acct_info.acct_id = acct.acct_id LEFT JOIN xp_question_attention ques_attention ON ques_attention.question_id = ques_answer_info.ques_id ");  
            sql.append(  
                    "LEFT JOIN xp_acct_attention acct_attention ON acct_attention.be_acct_id = ques_answer_info.ques_uid GROUP BY ques_answer_info.ques_id ) ques_answer_attent_info ");  
              
            sql.append(  
                    "  ORDER BY matching_rate DESC, ques_answer_attent_info.ques_view+0 DESC, ques_answer_attent_info.ques_createtime DESC LIMIT ");  
            sql.append((Integer.parseInt(pg) - 1) * pn + "," + pn);  
        }  
        System.err.println(sql.toString());  
        return this.jdbcTemplate.queryForList(sql.toString());  
    }  

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).