안드로이드 SQLite 검색문 예시( SELECT 문)
나중에 활용하기 위해 기록을 남긴다. 사람의 기억력은 그리 오래가지 못한다. 내가 개발하고도 일주일 지나면 내가 개발한 코드가 맞나? 싶을때가 가끔있다. 한 달이 지나면 그 기억은 더 쇠퇴한다. 그러하니 기록을 남기자.
그나마 불안감이 사라질테니까…..
[스크립트1]
public static Cursor selectMyNumberList(SQLiteDatabase database, String searchParam ){
String queryParam;
queryParam = "SELECT event_id "
+ ", _event_date"
+ ", _my_number_1"
+ ", _my_number_2"
+ ", _my_number_3"
+ ", _my_number_4"
+ ", _my_number_5"
+ ", _my_number_6"
+ ", _reg_type"
+ " FROM TB_LOTTO_MY_NUMBER ";
if(!StringUtil.isEmpty(searchParam)) {
queryParam = queryParam + " WHERE _is_delete= " + searchParam ;
}
queryParam = queryParam + " ORDER BY _event_date DESC ";
Cursor cursor = database.rawQuery(queryParam ,null);
return cursor;
}[스크립트2]
public static Cursor selectAllWinderNumbers(SQLiteDatabase database, String round){
String queryParam ="SELECT event_id "
+ ", _event_round"
+ ", _event_date"
+ ", _winner_numbers"
+ ", _bonus_number"
+ " FROM TB_LOTTO_WINNER_NUMBER"
+ " ORDER BY _event_round DESC ";
//+ " LIMIT 1"; // TOP 1
//+ " WHERE _event_date >=" + "'" + today + " 00:00:00'"
//+ " AND _event_date <=" + "'" + today + " 23:59:59'";
Cursor cursor = database.rawQuery(queryParam ,null);
return cursor;
}[스크립트3]
public static Cursor selectSearchWordingDayStory(SQLiteDatabase database, String searchParam , int CurrentPage , int PageSize ){
String queryParam;
queryParam = "SELECT _event_type "
+ ", _contents"
+ ", event_id"
+ ", _event_date"
+ ", _fight_hour"
+ ", _mg_content"
+ " FROM TB_LOTTO_WINNER_NUMBER ";
if(!StringUtil.isEmpty(searchParam)) {
queryParam = queryParam + " WHERE (_contents LIKE " + "'%" + searchParam + "%'" + " OR _mg_content LIKE " + "'%" + searchParam + "%'" + ")" ;
}
queryParam = queryParam + " ORDER BY _event_date DESC ";
if(StringUtil.isEmpty(searchParam)) {
queryParam = queryParam +" LIMIT " + PageSize + " OFFSET " + (CurrentPage - 1) * PageSize;
}
Cursor cursor = database.rawQuery(queryParam ,null);
return cursor;
}[스크립트4]
public static Cursor selectFightTimeStatForPIe(SQLiteDatabase database, String sYear){
String startDate = sYear +"-01-01";
String endDate = sYear +"-12-31";
Cursor cursor = database.rawQuery(
//SELECT strftime('%w', _event_date) AS WEEKEND_DAY FROM TB_LOTTO_WINNER_NUMBER WHERE _event_date >='2020-01-01 00:00:00' AND _event_date <='2020-12-31 23:59:59'
"SELECT "
+" _fight_hour AS FIGHT_TIME "
+" FROM TB_LOTTO_WINNER_NUMBER"
+ " WHERE _event_date >=" + "'" + startDate + " 00:00:00'"
+ " AND _event_date <=" + "'" + endDate + " 23:59:59' "
+ " AND _fight_hour >0 "
,null);
return cursor;
}[스크립트5]
public static Cursor selectSearchWordingDayStory_bal2(SQLiteDatabase database, String searchParam , int CurrentPage , int PageSize ){
String queryParam;
queryParam = "SELECT X.* FROM ( "
+ "SELECT _event_type "
+ ", _contents"
+ ", event_id"
+ ", _event_date"
//+" , count(*) over () as totalCnt "
//+" , rownum() AS int_ROWNUM"
//+" , ROW_NUMBER() OVER (PARTITION BY _event_date ORDER BY _event_date DESC) as int_ROWNUM"
//+" ,ROW_NUMBER() OVER ( ORDER BY _event_date DESC ) as RN"
//+" ,ROW_NUMBER() OVER () as RN"
+", (select count(*) from TB_LOTTO_WINNER_NUMBER b where a.event_id = b.event_id) as RN"
+ " FROM TB_LOTTO_WINNER_NUMBER a"
//+ " ORDER BY _event_date DESC"
//+ " WHERE _contents LIKE " + "'%" + searchParam + " %'"
+" ) X "
+" WHERE X.RN BETWEEN " + String.valueOf((CurrentPage - 1) * PageSize + 1) + " AND " + CurrentPage * PageSize;
//+" ORDER X.RN desc";
Cursor cursor = database.rawQuery(queryParam ,null);
return cursor;
}[스크립트6]
public static Cursor selectData(SQLiteDatabase database, String tabId){
String pDate ="";
if("tab1".equals(tabId)) {
pDate = DateUtil.getCurrentDate("yyyy-MM-dd");
}else if("tab2".equals(tabId)) {
//Calendar cal = new GregorianCalendar();
Calendar cal = Calendar.getInstance();
//cal.setTime(new Date());
//cal.add(Calendar.YEAR, 1); // 1년을 더한다.
//cal.add(Calendar.MONTH, 1); // 한달을 더한다.
//cal.add(Calendar.DAY_OF_YEAR, 1); // 하루를 더한다.
//cal.add(Calendar.HOUR, 1); // 시간을 더한다
cal.add(Calendar.DATE, -1); // 하루전날짜 구한다.
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
pDate = sdf.format(cal.getTime());
}catch(Exception e){
e.printStackTrace();
}
}
Cursor cursor = database.rawQuery(
"SELECT * FROM ( SELECT _id, _start_date, _end_date, _charge_time, _etc "
+" ,COUNT(CASE WHEN _type = 'FU' THEN 1 ELSE 0 END) OVER() AS full_cnt "
+" ,COUNT(CASE WHEN _type = 'NO' THEN 1 ELSE 0 END) OVER() AS normal_cnt "
+" ,COUNT(CASE WHEN _type = 'OC' THEN 1 ELSE 0 END) OVER() AS overcharge_cnt "
+" ,COUNT(CASE WHEN _type = 'OH' THEN 1 ELSE 0 END) OVER() AS overheat_cnt "
+" ,COUNT(CASE WHEN _type = 'OV' THEN 1 ELSE 0 END) OVER() AS over_voltage_cnt "
+" ,COUNT(CASE WHEN _type = 'CO' THEN 1 ELSE 0 END) OVER() AS cold_cnt "
+" FROM TB_CHARGE_HISTORY"
+ " WHERE _start_date >=" + "'" + pDate + " 00:00:00'"
+ " AND _start_date <=" + "'" + pDate + " 23:59:59' )"
,null);
return cursor;
}직장 다닐때 작성했던 스크립트들이 아쉬울뿐이다. 지금은 퇴사를 해서 볼 수 도 없고 기록할수도 없으니 말이다.
직장을 10년 다녔으니까 그동안 쌓였던 내공들을 모두 기록하기엔 너무 벅차다.
그때 그때 블로그에 기록을 해두었어야 했다.
누가 알았겠나… 이런 날 이런 시대가 올 줄을…………
