This is simple sqlite database tutorial without using External database. If you want to know how use external database in android then please click me!
To create and access a table in android using SQLite is very simple. The following method will show how to create a table and see column names of that table in android.
public void table1() { db = this.getWritableDatabase(); db.execSQL("CREATE TABLE IF NOT EXISTS table1(id INTEGER PRIMARY KEY AUTOINCREMENT , description TEXT)"); // to see colomn names in our table1 Cursor t = db.rawQuery("PRAGMA TABLE_INFO(table1)", null); if (t.moveToFirst()) { do { Log.i("", t.getString(0) + " " + t.getString(1)); } while (t.moveToNext()); } t.close(); db.close(); }And the following sample code is used to insert data into table.
public void inserIntoTable1(String desc) { db = this.getWritableDatabase(); ContentValues cv = new ContentValues(); try { db.beginTransaction(); cv.put("description", desc); db.insert("table1", null, cv); db.setTransactionSuccessful(); } catch (Exception ex) { } finally { db.endTransaction(); db.close(); } }And the following code is used to access data from our table. The method will return data as list.
public List<String> getData() { db = this.getReadableDatabase(); List<String> data = new ArrayList<String>(); Cursor c = db.rawQuery("SELECT * FROM table1", null); while (c.moveToNext()) { data.add(c.getString(1)); } c.close(); db.close(); return data; }Now we can populate those data into our listview.
ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, list); mylist = (ListView) findViewById(R.id.listView1); mylist.setAdapter(adapter);Full code. main.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="fill_parent" android:layout_height="fill_parent" android:orientation="vertical" > <TextView android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="Description" /> <EditText android:id="@+id/editText1" android:layout_width="match_parent" android:layout_height="wrap_content" > </EditText> <Button android:id="@+id/button1" android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="Save" /> <ListView android:id="@+id/listView1" android:layout_width="match_parent" android:layout_height="wrap_content" > </ListView> <LinearLayout>DBHelper.java
import java.util.ArrayList; import java.util.List; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; public class DBHelper extends SQLiteOpenHelper { final static String dbname = "Sample"; SQLiteDatabase db; public DBHelper(Context context) { super(context, dbname, null, 1); // TODO Auto-generated constructor stub } public void table1() { db = this.getWritableDatabase(); db.execSQL("CREATE TABLE IF NOT EXISTS table1(id INTEGER PRIMARY KEY AUTOINCREMENT , description TEXT)"); // to see colomn names in our table1 Cursor t = db.rawQuery("PRAGMA TABLE_INFO(table1)", null); if (t.moveToFirst()) { do { Log.i("", t.getString(0) + " " + t.getString(1)); } while (t.moveToNext()); } t.close(); db.close(); } public void inserIntoTable1(String desc) { db = this.getWritableDatabase(); ContentValues cv = new ContentValues(); try { db.beginTransaction(); cv.put("description", desc); db.insert("table1", null, cv); db.setTransactionSuccessful(); } catch (Exception ex) { } finally { db.endTransaction(); db.close(); } } public List<String> getData() { db = this.getReadableDatabase(); List<String> data = new ArrayList<String>(); Cursor c = db.rawQuery("SELECT * FROM table1", null); while (c.moveToNext()) { data.add(c.getString(1)); } c.close(); db.close(); return data; } @Override public void onCreate(SQLiteDatabase db) { } @Override public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) { // TODO Auto-generated method stub } }SQLiteActivity.java
import java.util.List; import android.app.Activity; import android.os.Bundle; import android.view.View; import android.view.View.OnClickListener; import android.widget.ArrayAdapter; import android.widget.Button; import android.widget.EditText; import android.widget.ListView; public class SQLiteActivity extends Activity { DBHelper dbhelper; Button save; EditText discription; ListView mylist; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); dbhelper = new DBHelper(this); accessTable1(); loadMyList(); } private void accessTable1() { dbhelper.table1(); discription = (EditText) findViewById(R.id.editText1); save = (Button) findViewById(R.id.button1); save.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { dbhelper.inserIntoTable1(discription.getText().toString()); loadMyList(); } }); } private void loadMyList() { List<String> list = dbhelper.getData(); ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, list); mylist = (ListView) findViewById(R.id.listView1); mylist.setAdapter(adapter); } }
Reduce your work by using SQLiteHelper.jar
sample screen shot
Hi, i've found this exactly what i needed, but my java/android compiler says that List<String> doesn't exist (using 2.3.3 API, last version of Java), any clue about that or where to find the List<String>.java or something like that so i can make this code work?, thx ind advance :3
ReplyDeleteSorry friend :( It is my problem only. Just replace "<" with "<" symbol and ">" with ">" symbol. See my updates.
DeleteThx u so much for that fast reply :3
DeleteNcyaocZsubhe Deanna Harris https://wakelet.com/wake/5d2GFcMKNbOXZDors5qlu
ReplyDeletesetzmenrocyc