* 根据汉字拼音首字母查询具体科室并分组,key为A-Z(除了I,U,V)
*
* @return
*/
@ResponseBody
@RequestMapping("/getDepartmentbyCode")
public Object getDepartmentbyCode() {
Map<String, Character> map = new HashMap<String, Character>();
List<Map<String, Object>> departmentList = new ArrayList<Map<String, Object>>();
List<Department> list = departmentService.getDepartmentbyCode();
int index = 0;
for (int i = 65; i < 91; i++) {
if (i == 73 || i == 85 || i == 86)
continue;
else {
List<Department> result = new ArrayList<Department>();
char alphabet = (char) i;
String alphabets = new Character(alphabet).toString();
for (Department department : list) {
if (department.getFirstSpell().equals(alphabets)) {
System.out.println(result);
result.add(list.get(index));
boolean contains = map.containsKey(alphabets);
if(!contains)
{
index++;
}
else
{
continue;
}
}
}
Map<String, Object> map1 = new HashMap<String, Object>();
map1.put("key",alphabets);
map1.put("data", result);
departmentList.add(map1);
HashSet<Map<String, Object>> h = new HashSet<Map<String, Object>>(departmentList);
departmentList.clear();
departmentList.addAll(h);
}
}
JSONArray jsonArray = JSONArray.fromObject(departmentList);
return new RequestResult(ResultCodeUtil._100, ResultCodeUtil.getMsg("100"), jsonArray);
}
查询语句
<select id="getDepartmentbyCode" parameterType="java.util.Map" resultType="com.pp.app.beans.Department">
SELECT
*, firstSpell (departmentName) AS firstSpell
FROM
department
WHERE
firstSpell (departmentName) IN (
'A',
'B',
'C',
'D',
'E',
'F',
'G',
'H',
'J',
'K',
'L',
'M',
'N',
'O',
'P',
'Q',
'R',
'S',
'T',
'W',
'X',
'Y',
'Z'
)
ORDER BY
firstSpell;
</select>
firstSpell函数:
BEGIN
DECLARE V_RETURN VARCHAR(255);
DECLARE V_BOOL INT DEFAULT 0;
DECLARE FIRST_VARCHAR VARCHAR(1);
SET FIRST_VARCHAR = left(CONVERT(P_NAME USING gbk),1);
SELECT FIRST_VARCHAR REGEXP '[a-zA-Z]' INTO V_BOOL;
IF V_BOOL = 1 THEN
SET V_RETURN = FIRST_VARCHAR;
ELSE
SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
END IF;
RETURN V_RETURN;
END