Related
Hey everyone,
I'm trying to get started with SQLite. My app keeps force closing on this activity:
Code:
public class ViewActivity extends Activity {
private TextView company;
private BillMeDB dh;
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.all);
company = (TextView) findViewById(R.id.Company1);
BillMeDB db = new BillMeDB(this);
db.open();
long id;
id = db.insertEntry("Power", "AEP", "item", "2010.9.15", "w2", "2010.9.24", "2011.1.1", 75.77);
id = db.insertEntry("Cell Phone", "Verizon", "item", "2010.9.15", "w2", "2010.9.24", "2011.1.1", 185.45);
Cursor c = db.getEntry(1);
if (c.moveToFirst())
company.setText(c.getString(1));
db.close();
// Cursor c = BillMeLoad.database.fetchAllEntries();
company.setText(this.dh.getEntry(0).toString());
}
}
I can't figure out why though. It blows up(I think) when it starts creating a DB. Here's my DB file - I have no idea where I've gone wrong, but apparently something isn't right. Can someone give me a hand?
My DB File:
Code:
package com.caleb.billme;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class BillMeDB {
public static final String KEY_BILLNAME = "billname";
public static final String KEY_COMPANY = "company";
public static final String KEY_ROWID = "_id";
public static final String KEY_ITEM = "item";
public static final String KEY_DUEDATE = "duedate";
public static final String KEY_REMFREQ = "remfreq";
public static final String KEY_REMDATE = "remdate";
public static final String KEY_ENDDATE = "enddate";
public static final String KEY_DUEAMNT = "dueamnt";
private static final String TAG = "BillMeDB";
private DatabaseHelper DBHelper;
private SQLiteDatabase db;
private static final String DATABASE_CREATE =
"CREATE TABLE BillTable (_id integer primary key autoincrement, "
+ "billname text not null, company text not null, item text not null, " +
"duedate text not null, remfreq text not null, " +
"remdate text not null, enddate text not null, dueamnt double not null)";
private static final String DATABASE_NAME = "Data";
private static final String DATABASE_TABLE = "BillTable";
private static final int DATABASE_VERSION = 2;
private final Context mCtx;
private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS BillTable");
onCreate(db);
}
}
public BillMeDB(Context ctx) {
mCtx = ctx;
}
public BillMeDB open() throws SQLException {
db = DBHelper.getWritableDatabase();
return this;
}
public void close() {
DBHelper.close();
}
public long insertEntry(String billname, String company, String item, String duedate, String remfreq, String remdate, String enddate, double dueamnt ) {
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_BILLNAME, billname);
initialValues.put(KEY_COMPANY, company);
initialValues.put(KEY_ITEM, item);
initialValues.put(KEY_DUEDATE, duedate);
initialValues.put(KEY_REMFREQ, remfreq);
initialValues.put(KEY_REMDATE, remdate);
initialValues.put(KEY_ENDDATE, enddate);
initialValues.put(KEY_DUEAMNT, dueamnt);
return db.insert(DATABASE_TABLE, null, initialValues);
}
public boolean deleteAllEntries() {
return db.delete(DATABASE_TABLE, null, null) > 0;
}
public boolean deleteEntry(long rowId) {
return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}
public Cursor getAllEntries() {
return db.query(DATABASE_TABLE, new String[] {
KEY_ROWID, KEY_BILLNAME,
KEY_COMPANY, KEY_ITEM,
KEY_DUEDATE, KEY_REMFREQ,
KEY_REMDATE, KEY_ENDDATE,
KEY_DUEAMNT},
null, null, null, null, null);
}
public Cursor getEntry(long rowId) throws SQLException{
Cursor c = db.query(DATABASE_TABLE, new String[] {
KEY_ROWID,
KEY_BILLNAME }, KEY_ROWID + "=" + rowId,
null, null, null, null);
if (c != null)
c.moveToFirst();
return c;
}
public boolean updateEntry(long rowId, String billname, String company, String item,
String duedate, String remfreq, String remdate, String enddate, double dueamnt) {
ContentValues args = new ContentValues();
args.put(KEY_BILLNAME, billname);
args.put(KEY_COMPANY, company);
args.put(KEY_ITEM, item);
args.put(KEY_DUEDATE, duedate);
args.put(KEY_REMFREQ, remfreq);
args.put(KEY_REMDATE, remdate);
args.put(KEY_ENDDATE, enddate);
args.put(KEY_DUEAMNT, dueamnt);
return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}
}
in BillMeDB.java change
Code:
public BillMeDB(Context ctx) {
mCtx = ctx;
}
to
Code:
public BillMeDB(Context ctx) {
mCtx = ctx;
DBHelper = new DatabaseHelper(mCtx);
}
also in ViewActivity.java
Code:
company.setText(this.dh.getEntry(0).toString());
i guess you are trying to display both the KEY_ROWID and KEY_BILLNAME but i think you can't treat a cursor like that. you must first use getEntry(rowId) to get the cursor and then use getString(columnIndex) like you did with getEntry(1). maybe something like
(you must do this before closing the db)
Code:
c = db.getEntry(2);
if (c.moveToFirst())
company.setText(c.getString(0) + "\t" + c.getString(1));
I'm new to using cursors to obtain data from the device. I'm working on a music player (see market link in signature) and I need to be able to list (and eventually play) the music found on the sdcard. I have some code, but I can't seem to get it to work
Here's the code I found on a website, but it leads to a force-close:
public class TestingData extends Activity {
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
TextView view = (TextView) findViewById(R.id.hello);
String[] projection = new String[] {
MediaStore.MediaColumns.DISPLAY_NAME
, MediaStore.MediaColumns.DATE_ADDED
, MediaStore.MediaColumns.MIME_TYPE
};
Cursor mCur = managedQuery(Media.EXTERNAL_CONTENT_URI,
projection, null, null, null
);
mCur.moveToFirst();
while (mCur.isAfterLast() == false) {
for (int i=0; i<mCur.getColumnCount(); i++) {
view.append("n" + mCur.getString(i));
}
mCur.moveToNext();
}
}
}
Here's my attempt at fixing it, which still leads to a force-close:
public class test3 extends Activity {
TextView view = (TextView) findViewById(R.id.text1);
ListView list;
private ArrayAdapter<String> adapter;
String[] projection = new String[] {
MediaStore.MediaColumns.DISPLAY_NAME
, MediaStore.MediaColumns.DATE_ADDED
, MediaStore.MediaColumns.MIME_TYPE
};
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
list = (ListView)findViewById(R.id.list);
ArrayList<String> _list = new ArrayList<String>(Arrays.asList(projection));
adapter = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,_list);
list.setAdapter(adapter);
Cursor mCur = managedQuery(Media.EXTERNAL_CONTENT_URI,
projection, null, null,
MediaStore.MediaColumns.DISPLAY_NAME + "ASC"
);
mCur.moveToFirst();
while (mCur.isAfterLast() == false) {
for (int i=0; i<mCur.getColumnCount(); i++) {
view.append("n" + mCur.getString(i));
}
mCur.moveToNext();
}
}
}
What am I doing wrong? Both codes lead to a force-close and I can't think of anything else to do. Thanks in advance.
did you set the correct permissions in the android manifest?
*slaps hand to forehead* I always forget about the manifest. Lol. Ummmm....what all am I supposed to put in there for these codes? Do both codes look like they would accomplish the same thing?
Well, I've written hundreds of Cursors in Android and I don't run my loop like you do, so, as a suggestion:
Code:
Cursror c = yada, yada;
if(c.moveToFirst()) {
do {
// TO DO HERE...
} while(c.moveToNext());
}
c.close();
Never had a problem.
Awesome! Thanks. Ill try it when I get a chance
Hello Boys,
I am a new Android developer and I'm developing an app with the API of Google Maps.
Into an area of the map I place many markers.
The application works correctly, but the map scroolling and the map zoom isn't quick, everything goes slow.
The marker that I have included in the map is in the "png" format image, and his weighs is approximately 600 bytes.
it is possible that many marker object cause low map scrool?
this is the code of my APP:
Code:
plublic class IDC extends MapActivity {
private LocationManager locationManager;
private LocationListener locationListener;
private MapController mc;
private MapView mapView;
private String myPosition;
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
String errore="";
myPosition="";
try{
mapView = (MapView) findViewById(R.id.mapview);
mc = mapView.getController();
locationManager = (LocationManager) getSystemService(Context.LOCATION_SERVICE);
locationListener = new GPSLocationListener();
//getMyLocation();
MyDBHelper myDB = new MyDBHelper(IDS.this);
Cursor cursor= myDB.query(new String[] { "x", "y", "y2", "w", "k", "latitude", "longitude"});
//Log.i("NOMI", "TOT. NOMI"+cursor.getCount());
List<Overlay> mapOverlays = mapView.getOverlays();
Drawable drawable = this.getResources().getDrawable(R.drawable.mm_20_blue);
MyItemizedOverlay itemizedoverlay = new MyItemizedOverlay(drawable,IDS.this);
List<Address> address = new ArrayList<Address>();
Log.i("TOT TUPLE", " = "+cursor.getCount());
while(cursor.moveToNext()){
String s= cursor.getString(0);
errore=s;
String nome[]=s.split("-");
// Log.i("Pos Colonna NOME", ""+cursor.getColumnIndex("nome"));
// Log.i("Pos. in Colonna", ""+cursor.getString(0));
//address.addAll(gc.getFromLocationName(nome[1], 1));
//Address a= address.get(address.size()-1);
String la=cursor.getString(5);
String lo=cursor.getString(6);
double latitude= Double.parseDouble(la);
double longitude= Double.parseDouble(lo);
int lan= (int)(latitude*1E6);
int lon= (int)(longitude*1E6);
GeoPoint point = new GeoPoint(lan, lon);
String tel1=cursor.getString(1);
String tel2=cursor.getString(2);
String mail=cursor.getString(4);
String web=cursor.getString(3);
String info[]= {tel1,tel2,nome[1],web,mail};
MyOverlayItem overlayitem = new MyOverlayItem(point, "Hello", nome[0], info);
//mc.animateTo(point);
itemizedoverlay.addOverlay(overlayitem);
mapOverlays.add(itemizedoverlay);
}
mapView.setBuiltInZoomControls(true);
mc.setZoom(6);
}catch (Exception e) {
e.printStackTrace();
}
}
}
Code:
public class MyItemizedOverlay extends ItemizedOverlay {
private ArrayList<OverlayItem> mOverlays = new ArrayList<OverlayItem>();
private Context mContext;
private CustomizeDialog customizeDialog;
public MyItemizedOverlay(Drawable defaultMarker) {
super(boundCenterBottom(defaultMarker));
}
public MyItemizedOverlay(Drawable defaultMarker, Context context) {
super(boundCenterBottom(defaultMarker));
mContext = context;
}
protected boolean onTap(int index)
MyOverlayItem item = (MyOverlayItem) mOverlays.get(index);
customizeDialog = new CustomizeDialog(mContext);
customizeDialog.setPersonalText(item.getSnippet());
String []info= item.getInfo();
customizeDialog.setT1(info[0]);
customizeDialog.setT2(info[1]);
customizeDialog.setA(info[2]);
customizeDialog.setW(info[3]);
customizeDialog.setM(info[4]);
customizeDialog.show();
return true;
}
protected OverlayItem createItem(int i) {
return mOverlays.get(i);
}
public int size() {
return mOverlays.size();
}
public void addOverlay(OverlayItem overlay) {
mOverlays.add(overlay);
populate();
}
}
what is the problem??....PLEASE, HELP ME!!
I have a large data to load from JSON.
I have implemented a custom list view by following a tutorial, now since the data is huge I want it load as the user scrolls.
This is my LoadRestaurant class code which is inside the main activity.
Code:
class LoadRestaurants extends AsyncTask<String, String, String> {
//Show Progress Dialog
@Override
protected void onPreExecute() {
super.onPreExecute();
pDialog = new ProgressDialog(SearchAll.this);
pDialog.setMessage("Loading All Restaurants...");
pDialog.setIndeterminate(false);
pDialog.setCancelable(false);
pDialog.show();
}
protected String doInBackground(String... arg) {
//building parameters
List<NameValuePair> params = new ArrayList<NameValuePair>();
//Getting JSON from URL
String json = jsonParser.makeHttpRequest(URL_RESTAURANT_LIST, "GET", params);
//Log Cat Response Check
Log.d("Areas JSON: ", "> " + json);
try {
restaurants = new JSONArray(json);
if (restaurants != null) {
//loop through all restaurants
for (int i = 0; i < restaurants.length(); i++) {
JSONObject c = restaurants.getJSONObject(i);
//Storing each json object in the variable.
String id = c.getString(TAG_ID);
String name = c.getString(TAG_NAME);
String location = c.getString(TAG_LOCATION);
String rating = c.getString(TAG_RATING);
//Creating New Hashmap
HashMap<String, String> map = new HashMap<String, String>();
//adding each child node to Hashmap key
map.put(TAG_ID, id);
map.put(TAG_NAME, name);
map.put(TAG_LOCATION, location);
map.put(TAG_RATING, rating);
//adding HashList to ArrayList
restaurant_list.add(map);
}
}
} catch (JSONException e) {
e.printStackTrace();
}
return null;
}
protected void onPostExecute(String file_url) {
//dismiss the dialog
pDialog.dismiss();
//Updating UI from the Background Thread
runOnUiThread(new Runnable() {
@Override
public void run() {
ListAdapter adapter = new SimpleAdapter(
SearchAll.this, restaurant_list,
R.layout.listview_restaurants, new String[]{
TAG_ID, TAG_NAME, TAG_LOCATION, TAG_RATING}, new int[]{
R.id.login_id, R.id.restaurant_name, R.id.address, R.id.rating});
setListAdapter(adapter);
ListView lv = getListView();
lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
// Bundle bundle = new Bundle();
Intent intent = new Intent(SearchAll.this, RestaurantProfile.class);
String loginId = ((TextView) view.findViewById(R.id.login_id)).getText().toString();
intent.putExtra("login_id", loginId);
startActivity(intent);
}
});
}
});
}
}
}
I want to load around 20 restaurants and then it auto loads another 20 as soon as user reaches the end of first 20.
There are lots of tutorials online but its confusing to implement.
Please help me out!
The custom ListView, support for automatic loading you can try https://github.com/chrisbanes/Android-PullToRefresh
Hello,
I create that thread to present you a tutorial learning you to save data with SQLite on Android. This tutorial is also available in video on Youtube :
Learn to save data with SQLite on Android
On Android, there are several solutions to persist data between users’ sessions. One solution is to use a relational database to persist data and then to be able to query easily these data. In standard, Android SDK comes with a SQLite implementation. Biggest advantage of SQLite integration to Android OS is the fact that there is no need to to setup the database. So, no administration of this database. SQLite is embedded in standard and each application can have its SQLite database.
The only job that developers must make is to define SQL tables and statements for creating and updating data. Access to an SQLite database involves accessing the file system. So, it can be a slow operation. To avoid ANR (Application Not Responding) errors, it’s recommended to perform database operations asynchronously.
When an application creates and uses a SQLite database, it will be saved by default in the directory : DATA/data/APP_PACKAGE/databases/FILENAME .
1. Architecture
All classes needed to manage databases in Android SDK are contained in the package android.database . The package android.database.sqlite contains the SQLite specific classes.
SQLite API is centered around 2 main classes :
SQLiteOpenHelper that is an helper class to extend to manage database operations.
SQLiteDatabase that is the base class for working with a SQLite database in Android.
2. SQLiteOpenHelper
When you want to work with a SQLite database in Android, you must extend SQLiteOpenHelper class. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.
You need also to override the following methods :
onCreate() that is called when database is accessed but not yet created.
onUpgrade() called when you choose to increment the version number of the database. In this method you can manage the migration process between two databases versions.
Both methods get and SQLiteDatabase instance in parameter which is the way to communicate with the database.
Furthermore, SQLiteOpenHelper provides 2 methods to get access to an SQLiteDatabase instance object respectively in read and in write modes :
getReadableDatabase() for read mode.
getWriteableDatabase() for write mode.
3. SQLiteDatabase
SQLiteDatabase is the class used to communicate with a SQLite database. It exposes several methods to interact with database like insert(), update() or delete().
In addition, it lets you to make queries via rawQuery() to queries made directly in SQL or via query() method. This last method provides a structured interface for specifying a SQL query.
4. Practice
Now, you know theory about SQLite in Android context. We can put in practice all the concepts. To achieve that, we’re going to make a database with a players table letting us to store NBA players.
To start, we create a simple Player Java POJO :
Code:
public class Player {
private int id;
private String name;
private String position;
private int height;
public Player() {
}
public Player(int id, String name, String position, int height) {
this.id = id;
this.name = name;
this.position = position;
this.height = height;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPosition() {
return position;
}
public void setPosition(String position) {
this.position = position;
}
public int getHeight() {
return height;
}
public void setHeight(int height) {
this.height = height;
}
@Override
public String toString() {
return name + " - " + position + " - " + height + " cm";
}
}
Then, we must create the SQLiteOpenHelper extended class to manage our application database. Code is here :
Code:
package com.ssaurel.samples.sqlite;
import java.util.LinkedList;
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;
public class SQLiteDatabaseHandler extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "PlayersDB";
private static final String TABLE_NAME = "Players";
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_POSITION = "position";
private static final String KEY_HEIGHT = "height";
private static final String[] COLUMNS = { KEY_ID, KEY_NAME, KEY_POSITION,
KEY_HEIGHT };
public SQLiteDatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATION_TABLE = "CREATE TABLE Players ( "
+ "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "name TEXT, "
+ "position TEXT, " + "height INTEGER )";
db.execSQL(CREATION_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// you can implement here migration process
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
this.onCreate(db);
}
public void deleteOne(Player player) {
// Get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_NAME, "id = ?", new String[] { String.valueOf(player.getId()) });
db.close();
}
public Player getPlayer(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_NAME, // a. table
COLUMNS, // b. column names
" id = ?", // c. selections
new String[] { String.valueOf(id) }, // d. selections args
null, // e. group by
null, // f. having
null, // g. order by
null); // h. limit
if (cursor != null)
cursor.moveToFirst();
Player player = new Player();
player.setId(Integer.parseInt(cursor.getString(0)));
player.setName(cursor.getString(1));
player.setPosition(cursor.getString(2));
player.setHeight(Integer.parseInt(cursor.getString(3)));
return player;
}
public List<Player> allPlayers() {
List<Player> players = new LinkedList<Player>();
String query = "SELECT * FROM " + TABLE_NAME;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
Player player = null;
if (cursor.moveToFirst()) {
do {
player = new Player();
player.setId(Integer.parseInt(cursor.getString(0)));
player.setName(cursor.getString(1));
player.setPosition(cursor.getString(2));
player.setHeight(Integer.parseInt(cursor.getString(3)));
players.add(player);
} while (cursor.moveToNext());
}
return players;
}
public void addPlayer(Player player) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, player.getName());
values.put(KEY_POSITION, player.getPosition());
values.put(KEY_HEIGHT, player.getHeight());
// insert
db.insert(TABLE_NAME,null, values);
db.close();
}
public int updatePlayer(Player player) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, player.getName());
values.put(KEY_POSITION, player.getPosition());
values.put(KEY_HEIGHT, player.getHeight());
int i = db.update(TABLE_NAME, // table
values, // column/value
"id = ?", // selections
new String[] { String.valueOf(player.getId()) });
db.close();
return i;
}
}
Database is created in the constructor of the extended class. Players table is created in the onCreate() method thanks to a SQL statement.
In our class, we add methods to add a new player, to delete an existing one, to update and then a method to get all the players in the table. In this last method, we use a Cursor object to iterate on rows and then build equivalent Player instances.
To use our class to create some players then display on a simple ListView, we can use the following code :
Code:
public class MainActivity extends Activity {
private SQLiteDatabaseHandler db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// create our sqlite helper class
db = new SQLiteDatabaseHandler(this);
// create some players
Player player1 = new Player(1, "Lebron James", "F", 203);
Player player2 = new Player(2, "Kevin Durant", "F", 208);
Player player3 = new Player(3, "Rudy Gobert", "C", 214);
// add them
db.addPlayer(player1);
db.addPlayer(player2);
db.addPlayer(player3);
// list all players
List<Player> players = db.allPlayers();
if (players != null) {
String[] itemsNames = new String[players.size()];
for (int i = 0; i < players.size(); i++) {
itemsNames[i] = players.get(i).toString();
}
// display like string instances
ListView list = (ListView) findViewById(R.id.list);
list.setAdapter(new ArrayAdapter<String>(this,
android.R.layout.simple_list_item_1, android.R.id.text1, itemsNames));
}
}
}
Execution result can be seen here :
{
"lightbox_close": "Close",
"lightbox_next": "Next",
"lightbox_previous": "Previous",
"lightbox_error": "The requested content cannot be loaded. Please try again later.",
"lightbox_start_slideshow": "Start slideshow",
"lightbox_stop_slideshow": "Stop slideshow",
"lightbox_full_screen": "Full screen",
"lightbox_thumbnails": "Thumbnails",
"lightbox_download": "Download",
"lightbox_share": "Share",
"lightbox_zoom": "Zoom",
"lightbox_new_window": "New window",
"lightbox_toggle_sidebar": "Toggle sidebar"
}
SQLite implementation in Android is simple and really powerful. You can now use it in your Android application to persist data.
Don't hesitate to give it a try and give me your feedbacks about this tutorial.
Thanks.
Sylvain
Hey, I have made a preview for SQLite database earlier this month for my friend.
If anyone's interested then it's there at https://www.GitHub.com/Fifa2151/SQLiteTest
Thanks,
Raj.
Sent from my Pixel using Tapatalk
sylsau said:
Hello,
I create that thread to present you a tutorial learning you to save data with SQLite on Android. This tutorial is also available in video on Youtube :
Learn to save data with SQLite on Android
On Android, there are several solutions to persist data between users’ sessions. One solution is to use a relational database to persist data and then to be able to query easily these data. In standard, Android SDK comes with a SQLite implementation. Biggest advantage of SQLite integration to Android OS is the fact that there is no need to to setup the database. So, no administration of this database. SQLite is embedded in standard and each application can have its SQLite database.
The only job that developers must make is to define SQL tables and statements for creating and updating data. Access to an SQLite database involves accessing the file system. So, it can be a slow operation. To avoid ANR (Application Not Responding) errors, it’s recommended to perform database operations asynchronously.
When an application creates and uses a SQLite database, it will be saved by default in the directory : DATA/data/APP_PACKAGE/databases/FILENAME .
1. Architecture
All classes needed to manage databases in Android SDK are contained in the package android.database . The package android.database.sqlite contains the SQLite specific classes.
SQLite API is centered around 2 main classes :
SQLiteOpenHelper that is an helper class to extend to manage database operations.
SQLiteDatabase that is the base class for working with a SQLite database in Android.
2. SQLiteOpenHelper
When you want to work with a SQLite database in Android, you must extend SQLiteOpenHelper class. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.
You need also to override the following methods :
onCreate() that is called when database is accessed but not yet created.
onUpgrade() called when you choose to increment the version number of the database. In this method you can manage the migration process between two databases versions.
Both methods get and SQLiteDatabase instance in parameter which is the way to communicate with the database.
Furthermore, SQLiteOpenHelper provides 2 methods to get access to an SQLiteDatabase instance object respectively in read and in write modes :
getReadableDatabase() for read mode.
getWriteableDatabase() for write mode.
3. SQLiteDatabase
SQLiteDatabase is the class used to communicate with a SQLite database. It exposes several methods to interact with database like insert(), update() or delete().
In addition, it lets you to make queries via rawQuery() to queries made directly in SQL or via query() method. This last method provides a structured interface for specifying a SQL query.
4. Practice
Now, you know theory about SQLite in Android context. We can put in practice all the concepts. To achieve that, we’re going to make a database with a players table letting us to store NBA players.
To start, we create a simple Player Java POJO :
Code:
public class Player {
private int id;
private String name;
private String position;
private int height;
public Player() {
}
public Player(int id, String name, String position, int height) {
this.id = id;
this.name = name;
this.position = position;
this.height = height;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPosition() {
return position;
}
public void setPosition(String position) {
this.position = position;
}
public int getHeight() {
return height;
}
public void setHeight(int height) {
this.height = height;
}
@Override
public String toString() {
return name + " - " + position + " - " + height + " cm";
}
}
Then, we must create the SQLiteOpenHelper extended class to manage our application database. Code is here :
Code:
package com.ssaurel.samples.sqlite;
import java.util.LinkedList;
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;
public class SQLiteDatabaseHandler extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "PlayersDB";
private static final String TABLE_NAME = "Players";
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_POSITION = "position";
private static final String KEY_HEIGHT = "height";
private static final String[] COLUMNS = { KEY_ID, KEY_NAME, KEY_POSITION,
KEY_HEIGHT };
public SQLiteDatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATION_TABLE = "CREATE TABLE Players ( "
+ "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "name TEXT, "
+ "position TEXT, " + "height INTEGER )";
db.execSQL(CREATION_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// you can implement here migration process
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
this.onCreate(db);
}
public void deleteOne(Player player) {
// Get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_NAME, "id = ?", new String[] { String.valueOf(player.getId()) });
db.close();
}
public Player getPlayer(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_NAME, // a. table
COLUMNS, // b. column names
" id = ?", // c. selections
new String[] { String.valueOf(id) }, // d. selections args
null, // e. group by
null, // f. having
null, // g. order by
null); // h. limit
if (cursor != null)
cursor.moveToFirst();
Player player = new Player();
player.setId(Integer.parseInt(cursor.getString(0)));
player.setName(cursor.getString(1));
player.setPosition(cursor.getString(2));
player.setHeight(Integer.parseInt(cursor.getString(3)));
return player;
}
public List<Player> allPlayers() {
List<Player> players = new LinkedList<Player>();
String query = "SELECT * FROM " + TABLE_NAME;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
Player player = null;
if (cursor.moveToFirst()) {
do {
player = new Player();
player.setId(Integer.parseInt(cursor.getString(0)));
player.setName(cursor.getString(1));
player.setPosition(cursor.getString(2));
player.setHeight(Integer.parseInt(cursor.getString(3)));
players.add(player);
} while (cursor.moveToNext());
}
return players;
}
public void addPlayer(Player player) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, player.getName());
values.put(KEY_POSITION, player.getPosition());
values.put(KEY_HEIGHT, player.getHeight());
// insert
db.insert(TABLE_NAME,null, values);
db.close();
}
public int updatePlayer(Player player) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, player.getName());
values.put(KEY_POSITION, player.getPosition());
values.put(KEY_HEIGHT, player.getHeight());
int i = db.update(TABLE_NAME, // table
values, // column/value
"id = ?", // selections
new String[] { String.valueOf(player.getId()) });
db.close();
return i;
}
}
Database is created in the constructor of the extended class. Players table is created in the onCreate() method thanks to a SQL statement.
In our class, we add methods to add a new player, to delete an existing one, to update and then a method to get all the players in the table. In this last method, we use a Cursor object to iterate on rows and then build equivalent Player instances.
To use our class to create some players then display on a simple ListView, we can use the following code :
Code:
public class MainActivity extends Activity {
private SQLiteDatabaseHandler db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// create our sqlite helper class
db = new SQLiteDatabaseHandler(this);
// create some players
Player player1 = new Player(1, "Lebron James", "F", 203);
Player player2 = new Player(2, "Kevin Durant", "F", 208);
Player player3 = new Player(3, "Rudy Gobert", "C", 214);
// add them
db.addPlayer(player1);
db.addPlayer(player2);
db.addPlayer(player3);
// list all players
List<Player> players = db.allPlayers();
if (players != null) {
String[] itemsNames = new String[players.size()];
for (int i = 0; i < players.size(); i++) {
itemsNames[i] = players.get(i).toString();
}
// display like string instances
ListView list = (ListView) findViewById(R.id.list);
list.setAdapter(new ArrayAdapter<String>(this,
android.R.layout.simple_list_item_1, android.R.id.text1, itemsNames));
}
}
}
Execution result can be seen here :
SQLite implementation in Android is simple and really powerful. You can now use it in your Android application to persist data.
Don't hesitate to give it a try and give me your feedbacks about this tutorial.
Thanks.
Sylvain
Click to expand...
Click to collapse
Awesome guide @sylsau...
Also, do you know how to make a flashify type app but only for a specific zip?
When you want to work with a SQLite database in Android, you must extend SQLiteOpenHelper class. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.
Click to expand...
Click to collapse
You don't actually need to use a subclass of SQLiteOpenHelper you can use the SQliteDatabase's open????? methods.
Furthermore, SQLiteOpenHelper provides 2 methods to get access to an SQLiteDatabase instance object respectively in read and in write modes :
Click to expand...
Click to collapse
Actually, in either case, except if the database cannot be opened, for write, both getReadableDatabase and getWritableDatabase will open a database that can be written to. As per :-
Create and/or open a database. This will be the same object returned by getWritableDatabase() unless some problem, such as a full disk, requires the database to be opened read-only. In that case, a read-only database object will be returned. If the problem is fixed, a future call to getWritableDatabase() may succeed, in which case the read-only database object will be closed and the read/write object will be returned in the future.
Click to expand...
Click to collapse
as per developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper#getReadableDatabase()
On occasions people new to SQLite sometimes wonder why no database exists after they have instantiated the subclass of SQLiteOpenHelper (aka the DatabaseHelper). This is because the database is only created when either getWritableDatabase or getReadableDatabase is called. With a single line added to the constructor, the constructor will create the database (and thus invoke the onCreate method) e.g.
Code:
public SQLiteDatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.getWritableDatabse();
}
AUTOINCREMENT is perhaps the most commonly misused keyword (perhaps wrongly named). It does not make the column automatically generate a unique ID. It is INTEGER PRIMARY KEY that does this, as it make the column an alias of the **rowid**.
Rather AUTOINCREMENT compliments INTEGER PRIMARY KEY adding a constraint that the generated ID must be larger that any ID that exists or have existed. However, this is a moot point as it's only when the largest possible ID has been assigned (9223372036854775807) that it comes into play (other than without AUTOINCREMENT a deleted highest ID will be resused). At this point a table with AUTOINCREMENT will then fail with an SQLITE_FULL exception (without AUTOINCREMENT will attempt to assign a free lower ID rather than fail). However, AUTOINCREMENT has overheads (using a limited test I came up with an 8-12% degradation in performance when inserting). This is due to a changed algorithm being used that utilises another table sqlite_sequence that stores the highest allocated ID.
The SQLite documentation states :-
The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
Click to expand...
Click to collapse
sqlite.org/autoinc.html
There are a few issues with the code, such as :-
You should always close Cursors when finished with them (not doing so may result in too many databases /database objects open exception ).
Checking a Cursor for null after a Cursor is returned from a call to an SQLiteDatabase method that returns a Cursor serves no purpose. A valid Cursor will always be returned. If there is no data then using a Cursor moveTo????? method will return false is the move cannot be made, alternately the getCount() method will return the number of rows in the Cursor.
If there were now rows in the Players table, the the code would fail with an error when an attempt is made to retrieve data at
Code:
player.setId(Integer.parseInt(cursor.getString(0)));
Issues regarding mis-calculated column offsets can be reduced by taking advantage of the Cursor's **getColumnIndex** method.
As such, as an example, the getPlayer method would be improved if changed to :-
Code:
public Player getPlayer(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_NAME, // a. table
COLUMNS, // b. column names
" id = ?", // c. selections
new String[] { String.valueOf(id) }, // d. selections args
null, // e. group by
null, // f. having
null, // g. order by
null); // h. limit
Player player = new Player(); //<<<<<<<<<< Always have a Player to return (should check for default player to indicated )
if (cursor.moveToFirst()) {
player.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(KEY_ID))));
player.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
player.setPosition(cursor.getString(cursor.getColumnIndex(KEY_POSITION)));
player.setHeight(Integer.parseInt(cursor.getString(cursor.getColumnIndex(KEY_HEIGHT))));
}
cursor.close(); //<<<<<<<<<< Important to close a Cursor
return player;
}