안드로이드 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년 다녔으니까 그동안 쌓였던 내공들을 모두 기록하기엔 너무 벅차다.

그때 그때 블로그에 기록을 해두었어야 했다.

누가 알았겠나… 이런 날 이런 시대가 올 줄을…………