Here we are using a java class for creating the table
PlaceDataSQL.java
package com.data.pack;
package com.data.pack;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/** Helper to the database, manages versions and creation */
public class PlaceDataSQL extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "sample.db";
private static final int DATABASE_VERSION = 1;
private Context context;
public PlaceDataSQL(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE gallery (id varchar(20), image BLOB,caption varchar(160),description varchar(200))");
}
private void versionUpdation(SQLiteDatabase db) {
}
/**
* Check if the database already exist to avoid re-copying the file each
* time you open the application.
*
* @return true if it exists, false if it doesn't
*/
public boolean checkDataBase(String db) {
SQLiteDatabase checkDB = null;
try {
String myPath = "data/data/com.data.pack/databases/" + db;
checkDB = SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READONLY);
} catch (SQLiteException e) {
// database does't exist yet.
} catch (Exception e) {
}
if (checkDB != null) {
checkDB.close();
}
return checkDB != null ? true : false;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion >= newVersion)
return;
if (oldVersion == 1) {
Log.d("New Version", "Datas can be upgraded");
}
Log.d("Sample Data", "onUpgrade : " + newVersion);
}
}
Here in the onCreate function the table is created, here for image we are using blog, so that we can insert the image inside the table itself and the onUpgrade function is used to upgrade the table on the next release, for example you had create an application and published it in market and a guy is using your application, now suddenly you like to add another field on your table means you need to change the " DATABASE_VERSION " to 2 and in the onUpgrade funtion you need to write the alter query there. Likewise i had using a funtion checkDataBase(), this is to check whether the database is exist or not.
Then in the main activity we are calling delete to delete the table content, likewise we will be using a function insertData, inside this function we will be inserting data to the table, like that we will be using getDataAndPopulate(), here we will be retrieving data from the table and displaying in the list.
The main thing is we inserting the image to database for this we are using HttpClient and on the retriving time it is very easy to assign the data, so while calling on the next time we can assign and there is no need of url call for showing image.
SqliteDBActivity.java
package com.data.pack;
package com.data.pack;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.HttpStatus;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.util.EntityUtils;
import android.app.ListActivity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.ImageView;
import android.widget.TextView;
public class SqliteDBActivity extends ListActivity {
private static PlaceDataSQL placeData;
private ArrayListid = new ArrayList ();
private ArrayListimage = new ArrayList ();
private ArrayListcaption = new ArrayList ();
private ArrayListdescription = new ArrayList ();
private Button populate;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
populate = (Button) findViewById(R.id.populate);
placeData = new PlaceDataSQL(this);
SQLiteDatabase db = placeData.getWritableDatabase();
Cursor cursors = getRawEvents("select * from gallery");
if (cursors.moveToNext()) {
populate.setVisibility(View.GONE);
getDataAndPopulate();
} else {
populate.setVisibility(View.VISIBLE);
}
db.delete("gallery", "id=?", new String[] {
"12" });
populate.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
try {
callInsertion("1","https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXQTlp6hJ5voBMw9WxJFAFUGW_kUzEeBdoHuRFMkEn8j-mYxrDDmoBizU3Seo1P0BxPre5g2i2JUZi1UpaISGq00rp-h1apLEUxMzVl8WHWQWiZUzhiFRyA63wsvq2tMsN2Ze5LbmhaPk/s1600/twitter_follow.gif","First","This is the first item");
callInsertion("2","https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6KGi-xGVQybCuzpxCAGMXIKz91RSKaLSCEhUF_yDtzNolI6jAfls0nh_xLEB9wJw0kExxZs0FgRHjxFU2Vm4EDzoFWebBinKNFvi8lc_xhpToMInmiOG71ER8jTSEFySuQ_u1lHgbW6I/s320/seek.JPG","Second","This is the second item");
} catch (ClientProtocolException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
getDataAndPopulate();
}
});
}
private void callInsertion(String id, String url, String caption, String description) throws ClientProtocolException, IOException {
DefaultHttpClient mHttpClient = new DefaultHttpClient();
HttpGet mHttpGet = new HttpGet(url);
HttpResponse mHttpResponse = mHttpClient.execute(mHttpGet);
if (mHttpResponse.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
HttpEntity entity = mHttpResponse.getEntity();
if ( entity != null) {
// insert to database
insertData(id,EntityUtils.toByteArray(entity),caption,description);
}
}
}
private void insertData(String id, byte[] image, String caption, String description) {
SQLiteDatabase db = placeData.getWritableDatabase();
ContentValues values;
values = new ContentValues();
values.put("id", id);
values.put("image", image);
values.put("caption", caption);
values.put("description", description);
db.insert("gallery", null, values);
}
private void getDataAndPopulate() {
id = new ArrayList();
image = new ArrayList();
caption = new ArrayList();
description = new ArrayList();
Cursor cursor = getEvents("gallery");
while (cursor.moveToNext()) {
String temp_id = cursor.getString(0);
byte[] temp_image = cursor.getBlob(1);
String temp_caption = cursor.getString(2);
String temp_description = cursor.getString(3);
id.add(temp_id);
image.add(temp_image);
caption.add(temp_caption);
description.add(temp_description);
}
String[] captionArray = (String[]) caption.toArray(
new String[caption.size()]);
ItemsAdapter itemsAdapter = new ItemsAdapter(
SqliteDBActivity.this, R.layout.item,
captionArray);
setListAdapter(itemsAdapter);
populate.setVisibility(View.GONE);
}
private class ItemsAdapter extends BaseAdapter {
String[] items;
public ItemsAdapter(Context context, int textViewResourceId,
String[] items) {
this.items = items;
}
@Override
public View getView(final int POSITION, View convertView,
ViewGroup parent) {
TextView desc;
TextView cap;
View view = convertView;
ImageView img;
if (view == null) {
LayoutInflater vi = (LayoutInflater) getSystemService(Context.LAYOUT_INFLATER_SERVICE);
view = vi.inflate(R.layout.item, null);
}
img = (ImageView) view.findViewById(R.id.image);
cap = (TextView) view.findViewById(R.id.caption);
desc = (TextView) view.findViewById(R.id.description);
cap.setText(caption.get(POSITION));
desc.setText(description.get(POSITION));
img.setImageBitmap(BitmapFactory.decodeByteArray(image.get(POSITION), 0, image.get(POSITION).length));
return view;
}
public int getCount() {
return items.length;
}
public Object getItem(int position) {
return position;
}
public long getItemId(int position) {
return position;
}
}
private Cursor getRawEvents(String sql) {
SQLiteDatabase db = (placeData).getReadableDatabase();
Cursor cursor = db.rawQuery(sql, null);
startManagingCursor(cursor);
return cursor;
}
private Cursor getEvents(String table) {
SQLiteDatabase db = (placeData).getReadableDatabase();
Cursor cursor = db.query(table, null, null, null, null, null, null);
startManagingCursor(cursor);
return cursor;
}
}
In this example i had wrote two types of retrieving methods from the table, by writing the query and specifying the table name itself.
You can download the full source code
Have a good day.
Awesome! Thanks.. This is sorta what im looking for. Can this be applied to Joomla?
ReplyDeletesqlite is local database only, so you can apply for any back end
ReplyDeletehi, first of all gr8 example :) helped a lot, but i also wanna know about storing an object into the database directly; any ideas???
ReplyDeletei am getting one error that is red line under the image.get(POSITION).length) what is the solution of this
Deletethanx
please reload link to source code
ReplyDeleteGreat tutorial. I'm looking for tutorial regarding store and retrieve image using sqlite with pick from photo gallery function, capture new photo by camera app and crop function like WhatsApp change profile photo function. Can you create this tutorial? Thanks in advance.
ReplyDeletehey this part of your code not working for me..
ReplyDeleteimg.setImageBitmap(BitmapFactory.decodeByteArray(image.get(POSITION), 0, image.get(POSITION).length));
Android doesn't recognize method image.get(POSITION).length and showing red line beneath it.. plz help!!
i am also getting same error can some one help me i am not also finding the method image.get(POSITION).length can any one help me
ReplyDeletegetting error
ReplyDeleteunable to download the sourcode even iam registering pls help me
ReplyDeleteI can send it through mail, send your mail id to mylink.mylink@gmail.com
Deletethis Link is unable to download
ReplyDeleteplease send source code
ReplyDeletemention link not worked
priyankahdp@gmail.com
hi , i can't download the souce plz help me or send it to me as soon as possible my id is vineetdhaka001@gmail.com.Thanks
ReplyDeleteThis is a great post. Your Blog the very informative i have learned some information about your blog.
ReplyDeletePhp projects with source code
Online examination system in php
Student management system in php
Php projects for students
Free source code for academic
Academic projects provider in nashik
Academic project free download
Iamlinkfeeder
ReplyDeleteIamlinkfeeder
Iamlinkfeeder
Iamlinkfeeder
Iamlinkfeeder
Iamlinkfeeder
Iamlinkfeeder
Iamlinkfeeder
Iamlinkfeeder
Iamlinkfeeder
Howdy are using Wordpress for your blog platform? I'm new to the blog world but I'm trying to get started and set up my own. Do you need any html coding expertise to make your own blog? Any help would be really appreciated! apple support berlin
ReplyDeleteThank you for sharing the valuable information with us. Besides if you face any kinds of issue with your laptop or pc then ITFUX24 can can you with the best Laptop reparatur Frankfurt service. The experts are highly trained. Make a call and get your first discount!
ReplyDelete