안드로이드 초성 검색 쿼리 (Where 절)

카테고리 없음 2017. 4. 17. 12:56

Where 절 초성 검색 쿼리 입니다.

사용법
1
2
3
4
5
6
7
8
9 public String searchSql(String searchStr) {
    String sql = "Select * FROM " + {TABLE_NAME};
        if (TextUtils.isEmpty(searchStr) == false) {
            sql += " WHERE ";
            sql += ChoSearchQuery.makeQuery(searchStr);
        }
    
    return sql;
}
Colored by Color Scripter cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173 public class ChoSearchQuery {
    public static final int EVENT_CODE_LENGTH = 6;
 
    public static final int DIGIT_BEGIN_UNICODE = 0x30; //0
    public static final int DIGIT_END_UNICODE = 0x3A; //9
    
    public static final int QUERY_DELIM = 39;//'
    public static final int LARGE_ALPHA_BEGIN_UNICODE = 0;
 
    public static final int HANGUL_BEGIN_UNICODE = 0xAC00; // 가
    public static final int HANGUL_END_UNICODE = 0xD7A3; // ?
    public static final int HANGUL_CHO_UNIT = 588; //한글 초성글자간 간격
    public static final int HANGUL_JUNG_UNIT = 28; //한글 중성글자간 간격
 
    public static final char[] CHO_LIST = { 'ㄱ', 'ㄲ', 'ㄴ', 'ㄷ', 'ㄸ', 'ㄹ',
            'ㅁ', 'ㅂ', 'ㅃ', 'ㅅ', 'ㅆ', 'ㅇ', 'ㅈ', 'ㅉ', 'ㅊ', 'ㅋ', 'ㅌ', 'ㅍ', 'ㅎ' };
    public static final boolean[] CHO_SEARCH_LIST = { true, false, true, true, false, true,
        true, true, false, true, false, true, true, false, true, true, true, true, true };
 
    /**
     * 문자를 유니코드(10진수)로 변환 후 반환한다.
     * @param ch 문자
     * @return 10진수 유니코드
     */
    public static int convertCharToUnicode(char ch) {
        return (int) ch;
    }
    
    /**
     * 10진수를 16진수 문자열로 변환한다.
     * @param decimal 10진수 숫자
     * @return 16진수 문자열
     */
    private static String toHexString(int decimal) {
        Long intDec = Long.valueOf(decimal);
        return Long.toHexString(intDec);
    }
 
    /**
     * 유니코드(16진수)를 문자로 변환 후 반환한다.
     * @param hexUnicode Unicode Hex String
     * @return 문자값
     */
    public static char convertUnicodeToChar(String hexUnicode) {
        return (char) Integer.parseInt(hexUnicode, 16);
    }
 
    /**
     * 유니코드(10진수)를 문자로 변환 후 반환한다.
     * @param unicode
     * @return 문자값
     */
    public static char convertUnicodeToChar(int unicode) {
        return convertUnicodeToChar(toHexString(unicode));
    }
    
    /**
     * 검색 문자열을 파싱해서 SQL Query 조건 문자열을 만든다.
     * @param strSearch 검색 문자열
     * @return SQL Query 조건 문자열
     */    
    public static String makeQuery(String strSearch){
        strSearch = strSearch == null ? "null" : strSearch.trim();
        
        StringBuilder retQuery = new StringBuilder();
        
            int nChoPosition;
            int nNextChoPosition;
            int StartUnicode;
            int EndUnicode;
            
            int nQueryIndex = 0;
//            boolean bChosung = false;
            StringBuilder query = new StringBuilder();
            for( int nIndex = 0 ; nIndex < strSearch.length() ; nIndex++ ){
                nChoPosition = -1;
                nNextChoPosition = -1;
                StartUnicode = -1;
                EndUnicode = -1;
                
                if( strSearch.charAt(nIndex) == QUERY_DELIM )
                    continue;
                
                if( nQueryIndex != 0 ){
                    query.append(" AND ");
                }
                
                for( int nChoIndex = 0 ; nChoIndex < CHO_LIST.length ; nChoIndex++ ){                    
                    if( strSearch.charAt(nIndex) == CHO_LIST[nChoIndex] ){
                        nChoPosition = nChoIndex;
                        nNextChoPosition = nChoPosition+1;
                        for( ; nNextChoPosition < CHO_SEARCH_LIST.length ; nNextChoPosition++ ){
                            if( CHO_SEARCH_LIST[nNextChoPosition] )
                                break;
                        }
                        break;
                    }
                }
                
                if( nChoPosition >= 0 ){ //초성이 있을 경우
//                    bChosung = true;
                    StartUnicode = HANGUL_BEGIN_UNICODE + nChoPosition*HANGUL_CHO_UNIT;
                    EndUnicode = HANGUL_BEGIN_UNICODE + nNextChoPosition*HANGUL_CHO_UNIT;                    
                }
                else{ 
                    int Unicode = convertCharToUnicode(strSearch.charAt(nIndex));
                    if( Unicode >= HANGUL_BEGIN_UNICODE && Unicode <= HANGUL_END_UNICODE){
                        int Jong = ((Unicode-HANGUL_BEGIN_UNICODE)%HANGUL_CHO_UNIT)%HANGUL_JUNG_UNIT;                    
                        
                        if( Jong == 0 ){// 초성+중성으로 되어 있는 경우 
                            StartUnicode = Unicode;                        
                            EndUnicode = Unicode+HANGUL_JUNG_UNIT;                        
                        }
                        else{
                            StartUnicode = Unicode;                        
                            EndUnicode = Unicode;
                        }
                    }
                }
                
                //Log.d("SearchQuery","query "+strSearch.codePointAt(nIndex));
                if( StartUnicode > 0 && EndUnicode > 0 ){
                    if( StartUnicode == EndUnicode )
                        query.append("substr(name,"+(nIndex+1)+",1)='"+strSearch.charAt(nIndex)+"'");
                    else
                        query.append("(substr(name,"+(nIndex+1)+",1)>='"+convertUnicodeToChar(StartUnicode)
                            +"' AND substr(name,"+(nIndex+1)+",1)<'"+convertUnicodeToChar(EndUnicode)+"')");
                }
                else{
                    if( Character.isLowerCase(strSearch.charAt(nIndex))){ //영문 소문자
                        query.append("(substr(name,"+(nIndex+1)+",1)='"+strSearch.charAt(nIndex)+"'"
                                + " OR substr(name,"+(nIndex+1)+",1)='"+Character.toUpperCase(strSearch.charAt(nIndex))+"')");
                    }
                    else if( Character.isUpperCase(strSearch.charAt(nIndex))){ //영문 대문자
                        query.append("(substr(name,"+(nIndex+1)+",1)='"+strSearch.charAt(nIndex)+"'"
                                + " OR substr(name,"+(nIndex+1)+",1)='"+Character.toLowerCase(strSearch.charAt(nIndex))+"')");
                    }
                    else //기타 문자
                        query.append("substr(name,"+(nIndex+1)+",1)='"+strSearch.charAt(nIndex)+"'");
                }
                
                nQueryIndex++;
            }
            
            if(query.length() > 0 && strSearch != null && strSearch.trim().length() > 0) {
                retQuery.append("("+query.toString()+")");
                
                if(strSearch.indexOf(" ") != -1) {
                    // 공백 구분 단어에 대해 단어 모두 포함 검색
                    String[] tokens = strSearch.split(" ");
                    retQuery.append(" OR (");
                    for(int i=0, isize=tokens.length; i<isize; i++) {
                        String token = tokens[i];
                        if(i != 0) {
                            retQuery.append(" AND ");
                        }
                        retQuery.append("name like '%"+token+"%'");
                    }
                    retQuery.append(")");
                } else {
                    // LIKE 검색 추가
                    retQuery.append(" OR name like '%"+strSearch+"%'");
                }
            } else {
                retQuery.append(query.toString());
            }
//        }
        //Log.d("SearchQuery","query "+query.toString());
        return retQuery.toString();
    }    
 
}
 
Colored by Color Scripter cs