N7AuLpza

1SQLite Android - how to select or check for a row
2import android.content.ContentValues;
3import android.content.Context;
4import android.database.sqlite.SQLiteDatabase;
5import android.database.sqlite.SQLiteDatabase.CursorFactory;
6import android.database.sqlite.SQLiteOpenHelper;
7
8public class DataBaseManager extends SQLiteOpenHelper{
9
10    static final String dbName ="LCInstore";
11
12    static final String allIcons = "Icons";
13    static final String homeIcons = "HomeScreenIcons";
14
15    static final String colIconID = "IconID";
16    static final String colID = "ID";
17    static final String colImage = "IconImage";
18    static final String colLabel = "IconLabel";
19    static final String colName = "IconName";
20
21    public DataBaseManager(Context context) {
22        super(context, dbName, null, 0); // starting value is zero must change on upgrades
23        // TODO Auto-generated constructor stub
24    }
25
26    @Override
27    public void onCreate(SQLiteDatabase db) {
28        // TODO Auto-generated method stub
29
30        // Create Icon Table if does not exist
31         db.execSQL("CREATE TABLE IF NOT CREATED"+ allIcons +"" +
32                "("+colIconID + " INTEGER PRIMARY KEY AUTOINCREMENT, "+
33                    colName + " TEXT," +
34                    colImage + " TEXT," +
35                    colLabel + "TEXT)");
36
37        // Create HomeScreen Icons Table if does not exist
38         db.execSQL("CREATE TABLE IF NOT EXIST" + homeIcons+"" +
39                "("+colID+" INTEGER NOT NULL, FOREIGN KEY ("+colID+") REFERENCES" +
40                        ""+allIcons+" ("+colIconID+"));");
41
42         InsertIcons(db);
43    }
44
45    @Override
46    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
47        // TODO Auto-generated method stub
48        db.execSQL("DROP TABLE IF EXISTS "+allIcons);
49
50    }
51
52    private void InsertIcons(SQLiteDatabase db) {
53        ContentValues cv=new ContentValues();
54           cv.put(colIconID, 1);
55           cv.put(colName, "Icon1");
56           cv.put(colImage, "icon_one");
57           cv.put(colLabel, "ONE");
58           db.insert(allIcons, colIconID, cv);
59
60           cv.put(colIconID, 2);
61           cv.put(colName, "Icon2");
62           cv.put(colImage, "icon_two");
63           cv.put(colLabel, "TWO");
64           db.insert(allIcons, colIconID, cv);
65
66           cv.put(colIconID, 3);
67           cv.put(colName, "Icon3");
68           cv.put(colImage, "icon_three");
69           cv.put(colLabel, "THREE");
70           db.insert(allIcons, colIconID, cv);
71
72           db.close();
73    }
74
75    public void AddHomeScreenIcon(int id){
76
77   SQLiteDatabase db=this.getWritableDatabase();
78   //HELP
79   // Need to check if id already exists in colID of homeIcons TABLE
80   // and if it doesn't do this
81   Cursor c = db.rawQuery("select * from "+ homeIcons +" where colID = " +id, null);
82   int numFound = c.getCount();
83
84   if(numFound<1){
85       ContentValues cv=new ContentValues();
86       cv.put(colID, id);
87       db.insert(homeIcons, colID, cv);
88   }
89
90   // else do nothing
91
92 }
93
94public void DeleteHomeScreenIcon (int id){
95    SQLiteDatabase db=this.getWritableDatabase();
96    db.delete(homeIcons, "id = " +id, null);
97    // HELP
98    //Need to  find row of homeIcons table that matches id and delete it
99    db.close();
100}
101
102
103}
104	
105//Querying TABLE_NAME for ID in the colId column
106cursor = db.query(TABLE_NAME,
107                  new String[] {ALL, My, COLUMN, NAMES, EG, colId },
108                  colId = " = ? ",
109                  new String[]{String.valueOf(id)}, null, null, null);
110
111if (cursor.moveToFirst())
112{ /* IT EXISTS! */ }
113	
114db = this.getWriteableDatabase();
115boolean success = db.delete(TABLE_NAME, 
116            colID + " = ?", 
117            new String[]{String.valueOf(id)})>0;