Cloudera CDH/CDP 및 Hadoop EcoSystem, Semantic IoT등의 개발/운영 기술을 정리합니다. gooper@gooper.com로 문의 주세요.
Mariadb에 접근하여 쿼리를 수행하고 필요시 정렬하여 List<Map<String, String>>에 담아서 리턴하는 메세드 예제
private final List<Map<String, String>> getResult (String query, String[] idxVals) throws Exception {
String db_server = ;
String db_port = ;
String db_name = ;
String db_user = ;
String db_pass = ;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
try {
Class.forName("org.mariadb.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mariadb://" + db_server + ":" + db_port + "/" + db_name, db_user, db_pass);
pstmt = conn.prepareStatement(query);
rs = pstmt.executeQuery();
int cnt = 0;
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
while (rs.next()){
HashMap<String,String> row = new HashMap<String, String>(columns);
for(int i=1; i<=columns; i++){
row.put(md.getColumnName(i), rs.getObject(i).toString());
}
//log.debug("row("+(cnt++)+") ===========>"+row.toString());
list.add(row);
}
// 정렬(test)
// Collections.sort(list, new MapStringComparator("rest_type"));
//Collections.sort(list, new MapStringComparator("corner_id"));
//Collections.sort(list, new MapFloatComparator("cnt"));
return list;
} catch (Exception e) {
throw e;
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException sqle) {
}
if (pstmt != null)
try {
pstmt.close();
} catch (SQLException sqle) {
}
if (conn != null) {
try {
conn.close();
} catch (SQLException sqle) {
}
}
}
}
String db_server = ;
String db_port = ;
String db_name = ;
String db_user = ;
String db_pass = ;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
try {
Class.forName("org.mariadb.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mariadb://" + db_server + ":" + db_port + "/" + db_name, db_user, db_pass);
pstmt = conn.prepareStatement(query);
rs = pstmt.executeQuery();
int cnt = 0;
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
while (rs.next()){
HashMap<String,String> row = new HashMap<String, String>(columns);
for(int i=1; i<=columns; i++){
row.put(md.getColumnName(i), rs.getObject(i).toString());
}
//log.debug("row("+(cnt++)+") ===========>"+row.toString());
list.add(row);
}
// 정렬(test)
// Collections.sort(list, new MapStringComparator("rest_type"));
//Collections.sort(list, new MapStringComparator("corner_id"));
//Collections.sort(list, new MapFloatComparator("cnt"));
return list;
} catch (Exception e) {
throw e;
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException sqle) {
}
if (pstmt != null)
try {
pstmt.close();
} catch (SQLException sqle) {
}
if (conn != null) {
try {
conn.close();
} catch (SQLException sqle) {
}
}
}
}
*정렬을 위해서 사용되는 클래스(문자열)
class MapStringComparator implements Comparator<Map<String, String>> {
private final String key;
public MapStringComparator(String key) {
this.key = key;
}
@Override
public int compare(Map<String, String> first, Map<String, String> second) {
String firstValue =first.get(key);
String secondValue = second.get(key);
// 내림차순 정렬
return firstValue.compareTo(secondValue);
}
}
private final String key;
public MapStringComparator(String key) {
this.key = key;
}
@Override
public int compare(Map<String, String> first, Map<String, String> second) {
String firstValue =first.get(key);
String secondValue = second.get(key);
// 내림차순 정렬
return firstValue.compareTo(secondValue);
}
}
*정렬을 위해서 사용되는 클래스(숫자)
class MapFloatComparator implements Comparator<Map<String, String>> {
private final String key;
public MapFloatComparator(String key) {
this.key = key;
}
@Override
public int compare(Map<String, String> first, Map<String, String> second) {
float firstValue = Float.valueOf(first.get(key));
float secondValue = Float.valueOf(second.get(key));
// 내림차순 정렬
if (firstValue > secondValue) {
return -1;
} else if (firstValue < secondValue) {
return 1;
} else /* if (firstValue == secondValue) */ {
return 0;
}
}
}
private final String key;
public MapFloatComparator(String key) {
this.key = key;
}
@Override
public int compare(Map<String, String> first, Map<String, String> second) {
float firstValue = Float.valueOf(first.get(key));
float secondValue = Float.valueOf(second.get(key));
// 내림차순 정렬
if (firstValue > secondValue) {
return -1;
} else if (firstValue < secondValue) {
return 1;
} else /* if (firstValue == secondValue) */ {
return 0;
}
}
}
