user3458008 user3458008 - 5 months ago 20
SQL Question

How to use select and where clause in sqlite Android?

I have the following code. How do I get a particular value from the list using select ?
Like if I want to use

select * from table_name where Name=Sam;
and get the result value in a string.

How do I do it in SQLITE based on following example?

package com.example.sqlitedemo;
public class MainActivity extends Activity {


LinearLayout Linear;
SQLiteDatabase mydb;
private static String DBNAME = "PERSONS.db"; // THIS IS THE SQLITE
DATABASE FILE NAME.
private static String TABLE = "MY_TABLE"; // THIS IS <span id="IL_AD9" class="IL_AD">THE TABLE</span> NAME
TextView textView1;

@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
textView1 = (TextView)findViewById(R.id.textView1);

Toast.makeText(getApplicationContext(), "Creating table.", Toast.LENGTH_SHORT).show();

dropTable(); // DROPPING THE TABLE.
createTable();

insertIntoTable();
selectTable();

}
public void showTableValues(){
try{
int rowid=1;
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);

// Cursor allrows = mydb.rawQuery("SELECT * FROM "+ TABLE, null);
}
finally{

}
}
// CREATE TABLE IF NOT EXISTS
public void createTable(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
mydb.execSQL("CREATE TABLE IF NOT EXISTS "+ TABLE +" (ID INTEGER PRIMARY KEY, NAME TEXT, PLACE TEXT);");
mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error in creating table", Toast.LENGTH_LONG);
}
}
// THIS FUNCTION INSERTS DATA TO THE DATABASE
public void insertIntoTable(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('CODERZHEAVEN','GREAT INDIA')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('ANTHONY','USA')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('SHUING','JAPAN')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('JAMES','INDIA')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('SOORYA','INDIA')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('MALIK','INDIA')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE) VALUES('myname','America')");
mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error in inserting into table", Toast.LENGTH_LONG);
}
}
public void selectTable(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
Cursor c = mydb.rawQuery("select * from TABLE where NAME=JAMES", null);

String name = c.getString(0);
String place = c.getString(1);
textView1.setText(name);
mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error selecting", Toast.LENGTH_LONG);
}

}

public void dropTable(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
mydb.execSQL("DROP TABLE " + TABLE);

mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error encountered while dropping.", Toast.LENGTH_LONG);
}
}
}

Answer

your variable mydb

Cursor c = mydb.rawQuery("select * from MY_TABLE where NAME=MALIK");
String name = c.getString(c.getColumnIndex("NAME"));
String place = c.getString(c.getColumnIndex("PLACE"));

Now you have access to your two columns Name and Place to do as you please as according to the data you have provided from your code snippet.