· 8 years ago · Aug 29, 2018, 06:06 PM
1/*
2 * Copyright (C) 2016 Google Inc.
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 * http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17package com.android.example.wordlistsqlsearchable;
18
19import android.content.ContentValues;
20import android.content.Context;
21import android.database.Cursor;
22import android.database.DatabaseUtils;
23import android.database.sqlite.SQLiteDatabase;
24import android.database.sqlite.SQLiteOpenHelper;
25import android.util.Log;
26
27import com.BoardiesITSolutions.AndroidMySQLConnector.Connection;
28import com.BoardiesITSolutions.AndroidMySQLConnector.Exceptions.InvalidSQLPacketException;
29import com.BoardiesITSolutions.AndroidMySQLConnector.Exceptions.MySQLConnException;
30import com.BoardiesITSolutions.AndroidMySQLConnector.Exceptions.MySQLException;
31import com.BoardiesITSolutions.AndroidMySQLConnector.Statement;
32
33import java.io.IOException;
34
35/**
36 * Open helper for the list of words database.
37 */
38public class WordListOpenHelper /* extends SQLiteOpenHelper */ {
39
40 private static final String TAG = WordListOpenHelper.class.getSimpleName();
41
42 // Declaring all these as constants makes code a lot more readable and looking like SQL.
43
44 // Version has to be 1 first time or app will crash.
45 private static final int DATABASE_VERSION = 1;
46 private static final String WORD_LIST_TABLE = "NotesTable";
47 private static final String DATABASE_NAME = "notesdb";
48
49 // Column names...
50 public static final String KEY_ID = "id";
51 public static final String KEY_TITLE = "title";
52
53 // ... and a string array of columns.
54 private static final String[] COLUMNS =
55 {KEY_ID, KEY_TITLE};
56
57 // Build the SQL query that creates the table.
58 private static final String WORD_LIST_TABLE_CREATE =
59 "CREATE TABLE " + WORD_LIST_TABLE + " (" +
60 KEY_ID + " INTEGER PRIMARY KEY, " + // will auto-increment if no value passed
61 KEY_TITLE + " TEXT );";
62
63// next for local variant
64// private SQLiteDatabase mWritableDB;
65// private SQLiteDatabase mReadableDB;
66 private Connection mWriteableDB, mReadableDB;
67
68 public WordListOpenHelper(Context context) {
69 // super(context, DATABASE_NAME, null, DATABASE_VERSION);
70 Log.d(TAG, "Construct WordListOpenHelper");
71 mWriteableDB= new Connection("192.168.0.12", "boot", "sed123sed", 3307, "notesdb", new MainActivity.MyConnectionHandler());
72 mReadableDB = new Connection("192.168.0.12", "boot", "sed123sed", 3307, "notesdb", new MainActivity.MyConnectionHandler());
73 }
74
75 // @Override
76 public void onCreate(SQLiteDatabase db) {
77 db.execSQL(WORD_LIST_TABLE_CREATE);
78 fillDatabaseWithData(db);
79 }
80
81 /**
82 * Adds the initial data set to the database.
83 * According to the docs, onCreate for the open helper does not run on the UI thread.
84 *
85 * @param db Database to fill with data since the member variables are not initialized yet.
86 */
87 public void fillDatabaseWithData(SQLiteDatabase db) {
88
89 String[] words = {"Android", "Adapter", "ListView", "AsyncTask", "Android Studio",
90 "SQLiteDatabase", "SQLOpenHelper", "Data model", "ViewHolder",
91 "Android Performance", "OnClickListener"};
92
93 // Create a container for the data.
94 ContentValues values = new ContentValues();
95
96 for (int i=0; i < words.length; i++) {
97 // Put column/value pairs for current row into the container.
98 values.put(KEY_TITLE, words[i]); // put() overrides existing values.
99 // Insert the row.
100 db.insert(WORD_LIST_TABLE, null, values);
101 }
102 }
103
104 Connection getReadableDatabase() {
105 return mReadableDB;
106 }
107 Connection getWritableDatabase() {
108 return mWriteableDB;
109 }
110 public Cursor search(String searchString) {
111 String[] columns = new String[]{KEY_TITLE};
112 String where = KEY_TITLE + " LIKE ?";
113 searchString = "%" + searchString + "%";
114 String[] whereArgs = new String[]{searchString};
115
116 Cursor cursor = null;
117 try {
118 if (mReadableDB == null) {
119 mReadableDB = getReadableDatabase();
120 }
121 Statement s = new Statement();
122 s.execute("SELECT * FROM NotesTable", );
123 cursor = mReadableDB.query(WORD_LIST_TABLE, columns, where, whereArgs, null, null, null);
124 } catch (Exception e) {
125 Log.d(TAG, "SEARCH EXCEPTION! " + e); // Just log the exception
126 }
127 return cursor;
128 }
129
130
131 /**
132 * Queries the database for an entry at a given position.
133 *
134 * @param position The Nth row in the table.
135 * @return a WordItem with the requested database entry.
136 */
137 public WordItem query(int position) {
138 String query = "SELECT * FROM " + WORD_LIST_TABLE +
139 " ORDER BY " + TITLE + " ASC " +
140 "LIMIT " + position + ",1";
141
142 Cursor cursor = null;
143 WordItem entry = new WordItem();
144
145 try {
146 if (mReadableDB == null) {
147 mReadableDB = getReadableDatabase();
148 }
149 cursor = mReadableDB.rawQuery(query, null);
150 cursor.moveToFirst();
151 entry.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID)));
152 entry.setWord(cursor.getString(cursor.getColumnIndex(KEY_TITLE)));
153 } catch (Exception e) {
154 Log.d(TAG, "QUERY EXCEPTION! " + e); // Just log the exception
155 } finally {
156 // Must close cursor and db now that we are done with it.
157 cursor.close();
158 return entry;
159 }
160 }
161
162 /**
163 * Gets the number of rows in the word list table.
164 *
165 * @return The number of entries in WORD_LIST_TABLE.
166 */
167 public long count() {
168 if (mReadableDB == null) {
169 mReadableDB = getReadableDatabase();
170 }
171 return DatabaseUtils.queryNumEntries(mReadableDB, WORD_LIST_TABLE);
172 }
173
174 /**
175 * Adds a single word row/entry to the database.
176 *
177 * @param word New word.
178 * @return The id of the inserted word.
179 */
180 public long insert(String word) {
181 long newId = 0;
182 ContentValues values = new ContentValues();
183 values.put(KEY_TITLE, word);
184 try {
185 if (mWritableDB == null) {
186 mWritableDB = getWritableDatabase();
187 }
188 newId = mWritableDB.insert(WORD_LIST_TABLE, null, values);
189 } catch (Exception e) {
190 Log.d(TAG, "INSERT EXCEPTION! " + e);
191 }
192 return newId;
193 }
194
195 /**
196 * Updates the word with the supplied id to the supplied value.
197 *
198 * @param id Id of the word to update.
199 * @param word The new value of the word.
200 * @return the number of rows affected or -1 of nothing was updated.
201 */
202 public int update(int id, String word) {
203 int mNumberOfRowsUpdated = -1;
204 try {
205 if (mWritableDB == null) {
206 mWritableDB = getWritableDatabase();
207 }
208 ContentValues values = new ContentValues();
209 values.put(KEY_TITLE, word);
210
211 mNumberOfRowsUpdated = mWritableDB.update(WORD_LIST_TABLE, //table to change
212 values, // new values to insert
213 KEY_ID + " = ?", // selection criteria for row (in this case, the _id column)
214 new String[]{String.valueOf(id)}); //selection args; the actual value of the id
215
216 } catch (Exception e) {
217 Log.d (TAG, "UPDATE EXCEPTION! " + e);
218 }
219 return mNumberOfRowsUpdated;
220 }
221
222 /**
223 * Deletes one entry identified by its id.
224 *
225 * @param id ID of the entry to delete.
226 * @return The number of rows deleted. Since we are deleting by id, this should be 0 or 1.
227 */
228 public int delete(int id) {
229 int deleted = 0;
230 try {
231 if (mWritableDB == null) {
232 mWritableDB = getWritableDatabase();
233 }
234 deleted = mWritableDB.delete(WORD_LIST_TABLE, //table name
235 KEY_ID + " =? ", new String[]{String.valueOf(id)});
236 } catch (Exception e) {
237 Log.d (TAG, "DELETE EXCEPTION! " + e); }
238 return deleted;
239 }
240
241 /**
242 * Called when a database needs to be upgraded. The most basic version of this method drops
243 * the tables, and then recreates them. All data is lost, which is why for a production app,
244 * you want to back up your data first. If this method fails, changes are rolled back.
245 *
246 * @param db
247 * @param oldVersion
248 * @param newVersion
249 */
250 @Override
251 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
252 Log.w(WordListOpenHelper.class.getName(),
253 "Upgrading database from version " + oldVersion + " to "
254 + newVersion + ", which will destroy all old data");
255 db.execSQL("DROP TABLE IF EXISTS " + WORD_LIST_TABLE);
256 onCreate(db);
257
258
259 }
260
261 class MyConnectionHandler implements com.BoardiesITSolutions.AndroidMySQLConnector.IConnectionInterface
262 {
263 public void actionCompleted() {
264 Log.i(TAG, "action completed");
265 }
266 public void handleInvalidSQLPacketException(InvalidSQLPacketException ex)
267 {
268 Log.e(TAG, "EX_1");
269 }
270 public void handleMySQLException(MySQLException ex) {
271 Log.e(TAG, "EX_2");
272 }
273 public void handleIOException(IOException ex) {
274 Log.e(TAG, "EX_3");
275 }
276 public void handleMySQLConnException(MySQLConnException ex) {
277 Log.e(TAG, "EX_4");
278 }
279 public void handleException(Exception exception) {
280 Log.e(TAG, "EX_5");
281 }
282 }
283}