SQLite DB Query

Question

I am building an SQLite DB. One of the tables consists of 2 columns - term and definition.

My question is : How can I query the DB, in order the pair term-definition to be returned in order to be able to insert the data in the activity after that (the term and data are in ExpandableListView, the term is the Key, the data - the value).

Here is the code of the data source so far:

public class TermDataSource extends DAO {

//constants
public static final String TABLE_NAME = "terms";
public static final String TERM = "term";
public static final String DEFINITION = "definition";

//columns in the table
public static final int FIELD_ID_ID = 0;
public static final int FIELD_ID_TERM = 1;
public static final int FIELD_ID_DEFINITION = 2;

public TermDataSource (Context context){
    super(context);
}

private String [] selectFields = {_ID, TERM, DEFINITION};

public Cursor getTermsData(){
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(TABLE_NAME, selectFields, null, null, null, null, null);

    return cursor;
}
public List<Term> getTerms(){

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(TABLE_NAME, selectFields, null, null, null, null, null);
    List<Term> terms = new ArrayList<Term>();

    if(cursor!=null){

        Term term = null;
        while(cursor.moveToNext()){
            term = getTermFromCursor(cursor);
            terms.add(term);
        }
        cursor.close();
    }
    db.close();
    return terms;
}

private Term getTermFromCursor (Cursor cursor){
    Term term = new Term();
    term.setTermId(cursor.getInt(FIELD_ID_ID));
    term.setTerm(cursor.getString(FIELD_ID_TERM));
    term.setDefinition(cursor.getString(FIELD_ID_DEFINITION));

    return term;
}
}

Means do you want to get data using term and definition for specific value ? — Piyush
– Piyush, Commented May 20, 2016 at 4:33

Janki Gadhiya · Accepted Answer · 2016-05-20 04:48:52Z

2

Create a folder res>raw and put youfile.csv in that folder.

Use this method to insert data in Your Database from CSV file.

public void insertCSVData(Activity activity, InputStream is, String tableName) {

    String colunmNames = null, str1 = null;
    open();

    try {

        BufferedReader buffer = new BufferedReader(new InputStreamReader(is));
        String line = "";

        String str2 = ");";

        db.beginTransaction();

        int i = 0;
        while ((line = buffer.readLine()) != null) {
            i++;
            if (i == 1) {
                colunmNames = line;
                str1 = "INSERT INTO " + tableName + " (" + colunmNames + ") values (";
            } else {

                StringBuilder sb = new StringBuilder(str1);
                String[] str = line.split(",");

                for (int h = 0; h < str.length; h++) {

                    if (h == str.length - 1) {
                        sb.append("'" + str[h] + "'");
                    } else {
                        sb.append("'" + str[h] + "',");
                    }
                }

                sb.append(str2);
                db.execSQL(sb.toString());
            }
        }

        db.setTransactionSuccessful();
        db.endTransaction();

    } catch (Exception e) {

        close();

        e.printStackTrace();
    }

    close();
}

Call this method by the below code :

insertCSVData(Activity.this, getResources().openRawResource(R.raw.yourfile),"Your Table Name");

answered May 20, 2016 at 4:48

Janki Gadhiya

4,5103 gold badges31 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

VyaraG Over a year ago

Thanks a lot! I'll come back with a feedback after testing :)

Janki Gadhiya Over a year ago

My pleasure.. Happy coding..!!

Collectives™ on Stack Overflow

SQLite DB Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related