Increment and insert Integer values on Android SQLite Database

Question

Hello, Im trying to insert values on Android SQLite Database. The question is, Iḿ trying to insert a word, the table has 3 columns, ID, WORD, COUNT.

When I insert a word in the database, some method will verify if this word exists on the database. If yes, it will increment the value of COUNT for this word.

Example. I have a word "Question" in the database with COUNT value 1, and i want to insert it again, the method will find this word and if I want to insert "qUeSTion" it doesnt matter, it will return "Question" for me when I retrieve the data, and it will increment the value of COUNT from 1 to 2. Got it??

SO here is my code. I'm having problems when try to do this, this verification. I dont know what method to use, etc. I'm using SQLiteStatement for insert. But the methods which offers doesn work. Any Idea of what use?

Thanks.

Class DataHelper

public class DataHelper {

private static final String DATABASE_NAME = "sms.db";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_NAME = "words";

private Context context;
private SQLiteDatabase db;

private SQLiteStatement insertStmt;
private static final String INSERT = "insert into " 
  + TABLE_NAME + "(word) values (?)";

public DataHelper(Context context) {
  this.context = context;
  OpenHelper openHelper = new OpenHelper(this.context);
  this.db = openHelper.getWritableDatabase();
  this.insertStmt = this.db.compileStatement(INSERT);
}

public long insert(String word) {
  this.insertStmt.bindString(1, word);              
  return this.insertStmt.executeInsert();
}

public List<String> selectAll() {
  List<String> list = new ArrayList<String>();
  Cursor cursor = this.db.query(TABLE_NAME, new String[] { "word"}, 
    null, null, null, null, "id desc");
  if (cursor.moveToFirst()) {
     do {
        list.add(cursor.getString(0)); 
     } while (cursor.moveToNext());
  }
  if (cursor != null && !cursor.isClosed()) {
     cursor.close();
  }
  return list;
}
}

Class SMS This is the class where I get the words and insert ond the database

public class SMS extends Activity { 

private DataHelper dh;      
private static TextView txtView;                
final Uri CONTENT_URI = Uri.parse("content://sms/sent");
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    this.dh = new DataHelper(this);        
    txtView =    (TextView)findViewById(R.id.txtView);                   
        Cursor cursor = getContentResolver().query(CONTENT_URI, null, null, null, null);                                    
        String body;                                        
        if(cursor.moveToFirst()){
            body = cursor.getString(cursor.getColumnIndexOrThrow("body")).toString();
            if(body == ""){
                Toast.makeText(getBaseContext(), "There is no words to save!", Toast.LENGTH_LONG).show();
            }
                else{                               
                    StringTokenizer st = new StringTokenizer(body);
                    while(st.hasMoreTokens()){                                  
                         this.dh.insert(st.nextToken());                             
                         Toast.makeText(getBaseContext(), "The set of words has been updated!", Toast.LENGTH_LONG).show();                                                                                                              
                    }
                    List<String> words = this.dh.selectAll();
                    StringBuilder sb = new StringBuilder();
                    sb.append("Set of words:\n\n");
                    for (String w : words) {
                        sb.append(w + " ");
                    }                       
                    Log.d("EXAMPLE", "words size - " + words.size());                       
                    txtView.setText(sb.toString());
                }
        }             
}
    }   

Answer 1

Answer by @MPelletier

You should also rethink your column name "Count", as it is a reserved word for a function.