SQLiteOpenHelper 1.0.1

 

/**
 * Created by lsh on 2016-04-06.
 * version 1.0.1
 */
public class SQLiteDBHelper extends SQLiteOpenHelper {
    public static  String SQLiteHelper = "SQLiteHelper";

    public static final String TABLE_NAME = "WIFISTORE_DB";
    private static final String DATABASE_NAME = "wifi.sqlite";
    private static final int DATABASE_VERSION = 1;

    private Context context;

    public static SQLiteDBHelper helper;
    public static SQLiteDatabase db;
    public static Cursor cursor;


    public SQLiteDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
        helper = this;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.e(SQLiteHelper, "onCreate");
        db.execSQL("CREATE TABLE " + TABLE_NAME + " (TAG_ORD INTEGER PRIMARY KEY," +
                "STORAGE_YMD CHAR(8), LATITUDE_NO REAL, LONGITUDE_NO REAL, SSID_NM TEXT, LEVEL_NO INTEGER);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.e(SQLiteHelper, "onUpgrade");
//        db.execSQL("DROP TABLE IF EXISTS " + tableName);
//        onCreate(db);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        Log.e(SQLiteHelper, "onOpen");
        super.onOpen(db);
    }

    /**
     * 데이터 추가하기






     */
    public static boolean insertData(String date, double latitude, double longitude, String ssid, int level) {
        boolean result = false;
        String query = "INSERT INTO " + TABLE_NAME + " (STORAGE_YMD, LATITUDE_NO, LONGITUDE_NO, SSID_NM, LEVEL_NO) "
                + "VALUES ( '" + date + "', '" + latitude + "', '" + longitude + "' , '" + ssid + "', '" + level + "' ) ";
        Log.e(SQLiteHelper, query);
        try {
            db = helper.getWritableDatabase();
            db.execSQL(query);
            helper.close();

            result = true;
        } catch (Exception e) {
            Log.e(SQLiteHelper, e.getMessage());
            result = false;
        }

        Log.e(SQLiteHelper, "insert success : " + result);
        return result;
    }

    /**
     * 데이터 조회하기
     * @return 조회된 리스트
     */
    public static ArrayList<DBColumn> loadData() {
        // 선언
        ArrayList<DBColumn> dataList = new ArrayList<DBColumn>();
        String query = "SELECT * FROM " + TABLE_NAME;// + " ORDER BY TAG_ORD";

        if (helper == null) {
            Log.e(SQLiteHelper, "helper가 null");
        }

        // db 조회
        db = helper.getReadableDatabase();
        cursor = db.rawQuery(query, null);
        cursor.moveToFirst();

        // db 조회 값을 list에 전달
        while (!cursor.isAfterLast()) {
            Log.e(SQLiteHelper, query);
            DBColumn dbColumn = new DBColumn();

            dbColumn.setDate(cursor.getString(1));
            dbColumn.setLatitude(cursor.getDouble(2));
            dbColumn.setLongitude(cursor.getDouble(3));
            dbColumn.setSsid(cursor.getString(4));
            dbColumn.setLevel(cursor.getInt(5));
            dataList.add(dbColumn);

            cursor.moveToNext();
        }
        cursor.close();
        helper.close();

        return dataList;
    }

    /**
     * 데이터 삭제하기
     *
     * @return
     */
    public static boolean deleteData(String 삭제 아이디) {
        boolean result = false;
        String query = "DELETE FROM " + TABLENAME_TB + " WHERE culm = '" + culm + "'; ";

        try {
            db = helper.getWritableDatabase();
            db.execSQL(query);
            helper.close();

            result = true;
        } catch (Exception e) {
            Log.e("에러", e.getMessage());
            result = false;
        }

        Log.e("DELETE", result + "");
        return result;
    }
}