i writing android application queries database 170,000 dictionary words. (it has 31 columns: _id, word, sortedword, length, , count_a, count_b, count_c, ... count_z)
here code dictionary database helper
public class dictionarydbhelper extends sqliteopenhelper { private static final string tag = "databasehelper"; // android's default system path of application database. private static string db_path = "/data/data/com.rabbitfighter.wordsleuth/databases/"; // database name private static string db_name = "dictionary.db"; // database, , context private sqlitedatabase mydatabase; private final context mycontext; // table name public static final string table_name = "dictionary"; // table columns public static final string uid = "_id"; // word public static final string column_name_word = "word"; // length public static final string column_name_word_length = "length"; // letters, a-z. public static final string column_name_count_a = "count_a"; public static final string column_name_count_b = "count_b"; public static final string column_name_count_c = "count_c"; public static final string column_name_count_d = "count_d"; public static final string column_name_count_e = "count_e"; public static final string column_name_count_f = "count_f"; public static final string column_name_count_g = "count_g"; public static final string column_name_count_h = "count_h"; public static final string column_name_count_i = "count_i"; public static final string column_name_count_j = "count_j"; public static final string column_name_count_k = "count_k"; public static final string column_name_count_l = "count_l"; public static final string column_name_count_m = "count_m"; public static final string column_name_count_n = "count_n"; public static final string column_name_count_o = "count_o"; public static final string column_name_count_p = "count_p"; public static final string column_name_count_q = "count_q"; public static final string column_name_count_r = "count_r"; public static final string column_name_count_s = "count_s"; public static final string column_name_count_t = "count_t"; public static final string column_name_count_u = "count_u"; public static final string column_name_count_v = "count_v"; public static final string column_name_count_w = "count_w"; public static final string column_name_count_x = "count_x"; public static final string column_name_count_y = "count_y"; public static final string column_name_count_z = "count_z"; /** * constructor * takes , keeps reference of passed context in order access application assets , resources. * @param context */ public dictionarydbhelper(context context) { super(context, db_name, null, 1); this.mycontext = context; } /* ------------------------ */ /* --- override methods --- */ /* ------------------------ */ /** * on creation * @param db - database */ public void oncreate(sqlitedatabase db) { //nothing here } /** * on upgrade * @param db - database * @param oldversion - old database version int * @param newversion - new database version int */ public void onupgrade(sqlitedatabase db, int oldversion, int newversion) { log.i(tag, "database " + db + " version " + oldversion + " upgraded " + newversion); } /** * on downgrade * @param db - database * @param oldversion - old database version int * @param newversion - new database version int */ public void ondowngrade(sqlitedatabase db, int oldversion, int newversion) { log.i(tag, "database " + db + " version " + oldversion + " downgraded " + newversion); } /** * creates empty database on system , rewrites own database. * */ public void createdatabase() throws ioexception{ if(checkdatabase()){ //do nothing - database exist log.i(tag, "database exists... nothing do."); }else{ //by calling method , empty database created default system path //of application gonna able overwrite database our database. this.getreadabledatabase(); try { copydatabase(); log.i(tag, "successfully copied database"); } catch (ioexception e) { log.i(tag, "error copying"); throw new error("error copying database"); } } } /** * check if database exist avoid re-copying file each time open application. * @return true if exists, false if doesn't */ private boolean checkdatabase(){ sqlitedatabase checkdb = null; try{ string mypath = db_path + db_name; checkdb = sqlitedatabase.opendatabase(mypath, null, sqlitedatabase.open_readonly); }catch(sqliteexception e){ //database doesn't exist yet. log.i(tag, "database doesn't exist yet"); } // check null db if(checkdb != null){ checkdb.close(); log.i(tag, "closed dict database"); } // return null or not null return checkdb != null; } /** * copies database local assets-folder created empty database in * system folder, can accessed , handled. * done transfering bytestream. * */ private void copydatabase() throws ioexception { //open local db input stream inputstream myinput = mycontext.getassets().open(db_name); // path created empty db string outfilename = db_path + db_name; //open empty db output stream outputstream myoutput = new fileoutputstream(outfilename); //transfer bytes input file output file byte[] buffer = new byte[1024]; int length; while ((length = myinput.read(buffer))>0){ myoutput.write(buffer, 0, length); } //close streams myoutput.flush(); myoutput.close(); myinput.close(); } public void opendatabase() throws sqlexception { //open database string mypath = db_path + db_name; mydatabase = sqlitedatabase.opendatabase(mypath, null, sqlitedatabase.open_readonly); } @override public synchronized void close() { if(mydatabase != null) { log.i(tag, "closing dictionary database"); mydatabase.close(); } super.close(); } /* -------------------------- */ /* --- database retrieval --- */ /* -------------------------- */ /** * anagrams in database * @return number of anagrams */ public arraylist<result> getmatches( // params. yeah, know... int count_a, int count_b, int count_c, int count_d, int count_e, int count_f, int count_g, int count_h, int count_i, int count_j, int count_k, int count_l, int count_m, int count_n, int count_o, int count_p, int count_q, int count_r, int count_s, int count_t, int count_u, int count_v, int count_w, int count_x, int count_y, int count_z, int count_wildcards ) { // database helper access everything... sqlitedatabase db = this.getwritabledatabase(); // list hold matches arraylist<result> resultlist = new arraylist<>(); // here columns care in our search string[] columns = { uid, column_name_word, column_name_word_length, column_name_count_a, column_name_count_b, column_name_count_c, column_name_count_d, column_name_count_e, column_name_count_f, column_name_count_g, column_name_count_h, column_name_count_i, column_name_count_j, column_name_count_k, column_name_count_l, column_name_count_m, column_name_count_n, column_name_count_o, column_name_count_p, column_name_count_q, column_name_count_r, column_name_count_s, column_name_count_t, column_name_count_u, column_name_count_v, column_name_count_w, column_name_count_x, column_name_count_y, column_name_count_z, }; string[] selectionargs = null; // selection (where ...) string selection = column_name_count_a +"<="+ count_a + " , " + column_name_count_b +"<="+ count_b + " , " + column_name_count_c +"<="+ count_c + " , " + column_name_count_d +"<="+ count_d + " , " + column_name_count_e +"<="+ count_e + " , " + column_name_count_f +"<="+ count_f + " , " + column_name_count_g +"<="+ count_g + " , " + column_name_count_h +"<="+ count_h + " , " + column_name_count_i +"<="+ count_i + " , " + column_name_count_j +"<="+ count_j + " , " + column_name_count_k +"<="+ count_k + " , " + column_name_count_l +"<="+ count_l + " , " + column_name_count_m +"<="+ count_m + " , " + column_name_count_n +"<="+ count_n + " , " + column_name_count_o +"<="+ count_o + " , " + column_name_count_p +"<="+ count_p + " , " + column_name_count_q +"<="+ count_q + " , " + column_name_count_r +"<="+ count_r + " , " + column_name_count_s +"<="+ count_s + " , " + column_name_count_t +"<="+ count_t + " , " + column_name_count_u +"<="+ count_u + " , " + column_name_count_v +"<="+ count_v + " , " + column_name_count_w +"<="+ count_w + " , " + column_name_count_x +"<="+ count_x + " , " + column_name_count_y +"<="+ count_y + " , " + column_name_count_z +"<="+ count_z; string groupby = null; string having = null; string orderby = null; // query database cursor cursor = db.query( table_name, // table name columns, // columns selection, // selection selectionargs, // selection arguments groupby, // group by... having, // having orderby // order ); while (cursor.movetonext()) { int columnresult = cursor.getcolumnindex(column_name_word); // add result list return resultlist.add(new result(cursor.getstring(columnresult))); } cursor.close(); // return list return resultlist; } }//eof and here query dictionary in class:
how can query wildcards given current setup? new sql, suggestions appreciated... looking parameter clause allow 2 wildcards, each of a-z.
here example of how i'm doing lookup now, search service:
dictionarydbhelper helper = dictionarydbhelper(this); arraylist<result> matches = helper.getmatches( this.getquery().getcount_a(), this.getquery().getcount_b(), this.getquery().getcount_c(), this.getquery().getcount_d(), this.getquery().getcount_e(), this.getquery().getcount_f(), this.getquery().getcount_g(), this.getquery().getcount_h(), this.getquery().getcount_i(), this.getquery().getcount_j(), this.getquery().getcount_k(), this.getquery().getcount_l(), this.getquery().getcount_m(), this.getquery().getcount_n(), this.getquery().getcount_o(), this.getquery().getcount_p(), this.getquery().getcount_q(), this.getquery().getcount_r(), this.getquery().getcount_s(), this.getquery().getcount_t(), this.getquery().getcount_u(), this.getquery().getcount_v(), this.getquery().getcount_w(), this.getquery().getcount_x(), this.getquery().getcount_y(), this.getquery().getcount_z(),0 // wildcards not in use yet )
Comments
Post a Comment