GkLbMvh9

1package com.bluej.movingbuddy;
2
3import java.util.ArrayList;
4import java.util.List;
5import android.content.ContentValues;
6import android.content.Context;
7import android.database.Cursor;
8import android.database.sqlite.SQLiteDatabase;
9import android.database.sqlite.SQLiteOpenHelper;
10import android.database.SQLException;
11import android.util.Log;
12
13public class MBDatabaseManager {
14
15
16
17//Database Version
18private final static int DATABASE_VERSION = 1;
19
20//Database Name
21private final static String DATABASE_NAME = "dbMovingBuddy";
22
23//items and weights table name
24private final static String tblInW = "ItemsAndWeights";
25//items and weights table columns
26private final static String InWID = "ID";
27private final static String InWItem = "Item";
28private final static String InWDesc = "Description";
29private final static String InWWeightOne = "Weight1";
30private final static String InWWeightTwo = "Weight2";
31private final static String InWWeightThree = "Weight3";
32private final static String InWWeightAvg = "WeightAvg";
33
34//allowances table name
35private final static String tblAllowances = "Allowances";
36//allowances table columns
37private final static String AllowancesID = "ID";
38private final static String AllowancesRank = "Rank";
39private final static String AllowancesWithDep = "WithDep";
40private final static String AllowancesNoDep = "NoDep";
41
42//estimator table name
43private final static String tblEstimator = "Estimator";
44//estimator table columns
45private final static String EstimatorID = "ID";
46private final static String EstimatorRoom = "Room";
47private final static String EstimatorItem = "Weight";
48
49//inventory table name
50private final static String tblInventory = "Inventory";
51//inventory table column
52private final static String InventoryID = "ID";
53private final static String InventoryItem = "Item";
54private final static String InventoryWeight = "Weight";
55private final static String InventoryValue = "Value";
56private final static String InventoryImage1 = "Image1";
57private final static String InventoryCondition = "Condition";
58private final static String InventoryImage2 = "Image2";
59private final static String InventoryNotes = "Notes";
60private final static String InventoryMovingInstructions = "MovingInstructions";
61
62//stunt table name
63private final static String TABLE_NAME = "database_table";
64//stunt table column names
65private final static String TABLE_ROW_ID = "id";
66private final static String TABLE_ROW_ONE = "table_row_one";
67private final static String TABLE_ROW_TWO = "table_row_two";
68
69public MBDatabaseManager(Context context) {
70    // TODO Auto-generated constructor stub
71    super(context, DATABASE_NAME, null, DATABASE_VERSION);
72}
73
74public void onCreate(SQLiteDatabase db) {
75    // TODO Auto-generated method stub
76    //This string is used to create the database. It should be changed to suit your needs.
77
78    //create items and weights table
79    String dbCreateItemsAndWeights = "create table " +
80    tblInW +
81    " (" +
82    InWID + " integer primary key autoincrement not null," +
83    InWItem + " text," +
84    InWDesc + " text," +
85    InWWeightOne + " integer," +
86    InWWeightTwo + " integer," +
87    InWWeightThree + " integer," +
88    InWWeightAvg + " integer" +
89    ");";
90
91    db.execSQL(dbCreateItemsAndWeights);
92
93    //allowances table
94    String dbCreateAllowances = "create table " +
95    tblAllowances +
96    " (" +
97    AllowancesID + " integer primary key autoincrement not null," +
98    AllowancesRank + " text," +
99    AllowancesWithDep + " integer," +
100    AllowancesNoDep + " integer" +
101    ");";
102
103    db.execSQL(dbCreateAllowances);
104
105    //estimator table
106    String dbCreateEstimator = "create table " +
107    tblEstimator + 
108    " (" +
109    EstimatorID + " integer primary key autoincrement not null," +
110    EstimatorRoom + " text," +
111    EstimatorItem + " integer" +
112    ");";
113
114    db.execSQL(dbCreateEstimator);
115
116    //inventory table
117    String dbCreateInventory = "create table " +
118    tblInventory + 
119    " (" +
120    InventoryID + " integer primary key autoincrement not null," +
121    InventoryItem + " text," + 
122    InventoryWeight + " integer," +
123    InventoryValue + " integer," +
124    InventoryImage1 + " blob," +
125    InventoryCondition + " text," + 
126    InventoryImage2 + " blob," + 
127    InventoryNotes + " text," +
128    InventoryMovingInstructions + " text" +
129    ");";
130
131    db.execSQL(dbCreateInventory);      
132
133    //stunt table
134    String newTableQueryString = "create table " +
135    TABLE_NAME +
136    " (" +
137    TABLE_ROW_ID + " integer primary key autoincrement not null," +
138    TABLE_ROW_ONE + " text," +
139    TABLE_ROW_TWO + " text" +
140    ");";
141
142    db.execSQL(newTableQueryString);        
143
144}
145
146public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
147    // TODO Auto-generated method stub
148
149    db.execSQL("DROP TABLE IF EXISTS " + tblInW);
150    db.execSQL("DROP TABLE IF EXISTS " + tblAllowances);
151    db.execSQL("DROP TABLE IF EXISTS " + tblEstimator);
152    db.execSQL("DROP TABLE IF EXISTS " + tblInventory);
153    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
154
155    onCreate(db);
156}
157
158// Adding a row to the database table
159public void addRow(String rowStringOne, String rowStringTwo){
160
161    SQLiteDatabase db = this.getWritableDatabase();
162
163    //this is a key value pair holder used by android's SQLite functions
164    ContentValues values = new ContentValues();
165    values.put(TABLE_ROW_ONE, rowStringOne);
166    values.put(TABLE_ROW_TWO, rowStringTwo);
167
168    //ask the database object to insert the new data
169    try {
170        db.insert(TABLE_NAME,  null, values);
171    } catch (Exception e) {
172        // TODO Auto-generated catch block
173        Log.e("DB ERROR", e.toString());
174        e.printStackTrace();
175    }
176}
177
178
179//DELETING A ROW FROM THE DATABASE TABLE
180//
181// This is an example of how to delete a row from a database table
182// using this class. In most cases, this method probably does not need to be rewritten.
183//
184// @param rowID the SQLite database identifier for the row to delete.
185//
186public void deleteRow(long rowID){
187
188    // ask the database object to delete the row of given rowID
189    try {
190        db.delete(TABLE_NAME, TABLE_ROW_ID + "=" + rowID, null);
191    } catch (Exception e) {
192        // TODO Auto-generated catch block
193        Log.e("DB ERROR", e.toString());
194        e.printStackTrace();
195    }
196
197}
198
199//UPDATING A ROW IN THE DATABASE TABLE
200//
201// This is an example of how to update a row in the database table
202// using this class. You should edit this method to suit your needs.
203//
204// @param rowID the SQLite database identifier for the row to update.
205// @param rowStringOne the new value for the row's first column
206// @param rowStringTwo the new value for the row's second column
207
208public void updateRow(long rowID, String rowStringOne, String rowStringTwo){
209
210    //this is a key value pair holder used by android's SQLite functions
211    ContentValues values = new ContentValues();
212    values.put(TABLE_ROW_ONE,  rowStringOne);
213    values.put(TABLE_ROW_TWO,  rowStringTwo);
214
215    //ask the database object to update the database row of given rowID
216    try {
217        db.update(TABLE_NAME,  values, TABLE_ROW_ID + "=" + rowID, null);
218    } catch (Exception e) {
219        // TODO Auto-generated catch block
220        Log.e("DB Error", e.toString());
221        e.printStackTrace();
222    }
223}
224
225//RETRIEVING A ROW IN THE DATABASE TABLE
226//'
227// This is an example of how to retrieve a row from a database table using this class. You should edit this method to suit your needs.
228//
229// @param rowID the id of the row to retrieve
230// @return an array containing the data from the row
231
232public ArrayList<Object> getRowAsArray(long rowID){
233
234    //create an array list to store data from the database row.
235    //I would recommend creating a JavaBean compliant object 
236    //to store this data instead. That way you can ensure data types are correct.
237    ArrayList<Object> rowArray = new ArrayList<Object>();
238    Cursor cursor;
239
240    try {
241        // this is a database call that creates a "cursor" object.
242        // the cursor object stores the information collected from the 
243        // database and is used to iterate through the data.
244        cursor = db.query(
245                TABLE_NAME,
246                new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO },
247                TABLE_ROW_ID + "=" + rowID,
248                null, null, null, null, null);
249
250        //move the pointer to position zero in the cursor.
251        cursor.moveToFirst();
252
253        // if there is data available after the cursor's pointer, add
254        // it to the ArrayList that will be returned by the method.
255
256        if (!cursor.isAfterLast()){
257            do{
258
259                rowArray.add(cursor.getLong(0));
260                rowArray.add(cursor.getString(1));
261                rowArray.add(cursor.getString(2));
262
263              } while (cursor.moveToNext());
264
265            }
266
267        //let java know that you are through with the cursor.
268        cursor.close();
269
270    } catch (SQLException e) {
271        // TODO Auto-generated catch block
272        Log.e("DB ERROR", e.toString());
273        e.printStackTrace();
274    }
275
276    //return the ArrayList containing the given row from the database.
277    return rowArray;
278
279
280    }
281
282//RETRIEVING ALL ROWS FROM THE DATABASE TABLE
283//
284//This is an example of how to retrieve all data from a database table using this class.
285//You should edit this method to suit your needs.
286//
287// the key is automatically assigned by the database
288
289public ArrayList<ArrayList<Object>> getAllRowsAsArrays(){
290
291    //create an ArrayList that will hold all of the data collected from the database
292    ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();
293
294    //this is a database call that creates a "cursor" object.
295    //the cursor object stores the information collected from the database and is used to iterate through the data.
296    Cursor cursor;
297
298    try{
299        //ask the database object to create the cursor.
300        cursor = db.query(
301                TABLE_NAME,
302                new String[]{TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO},
303                null, null, null, null, null
304                );
305
306        //move the cursor's pointer to position zero.
307        cursor.moveToFirst();
308
309        //if there is data after the current cursor position add it to the ArrayList.
310        if (!cursor.isAfterLast()){
311
312            do
313            {
314                ArrayList<Object> dataList = new ArrayList<Object>();
315
316                dataList.add(cursor.getLong(0));
317                dataList.add(cursor.getString(1));
318                dataList.add(cursor.getString(2));
319
320                dataArrays.add(dataList);
321            } 
322
323            //move the cursor's pointer up one position.
324            while (cursor.moveToNext());
325
326        }
327    }
328    catch (SQLException e){
329
330        Log.e("DB ERROR", e.toString());
331        e.printStackTrace();
332    }
333
334    //return the ArrayList that holds the data collected from the database.
335    return dataArrays;
336}
337
338}
339	
340public MBDatabaseManager(Context context) {
341    // TODO Auto-generated constructor stub
342    super(context, DATABASE_NAME, null, DATABASE_VERSION);
343}
344	
345SQLiteDatabase db = this.getWritableDatabase();