user3458008 user3458008 - 1 year ago 139
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download